Heureka database upgrade script 1.0
Jump to navigation
Jump to search
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.8.0.1') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.8.0.1 before running this script.'; END ELSE BEGIN -- 1. Stratification scripts -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTree' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTree.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeSample' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTreeSample.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitTree' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvReferenceUnitTree.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentHistory' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTreatmentHistory.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvSitePosition' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvSitePosition.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantMain' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvPlantMain.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantBi' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvPlantBi.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvReferenceUnitPlant.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'RejuvenationProposal') BEGIN EXEC sp_rename 'InvReferenceUnitPlant.[RejuvenationProposal]', 'RegenerationProposal', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeDead' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTreeDead.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentHistory' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTreatmentHistory.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitSiteData' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvReferenceUnitSiteData.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvReferenceUnit.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvTreatmentUnit.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvCorrectionFactor' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'InvCorrectionFactor.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'StandInventory.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- Change name of column StratificationGUID to InventoryGUID IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandobjectPlot' AND COLUMN_NAME = 'StratificationGUID') BEGIN EXEC sp_rename 'StandobjectPlot.[StratificationGUID]', 'InventoryGUID', 'COLUMN' END -- IF OBJECT_ID ('dbo.InventoryState','U') IS NULL BEGIN CREATE TABLE [dbo].[InventoryState]( [Stateid] [int] NOT NULL, [Description] [nchar](50) NULL, CONSTRAINT [PK_inventoryState] PRIMARY KEY CLUSTERED ( [Stateid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (0, 'Empty') INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (1, 'Under Construction') INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (2, 'Ready For Inventory') INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (3, 'Under Inventory') INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (4, 'Ready To Publish') INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (5, 'Published') END -- IF OBJECT_ID ('dbo.Inventory','U') IS NULL BEGIN CREATE TABLE [dbo].[Inventory]( [InventoryGUID] [uniqueidentifier] NOT NULL, [Description] [varchar](255) NOT NULL, [StratificationGUID] [uniqueidentifier] NULL, [StateId] [int] NULL, [InventoryTemplateGUID] [uniqueidentifier] NULL, [ModifiedBy] [varchar](255) NULL, [ModifiedDate] [datetime] NULL, CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED ( [InventoryGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_inventoryState] FOREIGN KEY([StateId]) REFERENCES [dbo].[inventoryState] ([Stateid]) ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_inventoryState] ALTER TABLE [dbo].[InvTreatmentUnit] WITH CHECK ADD CONSTRAINT [FK_InvTreatmentUnit_Inventory1] FOREIGN KEY([InventoryGUID]) REFERENCES [dbo].[Inventory] ([InventoryGUID]) ALTER TABLE [dbo].[InvTreatmentUnit] CHECK CONSTRAINT [FK_InvTreatmentUnit_Inventory1] END -- IF OBJECT_ID ('dbo.InventoryTemplate','U') IS NULL BEGIN CREATE TABLE [dbo].[InventoryTemplate]( [InventoryGUID] [uniqueidentifier] NOT NULL, [TableName] [nvarchar](50) NOT NULL, [ColumnName] [nvarchar](50) NOT NULL, [ColumnOrder] [int] NULL, [Visible] [bit] NULL, [IsRemovable] [bit] NULL, [IsEditable] [bit] NULL, [DataType] [nvarchar](50) NULL, [AllowNULL] [bit] NULL, [Description] [nvarchar](max) NULL, [DescriptionShort] [nvarchar](max) NULL, [IsSelected] [bit] NULL, [Mandatory] [bit] NULL, [DefaultValue] [varchar](max) NULL, [NumberOfDecimals] [int] NULL, [ValidValues] [varchar](max) NULL, [ValidValuesDesc] [varchar](max) NULL, CONSTRAINT [PK_InventoryTemplate] PRIMARY KEY CLUSTERED ( [InventoryGUID] ASC, [TableName] ASC, [ColumnName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[InventoryTemplate] WITH CHECK ADD CONSTRAINT [FK_InventoryTemplate_Inventory1] FOREIGN KEY([InventoryGUID]) REFERENCES [dbo].[Inventory] ([InventoryGUID]) ALTER TABLE [dbo].[InventoryTemplate] CHECK CONSTRAINT [FK_InventoryTemplate_Inventory1] END IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'HPCordEast' AND DATA_TYPE = 'real') BEGIN ALTER TABLE InvReferenceUnit ALTER COLUMN HPCordEast float END IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'HPCordNOrth' AND DATA_TYPE = 'real') BEGIN ALTER TABLE InvReferenceUnit ALTER COLUMN HPCordNOrth float END -- 2. Rename columns for rejuvenation to regeneration IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'RejuvenationProposal') BEGIN exec sp_rename 'ReferenceUnit.RejuvenationProposal', 'RegenerationProposal', 'COLUMN' END -- 3. Add columns for InitialState_ForestData --(added one at a time to make sure both columns are added, one of the columns has existed before, but may have been removed) IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'BiologicalMeanAgeTotal') BEGIN ALTER TABLE InitialState_ForestData ADD [BiologicalMeanAgeTotal] [float] NULL END IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'BiologicalMeanAgeTotalExclOverstorey') BEGIN ALTER TABLE InitialState_ForestData ADD [BiologicalMeanAgeTotalExclOverstorey] [float] NULL END IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'SiteIndex') BEGIN ALTER TABLE InitialState_ForestData ADD [SiteIndex] [float] NULL END -- Update Version Table UPDATE Version SET ApplicationVersion = '1.0.0.0'; PRINT 'Success! Database upgrade to version 1.0.0.0'; END