Heureka database upgrade script 0.5.1.2
Jump to navigation
Jump to search
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