Heureka database upgrade script 0.4.2.1
Jump to navigation
Jump to search
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.2' OR ApplicationVersion = '0.4.2.1' ) = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.4.2 before running this script.'; SELECT ApplicationVersion FROM VERSION; END ELSE BEGIN SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; -- 1. StandObject IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'SurveyorCode') BEGIN ALTER TABLE StandObject ADD SurveyorCode int NULL, SpacingPlots int NULL, RadPlants int NULL, RadTrees int NULL, P1 int NULL, P2 int NULL, P3 int NULL, CreateDate datetime NULL; END -- 2. StandObjectPlot IF OBJECT_ID ('dbo.StandObjectPlot','U') IS NULL BEGIN CREATE TABLE [dbo].[StandObjectPlot]( [StandObjectGuid] [uniqueidentifier] NOT NULL, [StandObjectPlotGUID] [uniqueidentifier] NOT NULL, [PlotId] [int] NOT NULL, [GpsNorth] [float] NULL, [GpsEast] [float] NULL, CONSTRAINT [PK_StandObjectPlot] PRIMARY KEY CLUSTERED ( [StandObjectGuid] ASC, [StandObjectPlotGUID] 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].[StandObjectPlot] WITH CHECK ADD CONSTRAINT [FK_StandObjectPlot_StandObject] FOREIGN KEY([StandObjectGuid]) REFERENCES [dbo].[StandObject] ([StandObjectGuid]); ALTER TABLE [dbo].[StandObjectPlot] CHECK CONSTRAINT [FK_StandObjectPlot_StandObject]; END -- 3. StandInventory IF OBJECT_ID ('dbo.StandInventory','U') IS NULL BEGIN CREATE TABLE [dbo].[StandInventory]( [StandObjectGuid] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_StandInventory] PRIMARY KEY CLUSTERED ( [StandObjectGuid] 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].[StandInventory] WITH CHECK ADD CONSTRAINT [FK_StandInventory_StandObject] FOREIGN KEY([StandObjectGuid]) REFERENCES [dbo].[StandObject] ([StandObjectGuid]); ALTER TABLE [dbo].[StandInventory] CHECK CONSTRAINT [FK_StandInventory_StandObject]; END -- 4. InitialState_SiteData IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_SiteData' AND COLUMN_NAME = 'Saplings_Unknown') BEGIN ALTER TABLE dbo.InitialState_SiteData ADD Saplings_Unknown float(53) NULL, Saplings_Pine float(53) NULL, Saplings_Spruce float(53) NULL, Saplings_Birch float(53) NULL, Saplings_Aspen float(53) NULL, Saplings_Oak float(53) NULL, Saplings_Beech float(53) NULL, Saplings_Deciduous float(53) NULL, Saplings_Contorta float(53) NULL, Saplings_Broadleaf float(53) NULL; END -- 5. InitialState_PredictionUnit IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_PredictionUnit' AND COLUMN_NAME = 'Saplings_Unknown') BEGIN ALTER TABLE dbo.InitialState_PredictionUnit ADD Saplings_Unknown float(53) NULL, Saplings_Pine float(53) NULL, Saplings_Spruce float(53) NULL, Saplings_Birch float(53) NULL, Saplings_Aspen float(53) NULL, Saplings_Oak float(53) NULL, Saplings_Beech float(53) NULL, Saplings_Deciduous float(53) NULL, Saplings_Contorta float(53) NULL, Saplings_Broadleaf float(53) NULL; END -- Update Version Table UPDATE Version SET ApplicationVersion = '0.4.2.1'; PRINT 'Success! Database upgrade to version 0.4.2.1'; END