// il est nécessaire de donner le droit à l'utilisateur lbd d'utiliser ctx_dll
-- pour mettre à jour l'index documentaire.
grant execute on ctxsys.ctx_ddl to lbd;

-- création d'une séquence pour alimenter la colonne Numero de la table draft
--et notes.

CREATE SEQUENCE seq_numero_draft INCREMENT BY 1

-- Start of DDL Script for Table LBD.DRAFT
-- Generated 18.02.2003 11:27:11 from LBD@ES25_LETO.CPLN.CH

CREATE TABLE draft
    (name                           VARCHAR2(128) NOT NULL,
    mime_type                      VARCHAR2(128),
    doc_size                       NUMBER,
    dad_charset                    VARCHAR2(128),
    last_updated                   DATE,
    content_type                   VARCHAR2(128),
    blob_content                   BLOB,
    numero                         NUMBER)

/


-- Indexes for DRAFT

CREATE INDEX inx_draft ON draft(blob_content) indextype is ctxsys.context


-- Constraints for DRAFT

ALTER TABLE draft
ADD UNIQUE (name)
/
ALTER TABLE draft
ADD CONSTRAINT xu_numero UNIQUE (numero)
USING INDEX
/

-- Triggers for DRAFT

CREATE OR REPLACE TRIGGER tr_incr_draft
BEFORE INSERT 
ON draft
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
numval integer;
begin
select seq_numero_draft.nextval into numval from dual;
:new.numero:=numval;
end;
/



-- Start of DDL Script for Table LBD.NOTES
-- Generated 18.02.2003 11:37:42 from LBD@ES25_LETO.CPLN.CH

CREATE TABLE notes
    (numero                         NUMBER(9) NOT NULL,
    titre                          VARCHAR2(50),
    creation                       DATE)
/


-- End of DDL Script for Table LBD.NOTES

-- Foreign Key
ALTER TABLE notes
ADD CONSTRAINT fk_numero FOREIGN KEY (numero)
REFERENCES draft (numero) ON DELETE SET NULL
/
-- End of DDL script for Foreign Key(s)
-- End of DDL Script for Table LBD.DRAFT