Peder at 13:49, 14 July 2009

2009-07-14T13:49:24Z

← Older revision		Revision as of 14:49, 14 July 2009
Line 1:		Line 1:
	[[~~Heureka database upgrade~~ scripts \| Back to scripts]]		[[:Category:Database scripts \| Back to scripts]]

	<pre>		<pre>
	IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0		IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0
Line 118:		Line 119:
	</pre>		</pre>

	[[Category:Database]]		[[Category:Database scripts]]

Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0 BEGIN 	PRINT 'Wrong...

2009-04-28T12:35:52Z

New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0 BEGIN PRINT 'Wrong...

New page

[[Heureka database upgrade scripts | Back to scripts]]
<pre>
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0
BEGIN
PRINT 'Wrong Version of database. You must upgrade to version 0.5.2.1 before running this script.';
SELECT ApplicationVersion FROM VERSION;
END
ELSE
BEGIN
-- 1. Rename columns in StandObject

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'ProportionPine')
BEGIN
exec sp_rename 'StandObject.ProportionPine', 'Proportion_Pine', 'COLUMN';
exec sp_rename 'StandObject.ProportionSpruce', 'Proportion_Spruce', 'COLUMN';
exec sp_rename 'StandObject.ProportionBirch', 'Proportion_Birch', 'COLUMN';
exec sp_rename 'StandObject.ProportionAspen', 'Proportion_Aspen', 'COLUMN';
exec sp_rename 'StandObject.ProportionOak', 'Proportion_Oak', 'COLUMN';
exec sp_rename 'StandObject.ProportionBeech', 'Proportion_Beech', 'COLUMN';
exec sp_rename 'StandObject.ProportionDeciduous', 'Proportion_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.ProportionContorta', 'Proportion_Contorta', 'COLUMN';
exec sp_rename 'StandObject.ProportionBroadleaf', 'Proportion_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.QuadraticMeanDiameterPine', 'QuadraticMeanDiameter_Pine', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterSpruce', 'QuadraticMeanDiameter_Spruce', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBirch', 'QuadraticMeanDiameter_Birch', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterAspen', 'QuadraticMeanDiameter_Aspen', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterOak', 'QuadraticMeanDiameter_Oak', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBeech', 'QuadraticMeanDiameter_Beech', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterDeciduous', 'QuadraticMeanDiameter_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterContorta', 'QuadraticMeanDiameter_Contorta', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameterBroadleaf', 'QuadraticMeanDiameter_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanDiameterPine', 'MeanDiameter_Pine', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterSpruce', 'MeanDiameter_Spruce', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBirch', 'MeanDiameter_Birch', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterAspen', 'MeanDiameter_Aspen', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterOak', 'MeanDiameter_Oak', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBeech', 'MeanDiameter_Beech', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterDeciduous', 'MeanDiameter_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterContorta', 'MeanDiameter_Contorta', 'COLUMN';
exec sp_rename 'StandObject.MeanDiameterBroadleaf', 'MeanDiameter_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanHeightPine', 'MeanHeight_Pine', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightSpruce', 'MeanHeight_Spruce', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBirch', 'MeanHeight_Birch', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightAspen', 'MeanHeight_Aspen', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightOak', 'MeanHeight_Oak', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBeech', 'MeanHeight_Beech', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightDeciduous', 'MeanHeight_Deciduous', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightContorta', 'MeanHeight_Contorta', 'COLUMN';
exec sp_rename 'StandObject.MeanHeightBroadleaf', 'MeanHeight_Broadleaf', 'COLUMN';

exec sp_rename 'StandObject.MeanDiameter', 'MeanDiameterTotal', 'COLUMN';
exec sp_rename 'StandObject.MeanHeight', 'MeanHeightTotal', 'COLUMN';
exec sp_rename 'StandObject.QuadraticMeanDiameter', 'QuadraticMeanDiameterTotal', 'COLUMN';
exec sp_rename 'StandObject.LastClearCutYear', 'LastFinalFellingYear', 'COLUMN';
END

-- 2. Add columns for arithmetic mean height to table InitialState_SpeciesData

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InitialState_SpeciesData' AND COLUMN_NAME = 'ArithmeticMeanHeightPlantPlots_Unknown')
BEGIN
ALTER TABLE [InitialState_SpeciesData] ADD
[ArithmeticMeanHeightPlantPlots_Unknown] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Pine] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Spruce] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Birch] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Aspen] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Oak] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Beech] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Deciduous] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Contorta] [float] NULL,
[ArithmeticMeanHeightPlantPlots_Broadleaf] [float] NULL
END

-- 3. Add table Cliques
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cliques')
BEGIN
CREATE TABLE [dbo].[Cliques](
[AreaGUID] [uniqueidentifier] NOT NULL,
[CliqueID] [int] NOT NULL,
[TreatmentUnitGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID])
REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID])
ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_TreatmentUnit]
ALTER TABLE [dbo].[Cliques] WITH CHECK ADD CONSTRAINT [FK_Cliques_Area] FOREIGN KEY([AreaGUID])
REFERENCES [dbo].[Area] ([AreaGUID])
ALTER TABLE [dbo].[Cliques] CHECK CONSTRAINT [FK_Cliques_Area]
END

-- 4. Add table HarvestClusters
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'HarvestClusters')
BEGIN
CREATE TABLE [dbo].[HarvestClusters](
[AreaGUID] [uniqueidentifier] NOT NULL,
[HarvestClusterID] [int] NOT NULL,
[TreatmentUnitGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_TreatmentUnit] FOREIGN KEY([TreatmentUnitGUID])
REFERENCES [dbo].[TreatmentUnit] ([TreatmentUnitGUID])
ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_TreatmentUnit]
ALTER TABLE [dbo].[HarvestClusters] WITH CHECK ADD CONSTRAINT [FK_HarvestClusters_Area] FOREIGN KEY([AreaGUID])
REFERENCES [dbo].[Area] ([AreaGUID])
ALTER TABLE [dbo].[HarvestClusters] CHECK CONSTRAINT [FK_HarvestClusters_Area]
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.3';

PRINT 'Success! Database upgrade to version 0.5.3';

END
</pre>

[[Category:Database]]

Heureka database upgrade script 0.5.3 - Revision history

Peder at 13:49, 14 July 2009

Cawa2: New page: Back to scripts
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.2.1' OR ApplicationVersion = '0.5.3' ) = 0 BEGIN PRINT 'Wrong...