Select Git revision
postfix-satellite.pp
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
module.sql 8.00 KiB
CREATE TABLE SYNC_LOG
(
ID NUMBER(*, 0) NOT NULL
, DATE_SYNC TIMESTAMP(6) NOT NULL
, MESSAGE CLOB NOT NULL
, TABLE_NAME VARCHAR2(30 CHAR)
, SOURCE_CODE VARCHAR2(200 CHAR)
, CONSTRAINT SYNC_LOG_PK PRIMARY KEY (ID)
USING INDEX (CREATE UNIQUE INDEX SYNC_LOG_PK ON SYNC_LOG (ID ASC)) ENABLE
);
CREATE TABLE IMPORT_TABLES
(
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL
, KEY_COLUMNS VARCHAR2(1000 CHAR)
, SYNC_FILTRE VARCHAR2(2000 CHAR)
, SYNC_ENABLED NUMBER(1, 0) DEFAULT 0 NOT NULL
, SYNC_JOB VARCHAR2(40 CHAR)
, SYNC_HOOK_BEFORE VARCHAR2(4000 CHAR)
, SYNC_HOOK_AFTER VARCHAR2(4000 CHAR)
, SYNC_NON_IMPORTABLES NUMBER(1, 0) DEFAULT 0 NOT NULL
, ORDRE NUMBER
, CONSTRAINT IMPORT_TABLES_PK PRIMARY KEY (TABLE_NAME)
USING INDEX (CREATE UNIQUE INDEX IMPORT_TABLES_PK ON IMPORT_TABLES (TABLE_NAME ASC)) ENABLE
);
/
create sequence SYNC_LOG_ID_SEQ;
/
CREATE OR REPLACE VIEW V_IMPORT_TAB_COLS AS
WITH importable_tables (table_name )AS (
SELECT
t.table_name
FROM
user_tab_cols c
join user_tables t on t.table_name = c.table_name
WHERE
c.column_name = 'SOURCE_CODE'
MINUS
SELECT
mview_name table_name
FROM
USER_MVIEWS
), c_values (table_name, column_name, c_table_name, c_column_name) AS (
SELECT
tc.table_name,
tc.column_name,
pcc.table_name c_table_name,
pcc.column_name c_column_name
FROM
user_tab_cols tc
JOIN USER_CONS_COLUMNS cc ON cc.table_name = tc.table_name AND cc.column_name = tc.column_name
JOIN USER_CONSTRAINTS c ON c.constraint_name = cc.constraint_name
JOIN USER_CONSTRAINTS pc ON pc.constraint_name = c.r_constraint_name
JOIN USER_CONS_COLUMNS pcc ON pcc.constraint_name = pc.constraint_name
WHERE
c.constraint_type = 'R' AND pc.constraint_type = 'P'
)
SELECT
tc.table_name,
tc.column_name,
CASE WHEN ',' || it.key_columns || ',' LIKE '%,' || tc.column_name || ',%' THEN 1 ELSE 0 END is_key,
tc.data_type,
CASE WHEN tc.char_length = 0 THEN NULL ELSE tc.char_length END length,
CASE WHEN tc.nullable = 'Y' THEN 1 ELSE 0 END nullable,
CASE WHEN tc.data_default IS NOT NULL THEN 1 ELSE 0 END has_default,
cv.c_table_name,
cv.c_column_name,
CASE WHEN stc.table_name IS NULL THEN 0 ELSE 1 END AS import_actif
FROM
user_tab_cols tc
JOIN importable_tables t ON t.table_name = tc.table_name
LEFT JOIN import_tables it ON it.table_name = tc.table_name
LEFT JOIN c_values cv ON cv.table_name = tc.table_name AND cv.column_name = tc.column_name
LEFT JOIN user_tab_cols stc ON stc.table_name = 'SRC_' || tc.table_name AND stc.column_name = tc.column_name
WHERE
tc.column_name not like 'HISTO_%'
AND tc.table_name <> 'SYNC_LOG'
ORDER BY
it.ordre, tc.table_name, tc.column_id;
/
create or replace PACKAGE UNICAEN_IMPORT AS
z__SYNC_FILRE__z CLOB DEFAULT '';
z__IGNORE_UPD_COLS__z CLOB DEFAULT '';
PROCEDURE set_current_user(p_current_user IN INTEGER);
FUNCTION get_current_user return INTEGER;
FUNCTION get_current_annee RETURN INTEGER;
PROCEDURE set_current_annee (p_current_annee INTEGER);
FUNCTION IN_COLUMN_LIST( VALEUR VARCHAR2, CHAMPS CLOB ) RETURN NUMERIC;
PROCEDURE REFRESH_MV( mview_name varchar2 );
PROCEDURE SYNC_LOG( message CLOB, table_name VARCHAR2 DEFAULT NULL, source_code VARCHAR2 DEFAULT NULL );
PROCEDURE SYNCHRONISATION( table_name VARCHAR2, SYNC_FILRE CLOB DEFAULT '', IGNORE_UPD_COLS CLOB DEFAULT '' );
END UNICAEN_IMPORT;
/
create or replace PACKAGE BODY UNICAEN_IMPORT AS
v_current_user INTEGER;
v_current_annee INTEGER;
FUNCTION get_current_user RETURN INTEGER IS
BEGIN
IF v_current_user IS NULL THEN
-- 1 à remplacer par un ID d'utilisateur (USER.ID)
-- Permet de préciser quel est l'utilisateur qui sera indiqué lorsque UnicaenImport fera ses synchros
v_current_user := 1;
END IF;
RETURN v_current_user;
END get_current_user;
PROCEDURE set_current_user (p_current_user INTEGER) is
BEGIN
v_current_user := p_current_user;
END set_current_user;
FUNCTION get_current_annee RETURN INTEGER IS
BEGIN
IF v_current_annee IS NULL THEN
-- NULL A remplacer par un NUMBER qui précise l'année à partir de laquelle vont
-- se faire les imports (2018 pour 2018/2019 par exemple)
v_current_annee := null;
END IF;
RETURN v_current_annee;
END get_current_annee;
PROCEDURE set_current_annee (p_current_annee INTEGER) IS
BEGIN
v_current_annee := p_current_annee;
END set_current_annee;
PROCEDURE SYNCHRONISATION( table_name VARCHAR2, SYNC_FILRE CLOB DEFAULT '', IGNORE_UPD_COLS CLOB DEFAULT '' ) IS
ok NUMERIC(1);
BEGIN
SELECT COUNT(*) INTO ok FROM import_tables it WHERE it.table_name = SYNCHRONISATION.table_name AND it.sync_enabled = 1 AND rownum = 1;
IF 1 = ok THEN
z__SYNC_FILRE__z := SYNCHRONISATION.SYNC_FILRE;
z__IGNORE_UPD_COLS__z := SYNCHRONISATION.IGNORE_UPD_COLS;
EXECUTE IMMEDIATE 'BEGIN UNICAEN_IMPORT_AUTOGEN_PROCS__.' || table_name || '(); END;';
END IF;
END;
PROCEDURE REFRESH_MV( mview_name varchar2 ) IS
BEGIN
DBMS_MVIEW.REFRESH(mview_name, 'C');
EXCEPTION WHEN OTHERS THEN
SYNC_LOG( SQLERRM, mview_name );
END;
PROCEDURE SYNC_LOG( message CLOB, table_name VARCHAR2 DEFAULT NULL, source_code VARCHAR2 DEFAULT NULL ) IS
BEGIN
INSERT INTO SYNC_LOG("ID","DATE_SYNC","MESSAGE","TABLE_NAME","SOURCE_CODE") VALUES (SYNC_LOG_ID_SEQ.NEXTVAL, SYSDATE, message,table_name,source_code);
END SYNC_LOG;
FUNCTION IN_COLUMN_LIST( VALEUR VARCHAR2, CHAMPS CLOB ) RETURN NUMERIC IS
BEGIN
IF REGEXP_LIKE(CHAMPS, '(^|,)[ \t\r\n\v\f]*' || VALEUR || '[ \t\r\n\v\f]*(,|$)') THEN RETURN 1; END IF;
RETURN 0;
END;
END UNICAEN_IMPORT;
/
create or replace PACKAGE UNICAEN_IMPORT_AUTOGEN_PROCS__ IS
END UNICAEN_IMPORT_AUTOGEN_PROCS__;
/
create or replace PACKAGE BODY UNICAEN_IMPORT_AUTOGEN_PROCS__ IS
FUNCTION IN_COLUMN_LIST( VALEUR VARCHAR2, CHAMPS CLOB ) RETURN NUMERIC IS
BEGIN
IF REGEXP_LIKE(CHAMPS, '(^|,)[ \t\r\n\v\f]*' || VALEUR || '[ \t\r\n\v\f]*(,|$)') THEN RETURN 1; END IF;
RETURN 0;
END;
END UNICAEN_IMPORT_AUTOGEN_PROCS__;
/
/*********************************************************************************************
* Privilèges
*********************************************************************************************/
insert into CATEGORIE_PRIVILEGE (ID, CODE, LIBELLE, ORDRE) values (
CATEGORIE_PRIVILEGE_ID_SEQ.nextval,
'import',
'Import',
2
);
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'ecarts',
'Écarts',
1
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'maj',
'Mise à jour',
2
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'tbl',
'Tableau de bord',
3
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'vues-procedures',
'Gestion des vues et procédures',
4
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'sources-edition',
'Sources (édition)',
5
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'sources-visualisation',
'Sources (visualisation)',
6
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'tables-edition',
'Tables (édition)',
5
from CATEGORIE_PRIVILEGE cat where code = 'import';
/
insert into PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) select
PRIVILEGE_ID_SEQ.nextval,
cat.id,
'tables-visualisation',
'Tables (visualisation)',
6
from CATEGORIE_PRIVILEGE cat where code = 'import';