From: Kevin K. <kev...@gm...> - 2010-05-19 00:38:48
|
Tom Jackson wrote: > SQL has an information_schema, a set of views which describe the user > databases. Nice things about the information_schema is that all the > values are strings and the names of columns are not as cryptic as say > the pg_* tables. Indeed it does. But the vendors are quite lax in actually implementing it consistently. (As they are about a lot of the SQL standard, actually!) My plan is to have the tdbc::connection base class provide versions of [$db primarykeys] and [$db foreignkeys] that query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS views, respectively. Individual database drivers must have the ability to override these, since as I mentioned above, database are pretty haphazard about the completeness of their implementations of INFORMATION_SCHEMA. While I'm on it, I might also provide default implementations of [db tables] and [db columns] that get the information from the appropriate INFORMATION_SCHEMA views. Alas, the ISO committee left out an INFORMATION_SCHEMA.STATISTICS view. (MySQL has one, but it's MySQL-specific.) For this reason, even though SQL/CLI has SQLStatistics, there isn't a comparable "portable" way to ask the database for index column usage at the SQL level. So the current plan is to do nothing in the base class and require that drivers implement [db indices]. Drivers facing databases with incomplete or nonconformant implementations of INFORMATION_SCHEMA will of course have to override the base class methods with ones that get the information another way. Of course, the tdbc::odbc driver will implement all three methods by making calls to the SQL/CLI SQLGetPrimaryKeys, SQLGetForeignKeys and SQLGetStatistics functions, respectively. In other words, tdbc::odbc will punt the problem a further level down into the vendor-supplied ODBC driver. I've already looked at the vendor docs, and it's at least possible to provide all the proposed functionality in the MySQL, ODBC, Postgres and SQLite3 drivers (and the unreleased Oracle driver as well). The fact that the functionality has all been in ODBC since version 1.0 also bodes well for its being available on other databases. -- 73 de ke9tv/2, Kevin |