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