Heureka database upgrade script 0.5.4.0
Jump to navigation
Jump to search
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.3.2') = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.5.3.2 before running this script.';
SELECT ApplicationVersion FROM VERSION;
END
ELSE
BEGIN
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
-- 1. Add new column for ObjectID to GIS_Polygon
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GIS_Polygon' AND COLUMN_NAME = 'ObjectId')
BEGIN
ALTER TABLE [dbo].[GIS_Polygon] ADD
[ObjectId] [int] NULL
END
-- 2. Create new stratification database
-- 2.1. Drop StratumStandObjectJunction
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumStandObjectJunction_StandObject]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumStandObjectJunction]'))
ALTER TABLE [dbo].[StratumStandObjectJunction] DROP CONSTRAINT [FK_StratumStandObjectJunction_StandObject];
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumStandObjectJunction_Stratum]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumStandObjectJunction]'))
ALTER TABLE [dbo].[StratumStandObjectJunction] DROP CONSTRAINT [FK_StratumStandObjectJunction_Stratum];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumStandObjectJunction]') AND type in (N'U'))
DROP TABLE [dbo].[StratumStandObjectJunction];
-- 2.2. Drop StratumConditionJunction
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumConditionJunction_Stratum]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumConditionJunction]'))
ALTER TABLE [dbo].[StratumConditionJunction] DROP CONSTRAINT [FK_StratumConditionJunction_Stratum];
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumConditionJunction_StratumCondition]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumConditionJunction]'))
ALTER TABLE [dbo].[StratumConditionJunction] DROP CONSTRAINT [FK_StratumConditionJunction_StratumCondition];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionJunction]') AND type in (N'U'))
DROP TABLE [dbo].[StratumConditionJunction];
-- 2.3. Drop StratumConditionDetailValue
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumConditionDetailValue_StratumConditionDetailValue]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumConditionDetailValue]'))
ALTER TABLE [dbo].[StratumConditionDetailValue] DROP CONSTRAINT [FK_StratumConditionDetailValue_StratumConditionDetailValue];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionDetailValue]') AND type in (N'U'))
DROP TABLE [dbo].[StratumConditionDetailValue];
-- 2.4. Drop StratumConditionDetail
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumConditionDetail_StratumCondition]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumConditionDetail]'))
ALTER TABLE [dbo].[StratumConditionDetail] DROP CONSTRAINT [FK_StratumConditionDetail_StratumCondition];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionDetail]') AND type in (N'U'))
DROP TABLE [dbo].[StratumConditionDetail];
-- 2.5. Drop StratumCondition
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratumCondition_Stratification]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratumCondition]'))
ALTER TABLE [dbo].[StratumCondition] DROP CONSTRAINT [FK_StratumCondition_Stratification];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumCondition]') AND type in (N'U'))
DROP TABLE [dbo].[StratumCondition];
-- 2.6. Drop Stratum table
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Strata_Strata]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stratum]'))
ALTER TABLE [dbo].[Stratum] DROP CONSTRAINT [FK_Strata_Strata];
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Strata_Stratification]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stratum]'))
ALTER TABLE [dbo].[Stratum] DROP CONSTRAINT [FK_Strata_Stratification];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stratum]') AND type in (N'U'))
DROP TABLE [dbo].[Stratum];
-- 2.7. Drop StrataMatrixValue
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StrataMatrixValue_StrataMatrix]') AND parent_object_id = OBJECT_ID(N'[dbo].[StrataMatrixValue]'))
ALTER TABLE [dbo].[StrataMatrixValue] DROP CONSTRAINT [FK_StrataMatrixValue_StrataMatrix];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrataMatrixValue]') AND type in (N'U'))
DROP TABLE [dbo].[StrataMatrixValue];
-- 2.8. Drop StrataMatrixInterval
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StrataMatrixInterval_StrataMatrix]') AND parent_object_id = OBJECT_ID(N'[dbo].[StrataMatrixInterval]'))
ALTER TABLE [dbo].[StrataMatrixInterval] DROP CONSTRAINT [FK_StrataMatrixInterval_StrataMatrix];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrataMatrixInterval]') AND type in (N'U'))
DROP TABLE [dbo].[StrataMatrixInterval];
-- 2.9. Drop StrataMatrix
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StrataMatrix_Stratification]') AND parent_object_id = OBJECT_ID(N'[dbo].[StrataMatrix]'))
ALTER TABLE [dbo].[StrataMatrix] DROP CONSTRAINT [FK_StrataMatrix_Stratification];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrataMatrix]') AND type in (N'U'))
DROP TABLE [dbo].[StrataMatrix];
-- 2.10. Drop Stratification table
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stratification_StandRegister]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stratification]'))
ALTER TABLE [dbo].[Stratification] DROP CONSTRAINT [FK_Stratification_StandRegister];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stratification]') AND type in (N'U'))
DROP TABLE [dbo].[Stratification];
-- 2.11. Create ClassDescription
IF OBJECT_ID ('dbo.ClassDescription','U') IS NULL
BEGIN
CREATE TABLE [dbo].[ClassDescription](
[ClassGuid] [uniqueidentifier] NOT NULL,
[PropertyName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[Species] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[ClassWidth] [float] NOT NULL,
[NumberOfClasses] [int] NOT NULL,
CONSTRAINT [PK_ClassDescription] PRIMARY KEY CLUSTERED
(
[ClassGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
END
-- 2.12. Create ContingencyTable
IF OBJECT_ID ('dbo.ContingencyTable','U') IS NULL
BEGIN
CREATE TABLE [dbo].[ContingencyTable](
[TableGuid] [uniqueidentifier] NOT NULL,
[RowClassGuid] [uniqueidentifier] NOT NULL,
[ColumnClassGuid] [uniqueidentifier] NOT NULL,
[DefaultCellValue] [float] NULL,
CONSTRAINT [PK_ContingencyTable] PRIMARY KEY CLUSTERED
(
[TableGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[ContingencyTable] WITH CHECK ADD CONSTRAINT [FK_ContingencyTable_ColumnClassDescription] FOREIGN KEY([ColumnClassGuid])
REFERENCES [dbo].[ClassDescription] ([ClassGuid]);
ALTER TABLE [dbo].[ContingencyTable] CHECK CONSTRAINT [FK_ContingencyTable_ColumnClassDescription];
ALTER TABLE [dbo].[ContingencyTable] WITH CHECK ADD CONSTRAINT [FK_ContingencyTable_RowClassDescription] FOREIGN KEY([RowClassGuid])
REFERENCES [dbo].[ClassDescription] ([ClassGuid]);
ALTER TABLE [dbo].[ContingencyTable] CHECK CONSTRAINT [FK_ContingencyTable_RowClassDescription];
END
-- 2.13. Create Stratification
IF OBJECT_ID ('dbo.Stratification','U') IS NULL
BEGIN
CREATE TABLE [dbo].[Stratification](
[StratificationGUID] [uniqueidentifier] NOT NULL,
[Description] [varchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[StandRegisterId] [int] NULL,
[DesiredTotalNoOfSamples] [int] NULL,
[MaxAreaProportion] [int] NOT NULL,
[RandomSeed] [int] NULL,
[P1] [float] NOT NULL,
[P3] [float] NOT NULL,
[NoOfPlotsTableGuid] [uniqueidentifier] NOT NULL,
[PlotRadiusTableGuid] [uniqueidentifier] NOT NULL,
[AvgSampleTreesOnPlot] [int] NOT NULL CONSTRAINT [DF_Stratification_AvgSampleTreesOnPlot] DEFAULT ((2)),
[StandardErrorPropertyName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[StandardErrorSpecies] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
CONSTRAINT [PK_Stratification] PRIMARY KEY CLUSTERED
(
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[Stratification] WITH CHECK ADD CONSTRAINT [FK_Stratification_NoOfPlotsContingencyTable] FOREIGN KEY([NoOfPlotsTableGuid])
REFERENCES [dbo].[ContingencyTable] ([TableGuid]);
ALTER TABLE [dbo].[Stratification] CHECK CONSTRAINT [FK_Stratification_NoOfPlotsContingencyTable];
ALTER TABLE [dbo].[Stratification] WITH CHECK ADD CONSTRAINT [FK_Stratification_PlotRadiusContingencyTable] FOREIGN KEY([PlotRadiusTableGuid])
REFERENCES [dbo].[ContingencyTable] ([TableGuid]);
ALTER TABLE [dbo].[Stratification] CHECK CONSTRAINT [FK_Stratification_PlotRadiusContingencyTable];
ALTER TABLE [dbo].[Stratification] WITH CHECK ADD CONSTRAINT [FK_Stratification_StandRegister] FOREIGN KEY([StandRegisterId])
REFERENCES [dbo].[StandRegister] ([StandRegisterId]);
ALTER TABLE [dbo].[Stratification] CHECK CONSTRAINT [FK_Stratification_StandRegister];
END
-- 2.14. Create StratificationDomain
IF OBJECT_ID ('dbo.StratificationDomain','U') IS NULL
BEGIN
CREATE TABLE [dbo].[StratificationDomain](
[StratificationDomainGuid] [uniqueidentifier] NOT NULL,
[StratificationGuid] [uniqueidentifier] NOT NULL,
[DomainNo] [int] NOT NULL,
[Name] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[IsDefault] [bit] NOT NULL,
[UserDefinedName] [bit] NOT NULL,
[TableGuid] [uniqueidentifier] NOT NULL,
[HasDefaultTable] [bit] NOT NULL,
CONSTRAINT [PK_StratificationDomain] PRIMARY KEY CLUSTERED
(
[StratificationDomainGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[StratificationDomain] WITH CHECK ADD CONSTRAINT [FK_StratificationDomain_ContingencyTable] FOREIGN KEY([TableGuid])
REFERENCES [dbo].[ContingencyTable] ([TableGuid]);
ALTER TABLE [dbo].[StratificationDomain] CHECK CONSTRAINT [FK_StratificationDomain_ContingencyTable];
ALTER TABLE [dbo].[StratificationDomain] WITH CHECK ADD CONSTRAINT [FK_StratificationDomain_Stratification] FOREIGN KEY([StratificationGuid])
REFERENCES [dbo].[Stratification] ([StratificationGUID])
ON DELETE CASCADE;
ALTER TABLE [dbo].[StratificationDomain] CHECK CONSTRAINT [FK_StratificationDomain_Stratification];
END
-- 2.15. Create StratificationDomainCondition
IF OBJECT_ID ('dbo.StratificationDomainCondition','U') IS NULL
BEGIN
CREATE TABLE [dbo].[StratificationDomainCondition](
[StratificationDomainConditionGuid] [uniqueidentifier] NOT NULL,
[StratificationDomainGuid] [uniqueidentifier] NOT NULL,
[PropertyName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[Species] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[OperatorType] [int] NOT NULL,
[SimpleValue] [varchar](1024) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[ValueList] [varchar](1024) COLLATE Finnish_Swedish_CI_AS NOT NULL,
CONSTRAINT [PK_StratificationDomainCondition] PRIMARY KEY CLUSTERED
(
[StratificationDomainConditionGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[StratificationDomainCondition] WITH CHECK ADD CONSTRAINT [FK_StratificationDomainCondition_StratificationDomain] FOREIGN KEY([StratificationDomainGuid])
REFERENCES [dbo].[StratificationDomain] ([StratificationDomainGuid])
ON DELETE CASCADE;
ALTER TABLE [dbo].[StratificationDomainCondition] CHECK CONSTRAINT [FK_StratificationDomainCondition_StratificationDomain];
ALTER TABLE [dbo].[StratificationDomainCondition] WITH CHECK ADD CONSTRAINT [FK_StratificationDomainCondition_StratificationDomainCondition] FOREIGN KEY([StratificationDomainConditionGuid])
REFERENCES [dbo].[StratificationDomainCondition] ([StratificationDomainConditionGuid]);
ALTER TABLE [dbo].[StratificationDomainCondition] CHECK CONSTRAINT [FK_StratificationDomainCondition_StratificationDomainCondition];
END
-- 2.16. Create Stratum
IF OBJECT_ID ('dbo.Stratum','U') IS NULL
BEGIN
CREATE TABLE [dbo].[Stratum](
[StratumGuid] [uniqueidentifier] NOT NULL,
[StratumNo] [int] NOT NULL,
[Color] [int] NOT NULL,
[DesiredNoOfSamples] [int] NULL,
[StratificationDomainGuid] [uniqueidentifier] NOT NULL,
[AutoGenerated] [bit] NOT NULL CONSTRAINT [DF_Stratum_AutoGenerated] DEFAULT ((0)),
CONSTRAINT [PK_Stratum] PRIMARY KEY CLUSTERED
(
[StratumGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[Stratum] WITH CHECK ADD CONSTRAINT [FK_Stratum_StratificationDomain] FOREIGN KEY([StratificationDomainGuid])
REFERENCES [dbo].[StratificationDomain] ([StratificationDomainGuid])
ON DELETE CASCADE;
ALTER TABLE [dbo].[Stratum] CHECK CONSTRAINT [FK_Stratum_StratificationDomain];
END
-- 2.17. Create StratumStandObject
IF OBJECT_ID ('dbo.StratumStandObject','U') IS NULL
BEGIN
CREATE TABLE [dbo].[StratumStandObject](
[StandObjectGuid] [uniqueidentifier] NOT NULL,
[StratumGuid] [uniqueidentifier] NOT NULL,
[RepresentativeArea] [float] NOT NULL,
[PlotCount] [int] NOT NULL,
[TreePlotRadius] [float] NOT NULL,
[PlantPlotRadius] [float] NOT NULL,
[P2] [float] NOT NULL,
CONSTRAINT [PK_StratumStandObject] PRIMARY KEY CLUSTERED
(
[StandObjectGuid] ASC,
[StratumGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[StratumStandObject] WITH CHECK ADD CONSTRAINT [FK_StratumStandObject_StandObject] FOREIGN KEY([StandObjectGuid])
REFERENCES [dbo].[StandObject] ([StandObjectGuid]);
ALTER TABLE [dbo].[StratumStandObject] CHECK CONSTRAINT [FK_StratumStandObject_StandObject];
ALTER TABLE [dbo].[StratumStandObject] WITH CHECK ADD CONSTRAINT [FK_StratumStandObject_Stratum] FOREIGN KEY([StratumGuid])
REFERENCES [dbo].[Stratum] ([StratumGuid])
ON DELETE CASCADE;
ALTER TABLE [dbo].[StratumStandObject] CHECK CONSTRAINT [FK_StratumStandObject_Stratum];
END
-- 2.18. Create ContingencyTableCell
IF OBJECT_ID ('dbo.ContingencyTableCell','U') IS NULL
BEGIN
CREATE TABLE [dbo].[ContingencyTableCell](
[CellGuid] [uniqueidentifier] NOT NULL,
[TableGuid] [uniqueidentifier] NOT NULL,
[RowNo] [int] NOT NULL,
[ColumnNo] [int] NOT NULL,
[StratumGuid] [uniqueidentifier] NULL,
[Value] [float] NULL,
CONSTRAINT [PK_ContingencyTableCell] PRIMARY KEY CLUSTERED
(
[CellGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[ContingencyTableCell] WITH CHECK ADD CONSTRAINT [FK_ContingencyTableCell_ContingencyTable] FOREIGN KEY([TableGuid])
REFERENCES [dbo].[ContingencyTable] ([TableGuid])
ON DELETE CASCADE;
ALTER TABLE [dbo].[ContingencyTableCell] CHECK CONSTRAINT [FK_ContingencyTableCell_ContingencyTable];
END
-- 3. Add table TreatmentUnitNeighbour
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TreatmentUnitNeighbour')
BEGIN
CREATE TABLE [dbo].[TreatmentUnitNeighbour](
[TreatmentUnitGUID] [uniqueidentifier] NOT NULL,
[TreatmentUnitNeighbourGUID] [uniqueidentifier] NOT NULL,
[CommonBorderLength] [int] NOT NULL,
CONSTRAINT [PK_TreatmentUnitNeighbour] PRIMARY KEY CLUSTERED
(
[TreatmentUnitGUID] ASC,
[TreatmentUnitNeighbourGUID] 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].[TreatmentUnitNeighbour] WITH CHECK ADD CONSTRAINT [FK_TreatmentUnitNeighbour_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID])
REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID]) ON DELETE CASCADE
ALTER TABLE [dbo].[TreatmentUnitNeighbour] CHECK CONSTRAINT [FK_TreatmentUnitNeighbour_TreatmentUnit]
END
-- 4. Add columns for arithmetic mean height to table InitialState_SpeciesData
TRUNCATE TABLE STANDOBJECTPLOT
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandobjectPlot' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
ALTER TABLE [StandobjectPlot] ADD [StratificationGUID] [uniqueidentifier] NOT NULL
END
-- 5. Add new column for ObjectID to InitialState_TreatmentUnit
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_TreatmentUnit' AND COLUMN_NAME = 'ObjectId')
BEGIN
ALTER TABLE [dbo].[InitialState_TreatmentUnit] ADD
[ObjectId] [int] NULL
END
-- 6. Add columns for arithmetic mean height to table InitialState_SpeciesData
TRUNCATE TABLE Standinventory
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Standinventory' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
ALTER TABLE [Standinventory] ADD [StratificationGUID] [uniqueidentifier] NOT NULL;
END
-- Update version
UPDATE Version SET ApplicationVersion = '0.5.4.0';
PRINT 'Success! Database upgrade to version 0.5.4.0';
END