Home » Database » DB » Bug #1607

[2004-06-10 17:17 UTC] s dot ballestrero at firenze dot linux dot it

Description:
------------
While trying to use DB_DataObjects on a PostgreSQL database
with schemas and failing, I found that $conn->getListOf('tables') returns the names of the tables without the schema name in front (i.e. 'mytable','mytable' instead of 'myschema1.mytable','myschema2.mytable').
I fixed it by replacing the SQL for listing the tables [DB/pgsql.php v 1.75 function getSpecialQuery($type) line 802] with

SELECT n.nspname||'.'||c.relname as "Name"
FROM pg_class c
JOIN pg_user u ON c.relowner = u.usesysid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND c.relname !~ '^pg_'
UNION
SELECT n.nspname||'.'||c.relname as "Name"
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND not exists (select 1 from pg_user where usesysid = c.relowner)
AND c.relname !~ '^pg_'

Would this be the right way to fix it ? Would this fix break something else ?

Cheers, Sergio

PS This bug is similar/related to [#682 Feature request: getListOf('pgsql:schema.table');] but, since it breaks DataObjects/createTables.php, I really think it should be a bug, not a feature request.

Reproduce code:
---------------
# create a db like:
create schema myschema1;
create schema myschema2;
create table myschema1.mytable (id serial, descr text);
create table myschema2.mytable (id serial, descr text);

# connect to it and do
print_r( $conn->getListOf('tables') );

Expected result:
----------------
Array
(
[0] =>myschema1.mytable,
[1] =>myschema2.mytable
)

Actual result:
--------------
Array
(
[0] =>mytable,
[1] =>mytable
)

[2004-06-18 09:02 UTC] s dot ballestrero at firenze dot linux dot it

I found out that also DB::_pgFieldFlags(...) is affected by PGSQL schemas.
I've put up a complete patch on http://www.planetweb.it/opensource/

Please note that this patch WILL BREAK COMPATIBILITY with PostgreSQL pre-7.3 that did not have the pg_namespace system table. How do you think I should handle this problem ?

[2004-06-18 09:06 UTC] s dot ballestrero at firenze dot linux dot it

(fix the bug Summary that Mozilla autofill messed up :-( )