-- Schema in ORACLE syntax

-- main table for holding attachment info (ie: A1.att)
CREATE TABLE FBT_ATTACHMENT (
    CONTEXT_ID INT,
    FBT_ATTACHMENT_ID NUMBER,
    FBT_STRUCT_ID NUMBER,
    FBT_CREATED_BY VARCHAR(255),
    FBT_ATTACH_FILENAME VARCHAR(255),
    FBT_ORIGINAL_FILENAME VARCHAR(255),
    FBT_CONTENT_TYPE VARCHAR(255),
    FBT_ATTACHMENT_DATE DATE,
    FBT_COMMENTS VARCHAR(255),
    FBT_CONTENT  LONG RAW,
    FBT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,FBT_ATTACHMENT_ID)
);

-- main table for bug details (ie: b1.bug field details)
CREATE TABLE FBT_BUG_STRUCT (
    CONTEXT_ID INT,
    FBT_STRUCT_ID NUMBER,
    FBT_SUBJECT VARCHAR(255),
    FBT_ASSIGNEDTO VARCHAR(255),
    FBT_STATUS VARCHAR(255),
    FBT_PRIORITY INT,
    FBT_PROJECT VARCHAR(255),
    FBT_AREA VARCHAR(255),
    FBT_ENVIRONMENT VARCHAR(255),
    FBT_VERSION VARCHAR(255),
    FBT_ENTEREDBY VARCHAR(255),
    FBT_NOTIFYLIST LONG,
    FBT_PARENT_ID NUMBER,
    FBT_REQUESTED_DUE_DATE DATE,
    FBT_ACTUAL_COMPLETION_DATE DATE,
    FBT_ESTIMATED_HOURS NUMBER,
    FBT_ACTUAL_HOURS NUMBER,
    FBT_PERCENT_COMPLETE NUMBER,
    FBT_ARCHIVED INT,
    FBT_DIRTY INT,
    PRIMARY KEY (CONTEXT_ID,FBT_STRUCT_ID)
);


-- table for bug history entries (ie: b1.bug history entries)
CREATE TABLE FBT_BUG_STRUCT_ENTRY (
    CONTEXT_ID INT,
    FBT_STRUCT_ENTRY_ID NUMBER,
    FBT_STRUCT_ID NUMBER,
    FBT_WHO VARCHAR(255),
    FBT_DATE_WHEN DATE,
    FBT_STATUS VARCHAR(255),
    FBT_ASSIGNEDTO VARCHAR(255),
    FBT_DESCRIPTION LONG,
    FBT_SUBJECT VARCHAR(255),
    FBT_PRIORITY INT,
    FBT_PROJECT VARCHAR(255),
    FBT_AREA VARCHAR(255),
    FBT_ENVIRONMENT VARCHAR(255),
    FBT_VERSION VARCHAR(255),
    FBT_NOTIFYLIST VARCHAR(255),
    FBT_PARENT_ID NUMBER,
    FBT_REQUESTED_DUE_DATE DATE,
    FBT_ACTUAL_COMPLETION_DATE DATE,
    FBT_ESTIMATED_HOURS NUMBER,
    FBT_ACTUAL_HOURS NUMBER,
    FBT_PERCENT_COMPLETE NUMBER,
    FBT_ATTACHMENTS VARCHAR(255),
    FBT_NOTES VARCHAR(255),
    PRIMARY KEY (CONTEXT_ID,FBT_STRUCT_ENTRY_ID)
);    

-- main table for holding bug custom field details (ie b1.bug custom details)
CREATE TABLE FBT_BUG_USER_FIELD (
    CONTEXT_ID INT,
    FBT_USER_FIELD_ID NUMBER,
    FBT_STRUCT_ID NUMBER,
    FBT_FIELD_ID INT,
    FBT_STRING_VALUE LONG,
    FBT_DATE_VALUE DATE,
    FBT_NUMBER_VALUE NUMBER,
    PRIMARY KEY (CONTEXT_ID,FBT_USER_FIELD_ID) 
    );

-- table for custom fields of bug history entries (ie: b1.bug custom history entries)
CREATE TABLE FBT_BUG_USER_FIELD_ENTRY (
    CONTEXT_ID INT,
    FBT_USER_FIELD_ENTRY_ID NUMBER,
    FBT_STRUCT_ID NUMBER,
    FBT_STRUCT_ENTRY_ID NUMBER,
    FBT_FIELD_ID INT,
    FBT_STRING_VALUE LONG,
    FBT_DATE_VALUE DATE,
    FBT_NUMBER_VALUE NUMBER,
    PRIMARY KEY (CONTEXT_ID,FBT_USER_FIELD_ENTRY_ID) 
    );

