[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[John]

Sorry I have taken a while to respond. Apparently my home server 
(postgres, mail relay, dns, dhcp, http proxy etc) decided that my first 
post to this list for probably a couple of years was a sufficiently 
auspicious occasion to warrant a major dummy-spit, and I have only just 
coaxed it back to life!

On 12/14/2012 09:53 PM, LacaK wrote:
> Hi John,
>> 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.
>>
> I think, that if this view(s) is(are) presented in all versions of 
> PostgreSQL which fcl-db is going to support, that it is no problem use 
> them
> (I must note, that my preffered way is follow sql standard 
> INFORMATION_SCHEMA views at least in column naming)
The equivalent INFORMATION_SCHEMA view is "tables", but it does not 
provide any easy way of distinguishing user tables from system tables.  
It appears to me that it would be necessary to link back to "schemata" 
and only select those schemas that were/were not owned by postgres.  (Of 
course, this would be OK if we were only selecting for a particular 
schema.)  If we were putting the code into TSQLConnection, it would be 
worth the pain to use the standard INFORMATION_SCHEMA views, I guess, 
but if it is done separately for each db connection, it seems it is just 
as easy - and probably more reliable -  to use the postgres view where 
all is done for us.
>
>> 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.)
> Hm, if you want get also schema_name then you should use this approach:
> sqlquery1.SchemaType:=stTables;
> sqlquery1.Open;
> and in loop fill TStrings using 
> sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name')
At present, the schema isn't read into the query (so we have to change 
the db connection), and the above code would have to be put into 
TSQLConnecion, where (1) it would only apply to stTables, and (2) would 
not work if any database species did not return the schema.  Probably to 
do this it would be necessary to be able to select multiple fields in 
the AReturnField of GetDBInfo, all of which would be far more 
complicated than what I suggested as a "simple" solution!

(This is before we start on the argument about whether it is better to 
return multiple fields and concatenate them in the client, or select the 
concatenated fields on the server in the first place, which I think is 
quite debatable.)

>
>>
>> 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.
>>
> Personally I am not fan of this approach (mainly, because of keeping 
> Delphi compatibility).
So how does Delphi do it ?  I can't imagine it returns all the table 
names with no schema specified (as a filter) or prepended.  It is a long 
time since I used Delphi against a real database (Delphi 4 C/S against 
Oracle) but I can't remember any of these problems.  I have no argument 
with matching Delphi behaviour if it is feasible, but by the same token 
I don't see adding behaviour is a problem as long as existing behaviour 
is not broken.
>
> -Laco.
>
@Reinier:
>> 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.
Yes, I saw that, but there seems no reason why it could not be used as a 
schema selection.

I also had a flip through the previous discussion, but it was indeed a 
long and tortuous thread to follow!

So the question comes back to "how should the table names be returned 
?"  Indeed, is there a specification for what should be returned for all 
of these metadata queries ?

cheers,
John Sunderland