Loading...
 
Print

SRS Automatic Database Update

Update.sql

Update sql depends on a maintained table "About". The table contains a major and minor revision for the database. These are then used to tell the update script what blocks to run. When you wish for the update to be run simply provide a increment higher than the current and contain the code in an if block as seen below. Ensure that updates are are added to the file sequentially.

Example

IF NOT EXISTS (select 1 from sysobjects where xtype='u' and name='About') BEGIN
CREATE TABLE [dbo].[About](
[InfoKey] [varchar](50) NOT NULL,
[InfoValue] [varchar](max) NOT NULL
) ON [PRIMARY]
INSERT INTO ABOUT(INFOKEY,INFOVALUE)
VALUES('CURRENT_MAJOR','-1')

INSERT INTO ABOUT(INFOKEY,INFOVALUE)
VALUES('CURRENT_MINOR','-1')
END

DECLARE @CURR_MAJ INT
SELECT @CURR_MAJ = CAST(INFOVALUE AS INTEGER)
FROM ABOUT
WHERE INFOKEY = 'CURRENT_MAJOR'

DECLARE @CURR_MIN INT
SELECT @CURR_MIN = CAST(INFOVALUE AS INTEGER)
FROM ABOUT
WHERE INFOKEY = 'CURRENT_MINOR'

/*--------- 1.1 updates ---------*/
IF (@CURR_MAJ < 1) OR (@CURR_MAJ = 1 AND @CURR_MIN < 1) BEGIN

/* Delete queue table to allow bubble and harvest trimming */
CREATE TABLE [dbo].[DeleteQueue](
[TableName] [varchar](50) NOT NULL,
[TableID] [int] NOT NULL,
CONSTRAINT [PK_DeleteQueue] PRIMARY KEY CLUSTERED
(
[TableName] ASC,
[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]

/* update the current database value */
UPDATE ABOUT
SET INFOVALUE = '1'
WHERE INFOKEY = 'CURRENT_MAJOR'

UPDATE ABOUT
SET INFOVALUE = '1'
WHERE INFOKEY = 'CURRENT_MINOR'
END


Page last modified on Friday, May 27, 2011

Print