/****************************************************************************
 * Schema upgrade - script 1 of 2
 * Run this before any data upgrade scripts
 *
 * Database:         jcr
 * DBMS:             postgres7
 *****************************************************************************/

/*===========================================================================
 * Changes for table: SystemUser
 * Upgrade strategy: changeByCopy
 *===========================================================================*/
-- Drop all table constraints and indexes prior to renaming
ALTER TABLE systemuser
    DROP CONSTRAINT pk_systemuser CASCADE;

ALTER TABLE SystemUser
    RENAME TO tmp_SystemUser;

CREATE TABLE SystemUser (
    systemId             INTEGER,
    username             VARCHAR(20),
    name                 VARCHAR(50),
    isSuperuser          CHAR(1),
    password             VARCHAR(20),
    email                VARCHAR(100)
);


/*===========================================================================
 * Changes for table: FileSet
 * Upgrade strategy: changeInPlace
 *===========================================================================*/
ALTER TABLE fileset
    DROP CONSTRAINT fk_fileset_systemuser CASCADE;


/*===========================================================================
 * Changes for table: Project
 * Upgrade strategy: changeInPlace
 *===========================================================================*/
ALTER TABLE project
    DROP CONSTRAINT fk_project_systemuser CASCADE;

ALTER TABLE project
    DROP CONSTRAINT fk_project_originalfileset CASCADE;

ALTER TABLE project
    DROP CONSTRAINT fk_project_modifiedfileset CASCADE;


/*===========================================================================
 * Changes for table: ProjectStatus
 * Upgrade strategy: changeInPlace
 *===========================================================================*/
ALTER TABLE projectstatus
    DROP CONSTRAINT fk_projectstatus_project CASCADE;

ALTER TABLE projectstatus
    DROP CONSTRAINT fk_projectstatus_changedBy CASCADE;


/*===========================================================================
 * Changes for table: ProjectFile
 * Upgrade strategy: changeInPlace
 *===========================================================================*/
ALTER TABLE projectfile
    DROP CONSTRAINT fk_projectfile_project CASCADE;


/*===========================================================================
 * Changes for table: FileComment
 * Upgrade strategy: changeInPlace
 *===========================================================================*/
ALTER TABLE filecomment
    DROP CONSTRAINT fk_filecomment_projectfile CASCADE;

ALTER TABLE filecomment
    DROP CONSTRAINT fk_filecomment_systemuser_owner CASCADE;

ALTER TABLE filecomment
    DROP CONSTRAINT fk_filecom_systemuser_reviewer CASCADE;

ALTER TABLE filecomment
    DROP CONSTRAINT fk_filecom_systemuser_revaction CASCADE;

ALTER TABLE filecomment
    DROP CONSTRAINT fk_filecom_systemuser_actioner CASCADE;


/*===========================================================================
 * Table:            Session
 * Description:      Contains details of HTTP sessions for logged-in users
 *===========================================================================*/
CREATE TABLE Session (
    systemId             INTEGER NOT NULL,
    sessionId            VARCHAR(20) NOT NULL,
    systemUser           INTEGER NOT NULL,
    lastRequest          TIMESTAMP NOT NULL,
    data                 TEXT
);