Heureka Wiki - User contributions [en]

Heureka database upgrade script 1.0.2.1

2010-02-12T15:19:18Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.2.0') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade...

[[:Category:Database scripts | Back to scripts]]

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

-- 1. Drop column RejuvenationProposal

-- 1.1 Drop column RejuvenationProposal from table InvReferenceUnitPlant
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'RejuvenationProposal')
BEGIN
ALTER TABLE InvReferenceUnitPlant DROP COLUMN RejuvenationProposal
END

-- 1.2 Remove rows containing RejuvenationProposal for ReferenceUnitPlant from InventoryTemplate
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InventoryTemplate')
BEGIN
DELETE FROM InventoryTemplate WHERE TableName = 'ReferenceUnitPlant' AND ColumnName = 'RejuvenationProposal'
END

-- 2. Add check constraint for column VegitationTypeCode in table ReferenceUnit
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = 'ck_ReferenceUnit_VegetationTypeCode')) AND
(EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'VegetationTypeCode'))
BEGIN
ALTER TABLE [dbo].[ReferenceUnit] WITH CHECK ADD CONSTRAINT [ck_ReferenceUnit_VegetationTypeCode] CHECK (([VegetationTypeCode]>=(0) AND [VegetationTypeCode]<=(18)))
ALTER TABLE [dbo].[ReferenceUnit] CHECK CONSTRAINT [ck_ReferenceUnit_VegetationTypeCode]

END

-- Update Version Table

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

</pre>
[[Category:Database scripts]]

Heureka database upgrade script 1.0.1.0

2009-11-13T11:43:36Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.1.0.0') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade ...

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

-- 1. Add Site Index columns for InitialState_ForestData
--(added one at a time to make sure both columns are added, one of the columns has existed before, but may have been removed)

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

-- 2. Add column for ParentStandObjectGuid
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'ParentStandObjectGuid')
BEGIN
ALTER TABLE StandObject ADD
ParentStandObjectGuid uniqueidentifier NULL
END

-- 3. Drop table GIS_StandPolygons
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GIS_StandPolygons')
BEGIN
ALTER TABLE GIS_StandPolygons
DROP CONSTRAINT FK_GIS_StandPolygons_TreatmentUnit

DROP TABLE GIS_StandPolygons
END

-- 4. Add DataOrigin column in table ReferenceUnit

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

-- 5. Add RegenerationSpeciesGroup column to table TreatmentHistory

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

-- 6. Update table structure for stand inventory

-- 1.1 Drop Constraints

ALTER TABLE [dbo].[StandInventory] DROP CONSTRAINT [FK_StandInventory_StandObject];
ALTER TABLE [dbo].[StandObjectPlot] DROP CONSTRAINT [FK_StandObjectPlot_StandObject];
ALTER TABLE [dbo].[StandInventory] DROP CONSTRAINT [PK_StandInventory];
ALTER TABLE [dbo].[StandObjectPlot] DROP CONSTRAINT [PK_StandObjectPlot];
ALTER TABLE [dbo].[Inventory] DROP CONSTRAINT [FK_Inventory_inventoryState];

-- 1.2 Add new column to inventory table

ALTER TABLE [dbo].[Inventory] ADD [PlotFileName] [varchar] (255) COLLATE Finnish_Swedish_CI_AS NULL;

-- 1.3 Drop inventory state table (no longer needed)

DROP TABLE [dbo].[InventoryState];

-- 1.4 Create new InventoryStandObject table (replaces StandInventory)

CREATE TABLE [dbo].[InventoryStandObject]
(
[InventoryStandObjectGuid] [uniqueidentifier] NOT NULL,
[StratumGuid] [uniqueidentifier] NOT NULL,
[StandObjectGuid] [uniqueidentifier] NOT NULL,
[InventoryGUID] [uniqueidentifier] NOT NULL
);

INSERT INTO [dbo].[InventoryStandObject]([InventoryStandObjectGuid], [StratumGuid], [StandObjectGuid], [InventoryGUID])
SELECT newid(), [dbo].[StratumStandObject].[StratumGuid], [StandInventory].[StandObjectGuid], [StandInventory].[InventoryGUID]
FROM [dbo].[StandInventory]
INNER JOIN [Inventory] ON [StandInventory].[InventoryGUID] = [Inventory].[InventoryGUID]
INNER JOIN [StratificationDomain] ON [Inventory].[StratificationGuid] = [StratificationDomain].[StratificationGuid]
INNER JOIN [Stratum] ON [StratificationDomain].[StratificationDomainGuid] = [Stratum].[StratificationDomainGuid]
INNER JOIN [StratumStandObject] ON [Stratum].[StratumGuid] = [StratumStandObject].[StratumGuid] and [StratumStandObject].[StandObjectGuid] = [StandInventory].[StandObjectGuid];

DROP TABLE [dbo].[StandInventory];

ALTER TABLE [dbo].[InventoryStandObject] ADD CONSTRAINT [PK_InventoryStandObject] PRIMARY KEY CLUSTERED ([InventoryStandObjectGuid]);

-- 1.5 Create new InventoryPlot table (replaces StandObjectPlot)

CREATE TABLE [dbo].[InventoryPlot]
(
[InventoryPlotGUID] [uniqueidentifier] NOT NULL,
[InventoryStandObjectGUID] [uniqueidentifier] NOT NULL,
[PlotId] [int] NOT NULL,
[GpsNorth] [float] NULL,
[GpsEast] [float] NULL,
[Climate] [int] NULL,
[Region] [int] NULL,
[DistancetoCoast] [int] NULL,
[County] [int] NULL,
[Latitude] [real] NULL,
[Altitude] [real] NULL
);

INSERT INTO [dbo].[InventoryPlot]([InventoryPlotGuid], [InventoryStandObjectGUID], [PlotId], [GpsNorth], [GpsEast], [Climate], [Region], [DistancetoCoast], [County], [Latitude], [Altitude])

SELECT NEWID(), [InventoryStandObject].[InventoryStandObjectGUID], [PlotId], [GpsNorth], [GpsEast], [Climate], [Region], [DistancetoCoast], [County], [Latitude], [Altitude]
FROM [dbo].[StandObjectPlot]
INNER JOIN [dbo].[InventoryStandObject] ON
[dbo].[InventoryStandObject].[StandObjectGuid] = [StandObjectPlot].[StandObjectGuid] AND
[dbo].[InventoryStandObject].[InventoryGUID] = [StandObjectPlot].[InventoryGUID];

DROP TABLE [dbo].[StandObjectPlot];

ALTER TABLE [dbo].[InventoryPlot] ADD CONSTRAINT [PK_InventoryPlot] PRIMARY KEY CLUSTERED ([InventoryPlotGUID]);

-- 1.6 Add constraints

ALTER TABLE [dbo].[Inventory] ADD CONSTRAINT [FK_Inventory_Stratification] FOREIGN KEY ([StratificationGUID]) REFERENCES [dbo].[Stratification] ([StratificationGUID]);
ALTER TABLE [dbo].[InventoryPlot] ADD CONSTRAINT [FK_InventoryPlot_InventoryStandObject] FOREIGN KEY ([InventoryStandObjectGUID]) REFERENCES [dbo].[InventoryStandObject] ([InventoryStandObjectGuid]);
ALTER TABLE [dbo].[InventoryStandObject] ADD
CONSTRAINT [FK_InventoryStandObject_StratumStandObject] FOREIGN KEY ([StandObjectGuid], [StratumGuid]) REFERENCES [dbo].[StratumStandObject] ([StandObjectGuid], [StratumGuid]),
CONSTRAINT [FK_InventoryStandObject_Inventory] FOREIGN KEY ([InventoryGUID]) REFERENCES [dbo].[Inventory] ([InventoryGUID]);

-- 2. Drop Existing tables with field inventory data

