Home » Database » DB_DataObject » Bug #3767

[2005-03-10 15:35 UTC] jeroen at terena dot nl

Description:
------------
Having a postgresql database with schemas (other than the "public" schema) does not work when using create_tables.php

The table list fetched by PEAR::DB still doesn't return the schema along with the table name (see bug #682) even though support for this was addded a while ago in DB version 1.7

If I echo the table names by adding a small echo statement in Generator.php I see all tablenames in my database, but all without the schemaname prepended..

So changed a line in Generator.php

$this->tables = $__DB->getListOf('tables');

becomes:

$this->tables = $__DB->getListOf('schema.tables');

This works and now my classes get generated okay and the schema name gets prepended.

It would be nice if you could set an option whether to automically prepend the schema name as you don't always want this.

Reproduce code:
---------------
Run create_tables.php on postgresql database with multiple schemas - I won't paste my whole db strcuture here ;-)

Expected result:
----------------
generated classes as happens when not using any schemas or just using the public schema.

Actual result:
--------------
something along the lines of:

DB_DataObject_Generator : 0 : CREATING FOR terena

[db_error: message="DB Error: insufficient data supplied" code=-20 mode=return level=notice prefix="" info="SELECT c.relname AS "Name" FROM pg_class c, pg_user u WHERE c.relowner = u.usesysid AND c.relkind = 'r' AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) AND c.relname !~ '^(pg_|sql_)' UNION SELECT c.relname AS "Name" FROM pg_class c 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_' [nativecode=ERROR: relation "area_events" does not exist]"]

[2005-03-14 11:59 UTC] jeroen at terena dot nl

Alan,

Yes you are correct in thinking that
tableInfo('tablename') returns an error if that table is in a schema other than the public schema. The call should then be tableInfo('schemaname.tablename') - this will work fine.

Schemas were introduced in postgresql version 7.4 IIRC. Note that if you run a version that can use schemas you don't *have* to use schemas. In that case all objects (tables, views, sequences etc) will apear in the public schema e.g. public.persons, public.accounts etc.

If you upgrade from a schema-incapable version of PG to one that supports schemas, your tables will be converted from tablename to public.tablename. SQL commands will still work the same because SELECT * FROM mytable will internally resolve to SELECT * FROM public.mytable. Additionally you can create new objects inside a custom schema like myschema.mytable.

I think what you propose could work, but only if you do it the other way around: First do a getListOf('schema.tables') and if that doesn't return anything try getListOf('tables') which will always return all the tables without the schema prefix.

If you have a schema capable version but you don't use schemas (everything is in the public schema) getListOf('schema.tables') will work fine but it will create classes like Public_Persons and Public_Account which some people will find annoying.. Perhaps there could be an option that removes the schema prefix from the generated classname, although this can lead to problems if you have say Office.persons and Someotherschema.persons.

[2005-08-02 09:13 UTC] darealremco at msn dot com

It also borks with tables containing CAPITALS. I've traced
the problem to $__DB->tableInfo, it seems to be
uncapitalizeing. But I got tired of it and
changed the table names to prevent future problems.
Version 1.7.15 sorry if this is already fixed in the CVS .