Difference between revisions of "Heureka database upgrade script 0.5.4.2"
Jump to navigation
Jump to search
(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 ...) |
|||
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 | |
− | + | [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 | |
− | + | [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 | |
− | + | [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 | |
− | + | [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 | |
− | + | [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 | |
− | + | [RepresentativeArea] [float] NULL | |
− | + | END | |
-- Update version | -- Update version | ||
Revision as of 15:02, 8 June 2009
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 END -- Update version UPDATE Version SET ApplicationVersion = '0.5.4.2'; PRINT 'Success! Database upgrade to version 0.5.4.2'; END