Skip to content
Snippets Groups Projects
Select Git revision
  • 1c8ebed546d00ee5e2858c0d2e66bc80ae9f5328
  • master default protected
  • ll-workflow
  • alc-scindage-donnees-pj
  • b24
  • FJ_LL_Tbl_Contrat
  • alc-docker-node
  • ll-apiplatform
  • php84
  • ll-rgpd
  • b23
  • alc-filtre-type-intervenant
  • ll-sans-mdb5
  • formules-ancienne-infra
  • ll-formules
  • alc-intervenant-dmep
  • ll-suppr-v_vol-s
  • b20
  • ll-postgresql
  • b23.0.1
  • b22
  • 24.8
  • 24.7
  • 24.6
  • 24.5
  • 24.4
  • 24.3
  • 24.2
  • 24.1
  • 24.0
  • 23.15
  • 24.0-beta19
  • 24.0-beta18
  • 24.0-beta17
  • 24.0-beta16
  • 24.0-beta15
  • 24.0-beta14
  • 24.0-beta13
  • 23.14
  • 24.0-beta12
  • 24.0-beta11
41 results

DdlGen.php

Blame
  • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    DdlGen.php 9.43 KiB
    <?php
    
    namespace GenDbStructure;
    
    use Doctrine\ORM\EntityManager;
    use UnicaenApp\Service\EntityManagerAwareTrait;
    
    class DdlGen
    {
        const SEQUENCES       = 'Séquences';
        const TABLES          = 'Tables';
        const PACKAGES        = 'Packages';
        const VIEWS           = 'Vues';
        const MVIEWS          = 'Vues matérialisées';
        const PACKAGES_BODIES = 'Packages Bodies';
        const CONSTRAINTS     = 'Clés étrangères';
        const INDEXES         = 'Indexs';
        const TRIGGERS        = 'Triggers';
        const JOBS            = 'Jobs';
    
        use EntityManagerAwareTrait;
    
        /**
         * @var array
         */
        private $ddlQueries = [];
    
        /**
         * @var array
         */
        private $ddl    = [];
    
        private $tables = [];
    
    
    
        public function __construct(EntityManager $em)
        {
            $this->setEntityManager($em);
        }
    
    
    
        protected function init()
        {
            $sql = "
            BEGIN
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA',        false);
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',      true );
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY',             true );
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',    false);
                DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',            false);
            END;";
    
            $this->getEntityManager()->getConnection()->executeQuery($sql);
    
            return $this;
        }
    
    
    
        private function getDdlQueries()
        {
            if (empty($this->ddlQueries)) {
                $this->makeDdlQueries();
            }
    
            return $this->ddlQueries;
        }
    
    
    
        public function addDdlQuery($type, $sql, $callback = null)
        {
            $this->ddlQueries[$type] = [
                'sql'      => $sql,
                'callback' => $callback,
            ];
    
            return $this;
        }
    
    
    
        protected function makeDdl($types = null)
        {
            $this->init();
    
            $queries = $this->getDdlQueries();
    
            if ($types !== null) {
                $types = (array)$types;
            } else {
                $types = array_keys($queries);
            }
    
            foreach ($types as $type) {
                $query = $queries[$type];
                $qr    = $this->getEntityManager()->getConnection()->fetchAll($query['sql']);
                foreach ($qr as $ql) {
                    $name = $ql['OBJECT_NAME'];
                    $ddl  = trim($ql['OBJECT_DDL']);
                    if (isset($query['callback'])) {
                        $this->ddl[$type][$name] = $query['callback']($name, $ddl);
                    } else {
                        $this->ddl[$type][$name] = $ddl;
                    }
                }
            }
        }
    
    
    
        private function comment1($str)
        {
            $len = 50;
    
            $result = str_pad('', $len, '-') . "\n";
            $result .= "-- " . $str . "\n";
            $result .= str_pad('', $len, '-') . "\n\n";
    
            return $result;
        }
    
    
    
        private function comment2($str)
        {
            return '-- ' . $str . "\n";
        }
    
    
    
        public function getDdl($types = null)
        {
            $this->makeDdl($types);
    
            $content = $this->comment1('DDL de la base de données OSE');
    
            $content .= "\nSET DEFINE OFF;\n\n\n";
    
            foreach ($this->ddl as $type => $query) {
                $content .= $this->comment1($type);
                foreach ($query as $name => $oddl) {
                    $content .= $this->comment2($name) . $oddl . "\n\n";
                }
            }
    
            return $content;
        }
    
    
    
        private function getTables()
        {
            if (empty($this->tables)) {
                $sql = "SELECT table_name FROM ALL_TABLES WHERE owner = 'OSE'";
                $ts  = $this->getEntityManager()->getConnection()->fetchAll($sql);
                foreach ($ts as $t) {
                    $this->tables[] = $t['TABLE_NAME'];
                }
            }
    
            return $this->tables;
        }
    
    
    
        private function getCurrentSchema()
        {
            $sql = "SELECT user scname FROM dual";
    
            return $this->getEntityManager()->getConnection()->fetchAssoc($sql)['SCNAME'];
        }
    
    
    
        public function delSchemaReff($name, $sql)
        {
            $tables        = $this->getTables();
            $currentSchema = $this->getCurrentSchema();
    
            foreach ($tables as $table) {
                $sql = str_replace('"' . $currentSchema . '"."' . $table . '"', '"' . $table . '"', $sql);
            }
    
            return $sql;
        }
    
    
    
        public function delMViewTableSpaceInfos($name, $sql)
        {
            return substr($sql, 0, strpos($sql, ')') + 1)
                . ' AS' . "\n"
                . substr($sql, strpos($sql, 'SELECT'));
        }
    
    
    
        public function delAutoGenPackageCode($sql)
        {
            return substr($sql, 0, strpos($sql, '-- AUTOMATIC GENERATION --') + 26)
                . substr($sql, strpos($sql, '-- END OF AUTOMATIC GENERATION --') - 4);
        }
    
    
    
        private function makeDdlQueries()
        {
            $q = "SELECT
                sequence_name object_name,
                to_clob('CREATE SEQUENCE ' || sequence_name || ' INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE;') object_ddl
              FROM
                ALL_SEQUENCES
              WHERE
                sequence_owner = 'OSE'
              ORDER BY
                sequence_name
            ";
            $this->addDdlQuery(self::SEQUENCES, $q);
    
    
            $q = "SELECT
                table_name object_name,
                DBMS_METADATA.get_ddl ('TABLE', table_name, user) 
                || CASE WHEN table_name like 'TBL_%' THEN 
                  '\nALTER TABLE ' || table_name || ' NOLOGGING;'
                ELSE '' END object_ddl
              FROM
                ALL_TABLES
              WHERE
                owner = 'OSE'
                AND table_name NOT LIKE 'MV_%'
                AND table_name NOT LIKE 'TBL_NOEUD'
                AND table_name NOT LIKE 'UNICAEN_%'
              ORDER BY
                table_name
            ";
            $this->addDdlQuery(self::TABLES, $q);
    
    
            $q = "SELECT
                object_name object_name,
                DBMS_METADATA.get_ddl ('PACKAGE', object_name, user) object_ddl
              FROM
                USER_OBJECTS
              WHERE
                object_type = 'PACKAGE'
                AND object_name NOT LIKE '%_AUTOGEN_PROCS_%'
              ORDER BY
                object_name
            ";
            $this->addDdlQuery(self::PACKAGES, $q, function ($name, $ddl) {
                $res = trim(substr($ddl, 0, strpos($ddl, 'CREATE OR REPLACE PACKAGE BODY')));
    
                return $res;
            });
    
    
            $q = "SELECT
                view_name object_name,
                DBMS_METADATA.get_ddl ('VIEW', view_name, user) object_ddl
              FROM
                ALL_VIEWS
              WHERE
                owner='OSE'
                AND view_name NOT LIKE 'SRC_%'
                AND view_name NOT LIKE 'V_DIFF_%'
                AND view_name NOT LIKE 'V_SYMPA_%'
                AND view_name NOT LIKE 'V_UNICAEN_%'
                AND view_name NOT LIKE 'V_TYPE_INTERVENTION_REGLE_EP'
              ORDER BY
                view_name
            ";
            $this->addDdlQuery(self::VIEWS, $q);
    
    
            $q = "SELECT
                mview_name object_name,
                DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, user) object_ddl
              FROM
                ALL_MVIEWS
              WHERE
                owner='OSE'
                AND mview_name NOT LIKE 'MV_%'
              ORDER BY
                mview_name
            ";
            $this->addDdlQuery(self::MVIEWS, $q, function ($name, $ddl) {
                return $this->delMViewTableSpaceInfos($name, $ddl);
            });
    
    
            $q = "SELECT
                object_name object_name,
                DBMS_METADATA.get_ddl ('PACKAGE_BODY', object_name, user) object_ddl
              FROM
                USER_OBJECTS
              WHERE
                object_type = 'PACKAGE'
                AND object_name NOT LIKE '%_AUTOGEN_PROCS_%'
              ORDER BY
                object_name
            ";
            $this->addDdlQuery(self::PACKAGES_BODIES, $q);
    
    
            $q = "SELECT
                constraint_name object_name,
                DBMS_METADATA.get_ddl ('REF_CONSTRAINT', constraint_name) object_ddl
              FROM
                all_constraints
              WHERE
                owner = 'OSE'
                AND constraint_type = 'R'
              ORDER BY
                constraint_name
            ";
            $this->addDdlQuery(self::CONSTRAINTS, $q);
    
    
            $q = "SELECT
                i.index_name object_name,
                DBMS_METADATA.get_ddl ('INDEX', i.index_name, user) object_ddl
              FROM
                ALL_INDEXES i
                LEFT JOIN all_constraints c ON c.constraint_name = i.index_name OR c.constraint_name || '_IDX' = i.index_name 
              WHERE
                i.owner='OSE'
                AND i.index_name NOT LIKE 'MV_%'
                AND i.index_name NOT LIKE 'UNICAEN_%'
                AND c.constraint_name IS NULL
                AND c.constraint_name NOT IN (
                'TBL_PJD_UN_IDX','TBL_PJF_UN_IDX','TBL_SERVICE_REFERENTIEL_UN_IDX','TBL_SERVICE_SAISIE_UN_IDX'
                )
              ORDER BY
                i.index_name
            ";
            $this->addDdlQuery(self::INDEXES, $q, function ($name, $ddl) {
                return trim(str_replace(";", '', $ddl)) . ';';
            });
    
    
            $q = "SELECT 
                trigger_name object_name, 
                DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, user) object_ddl
              FROM 
                ALL_TRIGGERS
              WHERE
                owner='OSE'
              ORDER BY 
                trigger_name
            ";
            $this->addDdlQuery(self::TRIGGERS, $q, function ($name, $ddl) {
                return $this->delSchemaReff($name, $ddl);
            });
        }
    }