Heureka database upgrade script 1.0

From Heureka Wiki
Revision as of 08:57, 10 October 2009 by Peder (talk | contribs)
Jump to navigation Jump to search

Back to scripts 

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 = '0.1.0.0';
	PRINT 'Success! Database upgrade to version 0.1.0.0';
END
Retrieved from "https://www.heurekaslu.se/w/index.php?title=Heureka_database_upgrade_script_1.0&oldid=3377"