-- D:\DDL\postgresql\CIFEX-schema-001.sql
--
-- Generated for ANSI SQL92 on Thu Jan 24  11:20:39 2008 by Server Generator 10.1.2.6.18
------------------------------------------------------------------------------------
--
--  Post-Generation Modifications:
--
--  1. Added the Sequence and Index sections
--  2. Add qualifiers to the foreigen key constraints:
--     FILE_USER_FK -> ON DELETE SET NULL
--     FISH_USER_FK -> ON DELETE CASCADE
--     FISH_FILE_FK -> ON DELETE CASCADE
--
--  Oracle Specific Details removed or modified:
--  1. Changed domain BOOLEAN_CHAR from CHAR(5) DEFAULT FALSE to BOOLEAN DEFAULT FALSE
--  2. Removed the check constraints required by Oracle for allowing only values TRUE or FALSE
--     this applies to AVCON_1197023825_IS_MA_000 CHECK from the
--     columns IS_EXTERNALLY_AUTHENTICATED, IS_ADMIN and IS_PERMANENT
--  3. At the table level, changed the DEFAULT value from SYSDATE to CURRENT_TIMESTAMP
--  4. Changed domain TECH_ID from NUMERIC(20) to BIGINT
------------------------------------------------------------------------------------

-- Creating domains

CREATE DOMAIN HASH AS VARCHAR(32);
CREATE DOMAIN CHECKSUM32 AS INTEGER;
CREATE DOMAIN FILE_NAME AS VARCHAR(250);
CREATE DOMAIN PATH_NAME AS VARCHAR(300);
CREATE DOMAIN COMMENT AS VARCHAR(1000);
CREATE DOMAIN BOOLEAN_CHAR AS BOOLEAN DEFAULT FALSE;
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN SIZE AS BIGINT;
CREATE DOMAIN DURATION AS BIGINT;
CREATE DOMAIN TIME_STAMP AS TIMESTAMP;
CREATE DOMAIN CONTENT_TYPE AS VARCHAR(120);
CREATE DOMAIN USER_ID AS VARCHAR(50);
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);

-- Creating tables

CREATE TABLE FILES  (ID TECH_ID NOT NULL,NAME FILE_NAME NOT NULL,PATH PATH_NAME NOT NULL,COMMENT COMMENT,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,EXPIRATION_TIMESTAMP TIME_STAMP NOT NULL,USER_ID TECH_ID,CONTENT_TYPE CONTENT_TYPE, SIZE SIZE, CRC32_CHECKSUM CHECKSUM32);
CREATE TABLE USERS  (ID TECH_ID NOT NULL,USER_ID USER_ID NOT NULL, EMAIL OBJECT_NAME NOT NULL,FULL_NAME OBJECT_NAME,PASSWORD_HASH HASH,IS_EXTERNALLY_AUTHENTICATED BOOLEAN_CHAR DEFAULT 'FALSE',IS_ADMIN BOOLEAN_CHAR NOT NULL DEFAULT 'FALSE',IS_PERMANENT BOOLEAN_CHAR NOT NULL DEFAULT 'FALSE',IS_ACTIVE BOOLEAN_CHAR NOT NULL DEFAULT 'TRUE',REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,USER_ID_REGISTRATOR TECH_ID, EXPIRATION_TIMESTAMP TIME_STAMP, MAX_UPLOAD_SIZE SIZE, FILE_RETENTION DURATION);
CREATE TABLE FILE_SHARES  (ID TECH_ID NOT NULL,FILE_ID TECH_ID NOT NULL,USER_ID TECH_ID NOT NULL);

-- Creating primary key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_PK PRIMARY KEY(ID);
ALTER TABLE USERS ADD CONSTRAINT USER_PK PRIMARY KEY(ID);
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_PK PRIMARY KEY(ID);

-- Creating unique key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_BK_UK UNIQUE(PATH);
ALTER TABLE USERS ADD CONSTRAINT USER_BK_UK UNIQUE(USER_ID);
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_BK_UK UNIQUE(USER_ID,FILE_ID);

-- Creating foreign key constraints

ALTER TABLE FILES ADD CONSTRAINT FILE_USER_FK FOREIGN KEY (USER_ID)REFERENCES USERS(ID) ON DELETE SET NULL;
ALTER TABLE USERS ADD CONSTRAINT USER_REGISTRATOR_FK FOREIGN KEY (USER_ID_REGISTRATOR)REFERENCES USERS(ID) ON DELETE SET NULL;
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE CASCADE;
ALTER TABLE FILE_SHARES ADD CONSTRAINT FISH_FILE_FK FOREIGN KEY (FILE_ID) REFERENCES FILES(ID) ON DELETE CASCADE;

-- Creating check constraints


-- Creating sequences

CREATE SEQUENCE FILE_ID_SEQ;
CREATE SEQUENCE FILE_SHARE_ID_SEQ;
CREATE SEQUENCE USER_ID_SEQ;

-- Creating indexes

CREATE INDEX FILE_USER_FK_I ON FILES (USER_ID);
CREATE INDEX FISH_FILE_FK_I ON FILE_SHARES (FILE_ID);
CREATE INDEX FISH_USER_FK_I ON FILE_SHARES (USER_ID);

