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