-- $Revision: 1.1.2.4 $
-- $Date: 2011/01/22 13:47:31 $
-- $Author: franciscom $
-- $RCSfile: db_schema_update.sql,v $
-- DB: Postgres
--
-- Changing a Column's Default Value
-- ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
--
-- To remove any default value, use:
-- ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
-- This is effectively the same as setting the default to null. 
-- As a consequence, it is not an error to drop a default where one hadn't been defined, 
-- because the default is implicitly the null value.
--
-- Changing a Column's Data Type
-- ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
--
-- 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.
--
-- ALTER TABLE table  RENAME TO newtable
--
-- You can also define constraints on the column at the same time, using the usual syntax:
-- ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
-- In fact all the options that can be applied to a column description in CREATE TABLE can be used here. 
-- Keep in mind however that the default value must satisfy the given constraints, or the ADD will fail. 
-- Alternatively, you can add constraints later (see below) after you've filled in the new column correctly.
-- Tip: Adding a column with a default requires updating each row of the table (to store the new column value). 
-- However, if no default is specified, PostgreSQL is able to avoid the physical update. 
-- So if you intend to fill the column with mostly nondefault values, it's best to add the column with no default, 
-- insert the correct values using UPDATE, and then add any desired default as described below.
--
-- 5.5.3. Adding a Constraint
--
-- To add a constraint, the table constraint syntax is used. For example:
-- 
-- ALTER TABLE products ADD CHECK (name <> '');
-- ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
-- ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
-- To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
-- 
-- ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
-- The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.
--
--
--
--
--
-- internal revision:
--  20110115 - franciscom - update to 1.9.1 DB 1.4
--													add new node types: requirement_revision
--													new tables: req_revisions
--													set some default value for new columns on old data
--
--
--  20101005 - franciscom - BUGID 3855: Upgrading from 1.8 to 1.9 does not work with PostgreSQL
--                          ALTER TABLE /*prefix*/builds ADD COLUMN release_date DATE NULL;
--  20100705 - asimon - added new column build_id to user_assignments
--  20100308 - franciscom - req_relations table added
--
--  20100113 - franciscom
--  work started
--
--
-- update some config data
INSERT INTO /*prefix*/node_types (id,description) VALUES (10,'requirement_revision');

-- 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 COLUMN "revision" INTEGER NOT NULL DEFAULT '1';
ALTER TABLE /*prefix*/req_versions ADD COLUMN "log_message" TEXT NULL DEFAULT NULL;
ALTER TABLE /*prefix*/req_versions DROP CONSTRAINT /*prefix*/req_versions_pkey;
ALTER TABLE /*prefix*/req_versions ADD PRIMARY KEY ("id");
COMMENT ON TABLE /*prefix*/req_versions IS 'Updated to TL 1.9.1 - DB 1.4';


-- Step 3 - new tables
--- BUGID 4056
CREATE TABLE /*prefix*/req_revisions(  
  "parent_id" BIGINT NOT NULL DEFAULT '0' REFERENCES  /*prefix*/req_versions (id),
  "id" BIGINT NOT NULL DEFAULT '0' REFERENCES  /*prefix*/nodes_hierarchy (id),
  "revision" INTEGER NOT NULL DEFAULT '1',   
  "req_doc_id" VARCHAR(64) NULL,  --- fman - it's OK to allow a simple update query on code ?
  "name" VARCHAR(100) NULL DEFAULT NULL,
  "scope" TEXT NULL DEFAULT NULL,
  "status" CHAR(1) NOT NULL DEFAULT 'V',
  "type" CHAR(1) NULL DEFAULT NULL,
  "active" INT2 NOT NULL DEFAULT '1',   --- fman - Need To understand use i.e. just as memory ?
  "is_open" INT2 NOT NULL DEFAULT '1',  --- fman - Need To understand use i.e. just as memory ?
  "expected_coverage" INTEGER NOT NULL DEFAULT 1,
  "log_message" TEXT NULL DEFAULT NULL,
  "author_id" BIGINT NULL DEFAULT NULL,
  "creation_ts" TIMESTAMP NOT NULL DEFAULT now(),
  "modifier_id" BIGINT NULL DEFAULT NULL,
  "modification_ts" TIMESTAMP NULL,
  PRIMARY KEY ("id")
); 
CREATE UNIQUE INDEX /*prefix*/req_revisions_uidx1 ON /*prefix*/req_revisions ("parent_id","revision");