Adding Schema support to DB/pgsql.php for _pgFieldFlags
| From: | Leo Lutz | Date: | Thu, 27 Oct 2005 17:22:44 +0000 |
| Subject: | Adding Schema support to DB/pgsql.php for _pgFieldFlags | ||
| Groups: | php.pear.dev | ||
| Request: | Send a blank email to pear-dev+get-40318@lists.php.net to get a copy of this message | ||
I've modded DB/pgsql.php so that _pgFieldFlags will work with the
scheme "schema.table". This also makes it so DB/Dataobject will pull
the keys automatically.
function _pgFieldFlags($resource, $num_field, $table_name)
{
$field_name = @pg_fieldname($resource, $num_field);
// <ADDED>
if( strpos($table_name, '.')===false )
{
$names = explode('.',$table_name);
$schema_name = $names[0];
$table_name = $names[1];
} else {
$result = @pg_exec($this->connection, "SELECT current_schema()
FROM category LIMIT 1");
//MISSING CHECK HERE, NOT SURE HOW TO HANDLE IT WITHIN PEAR
$row = @pg_fetch_row($result, 0);
$schema_name = $row[0];
}
// </ADDED>
// IN THE QUERIES I CHANGED
// tab.relname = typ.typnamespace TO tab.relname||tab.relnamespace =
typ.typname||typ.typnamespace
// AND ADDED
// AND ns.nspname = '$schema_name' AND tab.relnamespace = ns.oid
$result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
FROM pg_attribute f, pg_class tab,
pg_type typ, pg_namespace ns
WHERE tab.relname||tab.relnamespace =
typ.typname||typ.typnamespace
AND typ.typrelid = f.attrelid
AND tab.relnamespace = ns.oid
AND f.attname = '$field_name'
AND tab.relname = '$table_name'
AND ns.nspname = '$schema_name'");
if (@pg_numrows($result) > 0) {
$row = @pg_fetch_row($result, 0);
$flags = ($row[0] == 't') ? 'not_null ' : '';
if ($row[1] == 't') {
$result = @pg_exec($this->connection, "SELECT a.adsrc
FROM pg_attribute f, pg_class tab,
pg_type typ, pg_attrdef a
WHERE
tab.relname||tab.relnamespace = typ.typname||typ.typnamespace
AND typ.typrelid = f.attrelid
AND f.attrelid = a.adrelid
AND tab.relnamespace = ns.oid
AND f.attname = '$field_name'
AND tab.relname = '$table_name'
AND ns.oid = '$schema_name'
AND f.attnum = a.adnum");
$row = @pg_fetch_row($result, 0);
$num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
$flags .= 'default_' . rawurlencode($num) . ' ';
}
} else {
$flags = '';
}
$result = @pg_exec($this->connection, "SELECT i.indisunique,
i.indisprimary, i.indkey
FROM pg_attribute f, pg_class tab,
pg_type typ, pg_index i, pg_namespace ns
WHERE tab.relname||tab.relnamespace =
typ.typname||typ.typnamespace
AND typ.typrelid = f.attrelid
AND f.attrelid = i.indrelid
AND tab.relnamespace = ns.oid
AND f.attname = '$field_name'
AND tab.relname = '$table_name'
AND ns.nspname = '$schema_name'");
$count = @pg_numrows($result);
for ($i = 0; $i < $count ; $i++) {
$row = @pg_fetch_row($result, $i);
$keys = explode(' ', $row[2]);
if (in_array($num_field + 1, $keys)) {
$flags .= ($row[0] == 't' && $row[1] == 'f') ?
'unique_key ' : '';
$flags .= ($row[1] == 't') ? 'primary_key ' : '';
if (count($keys) > 1)
$flags .= 'multiple_key ';
}
}
return trim($flags);
}
In function getSpecialQuery I changed the case 'schema.tables'.
return "SELECT schemaname || '.' || tablename"
. ' AS "Name"'
. ' FROM pg_catalog.pg_tables'
. ' WHERE schemaname NOT IN'
. " ('pg_catalog', 'information_schema',
'pg_toast')"
// These privilege tests make it actually usable if another schema
// or table exists without privileges
. " AND has_schema_privilege(schemaname,'usage')"
. " AND has_table_privilege(schemaname || '.'
|| tablename,'select')";
Is there a way to get pgsql.php updated and can someone point out how
to properly handle the error where a table that doesn't exist is
passed or a better way of doing this?
--Leo