Hi,

Short version: A call to TSQLConnection.GetTableNames returns a list of tables on all schemas/owners, without the owner. Unless you are using a simple database with only one owner and are logging in as the owner, this is fairly unhelpful.

Environment: FPC Fixes 2.6 , Lazarus trunk, both fairly recent. Linux (Fedora 14)(not recent). Postgresql server 8.4 (also not recent!)

Analysis:
As far as I can work out, a call to GetTableNames calls GetDBInfo, with parameters
ASchemaType : TSchemaType - This specifies what info we want - user tables, sys tables, procedures, columns etc
ASchemaObjectName - Doesn't seem to be used, it is specified as "" AReturnField : string; - the name of the field to be returned AList: TStrings - this is what the list of tables is returned in.

ASchemaType, ASchemaObjectName and AReturnField are 'passed' as properties, and a sqlquery is opened with no query specified. The ASchemaType causes the TSQLQuery.prepare statement to fetch the actual sql used from theconnection.GetSchemaInfoSQL, where the query sql is overridden for each species of connection. In the case of postgres - pqconnection - the sql is:

select relfilenode as recno, <DatabaseName> as catalog_name, '' as schema_name, relname as table_name, 0 as table_type from pg_class
where (relowner > 1) and relkind=''r''
order by relname;

Thus schema name is not selected for postresql, nor is it returned for any connection. as only the field specified by AReturnField, in this case table_name, is added to the output list. Note that pg_class does not have an easy access to the schema/owner by name, it would require a join on an oid.

Suggestions:
1) The pg database also has a view, pg_stat_user_tables, that is simple and has the schema as well as the table name. There may be some good reason for not using it that I am not aware of.

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

3) A better solution, in my opinion, would be to add some extra TSchemaTypes, with matching TSQLConnection calls, for GetSchemas, GetTablesInSchema, and possibly the "TableBySchema" option as in (2). It seems the "infrastructure" already exists to do all of these things.

If this concept is acceptable I would be happy to work on patches for TSQLConnection and TPQConnection, but my knowledge of the other dbs varies from limited through out-of-date (Oracle) to none.

cheers
John Sunderland

Hi John,

I think, that if this view(s) is(are) presented in all versions of PostgreSQL which fcl-db is going to support, that it is no problem use them (I must note, that my preffered way is follow sql standard INFORMATION_SCHEMA views at least in column naming)

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

Personally I am not fan of this approach (mainly, because of keeping Delphi compatibility).

-Laco.

<retried; send it to wrong address earlier>

<snip>

No, it's not used for GetTableNames; it is used e.g. to specify the table name if you want to know info about columns.

AReturnField : string; - the name of the field to be returned AList: TStrings - this is what the list of tables is returned in.

Started some docs at the wiki:
http://wiki.lazarus.freepascal.org/Database_metadata Additions welcome!

IIRC, Ludo Brands had a look at the pg schema stuff; he might be able to give you an answer about this.

<snip suggestions>
There was a big discussion of the inadequacies of the metadata retrieval as it is right now and some proposals to fix it:

... it's been lying on our to do list for a while now. TBH, I've forgotten the details of what we discussed.

If this concept is acceptable I would be happy to work on patches for TSQLConnection and TPQConnection, but my knowledge of the other dbs varies from limited through out-of-date (Oracle) to none.

Well, I'd be very grateful if we can get the metadata support a bit more useful and consistent, so your help would be very welcome. Once we have an idea of an acceptable architecture, we can all chip in for the various dbs.

Sorry I have taken a while to respond. Apparently my home server (postgres, mail relay, dns, dhcp, http proxy etc) decided that my first post to this list for probably a couple of years was a sufficiently auspicious occasion to warrant a major dummy-spit, and I have only just coaxed it back to life!

The equivalent INFORMATION_SCHEMA view is "tables", but it does not provide any easy way of distinguishing user tables from system tables. It appears to me that it would be necessary to link back to "schemata" and only select those schemas that were/were not owned by postgres. (Of course, this would be OK if we were only selecting for a particular schema.) If we were putting the code into TSQLConnection, it would be worth the pain to use the standard INFORMATION_SCHEMA views, I guess, but if it is done separately for each db connection, it seems it is just as easy - and probably more reliable - to use the postgres view where all is done for us.

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!

(This is before we start on the argument about whether it is better to return multiple fields and concatenate them in the client, or select the concatenated fields on the server in the first place, which I think is quite debatable.)

So how does Delphi do it ? I can't imagine it returns all the table names with no schema specified (as a filter) or prepended. It is a long time since I used Delphi against a real database (Delphi 4 C/S against Oracle) but I can't remember any of these problems. I have no argument with matching Delphi behaviour if it is feasible, but by the same token I don't see adding behaviour is a problem as long as existing behaviour is not broken.

-Laco.

@Reinier:

Yes, I saw that, but there seems no reason why it could not be used as a schema selection.

I also had a flip through the previous discussion, but it was indeed a long and tortuous thread to follow!

So the question comes back to "how should the table names be returned ?" Indeed, is there a specification for what should be returned for all of these metadata queries ?

cheers,
John Sunderland

I understand; just wanted to let you know what it was used for.

I also had a flip through the previous discussion, but it was indeed a long and tortuous thread to follow!

Agreed. AFAIR, there was a decision in the end... but it was a long thread and a long while ago.
I've incorporated some of Lacak's proposal into
http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization ... and will update that page going through that thread. If anybody gets to it earlier, please feel free to update the page ;)

