/**
  * @param array $schemas
  */
 public function fillTableFk(&$schemas)
 {
     $this->em->clear();
     //Foreign Keys
     $sql = "SELECT n2.nspname as schema,\n               c2.oid as table,\n               c2.relname as table_name,\n               r.conname as name,\n               r.conname as oid,\n               r.confrelid as \"parentTable\",\n               CASE WHEN confupdtype = 'a' THEN 'no action'\n               WHEN confupdtype = 'r' THEN 'restrict'\n               WHEN confupdtype = 'c' THEN 'cascade'\n               WHEN confupdtype = 'n' THEN 'set null'\n               WHEN confupdtype = 'd' THEN 'set default'\n               ELSE NULL::text\n               END as  \"updateType\",\n               CASE WHEN confdeltype = 'a' THEN 'no action'\n               WHEN confdeltype = 'r' THEN 'restrict'\n               WHEN confdeltype = 'c' THEN 'cascade'\n               WHEN confdeltype = 'n' THEN 'set null'\n               WHEN confdeltype = 'd' THEN 'set default'\n               ELSE NULL::text\n               END as  \"deleteType\",\n               CASE WHEN confmatchtype = 'f' THEN 'full'\n               WHEN confmatchtype = 'p' THEN 'partial'\n               WHEN confmatchtype = 'u' THEN 'simple'\n               ELSE NULL::text\n               END as \"matchType\",\n               r.conkey as \"cols\",\n               r.confkey as \"refCols\",\n               pg_catalog.pg_get_constraintdef(r.oid, true) as \"creationQuery\"\n        FROM pg_catalog.pg_constraint r\n               INNER JOIN pg_catalog.pg_class c ON c.oid = r.confrelid\n                                                   AND c.relkind = 'r' --AND pg_catalog.pg_table_is_visible(c.oid)\n               INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n                                                       AND n.nspname <> 'pg_catalog'\n                                                       AND n.nspname <> 'information_schema'\n                                                       AND n.nspname !~ '^pg_toast'\n                                                       AND n.nspname !~ '^pg_temp'\n                                                       AND n.nspname <> 'londiste'\n                                                       AND n.nspname <> 'pgq'\n               INNER JOIN pg_catalog.pg_class c2 ON c2.oid = r.conrelid AND c2.relkind = 'r' --AND pg_catalog.pg_table_is_visible(c2.oid)\n               INNER JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace\n                                                       AND n2.nspname <> 'pg_catalog'\n                                                       AND n2.nspname <> 'information_schema'\n                                                       AND n2.nspname !~ '^pg_toast'\n                                                       AND n2.nspname !~ '^pg_temp'\n                                                       AND n2.nspname <> 'londiste'\n                                                       AND n2.nspname <> 'pgq'\n        WHERE  r.contype = 'f'\n\n        ORDER BY 1";
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('oid', 'oid');
     $rsm->addScalarResult('name', 'name');
     $rsm->addScalarResult('schema', 'schema');
     $rsm->addScalarResult('table', 'table');
     $rsm->addScalarResult('table_name', 'table_name');
     $rsm->addScalarResult('parentTable', 'parentTable');
     $rsm->addScalarResult('updateType', 'updateType');
     $rsm->addScalarResult('deleteType', 'deleteType');
     $rsm->addScalarResult('matchType', 'matchType');
     $rsm->addScalarResult('cols', 'cols');
     $rsm->addScalarResult('refCols', 'refCols');
     $rsm->addScalarResult('creationQuery', 'creationQuery');
     $stmt = $this->em->createNativeQuery($sql, $rsm);
     $stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME);
     foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
         $foreignKey = new ForeignKey($row['schema'], $row['table']);
         foreach ($row as $key => $value) {
             $foreignKey->__set($key, $key == 'cols' || $key == 'refCols' ? explode(',', str_replace(array('{', '}', ' '), '', $value)) : $value);
         }
         if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
             $schemas[$row['schema']]->addTableFk($row['table'], $foreignKey);
         } elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
             $schemas[$row['schema']]->addTableFk($row['table_name'], $foreignKey);
         }
         unset($foreignKey);
     }
     unset($stmt);
 }
Exemple #2
0
 /**
  * @param $schema
  * @param $tableOid
  * @return array
  */
 public function getForeignKeys($schema, $tableOid)
 {
     //Foreign Keys
     $sql = "SELECT conname as name,\n                                conname as oid,\n                                confrelid as \"parentTable\",\n                                CASE WHEN confupdtype = 'a' THEN 'no action'\n                                     WHEN confupdtype = 'r' THEN 'restrict'\n                                     WHEN confupdtype = 'c' THEN 'cascade'\n                                     WHEN confupdtype = 'n' THEN 'set null'\n                                     WHEN confupdtype = 'd' THEN 'set default'\n                                     ELSE NULL::text\n                                END as  \"updateType\",\n                                CASE WHEN confdeltype = 'a' THEN 'no action'\n                                     WHEN confdeltype = 'r' THEN 'restrict'\n                                     WHEN confdeltype = 'c' THEN 'cascade'\n                                     WHEN confdeltype = 'n' THEN 'set null'\n                                     WHEN confdeltype = 'd' THEN 'set default'\n                                     ELSE NULL::text\n                                END as  \"deleteType\",\n                                CASE WHEN confmatchtype = 'f' THEN 'full'\n                                    WHEN confmatchtype = 'p' THEN 'partial'\n                                    WHEN confmatchtype = 'u' THEN 'simple'\n                                    ELSE NULL::text\n                                END as \"matchType\",\n                                conkey as \"cols\",\n                                confkey as \"refCols\",\n                            pg_catalog.pg_get_constraintdef(r.oid, true) as \"creationQuery\"\n                        FROM pg_catalog.pg_constraint r\n                        WHERE  r.contype = 'f'\n                            AND r.conrelid = '" . pg_escape_string($tableOid) . "'\n                        ORDER BY 1";
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('oid', 'oid');
     $rsm->addScalarResult('name', 'name');
     $rsm->addScalarResult('parentTable', 'parentTable');
     $rsm->addScalarResult('updateType', 'updateType');
     $rsm->addScalarResult('deleteType', 'deleteType');
     $rsm->addScalarResult('matchType', 'matchType');
     $rsm->addScalarResult('cols', 'cols');
     $rsm->addScalarResult('refCols', 'refCols');
     $rsm->addScalarResult('creationQuery', 'creationQuery');
     $stmt = $this->em->createNativeQuery($sql, $rsm);
     $stmt->useResultCache(true, self::CACHE_LIFETIME);
     $foreignKeys = [];
     foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
         $foreignKey = new ForeignKey($schema, $tableOid);
         foreach ($row as $key => $value) {
             $foreignKey->__set($key, $key == 'cols' || $key == 'refCols' ? explode(',', str_replace(array('{', '}', ' '), '', $value)) : $value);
         }
         if ($this->index_type == self::INDEX_TYPE_OID) {
             $foreignKeys[$row['oid']] = $foreignKey;
         } elseif ($this->index_type == self::INDEX_TYPE_NAME) {
             $foreignKeys[$row['name']] = $foreignKey;
         }
     }
     return $foreignKeys;
 }