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

[Tom J. <tom...@gm...>]

On Mon, May 17, 2010 at 8:34 PM, Kevin Kenny <ke...@ac...> wrote:
> 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.

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.

I have started an introspection system based upon the
information_schema. Right now I only use it for browsing a database
and getting table information, but this information can be used to
write Tcl code using any object system, or just pure Tcl code. I've
done this before using the pg_* tables, but these tables change too
often and my code is outdated now.

Using info from the information_schema I plan on generating mapping
data which will allow table and object names to differ as well as
column and attribute names to differ.

Implementing a mapping system doesn't mean that you can't use defaults
and it also protects against future changes on either side of the map
(you don't change code, just the mapping). Mapping also allow you to
define multiple views of the same base table.