Heureka database upgrade script 1.0.2.0

Back to scripts

IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.1.0') = 0
BEGIN
	PRINT 'Wrong Version of database. You must upgrade to version 1.0.1.0 before running this script.';
END
ELSE 
BEGIN		

	-- 1. Drop column ImpedimentPercentage

	-- 1.1 Drop column ImpedimentPercentage from table InvReferenceUnitTree
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitTree' AND COLUMN_NAME = 'ImpedimentPercentage')
	BEGIN
		ALTER TABLE InvReferenceUnitTree DROP COLUMN ImpedimentPercentage
	END

	-- 1.2 Remove rows containing ImpedimentPercentage for ReferenceUnitTree from InventoryTemplate
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InventoryTemplate')
	BEGIN
		DELETE FROM InventoryTemplate WHERE TableName = 'ReferenceUnitTree' AND ColumnName = 'ImpedimentPercentage'
	END

	-- 2. Change constraint PlotTypes for ReferenceUnit

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReferenceUnit')
	BEGIN
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'ck_ReferenceUnit_PlotTypeCode')
		BEGIN
			ALTER TABLE [dbo].[ReferenceUnit]  
			DROP CONSTRAINT [ck_ReferenceUnit_PlotTypeCode];
		END

		UPDATE [dbo].[ReferenceUnit]  
		SET [PlotTypeCode] = 4 WHERE [PlotTypeCode] = 3;

		ALTER TABLE [dbo].[ReferenceUnit]  
			WITH CHECK ADD CONSTRAINT [ck_ReferenceUnit_PlotTypeCode] 
			CHECK (([PlotTypeCode]>=(0) AND [PlotTypeCode]<=(2)) OR [PlotTypeCode]=(4));
	END


	--  3. Change name of column VegitationTypeCode in table ReferenceUnit
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'VegitationTypeCode')
	BEGIN

		ALTER TABLE [dbo].[ReferenceUnit] DROP CONSTRAINT ck_ReferenceUnit_VegitationTypeCode

		EXEC sp_rename 'ReferenceUnit.[VegitationTypeCode]', 'VegetationTypeCode', 'COLUMN'
		
	END

	--  Change name of column VegitationTypeCode in table InvReferenceUnitSiteData
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitSiteData' AND COLUMN_NAME = 'VegitationTypeCode')
	BEGIN
		EXEC sp_rename 'InvReferenceUnitSiteData.[VegitationTypeCode]', 'VegetationTypeCode', 'COLUMN'
	END

	--  Change name of column ImpedimentPercentage to ImpedimentFraction in table ReferenceUnit
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'ImpedimentPercentage')
	BEGIN
		EXEC sp_rename 'ReferenceUnit.[ImpedimentPercentage]', 'ImpedimentFraction', 'COLUMN'
	END

	--  Change name of column ImpedimentPercentage to ImpedimentFraction in table IpakTreatmentUnit
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IpakTreatmentUnit' AND COLUMN_NAME = 'ImpedimentPercentage')
	BEGIN
		EXEC sp_rename 'IpakTreatmentUnit.[ImpedimentPercentage]', 'ImpedimentFraction', 'COLUMN'
	END

	-- Update rows containing metadata for column ReferenceUnitSiteData.VegetationTypeCode in table InventoryTemplate
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InventoryTemplate')
	BEGIN
		UPDATE [dbo].[InventoryTemplate] SET ColumnName = 'VegetationTypeCode' WHERE ColumnName = 'VegitationTypeCode' AND TableName = 'ReferenceUnitSiteData'
	END

	-- 4. separate scriptfile

	-- 5. Add column SmallTrees to table ReferenceUnit

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReferenceUnit')
	AND NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'SmallTrees')
	BEGIN
		ALTER TABLE [ReferenceUnit]
		ADD [SmallTrees] [INT] NOT NULL DEFAULT 0
	END

	-- 6. Add column PlantPlotRadius

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'PlantPlotRadius')
	BEGIN
		ALTER TABLE [Stratification] ADD
			[PlantPlotRadius] [float] NULL
	END


	-- Update Version Table

	UPDATE Version SET ApplicationVersion = '1.0.2.0';
	PRINT 'Success! Database upgrade to version 1.0.2.0';
END