So the question comes back to "how should the table names be returned ?" Indeed, is there a specification for what should be returned for all of these metadata queries ?

AFAIR, there is no single spec as there are multiple examples we can draw from (ODBC, JDBC, Delphi ADO etc)
IIRC, that thread did have a decision in the end though on what everybody agreed was a good proposal.

Reinier Olislagers wrote

I've incorporated some of Lacak's proposal into http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization ... and will update that page going through that thread. If anybody gets to it earlier, please feel free to update the page ;)

Which ignores the question I asked previously as to how you separate the system tables from the user tables ...

But I think I understand our different views a bit better now - you are looking at this from the point of view of simply getting the SQL and running it in a separate query ? Is this so ? Whereas I was specifically wanting to fix the TSQLConnection.GetTableNames. GetTableNames in its current form cannot benefit from adding the schema column into the query (or rather, getting it to work) as it only uses data from one column.

Anyway, it sounds like for now at least I had better just go down the path of deriving my own tPQConnection descendant.

cheers,
John Sunderland

It would be good to add a link to the 'standard sql' referred to ?

Michael.

The standard mentioned is the SQL ISO standard (e.g. the 2008 version). Draft versions are floating around the internet, but the official one you'd have to buy...

Regards,
Reinier

Also, e.g. PostgreSQL and Mimer have very good documentation on what these information_schema views contain.
I vaguely recall this was covered in our previous thread...

*yes

*

*in principle I am not against it. Let's use DB specific views if they are there, but use COLUMN NAMING compatible with sql standard (like Reinier mentioned here:
http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization )

*

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

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
2. override for TPQConnection.GetTableNames:
GetDBInfo(stTables,'','SCHEMA_TABLE_NAME',List)

-Laco.
*

Reinier Olislagers wrote / napísal(a):

Good job!
Thanks.
-Laco.

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.

It's easier to concatenate 2 fields than to separate out 2 parts of a field, doubly so if you don't know whether the 2 parts are there or not.

Michael.

wrote / napísal(a):

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)

-Laco.

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.

Michael.

wrote / napísal(a):

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

*

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

Redundancy is bad in data :)

There is no need for incompatibility ?

We keep the call as it exists now, and redirect it to the new call with the extended options. We do this in several places in the RTL/FCL already.

The idea is to offer more options, this way there is no need for custom solutions. Everyone benefits.

Michael.

The question is also why return SCHEMA_NAME || '.' || TABLE_NAME when this is not always usable in a query later on ? One DB needs "SCHEMA_NAME"."TABLE_NAME", another `SCHEMA_NAME`.`TABLE_NAME`. An additional option soQuoteNames?

Ludo

wrote / napísal(a):

ok, possible.
-Laco.

Well, if you don't need the concatenation, presumably you don't need the quotes :-)

I thought the schema query is connection dependent ?

In that case, there is no need for this option, as the connection will apply the quotes as needed ?

Michael.

I'm not talking about the query to get the schema/table names but the result from GetTableNames. For those that want the schema information (reason for the soPrependSchemaName option) and want to write a query using a string from the result from GetTableNames are in for some parsing to quote the different parts. Reason why I suggested an additional option to quote the result from GetTableNames so that that can be handled by the connection.

Ludo

For now I added in rev.23163 schema name into query for PostgreSQL (so no empty schema name is returned, but valid schema name) Here I understand:
stTables - list of all tables, which may include system tables (it is not clear from documentation)?
stSysTables - list of ONLY system tables (not views)

http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.GetTableNames "If SystemTables is true, only the system tables are added to List. If SystemTables is false, the list is filled with any tables that match the criteria specified by the TableScope
<http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.TableScope> property"

http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TCustomSQLDataSet.SetSchemaInfo

"stTables - Information about all the data tables on the database server that match the criteria specified by the SQL connection's TableScope
<http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.TableScope> property."

"stSysTables - Information about all of the system tables on the database server"

"If an application calls GetTablesNames with SystemTables set to true, or calls SetSchemaInfo
<http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TCustomSQLDataSet.SetSchemaInfo> with SchemaType set to stSysTables, system tables, and only system tables are returned, regardless of the value of *TableScope*."

-Laco.

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

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.

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

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

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

On 12/18/2012 07:35 PM, wrote:

<snip>

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

For the record, in Postgres, it IS optional, in the sense that you can make all your tables in the "public" schema, in which case you don't need to specify the schema to access them, but if you make them in any other schema, you must specify it in the query. (And yes, I have only just found that out, by experiment. Coming from an Oracle background, I just always made schemas for my tables.)

cheers,
John Sunderland

Confirming once again that we are all creatures of habit :-)

I think that with the modifications proposed here, we should be able to cater for many habits :-)

Michael.

(using public synonyms), but that is not a generally applicable solution.

Tomas

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

Ah now I understand what you mean: you mean a boolean property that specifies behaviour. I was thinking of a property or method that returned the table names (which seemed very strange to me but well..).

Yes, that could work, sorry I misunderstood you.

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, and then add a parameter Schema: String = '' to each of the other get... methods. 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, 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!

cheers,
John Sunderland

John wrote / napísal(a):

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;

Yes in trunk is commited patch, so for PQConnection is now correctly filled also schema_name column

Merry Christmas to all

-Laco.