Heureka database upgrade script 0.4.2.1

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