Heureka database upgrade script 0.5.3.2

From Heureka Wiki
Revision as of 14:05, 14 May 2009 by Hah (talk | contribs) (New page: IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.3.1' OR ApplicationVersion = '0.5.3.2' ) = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.5.3.1 ...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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

-- DROP OLD STRATIFICATION TABLES

-- Deletes all 8 tables related to Stratification in earlier versions: -- Tables: Stratification, StratificationInterval,Stratum,StratumCondition,StratumConditionDetail, -- StratumConditionDetailValue,StratumConditionJunction,StratumStandObjectJunction

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON;

-- 8. 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];

/****** Object: Table [dbo].[StratumStandObjectJunction] Script Date: 05/06/2009 16:21:36 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumStandObjectJunction]') AND type in (N'U')) DROP TABLE [dbo].[StratumStandObjectJunction] ;

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

/****** Object: Table [dbo].[StratumConditionJunction] Script Date: 05/06/2009 16:20:48 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionJunction]') AND type in (N'U')) DROP TABLE [dbo].[StratumConditionJunction] ;


-- 6. Drop StratumConditionDetalValue

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] ;

/****** Object: Table [dbo].[StratumConditionDetailValue] Script Date: 05/06/2009 16:19:34 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionDetailValue]') AND type in (N'U')) DROP TABLE [dbo].[StratumConditionDetailValue] ;

-- 5. 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] ;

/****** Object: Table [dbo].[StratumConditionDetail] Script Date: 05/06/2009 16:18:22 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumConditionDetail]') AND type in (N'U')) DROP TABLE [dbo].[StratumConditionDetail] ;

-- 4. 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 dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_StratumCondition_RowNo]') AND type = 'D') BEGIN ALTER TABLE [dbo].[StratumCondition] DROP CONSTRAINT [DF_StratumCondition_RowNo] END

;

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_StratumCondition_ColumnNo]') AND type = 'D') BEGIN ALTER TABLE [dbo].[StratumCondition] DROP CONSTRAINT [DF_StratumCondition_ColumnNo] END

;

/****** Object: Table [dbo].[StratumCondition] Script Date: 05/06/2009 16:16:39 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratumCondition]') AND type in (N'U')) DROP TABLE [dbo].[StratumCondition] ;

-- 3. 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 dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Stratum_SelectedDepartmentCount]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Stratum] DROP CONSTRAINT [DF_Stratum_SelectedDepartmentCount] END

;

/****** Object: Table [dbo].[Stratum] Script Date: 05/06/2009 16:15:12 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stratum]') AND type in (N'U')) DROP TABLE [dbo].[Stratum] ;

-- 2. Drop StratificationInterval

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StratificationInterval_Stratification]') AND parent_object_id = OBJECT_ID(N'[dbo].[StratificationInterval]')) ALTER TABLE [dbo].[StratificationInterval] DROP CONSTRAINT [FK_StratificationInterval_Stratification] ;

/****** Object: Table [dbo].[StratificationInterval] Script Date: 05/06/2009 16:33:30 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StratificationInterval]') AND type in (N'U')) DROP TABLE [dbo].[StratificationInterval] ;

-- 1. 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 dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Stratification_StandRegisterId]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Stratification] DROP CONSTRAINT [DF_Stratification_StandRegisterId] END

;

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Stratification_ValueColumnIndex]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Stratification] DROP CONSTRAINT [DF_Stratification_ValueColumnIndex] END

;

/****** Object: Table [dbo].[Stratification] Script Date: 05/06/2009 16:12:09 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stratification]') AND type in (N'U')) DROP TABLE [dbo].[Stratification] ;


-- CREATE NEW STRATIFICATION TABLES

-- Creates 10 tables related to Stratification: -- Tables: Stratification, StratificationInterval,Stratum,StratumCondition,StratumConditionDetail, -- StratumConditionDetailValue,StratumConditionJunction,StratumStandObjectJunction -- StrataMatrix, StrataMatrixInterval, StrataMatrixValue

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON;

-- 1. Create Stratification

/****** Object: Table [dbo].[Stratification] Script Date: 05/06/2009 16:35:44 ******/

IF OBJECT_ID ('dbo.Stratification','U') IS NULL BEGIN

