-- $Revision: 1.2.2.9 $ -- $Date: 2010/12/19 10:35:01 $ -- $Author: franciscom $ -- $RCSfile: db_schema_update.sql,v $ -- DB: MSSQL -- -- Important Warning: -- This file will be processed by sqlParser.class.php, that uses SEMICOLON to find end of SQL Sentences. -- It is not intelligent enough to ignore SEMICOLONS inside comments, then PLEASE -- USE SEMICOLONS ONLY to signal END of SQL Statements. -- -- -- rev: -- 201101216 - franciscom - update to 1.9.1 DB 1.4 -- -- update some config data SET IDENTITY_INSERT /*prefix*/node_types ON INSERT INTO /*prefix*/node_types (id,description) VALUES (10,'requirement_revision'); SET IDENTITY_INSERT /*prefix*/node_types OFF -- Step 1 - Drops if needed -- Step 2 - simple structure updates -- We need to this before creating new table because we have a FK ALTER TABLE /*prefix*/req_versions ADD revision INTEGER NOT NULL DEFAULT '1'; ALTER TABLE /*prefix*/req_versions ADD log_message TEXT NULL DEFAULT NULL; ALTER TABLE /*prefix*/req_versions DROP CONSTRAINT /*prefix*/PK_req_versions; ALTER TABLE /*prefix*/req_versions ADD CONSTRAINT /*prefix*/PK_req_versions PRIMARY KEY ("id"); -- Step 3 new tables CREATE TABLE /*prefix*/req_revisions( parent_id int NOT NULL, id int NOT NULL, revision INTEGER NOT NULL DEFAULT '1', req_doc_id varchar(64) NULL, name varchar(100) NULL, scope TEXT NULL DEFAULT NULL, status CHAR(1) NOT NULL DEFAULT 'V', type CHAR(1) NULL DEFAULT NULL, active INT NOT NULL DEFAULT '1', is_open INT NOT NULL DEFAULT '1', expected_coverage INT NOT NULL DEFAULT 1, log_message TEXT NULL DEFAULT NULL, author_id INT NULL DEFAULT NULL, creation_ts datetime NOT NULL CONSTRAINT /*prefix*/DF_req_revisions_creation_ts DEFAULT (getdate()), modifier_id INT NULL DEFAULT NULL, modification_ts datetime NULL, CONSTRAINT /*prefix*/PK_req_revisions PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] ) ON [PRIMARY]; CREATE UNIQUE NONCLUSTERED INDEX /*prefix*/IX1_req_revisions ON /*prefix*/req_revisions ( parent_id,revision ) ON [PRIMARY];