oracle.sql

create table SOURCE
(
	ID NUMBER not null constraint SOURCE_PK primary key,
	CODE VARCHAR2(64 char) not null constraint SOURCE_CODE_UN unique,
	LIBELLE VARCHAR2(128 char) not null,
	IMPORTABLE NUMBER(1) not null
);

INSERT INTO SOURCE (ID, CODE, LIBELLE, IMPORTABLE) VALUES (1, 'app', 'Application', 0);
--INSERT INTO SOURCE (ID, CODE, LIBELLE, IMPORTABLE) VALUES (2, 'octopus', 'Référentiel Octopus', 1);
--INSERT INTO SOURCE (ID, CODE, LIBELLE, IMPORTABLE) VALUES (3, 'apogee', 'Apogée', 1);

/*
--
-- Ajout des colonnes SOURCE_ID et SOURCE_CODE dans une table destination.
--
alter table TABLE_DESTINATION add SOURCE_CODE VARCHAR2(64 char) not null; -- identifiant unique commun
alter table TABLE_DESTINATION add SOURCE_ID NUMBER not null;
alter table TABLE_DESTINATION add constraint TABLE_DESTINATION_SOURCE_FK foreign key (SOURCE_ID) references SOURCE(ID) on delete cascade;
create unique index TABLE_DESTINATION_SOURCE_CODE_UN on TABLE_DESTINATION(SOURCE_CODE);

--
-- Ajout des colonnes de gestion de l'historique dans une table destination.
--
alter table TABLE_DESTINATION add CREATED_BY NUMBER not null;
alter table TABLE_DESTINATION add CREATED_ON DATE default SYSDATE not null;
alter table TABLE_DESTINATION add UPDATED_BY NUMBER;
alter table TABLE_DESTINATION add UPDATED_ON DATE;
alter table TABLE_DESTINATION add DELETED_BY NUMBER;
alter table TABLE_DESTINATION add DELETED_ON DATE;
alter table TABLE_DESTINATION add constraint TABLE_DESTINATION_HC_FK foreign key (CREATED_BY) references UTILISATEUR(ID) on delete cascade;
alter table TABLE_DESTINATION add constraint TABLE_DESTINATION_HM_FK foreign key (UPDATED_BY) references UTILISATEUR(ID) on delete cascade;
alter table TABLE_DESTINATION add constraint TABLE_DESTINATION_HD_FK foreign key (DELETED_BY) references UTILISATEUR(ID) on delete cascade;
*/


--
-- Tables nécessaires pour l'observation de la synchro.
--
create table IMPORT_OBSERV (
    ID NUMBER not null constraint IMPORT_OBSERV_PK primary key,
    CODE VARCHAR2(50 char) not null constraint IMPORT_OBSERV_CODE_UN unique,
    TABLE_NAME VARCHAR2(50 char) not null,
    COLUMN_NAME VARCHAR2(50 char) not null,
    OPERATION VARCHAR2(50 char) default 'UPDATE' not null,
    TO_VALUE VARCHAR2(1000 char),
    DESCRIPTION VARCHAR2(200 char),
    ENABLED NUMBER(1) default 0 not null,
    FILTER CLOB,
    constraint IMPORT_OBSERV_UN unique (TABLE_NAME, COLUMN_NAME, OPERATION, TO_VALUE)
);
create table IMPORT_OBSERV_RESULT (
    ID NUMBER not null constraint IMPORT_OBSERV_RESULT_PK primary key,
    IMPORT_OBSERV_ID NUMBER not null constraint IMPORT_OBSERV_RESULT_IOE_FK references IMPORT_OBSERV on delete cascade,
    DATE_CREATION DATE default SYSDATE not null,
    SOURCE_CODE VARCHAR2(64 char) not null,
    RESULTAT CLOB not null
);
create sequence IMPORT_OBSERV_ID_SEQ;
create sequence IMPORT_OBSERV_RESULT_ID_SEQ;
/*
-- Exemple :
INSERT INTO IMPORT_OBSERV (ID, CODE, TABLE_NAME, COLUMN_NAME, OPERATION, TO_VALUE, FILTER, DESCRIPTION, ENABLED)
VALUES (IMPORT_OBSERV_ID_SEQ.nextval, 'RESULTAT_PASSE_A_ADMIS', 'THESE', 'RESULTAT', 'UPDATE', '1', 'ETABLISSEMENT_ID IN (SELECT ID FROM ETABLISSEMENT WHERE SOURCE_CODE = ''UCN'')', 'Le résultat de la thèse passe à 1 (admis)', 1);
*/

create table IMPORT_LOG
(
    id NUMBER primary key,
    type varchar2(128) not null,
    name varchar2(128) not null,
    success int(1) not null,
    log clob not null,
    started_on date not null,
    ended_on date not null,
    import_hash varchar2(64)
);
create sequence IMPORT_LOG_ID_SEQ;