postgresql.sql

--
-- Table des sources.
--
create table source
(
    id bigint not null constraint source_pkey primary key,
    code varchar(64) not null constraint source_code_key unique,
    libelle varchar(128) not null,
    importable boolean not null
);
comment on table source is 'Sources de données, importables ou non, ex: Apogée, Physalis.';

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

/*
--
-- Ajout des colonnes SOURCE_ID et SOURCE_CODE dans une table destination.
--
alter table TABLE_DESTINATION add SOURCE_CODE VARCHAR(64) not null; -- identifiant unique commun
alter table TABLE_DESTINATION add SOURCE_ID INTEGER 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 INTEGER not null;
alter table TABLE_DESTINATION add CREATED_ON TIMESTAMP WITHOUT TIME ZONE default now() not null;
alter table TABLE_DESTINATION add UPDATED_BY INTEGER;
alter table TABLE_DESTINATION add UPDATED_ON TIMESTAMP WITHOUT TIME ZONE;
alter table TABLE_DESTINATION add DELETED_BY INTEGER;
alter table TABLE_DESTINATION add DELETED_ON TIMESTAMP WITHOUT TIME ZONE;
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 des synchros.
--
create table IMPORT_OBSERV (
    ID serial not null constraint IMPORT_OBSERV_PK primary key,
    CODE VARCHAR(50) not null constraint IMPORT_OBSERV_CODE_UN unique,
    TABLE_NAME VARCHAR(50) not null,
    COLUMN_NAME VARCHAR(50) not null,
    OPERATION VARCHAR(50) default 'UPDATE' not null,
    TO_VALUE VARCHAR(1000),
    DESCRIPTION VARCHAR(200),
    ENABLED smallint default 0 not null,
    FILTER text,
    constraint IMPORT_OBSERV_UN unique (TABLE_NAME, COLUMN_NAME, OPERATION, TO_VALUE)
);
create table IMPORT_OBSERV_RESULT (
    ID serial not null constraint IMPORT_OBSERV_RESULT_PK primary key,
    IMPORT_OBSERV_ID integer not null constraint IMPORT_OBSERV_RESULT_IOE_FK references IMPORT_OBSERV on delete cascade,
    DATE_CREATION DATE default now() not null,
    SOURCE_CODE VARCHAR(64) not null,
    RESULTAT text 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 (nextval('IMPORT_OBSERV_ID_SEQ'), '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 bigserial primary key,
    type varchar(128) not null,
    name varchar(128) not null,
    success boolean not null,
    log text not null,
    started_on timestamp not null,
    ended_on timestamp not null,
    import_hash varchar(64)
);