Heureka database upgrade script 0.5.4.3: Difference between revisions

From Heureka Wiki
Jump to navigation Jump to search
Cawa2 (talk | contribs)
77 edits
New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0 BEGIN PRINT 'Wrong Version of database. You must ...
 
No edit summary
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
[[Heureka database upgrade scripts | Back to scripts]]
[[:Category:Database scripts | Back to scripts]]
<pre>
<pre>
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.4.2') = 0
BEGIN
BEGIN
Line 10: Line 9:
BEGIN
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
-- STATEMENTS NOT WORKING COMBINED WITHIN THE SAME BEGIN - END, THEREFORE ENTERED SEPARATELY


Line 18: Line 29:
[ModifiedBy] [varchar](50) NULL
[ModifiedBy] [varchar](50) NULL
END
END
GO
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
Line 38: Line 36:


END
END
</pre>
</pre>
[[Category:Database]]
[[Category:Database scripts]]

Latest revision as of 14:42, 14 July 2009

Back to scripts 

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

	-- Update version

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

END
Retrieved from "https://www.heurekaslu.se/w/index.php?title=Heureka_database_upgrade_script_0.5.4.3&oldid=2763"