Heureka database upgrade script 0.7.0.0
Jump to navigation
Jump to search
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7.0.0') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.6.0.2 before running this script.'; END ELSE BEGIN -- 1. Rename columns in InitialState_StatisticsData IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_StatisticsData' AND COLUMN_NAME = 'Volume_ConfidenceInterval') BEGIN exec sp_rename 'InitialState_StatisticsData.Volume_ConfidenceInterval', 'VolumeStdDev', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Diameter_ConfidenceInterval', 'DiameterStdDev', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Age_ConfidenceInterval', 'AgeStdDev', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Stems_ConfidenceInterval', 'StemsStdDev', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Height_ConfidenceInterval', 'HeightStdDev', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Volume_StandardError', 'VolumeStdErr', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Diameter_StandardError', 'DiameterStdErr', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Age_StandardError', 'AgeStdErr', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Stems_StandardError', 'StemsStdErr', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Height_StandardError', 'HeightStdErr', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Volume_TotalPerHa', 'VolumeAvg', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Stems_TotalPerHa', 'StemsAvg', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Diameter_MeanValue', 'DiameterAvg', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Age_MeanValue', 'AgeAvg', 'COLUMN'; exec sp_rename 'InitialState_StatisticsData.Height_MeanValue', 'HeightAvg', 'COLUMN'; END -- 2. Add columns for sample plot to TreatmentUnit and InitialState_TreatmentUnit IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TreatmentUnit' AND COLUMN_NAME = 'IsSamplePlot') BEGIN ALTER TABLE [TreatmentUnit] ADD [IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsSamplePlot] DEFAULT ((0)), [IsCircular] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsCircular] DEFAULT ((0)) ALTER TABLE [InitialState_TreatmentUnit] ADD [IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_InitialState_TreatmentUnit_IsSamplePlot] DEFAULT ((0)) END -- 3. Add columns to InitialState IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'ArithmeticMeanAgeTotal') BEGIN ALTER TABLE [InitialState_ForestData] ADD [ArithmeticMeanAgeTotal] [float] NULL, [ArithmeticMeanAgeTotalExclOverstorey] [float] NULL ALTER TABLE [InitialState_PredictionUnit] ADD [ArithmeticMeanAgeTotal] [float] NULL END -- 4. Change site index in StandObject ALTER TABLE StandObject DROP CONSTRAINT [DF_StandObject_SiteIndex] ALTER TABLE StandObject ALTER COLUMN [SiteIndex] [real] NULL ALTER TABLE StandObject ADD CONSTRAINT [DF_StandObject_SiteIndex] DEFAULT ((0)) FOR [SiteIndex] -- 5. Add column for meanHeightArithmetic to ReferenceUnit IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'MeanHeightArithmetic') BEGIN ALTER TABLE [ReferenceUnit] ADD [MeanHeightArithmetic] [real] NULL END -- 6. Add column for SampleDesign to InvTreatmentUnit IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'SampleDesign') BEGIN ALTER TABLE [InvTreatmentUnit] ADD [SampleDesign] [varchar](50) NULL END -- Update Version Table UPDATE Version SET ApplicationVersion = '0.7.0.0'; PRINT 'Success! Database upgrade to version 0.7.0.0'; END