Peder at 13:51, 14 July 2009

2009-07-14T13:51:56Z

Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.4.3' OR ApplicationVersion = '0.5.1.0' ) = 0 BEGIN 	PRINT 'Wro...

2009-02-04T14:14:16Z

New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.4.3' OR ApplicationVersion = '0.5.1.0' ) = 0 BEGIN PRINT 'Wro...

New page

[[Heureka database upgrade scripts | Back to scripts]]
<pre>
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.4.3' OR ApplicationVersion = '0.5.1.0' ) = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.4.4.3 before running this script.';
SELECT ApplicationVersion FROM VERSION;
END
ELSE
BEGIN

-- 1. Add columns for to InvReferenceunit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceunit' AND COLUMN_NAME = 'TotalBasalArea')
BEGIN
ALTER TABLE InvReferenceunit ADD
[TotalBasalArea] [real] NULL
END

-- Creates all 8 tables related to Stratification:
-- Tables: Stratification, StratificationInterval,Stratum,StratumCondition,StratumConditionDetail,
-- StratumConditionDetailValue,StratumConditionJunction,StratumStandObjectJunction

-- 1. Create Stratification table

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

-- Create StratificationInterval table

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

SET ANSI_PADDING ON;

CREATE TABLE [dbo].[StratificationInterval](
[IntervalId] [int] NOT NULL,
[StratificationGuid] [uniqueidentifier] NOT NULL,
[TableName] [varchar](150) NULL,
[ColumnName] [varchar](150) NULL,
[Size] [int] NULL,
[NumberOfSteps] [int] NULL,
CONSTRAINT [PK_StratificationInterval] PRIMARY KEY CLUSTERED
(
[IntervalId] ASC,
[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'0 is row interval, 1 is column interval' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StratificationInterval', @level2type=N'COLUMN',@level2name=N'IntervalId';

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

ALTER TABLE [dbo].[StratificationInterval] CHECK CONSTRAINT [FK_StratificationInterval_Stratification];

END

-- Create Stratum table

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

SET ANSI_PADDING ON;

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

-- Create StratumCondition

IF OBJECT_ID ('dbo.StratumCondition','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

-- Create StratumConditionDetail

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

SET ANSI_PADDING ON;

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

-- Create StratumConditionDetalValue

IF OBJECT_ID ('dbo.StratumConditionDetailValue','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

-- Create StratumConditionJunction

IF OBJECT_ID ('dbo.StratumConditionJunction','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

-- StratumStandObjectJunction

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

CREATE TABLE [dbo].[StratumStandObjectJunction](
[StandObjectGUID] [uniqueidentifier] NOT NULL,
[StratumGUID] [uniqueidentifier] NOT NULL,
[RepresentativeArea] [float] NULL,
[ProductiveArea] [float] 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.1.0';

PRINT 'Success! Database upgrade to version 0.5.1.0';

END

</pre>

[[Category:Database]]

@@ Line 1: / Line 1: @@
-[[Heureka database upgrade scripts | Back to scripts]]
+[[:Category:Database scripts | Back to scripts]]
 <pre>
 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.4.3' OR ApplicationVersion = '0.5.1.0' ) = 0
@@ Line 313: / Line 313: @@
 </pre>
-[[Category:Database]]
+[[Category:Database scripts]]

Heureka database upgrade script 0.5.1 - Revision history

Peder at 13:51, 14 July 2009

Cawa2: New page: Back to scripts
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.4.3' OR ApplicationVersion = '0.5.1.0' ) = 0 BEGIN PRINT 'Wro...