Jean-Philippe Metivier
db-import

Repository

composer require unicaen/unicaen-db-import
cp -n vendor/unicaen/db-import/config/unicaen-db-import.global.php.dist config/autoload/unicaen-db-import.global.php
cp -n vendor/unicaen/db-import/config/unicaen-db-import.local.php.dist config/autoload/unicaen-db-import.local.php
php public/index.php run import --name "Import_PFI_SIFAC"
php public/index.php run import --all
created_on TIMESTAMP(0) WITH TIME ZONE DEFAULT LOCALTIMESTAMP(0) NOT NULL
updated_on TIMESTAMP(0) WITH TIME ZONE
deleted_on TIMESTAMP(0) WITH TIME ZONE
return [
    /**
     * Configuration du module UnicaenDbImport.
     */
    'import' => [
        /**
         * Liste des imports.
         */
        'imports' => [
            [
                /**
                 * Petit nom (unique) de l'import.
                 */
                'name' => 'Import_PFI_SIFAC',

                /**
                 * Configuration de la source de données à importer :
                 * - 'name'               : petit nom (unique) de la source
                 * - 'table'              : nom de la table source contenant les données à importer
                 * - 'select'             : select SQL de mise en forme des données source à importer (NB: antinomique avec 'table')
                 * - 'connection'         : identifiant de la connexion Doctrine à la base de données source
                 * - 'source_code_column' : nom de la colonne dans la table/vue source contenant l'identifiant unique
                 * - 'columns'            : liste ordonnée des noms des colonnes à prendre en compte dans la table/vue source
                 */
                'source' => [
                    'name'               => 'PFI SIFAC',
                    'connection'         => 'doctrine.connection.orm_oracle',
                    'source_code_column' => 'CODE',
                    'columns'            => ['LIBELLE', 'FLECHE', 'DEBUT_VALIDITE', 'FIN_VALIDITE'],
                    'select'             => <<<'EOT'
select distinct
  MEASURE                                               CODE,
  SHORT_DESC                                            LIBELLE,
  ZFLECHE                                               FLECHE,
  to_char(TO_DATE(VALID_FROM,'YYYYMMDD'),'YYYY-MM-DD')  DEBUT_VALIDITE,
  to_char(TO_DATE(VALID_TO,'YYYYMMDD'),'YYYY-MM-DD')    FIN_VALIDITE
  from (
    SELECT
      SAPSR3.FMMEASURE.MEASURE,
      SAPSR3.FMMEASURET.SHORT_DESC,
      SAPSR3.FMMEASURE.ZFLECHE,
      SAPSR3.FMMEASURE.VALID_FROM,
      SAPSR3.FMMEASURE.VALID_TO
    FROM
      SAPSR3.FMMEASURE,
      SAPSR3.FMMEASURET
    WHERE
      ( (SAPSR3.FMMEASURE.CLIENT in  (select A.MANDT from SAPSR3.FM01H A where A.MANDT = '500') OR SAPSR3.FMMEASURE.CLIENT is null)
    and (SAPSR3.FMMEASURE.FMAREA in  (select A.FIKRS from SAPSR3.FM01H A where A.FIKRS = '1010')  or SAPSR3.FMMEASURE.FMAREA is null)  )
      AND  ( SAPSR3.FMMEASURE.CLIENT=SAPSR3.FMMEASURET.CLIENT(+)  )
      AND  ( SAPSR3.FMMEASURE.FMAREA=SAPSR3.FMMEASURET.FMAREA(+)  )
      AND  ( SAPSR3.FMMEASURE.MEASURE=SAPSR3.FMMEASURET.MEASURE(+)  )
)
EOT
                    ,
                ],

                /**
                 * Forçage éventuel du nom de la table intermédiaire utilisée lorsque source et destination
                 * ne partagent pas la même connexion. NB: cette table intermédiaire est créée/peuplée/supprimée
                 * dans la base de données destination à chaque import.
                 * En l'absence de ce forçage, le nom de la table intermédiaire sera celui de la table destination
                 * préfixé par "src_".
                 */
                'intermediate_table' => 'src_progfin',

                /**
                 * Configuration de la destination des données importées :
                 * - 'name'               : petit nom (unique) de la destination
                 * - 'table'              : nom de la table destination vers laquelle les données sont importées
                 * - 'connection'         : identifiant de la connexion Doctrine à la base de données destination
                 * - 'source_code_column' : nom de la colonne dans la table destination contenant l'identifiant unique
                 * - 'columns'            : liste ordonnée des noms des colonnes importées dans la table destination
                 * - 'columns_to_char'    : format sprintf nécessaire pour mettre des colonnes au format requis (string)
                 */
                'destination' => [
                    'name'               => 'PFI Zébu',
                    'table'              => 'progfin',
                    'connection'         => 'doctrine.connection.orm_default',
                    'source_code_column' => 'code',
                    'columns'            => ['libelle', 'fleche', 'debut_validite', 'fin_validite'],
                    'columns_to_char' => [
                        'debut_validite' => "TO_CHAR(%s,'YYYY-MM-DD')", // car colonne destination de type TIMESTAMP
                        'fin_validite'   => "TO_CHAR(%s,'YYYY-MM-DD')", // idem
                    ],
                ],
            ],
        ],
    ],
];
return [
 /**
  * Configuration Doctrine minimum requise.
  */
 'doctrine' => [
     'connection' => [
         'orm_oracle' => [
             'driverClass' => 'Doctrine\\DBAL\\Driver\\OCI8\\Driver',
             'params'      => [
                 'host'     => 'host.domain.fr',
                 'port'     => '1525',
                 'user'     => 'x',
                 'password' => 'y',
                 'dbname'   => 'z',
                 'charset'  => 'AL32UTF8',
             ]
         ],
         'orm_default' => [
             'driverClass' => 'Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver',
             'params' => [
                 'host'     => 'host.domain.fr',
                 'port'     => '5432',
                 'charset'  => 'utf8',
                 'dbname'   => 'a',
                 'user'     => 'b',
                 'password' => 'c',

             ],
         ],
     ],
 ],
];
# Du lundi au vendredi, entre 6h00 et 19h45, toutes les 15 minutes
*/15 6-19 * * 1-5   root    /usr/bin/php /var/www/zebu-back/public/index.php run import --all 1> /tmp/zebu-cron.log 2>&1
######################## IMPORTS ########################

