Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.7.0.0' OR ApplicationVersion = '0.8.0.0') = 0 BEGIN 	PRINT 'Wrong Vers...

2009-09-21T13:20:00Z

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...

New page

[[:Category:Database scripts | 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 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

</pre>
[[Category:Database scripts]]

Heureka database upgrade script 0.8.0.0 - Revision history

Cawa2: New page: Back to scripts
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.7.0.0' OR ApplicationVersion = '0.8.0.0') = 0 BEGIN PRINT 'Wrong Vers...