Peder at 11:27, 22 December 2009

2009-12-22T11:27:44Z

Peder: New page: IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.1.0') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 1.0.1.0 before running this script.'; END EL...

2009-12-22T11:26:39Z

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

New page

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

-- 1. Drop column ImpedimentPercentage

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

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

-- 2. Change constraint PlotTypes for ReferenceUnit

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReferenceUnit')
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'ck_ReferenceUnit_PlotTypeCode')
BEGIN
ALTER TABLE [dbo].[ReferenceUnit]
DROP CONSTRAINT [ck_ReferenceUnit_PlotTypeCode];
END

UPDATE [dbo].[ReferenceUnit]
SET [PlotTypeCode] = 4 WHERE [PlotTypeCode] = 3;

ALTER TABLE [dbo].[ReferenceUnit]
WITH CHECK ADD CONSTRAINT [ck_ReferenceUnit_PlotTypeCode]
CHECK (([PlotTypeCode]>=(0) AND [PlotTypeCode]<=(2)) OR [PlotTypeCode]=(4));
END

-- 3. Change name of column VegitationTypeCode in table ReferenceUnit
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'VegitationTypeCode')
BEGIN

ALTER TABLE [dbo].[ReferenceUnit] DROP CONSTRAINT ck_ReferenceUnit_VegitationTypeCode

EXEC sp_rename 'ReferenceUnit.[VegitationTypeCode]', 'VegetationTypeCode', 'COLUMN'

END

-- Change name of column VegitationTypeCode in table InvReferenceUnitSiteData
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitSiteData' AND COLUMN_NAME = 'VegitationTypeCode')
BEGIN
EXEC sp_rename 'InvReferenceUnitSiteData.[VegitationTypeCode]', 'VegetationTypeCode', 'COLUMN'
END

-- Change name of column ImpedimentPercentage to ImpedimentFraction in table ReferenceUnit
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'ImpedimentPercentage')
BEGIN
EXEC sp_rename 'ReferenceUnit.[ImpedimentPercentage]', 'ImpedimentFraction', 'COLUMN'
END

-- Change name of column ImpedimentPercentage to ImpedimentFraction in table IpakTreatmentUnit
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IpakTreatmentUnit' AND COLUMN_NAME = 'ImpedimentPercentage')
BEGIN
EXEC sp_rename 'IpakTreatmentUnit.[ImpedimentPercentage]', 'ImpedimentFraction', 'COLUMN'
END

-- Update rows containing metadata for column ReferenceUnitSiteData.VegetationTypeCode in table InventoryTemplate
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InventoryTemplate')
BEGIN
UPDATE [dbo].[InventoryTemplate] SET ColumnName = 'VegetationTypeCode' WHERE ColumnName = 'VegitationTypeCode' AND TableName = 'ReferenceUnitSiteData'
END

-- 4. separate scriptfile

-- 5. Add column SmallTrees to table ReferenceUnit

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReferenceUnit')
AND NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'SmallTrees')
BEGIN
ALTER TABLE [ReferenceUnit]
ADD [SmallTrees] [INT] NOT NULL DEFAULT 0
END

-- 6. Add column PlantPlotRadius

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Stratification' AND COLUMN_NAME = 'PlantPlotRadius')
BEGIN
ALTER TABLE [Stratification] ADD
[PlantPlotRadius] [float] NULL
END

-- Update Version Table

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

@@ Line 1: / Line 1: @@
 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.1.0') = 0
 BEGIN
@@ Line 98: / Line 101: @@
 	PRINT 'Success! Database upgrade to version 1.0.2.0';
 END

Heureka database upgrade script 1.0.2.0 - Revision history

Peder at 11:27, 22 December 2009

Peder: New page: IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '1.0.1.0') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade to version 1.0.1.0 before running this script.'; END EL...