Peder at 07:57, 10 October 2009

2009-10-10T07:57:45Z

Fkl: New page: Back to scripts

 IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.8.0.1') = 0 BEGIN 	PRINT 'Wrong Version of database. You must upgrade ...

2009-10-02T13:45:38Z

New page: Back to scripts <pre> IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.8.0.1') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade ...

New page

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

-- 1. Stratification scripts

-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTree' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTree.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeSample' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTreeSample.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitTree' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvReferenceUnitTree.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentHistory' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTreatmentHistory.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvSitePosition' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvSitePosition.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantMain' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvPlantMain.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvPlantBi' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvPlantBi.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvReferenceUnitPlant.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitPlant' AND COLUMN_NAME = 'RejuvenationProposal')
BEGIN
EXEC sp_rename 'InvReferenceUnitPlant.[RejuvenationProposal]', 'RegenerationProposal', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreeDead' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTreeDead.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentHistory' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTreatmentHistory.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnitSiteData' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvReferenceUnitSiteData.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END

-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvReferenceUnit.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvTreatmentUnit' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvTreatmentUnit.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvCorrectionFactor' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'InvCorrectionFactor.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandInventory' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'StandInventory.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
-- Change name of column StratificationGUID to InventoryGUID
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'StandobjectPlot' AND COLUMN_NAME = 'StratificationGUID')
BEGIN
EXEC sp_rename 'StandobjectPlot.[StratificationGUID]', 'InventoryGUID', 'COLUMN'
END
--
IF OBJECT_ID ('dbo.InventoryState','U') IS NULL
BEGIN
CREATE TABLE [dbo].[InventoryState](
[Stateid] [int] NOT NULL,
[Description] [nchar](50) NULL,
CONSTRAINT [PK_inventoryState] PRIMARY KEY CLUSTERED
(
[Stateid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (0, 'Empty')
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (1, 'Under Construction')
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (2, 'Ready For Inventory')
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (3, 'Under Inventory')
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (4, 'Ready To Publish')
INSERT INTO INVENTORYSTATE (STATEID, DESCRIPTION) VALUES (5, 'Published')
END
--
IF OBJECT_ID ('dbo.Inventory','U') IS NULL
BEGIN
CREATE TABLE [dbo].[Inventory](
[InventoryGUID] [uniqueidentifier] NOT NULL,
[Description] [varchar](255) NOT NULL,
[StratificationGUID] [uniqueidentifier] NULL,
[StateId] [int] NULL,
[InventoryTemplateGUID] [uniqueidentifier] NULL,
[ModifiedBy] [varchar](255) NULL,
[ModifiedDate] [datetime] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[InventoryGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_inventoryState] FOREIGN KEY([StateId])
REFERENCES [dbo].[inventoryState] ([Stateid])
ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_inventoryState]

ALTER TABLE [dbo].[InvTreatmentUnit] WITH CHECK ADD CONSTRAINT [FK_InvTreatmentUnit_Inventory1] FOREIGN KEY([InventoryGUID])
REFERENCES [dbo].[Inventory] ([InventoryGUID])

ALTER TABLE [dbo].[InvTreatmentUnit] CHECK CONSTRAINT [FK_InvTreatmentUnit_Inventory1]

END
--
IF OBJECT_ID ('dbo.InventoryTemplate','U') IS NULL
BEGIN
CREATE TABLE [dbo].[InventoryTemplate](
[InventoryGUID] [uniqueidentifier] NOT NULL,
[TableName] [nvarchar](50) NOT NULL,
[ColumnName] [nvarchar](50) NOT NULL,
[ColumnOrder] [int] NULL,
[Visible] [bit] NULL,
[IsRemovable] [bit] NULL,
[IsEditable] [bit] NULL,
[DataType] [nvarchar](50) NULL,
[AllowNULL] [bit] NULL,
[Description] [nvarchar](max) NULL,
[DescriptionShort] [nvarchar](max) NULL,
[IsSelected] [bit] NULL,
[Mandatory] [bit] NULL,
[DefaultValue] [varchar](max) NULL,
[NumberOfDecimals] [int] NULL,
[ValidValues] [varchar](max) NULL,
[ValidValuesDesc] [varchar](max) NULL,
CONSTRAINT [PK_InventoryTemplate] PRIMARY KEY CLUSTERED
(
[InventoryGUID] ASC,
[TableName] ASC,
[ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[InventoryTemplate] WITH CHECK ADD CONSTRAINT [FK_InventoryTemplate_Inventory1] FOREIGN KEY([InventoryGUID])
REFERENCES [dbo].[Inventory] ([InventoryGUID])
ALTER TABLE [dbo].[InventoryTemplate] CHECK CONSTRAINT [FK_InventoryTemplate_Inventory1]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'HPCordEast' AND DATA_TYPE = 'real')
BEGIN
ALTER TABLE InvReferenceUnit ALTER COLUMN HPCordEast float
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InvReferenceUnit' AND COLUMN_NAME = 'HPCordNOrth' AND DATA_TYPE = 'real')
BEGIN
ALTER TABLE InvReferenceUnit ALTER COLUMN HPCordNOrth float
END

-- 2. Rename columns for rejuvenation to regeneration

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ReferenceUnit' AND COLUMN_NAME = 'RejuvenationProposal')
BEGIN
exec sp_rename 'ReferenceUnit.RejuvenationProposal', 'RegenerationProposal', 'COLUMN'
END

-- 3. Add columns for InitialState_ForestData
--(added one at a time to make sure both columns are added, one of the columns has existed before, but may have been removed)

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

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

-- Update Version Table

UPDATE Version SET ApplicationVersion = '0.1.0.0';
PRINT 'Success! Database upgrade to version 0.1.0.0';
END

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

Heureka database upgrade script 1.0 - Revision history

Peder at 07:57, 10 October 2009

Fkl: New page: Back to scripts
IF (SELECT COUNT(*) FROM VERSION WHERE ApplicationVersion = '0.8.0.1') = 0 BEGIN PRINT 'Wrong Version of database. You must upgrade ...

@@ Line 215: / Line 215: @@
 	-- Update Version Table
-	UPDATE Version SET ApplicationVersion = '0.1.0.0';
+	UPDATE Version SET ApplicationVersion = '1.0.0.0';
-	PRINT 'Success! Database upgrade to version 0.1.0.0';
+	PRINT 'Success! Database upgrade to version 1.0.0.0';
 END
 </pre>
 [[Category:Database scripts]]

@@ Line 205: / Line 205: @@
 			[BiologicalMeanAgeTotalExclOverstorey] [float] NULL
 	END
 	-- Update Version Table