Skip to content
Snippets Groups Projects
Select Git revision
  • 2fac10cd89dbd5d36a64411f7e44df9ca9a831c8
  • master default protected
  • subtemplate
  • release_7.0.6
  • php84
  • 6.x
  • v5.x-test
  • 5x
  • 7.1.0
  • 7.0.6
  • 7.0.5
  • 7.0.4
  • 7.0.3
  • 7.0.2
  • 7.0.1
  • 7.0.0
  • 6.1.7
  • 6.1.6
  • 6.1.5
  • 6.1.4
  • 6.1.3
  • 6.1.2
  • 6.1.1
  • 6.1.0
  • 6.0.3
  • 6.0.2
  • 5.0.6
  • 6.0.1
28 results

Module.php

Blame
  • 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';