Difference between revisions of "Heureka database upgrade script 0.5.3.2"

Revision as of 14:07, 14 May 2009


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
@@ Line 1: / Line 1: @@
+[[Heureka database upgrade scripts | Back to scripts]]
+<pre>
 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.3.1' OR ApplicationVersion = '0.5.3.2' ) = 0
 BEGIN
@@ Line 496: / Line 499: @@
 	PRINT 'Success! Database upgrade to version 0.5.3.2';
 END
+</pre>
Difference between revisions of "Heureka database upgrade script 0.5.3.2"

Revision as of 14:07, 14 May 2009

Navigation menu

Search
