[fpc-devel] TField.FieldName: how about database and table names?

[Bram Kuijvenhoven]

Michael Van Canneyt wrote:
> On Thu, 19 Apr 2007, Bram Kuijvenhoven wrote:
>> In ODBC, the columns in the result set have a number of 'descriptor fields'
>> that are connected to their 'name':
>> - SQL_DESC_BASE_COLUMN_NAME
>> - SQL_DESC_BASE_TABLE_NAME
>> - SQL_DESC_CATALOG_NAME
>> - SQL_DESC_NAME (<--- currently used in TODBCConnection as FieldNames)
>> - SQL_DESC_SCHEMA_NAME
>> - SQL_DESC_TABLE_NAME
>> (Source:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetdescfield.asp,
>> under 'FieldIdentifier Argument')
>>
>> There is no statement in the ODBC specification claiming that any of these
>> field names should be unique. It appears that I must implement a procedure in
>> TODBCConnection to ensure the FieldNames are unique.
> 
> The SQL standard specifies this, as far as I know ?

The sequence Schema.Table.Column could be unique, but this does not hold for e.g. SQL_DESC_NAME in ODBC or MYSQL_FIELD.name in the MySQL C API.

(As an experiment, try to run a query like 'SELECT * FROM db.table, db.table' in MySQL; it will complain: 'Not unique table/alias: 'table''. This can be fixed by 'SELECT * FROM db.table, db.table AS aTableAlias'. On the other hand it does accept 'SELECT * FROM db2006.table, db2007.table', here the table names are the same, but the schema names differ.)

>> To create unique FieldNames I can indeed use the technique proposed by
>> Leonardo (age, age1, age2, etc.); this will ensure uniqueness of FieldNames.
>>
>> For the purposes of my application, however, it would also be nice if I could
>> tell the 'components' catalog/database, table/schema and column/field of a
>> field, as given by the ODBC driver.
> 
> Yes, but this behaviour would be unique to ODBC. Not something to generalize.

The MySQL C API also defines this; see the MYSQL_FIELD record. I expect at least some other DB engines also support this, in particular because it is also in ODBC.

(NB: I need to make one correction to my terminology: a 'schema' appears to be the same as a 'database' (in MySQL), but 'catalog' refers to the database metadata.)

(NB: I currently have only a MySQL database running here, so that's why I will mostly refer to MySQL)

>> I did not refer to multi-connection queries, but multi-catalog queries; e.g.
>> in MySQL one can perfectly do a query like
>>
>>  SELECT dbFoo.tableBar.aField, dbBar.tableFoor.anotherField
>>    FROM dbFoo.tableBar, dbBar.tblFoo
>>
>> Here dbFoo and dbBar are two catalogs (or: databases).
> 
> I didn't know you can connect to 2 databases at once in mysql ? Is this new
> in MySQL 5 ?

I think this has been supported in MySQL for as long as I know it :)

This can be very useful in applications; for example one might have one a (huge) postal code database (mapping addresses to postal codes) which is used in several other databases. (Of course it is also useful when transferring data from one database to another.)

>> In TODBCConnection the DatabaseName has a different meaning than with e.g.
>> TMySQLConnectionXX; it actually refers to a DSN, which I thought to be more
>> appropriate in the setting of ODBC. Another reason I used DatabaseName for a
>> DSN, is that there is no ODBC-defined connection parameter defining the
>> 'database' to connect to, as with for example MySQL. The MySQL ODBC driver
>> defines the DATABASE parameter for this, but this is not part of the ODBC
>> specification. (Note: use TODBCConnection.Params to specify custom parameters
>> such as DATABASE=myDB and SERVER=127.0.0.1) Some database driver operate on
>> files, e.g. the MS Access driver, which uses the DBQ parameter to specify a
>> file that holds the database.
> 
> Yes, and all this is why I think such thing should not be part of TDataset/TField.
> It's very ODBC specific.

If TDataset and TField are too general for this, then how about TSQLQuery? Anyway, we also have TField.Origin.

>>> Also, it would take a lot of extra queries to be able to retrieve these
>>> values, and this would seriously impact performance. If there is anything
>>> we should not compromise on, it's performance.
>> For ODBC, the information does not require additional queries, only some API
>> calls, which could also be delayed until the properties are actually read.
> 
> Yes, but this would not be so for Firebird or - as far as I know - MySQL.
> There you would need additional queries to retrieve such information.
> Therefore I don't think that having such information available should
> be part of the standard set of SQLDB or TDataset.

Because we use DESCRIBE queries instead of e.g. accessing MYSQL_FIELD?

>>> The only thing we can do is add the 'Origin' property such as Delphi has it.
>>> And even then, this is of limited use.
>> What is this 'Origin' property? I'm curious.
> 
> It's equivalent to TableName.FieldName. It's mainly used for the
> DatasetProvider resolver, I think.

I see TField.Origin is also in FPC's db unit. Perhaps we can use this one.

Should TField.Origin also contain the schema name? It might be required to guarantee uniqueness.

We should take care, however: at least some DBMSes support (yes, MySQL) . characters in field names, we could end up with 'schema.table.a.field.with.dots.in.its.name'. If an application wants to parse this into schema, table and field name, it should be really careful. When the schema name is omitted form Origin (for some reason), it could even fail parsing on e.g. 'table.field.withadot'.

This is why I prefer to have separate TableName and SchemaName properties added, at the level of TField or perhaps at the level of SQLDB.

Actually there is even more: 
- SQL_DESC_BASE_COLUMN_NAME / MYSQL_FIELD.org_name
- SQL_DESC_BASE_TABLE_NAME / MYSQL_FIELD.org_table
- SQL_DESC_CATALOG_NAME / MYSQL_FIELD.catalog

The first two are for column and table aliases; the purpose and meaning of last one is still a little bit of a mystery to me.

>From my colleagues I heard few DB layers support the schema.table.column distinction, and thereby give rise to the need to supply aliases for columns in queries doing multiple joins on the same table. I hope the FPC DB layer can be made more powerful than that!

Regards,

Bram