[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[LacaK]

>>>
>> 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.
*yes

*
>   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.
*in principle I am not against it. Let's use DB specific views if they 
are there, but use COLUMN NAMING compatible with sql standard
(like Reinier mentioned here: 
http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization 
)

*
>
>>
>>> 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!
*Yes it will be more complicated.

I did short comparasion in Delphi and in case of BDE table names are 
returned as "schema.table" in case of ADO,DBX only "table" without 
schema is returned. So again inconsistency between various DB client 
technologies.

If others agree, IMO we can do it like this:
1. use PG specific system view query, where we add SCHEMA_TABLE_NAME 
column (other columns like SCHEMA_NAME, TABLE_NAME, TABLE_TYPE etc 
remains) which will be as you suggested: schemaname||'.'||relname
2. override for TPQConnection.GetTableNames: 
GetDBInfo(stTables,'','SCHEMA_TABLE_NAME',List)

-Laco.
*
>
> (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
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20121217/ab7b7960/attachment.html>