Heureka database upgrade script 0.5.1.1

From Heureka Wiki
Revision as of 15:45, 16 February 2009 by Cawa2 (talk | contribs) (New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.1.0' OR ApplicationVersion = '0.5.1.1' ) = 0 BEGIN PRINT 'Wro...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Back to scripts

IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.1.0' OR ApplicationVersion = '0.5.1.1' ) = 0
BEGIN
	PRINT 'Wrong Version of database. You must upgrade to version 0.5.1.0 before running this script.';
	SELECT ApplicationVersion FROM VERSION;
END
ELSE 
BEGIN

	-- 1. Add extended property on database to identify heureka forest databases

	IF NOT EXISTS( SELECT 1 FROM fn_listextendedproperty('Heureka Database Type', default, default, default, default, default, default))
	BEGIN
		EXEC sp_addextendedproperty @name = N'Heureka Database Type', @value = 'ForestDatabase';
	END


	-- 2. Add columns for to StandObject for number of stems per speciesGroup

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'StemProportionPine')
	BEGIN
			ALTER TABLE StandObject ADD
				[StemProportionPine] [real] NULL,
				[StemProportionSpruce] [real] NULL,
				[StemProportionBirch] [real] NULL,
				[StemProportionAspen] [real] NULL,
				[StemProportionOak] [real] NULL,
				[StemProportionBeech] [real] NULL,
				[StemProportionDeciduous] [real] NULL,
				[StemProportionContorta] [real] NULL,
				[StemProportionBroadleaf] [real] NULL
	END


	-- 3. Add new columns for to InitialState_ForestData

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'BasalAreaWeightedMeanDiameterAll')
	BEGIN
		ALTER TABLE [dbo].[InitialState_ForestData] ADD
			[BasalAreaWeightedMeanDiameterAll] [float] NULL,
			[BasalAreaWeightedMeanDiameterOverstorey] [float] NULL,
			[BasalAreaWeightedMeanDiameterExclOverstorey] [float] NULL,
			[MeanHeightAll] [float] NULL,
			[MeanHeightOverstorey] [float] NULL,
			[MeanHeightExclOverstorey] [float] NULL
	END

	
	-- Update Version Table

	UPDATE Version SET ApplicationVersion = '0.5.1.1';

	PRINT 'Success! Database upgrade to version 0.5.1.1';

END