Fkl at 18:50, 30 September 2008

2008-09-30T18:50:48Z

Fkl: New page:

 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...

2008-09-30T17:52:56Z

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...

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.';
END
ELSE
BEGIN

BEGIN TRANSACTION;

BEGIN TRY

-- 1. StandObject

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;

-- 2. StandObjectPlot

/****** Object: Table [dbo].[StandObjectPlot] Script Date: 09/29/2008 15:28:12 ******/
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
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];

-- 3. StandInventory

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];

-- 4. InitialState_SiteData

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;

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.4.2.1';

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

PRINT 'Success! Database upgrade to version 0.4.2.1';

END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

END
</pre>

Heureka database upgrade script 0.4.2.1 - Revision history

Fkl at 18:50, 30 September 2008

Fkl: New page:
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>
-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
-   PRINT 'Wrong Version of database. You must upgrade to version 0.4.2 before running this script.';
+	PRINT 'Wrong Version of database. You must upgrade to version 0.4.2 before running this script.';
 END
 ELSE
 BEGIN
-	BEGIN TRANSACTION;
+	SET ANSI_NULLS ON;
-	BEGIN TRY
+	-- 1. StandObject
-		-- 1. StandObject
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
-		ALTER TABLE StandObject ADD
+	ALTER TABLE StandObject ADD
-			SurveyorCode int NULL,
+		SurveyorCode int NULL,
-			SpacingPlots int NULL,
+		SpacingPlots int NULL,
-			RadPlants int NULL,
+		RadPlants int NULL,
-			RadTrees int NULL,
+		RadTrees int NULL,
-			P1 int NULL,
+		P1 int NULL,
-			P2 int NULL,
+		P2 int NULL,
-			P3 int NULL,
+		P3 int NULL,
-			CreateDate datetime NULL;
+		CreateDate datetime NULL;
-		-- 2. StandObjectPlot
+	END
 		CREATE TABLE [dbo].[StandObjectPlot](
 			[StandObjectGuid] [uniqueidentifier] NOT NULL,
@@ Line 44: / Line 50: @@
 		REFERENCES [dbo].[StandObject] ([StandObjectGuid]);
 		ALTER TABLE [dbo].[StandObjectPlot] CHECK CONSTRAINT [FK_StandObjectPlot_StandObject];
-		-- 3. StandInventory
+	-- 3. StandInventory
 		CREATE TABLE [dbo].[StandInventory](
@@ Line 59: / Line 69: @@
 		ALTER TABLE [dbo].[StandInventory] CHECK CONSTRAINT [FK_StandInventory_StandObject];
-		-- 4. InitialState_SiteData
+	-- 4. InitialState_SiteData
 		ALTER TABLE dbo.InitialState_SiteData ADD
@@ Line 74: / Line 88: @@
 			Saplings_Contorta float(53) NULL,
 			Saplings_Broadleaf float(53) NULL;
-		-- Update Version Table
+		-- 5. InitialState_PredictionUnit
-		UPDATE Version SET ApplicationVersion = '0.4.2.1';
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
-	   PRINT 'Success! Database upgrade to version 0.4.2.1';
+		ALTER TABLE dbo.InitialState_PredictionUnit ADD
-	END TRY
+	END
-	BEGIN CATCH
+	-- Update Version Table
-		IF @@TRANCOUNT > 0
+	UPDATE Version SET ApplicationVersion = '0.4.2.1';
-			ROLLBACK TRANSACTION;
-	END CATCH;
+   PRINT 'Success! Database upgrade to version 0.4.2.1';
 END
 </pre>