[fpc-pascal] Re: GetTableNames in TSQLConnection / Postgresql

[Reinier Olislagers]

<retried; send it to wrong address earlier>

On 13-12-2012 10:42, John wrote:
> 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.
> 
<snip>
> 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 ""
No, it's not used for GetTableNames; it is used e.g. to specify the
table name if you want to know info about columns.
>   AReturnField : string; - the name of the field to be returned
>   AList: TStrings - this is what the list of tables is returned in.

Started some docs at the wiki:
http://wiki.lazarus.freepascal.org/Database_metadata
Additions welcome!

> 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:
IIRC, Ludo Brands had a look at the pg schema stuff; he might be able to
give you an answer about this.

<snip suggestions>
There was a big discussion of the inadequacies of the metadata retrieval
as it is right now and some proposals to fix it:
http://www.mail-archive.com/fpc-pascal@lists.freepascal.org/msg28167.html
... it's been lying on our to do list for a while now. TBH, I've
forgotten the details of what we discussed.

> 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.
Well, I'd be very grateful if we can get the metadata support a bit more
useful and consistent, so your help would be very welcome.
Once we have an idea of an acceptable architecture, we can all chip in
for the various dbs.