Heureka database upgrade script 0.8.0.0

Back to scripts

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

	-- 1. Create table for Climate Scenarios
	IF OBJECT_ID ('dbo.Scenario','U') IS NULL
	BEGIN
		CREATE TABLE [dbo].[Scenario](
			[ScenarioGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Scenario_ScenarionGuid]  DEFAULT (newid()),
			[Description] [nvarchar](50) NOT NULL,
			[CellSize] [int] NOT NULL,
			[CreatedDate] [datetime] NULL,
			[CreatedBy] [varchar](50) NULL,
		 CONSTRAINT [PK_Scenario] PRIMARY KEY CLUSTERED 
		(
			[ScenarioGuid] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]

		CREATE UNIQUE NONCLUSTERED INDEX [IX_Scenario_Description] ON [dbo].[Scenario] 
		(
			[Description] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	END

	IF OBJECT_ID ('dbo.ScenarioCell','U') IS NULL
	BEGIN
		CREATE TABLE [dbo].[ScenarioCell](
			[ScenarioCellGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ScenarioCell_ScenarioCellGuid]  DEFAULT (newid()),
			[ScenarioGuid] [uniqueidentifier] NOT NULL,
			[CellRow] [int] NULL,
			[CellColumn] [int] NULL,
			[TSChangeFactor] [float] NULL,
		 CONSTRAINT [PK_ScenarioCell] PRIMARY KEY CLUSTERED 
		(
			[ScenarioCellGuid] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]

		ALTER TABLE [dbo].[ScenarioCell]  WITH CHECK ADD  CONSTRAINT [FK_ScenarioCell_Scenario] FOREIGN KEY([ScenarioGuid])
		REFERENCES [dbo].[Scenario] ([ScenarioGuid])
		ON DELETE CASCADE
		ALTER TABLE [dbo].[ScenarioCell] CHECK CONSTRAINT [FK_ScenarioCell_Scenario]
	END

	IF OBJECT_ID ('dbo.ScenarioBiomassEffect','U') IS NULL
	BEGIN
		CREATE TABLE [dbo].[ScenarioBiomassEffect](
			[ScenarioBiomassEffectGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ScenarioParameter_ScenarioParameterGuid]  DEFAULT (newid()),
			[ScenarioCellGuid] [uniqueidentifier] NOT NULL,
			[Species] [int] NOT NULL,
			[SoilMoisture] [int] NOT NULL,
			[a] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_a]  DEFAULT ((0)),
			[b] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_b]  DEFAULT ((0)),
			[c] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_c]  DEFAULT ((0)),
		 CONSTRAINT [PK_ScenarioParameter] PRIMARY KEY CLUSTERED 
		(
			[ScenarioBiomassEffectGuid] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]

		EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=Pine, 2=Spruce, 3=Other' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScenarioBiomassEffect', @level2type=N'COLUMN',@level2name=N'Species'
		EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = Dry, 2 = Mesic, 3 = Moist' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScenarioBiomassEffect', @level2type=N'COLUMN',@level2name=N'SoilMoisture'
		ALTER TABLE [dbo].[ScenarioBiomassEffect]  WITH CHECK ADD  CONSTRAINT [FK_ScenarioParameter_ScenarioCell] FOREIGN KEY([ScenarioCellGuid])
		REFERENCES [dbo].[ScenarioCell] ([ScenarioCellGuid])
		ON DELETE CASCADE
		ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [FK_ScenarioParameter_ScenarioCell]
		ALTER TABLE [dbo].[ScenarioBiomassEffect]  WITH CHECK ADD  CONSTRAINT [CK_ScenarioParameter_SoilMoisture] CHECK  (([SoilMoisture]>(0) AND [SoilMoisture]<(4)))
		ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [CK_ScenarioParameter_SoilMoisture]
		ALTER TABLE [dbo].[ScenarioBiomassEffect]  WITH CHECK ADD  CONSTRAINT [CK_ScenarioParameter_Species] CHECK  (([Species]>(0) AND [Species]<(4)))
		ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [CK_ScenarioParameter_Species]
	END

	-- 2. Add new column to StandObject

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'NatureConservationArea')
	BEGIN
		ALTER TABLE StandObject ADD
			[NatureConservationArea] [real] NULL
	END

	-- 3. Add new columns to InitialState_TreatmentUnit

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_TreatmentUnit' AND COLUMN_NAME = 'ParentTreatmentUnitGuid')
	BEGIN
		ALTER TABLE InitialState_TreatmentUnit ADD
			[ParentTreatmentUnitGuid] [uniqueidentifier] NULL,
			[TreatmentUnitType] [int] NULL
	END

	-- 4. Add new columns to InitialState_EvenAgedData

	exec sp_rename 'InitialState_EvenAgedData.EvenAgeType', 'EvenAgedType', 'COLUMN'

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_EvenAgedData' AND COLUMN_NAME = 'TreeSizeDiversity')
	BEGIN
		ALTER TABLE InitialState_EvenAgedData ADD
			[TreeSizeDiversity] [int] NULL,
			[TreeSizeDiversityGiniCoefficient] [float] NULL
	END

	-- 5. Add new columns to InitialState_BiomassData

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_BiomassData' AND COLUMN_NAME = 'TotalMassTotal')
	BEGIN
		ALTER TABLE InitialState_BiomassData ADD
			[TotalMassTotal] [float] NULL
	END


	-- Update Version Table

	UPDATE Version SET ApplicationVersion = '0.8.0.0';

	PRINT 'Success! Database upgrade to version 0.8.0.0';
END