public function fillParentTables(&$schemas)
{
$this->em->clear();
//Inherit
$sql = "WITH RECURSIVE inh AS (\n SELECT i.inhrelid, i.inhparent\n FROM pg_catalog.pg_inherits i\n UNION\n SELECT i.inhrelid, inh.inhparent\n FROM inh INNER JOIN pg_catalog.pg_inherits i ON (inh.inhrelid = i.inhparent)\n )\n SELECT n.nspname as schema,\n c.oid,\n c.oid::pg_catalog.regclass as name,\n c.relname as table_name,\n --c2.relname as parent_name,\n c2.oid as table\n FROM inh\n INNER JOIN pg_catalog.pg_class c ON (inh.inhrelid = c.oid)\n INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)\n INNER JOIN pg_catalog.pg_class c2 ON (inh.inhparent = c2.oid)\n WHERE 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'";
$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');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME);
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$child = new ParentTable($row['schema'], $row['table']);
foreach ($row as $key => $value) {
$child->__set($key, $value);
}
try {
if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
$schemas[$row['schema']]->addTableParentTable($row['oid'], $child);
} elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
$schemas[$row['schema']]->addTableParentTable($row['table_name'], $child);
}
} catch (\Exception $e) {
$this->logger->addAlert($e->getMessage() . ' ' . json_encode($row), $e->getTrace());
}
unset($child);
}
unset($stmt);
}
/**
* @param $schema
* @param $tableOid
* @return array
*/
public function getParentTables($schema, $tableOid)
{
//Inherit
$sql = "\n WITH RECURSIVE inh AS (\n SELECT i.inhrelid, i.inhparent\n FROM pg_catalog.pg_inherits i\n WHERE i.inhrelid = '" . pg_escape_string($tableOid) . "'\n UNION\n SELECT i.inhrelid, inh.inhparent\n FROM inh INNER JOIN pg_catalog.pg_inherits i ON (inh.inhrelid = i.inhparent)\n )\n SELECT n.nspname as schema,\n c.oid,\n c.oid::pg_catalog.regclass as name,\n c.relname as table_name,\n --c2.relname as parent_name,\n c2.oid as table\n FROM inh\n INNER JOIN pg_catalog.pg_class c ON (inh.inhrelid = c.oid)\n INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)\n INNER JOIN pg_catalog.pg_class c2 ON (inh.inhparent = c2.oid)\n ";
$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');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, self::CACHE_LIFETIME);
$parents = [];
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$child = new ParentTable($schema, $row['table']);
foreach ($row as $key => $value) {
$child->__set($key, $value);
}
if ($this->index_type == self::INDEX_TYPE_OID) {
$parents[$row['oid']] = $child;
} elseif ($this->index_type == self::INDEX_TYPE_NAME) {
$parents[$row['name']] = $child;
}
}
return $parents;
}
