Re: [Tcl-tdbc] [TCLCORE] Tickets for TDBC et al

[Kevin K. <kk...@ny...>]

Redirecting replies to the tcl-tdbc list.

On 11/29/2012 08:11 AM, Harald Oehlmann wrote:
> On http://wiki.tcl.tk/tdbc, Kevin Kenny stated that TDBC issues should
> be reported by tickets on http://tdbc.tcl.tk/ (which seams to be linked
> to http://core.tcl.tk/tdbc , at least my ticket there appeared on the
> first (they are only ordered differently)).
>
> Now, both Ticket systems show issues:
>
> http://tdbc.tcl.tk/index.cgi/info/e226dbc23c
> and
> http://core.tcl.tk/tdbc/tktview?name=e226dbc23c
> show an error message:
> "ERROR: SQL error: no such table: ticketchng"
>
> When pressing on "New Ticket":
> http://core.tcl.tk/tdbc/tktnew
> there is an error message:
> "ERROR: no such variable: icomment"
>
> My login "oehhar" is only valid on "core.tcl.tk/tdbc", not on
> "tdbc.tcl.tk". That is why I prefer core.tcl.tk/tdbc

The two are indeed mirrored.  The tdbc tracking system on core.tcl.tk
appears to be down at the moment.  I've sent email to Richard, since
he made recent changes there, and I'm sure things will get sorted.

> ----------------------------
> Here are some implementation remarks.
>
> I don't want to blame anybody (specially not Kevin Kenny how feels quite
> allone with tdbc) but I want to understand why things are how they are.
> ----------------------------
> I wondered about the odbc implementation and the metadata query commands.
> See the summary at:
> http://wiki.tcl.tk/tdbc -> button "Return dictionaries of the metadata
> query commands"
>
> I understood, that each driver may return different dict keys.
> But why does the same driver gives the same thing many different key names.
> For example, the dict key for the table name:
> "TABLE_NAME": ODBC, tables subcommand
> "tbl_name": SQLite3, table subcommand
> "table_name": ODBC, columns subcommand
> "tableName": ODBC, primarykeys subcommand

Generally speaking, the underlying code for each driver that handles
commands like 'tables,' 'columns,' and 'primarykeys' returns the result
as a SQL result set, as if it were any other statement (In fact, in
several of the databases, the commands are implemented as SQL statements
querying the INFORMATION_SCHEMA.)

No column is deleted from this result set, by design. This allows for
inspecting driver-specific results when the underlying database is
known; various databases have additional information about tables,
columns and constraints that is not represented in the standard set
of results.

Nevertheless, portable applications want a standard set of column names
that they can depend on. TIP 308 describes, and all the drivers provide,
a standard set of column names that will be returned from [db tables],
[db columns], [db primarykeys] and so on. These result columns should
be present on all databases: if one or another is missing, that's
a bug. (Note that the last remark has to be taken with a grain of salt.
It's not uncommon, for instance, for the key to be missing in the
returned dictionary because the associated value is NULL.)

So you will see additional columns in the results returned from the
introspection commands. TIP 308's list is the minimal set.

> ----------------------------
> Method starttransaction is begintransaction
>
> In TIP 308, the method "starttransaction" is described.
> With odbc, it is implemented as "begintransaction".

TIP 308 included several editorial errors. TIP 350 contains this and
other corrections. I recommend using the man pages instead of the TIPs
for reference.

> ----------------------------
> Why is the ODBC driver not usable with tcl8.5 but the other drivers are?

As far as I know, only the SQLite3 driver remains usable with 8.5, and
even that is something of an accident: it happens not to depend on any
8.6 features.  The MySQL, ODBC and PostgreSQL drivers were all rewritten
to depend on the functionality described in TIP #357 (Export
Tcl_LoadFile).  This change turns out to be important for distributors.
A distributor can build tdbc::odbc, tdbc::mysql and tdbc::postgres
without needing to have the client libraries for the respective
databases on the build system. The library references, and the functions
therein, are resolved at runtime, when the package is required.

Unfortunately, the change was a fairly major fork to the code.
The TEA build system is quite complex, and TDBC stresses it some.
Figuring out the correct incantations needed to make an 8.5 version
link directly to the database client libraries while having an 8.6
version use Tcl_LoadLibrary simply was more than I could handle
at the time, and so those drivers - which were in beta state to begin
with, targeted to ship together with 8.6 - were shifted to an explicit
8.6 dependency.  The intent is not to run TDBC's release cycle in
lock-step with Tcl's, but in this specific case, there was a strong
dependency on a new feature of 8.6.

I'd be willing to help guide a programmer who wants to rework a version
0.18 of the drivers to build and execute against 8.5 (point out what in
the code has to change, and so on...) but at present I don't have the
bandwidth to take it on myself. This is one of several ODBC projects in
need of an implementor. (I'd like to see the Oracle driver finished,
have a driver for the SQL Server native API, and have a C version of
the SQLite3 driver - the Tcl version that we have is pretty slow and
has a couple of unfixable bugs.)

-- 
73 de ke9tv/2, Kevin