lib
unicaen
db-import

Repository

composer require unicaen/db-import
composer require unicaen/db-import
    "scripts": {
        "post-install-cmd": [
            "mkdir -p public/unicaen ; cp -r vendor/unicaen/db-import/public/unicaen public/",
composer run-script post-install-cmd
composer run-script post-install-cmd
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
return [
    'bjyauthorize'    => [
        'guards' => [
            \UnicaenAuth\Guard\PrivilegeController::class => [
                [
                    'controller' => 'UnicaenDbImport\Controller\Console',
                    'action'     => [
                        'runImport',
                        'runSynchro',
                    ],
                    'roles' => [],
                ],
            ],
        ],
    ],
    //...
];
php public/index.php run import --name "NOM_ETABLI_DANS_LA_CONFIG"
php public/index.php run import
php public/index.php run synchro --name "NOM_ETABLI_DANS_LA_CONFIG"
public/index.php run synchro
ALTER TABLE TABLE_DESTINATION ADD COLUMN created_on TIMESTAMP(0) WITH TIME ZONE DEFAULT LOCALTIMESTAMP(0) NOT NULL;
ALTER TABLE TABLE_DESTINATION ADD COLUMN updated_on TIMESTAMP(0) WITH TIME ZONE;
ALTER TABLE TABLE_DESTINATION ADD COLUMN deleted_on TIMESTAMP(0) WITH TIME ZONE;
ALTER TABLE TABLE_DESTINATION ADD COLUMN created_by INTEGER NOT NULL;
ALTER TABLE TABLE_DESTINATION ADD COLUMN updated_by INTEGER;
ALTER TABLE TABLE_DESTINATION ADD COLUMN deleted_by INTEGER;
ALTER TABLE TABLE_DESTINATION ADD source_id integer NOT NULL ;
ALTER TABLE TABLE_DESTINATION ADD source_code varchar(100) ;
return [
    'import' => [
        'connections' => [
            'orm_A' => 'doctrine.connection.orm_A',
            'orm_B' => 'doctrine.connection.orm_B',
            'geo' => [
                'url'      => 'https://geo.api.gouv.fr/',
                'proxy'    => false,
                'verify'   => true,
                'user'     => null,
                'password' => null,
            ],
        ],
    ],
    'doctrine' => [
        'connection' => [
            'orm_A' => [
                'driverClass' => 'Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver',
                'params' => [
                    'host'     => 'host.domain.fr',
                    'port'     => '5432',
                    'charset'  => 'utf8',
                    'user'     => '???',
                    'dbname'   => '???',
                    'password' => '???',
                ],
            ],
            'orm_B' => [
                'driverClass' => 'Doctrine\\DBAL\\Driver\\OCI8\\Driver',
                'params'      => [
                    'host'     => 'host.domain.fr',
                    'port'     => '1527',
                    'user'     => '???',
                    'password' => '???',
                    'dbname'   => '???',
                    'charset'  => 'AL32UTF8',
                ],
                'eventmanager' => 'orm_B',
            ],
        ],
    ],
];
return [
    'import' => [
        'imports' => [
            [
                'name' => "IMPORT_SIMPLE",
                'source' => [
                    'code'               => 'UTILISATEUR_OCTOPUS',
                    'table'              => 'UTILISATEUR',
                    'connection'         => 'orm_B',
                    'source_code_column' => 'CODE',
                ],
                'destination' => [
                    'code'               => 'TABLE UTILISATEUR LOCALE',
                    'table'              => 'UTILISATEUR',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',
                ],
            ],
        ],
    ],
];
php public/index.php run import --name "IMPORT_SIMPLE"
return [
    'import' => [
        'imports' => [
            [
                'name' => "IMPORT_REGIONS_API",
                'source' => [
                    'code'               => 'geo_api', // DOIT correspondre à un `CODE` de la table `SOURCE`
                    'select'             => '/regions?fields=nom,code',
                    'connection'         => 'geo',
                    'source_code_column' => 'code',
                ],
                'destination' => [
                    'code'               => 'Table REGION',
                    'table'              => 'REGION',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',
                ],
            ],
        ],
    ],
];
php public/index.php run import --name "IMPORT_REGIONS_API"
return [
    'import' => [
        'synchros' => [
            [
                'name' => "SYNCHRO_LOCALE_ROLE",
                'source' => [
                    'code'               => 'app',
                    'select'             => 'SELECT CODE, LIBELLE, DESCRIPTION FROM SRC_ROLE',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',
                ],
                'destination' => [
                    'code'               => 'Table ROLE',
                    'table'              => 'ROLE',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',
                ],
            ],
        ],
    ],
];
php public/index.php run synchro --name "SYNCHRO_LOCALE_UTILISATEUR"
return [
    'import' => [
        'synchros' => [
            [
                'name' => "SYNCHRO_BDD_EXTERNE",
                'source' => [
                    'code'               => 'UTILISATEUR_OCTOPUS',
                    'select'             => 'SELECT CODE, NOM, PRENOM, NAISSANCE FROM UTILISATEUR',
                    'connection'         => 'orm_B',
                    'source_code_column' => 'CODE',
                ],
                'destination' => [
                    'code'               => 'Table UTILISATEUR',
                    'table'              => 'UTILISATEUR',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',

                    'intermediate_table' => 'NOM_QUE_JE_FORCE', // sinon ce sera 'TMP_UTILISATEUR'
                    'intermediate_table_auto_drop' => true, // supprime la table intermédiaire si elle existe déjà

                ],
            ],
        ],
    ],
];
php public/index.php run synchro --name "SYNCHRO_BDD_EXTERNE"
return [
    'import' => [
        'synchros' => [
            [
                'name' => "SYNCHRO_COMMUNES",
                'source' => [
                    'code'               => 'geo_api', // DOIT correspondre à un `CODE` de la table `SOURCE`
                    'select'             => '/departements/63/communes?fields=nom,code',
                    'connection'         => 'geo',
                    'source_code_column' => 'code',
                ],
                'destination' => [
                    'code'               => 'Table locale',
                    'table'              => 'REGION',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'CODE',

                    'intermediate_table_auto_drop' => true,
                ],
            ],
        ],
    ],
];
php public/index.php run synchro --name "SYNCHRO_COMMUNES"
CREATE VIEW SRC_FORMATION AS
    SELECT
        tmp.ID   AS ID,
        tmp.CODE AS CODE,
        tmp.NOM  AS NOM,
        c.ID     AS COMPOSANTE_ID
    FROM
        TMP_FORMATION tmp
    INNER JOIN COMPOSANTE c ON tmp.COMPOSANTE_CODE = c.CODE;
return [
    'import' => [
        'imports' => [
            [
                'name' => "IMPORT_PRÉALABLE", // Importation des données externes dans une table intermédiaire
                'source' => [
                    'code'               => 'FORMATION_FCA',
                    'select'             => 'SELECT ID, CODE, NOM, COMPOSANTE_CODE FROM FORMATION',
                    'connection'         => 'orm_B',
                    'source_code_column' => 'ID',
                ],
                'destination' => [
                    'code'               => 'Table TMP_FORMATION locale',
                    'table'              => 'TMP_FORMATION',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'ID',
                ],
            ],
        ],
        'synchros' => [
            [
                'name' => "SYNCHRO_FINALE", // Synchro des données mise en forme vers la table finale
                'source' => [
                    'code'               => 'TABLE SRC_FORMATION DE MA BASE A UTILISANT TMP_FORMATION',
                    'select'             => 'SELECT * FROM SRC_FORMATION',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'ID',
                ],
                'destination' => [
                    'code'               => 'TABLE FORMATION DE MA BASE A',
                    'table'              => 'FORMATION',
                    'connection'         => 'orm_A',
                    'source_code_column' => 'ID',
                ],
            ],
        ],
    ],
];
php public/index.php run import --name "IMPORT_PRÉALABLE"
php public/index.php run synchro --name "SYNCHRO_FINALE"
return [
    'import' => [
        'code_generators_mapping' => [
            \Doctrine\DBAL\Platforms\OraclePlatform::class => \UnicaenDbImport\CodeGenerator\Oracle\CodeGenerator::class,
        ],
        'code_generators_factories' => [
            \UnicaenDbImport\CodeGenerator\Oracle\CodeGenerator::class => \UnicaenDbImport\CodeGenerator\Oracle\CodeGeneratorFactory::class,
        ],
    ],
];
.
├── Helper
│   ├── LogTableHelper.php
│   ├── TableHelper.php
│   └── TableValidationHelper.php
├── CodeGenerator.php
├── CodeGeneratorFactory.php
├── HelperAbstractFactory.php
namespace UnicaenDbImport\CodeGenerator\Oracle;

use Doctrine\DBAL\Platforms\OraclePlatform;

class CodeGenerator extends \UnicaenDbImport\CodeGenerator\CodeGenerator
{
    public function __construct()
    {
        $this->platform = new OraclePlatform();
    }

    public function generateSQLForIntermmediateTableDrop($tableName)
    {
        return "DROP TABLE $tableName CASCADE CONSTRAINTS ;";
    }
}
namespace UnicaenDbImport\CodeGenerator\Oracle\Helper;

use Doctrine\DBAL\Platforms\OraclePlatform;

class TableHelper extends \UnicaenDbImport\CodeGenerator\Helper\TableHelper
{
    /**
     * @var OraclePlatform
     */
    protected $platform;

    public function __construct()
    {
        $this->platform = new OraclePlatform();
    }

    protected function generateDiffViewDeletionSQLSnippet($destinationTable)
    {
        $name = $this->generateDiffViewName($destinationTable);

        return "DROP VIEW $name";
    }

    //...

}
namespace UnicaenDbImport\CodeGenerator\Oracle;

use Psr\Container\ContainerInterface;
use UnicaenDbImport\Config\Config;

class CodeGeneratorFactory
{
    /**
     * @param ContainerInterface $container
     * @return CodeGenerator
     */
    public function __invoke(ContainerInterface $container)
    {
        /** @var Config $config */
        $config = $container->get(Config::class);

        $tableHelper = $container->get(Helper\TableHelper::class);
        $tableValidationHelper = $container->get(Helper\TableValidationHelper::class);
        $logTableHelper = $container->get(Helper\LogTableHelper::class);

        $codeGenerator = new CodeGenerator();
        $codeGenerator->setConfig($config);
        $codeGenerator->setTableHelper($tableHelper);
        $codeGenerator->setTableValidationHelper($tableValidationHelper);
        $codeGenerator->setLogTableHelper($logTableHelper);

        return $codeGenerator;
    }
}