Difference between revisions of "Heureka database upgrade script 0.5.4.3"

From Heureka Wiki
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 11:21, 10 June 2009

Back to scripts
Retrieved from "https://www.heurekaslu.se/w/index.php?title=Heureka_database_upgrade_script_0.5.4.3&oldid=2260"