Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7....

2009-08-21T12:50:12Z

New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7....

New page

[[:Category:Database scripts | Back to scripts]]
<pre>
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7.0.0') = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.6.0.2 before running this script.';
END
ELSE
BEGIN

-- 1. Rename columns in InitialState_StatisticsData

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_StatisticsData' AND COLUMN_NAME = 'Volume_ConfidenceInterval')
BEGIN
exec sp_rename 'InitialState_StatisticsData.Volume_ConfidenceInterval', 'VolumeStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_ConfidenceInterval', 'DiameterStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_ConfidenceInterval', 'AgeStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_ConfidenceInterval', 'StemsStdDev', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_ConfidenceInterval', 'HeightStdDev', 'COLUMN';

exec sp_rename 'InitialState_StatisticsData.Volume_StandardError', 'VolumeStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_StandardError', 'DiameterStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_StandardError', 'AgeStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_StandardError', 'StemsStdErr', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_StandardError', 'HeightStdErr', 'COLUMN';

exec sp_rename 'InitialState_StatisticsData.Volume_TotalPerHa', 'VolumeAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Stems_TotalPerHa', 'StemsAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Diameter_MeanValue', 'DiameterAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Age_MeanValue', 'AgeAvg', 'COLUMN';
exec sp_rename 'InitialState_StatisticsData.Height_MeanValue', 'HeightAvg', 'COLUMN';
END

-- 2. Add columns for sample plot to TreatmentUnit and InitialState_TreatmentUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TreatmentUnit' AND COLUMN_NAME = 'IsSamplePlot')
BEGIN
ALTER TABLE [TreatmentUnit] ADD
[IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsSamplePlot] DEFAULT ((0)),
[IsCircular] [bit] NOT NULL CONSTRAINT [DF_TreatmentUnit_IsCircular] DEFAULT ((0))

ALTER TABLE [InitialState_TreatmentUnit] ADD
[IsSamplePlot] [bit] NOT NULL CONSTRAINT [DF_InitialState_TreatmentUnit_IsSamplePlot] DEFAULT ((0))
END

-- 3. Add columns to InitialState

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_ForestData' AND COLUMN_NAME = 'ArithmeticMeanAgeTotal')
BEGIN
ALTER TABLE [InitialState_ForestData] ADD
[ArithmeticMeanAgeTotal] [float] NULL,
[ArithmeticMeanAgeTotalExclOverstorey] [float] NULL

ALTER TABLE [InitialState_PredictionUnit] ADD
[ArithmeticMeanAgeTotal] [float] NULL
END

-- 4. Change site index in StandObject

ALTER TABLE StandObject
DROP CONSTRAINT [DF_StandObject_SiteIndex]

ALTER TABLE StandObject
ALTER COLUMN [SiteIndex] [real] NULL

ALTER TABLE StandObject
ADD CONSTRAINT [DF_StandObject_SiteIndex] DEFAULT ((0)) FOR [SiteIndex]

-- 5. Add column for meanHeightArithmetic to ReferenceUnit

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

-- 6. Add column for SampleDesign to InvTreatmentUnit

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'SampleDesign')
BEGIN
ALTER TABLE [InvTreatmentUnit] ADD
[SampleDesign] [varchar](50) NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.7.0.0';

PRINT 'Success! Database upgrade to version 0.7.0.0';
END

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

Heureka database upgrade script 0.7.0.0 - Revision history

Cawa2: New page: Back to scripts
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.6.0.2' OR ApplicationVersion = '0.6.0.3' OR ApplicationVersion = '0.7....