CREATE TABLE [dbo].[Stratification]( [StratificationGUID] [uniqueidentifier] NOT NULL, [Description] [varchar](50) NULL, [Date] [datetime] NULL, [StandRegisterId] [int] NULL, [ValueColumnIndex] [int] NULL, [DepartmentCount] [int] NULL, [MaxAreaProportion] [int] NULL, [Frozen] [bit] NULL, [P1] [float] NULL, [P3] [float] NULL, [AvgSampleTreesOnPlot] [int] NULL, [Finished] [bit] NULL, CONSTRAINT [PK_Stratification] PRIMARY KEY CLUSTERED ( [StratificationGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];

SET ANSI_PADDING OFF;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name or short description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratification', @level2type=N'COLUMN',@level2name=N'Description';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date when stratification was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratification', @level2type=N'COLUMN',@level2name=N'Date';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id for standregister that this stratification works with' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratification', @level2type=N'COLUMN',@level2name=N'StandRegisterId';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The index of the value that will show in stratification matrix. 0 = Producitve Area, 1= No of departments, 2 = Biggest department area' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratification', @level2type=N'COLUMN',@level2name=N'ValueColumnIndex';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If frozen, (true or 1) then the Stratification cannot be altered' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratification', @level2type=N'COLUMN',@level2name=N'Frozen';

ALTER TABLE [dbo].[Stratification] WITH NOCHECK ADD CONSTRAINT [FK_Stratification_StandRegister] FOREIGN KEY([StandRegisterId]) REFERENCES [dbo].[StandRegister] ([StandRegisterId]);

ALTER TABLE [dbo].[Stratification] NOCHECK CONSTRAINT [FK_Stratification_StandRegister];

ALTER TABLE [dbo].[Stratification] ADD CONSTRAINT [DF_Stratification_StandRegisterId] DEFAULT ((-1)) FOR [StandRegisterId];

ALTER TABLE [dbo].[Stratification] ADD CONSTRAINT [DF_Stratification_ValueColumnIndex] DEFAULT ((0)) FOR [ValueColumnIndex];

END

-- 2. CREATE StrataMatrix


/****** Object: Table [dbo].[StrataMatrix] Script Date: 05/06/2009 16:39:36 ******/

IF OBJECT_ID ('dbo.[StrataMatrix]','U') IS NULL BEGIN

CREATE TABLE [dbo].[StrataMatrix]( [MatrixGuid] [uniqueidentifier] NOT NULL, [Description] [varchar](150) NULL, [StratificationGuid] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_StrataMatrix] PRIMARY KEY CLUSTERED ( [MatrixGuid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];

SET ANSI_PADDING OFF;

ALTER TABLE [dbo].[StrataMatrix] WITH CHECK ADD CONSTRAINT [FK_StrataMatrix_Stratification] FOREIGN KEY([StratificationGuid]) REFERENCES [dbo].[Stratification] ([StratificationGUID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StrataMatrix] CHECK CONSTRAINT [FK_StrataMatrix_Stratification];

END

-- 3. CREATE StrataMatrixInterval

IF OBJECT_ID ('[dbo].[StrataMatrixInterval]','U') IS NULL BEGIN

CREATE TABLE [dbo].[StrataMatrixInterval]( [IntervalGuid] [uniqueidentifier] NOT NULL, [RowInterval] [bit] NOT NULL, [TableName] [varchar](150) NULL, [ColumnName] [varchar](150) NULL, [Size] [float] NULL, [NumberOfSteps] [int] NULL, [StrataMatrixGuid] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_StratificationInterval] PRIMARY KEY CLUSTERED ( [IntervalGuid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];

SET ANSI_PADDING OFF;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 is row interval, 1 is column interval' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StrataMatrixInterval', @level2type=N'COLUMN',@level2name=N'RowInterval';

ALTER TABLE [dbo].[StrataMatrixInterval] WITH CHECK ADD CONSTRAINT [FK_StrataMatrixInterval_StrataMatrix] FOREIGN KEY([StrataMatrixGuid]) REFERENCES [dbo].[StrataMatrix] ([MatrixGuid]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StrataMatrixInterval] CHECK CONSTRAINT [FK_StrataMatrixInterval_StrataMatrix];

END

-- 4. CREATE StrataMatrixValue

IF OBJECT_ID('[dbo].[StrataMatrixValue]','U') IS NULL BEGIN

CREATE TABLE [dbo].[StrataMatrixValue]( [RowIndex] [int] NOT NULL, [ColumnIndex] [int] NOT NULL, [MatrixGuid] [uniqueidentifier] NOT NULL, [MatrixValue] [float] NULL, CONSTRAINT [PK_StrataMatrixValue] PRIMARY KEY CLUSTERED ( [RowIndex] ASC, [ColumnIndex] ASC, [MatrixGuid] 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].[StrataMatrixValue] WITH CHECK ADD CONSTRAINT [FK_StrataMatrixValue_StrataMatrix] FOREIGN KEY([MatrixGuid]) REFERENCES [dbo].[StrataMatrix] ([MatrixGuid]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StrataMatrixValue] CHECK CONSTRAINT [FK_StrataMatrixValue_StrataMatrix];

END

-- 5. CREATE Stratum

IF OBJECT_ID('dbo.[Stratum]','U') IS NULL BEGIN

CREATE TABLE [dbo].[Stratum]( [StratumGUID] [uniqueidentifier] NOT NULL, [Description] [varchar](200) NULL, [StratumLevel] [int] NOT NULL, [Color] [int] NULL, [SelectedDepartmentCount] [int] NULL, [ParentStratumGUID] [uniqueidentifier] NULL, [StratificationGUID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_Strata] PRIMARY KEY CLUSTERED ( [StratumGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];

SET ANSI_PADDING OFF;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Level in hierarchy' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum', @level2type=N'COLUMN',@level2name=N'StratumLevel';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The number of departments that area ;ing to be selectd from this Stratum' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum', @level2type=N'COLUMN',@level2name=N'SelectedDepartmentCount';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A Stratum can contain 1-M Stratums' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum', @level2type=N'COLUMN',@level2name=N'ParentStratumGUID';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'a Stratum belong to a Stratification' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum', @level2type=N'COLUMN',@level2name=N'StratificationGUID';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A Stratum is a container for trees in a standregister' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum';

ALTER TABLE [dbo].[Stratum] WITH NOCHECK ADD CONSTRAINT [FK_Strata_Strata] FOREIGN KEY([ParentStratumGUID]) REFERENCES [dbo].[Stratum] ([StratumGUID]);

ALTER TABLE [dbo].[Stratum] CHECK CONSTRAINT [FK_Strata_Strata];

ALTER TABLE [dbo].[Stratum] WITH CHECK ADD CONSTRAINT [FK_Strata_Stratification] FOREIGN KEY([StratificationGUID]) REFERENCES [dbo].[Stratification] ([StratificationGUID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[Stratum] CHECK CONSTRAINT [FK_Strata_Stratification];

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 Strata belong to 1 Stratification. 1 Stratification has 1-M Strata' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stratum', @level2type=N'CONSTRAINT',@level2name=N'FK_Strata_Stratification';

ALTER TABLE [dbo].[Stratum] ADD CONSTRAINT [DF_Stratum_SelectedDepartmentCount] DEFAULT ((0)) FOR [SelectedDepartmentCount];

END

-- 6. CREATE StratumCondition

IF OBJECT_ID(,'U') IS NULL BEGIN CREATE TABLE [dbo].[StratumCondition]( [StratumConditionGuid] [uniqueidentifier] NOT NULL, [RowNo] [int] NULL, [ColumnNo] [int] NULL, [StratificationGuid] [uniqueidentifier] NULL, CONSTRAINT [PK_StratumCondition] PRIMARY KEY CLUSTERED ( [StratumConditionGuid] 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'Row no in matrix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumCondition', @level2type=N'COLUMN',@level2name=N'RowNo';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Column no.position in matrix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumCondition', @level2type=N'COLUMN',@level2name=N'ColumnNo' ;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains criteria specification for a tree to fall into a Stratum. E.g. a StratumCondition can be: Volume 200-400 m3 AND Age 60-70 year' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumCondition';

ALTER TABLE [dbo].[StratumCondition] WITH CHECK ADD CONSTRAINT [FK_StratumCondition_Stratification] FOREIGN KEY([StratificationGuid]) REFERENCES [dbo].[Stratification] ([StratificationGUID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StratumCondition] CHECK CONSTRAINT [FK_StratumCondition_Stratification];

ALTER TABLE [dbo].[StratumCondition] ADD CONSTRAINT [DF_StratumCondition_RowNo] DEFAULT ((-1)) FOR [RowNo];

ALTER TABLE [dbo].[StratumCondition] ADD CONSTRAINT [DF_StratumCondition_ColumnNo] DEFAULT ((-1)) FOR [ColumnNo];

END

-- 7. CREATE StratumConditionDetail IF OBJECT_ID(,'U') IS NULL BEGIN

CREATE TABLE [dbo].[StratumConditionDetail]( [StratumConditionDetailGUID] [uniqueidentifier] NOT NULL, [StratumConditionGuid] [uniqueidentifier] NOT NULL, [Variable] [varchar](150) NULL, [Operator] [varchar](50) NULL, [LeftValue] [float] NULL, [RightValue] [float] NULL, [StringValue] [varchar](150) NULL, CONSTRAINT [PK_StratumConditionDetail_1] PRIMARY KEY CLUSTERED ( [StratumConditionDetailGUID] ASC, [StratumConditionGuid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY];

SET ANSI_PADDING OFF;

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A Condition: E.g. Age between 60-70' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumConditionDetail';

ALTER TABLE [dbo].[StratumConditionDetail] WITH CHECK ADD CONSTRAINT [FK_StratumConditionDetail_StratumCondition] FOREIGN KEY([StratumConditionGuid]) REFERENCES [dbo].[StratumCondition] ([StratumConditionGuid]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StratumConditionDetail] CHECK CONSTRAINT [FK_StratumConditionDetail_StratumCondition];

END

-- 8. CREATE StratumConditionDetailValue IF OBJECT_ID(,'U') IS NULL BEGIN CREATE TABLE [dbo].[StratumConditionDetailValue]( [StratumConditionDetailGuid] [uniqueidentifier] NOT NULL, [DoubleValue] [float] NOT NULL, [StratumConditionGuid] [uniqueidentifier] NULL, CONSTRAINT [PK_StratumConditionDetailValue] PRIMARY KEY CLUSTERED ( [StratumConditionDetailGuid] ASC, [DoubleValue] 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'Contains a list of values for a StratumConditionDetailRow' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumConditionDetailValue', @level2type=N'COLUMN',@level2name=N'StratumConditionDetailGuid';

ALTER TABLE [dbo].[StratumConditionDetailValue] WITH CHECK ADD CONSTRAINT [FK_StratumConditionDetailValue_StratumConditionDetailValue] FOREIGN KEY([StratumConditionDetailGuid], [StratumConditionGuid]) REFERENCES [dbo].[StratumConditionDetail] ([StratumConditionDetailGUID], [StratumConditionGuid]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StratumConditionDetailValue] CHECK CONSTRAINT [FK_StratumConditionDetailValue_StratumConditionDetailValue]; END

-- 9. CREATE StratumConditionJunction IF OBJECT_ID(,'U') IS NULL BEGIN

CREATE TABLE [dbo].[StratumConditionJunction]( [StratumGuid] [uniqueidentifier] NOT NULL, [StratumConditionGuid] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_StratumConditionJunction] PRIMARY KEY CLUSTERED ( [StratumGuid] ASC, [StratumConditionGuid] 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'Junction table between Stratum and StratumCondition tables' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratumConditionJunction';

ALTER TABLE [dbo].[StratumConditionJunction] WITH CHECK ADD CONSTRAINT [FK_StratumConditionJunction_Stratum] FOREIGN KEY([StratumGuid]) REFERENCES [dbo].[Stratum] ([StratumGUID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StratumConditionJunction] CHECK CONSTRAINT [FK_StratumConditionJunction_Stratum];

ALTER TABLE [dbo].[StratumConditionJunction] WITH CHECK ADD CONSTRAINT [FK_StratumConditionJunction_StratumCondition] FOREIGN KEY([StratumConditionGuid]) REFERENCES [dbo].[StratumCondition] ([StratumConditionGuid]);

ALTER TABLE [dbo].[StratumConditionJunction] CHECK CONSTRAINT [FK_StratumConditionJunction_StratumCondition];

END -- 10. CREATE StratumStandobjectJunction IF OBJECT_ID(,'U') IS NULL BEGIN CREATE TABLE [dbo].[StratumStandObjectJunction]( [StandObjectGUID] [uniqueidentifier] NOT NULL, [StratumGUID] [uniqueidentifier] NOT NULL, [RepresentativeArea] [float] NULL, [ProductiveArea] [float] NULL, [PlotCount] [int] NULL, [PlotRadius] [int] NULL, [P2] [int] NULL, CONSTRAINT [PK_StratumStandObjectJunction] PRIMARY KEY CLUSTERED ( [StandObjectGUID] ASC, [StratumGUID] 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].[StratumStandObjectJunction] WITH CHECK ADD CONSTRAINT [FK_StratumStandObjectJunction_StandObject] FOREIGN KEY([StandObjectGUID]) REFERENCES [dbo].[StandObject] ([StandObjectGuid]);

ALTER TABLE [dbo].[StratumStandObjectJunction] CHECK CONSTRAINT [FK_StratumStandObjectJunction_StandObject];

ALTER TABLE [dbo].[StratumStandObjectJunction] WITH CHECK ADD CONSTRAINT [FK_StratumStandObjectJunction_Stratum] FOREIGN KEY([StratumGUID]) REFERENCES [dbo].[Stratum] ([StratumGUID]) ON DELETE CASCADE;

ALTER TABLE [dbo].[StratumStandObjectJunction] CHECK CONSTRAINT [FK_StratumStandObjectJunction_Stratum]; END


-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.3.2'; PRINT 'Success! Database upgrade to version 0.5.3.2'; END

Retrieved from "https://www.heurekaslu.se/w/index.php?title=Heureka_database_upgrade_script_0.5.3.2&oldid=1929"