Difference between revisions of "Heureka database upgrade script 0.4.2.1"
Jump to navigation
Jump to search
(New page: <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.2') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.4.2 before running this script.'; EN...) |
|||
Line 1: | Line 1: | ||
<pre> | <pre> | ||
− | IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.2') = 0 | + | IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.4.2' OR ApplicationVersion = '0.4.2.1' ) = 0 |
BEGIN | BEGIN | ||
− | + | PRINT 'Wrong Version of database. You must upgrade to version 0.4.2 before running this script.'; | |
+ | SELECT ApplicationVersion FROM VERSION; | ||
END | END | ||
ELSE | ELSE | ||
BEGIN | 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]( | CREATE TABLE [dbo].[StandObjectPlot]( | ||
[StandObjectGuid] [uniqueidentifier] NOT NULL, | [StandObjectGuid] [uniqueidentifier] NOT NULL, | ||
Line 44: | Line 50: | ||
REFERENCES [dbo].[StandObject] ([StandObjectGuid]); | REFERENCES [dbo].[StandObject] ([StandObjectGuid]); | ||
ALTER TABLE [dbo].[StandObjectPlot] CHECK CONSTRAINT [FK_StandObjectPlot_StandObject]; | 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]( | CREATE TABLE [dbo].[StandInventory]( | ||
Line 59: | Line 69: | ||
ALTER TABLE [dbo].[StandInventory] CHECK CONSTRAINT [FK_StandInventory_StandObject]; | 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 | ALTER TABLE dbo.InitialState_SiteData ADD | ||
Line 74: | Line 88: | ||
Saplings_Contorta float(53) NULL, | Saplings_Contorta float(53) NULL, | ||
Saplings_Broadleaf 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 | + | END |
− | + | -- Update Version Table | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | UPDATE Version SET ApplicationVersion = '0.4.2.1'; | |
− | + | ||
− | + | PRINT 'Success! Database upgrade to version 0.4.2.1'; | |
END | END | ||
+ | |||
</pre> | </pre> |
Latest revision as of 20:50, 30 September 2008
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