------------------------------------------------------------------------------
--
--  SequenceObject Database 
--
--  Author: Andre Yoshiaki Kashiwabara   <yoshiaki@ime.usp.br>
--
------------------------------------------------------------------------------

DROP SEQUENCE get_id;
DROP TABLE  pipeline;
DROP TABLE PipelineSequence ;
DROP TABLE Sequence ;
DROP TABLE Sequence_Status ;
DROP TABLE Operation ;
DROP TABLE Program ;
DROP TABLE invalidation ;
DROP TABLE masking ;
DROP TABLE trimming ;

CREATE SEQUENCE get_id;

------------------------------------------------------------------------------
--
--   ENTITIES 
--
------------------------------------------------------------------------------

CREATE TABLE Pipeline (
	pipeline_id INT8 NOT NULL,
	name VARCHAR(255) NOT NULL,
	config_file OID,
	date timestamp without time zone
);

CREATE TABLE PipelineSequence (
	pipeline_id INT8 NOT NULL,
	sequence_id INT8 NOT NULL
);

CREATE TABLE Sequence (
        sequence_id INT8 NOT NULL,
	status_id INT8 NOT NULL,
        sequence_name TEXT NOT NULL,
	sequence_file OID
);

CREATE TABLE Sequence_Status (
	status_id INT8 NOT NULL,
 	name VARCHAR(255),
	description VARCHAR(255)
);

CREATE TABLE Operation (
        operation_id INT8 NOT NULL,
        sequence_id INT8 NOT NULL,
	program_id INT8 NOT NULL,
        operation_key VARCHAR(25),
	operation_order INTEGER,
	date timestamp without time zone
);

CREATE TABLE Program (
        program_id INT8 NOT NULL,
        program_name VARCHAR(255),
	version VARCHAR(255),
	arguments TEXT,	
	program_type VARCHAR(255),
	parent_program INT8
);

-------------------------------------------------------------------------------
--
--   Operation
--
-------------------------------------------------------------------------------

CREATE TABLE invalidation (
        operation_id INT8 NOT NULL,
        invalid BOOLEAN
) ;

CREATE TABLE masking (
        masked_region_id INT8 DEFAULT NEXTVAL('get_id') NOT NULL,
        operation_id INT8 NOT NULL,
        first_base INT8,
        last_base INT8,
        masked_sequence_name TEXT
);

CREATE TABLE trimming (
        trimmed_region_id INT8 DEFAULT NEXTVAL('get_id') NOT NULL,
        operation_id INT8 NOT NULL,
        size INT8,
        orientation VARCHAR(5)
);

\i functions.sql

\i status.sql
