Heureka database upgrade script 1.0.2.0
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