### Import des PFI SIFAC ###
05/09/2017 16:22:08
# delete :
UPDATE progfin SET deleted_on = LOCALTIMESTAMP(0) WHERE CODE = 'fddsfsd' ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = 'fddsfsd-99c4b652-9245-11e7-bddd-0242f725575b' ;
(1 instructions exécutées)
# insert :
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('971UP009', 'Of Wisco', ' ', '2014-01-01', '2047-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '971UP009-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('977CA025', 'Screening de 5', ' ', '2016-07-21', '2018-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '977CA025-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('F950MEC1', 'MDE CAEN FONCTIONNEM', ' ', '2015-01-01', '2047-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = 'F950MEC1-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('F971CF03', 'UNIVERSITE DU HAVRE', ' ', '2015-01-01', '2047-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = 'F971CF03-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('014DU009', 'DU RDCM', ' ', '2016-01-01', '2047-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '014DU009-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('012RE001', 'COLLOQUE 50 ANS SC E', ' ', '2017-01-01', '2017-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '012RE001-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('012CH021', 'Typo-chronologie', ' ', '2017-01-01', '2017-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '012CH021-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('013PL002', 'CMABIO', ' ', '2016-01-01', '2047-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '013PL002-99c4b652-9245-11e7-bddd-0242f725575b' ;
INSERT INTO progfin(CODE, LIBELLE, FLECHE, DEBUT_VALIDITE, FIN_VALIDITE, created_on) VALUES ('999CM003', '2016-AGRI-133', ' ', '2016-03-30', '2018-12-31', LOCALTIMESTAMP(0)) ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '999CM003-99c4b652-9245-11e7-bddd-0242f725575b' ;
(9 instructions exécutées)
# undelete :
UPDATE progfin SET LIBELLE = 'TA 2017', FLECHE = ' ', DEBUT_VALIDITE = '2017-01-01', FIN_VALIDITE = '2047-12-31', updated_on = LOCALTIMESTAMP(0), deleted_on = null WHERE CODE = '913TA017' ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '913TA017-99c4b652-9245-11e7-bddd-0242f725575b' ;
UPDATE progfin SET LIBELLE = 'TA 2017', FLECHE = ' ', DEBUT_VALIDITE = '2017-01-01', FIN_VALIDITE = '2047-12-31', updated_on = LOCALTIMESTAMP(0), deleted_on = null WHERE CODE = '920TA017' ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '920TA017-99c4b652-9245-11e7-bddd-0242f725575b' ;
UPDATE progfin SET LIBELLE = 'PAIE DIU ADOLESCENTS', FLECHE = ' ', DEBUT_VALIDITE = '2017-01-01', FIN_VALIDITE = '2047-12-31', updated_on = LOCALTIMESTAMP(0), deleted_on = null WHERE CODE = 'P14DU910' ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = 'P14DU910-99c4b652-9245-11e7-bddd-0242f725575b' ;
(3 instructions exécutées)
# update :
UPDATE progfin SET LIBELLE = 'PLATIN''', updated_on = LOCALTIMESTAMP(0) WHERE CODE = '925PL005' ; UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = '925PL005-PLATINvxcvxc-99c4b652-9245-11e7-bddd-0242f725575b' ;
(1 instructions exécutées)

Import réalisé avec succès.
SELECT create_import_metarequest_for_ztemptable(
    src.code, src.libelle, TO_CHAR(src.debut_validite,'YYYY-MM-DD'),
    dest.code, dest.libelle, TO_CHAR(dest.debut_validite,'YYYY-MM-DD'), dest.deleted_on,
    'eb1ab85c-916a-11e7-aba7-0242f725575b'
) AS operation
FROM src_ztemptable src
FULL OUTER JOIN ztemptable dest ON src.code = dest.code
;
CREATE OR REPLACE FUNCTION create_import_metarequest_for_ztemptable(
    src_code TEXT, src_libelle TEXT, src_debut_validite TEXT, src_fin_validite TEXT,
    dest_code TEXT, dest_libelle TEXT, dest_debut_validite TEXT, dest_fin_validite TEXT, dest_deleted_on TIMESTAMP(0) WITH TIME ZONE,
    import_hash VARCHAR(255)
) RETURNS VARCHAR(255) AS
$Q$
DECLARE
    operation VARCHAR(64);
    hash VARCHAR(255);
    SQL TEXT;
BEGIN
    -- l'enregistrement existe dans la source mais pas dans la destination : il devra être ajouté
    IF (src_code IS NOT NULL AND dest_code IS NULL) THEN
        operation = 'insert';
        hash = concat(concat(src_code, '-'), import_hash);
        SQL = concat('INSERT INTO ztemptable(code, libelle, debut_validite, fin_validite, created_on) VALUES (', quote_literal(src_code), ', ', quote_literal(src_libelle), ', ', quote_literal(src_debut_validite), ', ', quote_literal(src_fin_validite), ', LOCALTIMESTAMP(0)) ;');
        SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
        INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('insert', 'ztemptable', src_code, NULL, NULL, NULL, SQL, LOCALTIMESTAMP(0), hash);
    END IF;

    -- l'enregistrement existe dans la destination et n'est pas historisé
    IF (src_code IS NOT NULL AND dest_code IS NOT NULL AND dest_deleted_on IS NULL) THEN
        -- 'libelle' doit être mis à jour
        IF (src_libelle <> dest_libelle) THEN
            operation = 'update';
            hash = concat(concat(concat(concat(dest_code, '-'), dest_libelle), '-'), import_hash);
            SQL = concat('UPDATE ztemptable SET libelle = ', quote_literal(src_libelle), ', updated_on = LOCALTIMESTAMP(0) WHERE code = ', quote_literal(dest_code), ' ;');
            SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
            INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('update', 'ztemptable', src_code, 'libelle', src_libelle, dest_libelle, SQL, LOCALTIMESTAMP(0), hash);
        END IF;
        -- 'debut_validite' doit être mis à jour
        IF (src_debut_validite <> dest_debut_validite) THEN
            operation = 'update';
            hash = concat(concat(concat(concat(dest_code, '-'), dest_debut_validite), '-'), import_hash);
            SQL = concat('UPDATE ztemptable SET debut_validite = ', quote_literal(src_debut_validite), ', updated_on = LOCALTIMESTAMP(0) WHERE code = ', quote_literal(dest_code), ' ;');
            SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
            INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('update', 'ztemptable', src_code, 'debut_validite', src_debut_validite, dest_debut_validite, SQL, LOCALTIMESTAMP(0), hash);
        END IF;
        -- 'fin_validite' doit être mis à jour
        IF (src_fin_validite <> dest_fin_validite) THEN
            operation = 'update';
            hash = concat(concat(concat(concat(dest_code, '-'), dest_fin_validite), '-'), import_hash);
            SQL = concat('UPDATE ztemptable SET fin_validite = ', quote_literal(src_fin_validite), ', updated_on = LOCALTIMESTAMP(0) WHERE code = ', quote_literal(dest_code), ' ;');
            SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
            INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('update', 'ztemptable', src_code, 'fin_validite', src_fin_validite, dest_fin_validite, SQL, LOCALTIMESTAMP(0), hash);
        END IF;

    END IF;

    -- l'enregistrement existe dans la destination mais historisé : il sera dé-historisé
    IF (src_code IS NOT NULL AND dest_code IS NOT NULL AND dest_deleted_on IS NOT NULL) THEN
        operation = 'undelete';
        hash = concat(concat(dest_code, '-'), import_hash);
        SQL = concat('UPDATE ztemptable SET libelle = ', quote_literal(src_libelle), ', debut_validite = ', quote_literal(src_debut_validite), ', fin_validite = ', quote_literal(src_fin_validite), ', updated_on = LOCALTIMESTAMP(0), deleted_on = null WHERE code = ', quote_literal(dest_code), ' ;');
        SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
        INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('undelete', 'ztemptable', src_code, NULL, NULL, NULL, SQL, LOCALTIMESTAMP(0), hash);
    END IF;

    -- l'enregistrement existe dans la destination mais plus dans la source : il sera historisé
    IF (src_code IS NULL AND dest_code IS NOT NULL AND dest_deleted_on IS NULL) THEN
        operation = 'delete';
        hash = concat(concat(dest_code, '-'), import_hash);
        SQL = concat('UPDATE ztemptable SET deleted_on = LOCALTIMESTAMP(0) WHERE code = ', quote_literal(dest_code), ' ;');
        SQL = concat(SQL, ' UPDATE import_reg SET executed_on = LOCALTIMESTAMP(0) WHERE import_hash = ''', hash, ''' ;');
        INSERT INTO import_reg(operation, TABLE_NAME, source_code, field_name, to_value, from_value, SQL, created_on, import_hash) VALUES ('delete', 'ztemptable', src_code, NULL, NULL, NULL, SQL, LOCALTIMESTAMP(0), hash);
    END IF;

    RETURN operation;
END; $Q$
LANGUAGE plpgsql;