Re: [Tcl-tdbc] [TCLCORE] CALL FOR DISCUSSION: TDBC introspection

[Kevin K. <ke...@ac...>]

Larry McVoy wrote:
> On Mon, May 17, 2010 at 11:09:50PM -0400, Kevin Kenny wrote:
>> Larry McVoy wrote:
>>> I am _so_ not an expert in all this stuff.
>>>
>>> The only question I have is has anyone looked at what perl/python/etc
>>> do in this area?
>> Yes.
> 
> And?  What was learned?

ODBC implements this stuff with the SQLGetPrimaryKeys,
SQLGetForeignKeys, and SQLGetStatistics calls.
Perl's DBI essentially copies what ODBC does directly: it has
'primary_key_info', 'foreign_key_info' and the (misleadingly named)
'statistics_info' to query primary keys, foreign keys, and indices
respectively.  JDBC also does the same thing -- in a typically
Byzantine fashion. Its 'DatabaseMetaData' interface - which must
be constructed/retrieved by a separate call upon the connection
object - supports 'getPrimaryKeys', 'getImportedKeys',
'getExportedKeys', 'getCrossReference', and 'getIndexInfo'.

All three of these define a very similar API to what I propose, with
perhaps slightly more awkward names of things.  Perl's documentation
also warns that 'statistics_info' is 'experimental and subject to
change.

Caveat - All three seem to use the ODBC names for things.
I dislike them - all uppercase, and not always as mnemonic as
they should be, and think them un-Tclish. I'm willing to be convinced
that they are the True Names, if enough people feel strongly
about it.

The JDBC, ODBC and Perl API's require negotiating a result set
rather than returning lists-of-dicts directly. That interface
seemed rather like overkill to me.

Python's DB-API has no support for schema introspection.  It
appears to be under consideration for dbApi3.
http://wiki.python.org/moin/DbApi3#SchemaInformation

PHP, predictably enough, is a hodgepodge. I'm not even going to
discuss it.

-- 
73 de ke9tv/2, Kevin