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