[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[Tomas Hajny]

On Tue, December 18, 2012 09:35, michael.vancanneyt at wisa.be wrote:
> On Tue, 18 Dec 2012, John wrote:
>
>>> 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.
>
> 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)?


>> 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
>> ?
>
> MySQL, Firebird, MS-access, sqlite simply do not have schema as Oracle
> has.
> In PostGres or MS-SQL it's optional, to my knowledge (not an expert).

Well, it's probably always 'optional' (i.e. also with Oracle) as long as
you use your own objects only.


> I am using Firebird, so being "forced" to use schema would be a serious
> disadvantage.
>
> That said, I understand you need it, we're just trying to find a solution
> that is good&acceptable for everyone.

I would assume that using/promoting use of FullTableName (as suggested
above) in queries should be probably a safe bet without disadvantage for
other databases.

Tomas