-- SCRIPT DE CREATION DE LA BASE DE DONNEES 

-- ************* PORPHYRY2003 MAPPING SERVER ************

drop table note;
drop table fragment;
drop table source;
drop table documentObject;

--create sequence document_seq start 100 increment 1;

Create table documentObject (
        -- documentObject_Key integer primary key default (nextval('document_seq')),
        key varchar(50) primary key,
        type char NOT NULL -- 0 : Source , 1 : Fragment , 2 : Notes
);

GRANT SELECT, INSERT, DELETE, UPDATE ON documentObject TO PUBLIC;

create table source (
        source_path varchar(100) NOT NULL,
        source_type char NOT NULL -- 0 : Image , 1 : Texte , 2 : Epure
)
inherits (documentObject);
alter table source add Constraint "source_uniq" Primary Key ("key") ;
--alter table source add constraint "source_all_uniq" check (key NOT IN documentObject(key));

GRANT SELECT, INSERT, DELETE, UPDATE ON source TO PUBLIC;

create table fragment (
        fragment_parameters varchar(50),
        fragment_source_key varchar(50) NOT NULL, --references source(key)
        CONSTRAINT source_exists FOREIGN KEY(fragment_source_key) REFERENCES source(key)
)
inherits (documentObject);
alter table fragment add Constraint "fragment_uniq" Primary Key ("key") ;

GRANT SELECT, INSERT, DELETE, UPDATE ON fragment TO PUBLIC;

create table note(
        note_content text
)
inherits (documentObject);
alter table note add Constraint "note_uniq" Primary Key ("key") ;

GRANT SELECT, INSERT, DELETE, UPDATE ON note TO PUBLIC;