tcl-tdbc — Discussion of TDBC - Tcl DataBase Connectivity - TIP #308

Re: [Tcl-tdbc] [TCLCORE] TIP 308 to be updated.

[Frédéric B. <fre...@fr...>]

In light of the recent discussion on null handling in Tcl, I'd like to 
make the following suggestion regarding TIP #308 : that the null 
representation returned in *-as lists* result sets be configurable, and 
not be fixed to the empty string as it is currently specified (although 
it's a sensible default value). That way a sensible domain-specific 
value can be chosen (e.g. \uffff).

I hope it's not too late, I've been very busy these days in the real 
life (kids).

Re: [Tcl-tdbc] [TCLCORE] TIP 308 to be updated.

[Joe E. <jen...@fl...>]

Kevin Kenny wrote:

> I still plan to call the vote in the coming week, but will wait
> at least 48 hours after Donal confirms updating the TIP.

Since I'll be on vacation the next two weeks
with unpredictable access to email, can I cast
an early absentee ballot?

If that's OK:

TIP#308 "Tcl Database Connectivity": YES.


--Joe English

  jen...@fl...

[Tcl-tdbc] TIP 308 to be updated.

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

I've sent another batch of changes to Donal for inclusion in TIP #308.
Most of them are editorial, but there are a few that reviewers should
be aware of:

(1) The 'nextrow' method on result sets has been split into two:
       $resultset nextlist variableName
and   $resultset nextdict variableName

The 'nextrow' method, as specified in the TIP, is retained, but
database interfaces need not implement it. It is implemented in the
'tdbc::resultset' base class and delegates to the 'nextlist' or
'nextdict' method of the derived class as appropriate.  This change
saves implementors of database interfaces one round of -option
parsing.

(2) The 'transaction' method on database connections has had its
     handling of unusual status codes simplified:
       TCL_OK, TCL_BREAK, TCL_CONTINUE, TCL_RETURN: Commit the
           transaction and rethrow.
       TCL_ERROR, anything else: Rollback the transaction and
           rethrow.
     No attempt is made to decode return options on TCL_RETURN;
     the TIP warns that scripts should not 'return -code',
     'return -level' or 'return -options' to escape from a
     transaction, but that if they do, the transaction is committed.

(3) [db tables] and [db columns] were elevated to MUST.

(4) The return values for [db configure] were rationalized; it
     now returns configuration options in a parallel way to what
     [fconfigure] does.

The TIP should be on the site when Donal gets it there. I wasn't
able to do it with the 'edit' interface because the TIP content
fails to clear the spam filter.

I still plan to call the vote in the coming week, but will wait
at least 48 hours after Donal confirms updating the TIP.

-- 
73 de ke9tv/2, Kevin

[Tcl-tdbc] Pre-CFV: TIP #308

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

While implementation is incomplete, I've reached a stage in working
with TDBC that I'm confident that the API as specified is implementable
in both C and Tcl, and consider the specification stable.

Failing further substantive discussion, I intend to call the vote
on TIP 308 beginning about a week from now.

Note that I do not intend at this time to entertain further changes
to NULL handling. (Perhaps once further user experience is gained,
the subject can be revisited.) The subject has been discussed to
death, and the discussions have yielded considerably greater heat
than light.  Anything else in the TIP is fair game, if people have
API changes they think are needed, now is the time to raise them.

Please note that TIP 308 specifies the interface with an eye to
simplicity. A great many things that databases do are not included;
examples include bulk loading of data, procedures that return
references to cursors, and asynchronous query processing.
These can be added later; it is easy to write a TIP to add
functionality, but very hard to write one to remove it. It
is important to remember that much of the TIP 308 interface
must be reimplemented for every database that we intend to support.

Incomplete, but still useful, reference implementations of TIP
308 for ODBC (written in C) and SQLite (written as a Tcl wrapper
around the 'sqlite3' package) are available in the Fossil repository
at http://tdbc.tcl.tk/fossil .  The easiest way to get a current
development snapshot is to navigate to 'Leaves' and then download
the ZIP file of the newest leaf.

-- 
73 de ke9tv/2, Kevin

Re: [Tcl-tdbc] TIP 308: Placeholders in prepared statements - an alternative perspective

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

Jonathan Leffler wrote:
> Also, I should point out that my perspective is from the Informix ESQL/C 
> viewpoint; there is also a choice of two separate ODBC drivers (one 
> talking native Informix SQLI protocol, the other talking the IBM DRDA 
> protocol) which do all sorts of weird analysis of SQL (there's a 
> significant chunk of a complete SQL parser in the drivers).  It may be 
> better to write the TDBC driver using one of those, instead of ESQL/C, 
> because the problems can be punted; they become SEPs -- someone else's 
> problem.

Great.  I like Other People's Problems.

> OK - but then we need to be sure that we have defined the comment 
> conventions that must be supported.  Informix supports double-dash to 
> end-of-line (per the standard); it also supports C style slash-star to 
> star-slash comments.  These are both in SQL 2003 and so should, 
> presumably, be supported by TDBC.

Right.  The supplied tokenizer recognizes both -- and /*...*/.
They're removed from the native SQL before statements are prepared,
so a SQL that lacks one or the other syntax would also be covered.
(Assuming

> Also, for legacy reasons (that is, from the days before the SQL-86 
> standard) Informix supports open-brace to close-brace except when the 
> open brace is syntactically significant as something other than a 
> comment - the context sensitive stuff I mentioned.  It's loathsome; it's 
> there.  If I read the SQL/Foundation accurately, then <left brace> is 
> used in one context and that is in a regular expression.  So, the 
> chances are braces won't have much effect on portable code - portable 
> code won't use braces.

OK. So TDBC doesn't need to worry about Informix's braces. Good.

> Treat these remarks as a precaution - a warning.  Beware of making it 
> too onerous for people to adhere to your proposed standard.

What the ODBC bridge does is to rewrite ':variable' that appears
outside a comment, single quoted string, or double quoted column
name, replacing it with a ?. It remembers the position, so the
Tcl code sees the parameter by name, but the ODBC driver sees the
question mark.  That's obviously not adequate to handle Informix's
schema::table or column:type notations, but it handles the simple
case of variable substitution quite nicely. I'm still not
entirely convinced that we have to give up named parameters.

We'd need to look more closely at the remaining Informix syntax
quirks. I admit that I had been unaware of them; I'd looked
fairly closely at Oracle, Sybase, SQL Server, Jet, MySQL,
Postgres95, SQLite and DB2, but was ignorant of how Informix
"interprets" the standard.  My guess is that we can come up
with either appropriate _ad hoc_ tokenization that handles
common cases, or else layer atop ODBC (the ODBC bridge is already
there) and let the driver worry about it for us.

-- 
73 de ke9tv/2, Kevin

Re: [Tcl-tdbc] TIP 308: Placeholders in prepared statements - an alternative perspective

[Jonathan L. <jon...@gm...>]

On Sun, May 11, 2008 at 11:46 AM, Kevin Kenny <kk...@ny...> wrote:

> Jonathan Leffler wrote:
>
> > Presumably, TIP 308 should be upgraded to recognize that:
> >
> > SELECT ':name' FROM SomeTable;
> >
> > is not a placeholder but a string.
> >
>
> Indeed.  Moreover, ":name" is not a placeholder but a column name,
> and --:name is a comment. You're right, the description of the
> tokenizer should be clarified to reflect what it actually does.
>

Again - true enough in the SQL standard, but Informix uses single quotes and
double quotes interchangeably for strings unless an environment variable,
DELIMIDENT, is set, whereupon it behaves according to the standard.  It may
be the that TDBC driver for Informix has to set that.

Also, I should point out that my perspective is from the Informix ESQL/C
viewpoint; there is also a choice of two separate ODBC drivers (one talking
native Informix SQLI protocol, the other talking the IBM DRDA protocol)
which do all sorts of weird analysis of SQL (there's a significant chunk of
a complete SQL parser in the drivers).  It may be better to write the TDBC
driver using one of those, instead of ESQL/C, because the problems can be
punted; they become SEPs -- someone else's problem.


What about comments?  Which comment conventions?  (Informix has 3, just for
> fun!  And one of those is context sensitive, to boot.  No, I don't want to
> have to explain - but be aware that real systems have weird notations that
> are not part of the standard.)
>

I think we have to approach these from a "lowest common denominator"
> perspective.  Really, we have two possibilities.
>
> (1) Accept the local database's dialect of SQL, and its notation
>    for substitutions.  That means that all calling code has to
>    be adapted to the local database.  I tend to think this sort
>    of defeats the purpose of TDBC as a portability layer.
>

OK - but then we need to be sure that we have defined the comment
conventions that must be supported.  Informix supports double-dash to
end-of-line (per the standard); it also supports C style slash-star to
star-slash comments.  These are both in SQL 2003 and so should, presumably,
be supported by TDBC.

Also, for legacy reasons (that is, from the days before the SQL-86 standard)
Informix supports open-brace to close-brace except when the open brace is
syntactically significant as something other than a comment - the context
sensitive stuff I mentioned.  It's loathsome; it's there.  If I read the
SQL/Foundation accurately, then <left brace> is used in one context and that
is in a regular expression.  So, the chances are braces won't have much
effect on portable code - portable code won't use braces.

(2) Do some limited rewriting of queries from a subset of standard
>    SQL to whatever the local database's notation is.  This is the
>    approach taken in both TDBC and JDBC; I'm reasonably certain that
>    if there is an Informix JDBC driver, it will accept
>    somedb.usertable and rewrite to somedb:usertable, accept
>    TO_INTEGER(mycolumn) and rewrite to mycolumn::Integer,
>    and so on.  This places a much heavier onus on driver implementors
>    who must deal with nonstandard systems, but has the advantage
>    that calling code is uniform.  It also constrains the system
>    to a "lowest common denominator" approach, since the more
>    esoteric aspects of a native database may well not be
>    reflected. This in turn implies that there will likely
>    still be a place for full native APIs for applications
>    that need a specific database's capabilities. These apps
>    will _ipso facto_ remain nonportable.
>

Clearly, the less of this there is to do, the better.  Writing a full-scale
parser is onerous at best; writing an accurate ad hoc parser is just about
as bad.  TTBOMK, the SQL standard only supports question marks as
placeholders -- that would be my recommendation/preference.  It's the lowest
common denominator, and it is the standard.  (You can find rules in the
standard about colon and host identifier, but those apply to embedded SQL,
and not to SQL statements that are not in embedded SQL.  To claim that TDBC
or TIP 308 is using embedded SQL would be a large leap, IMO; large and not
dreadfully accurate.

Treat these remarks as a precaution - a warning.  Beware of making it too
onerous for people to adhere to your proposed standard.

-- 
Jonathan Leffler <jon...@gm...> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

Re: [Tcl-tdbc] TIP 308: Placeholders in prepared statements - an alternative perspective

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

Jonathan Leffler wrote:
> Presumably, TIP 308 should be upgraded to recognize that:
> 
> SELECT ':name' FROM SomeTable;
> 
> is not a placeholder but a string.

Indeed.  Moreover, ":name" is not a placeholder but a column name,
and --:name is a comment. You're right, the description of the
tokenizer should be clarified to reflect what it actually does.

> What about comments?  Which comment conventions?  (Informix has 3, just 
> for fun!  And one of those is context sensitive, to boot.  No, I don't 
> want to have to explain - but be aware that real systems have weird 
> notations that are not part of the standard.)

I think we have to approach these from a "lowest common denominator"
perspective.  Really, we have two possibilities.

(1) Accept the local database's dialect of SQL, and its notation
     for substitutions.  That means that all calling code has to
     be adapted to the local database.  I tend to think this sort
     of defeats the purpose of TDBC as a portability layer.

(2) Do some limited rewriting of queries from a subset of standard
     SQL to whatever the local database's notation is.  This is the
     approach taken in both TDBC and JDBC; I'm reasonably certain that
     if there is an Informix JDBC driver, it will accept
     somedb.usertable and rewrite to somedb:usertable, accept
     TO_INTEGER(mycolumn) and rewrite to mycolumn::Integer,
     and so on.  This places a much heavier onus on driver implementors
     who must deal with nonstandard systems, but has the advantage
     that calling code is uniform.  It also constrains the system
     to a "lowest common denominator" approach, since the more
     esoteric aspects of a native database may well not be
     reflected. This in turn implies that there will likely
     still be a place for full native APIs for applications
     that need a specific database's capabilities. These apps
     will _ipso facto_ remain nonportable.

-- 
73 de ke9tv/2, Kevin

[Tcl-tdbc] TIP 308: Placeholders in prepared statements - an alternative perspective

[Jonathan L. <jon...@gm...>]

TIP 308 says:

The database interface MUST support substitutions in *SQL-code*. Each
substitution request has the form *:variableName*. That is, each
substitution request begins with a literal colon (:), followed by a letter
or underscore, followed by zero or more letters, digits, or underscores. The
database interface is responsible for translating from this syntax to
whatever the underlying engine requires. Typical strings required in
database interfaces are *:name*, *:number*, *@name*, *@number*, and *?*.

This presents a problem for the following valid Informix code, which has no
host variables in use whatsoever:

SELECT * FROM somedb:user.table
    WHERE SomeColumn::Integer = 42
      AND DATETIME(2008-05-10 23:30) YEAR TO MINUTE < CURRENT;

That contains the sequence ':user' which you are demanding is a placeholder;
it also contains the sequence :Integer, which you are also demanding is a
placeholder.  Neither is a placeholder in Informix's dialect of SQL.  The
first colon-name is a database-qualified table name - probably a reference
to a table in a remote database, though it could be in the local DB if the
user was careless.  I'm not sure that there's an easy way to disambiguate
this; it requires considerable knowledge of the structure of SQL statements
to know whether a placeholder is permitted at any given location in an SQL
statement.  The second colon-name is a cast -- the double-colon could be
used to disambiguate that.

DBD::Informix (for Perl - sorry, I've been looking after it even longer than
I've been involved with isqltcl) does not accept either :name or :12
notation for placeholders because the sequence can occur in native SQL in a
number of contexts - see the weird DATETIME constant notation which, unlike
standard SQL, is not inside quotes.  The Informix parser probably could
treat DATETIME( ... ) as a funny sort of string - and TIP 308 does not
mention :12 as a placeholder for the source code, so it may not be an issue.

Presumably, TIP 308 should be upgraded to recognize that:

SELECT ':name' FROM SomeTable;

is not a placeholder but a string.

What about comments?  Which comment conventions?  (Informix has 3, just for
fun!  And one of those is context sensitive, to boot.  No, I don't want to
have to explain - but be aware that real systems have weird notations that
are not part of the standard.)

-- 
Jonathan Leffler <jon...@gm...> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

[Tcl-tdbc] tcl-tdbc mailing list

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

If you have received this message, you are either subscribed to the
'tcl-core' mailing list, or else are listed as a developer of one or
more Tcl extensions that deal with SQL database connectivity.
I am sending this message because those two groups of people are
ones that are likely to have an interest in TIP #308:
Tcl DataBase Connectivity (TDBC):  http://tip.tcl.tk/308

I have opened up a new mailing list for discussion of this TIP.
Subscription information can be obtained at
https://lists.sourceforge.net/lists/listinfo/tcl-tdbc

I have taken this action because I believe that TIP #308 is
now far enough along to allow community development. In particular,
here is a brief status report.

TDBC is implemented as a layer atop TclOO (TIP #257 -
http://tip.tcl.tk/257)  There are a set of base classes that
the developer of a database interface can use to represent
database connections, statements and results.  There is also
(thanks to Richard Hipp) a tokeniser for SQL code to simplify
the job of substituting data from Tcl into variables in
a SQL statement.

As a proof of concept, to show that TDBC interfaces can be
built in both Tcl and C, I've done most of the implementation
of a TDBC-ODBC bridge (in C), and a TDBC-Sqlite3 interface
(in Tcl).  These are not yet broadly usable, owing to some
rather nasty bugs in the current TclOO reference implementation,
but include at least (somewhat) complete code, and a
reasonably large test suite. Documentation other than
the TIP is lacking at this point, but I promise to work on
that bit!  In any case, both interfaces run, more or less,
on both Windows XP and Ubuntu (with the ODBC interface tested
against Jet and Sqlite).  Not all tests pass (I *said* that
it's incomplete!), and in fact the ODBC bridge crashes
because of some method resolution problems in TclOO,
but in fact some of the failures are Heisenbugs - some of
the cases that fail succeed when run individually.

In order (I hope) to make community development a bit easier,
I have decided at this stage to use the Fossil distributed
configuration management system. Information about this system,
for those that are unfamiliar with it, can be obtained at
http://www.fossil-scm.org/. At the moment, the repository is
located at http://kbk.is-a-geek.net:2301/.  This is my home
server, not some big collocation box, so please try to treat
it gently!  In particular, I'm hoping to recruit developers
so that TDBC will initially support most of the popular
database engines; it would be nice for it to come
"out of the box" with a broad base underneath it.

Before the TIP is ready for vote, I want to get input from
the community on a few questions that I have about its
"corner cases". I'll raise those on the mailing list in the
next few days. (Of course, another condition of TIP 308
being ready for vote is the acceptance of TIP 257 [TclOO],
but since TDBC's APIs would be easy to layer atop a
different object system, I'm confident that it can
be made release-worthy with or without TclOO.)

-- 
73 de ke9tv/2, Kevin

[Tcl-tdbc] TDBC mailing list

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

This mailing list is set up for discussion of the Tcl DataBase
Connectivity application programmers' interface (API), as
described in TIP #308 (http://tip.tcl.tk/308).