Back to scripts
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