/** * @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); }
/** * @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; }