-- D:\DDL\postgresql\3V_LIMS_REL03.sql
--
-- Generated for ANSI SQL92 on Wed Jun 13  14:52:01 2007 by Server Generator 10.1.2.6.18
--
--
-- Post generation PostgreSQL specific Changes:
-- 
--  1. change domain FILE to BYTEA
--  2. change domain TECH_ID from NUMERIC(20) to BIGINT
-- 

CREATE DOMAIN DESCRIPTION_80 AS VARCHAR(80);
CREATE DOMAIN BOOLEAN_NUM AS NUMERIC(1);
CREATE DOMAIN DESCRIPTION_1000 AS VARCHAR(1000);
CREATE DOMAIN USER_ID AS VARCHAR(20);
CREATE DOMAIN BUSINESS_ID AS VARCHAR(20);
CREATE DOMAIN FILE_NAME AS VARCHAR(100);
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);
CREATE DOMAIN DIRECTORY_NAME AS VARCHAR(100);
CREATE DOMAIN URL AS VARCHAR(200);
CREATE DOMAIN FILE AS BYTEA;
CREATE DOMAIN CODE AS VARCHAR(20);
CREATE DOMAIN DESCRIPTION_250 AS VARCHAR(250);
CREATE TABLE EXPERIMENT_PROPERTIES  (ID TECH_ID NOT NULL,EXPE_ID TECH_ID NOT NULL,FILE_NAME FILE_NAME NOT NULL,MIME_TYPE VARCHAR(80),VALUE FILE NOT NULL) ;
CREATE TABLE EXPERIMENT_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE PERSONS  (ID TECH_ID NOT NULL,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),USER_ID USER_ID,EMAIL OBJECT_NAME) ;
CREATE TABLE FILE_FORMAT_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE OBSERVABLE_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE PROCEDURES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_1000,EXPE_ID TECH_ID NOT NULL,PCTY_ID TECH_ID NOT NULL,PERS_ID TECH_ID) ;
CREATE TABLE PROCEDURE_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL) ;
CREATE TABLE BARCODES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,LABEL DESCRIPTION_80) ;
CREATE TABLE CONTAINER_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE EXTERNAL_DATA  (DATA_ID TECH_ID NOT NULL,URL URL,HOST_NAME OBJECT_NAME,DIRECTORY DIRECTORY_NAME,FILE_NAME FILE_NAME,FFTY_ID TECH_ID NOT NULL) ;
CREATE TABLE DATA  (ID TECH_ID NOT NULL,OBTY_ID TECH_ID NOT NULL,PROC_ID_ACQUIRED_BY TECH_ID NOT NULL,DATA_ID_DERIVED_FROM TECH_ID,PROC_ID_INPUT_FOR TECH_ID,CONT_ID_ACQUIRED_FROM TECH_ID) ;
CREATE TABLE MATERIAL_TYPES  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80) ;
CREATE TABLE MATERIALS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_250,MATE_ID TECH_ID,MATY_ID TECH_ID NOT NULL) ;
CREATE TABLE MOLECULES  (MATE_ID TECH_ID NOT NULL,SEQUENCE DESCRIPTION_1000,ACCESSION_NUMBER OBJECT_NAME) ;
CREATE TABLE EXPERIMENTS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,EXTY_ID TECH_ID NOT NULL,MATE_ID_STUDY_OBJECT TECH_ID,PERS_ID_REGISTERER TECH_ID,REGISTRATION_TIMESTAMP TIMESTAMP,DESCRIPTION DESCRIPTION_250) ;
CREATE TABLE CONTAINERS  (ID TECH_ID NOT NULL,CODE CODE,COTY_ID TECH_ID NOT NULL,EXPE_ID TECH_ID,BACO_ID TECH_ID,CONT_ID TECH_ID,DISPOSAL_PERMISSION_FLAG BOOLEAN_NUM,DISPOSAL_OCCURRENCE_FLAG BOOLEAN_NUM) ;

ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_PK PRIMARY KEY(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_PK PRIMARY KEY(ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_PK PRIMARY KEY(ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_PK PRIMARY KEY(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PK PRIMARY KEY(ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_PK PRIMARY KEY(ID);
ALTER TABLE BARCODES ADD CONSTRAINT BACO_PK PRIMARY KEY(ID);
ALTER TABLE CONTAINER_TYPES ADD CONSTRAINT COTY_PK PRIMARY KEY(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_PK PRIMARY KEY(DATA_ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PK PRIMARY KEY(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_PK PRIMARY KEY(MATE_ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PK PRIMARY KEY(ID);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE(EXPE_ID,FILE_NAME);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE(CODE);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE(USER_ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_BK_UK UNIQUE(CODE);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PROC_BK_UK UNIQUE(CODE);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_BK_UK UNIQUE(CODE);
ALTER TABLE BARCODES ADD CONSTRAINT BACO_BK_UK UNIQUE(CODE);
ALTER TABLE CONTAINER_TYPES ADD CONSTRAINT COTY_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE(CODE);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_BK_UK UNIQUE(SEQUENCE);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_BK_UK UNIQUE(CODE);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_BK_UK UNIQUE(CODE);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_BARCODE_UK UNIQUE(BACO_ID);


ALTER TABLE CONTAINERS ADD CONSTRAINT AVCON_1181739121_DISPO_000 CHECK (DISPOSAL_PERMISSION_FLAG IN (0, 1)) ;
ALTER TABLE CONTAINERS ADD CONSTRAINT AVCON_1181739121_DISPO_001 CHECK (DISPOSAL_OCCURRENCE_FLAG IN (0, 1)) ;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_EXPE_FK FOREIGN KEY (EXPE_ID)REFERENCES EXPERIMENTS(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PERS_FK FOREIGN KEY (PERS_ID)REFERENCES PERSONS(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_EXPE_FK FOREIGN KEY (EXPE_ID)REFERENCES EXPERIMENTS(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PCTY_FK FOREIGN KEY (PCTY_ID)REFERENCES PROCEDURE_TYPES(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_DATA_FK FOREIGN KEY (DATA_ID)REFERENCES DATA(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_FFTY_FK FOREIGN KEY (FFTY_ID)REFERENCES FILE_FORMAT_TYPES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_DATA_FK FOREIGN KEY (DATA_ID_DERIVED_FROM)REFERENCES DATA(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PROC_INPUT_FOR_FK FOREIGN KEY (PROC_ID_INPUT_FOR)REFERENCES PROCEDURES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PROC_ACQUIRED_BY_FK FOREIGN KEY (PROC_ID_ACQUIRED_BY)REFERENCES PROCEDURES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_OBTY_FK FOREIGN KEY (OBTY_ID)REFERENCES OBSERVABLE_TYPES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_CONT_FK FOREIGN KEY (CONT_ID_ACQUIRED_FROM)REFERENCES CONTAINERS(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATY_FK FOREIGN KEY (MATY_ID)REFERENCES MATERIAL_TYPES(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATE_FK FOREIGN KEY (MATE_ID)REFERENCES MATERIALS(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_MATE_FK FOREIGN KEY (MATE_ID)REFERENCES MATERIALS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_MATE_FK FOREIGN KEY (MATE_ID_STUDY_OBJECT)REFERENCES MATERIALS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_EXTY_FK FOREIGN KEY (EXTY_ID)REFERENCES EXPERIMENT_TYPES(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER)REFERENCES PERSONS(ID);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_COTY_FK FOREIGN KEY (COTY_ID)REFERENCES CONTAINER_TYPES(ID);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_EXPE_FK FOREIGN KEY (EXPE_ID)REFERENCES EXPERIMENTS(ID);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_BACO_FK FOREIGN KEY (BACO_ID)REFERENCES BARCODES(ID);
ALTER TABLE CONTAINERS ADD CONSTRAINT CONT_CONT_FK FOREIGN KEY (CONT_ID)REFERENCES CONTAINERS(ID);

-- D:\DDL\oracle\3V_LIMS_SEQ_PG_002.sql
--
-- Generated for Oracle 10g on Thu May 10  15:49:25 2007 by Server Generator 10.1.2.6.18
-- 
-- Manually adjusted to conform with PostgreSQL sytax
-- 
-----------------------
--  Creating Sequences
------------------------
CREATE SEQUENCE BARCODE_ID_SEQ;

CREATE SEQUENCE BARCODE_TYPE_ID_SEQ;

CREATE SEQUENCE CONTAINER_ID_SEQ;

CREATE SEQUENCE CONTAINER_TYPE_ID_SEQ;

CREATE SEQUENCE DATA_ID_SEQ;

CREATE SEQUENCE EXPERIMENT_ID_SEQ;

CREATE SEQUENCE EXPERIMENT_PROPERTY_ID_SEQ;

CREATE SEQUENCE EXPERIMENT_TYPE_ID_SEQ;

CREATE SEQUENCE FILE_FORMAT_TYPE_ID_SEQ;

CREATE SEQUENCE MATERIAL_ID_SEQ;

CREATE SEQUENCE MATERIAL_TYPE_ID_SEQ;

CREATE SEQUENCE OBSERVABLE_TYPE_ID_SEQ;

CREATE SEQUENCE PERSON_ID_SEQ;

CREATE SEQUENCE PROCEDURE_ID_SEQ;

CREATE SEQUENCE PROCEDURE_TYPE_ID_SEQ;
