-- Oracle Schema Changes for advanced attachment support
--

USE test;

## begin tran fbtupdate;

ALTER TABLE FBT_ATTACHMENT ADD (
    FBT_VERSION VARCHAR(255),
    FBT_FOLDER_ID NUMBER,
    FBT_ATTACHMENT_VERSION_ID NUMBER
);

-- table for holding attachment version info (ie: A1.att)
CREATE TABLE FBT_ATTACHMENT_VERSION (
    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_VERSION VARCHAR(255),
    FBT_FOLDER_ID NUMBER,
    FBT_ATTACHMENT_VERSION_ID NUMBER,
    FBT_CONTENT  LONG RAW,
    FBT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,FBT_ATTACHMENT_VERSION_ID)
);

-- table for holding attachment folder info (ie: f1.fol)
CREATE TABLE FBT_ATTACHMENT_FOLDER (
    CONTEXT_ID INT,
    FBT_FOLDER_ID NUMBER,
    FBT_STRUCT_ID NUMBER,
    FBT_GLOBAL INT,
    FBT_CREATED_BY VARCHAR(255),
    FBT_FOLDER_DATE DATE,
    FBT_FOLDER_NAME VARCHAR(255),
    FBT_PARENT_FOLDER_ID NUMBER,
    FBT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,FBT_FOLDER_ID)
);

ALTER TABLE TS_ATTACHMENT ADD (
    TS_VERSION VARCHAR(255),
    TS_FOLDER_ID NUMBER,
    TS_ATTACHMENT_VERSION_ID NUMBER
);

-- table for holding attachment version info (ie: A1.att)
CREATE TABLE TS_ATTACHMENT_VERSION (
    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_VERSION VARCHAR(255),
    TS_FOLDER_ID NUMBER,
    TS_ATTACHMENT_VERSION_ID NUMBER,
    TS_CONTENT  LONG RAW,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_ATTACHMENT_VERSION_ID)
);

-- table for holding attachment folder info (ie: f1.fol)
CREATE TABLE TS_ATTACHMENT_FOLDER (
    CONTEXT_ID INT,
    TS_FOLDER_ID NUMBER,
    TS_STRUCT_ID NUMBER,
    TS_GLOBAL INT,
    TS_CREATED_BY VARCHAR(255),
    TS_FOLDER_DATE DATE,
    TS_FOLDER_NAME VARCHAR(255),
    TS_PARENT_FOLDER_ID NUMBER,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_FOLDER_ID)
);

commit;