Difference between revisions of "Heureka database upgrade script 0.5.4.3"
Jump to navigation
Jump to search
(Replacing page with ' Back to scripts <pre> </pre> Category:Database') |
|||
Line 1: | Line 1: | ||
[[Heureka database upgrade scripts | Back to scripts]] | [[Heureka database upgrade scripts | Back to scripts]] | ||
<pre> | <pre> | ||
+ | IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0 | ||
+ | BEGIN | ||
+ | PRINT 'Wrong Version of database. You must upgrade to version 0.5.4.2 before running this script.'; | ||
+ | SELECT ApplicationVersion FROM VERSION; | ||
+ | END | ||
+ | ELSE | ||
+ | BEGIN | ||
+ | --1. Add Constraints to StandInventory | ||
+ | |||
+ | IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StandObjectGUID' AND COLUMN_NAME = 'StratificationGUID') | ||
+ | BEGIN | ||
+ | ALTER TABLE [STANDINVENTORY] DROP CONSTRAINT PK_StandInventory | ||
+ | |||
+ | ALTER TABLE [STANDINVENTORY] | ||
+ | ADD CONSTRAINT PK_StandInventory PRIMARY KEY (StandObjectGUID, StratificationGUID) | ||
+ | |||
+ | END | ||
+ | |||
+ | -- 2. Add columns to Stratification | ||
+ | -- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY | ||
+ | |||
+ | IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'ModifiedDate') | ||
+ | BEGIN | ||
+ | ALTER TABLE [dbo].[Stratification] ADD | ||
+ | [ModifiedDate] [datetime] NULL, | ||
+ | [ModifiedBy] [varchar](50) NULL | ||
+ | END | ||
+ | |||
+ | IF EXISTS(SELECT * FROM Stratification WHERE ModifiedDate IS NULL) | ||
+ | BEGIN | ||
+ | UPDATE [dbo].[Stratification] SET | ||
+ | ModifiedDate = CreatedDate, | ||
+ | ModifiedBy = CreatedBy | ||
+ | |||
+ | ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedDate] [datetime] NOT NULL; | ||
+ | ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedBy] [varchar](50) NOT NULL; | ||
+ | END | ||
+ | |||
+ | |||
+ | |||
+ | -- Update version | ||
+ | |||
+ | UPDATE Version SET ApplicationVersion = '0.5.4.3'; | ||
+ | PRINT 'Success! Database upgrade to version 0.5.4.3'; | ||
+ | |||
+ | END | ||
</pre> | </pre> | ||
[[Category:Database]] | [[Category:Database]] |
Revision as of 10:18, 10 June 2009
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 0.5.4.2 before running this script.'; SELECT ApplicationVersion FROM VERSION; END ELSE BEGIN --1. Add Constraints to StandInventory IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StandObjectGUID' AND COLUMN_NAME = 'StratificationGUID') BEGIN ALTER TABLE [STANDINVENTORY] DROP CONSTRAINT PK_StandInventory ALTER TABLE [STANDINVENTORY] ADD CONSTRAINT PK_StandInventory PRIMARY KEY (StandObjectGUID, StratificationGUID) END -- 2. Add columns to Stratification -- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'ModifiedDate') BEGIN ALTER TABLE [dbo].[Stratification] ADD [ModifiedDate] [datetime] NULL, [ModifiedBy] [varchar](50) NULL END IF EXISTS(SELECT * FROM Stratification WHERE ModifiedDate IS NULL) BEGIN UPDATE [dbo].[Stratification] SET ModifiedDate = CreatedDate, ModifiedBy = CreatedBy ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedDate] [datetime] NOT NULL; ALTER TABLE [dbo].[Stratification] ALTER COLUMN [ModifiedBy] [varchar](50) NOT NULL; END -- Update version UPDATE Version SET ApplicationVersion = '0.5.4.3'; PRINT 'Success! Database upgrade to version 0.5.4.3'; END