| Package home | Report new bug | New search | Development Roadmap | Status: Open | Feedback | All | Closed Since Version 1.12.2 |
| Request #1607 | [patch] PostgreSQL schema (aka namespace) support | ||
|---|---|---|---|
| Submitted: | 2004-06-10 17:17 UTC | ||
| From: | s dot ballestrero at firenze dot linux dot it | Assigned: | danielc |
| Status: | Duplicate | Package: | DB |
| PHP Version: | 4.3.6 | OS: | Any |
| Roadmaps: | (Not assigned) | ||
[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
)
Comments
[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 :-( )
[2005-02-02 22:32 UTC] danielc
Support for something like this has been added to CVS in response to Request 682.