/**
* @param array $schemas
*/
public function fillTableColumns(&$schemas)
{
$this->em->clear();
$sql = "SELECT n.nspname as schema,\n a.attrelid as table,\n c.relname as table_name,\n a.attnum as oid,\n a.attname as name,\n pg_catalog.format_type(a.atttypid, a.atttypmod) as type,\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)\n FROM pg_catalog.pg_attrdef d\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default,\n (not a.attnotnull)::text as nullable,\n a.attnum as real_position,\n ROW_NUMBER() OVER(PARTITION BY a.attrelid ORDER BY a.attnum) as position,\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation\n FROM pg_catalog.pg_attribute a\n INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid\n INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n WHERE a.attnum > 0 AND NOT a.attisdropped\n AND c.relkind = 'r'\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 --AND pg_catalog.pg_table_is_visible(c.oid)\n ORDER BY n.nspname, a.attrelid, a.attnum\n ";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('schema', 'schema');
$rsm->addScalarResult('table', 'table');
$rsm->addScalarResult('table_name', 'tableName');
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('type', 'type');
$rsm->addScalarResult('default', 'default');
$rsm->addScalarResult('nullable', 'nullable');
$rsm->addScalarResult('position', 'position');
$rsm->addScalarResult('attcollation', 'attcollation');
$rsm->addScalarResult('real_position', 'realPosition');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME);
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$column = new Column($row['schema']);
foreach ($row as $key => $value) {
$column->__set($key, $value);
}
if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
$schemas[$row['schema']]->addTableColumn($row['table'], $column);
} elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
$schemas[$row['schema']]->addTableColumn($row['table_name'], $column);
}
unset($column);
}
unset($stmt);
}
/**
* @param $schema
* @param $tableOid
* @return array
*/
public function getColumns($schema, $tableOid)
{
//Filling the columns
$sql = "SELECT attrelid as table,\n a.attnum as oid,\n a.attname as name,\n pg_catalog.format_type(a.atttypid, a.atttypmod) as type,\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)\n FROM pg_catalog.pg_attrdef d\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default,\n (not a.attnotnull)::text as nullable,\n a.attnum as position,\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation\n FROM pg_catalog.pg_attribute a\n WHERE a.attrelid = '" . pg_escape_string($tableOid) . "'\n AND a.attnum > 0 AND NOT a.attisdropped\n ORDER BY a.attnum\n ";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('table', 'table');
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('type', 'type');
$rsm->addScalarResult('default', 'default');
$rsm->addScalarResult('nullable', 'nullable');
$rsm->addScalarResult('position', 'position');
$rsm->addScalarResult('attcollation', 'attcollation');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, self::CACHE_LIFETIME);
$columns = [];
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$column = new Column($schema);
foreach ($row as $key => $value) {
$column->__set($key, $value);
}
if ($this->index_type == self::INDEX_TYPE_OID) {
$columns[$row['oid']] = $column;
} elseif ($this->index_type == self::INDEX_TYPE_NAME) {
$columns[$row['name']] = $column;
}
}
return $columns;
}
