[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[LacaK]

michael.vancanneyt at wisa.be  wrote / napísal(a):
>
>
> 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 ?
*Yes is redundant

*
>
> If you need the schema name, why not just access the SCHEMA_NAME field ?
>
*SCHEMA_TABLE_NAME only as helper for GetDBInfo (look how is implemented 
;-))

*
> A different story is the GetTableNames call, which will - presumably - 
> use
> the above information.
>
*Yes it is point of story ... GetTableNames call GetDBInfo which calls 
SetSchemaInfo and in loop fills TStrings with provided AReturnField Field.

*
> There I think some extra options are needed:
>
> TSchemaOption = (soPrependSchemaName,soIncludeSystemObjects);
> TSchemaOptions = set of TSchemaOption;
>
> Procedure GetTableNames(List : TStrings; Options : TSchemaOptions = []);
>
*possible, but introduces incompatibility with Delphi, where 
GetTableNames is declared like now in FPC (GetTableNames(List: TStrings; 
SystemTables: Boolean) + other overloads)
So SystemTables is there already ...

*
> I am not interested in schema information,
*ok, then we can leave things as are now and do only:
1. update PG GetSchemaInfoSQL to correctly fill schema_name (now it is 
always blank)
2. users who are interested in schema name must use instead of 
GetTableNames own but simple loop:
*  sqlquery1.SchemaType:=stTables;
  sqlquery1.Open;
  while not sqlquery1.Eof do begin
    
List.Items.Append(sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name'));
    sqlquery1.next;
  end;

*-Laco.*
> but I can imagine some people are
> (the original poster, obviously). The above caters for everyone and keeps
> backwards compatibility.
>
> Michael.
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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/e0934cde/attachment.html>