Heureka database upgrade script 0.5.3
Jump to navigation
Jump to search
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.5.2.1 before running this script.'; SELECT ApplicationVersion FROM VERSION; END ELSE BEGIN -- 1. Rename columns in StandObject IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'ProportionPine') BEGIN exec sp_rename 'StandObject.ProportionPine', 'Proportion_Pine', 'COLUMN'; exec sp_rename 'StandObject.ProportionSpruce', 'Proportion_Spruce', 'COLUMN'; exec sp_rename 'StandObject.ProportionBirch', 'Proportion_Birch', 'COLUMN'; exec sp_rename 'StandObject.ProportionAspen', 'Proportion_Aspen', 'COLUMN'; exec sp_rename 'StandObject.ProportionOak', 'Proportion_Oak', 'COLUMN'; exec sp_rename 'StandObject.ProportionBeech', 'Proportion_Beech', 'COLUMN'; exec sp_rename 'StandObject.ProportionDeciduous', 'Proportion_Deciduous', 'COLUMN'; exec sp_rename 'StandObject.ProportionContorta', 'Proportion_Contorta', 'COLUMN'; exec sp_rename 'StandObject.ProportionBroadleaf', 'Proportion_Broadleaf', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterPine', 'QuadraticMeanDiameter_Pine', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterSpruce', 'QuadraticMeanDiameter_Spruce', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterBirch', 'QuadraticMeanDiameter_Birch', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterAspen', 'QuadraticMeanDiameter_Aspen', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterOak', 'QuadraticMeanDiameter_Oak', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterBeech', 'QuadraticMeanDiameter_Beech', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterDeciduous', 'QuadraticMeanDiameter_Deciduous', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterContorta', 'QuadraticMeanDiameter_Contorta', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameterBroadleaf', 'QuadraticMeanDiameter_Broadleaf', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterPine', 'MeanDiameter_Pine', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterSpruce', 'MeanDiameter_Spruce', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterBirch', 'MeanDiameter_Birch', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterAspen', 'MeanDiameter_Aspen', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterOak', 'MeanDiameter_Oak', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterBeech', 'MeanDiameter_Beech', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterDeciduous', 'MeanDiameter_Deciduous', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterContorta', 'MeanDiameter_Contorta', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameterBroadleaf', 'MeanDiameter_Broadleaf', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightPine', 'MeanHeight_Pine', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightSpruce', 'MeanHeight_Spruce', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightBirch', 'MeanHeight_Birch', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightAspen', 'MeanHeight_Aspen', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightOak', 'MeanHeight_Oak', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightBeech', 'MeanHeight_Beech', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightDeciduous', 'MeanHeight_Deciduous', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightContorta', 'MeanHeight_Contorta', 'COLUMN'; exec sp_rename 'StandObject.MeanHeightBroadleaf', 'MeanHeight_Broadleaf', 'COLUMN'; exec sp_rename 'StandObject.MeanDiameter', 'MeanDiameterTotal', 'COLUMN'; exec sp_rename 'StandObject.MeanHeight', 'MeanHeightTotal', 'COLUMN'; exec sp_rename 'StandObject.QuadraticMeanDiameter', 'QuadraticMeanDiameterTotal', 'COLUMN'; exec sp_rename 'StandObject.LastClearCutYear', 'LastFinalFellingYear', 'COLUMN'; END -- 2. Add columns for arithmetic mean height to table InitialState_SpeciesData IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_SpeciesData' AND COLUMN_NAME = 'ArithmeticMeanHeightPlantPlots_Unknown') BEGIN ALTER TABLE [InitialState_SpeciesData] ADD [ArithmeticMeanHeightPlantPlots_Unknown] [float] NULL, [ArithmeticMeanHeightPlantPlots_Pine] [float] NULL, [ArithmeticMeanHeightPlantPlots_Spruce] [float] NULL, [ArithmeticMeanHeightPlantPlots_Birch] [float] NULL, [ArithmeticMeanHeightPlantPlots_Aspen] [float] NULL, [ArithmeticMeanHeightPlantPlots_Oak] [float] NULL, [ArithmeticMeanHeightPlantPlots_Beech] [float] NULL, [ArithmeticMeanHeightPlantPlots_Deciduous] [float] NULL, [ArithmeticMeanHeightPlantPlots_Contorta] [float] NULL, [ArithmeticMeanHeightPlantPlots_Broadleaf] [float] NULL END -- 3. Add table Cliques IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cliques') BEGIN CREATE TABLE [dbo].[Cliques]( [AreaGUID] [uniqueidentifier] NOT NULL, [CliqueID] [int] NOT NULL, [TreatmentUnitGUID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID]) REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID]) ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_TreatmentUnit] ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_Area] FOREIGN KEY([AreaGUID]) REFERENCES [dbo].[Area] ([AreaGUID]) ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_Area] END -- 4. Add table HarvestClusters IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'HarvestClusters') BEGIN CREATE TABLE [dbo].[HarvestClusters]( [AreaGUID] [uniqueidentifier] NOT NULL, [HarvestClusterID] [int] NOT NULL, [TreatmentUnitGUID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID]) REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID]) ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_TreatmentUnit] ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_Area] FOREIGN KEY([AreaGUID]) REFERENCES [dbo].[Area] ([AreaGUID]) ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_Area] END -- Update Version Table UPDATE Version SET ApplicationVersion = '0.5.3'; PRINT 'Success! Database upgrade to version 0.5.3'; END