Hi,
Short version: A call to TSQLConnection.GetTableNames returns a list of
tables on all schemas/owners, without the owner. Unless you are using a
simple database with only one owner and are logging in as the owner,
this is fairly unhelpful.
Environment: FPC Fixes 2.6 , Lazarus trunk, both fairly recent. Linux
(Fedora 14)(not recent). Postgresql server 8.4 (also not recent!)
Analysis:
As far as I can work out, a call to GetTableNames calls GetDBInfo, with
parameters
ASchemaType : TSchemaType - This specifies what info we want - user
tables, sys tables, procedures, columns etc
ASchemaObjectName - Doesn't seem to be used, it is specified as ""
AReturnField : string; - the name of the field to be returned
AList: TStrings - this is what the list of tables is returned in.
ASchemaType, ASchemaObjectName and AReturnField are 'passed' as
properties, and a sqlquery is opened with no query specified. The
ASchemaType causes the TSQLQuery.prepare statement to fetch the actual
sql used from theconnection.GetSchemaInfoSQL, where the query sql is
overridden for each species of connection. In the case of postgres -
pqconnection - the sql is:
select relfilenode as recno, <DatabaseName> as catalog_name, '' as
schema_name, relname as table_name, 0 as table_type
from pg_class
where (relowner > 1) and relkind=''r''
order by relname;
Thus schema name is not selected for postresql, nor is it returned for
any connection. as only the field specified by AReturnField, in this
case table_name, is added to the output list. Note that pg_class does
not have an easy access to the schema/owner by name, it would require a
join on an oid.
Suggestions:
1) The pg database also has a view, pg_stat_user_tables, that is simple
and has the schema as well as the table name. There may be some good
reason for not using it that I am not aware of.
2) The simple way to get the schemas would be to simply 'select ...
schemaname||'.'||relname as table_name ... from pg_stat_user_tables.
This would be a change ONLY to the sql in pqconnection. (If worst comes
to worst, I dare say could create my own pqconnection derivative with
this change.)
3) A better solution, in my opinion, would be to add some extra
TSchemaTypes, with matching TSQLConnection calls, for GetSchemas,
GetTablesInSchema, and possibly the "TableBySchema" option as in (2).
It seems the "infrastructure" already exists to do all of these things.
If this concept is acceptable I would be happy to work on patches for
TSQLConnection and TPQConnection, but my knowledge of the other dbs
varies from limited through out-of-date (Oracle) to none.
cheers
John Sunderland
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
