Heureka database upgrade script 0.5.3

Back to scripts

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