[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[michael.vancanneyt at wisa.be]

On Mon, 17 Dec 2012, LacaK wrote:

> michael.vancanneyt at wisa.be  wrote / napísal(a):
>> 
>> 
>> On Mon, 17 Dec 2012, LacaK wrote:
>> 
>>>>>> 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.
>> 
>> IMHO all the more reason to use 2 different fields, and keep table in 1 
>> field.
>> 
>>> 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
>> 
>> I would use 2 separate fields. This way the table field always only 
>> contains
>> the table. People interested in the schema, can read the schema field.
> Yes. May be, that I was not clear. My suggestion was 3+ fields in query:
> SCHEMA_NAME, TABLE_NAME, SCHEMA_TABLE_NAME (SCHEMA_NAME || '.' || TABLE_NAME)
>
> But original question AFAIU was about: what should GetTableNames return 
> (schema query can return multiple columns, but what column use when fill list 
> of table names)?
> GetTableNames return list of table names into TStrings.
> Now only TABLE_NAME is returned. John requested, that also schema name should 
> be prefixed (if there is any)

Ah, that was not clear to me :-)

But why the SCHEMA_TABLE_NAME ? It's redundant information, as the
information is present in SCHEMA_NAME and TABLE_NAME anyway ?

If you need the schema name, why not just access the SCHEMA_NAME field ?

A different story is the GetTableNames call, which will - presumably - use
the above information.

There I think some extra options are needed:

TSchemaOption = (soPrependSchemaName,soIncludeSystemObjects);
TSchemaOptions = set of TSchemaOption;

Procedure GetTableNames(List : TStrings; Options : TSchemaOptions = []);

I am not interested in schema information, but I can imagine some people are
(the original poster, obviously). The above caters for everyone and keeps
backwards compatibility.

Michael.