[fpc-pascal] Re: SQLDB GetSchemaInfoSQL for indexes etc?

[Ludo Brands]

> Thinking about column names I would suggest, change it according to 
> SQL-Standard (information_schema views). To be fully compatible. 
> (because ATM we are not compatible with SQL-Standard NOR Delphi) 
> Advantage will be, that we will be able do for example 'select * from 
> INFORMATION_SCHEMA.TABLES' so if some DB provides also other columns 
> (additional information on tables, procedures, indexes, columns etc.) 
> we will get it. It will of course lead to breaking backward 
> compatibility, but I think, that there is very, very (if any) small 
> amount of users, which use
> setschemainfo+open to get metadata information.
> 
 
I completely agree. Delphi isn't that consistent with itself neither. Just
take a look at the schema info returned by dbado and you'll get fe. for
siTables the columns TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME and TABLE_TYPE. 

Going one step further I would not bother about stIndexes in favor of
table_constraints, referential_constraints,key_column_usage,
constraint_column_usage and constraint_table_usage which is also iso. The
info contained in these views can't be combined in one stIndexes result set.
Advantage is also that adhering to these standard views, as noted earlier,
can be just a 'select * from INFORMATION_SCHEMA.xxx' for those db's that
support INFORMATION_SCHEMA. For those that don't, there are enough queries
around that map to INFORMATION_SCHEMA or to the very similar ODBC
equivalents. 

Ludo