Heureka database upgrade script 1.0.2.1

Back to scripts

IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.2.0') = 0
BEGIN
	PRINT 'Wrong Version of database. You must upgrade to version 1.0.2.0 before running this script.';
END
ELSE 
BEGIN		

	-- 1. Drop column RejuvenationProposal

	-- 1.1 Drop column RejuvenationProposal from table InvReferenceUnitPlant
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'RejuvenationProposal')
	BEGIN
		ALTER TABLE InvReferenceUnitPlant DROP COLUMN RejuvenationProposal
	END

	-- 1.2 Remove rows containing RejuvenationProposal for ReferenceUnitPlant from InventoryTemplate
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InventoryTemplate')
	BEGIN
		DELETE FROM InventoryTemplate WHERE TableName = 'ReferenceUnitPlant' AND ColumnName = 'RejuvenationProposal'
	END

	--  2. Add check constraint for column VegitationTypeCode in table ReferenceUnit
	IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = 'ck_ReferenceUnit_VegetationTypeCode')) AND
		(EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'VegetationTypeCode'))
	BEGIN
		ALTER TABLE [dbo].[ReferenceUnit]  WITH CHECK ADD  CONSTRAINT [ck_ReferenceUnit_VegetationTypeCode] CHECK  (([VegetationTypeCode]>=(0) AND [VegetationTypeCode]<=(18)))
		ALTER TABLE [dbo].[ReferenceUnit] CHECK CONSTRAINT [ck_ReferenceUnit_VegetationTypeCode]

	END

	-- Update Version Table

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