Difference between revisions of "Heureka database upgrade script 0.8.0.0"
Jump to navigation
Jump to search
(New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.7.0.0' OR ApplicationVersion = '0.8.0.0') = 0 BEGIN PRINT 'Wrong Vers...) |
(No difference)
|
Latest revision as of 15:20, 21 September 2009
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