Heureka database upgrade script 0.5.4.2 - Revision history

Peder at 13:44, 14 July 2009

2009-07-14T13:44:48Z

Fkl at 13:02, 8 June 2009

2009-06-08T13:02:30Z

Fkl: New page: Back to scripts

  IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.1') = 0 BEGIN 		PRINT 'Wrong Version of database. You must ...

2009-06-08T13:00:16Z

New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.1') = 0 BEGIN PRINT 'Wrong Version of database. You must ...

New page

[[Heureka database upgrade scripts | Back to scripts]]
<pre>

IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.1') = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.5.4.1 before running this script.';
SELECT ApplicationVersion FROM VERSION;
END
ELSE
BEGIN

-- 1. Add new columns to InvTreatmentUnit
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'TreatmentUnitParentGUID')
BEGIN
ALTER TABLE [dbo].[InvTreatmentUnit] ADD
[TreatmentUnitParentGUID] [uniqueidentifier] NULL
END

-- 2. Add new columns to StandInventory
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P1')
BEGIN
ALTER TABLE [dbo].[StandInventory] ADD
[P1] [float] NULL
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P2')
BEGIN
ALTER TABLE [dbo].[StandInventory] ADD
[P2] [float] NULL
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P3')
BEGIN
ALTER TABLE [dbo].[StandInventory] ADD
[P3] [float] NULL
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StratumNo')
BEGIN
ALTER TABLE [dbo].[StandInventory] ADD
[StratumNo] [int] NULL
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'RepresentativeArea')
BEGIN
ALTER TABLE [dbo].[StandInventory] ADD
[RepresentativeArea] [float] NULL

-- Update version

UPDATE Version SET ApplicationVersion = '0.5.4.2';
PRINT 'Success! Database upgrade to version 0.5.4.2';

END

</pre>
[[Category:Database]]

@@ Line 1: / Line 1: @@
-[[Heureka database upgrade scripts | Back to scripts]]
+[[:Category:Database scripts | Back to scripts]]
 <pre>
@@ Line 52: / Line 52: @@
 </pre>
-[[Category:Database]]
+[[Category:Database scripts]]

@@ Line 11: / Line 11: @@
--- 1. Add new columns to InvTreatmentUnit
+	-- 1. Add new columns to InvTreatmentUnit
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'TreatmentUnitParentGUID')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'TreatmentUnitParentGUID')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[InvTreatmentUnit] ADD
+	 ALTER TABLE [dbo].[InvTreatmentUnit] ADD
-  [TreatmentUnitParentGUID] [uniqueidentifier] NULL
+	  [TreatmentUnitParentGUID] [uniqueidentifier] NULL
-END
+	END
--- 2. Add new columns to StandInventory
+	-- 2. Add new columns to StandInventory
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P1')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P1')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[StandInventory] ADD
+ 	ALTER TABLE [dbo].[StandInventory] ADD
-  [P1] [float] NULL
+ 	 [P1] [float] NULL
-END
+	END
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P2')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P2')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[StandInventory] ADD
+	 ALTER TABLE [dbo].[StandInventory] ADD
-  [P2] [float] NULL
+	  [P2] [float] NULL
-END
+	END
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P3')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'P3')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[StandInventory] ADD
+	 ALTER TABLE [dbo].[StandInventory] ADD
-  [P3] [float] NULL
+	  [P3] [float] NULL
-END
+	END
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StratumNo')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StratumNo')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[StandInventory] ADD
+	  ALTER TABLE [dbo].[StandInventory] ADD
-  [StratumNo] [int] NULL
+	  [StratumNo] [int] NULL
-END
+	END
-IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'RepresentativeArea')
+	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'RepresentativeArea')
-BEGIN
+	BEGIN
- ALTER TABLE [dbo].[StandInventory] ADD
+	   ALTER TABLE [dbo].[StandInventory] ADD
-  [RepresentativeArea] [float] NULL
+	  [RepresentativeArea] [float] NULL
+	END
 	-- Update version