DROP TABLE [dbo].[InvTreatmentHistory];
DROP TABLE [dbo].[InvSitePosition];
DROP TABLE [dbo].[InvTreeDead];
DROP TABLE [dbo].[InvTreeSample];
DROP TABLE [dbo].[InvTree];
DROP TABLE [dbo].[InvReferenceUnitTree];
DROP TABLE [dbo].[InvPlantBi];
DROP TABLE [dbo].[InvPlantMain];
DROP TABLE [dbo].[InvReferenceUnitPlant];
DROP TABLE [dbo].[InvReferenceUnitSiteData];
DROP TABLE [dbo].[InvReferenceUnit];
DROP TABLE [dbo].[InvCorrectionFactor];
DROP TABLE [dbo].[InvTreatmentUnit];

-- 3. Create field inventory tables

-- 3.1 InvTreatmentUnit

CREATE TABLE [dbo].[InvTreatmentUnit](
[InventoryStandObjectGUID] [uniqueidentifier] NOT NULL,
[InventoryGUID] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](max) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[AreaFactor] [real] NULL,
[InventoryYear] [int] NOT NULL,
[Area] [real] NOT NULL,
[Spacing] [int] NOT NULL,
[SurveyorCode] [int] NOT NULL,
[P1] [float] NOT NULL,
[P2] [float] NOT NULL,
[P3] [float] NOT NULL,
[PlotRadiusPlant] [int] NOT NULL,
[PlotRadiusTree] [int] NOT NULL,
[Checked] [int] NULL,
[SampleDesign] [varchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[Created] [datetime] NOT NULL,
[TreatmentUnitParentGUID] [uniqueidentifier] NULL,
[SplitId] [int] NULL,
[ManagementCode] [int] NOT NULL,
[MaturityClassCode] [int] NULL,
[Mixed] [int] NOT NULL,
CONSTRAINT [PK_InvTreatmentUnit] PRIMARY KEY CLUSTERED
(
[InventoryStandObjectGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ__InvTreatmentUnit__2A76FF3E] UNIQUE NONCLUSTERED
(
[InventoryStandObjectGUID] ASC,
[InventoryGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[InvTreatmentUnit] WITH CHECK ADD CONSTRAINT [FK_InvTreatmentUnit_Inventory1] FOREIGN KEY([InventoryGUID])
REFERENCES [dbo].[Inventory] ([InventoryGUID]);
ALTER TABLE [dbo].[InvTreatmentUnit] CHECK CONSTRAINT [FK_InvTreatmentUnit_Inventory1];

-- 3.2. InvCorrectionFactor

CREATE TABLE [dbo].[InvCorrectionFactor](
[InventoryStandObjectGUID] [uniqueidentifier] NOT NULL,
[CorrectionType] [int] NOT NULL,
[SpeciesGroup] [int] NOT NULL,
[CorrectionFactor] [float] NOT NULL,
CONSTRAINT [PK_InvCorrectionFactor] PRIMARY KEY CLUSTERED
(
[InventoryStandObjectGUID] ASC,
[CorrectionType] ASC,
[SpeciesGroup] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvCorrectionFactor] WITH CHECK ADD CONSTRAINT [FK_InvCorrectionFactor_TreatmentUnit] FOREIGN KEY([InventoryStandObjectGUID])
REFERENCES [dbo].[InvTreatmentUnit] ([InventoryStandObjectGUID]);
ALTER TABLE [dbo].[InvCorrectionFactor] CHECK CONSTRAINT [FK_InvCorrectionFactor_TreatmentUnit];

-- 3.3. InvReferenceUnit

CREATE TABLE [dbo].[InvReferenceUnit](
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[InventoryStandObjectGUID] [uniqueidentifier] NOT NULL,
[PlotNr] [int] NOT NULL,
[InventoryYear] [int] NOT NULL,
[Altitude] [int] NULL,
[ClimateCode] [int] NULL,
[ImpedimentPercentage] [real] NULL,
[GPSCordEast] [real] NULL,
[GPSCordNorth] [real] NULL,
[TCordEast] [real] NULL,
[TCordNorth] [real] NULL,
[HPCordEast] [float] NULL,
[HPCordNorth] [float] NULL,
[Checked] [int] NULL,
[SiteIndex] [real] NULL,
[TotalBasalArea] [real] NULL,
[PlotType] [int] NOT NULL,
[PlotNrOriginal] [int] NULL,
[TreatmentUnitOriginalGUID] [uniqueidentifier] NULL,
CONSTRAINT [PK_InvReferenceUnit] PRIMARY KEY CLUSTERED
(
[ReferenceUnitGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvReferenceUnit] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnit_TreatmentUnit] FOREIGN KEY([InventoryStandObjectGUID])
REFERENCES [dbo].[InvTreatmentUnit] ([InventoryStandObjectGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvReferenceUnit] CHECK CONSTRAINT [FK_ReferenceUnit_TreatmentUnit];

-- 3.4 InvReferenceUnitSiteData

CREATE TABLE [dbo].[InvReferenceUnitSiteData](
[ReferenceUnitSiteDataGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[Peat] [bit] NULL,
[SoilMoistCode] [int] NULL,
[LaterW] [int] NULL,
[TextureCode] [int] NULL,
[SoilDepth] [int] NULL,
[GroundLayerCode] [int] NULL,
[VegitationTypeCode] [int] NULL,
[Ditch] [bit] NULL,
[Direction] [int] NULL,
[DominantSpeciesCode] [int] NULL,
[GroundCondition] [int] NULL,
[Boulder] [int] NULL,
[Slope] [int] NULL,
[SmallTreesP] [int] NULL,
[SmallTreesS] [int] NULL,
[SmallTreesB] [int] NULL,
[SmallTreesOther] [int] NULL,
[DamageCause] [int] NULL,
[DamagePercentage] [int] NULL,
[Split] [bit] NULL,
[PositionTypeCode1] [int] NULL,
[PositionTypeCode2] [int] NULL,
CONSTRAINT [PK_InvReferenceUnitSiteData] PRIMARY KEY CLUSTERED
(
[ReferenceUnitSiteDataGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvReferenceUnitSiteData] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitSiteData_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvReferenceUnitSiteData] CHECK CONSTRAINT [FK_ReferenceUnitSiteData_ReferenceUnit];

-- 3.5 InvReferenceUnitPlant

CREATE TABLE [dbo].[InvReferenceUnitPlant](
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[ImpedimentPercentage] [real] NULL,
[RejuvenationProposal] [int] NULL,
[CleaningProposal] [int] NULL,
[PlotRadiusPlant] [real] NULL,
[MeanAge] [real] NULL,
[LastCleaning] [int] NULL,
[RegenerationType] [int] NULL,
[RegenerationProposal] [int] NULL,
[TreatmentPriority] [int] NULL,
CONSTRAINT [PK_InvReferenceUnitPlant] PRIMARY KEY CLUSTERED
(
[ReferenceUnitPlantGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvReferenceUnitPlant] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitPlant_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvReferenceUnitPlant] CHECK CONSTRAINT [FK_ReferenceUnitPlant_ReferenceUnit];

-- 3.6 InvPlantMain

CREATE TABLE [dbo].[InvPlantMain](
[PlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NOT NULL,
[PlantId] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Height] [real] NULL,
[Stems] [real] NOT NULL,
CONSTRAINT [PK_InvPlantMain] PRIMARY KEY CLUSTERED
(
[PlantGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvPlantMain] WITH CHECK ADD CONSTRAINT [FK_PlantMain_ReferenceUnitPlant] FOREIGN KEY([ReferenceUnitPlantGUID])
REFERENCES [dbo].[InvReferenceUnitPlant] ([ReferenceUnitPlantGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvPlantMain] CHECK CONSTRAINT [FK_PlantMain_ReferenceUnitPlant];

-- 3.7 InvPlantBi

CREATE TABLE [dbo].[InvPlantBi](
[PlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[PlantId] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Height] [real] NULL,
[Stems] [real] NOT NULL,
CONSTRAINT [PK_InvPlantBi] PRIMARY KEY CLUSTERED
(
[PlantGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[InvPlantBi] WITH CHECK ADD CONSTRAINT [FK_PlantBi_ReferenceUnitPlan] FOREIGN KEY([ReferenceUnitPlantGUID])
REFERENCES [dbo].[InvReferenceUnitPlant] ([ReferenceUnitPlantGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvPlantBi] CHECK CONSTRAINT [FK_PlantBi_ReferenceUnitPlan];

-- 3.8 InvReferenceUnitTree

CREATE TABLE [dbo].[InvReferenceUnitTree](
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[EvenAgedCode] [int] NOT NULL,
[MeanAgeBreastHeight] [real] NULL,
[QualityPine] [int] NULL,
[QualitySpruce] [int] NULL,
[PlotRadiusTree] [real] NULL,
[ImpedimentPercentage] [real] NULL,
[LastThinning] [int] NULL,
[LastCleaning] [int] NULL,
CONSTRAINT [PK_InvReferenceUnitTree] PRIMARY KEY CLUSTERED
(
[ReferenceUnitTreeGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[InvReferenceUnitTree] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitTree_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvReferenceUnitTree] CHECK CONSTRAINT [FK_ReferenceUnitTree_ReferenceUnit];

-- 3.9 InvTree

CREATE TABLE [dbo].[InvTree](
[TreeGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[Distance] [real] NULL,
[IsSampleTree] [int] NOT NULL,
[TreeId] [int] NOT NULL,
[TypeCode] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[DistA] [real] NULL,
[DistB] [real] NULL,
[DistC] [real] NULL,
[Angel] [real] NULL,
[XCoordinate] [real] NULL,
[YCoordinate] [real] NULL,
[Angle] [real] NULL,
CONSTRAINT [PK_InvTree] PRIMARY KEY CLUSTERED
(
[TreeGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[InvTree] WITH CHECK ADD CONSTRAINT [FK_Tree_ReferenceUnitTree] FOREIGN KEY([ReferenceUnitTreeGUID])
REFERENCES [dbo].[InvReferenceUnitTree] ([ReferenceUnitTreeGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvTree] CHECK CONSTRAINT [FK_Tree_ReferenceUnitTree];

-- 3.10 InvTreeSample

CREATE TABLE [dbo].[InvTreeSample](
[TreeSampleGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[IsSampleTree] [int] NOT NULL,
[TreeId] [int] NOT NULL,
[TypeCode] [int] NOT NULL,
[SCode] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[Height] [real] NULL,
[Age] [int] NULL,
[Quality] [int] NULL,
[Damage] [int] NULL,
[DistA] [real] NULL,
[DistB] [real] NULL,
[DistC] [real] NULL,
[Angel] [real] NULL,
[XCoordinate] [real] NULL,
[YCoordinate] [real] NULL,
[Distance] [real] NULL,
[Angle] [real] NULL,
[Checked] [int] NOT NULL,
CONSTRAINT [PK_InvTreeSample] PRIMARY KEY CLUSTERED
(
[TreeSampleGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvTreeSample] WITH CHECK ADD CONSTRAINT [FK_TreeSample_ReferenceUnitTree] FOREIGN KEY([ReferenceUnitTreeGUID])
REFERENCES [dbo].[InvReferenceUnitTree] ([ReferenceUnitTreeGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvTreeSample] CHECK CONSTRAINT [FK_TreeSample_ReferenceUnitTree];

-- 3.11 InvTreeDead

CREATE TABLE [dbo].[InvTreeDead](
[TreeDeadGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[TreeId] [int] NOT NULL,
[Position] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[DecayClass] [int] NOT NULL,
[HeightToBreak] [int] NULL,
[PartLength1] [int] NULL,
[TopDiameter1] [int] NULL,
[PartLength2] [int] NULL,
[TopDiameter2] [int] NULL,
[PartLength3] [int] NULL,
[TopDiameter3] [int] NULL,
[PartLength4] [int] NULL,
[TopDiameter4] [int] NULL,
[PileLength] [int] NULL,
[Stems] [int] NULL,
CONSTRAINT [PK_InvTreeDead] PRIMARY KEY CLUSTERED
(
[TreeDeadGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvTreeDead] WITH CHECK ADD CONSTRAINT [FK_TreeDead_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvTreeDead] CHECK CONSTRAINT [FK_TreeDead_ReferenceUnit]

-- 3.12 InvSitePosition

CREATE TABLE [dbo].[InvSitePosition](
[SitePositionGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[Direction] [int] NULL,
[PositionTypeCode] [int] NULL,
[MaturityClassCode] [int] NULL,
CONSTRAINT [PK_InvSitePosition] PRIMARY KEY CLUSTERED
(
[SitePositionGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[InvSitePosition] WITH CHECK ADD CONSTRAINT [FK_SitePosition_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE [dbo].[InvSitePosition] CHECK CONSTRAINT [FK_SitePosition_ReferenceUnit];

-- 3.13 InvTreatmentHistory

CREATE TABLE [dbo].[InvTreatmentHistory](
[TreatmentHistoryGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[TreatmentTypeCode] [int] NOT NULL,
[YearsSinceTreatment] [int] NOT NULL,
CONSTRAINT [PK_InvTreatmentHistory] PRIMARY KEY CLUSTERED
(
[TreatmentHistoryGUID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE [dbo].[InvTreatmentHistory] WITH CHECK ADD CONSTRAINT [FK_TreatmentHistory_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID])
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE [dbo].[InvTreatmentHistory] CHECK CONSTRAINT [FK_TreatmentHistory_ReferenceUnit];

-- Update Version Table

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

</pre>
[[Category:Database scripts]]

Heureka database upgrade script 0.8.0.0

2009-09-21T13:20:00Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.7.0.0' OR ApplicationVersion = '0.8.0.0') = 0 BEGIN PRINT 'Wrong Vers...

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

-- 1. Create table for Climate Scenarios
IF OBJECT_ID ('dbo.Scenario','U') IS NULL
BEGIN
CREATE TABLE [dbo].[Scenario](
[ScenarioGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Scenario_ScenarionGuid] DEFAULT (newid()),
[Description] [nvarchar](50) NOT NULL,
[CellSize] [int] NOT NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
CONSTRAINT [PK_Scenario] PRIMARY KEY CLUSTERED
(
[ScenarioGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Scenario_Description] ON [dbo].[Scenario]
(
[Description] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

IF OBJECT_ID ('dbo.ScenarioCell','U') IS NULL
BEGIN
CREATE TABLE [dbo].[ScenarioCell](
[ScenarioCellGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ScenarioCell_ScenarioCellGuid] DEFAULT (newid()),
[ScenarioGuid] [uniqueidentifier] NOT NULL,
[CellRow] [int] NULL,
[CellColumn] [int] NULL,
[TSChangeFactor] [float] NULL,
CONSTRAINT [PK_ScenarioCell] PRIMARY KEY CLUSTERED
(
[ScenarioCellGuid] 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].[ScenarioCell] WITH CHECK ADD CONSTRAINT [FK_ScenarioCell_Scenario] FOREIGN KEY([ScenarioGuid])
REFERENCES [dbo].[Scenario] ([ScenarioGuid])
ON DELETE CASCADE
ALTER TABLE [dbo].[ScenarioCell] CHECK CONSTRAINT [FK_ScenarioCell_Scenario]
END

IF OBJECT_ID ('dbo.ScenarioBiomassEffect','U') IS NULL
BEGIN
CREATE TABLE [dbo].[ScenarioBiomassEffect](
[ScenarioBiomassEffectGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ScenarioParameter_ScenarioParameterGuid] DEFAULT (newid()),
[ScenarioCellGuid] [uniqueidentifier] NOT NULL,
[Species] [int] NOT NULL,
[SoilMoisture] [int] NOT NULL,
[a] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_a] DEFAULT ((0)),
[b] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_b] DEFAULT ((0)),
[c] [float] NOT NULL CONSTRAINT [DF_ScenarioParameter_c] DEFAULT ((0)),
CONSTRAINT [PK_ScenarioParameter] PRIMARY KEY CLUSTERED
(
[ScenarioBiomassEffectGuid] 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'1=Pine, 2=Spruce, 3=Other' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScenarioBiomassEffect', @level2type=N'COLUMN',@level2name=N'Species'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = Dry, 2 = Mesic, 3 = Moist' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ScenarioBiomassEffect', @level2type=N'COLUMN',@level2name=N'SoilMoisture'
ALTER TABLE [dbo].[ScenarioBiomassEffect] WITH CHECK ADD CONSTRAINT [FK_ScenarioParameter_ScenarioCell] FOREIGN KEY([ScenarioCellGuid])
REFERENCES [dbo].[ScenarioCell] ([ScenarioCellGuid])
ON DELETE CASCADE
ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [FK_ScenarioParameter_ScenarioCell]
ALTER TABLE [dbo].[ScenarioBiomassEffect] WITH CHECK ADD CONSTRAINT [CK_ScenarioParameter_SoilMoisture] CHECK (([SoilMoisture]>(0) AND [SoilMoisture]<(4)))
ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [CK_ScenarioParameter_SoilMoisture]
ALTER TABLE [dbo].[ScenarioBiomassEffect] WITH CHECK ADD CONSTRAINT [CK_ScenarioParameter_Species] CHECK (([Species]>(0) AND [Species]<(4)))
ALTER TABLE [dbo].[ScenarioBiomassEffect] CHECK CONSTRAINT [CK_ScenarioParameter_Species]
END

-- 2. Add new column to StandObject

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

-- 3. Add new columns to InitialState_TreatmentUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_TreatmentUnit' AND COLUMN_NAME = 'ParentTreatmentUnitGuid')
BEGIN
ALTER TABLE InitialState_TreatmentUnit ADD
[ParentTreatmentUnitGuid] [uniqueidentifier] NULL,
[TreatmentUnitType] [int] NULL
END

-- 4. Add new columns to InitialState_EvenAgedData

exec sp_rename 'InitialState_EvenAgedData.EvenAgeType', 'EvenAgedType', 'COLUMN'

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_EvenAgedData' AND COLUMN_NAME = 'TreeSizeDiversity')
BEGIN
ALTER TABLE InitialState_EvenAgedData ADD
[TreeSizeDiversity] [int] NULL,
[TreeSizeDiversityGiniCoefficient] [float] NULL
END

-- 5. Add new columns to InitialState_BiomassData

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

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.8.0.0';

PRINT 'Success! Database upgrade to version 0.8.0.0';
END

</pre>
[[Category:Database scripts]]

Heureka database upgrade script 0.7.0.0

2009-08-21T12:50:12Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7....

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

-- 1. Rename columns in InitialState_StatisticsData

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_StatisticsData' AND COLUMN_NAME = 'Volume_ConfidenceInterval')
BEGIN
exec sp_rename 'InitialState_StatisticsData.Volume_ConfidenceInterval', 'VolumeStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_ConfidenceInterval', 'DiameterStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_ConfidenceInterval', 'AgeStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_ConfidenceInterval', 'StemsStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_ConfidenceInterval', 'HeightStdDev', 'COLUMN';

exec sp_rename 'InitialState_StatisticsData.Volume_StandardError', 'VolumeStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_StandardError', 'DiameterStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_StandardError', 'AgeStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_StandardError', 'StemsStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_StandardError', 'HeightStdErr', 'COLUMN';

exec sp_rename 'InitialState_StatisticsData.Volume_TotalPerHa', 'VolumeAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_TotalPerHa', 'StemsAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_MeanValue', 'DiameterAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_MeanValue', 'AgeAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_MeanValue', 'HeightAvg', 'COLUMN';
END

-- 2. Add columns for sample plot to TreatmentUnit and InitialState_TreatmentUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TreatmentUnit' AND COLUMN_NAME = 'IsSamplePlot')
BEGIN
ALTER TABLE [TreatmentUnit] ADD
[IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsSamplePlot] DEFAULT ((0)),
[IsCircular] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsCircular] DEFAULT ((0))

ALTER TABLE [InitialState_TreatmentUnit] ADD
[IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_InitialState_TreatmentUnit_IsSamplePlot] DEFAULT ((0))
END

-- 3. Add columns to InitialState

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'ArithmeticMeanAgeTotal')
BEGIN
ALTER TABLE [InitialState_ForestData] ADD
[ArithmeticMeanAgeTotal] [float] NULL,
[ArithmeticMeanAgeTotalExclOverstorey] [float] NULL

ALTER TABLE [InitialState_PredictionUnit] ADD
[ArithmeticMeanAgeTotal] [float] NULL
END

-- 4. Change site index in StandObject

ALTER TABLE StandObject
DROP CONSTRAINT [DF_StandObject_SiteIndex]

ALTER TABLE StandObject
ALTER COLUMN [SiteIndex] [real] NULL

ALTER TABLE StandObject
ADD CONSTRAINT [DF_StandObject_SiteIndex] DEFAULT ((0)) FOR [SiteIndex]

-- 5. Add column for meanHeightArithmetic to ReferenceUnit

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

-- 6. Add column for SampleDesign to InvTreatmentUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'SampleDesign')
BEGIN
ALTER TABLE [InvTreatmentUnit] ADD
[SampleDesign] [varchar](50) NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.7.0.0';

PRINT 'Success! Database upgrade to version 0.7.0.0';
END

</pre>
[[Category:Database scripts]]

Import of stand register

2009-08-04T08:49:02Z

Cawa2: /* File specification */

Heureka database upgrade script 0.6.0.1

2009-06-24T12:29:09Z

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

[[Heureka database upgrade scripts | Back to scripts]]
<pre>
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.0' OR ApplicationVersion = '0.6.0.0' ) = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.6.0.0 before running this script.';
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentHistory')
BEGIN
DROP TABLE InvTreatmentHistory
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvSitePosition')
BEGIN
DROP TABLE InvSitePosition
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTree')
BEGIN
DROP TABLE InvTree
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeDead')
BEGIN
DROP TABLE InvTreeDead
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeSample')
BEGIN
DROP TABLE InvTreeSample
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantBi')
BEGIN
DROP TABLE InvPlantBi
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantMain')
BEGIN
DROP TABLE InvPlantMain
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitTree')
BEGIN
DROP TABLE InvReferenceUnitTree
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant')
BEGIN
DROP TABLE InvReferenceUnitPlant
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitSiteData')
BEGIN
DROP TABLE InvReferenceUnitSiteData
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit')
BEGIN
DROP TABLE InvReferenceUnit
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvCorrectionFactor')
BEGIN
DROP TABLE InvCorrectionFactor
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit')
BEGIN
DROP TABLE InvTreatmentUnit
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnitHistory')
BEGIN
DROP TABLE InvTreatmentUnitHistory
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvSitePosition')
BEGIN
DROP TABLE InvSitePosition
END

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[InvTreatmentUnit](
[StandObjectGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[AreaFactor] [real] NULL,
[InventoryYear] [int] NOT NULL,
[Area] [real] NOT NULL,
[Spacing] [int] NOT NULL,
[SurveyorCode] [int] NOT NULL,
[P1] [int] NOT NULL,
[P2] [int] NOT NULL,
[P3] [int] NOT NULL,
[PlotRadiusPlant] [int] NOT NULL,
[PlotRadiusTree] [int] NOT NULL,
[Checked] [int] NULL,
CONSTRAINT [InvTreatmentUnit_PK] PRIMARY KEY CLUSTERED
(
[StandObjectGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[StandObjectGUID] 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]

/****** Object: Table [dbo].[InvTreeSample] Script Date: 06/11/2009 07:33:01 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[InvTreeSample](
[TreeSampleGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[IsSampleTree] [int] NOT NULL,
[TreeId] [int] NOT NULL,
[TypeCode] [int] NOT NULL,
[SCode] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[Height] [real] NULL,
[Age] [int] NULL,
[Quality] [int] NULL,
[Damage] [int] NULL,
[DistA] [real] NULL,
[DistB] [real] NULL,
[DistC] [real] NULL,
[Angel] [real] NULL,
[XCoordinate] [real] NULL,
[YCoordinate] [real] NULL,
[Distance] [real] NULL,
CONSTRAINT [InvTreeSample_PK] PRIMARY KEY CLUSTERED
(
[TreeSampleGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[TreeSampleGUID] 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]
/****** Object: Table [dbo].[InvTree] Script Date: 06/11/2009 07:32:45 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[InvTree](
[TreeGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[Distance] [real] NULL,
[IsSampleTree] [int] NOT NULL,
[TreeId] [int] NOT NULL,
[TypeCode] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[DistA] [real] NULL,
[DistB] [real] NULL,
[DistC] [real] NULL,
[Angel] [real] NULL,
[XCoordinate] [real] NULL,
[YCoordinate] [real] NULL,
CONSTRAINT [InvTree_PK] PRIMARY KEY CLUSTERED
(
[TreeGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__InvTree__167A2832] UNIQUE NONCLUSTERED
(
[TreeGUID] 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]
/****** Object: Table [dbo].[InvPlantBi] Script Date: 06/11/2009 07:32:07 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[InvPlantBi](
[PlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[PlantId] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Height] [real] NULL,
[Stems] [real] NOT NULL,
CONSTRAINT [InvPlantBi_PK] PRIMARY KEY CLUSTERED
(
[PlantGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[PlantGUID] 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]

/****** Object: Table [dbo].[InvPlantMain] Script Date: 06/11/2009 07:32:11 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvPlantMain](
[PlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NOT NULL,
[PlantId] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Height] [real] NULL,
CONSTRAINT [InvPlantMain_PK] PRIMARY KEY CLUSTERED
(
[PlantGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[PlantGUID] 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]

/****** Object: Table [dbo].[InvCorrectionFactor] Script Date: 06/11/2009 07:32:04 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvCorrectionFactor](
[StandObjectGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[CorrectionType] [int] NOT NULL,
[SpeciesGroup] [int] NOT NULL,
[CorrectionFactor] [float] NOT NULL,
CONSTRAINT [PK_InvCorrFactor] PRIMARY KEY CLUSTERED
(
[StandObjectGUID] ASC,
[StratificationGUID] ASC,
[CorrectionType] ASC,
[SpeciesGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** Object: Table [dbo].[InvReferenceUnit] Script Date: 06/11/2009 07:32:18 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvReferenceUnit](
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[StandObjectGUID] [uniqueidentifier] NOT NULL,
[PlotNr] [int] NOT NULL,
[InventoryYear] [int] NOT NULL,
[Altitude] [int] NULL,
[ClimateCode] [int] NULL,
[ImpedimentPercentage] [real] NULL,
[GPSCordEast] [real] NULL,
[GPSCordNorth] [real] NULL,
[TCordEast] [real] NULL,
[TCordNorth] [real] NULL,
[HPCordEast] [real] NULL,
[HPCordNorth] [real] NULL,
[Checked] [int] NULL,
[SiteIndex] [real] NULL,
[TotalBasalArea] [real] NULL,
CONSTRAINT [InvReferenceUnit_PK] PRIMARY KEY CLUSTERED
(
[ReferenceUnitGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[ReferenceUnitGUID] 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]

/****** Object: Table [dbo].[InvReferenceUnitSiteData] Script Date: 06/11/2009 07:32:30 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvReferenceUnitSiteData](
[ReferenceUnitSiteDataGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[Peat] [bit] NULL,
[SoilMoistCode] [int] NULL,
[LaterW] [int] NULL,
[TextureCode] [int] NULL,
[SoilDepth] [int] NULL,
[GroundLayerCode] [int] NULL,
[VegitationTypeCode] [int] NULL,
[Ditch] [bit] NULL,
[Direction] [int] NULL,
[DominantSpeciesCode] [int] NULL,
[GroundCondition] [int] NULL,
[Boulder] [int] NULL,
[Slope] [int] NULL,
[SmallTreesP] [int] NULL,
[SmallTreesS] [int] NULL,
[SmallTreesB] [int] NULL,
[SmallTreesOther] [int] NULL,
[DamageCause] [int] NULL,
[DamagePercentage] [int] NULL,
[Split] [bit] NULL,
CONSTRAINT [InvReferenceUnitSiteData_PK] PRIMARY KEY CLUSTERED
(
[ReferenceUnitSiteDataGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__InvReferenceUnit__09202D14] UNIQUE NONCLUSTERED
(
[ReferenceUnitSiteDataGUID] 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]

/****** Object: Table [dbo].[InvReferenceUnitPlant] Script Date: 06/11/2009 07:32:21 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvReferenceUnitPlant](
[ReferenceUnitPlantGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[ImpedimentPercentage] [real] NULL,
[RejuvenationProposal] [int] NULL,
[CleaningProposal] [int] NULL,
CONSTRAINT [InvReferenceUnitPlant_PK] PRIMARY KEY CLUSTERED
(
[ReferenceUnitPlantGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[ReferenceUnitPlantGUID] 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]

/****** Object: Table [dbo].[InvTreeDead] Script Date: 06/11/2009 07:32:53 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvTreeDead](
[TreeDeadGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[DateInsert] [datetime] NULL,
[TreeId] [int] NOT NULL,
[Position] [int] NOT NULL,
[SpeciesCode] [int] NOT NULL,
[Diameter] [real] NULL,
[DecayClass] [int] NOT NULL,
[HeightToBreak] [int] NULL,
[PartLength1] [int] NULL,
[TopDiameter1] [int] NULL,
[PartLength2] [int] NULL,
[TopDiameter2] [int] NULL,
[PartLength3] [int] NULL,
[TopDiameter3] [int] NULL,
[PartLength4] [int] NULL,
[TopDiameter4] [int] NULL,
[PileLength] [int] NULL,
[Stems] [int] NULL,
CONSTRAINT [InvTreeDead_PK] PRIMARY KEY CLUSTERED
(
[TreeDeadGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[TreeDeadGUID] 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]

/****** Object: Table [dbo].[InvTreatmentHistory] Script Date: 06/12/2009 11:53:08 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvTreatmentHistory](
[TreatmentHistoryGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[TreatmentTypeCode] [int] NOT NULL,
[YearsSinceTreatment] [int] NOT NULL
) ON [PRIMARY]

/****** Object: Table [dbo].[InvSitePosition] Script Date: 06/12/2009 11:53:06 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvSitePosition](
[SitePositionGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[Direction] [int] NULL,
[PositionTypeCode] [int] NULL,
[MaturityClassCode] [int] NULL,
CONSTRAINT [InvSitePosition_PK] PRIMARY KEY CLUSTERED
(
[SitePositionGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[SitePositionGUID] 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]

/****** Object: Table [dbo].[InvReferenceUnitTree] Script Date: 06/11/2009 07:32:33 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvReferenceUnitTree](
[ReferenceUnitTreeGUID] [uniqueidentifier] NOT NULL,
[ReferenceUnitGUID] [uniqueidentifier] NOT NULL,
[StratificationGUID] [uniqueidentifier] NOT NULL,
[EvenAgedCode] [int] NOT NULL,
[MeanAgeBreastHeight] [real] NULL,
[QualityPine] [int] NULL,
[QualitySpruce] [int] NULL,
CONSTRAINT [InvReferenceUnitTree_PK] PRIMARY KEY CLUSTERED
(
[ReferenceUnitTreeGUID] ASC,
[StratificationGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[ReferenceUnitTreeGUID] 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]

/****** Object: ForeignKey [FK_InvCorrectionFactor_TreatmentUnit] Script Date: 06/11/2009 07:32:04 ******/
ALTER TABLE [dbo].[InvCorrectionFactor] WITH CHECK ADD CONSTRAINT [FK_InvCorrectionFactor_TreatmentUnit] FOREIGN KEY([StandObjectGUID], [StratificationGUID])
REFERENCES [dbo].[InvTreatmentUnit] ([StandObjectGUID], [StratificationGUID])

ALTER TABLE [dbo].[InvCorrectionFactor] CHECK CONSTRAINT [FK_InvCorrectionFactor_TreatmentUnit]

/****** Object: ForeignKey [FK_PlantBi_ReferenceUnitPlan] Script Date: 06/11/2009 07:32:08 ******/
ALTER TABLE [dbo].[InvPlantBi] WITH CHECK ADD CONSTRAINT [FK_PlantBi_ReferenceUnitPlan] FOREIGN KEY([ReferenceUnitPlantGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnitPlant] ([ReferenceUnitPlantGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvPlantBi] CHECK CONSTRAINT [FK_PlantBi_ReferenceUnitPlan]

/****** Object: ForeignKey [FK_PlantMain_ReferenceUnitPlant] Script Date: 06/11/2009 07:32:11 ******/
ALTER TABLE [dbo].[InvPlantMain] WITH CHECK ADD CONSTRAINT [FK_PlantMain_ReferenceUnitPlant] FOREIGN KEY([ReferenceUnitPlantGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnitPlant] ([ReferenceUnitPlantGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvPlantMain] CHECK CONSTRAINT [FK_PlantMain_ReferenceUnitPlant]

/****** Object: ForeignKey [FK_ReferenceUnit_TreatmentUnit] Script Date: 06/11/2009 07:32:18 ******/
ALTER TABLE [dbo].[InvReferenceUnit] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnit_TreatmentUnit] FOREIGN KEY([StandObjectGUID], [StratificationGUID])
REFERENCES [dbo].[InvTreatmentUnit] ([StandObjectGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvReferenceUnit] CHECK CONSTRAINT [FK_ReferenceUnit_TreatmentUnit]

/****** Object: ForeignKey [FK_ReferenceUnitPlant_ReferenceUnit] Script Date: 06/11/2009 07:32:21 ******/
ALTER TABLE [dbo].[InvReferenceUnitPlant] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitPlant_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvReferenceUnitPlant] CHECK CONSTRAINT [FK_ReferenceUnitPlant_ReferenceUnit]

/****** Object: ForeignKey [FK_ReferenceUnitSiteData_ReferenceUnit] Script Date: 06/11/2009 07:32:30 ******/
ALTER TABLE [dbo].[InvReferenceUnitSiteData] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitSiteData_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvReferenceUnitSiteData] CHECK CONSTRAINT [FK_ReferenceUnitSiteData_ReferenceUnit]

/****** Object: ForeignKey [FK_ReferenceUnitTree_ReferenceUnit] Script Date: 06/11/2009 07:32:33 ******/
ALTER TABLE [dbo].[InvReferenceUnitTree] WITH CHECK ADD CONSTRAINT [FK_ReferenceUnitTree_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID],[StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvReferenceUnitTree] CHECK CONSTRAINT [FK_ReferenceUnitTree_ReferenceUnit]

/****** Object: ForeignKey [FK_Tree_ReferenceUnitTree] Script Date: 06/11/2009 07:32:45 ******/
ALTER TABLE [dbo].[InvTree] WITH CHECK ADD CONSTRAINT [FK_Tree_ReferenceUnitTree] FOREIGN KEY([ReferenceUnitTreeGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnitTree] ([ReferenceUnitTreeGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvTree] CHECK CONSTRAINT [FK_Tree_ReferenceUnitTree]

/****** Object: ForeignKey [FK_TreeDead_ReferenceUnit] Script Date: 06/11/2009 07:32:53 ******/
ALTER TABLE [dbo].[InvTreeDead] WITH CHECK ADD CONSTRAINT [FK_TreeDead_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvTreeDead] CHECK CONSTRAINT [FK_TreeDead_ReferenceUnit]

/****** Object: ForeignKey [FK_TreeSample_ReferenceUnitTree] Script Date: 06/11/2009 07:33:02 ******/
ALTER TABLE [dbo].[InvTreeSample] WITH CHECK ADD CONSTRAINT [FK_TreeSample_ReferenceUnitTree] FOREIGN KEY([ReferenceUnitTreeGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnitTree] ([ReferenceUnitTreeGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvTreeSample] CHECK CONSTRAINT [FK_TreeSample_ReferenceUnitTree]

/****** Object: ForeignKey [FK_TreatmentHistory_ReferenceUnit] Script Date: 06/12/2009 11:53:09 ******/
ALTER TABLE [dbo].[InvTreatmentHistory] WITH CHECK ADD CONSTRAINT [FK_TreatmentHistory_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvTreatmentHistory] CHECK CONSTRAINT [FK_TreatmentHistory_ReferenceUnit]

/****** Object: ForeignKey [FK_SitePosition_ReferenceUnit] Script Date: 06/12/2009 11:53:06 ******/
ALTER TABLE [dbo].[InvSitePosition] WITH CHECK ADD CONSTRAINT [FK_SitePosition_ReferenceUnit] FOREIGN KEY([ReferenceUnitGUID], [StratificationGUID])
REFERENCES [dbo].[InvReferenceUnit] ([ReferenceUnitGUID], [StratificationGUID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[InvSitePosition] CHECK CONSTRAINT [FK_SitePosition_ReferenceUnit]

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.6.0.1';

PRINT 'Success! Database upgrade to version 0.6.0.1';
END

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.4

2009-06-10T09:29:23Z

Cawa2:

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

-- 2. Update columns in Stratification

IF EXISTS(SELECT * FROM Stratification WHERE ModifiedDate IS NULL)
BEGIN
UPDATE [dbo].[Stratification] SET
ModifiedDate = CreatedDate,
ModifiedBy = CreatedBy

ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedDate] [datetime] NOT NULL;
ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedBy] [varchar](50) NOT NULL;
END

-- Update version

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

END
</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.3

2009-06-10T09:29:02Z

Cawa2:

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

--1. Add Constraints to StandInventory

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StandObjectGUID' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
ALTER TABLE [STANDINVENTORY] DROP CONSTRAINT PK_StandInventory

ALTER TABLE [STANDINVENTORY]
ADD CONSTRAINT PK_StandInventory PRIMARY KEY (StandObjectGUID, StratificationGUID)

END

-- 2. Add columns to Stratification
-- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'ModifiedDate')
BEGIN
ALTER TABLE [dbo].[Stratification] ADD
[ModifiedDate] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL
END

-- Update version

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

END
</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.4

2009-06-10T09:25:46Z

Cawa2: New page: Back to scripts <pre> </pre> Category:Database

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.3

2009-06-10T09:21:06Z

Cawa2: Replacing page with ' Back to scripts <pre> </pre> Category:Database'

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.3

2009-06-10T09:18:30Z

Cawa2:

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

--1. Add Constraints to StandInventory

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StandObjectGUID' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
ALTER TABLE [STANDINVENTORY] DROP CONSTRAINT PK_StandInventory

ALTER TABLE [STANDINVENTORY]
ADD CONSTRAINT PK_StandInventory PRIMARY KEY (StandObjectGUID, StratificationGUID)

END

-- 2. Add columns to Stratification
-- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'ModifiedDate')
BEGIN
ALTER TABLE [dbo].[Stratification] ADD
[ModifiedDate] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL
END

IF EXISTS(SELECT * FROM Stratification WHERE ModifiedDate IS NULL)
BEGIN
UPDATE [dbo].[Stratification] SET
ModifiedDate = CreatedDate,
ModifiedBy = CreatedBy

ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedDate] [datetime] NOT NULL;
ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedBy] [varchar](50) NOT NULL;
END

-- Update version

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

END

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.3

2009-06-10T09:12:11Z

Cawa2: Replacing page with ' Back to scripts <pre> </pre> Category:Database'

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.4.3

2009-06-10T09:05:18Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0 BEGIN PRINT 'Wrong Version of database. You must ...

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

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

-- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'ModifiedDate')
BEGIN
ALTER TABLE [dbo].[Stratification] ADD
[ModifiedDate] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL
END
GO

IF EXISTS(SELECT * FROM Stratification WHERE ModifiedDate IS NULL)
BEGIN
UPDATE [dbo].[Stratification] SET
ModifiedDate = CreatedDate,
ModifiedBy = CreatedBy

ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedDate] [datetime] NOT NULL;
ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedBy] [varchar](50) NOT NULL;
END

-- Update version

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

END

</pre>
[[Category:Database]]

Import of stand register

2009-06-02T12:43:11Z

Cawa2: /* File specification */

Import of stand register

2009-06-02T12:41:15Z

Cawa2: /* File specification */

Heureka database upgrade script 0.5.4.1

2009-06-02T12:07:01Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.0' OR ApplicationVersion = '0.5.4.1') = 0 BEGIN PRINT 'Wr...

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

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

-- 1. Add new columns to StandObject

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'AreaLevel2')
BEGIN
ALTER TABLE [dbo].[StandObject] ADD
[AreaLevel2] [nvarchar](50) NULL,
[AreaLevel3] [nvarchar](50) NULL,
[Register] [int] NULL
END

-- 2. Add new column to StratificationDomain

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StratificationDomain' AND COLUMN_NAME = 'PriorityNo')
BEGIN
ALTER TABLE [dbo].[StratificationDomain] ADD
[PriorityNo] [int] NULL
END

-- Update version

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

END

</pre>
[[Category:Database]]

Heureka database upgrade script 0.5.3.1

2009-05-06T12:13:53Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.3' OR ApplicationVersion = '0.5.3.1' ) = 0 BEGIN PRINT 'Wron...

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

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

-- 1. 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

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.3.1';

PRINT 'Success! Database upgrade to version 0.5.3.1';

END

</pre>

[[Category:Database]]

Heureka database upgrade script 0.5.3

2009-04-28T12:35:52Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0 BEGIN PRINT 'Wrong...

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

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'ProportionPine')
BEGIN
exec sp_rename 'StandObject.ProportionPine', 'Proportion_Pine', 'COLUMN';
exec sp_rename 'StandObject.ProportionSpruce', 'Proportion_Spruce', 'COLUMN';
exec sp_rename 'StandObject.ProportionBirch', 'Proportion_Birch', 'COLUMN';
exec sp_rename 'StandObject.ProportionAspen', 'Proportion_Aspen', 'COLUMN';
exec sp_rename 'StandObject.ProportionOak', 'Proportion_Oak', 'COLUMN';
exec sp_rename 'StandObject.ProportionBeech', 'Proportion_Beech', 'COLUMN';
exec sp_rename 'StandObject.ProportionDeciduous', 'Proportion_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.ProportionContorta', 'Proportion_Contorta', 'COLUMN';
exec sp_rename 'StandObject.ProportionBroadleaf', 'Proportion_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.QuadraticMeanDiameterPine', 'QuadraticMeanDiameter_Pine', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterSpruce', 'QuadraticMeanDiameter_Spruce', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBirch', 'QuadraticMeanDiameter_Birch', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterAspen', 'QuadraticMeanDiameter_Aspen', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterOak', 'QuadraticMeanDiameter_Oak', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBeech', 'QuadraticMeanDiameter_Beech', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterDeciduous', 'QuadraticMeanDiameter_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterContorta', 'QuadraticMeanDiameter_Contorta', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBroadleaf', 'QuadraticMeanDiameter_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanDiameterPine', 'MeanDiameter_Pine', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterSpruce', 'MeanDiameter_Spruce', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBirch', 'MeanDiameter_Birch', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterAspen', 'MeanDiameter_Aspen', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterOak', 'MeanDiameter_Oak', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBeech', 'MeanDiameter_Beech', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterDeciduous', 'MeanDiameter_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterContorta', 'MeanDiameter_Contorta', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBroadleaf', 'MeanDiameter_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanHeightPine', 'MeanHeight_Pine', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightSpruce', 'MeanHeight_Spruce', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBirch', 'MeanHeight_Birch', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightAspen', 'MeanHeight_Aspen', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightOak', 'MeanHeight_Oak', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBeech', 'MeanHeight_Beech', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightDeciduous', 'MeanHeight_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightContorta', 'MeanHeight_Contorta', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBroadleaf', 'MeanHeight_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanDiameter', 'MeanDiameterTotal', 'COLUMN';
exec sp_rename 'StandObject.MeanHeight', 'MeanHeightTotal', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameter', 'QuadraticMeanDiameterTotal', 'COLUMN';
exec sp_rename 'StandObject.LastClearCutYear', 'LastFinalFellingYear', 'COLUMN';
END

-- 2. Add columns for arithmetic mean height to table InitialState_SpeciesData

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_SpeciesData' AND COLUMN_NAME = 'ArithmeticMeanHeightPlantPlots_Unknown')
BEGIN
ALTER TABLE [InitialState_SpeciesData] ADD
[ArithmeticMeanHeightPlantPlots_Unknown] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Pine] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Spruce] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Birch] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Aspen] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Oak] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Beech] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Deciduous] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Contorta] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Broadleaf] [float] NULL
END

-- 3. Add table Cliques
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cliques')
BEGIN
CREATE TABLE [dbo].[Cliques](
[AreaGUID] [uniqueidentifier] NOT NULL,
[CliqueID] [int] NOT NULL,
[TreatmentUnitGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID])
REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID])
ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_TreatmentUnit]
ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_Area] FOREIGN KEY([AreaGUID])
REFERENCES [dbo].[Area] ([AreaGUID])
ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_Area]
END

-- 4. Add table HarvestClusters
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'HarvestClusters')
BEGIN
CREATE TABLE [dbo].[HarvestClusters](
[AreaGUID] [uniqueidentifier] NOT NULL,
[HarvestClusterID] [int] NOT NULL,
[TreatmentUnitGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID])
REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID])
ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_TreatmentUnit]
ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_Area] FOREIGN KEY([AreaGUID])
REFERENCES [dbo].[Area] ([AreaGUID])
ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_Area]
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.3';

PRINT 'Success! Database upgrade to version 0.5.3';

END
</pre>

[[Category:Database]]

Import of stand register

2009-03-19T13:07:16Z

Cawa2: /* File specification */

Heureka database upgrade script 0.5.2.1

2009-03-18T14:20:06Z

Cawa2:

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

-- 1. Add new columns for to InitialState_SiteData and InitialState_PredictionUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_SiteData' AND COLUMN_NAME = 'SoilMoistCode')
BEGIN
ALTER TABLE [dbo].[InitialState_SiteData] ADD
[SoilMoistCode] [int] NULL,
[Peat] [bit] NULL
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_PredictionUnit' AND COLUMN_NAME = 'SoilMoistCode')
BEGIN
ALTER TABLE [dbo].[InitialState_PredictionUnit] ADD
[SoilMoistCode] [int] NULL,
[Peat] [bit] NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.2.1';

PRINT 'Success! Database upgrade to version 0.5.2.1';

END
</pre>

[[Category:Database]]

Heureka database upgrade script 0.5.2.1

2009-03-18T13:23:46Z

Cawa2: New page: Back to scripts <pre> </pre> Category:Database

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

</pre>

[[Category:Database]]

Heureka database upgrade script 0.5.2

2009-03-17T09:47:16Z

Cawa2: New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.1.2' OR ApplicationVersion = '0.5.2' ) = 0 BEGIN PRINT 'Wrong...

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

--1. Add columns to StandObject
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'LastRegenerationYear')
BEGIN
ALTER TABLE StandObject ADD
[LastRegenerationYear] [int] NULL
END

-- 2. Remove columns from StandObject
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'TotalStandAge')
BEGIN
ALTER TABLE StandObject DROP COLUMN
[TotalStandAge],
[CleaningHistory],
[FertilizationHistory]
END

--3. Rename InitialState_QModelData
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'InitialState_QModelData')
BEGIN
exec sp_rename 'InitialState_QModelData', 'InitialState_CarbonAndNitrogenData'
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.2';

PRINT 'Success! Database upgrade to version 0.5.2';

END
</pre>

[[Category:Database]]

Import of stand register

2009-03-16T09:39:44Z

Cawa2:

Import of stand register

2009-03-10T07:11:32Z

Cawa2: /* File specification */

Import of stand register

2009-03-03T14:04:07Z

Cawa2: /* Template */

Import of stand register

2009-03-03T12:04:40Z

Cawa2: /* County (field 62) */

Import of stand register

2009-03-03T12:04:06Z

Cawa2: /* File specification */

File:StandRegisterImportTemplate.xls

2009-03-02T14:15:54Z

Cawa2: uploaded a new version of "Image:StandRegisterImportTemplate.xls"

Template for import of standregister

Import of stand register

2009-03-02T14:14:19Z

Cawa2: /* File specification */

Import of stand register

2009-03-02T13:41:16Z

Cawa2: /* File specification */

Heureka database upgrade script 0.5.1.2

2009-02-27T10:54:01Z

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

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

/*===================== 1. Constraints on importsource in area ==========================*/

--1. Change constraint on column ImportSource in table Area

ALTER TABLE [dbo].[Area] DROP CONSTRAINT [CK_Area_ImportSource]
ALTER TABLE [dbo].[Area] WITH CHECK ADD CONSTRAINT [CK_Area_ImportSource] CHECK (([ImportSource]>=(1) AND [ImportSource]<=(6)))

--2. Change description on column ImportSource in table Area
IF NOT EXISTS(SELECT 1 FROM ::fn_listextendedproperty ('MS_Description', 'Schema', 'dbo', 'table', 'Area', 'column', 'ImportSource' ))
BEGIN
EXEC sp_addextendedproperty
@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table', @level1name = Area,
@level2type = N'Column', @level2name = ImportSource;
END
ELSE
BEGIN
--Update the extended property.
EXEC sp_updateextendedproperty
@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table', @level1name = Area,
@level2type = N'Column', @level2name = ImportSource;
END

/*===================== 2. Changes to StandObject ==========================*/

--1. Add columns for QMD
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'QuadraticMeanDiameter')
BEGIN
ALTER TABLE StandObject ADD
[QuadraticMeanDiameter] [float] NULL,
[QuadraticMeanDiameterPine] [float] NULL,
[QuadraticMeanDiameterSpruce] [float] NULL,
[QuadraticMeanDiameterBirch] [float] NULL,
[QuadraticMeanDiameterAspen] [float] NULL,
[QuadraticMeanDiameterOak] [float] NULL,
[QuadraticMeanDiameterBeech] [float] NULL,
[QuadraticMeanDiameterDeciduous] [float] NULL,
[QuadraticMeanDiameterContorta] [float] NULL,
[QuadraticMeanDiameterBroadleaf] [float] NULL
END

--2. Remove columns for StemProportion
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'StemProportionPine')
BEGIN
ALTER TABLE StandObject DROP COLUMN
[StemProportionPine],
[StemProportionSpruce],
[StemProportionBirch],
[StemProportionAspen],
[StemProportionOak],
[StemProportionBeech],
[StemProportionDeciduous],
[StemProportionContorta],
[StemProportionBroadleaf]
END

--3. Add columns for Regeneration informaiton
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'RegenerationMethod')
BEGIN
ALTER TABLE StandObject ADD
[RegenerationMethod] [int] NULL,
[RegenerationSpecies] [int] NULL
END

--4. Add columns for SoilMoistCode and VegetationTypeCode
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'SoilMoistCode')
BEGIN
ALTER TABLE StandObject ADD
[SoilMoistCode] [int] NULL,
[VegetationTypeCode] [int] NULL
END

--5. Add columns for MeanDiameter and MeanHeight per species
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'MeanDiameterPine')
BEGIN
ALTER TABLE StandObject ADD
[MeanDiameterPine] [float] NULL,
[MeanDiameterSpruce] [float] NULL,
[MeanDiameterBirch] [float] NULL,
[MeanDiameterAspen] [float] NULL,
[MeanDiameterOak] [float] NULL,
[MeanDiameterBeech] [float] NULL,
[MeanDiameterDeciduous] [float] NULL,
[MeanDiameterContorta] [float] NULL,
[MeanDiameterBroadleaf] [float] NULL,
[MeanHeightPine] [float] NULL,
[MeanHeightSpruce] [float] NULL,
[MeanHeightBirch] [float] NULL,
[MeanHeightAspen] [float] NULL,
[MeanHeightOak] [float] NULL,
[MeanHeightBeech] [float] NULL,
[MeanHeightDeciduous] [float] NULL,
[MeanHeightContorta] [float] NULL,
[MeanHeightBroadleaf] [float] NULL
END

--6. Add columns for additional userdefined columns
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'UserDefined4')
BEGIN
ALTER TABLE StandObject ADD
[UserDefined4] [int] NULL,
[UserDefined5] [int] NULL,
[UserDefined6] [real] NULL,
[UserDefined7] [real] NULL,
[UserDefined8] [real] NULL,
[UserDefined9] [real] NULL,
[UserDefined10] [real] NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.1.2';

PRINT 'Success! Database upgrade to version 0.5.1.2';

END

</pre>

[[Category:Database]]

Import of stand register

2009-02-27T09:50:20Z

Cawa2: /* File specification */

File:StandRegisterImportTemplate.xls

2009-02-27T08:38:26Z

Cawa2: uploaded a new version of "Image:StandRegisterImportTemplate.xls"

Template for import of standregister

Import of stand register

2009-02-27T08:36:20Z

Cawa2: /* File specification */

Import of stand register

2009-02-27T08:29:57Z

Cawa2:

Heureka database upgrade script 0.5.1.1

2009-02-16T14:45:08Z

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

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

-- 1. Add extended property on database to identify heureka forest databases

IF NOT EXISTS( SELECT 1 FROM fn_listextendedproperty('Heureka Database Type', default, default, default, default, default, default))
BEGIN
EXEC sp_addextendedproperty @name = N'Heureka Database Type', @value = 'ForestDatabase';
END

-- 2. Add columns for to StandObject for number of stems per speciesGroup

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'StemProportionPine')
BEGIN
ALTER TABLE StandObject ADD
[StemProportionPine] [real] NULL,
[StemProportionSpruce] [real] NULL,
[StemProportionBirch] [real] NULL,
[StemProportionAspen] [real] NULL,
[StemProportionOak] [real] NULL,
[StemProportionBeech] [real] NULL,
[StemProportionDeciduous] [real] NULL,
[StemProportionContorta] [real] NULL,
[StemProportionBroadleaf] [real] NULL
END

-- 3. Add new columns for to InitialState_ForestData

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'BasalAreaWeightedMeanDiameterAll')
BEGIN
ALTER TABLE [dbo].[InitialState_ForestData] ADD
[BasalAreaWeightedMeanDiameterAll] [float] NULL,
[BasalAreaWeightedMeanDiameterOverstorey] [float] NULL,
[BasalAreaWeightedMeanDiameterExclOverstorey] [float] NULL,
[MeanHeightAll] [float] NULL,
[MeanHeightOverstorey] [float] NULL,
[MeanHeightExclOverstorey] [float] NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.1.1';

PRINT 'Success! Database upgrade to version 0.5.1.1';

END
</pre>

[[Category:Database]]

File:StandRegisterImportTemplate.xls

2009-02-13T12:19:30Z

Cawa2: uploaded a new version of "Image:StandRegisterImportTemplate.xls"

Template for import of standregister

File:StandRegisterImportTemplate.xls

2009-02-13T12:17:33Z

Cawa2: uploaded a new version of "Image:StandRegisterImportTemplate.xls"

Template for import of standregister

File:StandRegisterImportTemplate.xls

2009-02-13T12:15:00Z

Cawa2: uploaded a new version of "Image:StandRegisterImportTemplate.xls"

Template for import of standregister

Import of stand register

2009-02-13T12:13:45Z

Cawa2:

Heureka database upgrade script 0.5.1

2009-02-04T14:14:16Z

Cawa2: 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...

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

Import of stand register

2008-12-03T11:38:49Z

Cawa2:

Import of stand register

2008-10-16T11:26:52Z

Cawa2: /* Template */

Import of stand register

2008-10-16T11:26:41Z

Cawa2: /* Template */

Import of stand register

2008-10-16T11:25:22Z

Cawa2:

Import of stand register

2008-10-16T11:23:05Z

Cawa2: New page: The stand register import utility requires as input a semicolon separated file containing the fields specified in this document. The easiest way to produce such a file is to use the suppli...

File:StandRegisterImportTemplate.xls

2008-10-16T07:35:31Z

Cawa2: Template for import of standregister

Template for import of standregister