Difference between revisions of "Heureka database upgrade script 1.0.1.0"
Jump to navigation
Jump to search
(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 ...) |
|
(No difference)
|
Revision as of 11:18, 14 November 2009
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