Peder at 09:24, 14 November 2009

2009-11-14T09:24:35Z

Peder: Heureka database upgrade script 1.0.0.1 moved to Heureka database upgrade script 1.0.1.0

2009-11-14T09:18:41Z

Heureka database upgrade script 1.0.1.0 moved to Heureka database upgrade script 1.0.1.0

Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.1.0.0') = 0 BEGIN 	PRINT 'Wrong Version of database. You must upgrade ...

2009-11-13T11:43:36Z

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

New page

[[: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 1.0.1.0 - Revision history

Peder at 09:24, 14 November 2009

Peder: Heureka database upgrade script 1.0.0.1 moved to Heureka database upgrade script 1.0.1.0

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

@@ Line 1: / Line 1: @@
 [[:Category:Database scripts | Back to scripts]]
 <pre>
-IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.1.0.0') = 0
+IF (SELECT COUNT(*) FROM VERSION WHERE (ApplicationVersion = '0.1.0.0' OR ApplicationVersion = '1.0.0.0')) = 0
 BEGIN
-	PRINT 'Wrong Version of database. You must upgrade to version 0.1.0.0 before running this script.';
+	PRINT 'Wrong Version of database. You must upgrade to version 0.1.0.0 / 1.0.0.0) before running this script.';
 END
 ELSE
@@ Line 513: / Line 513: @@
 	-- Update Version Table
-	UPDATE Version SET ApplicationVersion = '1.0.0.1';
+	UPDATE Version SET ApplicationVersion = '1.0.1.0';
-	PRINT 'Success! Database upgrade to version 1.0.0.1';
+	PRINT 'Success! Database upgrade to version 1.0.1.0';
 END

← Older revision	Revision as of 09:18, 14 November 2009
(No difference)