[fpc-pascal] Re: GetTableNames in TSQLConnection / Postgresql

[LacaK]

John  wrote / napísal(a):
> On 12/21/2012 10:37 PM, Tomas Hajny wrote:
>> On Fri, December 21, 2012 11:59, Reinier Olislagers wrote:
>>> On 18-12-2012 10:49, Tomas Hajny wrote:
>>>> On Tue, December 18, 2012 09:35,
>>>> michael.vancanneyt-0Is9KJ9Sb0A at public.gmane.org wrote:
>>>>> On Tue, 18 Dec 2012, John wrote:
>>>>>> 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.
>>>>> I would opt for the latter, hence the soPrependSchemaName.
>>>> Isn't something like a FullTableName property/method
>>>> (connection-specific)
>>>> a better solution (more universal, also catering for differences in
>>>> construction of the full name with different database engines)?
>>> No, because then you won't be able to get metadata using the same
>>> mechanism for all connections.
>>> If we create a FullTableName method (or property, with corresponding
>>> tablenames collection etc), we should do the same for stored 
>>> procedures,
>>> views, and any other object a db supports.
>>> Switching dbs would then be very problematic.
>>>
>>> The current mechanism has the advantage that it is extendable for
>>> various dbs without too much effort... and is like other mechanisms
>>> (e.g. the ADO.Net method of doing things).
>> I must have missed something, because my thoughts went exactly into the
>> direction of simplifying switching from one database to another. Indeed,
>> doing the same for all database objects would be IMHO beneficial too, 
>> but
>> that's another story. My proposal was exactly this - whatever naming the
>> respective DB provider requires natively when addressing objects, it 
>> would
>> be reflected in this property (implemented in DB provider specific way).
>> If it is 'SCHEMA_NAME.TABLE_NAME' with Oracle, so be it. If it is just
>> 'TABLE_NAME' with MySQL - OK, no problem. Obviously, simple TableName
>> field would still exist, but use of that field would then become DB
>> provider specific (meaning that you could combine it with another
>> selection condition like schema when querying the database dictionary or
>> not depending on your needs). Obviously, accessing (querying) 
>> information
>> about the database dictionary data is then DB provider specific too, but
>> completely transparent to the end user.
>>
>> Then again, I might have missed something. I know Oracle fairly well
>> (hopefully ;-) ), but certainly not all the other supported DB engines
>> equally so (some of them not at all, with some of them just bare 
>> basics).
>>
>> Tomas
>>
> What you are suggesting is more-or-less one of the options I 
> originally suggested, so I guess I have to agree.
>
> Another option that seems quite simple to me would be to add a 
> 'GetSchema' method,
http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.GetSchemaNames

procedure GetSchemaNames(List: TStrings); overload;



> and then add a parameter Schema: String  = '' to each of the other 
> get... methods.
yes like Delphi does see: 
http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.GetTableNames

procedure GetTableNames(List: TStrings; SchemaName: string; SystemTables: Boolean = False); overload;


> Each dbConnection could then ignore or implement that parameter as 
> appropriate, with each of the get... methods would then operating only 
> on a single schema, when the db used schemas and one was supplied.  It 
> would mean iterating through the schemas if you wanted a complete 
> list, but usually I would operate only on one schema in a given 
> application, and even if not I think that would be better that having 
> to separate them from a combined list.  I suppose a final rule could 
> be that if a db used schemas and no schema was supplied, THEN the 
> full, schema qualified list would be given.  Having said all that, the 
> option that was suggested, I think by Michael, will also work, though 
> I think a little more clumsily, at least for my use case.
>
> (Finally, when I started all this, I thought I had to fix the 
> sqlconnection components, not realising I could get the metadata 
> directly from sqlquery.  As I was planning to derive a descendant of 
> TSQLQuery anyway, as long as the schema data is filled in, which I 
> understand has already been done,
Yes in trunk is commited patch, so for PQConnection is now correctly 
filled also schema_name column

> then I can work from there.)
>
> This will probably be my last chance to post for a few days, so Merry 
> Christmas to all those who celebrate it!
>
Merry Christmas to all

-Laco.

> cheers,
> John Sunderland
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>