Heureka database upgrade script 0.5.1.2

Back to scripts

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

	/*===================== 1. Constraints on importsource in area ==========================*/

	--1. Change constraint on column ImportSource in table Area

	ALTER TABLE [dbo].[Area] DROP CONSTRAINT [CK_Area_ImportSource]
	ALTER TABLE [dbo].[Area]  WITH CHECK ADD  CONSTRAINT [CK_Area_ImportSource] CHECK  (([ImportSource]>=(1) AND [ImportSource]<=(6)))

	--2. Change description on column ImportSource in table Area
	IF NOT EXISTS(SELECT 1 FROM ::fn_listextendedproperty ('MS_Description', 'Schema', 'dbo', 'table', 'Area', 'column', 'ImportSource' ))
	BEGIN
		EXEC sp_addextendedproperty 
		@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
		@level0type = N'Schema', @level0name = dbo, 
		@level1type = N'Table',  @level1name = Area, 
		@level2type = N'Column', @level2name = ImportSource;
	END
	ELSE
	BEGIN
		--Update the extended property.
		EXEC sp_updateextendedproperty 
		@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
		@level0type = N'Schema', @level0name = dbo, 
		@level1type = N'Table',  @level1name = Area, 
		@level2type = N'Column', @level2name = ImportSource;
	END


	/*===================== 2. Changes to StandObject ==========================*/

	--1. Add columns for QMD
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'QuadraticMeanDiameter')
	BEGIN
		ALTER TABLE StandObject ADD
			[QuadraticMeanDiameter] [float] NULL,
			[QuadraticMeanDiameterPine] [float] NULL,
			[QuadraticMeanDiameterSpruce] [float] NULL,
			[QuadraticMeanDiameterBirch] [float] NULL,
			[QuadraticMeanDiameterAspen] [float] NULL,
			[QuadraticMeanDiameterOak] [float] NULL,
			[QuadraticMeanDiameterBeech] [float] NULL,
			[QuadraticMeanDiameterDeciduous] [float] NULL,
			[QuadraticMeanDiameterContorta] [float] NULL,
			[QuadraticMeanDiameterBroadleaf] [float] NULL
	END

	--2. Remove columns for StemProportion
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'StemProportionPine')
	BEGIN
		ALTER TABLE StandObject DROP COLUMN
			[StemProportionPine],
			[StemProportionSpruce],
			[StemProportionBirch],
			[StemProportionAspen],
			[StemProportionOak],
			[StemProportionBeech],
			[StemProportionDeciduous],
			[StemProportionContorta],
			[StemProportionBroadleaf]
	END


	--3. Add columns for Regeneration informaiton
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'RegenerationMethod')
	BEGIN
		ALTER TABLE StandObject ADD
			[RegenerationMethod] [int] NULL,
			[RegenerationSpecies] [int] NULL
	END

	--4. Add columns for SoilMoistCode and VegetationTypeCode
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'SoilMoistCode')
	BEGIN
		ALTER TABLE StandObject ADD
			[SoilMoistCode] [int] NULL,
			[VegetationTypeCode] [int] NULL
	END

	--5. Add columns for MeanDiameter and MeanHeight per species
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'MeanDiameterPine')
	BEGIN
		ALTER TABLE StandObject ADD
			[MeanDiameterPine] [float] NULL,
			[MeanDiameterSpruce] [float] NULL,
			[MeanDiameterBirch] [float] NULL,
			[MeanDiameterAspen] [float] NULL,
			[MeanDiameterOak] [float] NULL,
			[MeanDiameterBeech] [float] NULL,
			[MeanDiameterDeciduous] [float] NULL,
			[MeanDiameterContorta] [float] NULL,
			[MeanDiameterBroadleaf] [float] NULL,
			[MeanHeightPine] [float] NULL,
			[MeanHeightSpruce] [float] NULL,
			[MeanHeightBirch] [float] NULL,
			[MeanHeightAspen] [float] NULL,
			[MeanHeightOak] [float] NULL,
			[MeanHeightBeech] [float] NULL,
			[MeanHeightDeciduous] [float] NULL,
			[MeanHeightContorta] [float] NULL,
			[MeanHeightBroadleaf] [float] NULL
	END


	--6. Add columns for additional userdefined columns
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'UserDefined4')
	BEGIN
		ALTER TABLE StandObject ADD
			[UserDefined4] [int] NULL,
			[UserDefined5] [int] NULL,
			[UserDefined6] [real] NULL,
			[UserDefined7] [real] NULL,
			[UserDefined8] [real] NULL,
			[UserDefined9] [real] NULL,
			[UserDefined10] [real] NULL
	END


	-- Update Version Table

	UPDATE Version SET ApplicationVersion = '0.5.1.2';

	PRINT 'Success! Database upgrade to version 0.5.1.2';

END