-- Oracle Schema Changes for converting Fast BugTrack/Fast IssueTrack to FIT 7.0
--

USE test;

## begin tran fbtsetup;

-- main table for holding attachment info (ie: A1.att)
ALTER TABLE FBT_ATTACHMENT DROP PRIMARY KEY;
ALTER TABLE FBT_ATTACHMENT ADD CONTEXT_ID INT;
UPDATE FBT_ATTACHMENT SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_ATTACHMENT ADD PRIMARY KEY (CONTEXT_ID,FBT_ATTACHMENT_ID);

ALTER TABLE FBT_BUG_STRUCT DROP PRIMARY KEY;
ALTER TABLE FBT_BUG_STRUCT ADD CONTEXT_ID INT;
UPDATE FBT_BUG_STRUCT SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_BUG_STRUCT ADD PRIMARY KEY (CONTEXT_ID,FBT_STRUCT_ID);

ALTER TABLE FBT_BUG_STRUCT_ENTRY DROP PRIMARY KEY;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD CONTEXT_ID INT;
UPDATE FBT_BUG_STRUCT_ENTRY SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD PRIMARY KEY (CONTEXT_ID,FBT_STRUCT_ENTRY_ID);

ALTER TABLE FBT_BUG_USER_FIELD DROP PRIMARY KEY;
ALTER TABLE FBT_BUG_USER_FIELD ADD CONTEXT_ID INT;
UPDATE FBT_BUG_USER_FIELD SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_BUG_USER_FIELD ADD PRIMARY KEY (CONTEXT_ID,FBT_USER_FIELD_ID);

ALTER TABLE FBT_BUG_USER_FIELD_ENTRY DROP PRIMARY KEY;
ALTER TABLE FBT_BUG_USER_FIELD_ENTRY ADD CONTEXT_ID INT;
UPDATE FBT_BUG_USER_FIELD_ENTRY SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_BUG_USER_FIELD_ENTRY ADD PRIMARY KEY (CONTEXT_ID,FBT_USER_FIELD_ENTRY_ID);

ALTER TABLE FBT_CFG_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_CFG_DEF ADD CONTEXT_ID INT;
UPDATE FBT_CFG_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_CFG_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_CFG_ID);

ALTER TABLE FBT_CHART_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_CHART_DEF ADD CONTEXT_ID INT;
UPDATE FBT_CHART_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_CHART_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_CHART_ID);

ALTER TABLE FBT_COLORCODE_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_COLORCODE_DEF ADD CONTEXT_ID INT;
UPDATE FBT_COLORCODE_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_COLORCODE_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_COLORCODE_ID);

ALTER TABLE FBT_EVENT_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_EVENT_DEF ADD CONTEXT_ID INT;
UPDATE FBT_EVENT_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_EVENT_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_EVENT_ID);

ALTER TABLE FBT_FLD_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_FLD_DEF ADD CONTEXT_ID INT;
UPDATE FBT_FLD_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_FLD_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_FLD_ID);

ALTER TABLE FBT_FLT_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_FLT_DEF ADD CONTEXT_ID INT;
UPDATE FBT_FLT_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_FLT_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_FLT_ID);

ALTER TABLE FBT_GROUP_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_GROUP_DEF ADD CONTEXT_ID INT;
UPDATE FBT_GROUP_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_GROUP_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_GROUP_ID);

ALTER TABLE FBT_HS_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_HS_DEF ADD CONTEXT_ID INT;
UPDATE FBT_HS_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_HS_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_HS_CHILD_ID);

ALTER TABLE FBT_MAILRULE_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_MAILRULE_DEF ADD CONTEXT_ID INT;
UPDATE FBT_MAILRULE_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_MAILRULE_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_MAILRULE_ID);

ALTER TABLE FBT_PROJECT_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_PROJECT_DEF ADD CONTEXT_ID INT;
UPDATE FBT_PROJECT_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_PROJECT_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_PROJECT_ID);

ALTER TABLE FBT_REPORT_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_REPORT_DEF ADD  CONTEXT_ID INT;
UPDATE FBT_REPORT_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_REPORT_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_REPORT_ID);

ALTER TABLE FBT_RET_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_RET_DEF ADD CONTEXT_ID INT;
UPDATE FBT_RET_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_RET_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_RET_ID);

ALTER TABLE FBT_USER_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_USER_DEF ADD CONTEXT_ID INT;
UPDATE FBT_USER_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_USER_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_USER_ID);

ALTER TABLE FBT_WF_DEF DROP PRIMARY KEY;
ALTER TABLE FBT_WF_DEF ADD CONTEXT_ID INT;
UPDATE FBT_WF_DEF SET CONTEXT_ID = 0 where CONTEXT_ID is null;
ALTER TABLE FBT_WF_DEF ADD PRIMARY KEY (CONTEXT_ID,FBT_WF_ID);

DROP INDEX FBSE_FSID;
DROP INDEX FBUF_FSID;
DROP INDEX FBUFE_FSID;
DROP INDEX FBUFE_FSEID;

CREATE INDEX FBSE_FSID ON FBT_BUG_STRUCT_ENTRY (FBT_STRUCT_ID,CONTEXT_ID);
CREATE INDEX FBUF_FSID ON FBT_BUG_USER_FIELD (FBT_STRUCT_ID,CONTEXT_ID);
CREATE INDEX FBUFE_FSID ON FBT_BUG_USER_FIELD_ENTRY (FBT_STRUCT_ID,CONTEXT_ID);
CREATE INDEX FBUFE_FSEID ON FBT_BUG_USER_FIELD_ENTRY (FBT_STRUCT_ENTRY_ID,CONTEXT_ID);

-- table used for generic objects (ie: FTS)
CREATE TABLE FIT_OBJECT (
    CONTEXT_ID INT,
    SYSTEM_ID INT,
    FIT_OBJECT_ID NUMBER, 
    CLASSNAME VARCHAR(255), 
    OBJECT_DATA LONG,
    FIT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,SYSTEM_ID,FIT_OBJECT_ID)
);

-- New in Version 7.04
-- main table for holding TestSpec attachment info (ie: tsA1.tsa)
CREATE TABLE TS_ATTACHMENT (
    CONTEXT_ID INT,
    TS_ATTACHMENT_ID NUMBER,
    TS_STRUCT_ID NUMBER,
    TS_CREATED_BY VARCHAR(255),
    TS_ATTACH_FILENAME VARCHAR(255),
    TS_ORIGINAL_FILENAME VARCHAR(255),
    TS_CONTENT_TYPE VARCHAR(255),
    TS_ATTACHMENT_DATE DATE,
    TS_COMMENTS VARCHAR(255),
    TS_CONTENT  LONG RAW,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_ATTACHMENT_ID)
);

commit;