Heureka database upgrade script 1.0.1.0

Back to scripts

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 / 1.0.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.1.0';
	PRINT 'Success! Database upgrade to version 1.0.1.0';
END