Home » Database » DB » Bug #2084

[2004-08-08 00:10 UTC] ylf at xung dot org

Description:
------------
Hi,

At first I tried to use the DataObject Auto Builder against my postgresql 7.4.2 database : it failed complaining about some non-existent relation.

My database uses schemas and the error shortly described bove was coming from the DB::getListOf('tables') call performed by DataObject_Generator.

Briefly, it was trying to get informations about table 'bar' while this table belongs to the 'foo' schema, and thus can only be accessed with 'foo.bar', if no schema search path is set up (my case).

Here is a patch against DB 1.6.5 that fixes DB/pgsql.php for this issue :
http://xung.org/misc/pear/DB-pgschema.diff

It modifies the getSpecialQuery() method, so that it returns a query that fetches fully qualified table names (as 'schema.table'). For those tables whose schema is 'public' (the default schema), the table names are retrieved without any schema prefix.

Using this modified DB_pgsql class, Dataobject_Generator ran smoothly and created the dozen or so objects needed for my database.

But:
- it will break on Postgresql versions less than 7.3, which do not implement schemas. The best would be to figure out which version is running in DB_pgsql::connect(). I don't know if this breaks some of the PEAR DB design guidelines though (it would take one extra query).
- this patch does not address the same issue with functions and views, which belong to a schema as well.

--
Olivier Guilyardi

Reproduce code:
---------------
/* Please use postgresql 7.3 or later */

require_once 'PEAR.php';
require_once 'DB.php';

$db =& DB::connect("pgsql://user:pw@host/db");

$db->query("create schema foo;");
$db->query("create table foo.bar (i int);");

$tables = $db->getListOf('tables');
print_r ($tables);

Expected result:
----------------
I expect to see the fully qualified table name 'foo.bar'. Any select queries using the 'bar' table name as returned by getListOf() will fail if no schema search path is set, which is a usual setup.

Actual result:
--------------
Array
(
[0] => bar
)

[2004-08-08 01:10 UTC] ylf at xung dot org

Sorry, I did not check the open bugs properly : issues 682 and specifically 1607 (breaks DB_Dataobject as well) appears to address the same problem.