Heureka database upgrade script 0.7.0.0

Back to scripts

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