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

CREATE PROC fbt70Update  
@sTableName varchar(256),
@sFieldName varchar(256)
AS
BEGIN
DECLARE @fbtConstraint varchar(256)
SELECT @fbtConstraint=name
      FROM      sysobjects
      WHERE     xtype = 'PK'
      AND       parent_obj = object_id(@sTableName)
EXEC('ALTER TABLE '+@sTableName+' DROP CONSTRAINT '+@fbtConstraint);
EXEC('ALTER TABLE '+@sTableName+' ADD CONTEXT_ID INT DEFAULT 0 NOT NULL');
EXEC('ALTER TABLE '+@sTableName+' ADD PRIMARY KEY (CONTEXT_ID, '+@sFieldName+')');
RETURN 1
END
GO

use fbt;
begin tran fbtupdate

--  update tables using stored procedures above
EXEC fbt70Update 'FBT_ATTACHMENT', 'FBT_ATTACHMENT_ID'
EXEC fbt70Update 'FBT_BUG_STRUCT', 'FBT_STRUCT_ID'
EXEC fbt70Update 'FBT_BUG_STRUCT_ENTRY', 'FBT_STRUCT_ENTRY_ID'
EXEC fbt70Update 'FBT_BUG_USER_FIELD', 'FBT_USER_FIELD_ID'
EXEC fbt70Update 'FBT_BUG_USER_FIELD_ENTRY', 'FBT_USER_FIELD_ENTRY_ID'
EXEC fbt70Update 'FBT_CFG_DEF', 'FBT_CFG_ID'
EXEC fbt70Update 'FBT_CHART_DEF', 'FBT_CHART_ID'
EXEC fbt70Update 'FBT_COLORCODE_DEF', 'FBT_COLORCODE_ID'
EXEC fbt70Update 'FBT_EVENT_DEF', 'FBT_EVENT_ID'
EXEC fbt70Update 'FBT_FLD_DEF', 'FBT_FLD_ID'
EXEC fbt70Update 'FBT_FLT_DEF', 'FBT_FLT_ID'
EXEC fbt70Update 'FBT_GROUP_DEF', 'FBT_GROUP_ID'
EXEC fbt70Update 'FBT_HS_DEF', 'FBT_HS_CHILD_ID'
EXEC fbt70Update 'FBT_MAILRULE_DEF', 'FBT_MAILRULE_ID'
EXEC fbt70Update 'FBT_PROJECT_DEF', 'FBT_PROJECT_ID'
EXEC fbt70Update 'FBT_REPORT_DEF', 'FBT_REPORT_ID'
EXEC fbt70Update 'FBT_RET_DEF', 'FBT_RET_ID'
EXEC fbt70Update 'FBT_USER_DEF', 'FBT_USER_ID'
EXEC fbt70Update 'FBT_WF_DEF', 'FBT_WF_ID'

--  drop old indexes
DROP INDEX FBT_BUG_STRUCT_ENTRY.FBSE_FSID;
DROP INDEX FBT_BUG_USER_FIELD.FBUF_FSID;
DROP INDEX FBT_BUG_USER_FIELD_ENTRY.FBUFE_FSID;
DROP INDEX FBT_BUG_USER_FIELD_ENTRY.FBUFE_FSEID;

--  create new indexes with new primary keys
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 BIGINT, 
    CLASSNAME VARCHAR(255), 
    OBJECT_DATA TEXT,
    FIT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,SYSTEM_ID,FIT_OBJECT_ID)
);

-- New in Version 7.04
-- main table for holding attachment info (ie: tsA1.tsa)
CREATE TABLE TS_ATTACHMENT (
    CONTEXT_ID INT,
    TS_ATTACHMENT_ID BIGINT,
    TS_STRUCT_ID BIGINT,
    TS_CREATED_BY VARCHAR(255),
    TS_ATTACH_FILENAME VARCHAR(255),
    TS_ORIGINAL_FILENAME VARCHAR(255),
    TS_CONTENT_TYPE VARCHAR(255),
    TS_ATTACHMENT_DATE DATETIME,
    TS_COMMENTS VARCHAR(255),
    TS_CONTENT  IMAGE,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_ATTACHMENT_ID)
);
-- no need for this any more
drop proc fbt70Update

commit tran fbtupdate