Peder at 13:50, 14 July 2009

2009-07-14T13:50:43Z

← Older revision		Revision as of 14:50, 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.1.1' OR ApplicationVersion = '0.5.1.2' ) = 0		IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.1.1' OR ApplicationVersion = '0.5.1.2' ) = 0
Line 135:		Line 135:
	</pre>		</pre>

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

Cawa2: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.5.1.1' OR ApplicationVersion = '0.5.1.2' ) = 0 BEGIN 	PRINT 'Wro...

2009-02-27T10:54:01Z

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

New page

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

/*===================== 1. Constraints on importsource in area ==========================*/

--1. Change constraint on column ImportSource in table Area

ALTER TABLE [dbo].[Area] DROP CONSTRAINT [CK_Area_ImportSource]
ALTER TABLE [dbo].[Area] WITH CHECK ADD CONSTRAINT [CK_Area_ImportSource] CHECK (([ImportSource]>=(1) AND [ImportSource]<=(6)))

--2. Change description on column ImportSource in table Area
IF NOT EXISTS(SELECT 1 FROM ::fn_listextendedproperty ('MS_Description', 'Schema', 'dbo', 'table', 'Area', 'column', 'ImportSource' ))
BEGIN
EXEC sp_addextendedproperty
@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table', @level1name = Area,
@level2type = N'Column', @level2name = ImportSource;
END
ELSE
BEGIN
--Update the extended property.
EXEC sp_updateextendedproperty
@name = N'MS_Description', @value = 'NULL = not defined, 1=TAX, 2=IPAK, 3=KNN, 4=Manual, 5=HeurekaInvApplication, 6=StandRegister',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table', @level1name = Area,
@level2type = N'Column', @level2name = ImportSource;
END

/*===================== 2. Changes to StandObject ==========================*/

--1. Add columns for QMD
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'QuadraticMeanDiameter')
BEGIN
ALTER TABLE StandObject ADD
[QuadraticMeanDiameter] [float] NULL,
[QuadraticMeanDiameterPine] [float] NULL,
[QuadraticMeanDiameterSpruce] [float] NULL,
[QuadraticMeanDiameterBirch] [float] NULL,
[QuadraticMeanDiameterAspen] [float] NULL,
[QuadraticMeanDiameterOak] [float] NULL,
[QuadraticMeanDiameterBeech] [float] NULL,
[QuadraticMeanDiameterDeciduous] [float] NULL,
[QuadraticMeanDiameterContorta] [float] NULL,
[QuadraticMeanDiameterBroadleaf] [float] NULL
END

--2. Remove columns for StemProportion
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'StemProportionPine')
BEGIN
ALTER TABLE StandObject DROP COLUMN
[StemProportionPine],
[StemProportionSpruce],
[StemProportionBirch],
[StemProportionAspen],
[StemProportionOak],
[StemProportionBeech],
[StemProportionDeciduous],
[StemProportionContorta],
[StemProportionBroadleaf]
END

--3. Add columns for Regeneration informaiton
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'RegenerationMethod')
BEGIN
ALTER TABLE StandObject ADD
[RegenerationMethod] [int] NULL,
[RegenerationSpecies] [int] NULL
END

--4. Add columns for SoilMoistCode and VegetationTypeCode
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'SoilMoistCode')
BEGIN
ALTER TABLE StandObject ADD
[SoilMoistCode] [int] NULL,
[VegetationTypeCode] [int] NULL
END

--5. Add columns for MeanDiameter and MeanHeight per species
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'MeanDiameterPine')
BEGIN
ALTER TABLE StandObject ADD
[MeanDiameterPine] [float] NULL,
[MeanDiameterSpruce] [float] NULL,
[MeanDiameterBirch] [float] NULL,
[MeanDiameterAspen] [float] NULL,
[MeanDiameterOak] [float] NULL,
[MeanDiameterBeech] [float] NULL,
[MeanDiameterDeciduous] [float] NULL,
[MeanDiameterContorta] [float] NULL,
[MeanDiameterBroadleaf] [float] NULL,
[MeanHeightPine] [float] NULL,
[MeanHeightSpruce] [float] NULL,
[MeanHeightBirch] [float] NULL,
[MeanHeightAspen] [float] NULL,
[MeanHeightOak] [float] NULL,
[MeanHeightBeech] [float] NULL,
[MeanHeightDeciduous] [float] NULL,
[MeanHeightContorta] [float] NULL,
[MeanHeightBroadleaf] [float] NULL
END

--6. Add columns for additional userdefined columns
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandObject' AND COLUMN_NAME = 'UserDefined4')
BEGIN
ALTER TABLE StandObject ADD
[UserDefined4] [int] NULL,
[UserDefined5] [int] NULL,
[UserDefined6] [real] NULL,
[UserDefined7] [real] NULL,
[UserDefined8] [real] NULL,
[UserDefined9] [real] NULL,
[UserDefined10] [real] NULL
END

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.5.1.2';

PRINT 'Success! Database upgrade to version 0.5.1.2';

END

</pre>

[[Category:Database]]

Heureka database upgrade script 0.5.1.2 - Revision history

Peder at 13:50, 14 July 2009

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