[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[michael.vancanneyt at wisa.be]

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.

>
> 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).

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.

Michael.