[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[John]

On 12/17/2012 08:45 PM, michael.vancanneyt at wisa.be wrote:
>
>
> 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.

A table name without a schema is useless, at least in Postgres, and, 
from memory, in Oracle.  Most simply, two schemas foo1 and foo2 (in the 
same database) both can have a table jobs.  If you log in as a user with 
access on both schemas, you cannot just ask for table 'jobs' without 
prefixing it.  If you log in as the owner of foo1 and just ask for jobs, 
you MAY get it - I haven't checked - but if you log in as anyone else, 
you HAVE to prefix it with the schema - ie - select * from foo1.jobs - 
or the query will fail.  This is true even if there is no duplication of 
table names.  So if you want to use GetTableNames to get a list of 
tables, then try to use one - mostly you can't!  That brings me back to 
the original point that either gettablenames has to operate on a known 
schema only (so you can supply the schema afterwards), or it has to 
supply the schema with the table name.

The term "Schema" seems to be used in different ways in the code - I 
wonder if there is some confusion ?  Maybe other databases use it in a 
different way ?

John Sunderland