Re: Standard DBI Proposal (was: Re: Standard Database Interface?)

On Oct 23, 9:33 am, "Donal K. Fellows" <donal.k.fell...@xxxxxxxxx>
wrote:

Comments?

Donal.

Well I have, IMHO, a decent set of canned statement templates. The
idea
is to exploit some performance enhancing features of one dialect of
SQL
over another, while at the same time remove a little bit of the
complexity
on the part of the user.

With each TDIF connector is an SQL translator that takes Tcl data and
uses it to formulate the appropriate statement in the native dialect
of the database. For instance, in many SQL's, there is no equivalent
to
"UPDATE OR REPLACE" that a lot of us use in sqlite. The introspection
techniques for database schema also vary. MySql uses the "SHOW"
command.
Sqlite uses a data table. Microsoft SQL pushes a lot of the
introspection
out to ODBC.

So at the risk of another long post, here's a quick draft of the "Sql
dialect"
functions I've developed for my own systems:

primary_key table - Return the primary key (be it from the driver
or a query) Can return multiple columns

stmt_insert {keylist valuelist dtable} - Generate an insert statement
stmt_exists {keylist dtable} - Return 1 if a record matches keylist
in table
stmt_replace {keylist valuelist dtable} -
Generate an "UPDATE" statement if the record exists, an INSERT
if it doesn't, or exploit the nature "INSERT OR REPLACE" feature
of the SQL dialect
stmt_select {keylist fieldlist dtable} -
Generate a SELECT statement
stmt_update {keylist valuelist dtable} -
Generate an UPDATE statement
stmt_where {keylist {forbid_null 0}} -
Generate a conditional expression from keylist, optionally
throwing an error if a field is NULL
stmt_delete {keylist dtable} -
Generate a DELETE statement
table_qualify {rawtable} -
Take a raw string and figure out if we need to insert a/o
remove the db name
column_qualify {rawcolumn {table {}}} -
Like table qualify, but for columns
table_exists table -
Return 1 if table exists, zero otherwise
sqlprep value -
Escape and quote a value in the manner the database engine
likes best. (i.e. foo -> 'foo')
sqlfix data -
Escape a string the way the database engine expects
table_create {table schemaDict} -
Return a table create statement
stmt_create_column {field infoDict} -
Used internally by table_create, it translates our Tcl API
column types into the most appropriate for the database
platform.
stmt_create_index {idxtable idxname infoDict} -
Used internally by table_create, translates our TCL API
notation for index definitions into the most appropriate
for the database engine
searchFullText {value {columns {}}}
Perform a full text search where supported, or fake it
with some "like" statements where it's not

My system also has a query builder
searchStmtPrim {var op val} -
Return an expression, mapping "op" from our API standard to
the database specific one, if it exists, or the best analog
to it. For instance {foo soundex bar) will use the native
soundex check in the database if it exists, or will fake it
in script if it doesnt
searchStmtJoin {joinop stmtl} -
Join together several search prims. Sure, it's the same in
sql, but I have grand ambitions of including LDAP into this
mess
searchStmtNest stmtl -
For SQL, basically wrap a join in "( )". Other database systems
may do it differently

I have a more complete explanation of all this in my TDIF paper:
http://www.etoyoc.com/tao/download/TDIF-Paper.pdf

So whaddya all say we assemble the best ideas discussed in this
thread and hammer out a successor to TDIF, written from scratch
in an OOP to be decided.

At the conference we came up with a name that I really wish
I had thought of before I started tagging all my code:
TOAD: Tcl Objects Accessing Data

Right now my stiff is written in my bastardized Incr Tcl-like
pseudocode "Tao". I'm perfectly willing to rewrite it in SNIT,
pure namespace code, or whatever form you guys would find useful.

-The Hypnotoad

.