-- config files (ie: NAME.cfg)
CREATE TABLE FBT_CFG_DEF (    
    CONTEXT_ID INT,
    FBT_CFG_ID NUMBER,    
    FBT_CFG_NAME VARCHAR(40),    
    FBT_CFG_INFO LONG,
    FBT_CFG_DUPLICATE INT,
    PRIMARY KEY (CONTEXT_ID,FBT_CFG_ID)
);

-- saved charts
CREATE TABLE FBT_CHART_DEF (
    CONTEXT_ID INT,
    FBT_CHART_ID NUMBER,
    FBT_CHART_NAME VARCHAR(255),
    FBT_CHART_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_CHART_ID)
);

-- saved color codes
CREATE TABLE FBT_COLORCODE_DEF (
    CONTEXT_ID INT,
    FBT_COLORCODE_ID NUMBER,
    FBT_COLORCODE_FIELD VARCHAR(255),
    FBT_COLORCODE_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_COLORCODE_ID)
);

-- event definitions (ie: e1.es)
CREATE TABLE FBT_EVENT_DEF ( 
    CONTEXT_ID INT,
    FBT_EVENT_ID NUMBER, 
    FBT_EVENT_NAME VARCHAR(255), 
    FBT_EVENT_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_EVENT_ID)
);

-- defines custom field files (ie: F1.fld)
CREATE TABLE FBT_FLD_DEF (
    CONTEXT_ID INT,
    FBT_FLD_ID NUMBER,
    FBT_FLD_NAME VARCHAR(255),
    FBT_FLD_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_FLD_ID)
);


-- defines filter files (ie: F1.flt)
CREATE TABLE FBT_FLT_DEF (
    CONTEXT_ID INT,
    FBT_FLT_ID NUMBER,
    FBT_FLT_NAME VARCHAR(255),
    FBT_FLT_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_FLT_ID)
);

-- group definitions (ie: G1.grp)
CREATE TABLE FBT_GROUP_DEF ( 
    CONTEXT_ID INT,   
    FBT_GROUP_ID NUMBER,    
    FBT_GROUP_NAME VARCHAR(255),    
    FBT_GROUP_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_GROUP_ID)
);

-- hierarchy definitions (ie: 1.hs)
CREATE TABLE FBT_HS_DEF (  
    CONTEXT_ID INT,  
    FBT_HS_CHILD_ID NUMBER, 
    FBT_HS_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_HS_CHILD_ID)
);

-- mail rules (ie: mr1.rul)
CREATE TABLE FBT_MAILRULE_DEF (
    CONTEXT_ID INT,    
    FBT_MAILRULE_ID NUMBER,    
    FBT_MAILRULE_NAME VARCHAR(255),    
    FBT_MAILRULE_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_MAILRULE_ID)
);

-- project definitions (ie: P1.prj)
CREATE TABLE FBT_PROJECT_DEF (   
    CONTEXT_ID INT, 
    FBT_PROJECT_ID NUMBER,    
    FBT_PROJECT_NAME VARCHAR(255),    
    FBT_PROJECT_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_PROJECT_ID)
);

-- saved reports
CREATE TABLE FBT_REPORT_DEF (
    CONTEXT_ID INT,
    FBT_REPORT_ID NUMBER,
    FBT_REPORT_NAME VARCHAR(255),
    FBT_REPORT_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_REPORT_ID)
);

-- mail rule return messages (ie: rm1.ret)
CREATE TABLE FBT_RET_DEF (  
    CONTEXT_ID INT,  
    FBT_RET_ID NUMBER,    
    FBT_RET_NAME VARCHAR(255),    
    FBT_RET_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_RET_ID)
);

-- user definitions (ie: U1.use)
CREATE TABLE FBT_USER_DEF (
    CONTEXT_ID INT,    
    FBT_USER_ID NUMBER,    
    FBT_LOGINID VARCHAR(255),    
    FBT_USER_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_USER_ID)
);

-- workflow definitions (ie: W1.wf)
CREATE TABLE FBT_WF_DEF (   
    CONTEXT_ID INT, 
    FBT_WF_ID NUMBER,    
    FBT_WF_NAME VARCHAR(255),    
    FBT_WF_INFO LONG,
    PRIMARY KEY (CONTEXT_ID,FBT_WF_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)
);

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

commit;