Standard Database Interface?
tclt...@aol.com
database interface. Sybtcl is unusable so I resorted to snodbc to
access Sybase ASE but I don't think snodbc will be maintained in the
future. So I'm planning to use something else to develop my next
project after I deliver my current project. It's true that postgres
and oracle database tcl api are currently maintained but some of my
clients are using Sybase.
I really like tcl/tk but I don't want to be in hot water in the
future. I really hope someone will do something about this. I know
that tcl/tk is used for many things but for I make a living by
developing business applications and w/o a standardize database
interface and tcl database bindings for popular database would deter
me and others from using tcl/tk for this purpose.
I am now using/maintaining the following database servers:
ASE Sybase 12.5.x since
2001 Windows/Linux
Oracle
2005 Windows/Linux
Postgres
2004 Windows/Linux
MS SQL Server 2000
2005 ---
Aric Bills
> I really hope someone will do something about this.
With all due respect, why not take your future into your own hands by
tackling the problem yourself? Nothing wrong with throwing out an
appeal for somebody to adopt the issue, but there are more certain
ways to achieve the results you want--especially considering that you
are more likely to have the relevant experience, understanding of the
problem, access to the databases in question, interest, etc. than the
average c.l.t. reader.
You give the impression that snodbc meets your current needs. Have
you contacted Anton Kovalenko about his intentions to maintain it? If
he doesn't intend to maintain it, you might seriously consider
volunteering for the job.
Wojciech Kocjan
Aric,
While I do understand your point that all Tcl related code is opensource
and everyone who needs it might just step in and do the job. I partly
agree with that and encourage people to do so, but ...
There are a couple of issues - first of all, snodbc is written in C - and
not everyone coding in C really wants to and knows C that good. Also,
since it's native binary, he/she will encounter a lot of issues with libc
on Linux and so on - so stepping into such a job is not trivial.
And if that's his/her company's issue, then a lot needs to be done to
decide how to proceed - for example predict cost of maintaining the tool,
cost of maintaining required HW for compiling and so on. I doubt any major
company would accept such a possibility - they'd rather switch to another
language with better support for their DBs.
The second issue is very different - a consistent API for various
databases is something that Tcl lacks a lot - and I think it's one of not
so many languages that don't have such a standard set up. One of the
reasons is that since there's no standard API, migration or porting
between different DB engines is a huge mess. SQL itself is not that
compliant for everything to work like a charm, but for many applications
the most time consuming thing would be to convert calls between different
DB interfaces.
I personally did such a toolkit to allow standarized DB access - tcldb
(http://dqsoftware.sourceforge.net/). It does not support Sybase and MS
SQL, but if the author is interested then it's possible to add it and I
can easily help on this. It does support Oracle and PostgreSQL though.
--
Wojciech Kocjan
Aric Bills
> There are a couple of issues - first of all, snodbc is written in C - and
> not everyone coding in C really wants to and knows C that good. Also,
> since it's native binary, he/she will encounter a lot of issues with libc
> on Linux and so on - so stepping into such a job is not trivial.
That's understandable. And it brings up an interesting issue that
others have mentioned recently--that while there are good resources to
help people become proficient Tcl programmers, there are fewer aimed
at helping Tcl programmers become proficient enough in C to write
their own extensions or help with the Tcl core. While I've done a
little work writing C extensions, I feel like there's a lot I don't
understand, both about C and about Tcl's C API. If somebody wrote a
book devoted to the Tcl/C interface (Tcl C philosophy; writing
extensions; embedding Tcl; understanding and modifying the core [maybe
a roadmap--where is the code that implements Tcl command <X>?]; stubs;
and the bytecode engine), I'd buy it. However, I'm not the one to
write it (maybe in 10 years...).
> And if that's his/her company's issue, then a lot needs to be done to
> decide how to proceed - for example predict cost of maintaining the tool,
> cost of maintaining required HW for compiling and so on. I doubt any major
> company would accept such a possibility - they'd rather switch to another
> language with better support for their DBs.
Sure.
> The second issue is very different - a consistent API for various
> databases is something that Tcl lacks a lot - and I think it's one of not
> so many languages that don't have such a standard set up. One of the
> reasons is that since there's no standard API, migration or porting
> between different DB engines is a huge mess. SQL itself is not that
> compliant for everything to work like a charm, but for many applications
> the most time consuming thing would be to convert calls between different
> DB interfaces.
>
> I personally did such a toolkit to allow standarized DB access - tcldb
> (http://dqsoftware.sourceforge.net/). It does not support Sybase and MS
> SQL, but if the author is interested then it's possible to add it and I
> can easily help on this. It does support Oracle and PostgreSQL though.
>
> --
> Wojciech Kocjan
I can see the desirability of a standardized database API. Kudos to
you for being willing to fill the void.
Aric
Michael Schlenker
> [...] While I've done a
> little work writing C extensions, I feel like there's a lot I don't
> understand, both about C and about Tcl's C API. If somebody wrote a
> book devoted to the Tcl/C interface (Tcl C philosophy; writing
> extensions; embedding Tcl; understanding and modifying the core [maybe
> a roadmap--where is the code that implements Tcl command <X>?]; stubs;
> and the bytecode engine), I'd buy it. However, I'm not the one to
> write it (maybe in 10 years...).
read in most places (some subsystems like the re engine etc. are a bit
too dense...). Quite a bit of the C interface stuff is covered in
Ousterhouts book on Tcl.
>> The second issue is very different - a consistent API for various
>> databases is something that Tcl lacks a lot - and I think it's one of not
>> so many languages that don't have such a standard set up. One of the
>> reasons is that since there's no standard API, migration or porting
>> between different DB engines is a huge mess. SQL itself is not that
>> compliant for everything to work like a charm, but for many applications
>> the most time consuming thing would be to convert calls between different
>> DB interfaces.
>>
>> I personally did such a toolkit to allow standarized DB access - tcldb
>> (http://dqsoftware.sourceforge.net/). It does not support Sybase and MS
>> SQL, but if the author is interested then it's possible to add it and I
>> can easily help on this. It does support Oracle and PostgreSQL though.
>>
I can just recommend tcldb, its really nice and powerful to work with,
but could need a better distribution format (the UI parts, a designer
and lots of other stuff is mixed with the basic db layers in the
available package, easily extractable, but a little nuisance.)
Michael
Michael Schlenker
http://wiki.tcl.tk/14972
compares all the major offerings for Tcl database abstraction...
Michael
Wojciech Kocjan
napisał(a):
> I can just recommend tcldb, its really nice and powerful to work with,
> but could need a better distribution format (the UI parts, a designer
> and lots of other stuff is mixed with the basic db layers in the
> available package, easily extractable, but a little nuisance.)
Well, I'm not developing it that eager anymore, I guess it does make sense
to try and split the package - into tcldb and tkdb perhaps?
And the designer's editing interface is basically awful - it's still
better than writing text files, at least for me.
Anyway - as other people advocated before, since it's all on SF (and CVS
is up to date), if someone is willing to either try to improve designer or
work on nicer packaging (ie splitting the docs, or even automating the
packaging process somehow), I'm more than willing to add them to the SF
project.
Of course, people contributing to support for wider database range are
also welcome!
--
Wojciech Kocjan
Larry W. Virden
> > I really hope someone will do something about this.
>
> With all due respect, why not take your future into your own hands by
> tackling the problem yourself?
Many businesses, and developers, have the situation where they prefer
to solve the problems of the day, not to take on maintaining
infrastructure.
Answers of "do it yourself", while certainly valid, do nothing to
encourage those considering Tcl to move forward. I myself have seen
developers accomplished in writing C code move on from Tcl because
specific extensions were no longer supported. Certainly they _could_
support it. But with so many languages vying for a developer's
attention, telling someone to "do it yourself" is going to be weighed
against "comes with the language out of the box" and, in general,
going to be considered a negative.
Sean Woods
>
> I really like tcl/tk but I don't want to be in hot water in the
> future. I really hope someone will do something about this. I know
> that tcl/tk is used for many things but for I make a living by
> developing business applications and w/o a standardize database
> interface and tcl database bindings for popular database would deter
> me and others from using tcl/tk for this purpose.
>
Just so you know, this is an issue that we are aware of in the Tcl
Community,
and we have a few projects that are underway that are designed to do
exactly
what you have in mind:
Give a consistent interface to database engines.
I presented a paper at this year's conference about my current
approach,
TDIF (Tcl Data Interface Framework). TDIF is a which is a script
level
wrapper around the various binary drivers and sql dialects. There is
also
some talk amoungst the database driver maintainers about exchanging
notes
on how the APIs should be designed. Look for some TIPs on the subject
this year.
I presently maintain several MySql, MSSQL, Sqlite, and LDAP databases.
The
layer I've written is essentially laziness on my part. I like to write
in
pseudocode, and I think in terms of "query" "cmnd" or "query_flat".
(Though
the sqlite eval that will sub the data into a script is going to be
adapted
for the rest.)
The library I'm working on is being cleaned up for submission into
Tcllib.
You can download snapshots from my website:
(It uses an OOP system I wrote called Tao. The database level stuff
would
probably do better in Will's SNIT.)
--Sean
Robert Heller
>
> Dnia 15-10-2007 o 10:57:23 Aric Bills <aric....@gmail.com> napisa³(a):
>
> > On Oct 14, 9:30 pm, tcltk...@aol.com wrote:
> >> I really hope someone will do something about this.
> >
> > With all due respect, why not take your future into your own hands by
> > tackling the problem yourself? Nothing wrong with throwing out an
> > appeal for somebody to adopt the issue, but there are more certain
> > ways to achieve the results you want--especially considering that you
> > are more likely to have the relevant experience, understanding of the
> > problem, access to the databases in question, interest, etc. than the
> > average c.l.t. reader.
> >
> > You give the impression that snodbc meets your current needs. Have
> > you contacted Anton Kovalenko about his intentions to maintain it? If
> > he doesn't intend to maintain it, you might seriously consider
> > volunteering for the job.
>
> Aric,
>
> While I do understand your point that all Tcl related code is opensource
> and everyone who needs it might just step in and do the job. I partly
> agree with that and encourage people to do so, but ...
>
> There are a couple of issues - first of all, snodbc is written in C - and
> not everyone coding in C really wants to and knows C that good. Also,
> since it's native binary, he/she will encounter a lot of issues with libc
> on Linux and so on - so stepping into such a job is not trivial.
Actually, snodbc is NOT written in is, it is in snit (pure Tcl). It
uses a 'standard' C-coded Tcl extension (ffidl) that lets Tcl programs
gain access to standard C-coded libraries (like unixODBC's libodbc.so).
So long as the ffidl extension is maintained, maintaining snodbc is
just a matter of maintaining some pure Tcl code using snit.
>
> And if that's his/her company's issue, then a lot needs to be done to
> decide how to proceed - for example predict cost of maintaining the tool,
> cost of maintaining required HW for compiling and so on. I doubt any major
> company would accept such a possibility - they'd rather switch to another
> language with better support for their DBs.
>
> The second issue is very different - a consistent API for various
> databases is something that Tcl lacks a lot - and I think it's one of not
> so many languages that don't have such a standard set up. One of the
> reasons is that since there's no standard API, migration or porting
> between different DB engines is a huge mess. SQL itself is not that
> compliant for everything to work like a charm, but for many applications
> the most time consuming thing would be to convert calls between different
> DB interfaces.
>
> I personally did such a toolkit to allow standarized DB access - tcldb
> (http://dqsoftware.sourceforge.net/). It does not support Sybase and MS
> SQL, but if the author is interested then it's possible to add it and I
> can easily help on this. It does support Oracle and PostgreSQL though.
>
--
Robert Heller -- Get the Deepwoods Software FireFox Toolbar!
Deepwoods Software -- Linux Installation and Administration
http://www.deepsoft.com/ -- Web Hosting, with CGI and Database
hel...@deepsoft.com -- Contract Programming: C/C++, Tcl/Tk
tclt...@aol.com
I still really enjoy programming in tcl/tk. It's like a contagious
disease that has infected me I just can't get it out of my system. I
seem to be very fascinated of its quirky way of scripting.
> Sean Wood
> Just so you know, this is an issue that we are aware of in the Tcl
> Community, and we have a few projects that are underway that are
> designed to do exactly what you have in mind:
I'm really glad to know that.
> Robert Heller
> Actually, snodbc is NOT written in is, it is in snit (pure Tcl). It
> uses a 'standard' C-coded Tcl extension (ffidl) that lets Tcl programs
> gain access to standard C-coded libraries (like unixODBC's libodbc.so).
> So long as the ffidl extension is maintained, maintaining snodbc is
> just a matter of maintaining some pure Tcl code using snit.
That's really good to know. I visited http://www.elf.org/ffidl/ and it
seems it was last updated in 2005. I hope this means that ffidl is
really that stable and doesn't need frequent update. Instead a warning
as being abandoned.
> Larry W. Virden
> Answers of "do it yourself", while certainly valid, do nothing to
> encourage those considering Tcl to move forward. I myself have seen
> developers accomplished in writing C code move on from Tcl because
> specific extensions were no longer supported. Certainly they _could_
> support it. But with so many languages vying for a developer's
> attention, telling someone to "do it yourself" is going to be weighed
> against "comes with the language out of the box" and, in general,
> going to be considered a negative.
I agree to that. I used to program in C when I was in college but now
any attempt to do that would only get me trouble.
tclt...@aol.com
Where can I find the examples on how to use taodbi? I need plenty of
examples because I'm not that smart. What databases are supported? Do
I need install packages for postgres, oracle and tclodbc?
Thanks.
tclt...@aol.com
link to CPAN and found database api for so many databases and was very
envious. How I wish we could have something like that in tcl/tk.
Here's the link http://search.cpan.org/search?query=DBD%3A%3A&mode=module.
Larry W. Virden
> I was surfing the web randomly and came across perl. I clicked the
> link to CPAN and found database api for so many databases and was very
> envious. How I wish we could have something like that in tcl/tk.
There are two factors in why perl has support for so many databases.
1. I believe most of the support is because someone proposed, wrote,
debugged, documented, and promoted a common database interface that
was flexible enough to be useful with many databases
2. The community of developers wrote and promoted support for the
databases they use, which then got used by the perl community
There have been a number of developers who have implemented something
like 1 for Tcl.
Perhaps the Tcl community of developers just don't use as many
databases. Or perhaps there is just too much NIH (not invented here)
syndrome (where developers would rather write their own private code
than use shared code).
Donal K. Fellows
> Perhaps the Tcl community of developers just don't use as many
> databases. Or perhaps there is just too much NIH (not invented here)
> syndrome (where developers would rather write their own private code
> than use shared code).
There have been many attempts to define a common database interface,
but they've often not been as good as the Best Of Breed interfaces out
there. I don't know about you, but I hate to take a backwards step
just for compatibility with third-party software I won't ever use. :-)
As such, the real question is whether we should instead try to work
out what a common database interface ought to look like. I think so,
and I think we ought to make sure that it is a good one. But it should
also be a very Tcl-like interface, and should conceal much of the
details that are specific to a particular database. We should also be
clear that there are some parts which are not part of the common
interface (which probably includes the opening of a connection to the
database).
Donal.
Gerald W. Lester
I have a candidate interface syntax -- the one for Sqlite. The syntax fits
*very* well with Tcl.
--
+--------------------------------+---------------------------------------+
| Gerald W. Lester |
|"The man who fights for his ideals is the man who is alive." - Cervantes|
+------------------------------------------------------------------------+
Patrick Finnegan
Use Tclblend with JDBC drivers. See www.patrickfinnegan.com for
Windows binary and examples at http://aspn.activestate.com/ASPN/Cookbook/Tcl?kwd=TclBlend.
Sean Woods
I second that.
Sean Woods
> Where can I find the examples on how to use taodbi? I need plenty of
> examples because I'm not that smart. What databases are supported? Do
> I need install packages for postgres, oracle and tclodbc?
>
> Thanks.
You would need install packages for postgres, oracle, and tclodbc.
Taodbi
is a wrapper layer on top of the existing driver. Unfortunately, I
don't have
a wrapper at present for either postgres or Oracle. (Postgres is easy
enough to
come by, but a live Oracle system is a teensy bit more of a pain to
set up to
test.)
Once you have a wrapper though it would simply be
package require taodbi-connectors
tao::object_create myDbObject [list class theDriverClass ...
(connection info)...]
myDbObject query_flat "select field1,field2 from someTable where
field3='somevalue'"
(List returned)
myDbObject cmnd "update someTable set field1='newValue' where
primary_key='someKey'"
There are some more complicated examples for when you want the object
to generate
the query for you, but essentially the point of TaoDbi is to make it
damn simple
to string up a database.
tclt...@aol.com
wrote:
If I were to use tclblend would I lose any functionality? Can I still
use native tcl and binary packages. What is the trade off?
Patrick Finnegan
TclBlend supports all native tcl and binary packages that work with
Tcl 8.4.13. The trade off is TclBlend supports point releases of Tcl
and Java i.e Tcl/TK 8.4.13 and Java 1.4.2.12. Compiling other
versions of tcl/java may work but is untested. AFAIK only a Windows
binary is available so other platforms require local compilation of
the source unless of course someone builds installation packages for
distribution to the target platform. I suggest you grab the Windows
version and test your scripts against it. Just copy any required none
core tcl and third party packages to the installation lib directory
after expanding the zip file. For non Windows platforms download from
http://tcljava.sourceforge.net/docs/website/index.html and
compile.
Lan
wrote:
> I can just recommend tcldb, its really nice and powerful to work with,
> but could need a better distribution format (the UI parts, a designer
> and lots of other stuff is mixed with the basic db layers in the
> available package, easily extractable, but a little nuisance.)
>
> Michael
Apropos of nothing but relative to this:
I have the luxury of developing my stuff against only one back end
(PostgreSQL). However portability is a Good Thing. Are there any
advantages/disadvantages for me switching to tcldb?
Michael Schlenker
- You don't have the full postgres interface available, as some parts
like LISTEN etc. are not implemented in tcldb.
- You add another layer, which is slower.
Advantages:
- You can more easily use your code with a different database
- You get the nice table definition language of tcldb if you want it
Portability is nice, but especially for SQL its overrated. If you need
all the speed you can get from your database, use the native layer, if
you just need any db layer something like tcldb is good.
Michael
Donal K. Fellows
> I have a candidate interface syntax -- the one for Sqlite. The syntax fits
> *very* well with Tcl.
That's what I had in mind too, but I didn't want to prejudice
others. :-)
Donal.
tclt...@aol.com
I'm very dependent on ready made binary distribution so can someone be
patient enough to show me a step by step method on how to compile
tclblend on ubuntu linux. I notice on MS Windows binary that there is
a folder named j2sdk1.4.2_12. I'm sure I need to include this.
Thanks.
Aric Bills
I agree. But in the event that no one else tackles an issue that you
consider a show-stopper, there are only two options--deal with it
yourself or switch languages. Either one involves a lot of time and
effort. Since the OP led me to believe that he was willing to invest
some serious effort and he had a particular affinity for Tcl, I
thought it was at least worth pointing out that, in the worst case
scenario where nobody else would be willing to address his issue, he
theoretically had an option that would allow him to stay with Tcl.
Not a thrilling option, but--he does say that the extension currently
works for him. I don't understand the issues that prompt his concerns
about the extension's future, but I'm sure he does, and in my mind
that understanding, coupled with his considerable database expertise,
makes him a good candidate to maintain the package that allows him to
keep his day job. The suggestion wasn't meant in an unkind way but
rather as a possible (last-resort) option in the event that no other
solution was forthcoming from the community.
Larry W. Virden
The suggestion wasn't meant in an unkind way but
> rather as a possible (last-resort) option in the event that no other
> solution was forthcoming from the community.
And I think that it is useful to point this out, particularly if one
has a strong commitment to Tcl.
However, I know of a specific case where a group with a tremendous
investment in Tcl found they were not willing to divert programming
resources from revenue generating projects to supporting an
infrastructure type database package. The business case was made that
it was cheaper to move to a different language for the application in
question than to take on the learning, documenting, and supporting of
a package that was not working and that had no community support.
So things can go both ways. But it is appropriate and useful to remind
people that they are not trapped if the source code is available.
Patrick Finnegan
I only work with Windows systems. Suggest you post to tcljava mail
list re Ubuntu. See
https://lists.sourceforge.net/lists/listinfo/tcljava-user.
The Unix compile instructions are included with the Unix source
download at http://sourceforge.net/project/showfiles.php?group_id=13005.
The physical location of the Java runtime is specified at compile
time. Since we don't know the location or version of the Java
runtime on the end user's machine the Java runtime needs to be
distributed with the TclBlend binary. For Windows the Java runtime is
placed in a subdirectory of the directory to hold the Tcl binary which
is specified at compile time.
Patrick Finnegan
>
> The Unix compile instructions are included with the Unix source
>
> The physical location of the Java runtime is specified at compile
> time. Since we don't know the location or version of the Java
> runtime on the end user's machine the Java runtime needs to be
> distributed with the TclBlend binary. For Windows the Java runtime is
> placed in a subdirectory of the directory to hold the Tcl binary which
> is specified at compile time.
If you decide to use JDBC via TclBlend they you may be able to use
Hibernate ORM as well. Here is an article on using Hibernate outside
the container from a standalone command line Java app.
http://www.onjava.com/pub/a/onjava/2005/11/16/hibernate-for-java-se.html
thel...@gmail.com
quote <but a live Oracle system is a teensy bit more of a pain to set
up>
Note much of a pain at all. Oracle XE (free) is an RPM setup on Linux
and is an installable executable on windows.
OraTcl works just fine with Oracle XE.
When the community adopts a common DB API. I will write wrappers to
OraTcl to make it work.
-Todd
Sean Woods
> quote <but a live Oracle system is a teensy bit more of a pain to set
> up>
>
> Note much of a pain at all. Oracle XE (free) is an RPM setup on Linux
> and is an installable executable on windows.
>
> OraTcl works just fine with Oracle XE.
>
> When the community adopts a common DB API. I will write wrappers to
> OraTcl to make it work.
>
> -Todd
That's good to know.
There are a couple of "flexibities" that sqlite has that other
db interfaces cannot share by virtue of the fact it lives in the
same memory as the interpreter. This is not insurmountable, but
I'm thinking operations like evaluating a result to a script would
require a bit of caching by the interpreter.
Perhaps one approach would be to cheat and use sqlite as a cache
for other database engines?
I also know that the MySql API seems to care if a statement will
return a value or not. Running an update as a query will lock up
the db connection handle, so they have seperate "query" and
"command" hooks. I'm digging through the C code now for MySqlTcl
to see if I can make heads or tails of why.
Nice part is that this is a standard for extensions. We can
pretty much say what we want it to be, because it doesn't require
mucking around with the Core.
Here's my API in a nutshell:
driverCommand object {connect info as a key/value list}
object query stmt - Evaluate a statement, and return the values
as one result per line
foo query "select name,rank,serial from pows"
{Percy Capt 234123} {Clancy PFC 141511}
object query_flat stmt - Evaluated a statement and return the
values as a flat list
foo query_flat "select name,rank,serial from pows"
Percy Capt 234123 Clancy PFC 141511
object cmnd stmt - Evaluate a statement that doesn't return a
value (though it would be nice if it returned
something, like rows matched and rows changed
doesn't now)
object describe table - Returns a dict describing a table:
foo describe weatherReports
{index dates {
type index columns {dateReported zipCode}
}
columns {
reportId {type int autoincrement 1}
dateReports {type datetime}
zipcode {type char length 10}
humidity {type int max 100 min 0}
barometer {type real}
temperature {type real}
clouds {type string length 255}
details {type text}
}
primary_key reportId
}
object create_table dict - Create a table based on the
tcl API standard template
object tables - Return a list of tables
One I'd like to add from Sqlite is:
object eval stmt {body} - Evaluate body for every result in stmt
with a variable for each column, filled with the value
for that row.
object roweval stmt body -
like eval, but put the values in a key/value list
named "row" instead of as local variables
object arrayeval stmt body -
like eval, but put the values in an array named "row"
instead of as local variables. List of columns is
stored in element (*) just like in sqlite's implementationn
Donal K. Fellows
> There are a couple of "flexibities" that sqlite has that other
> db interfaces cannot share by virtue of the fact it lives in the
> same memory as the interpreter. This is not insurmountable, but
> I'm thinking operations like evaluating a result to a script would
> require a bit of caching by the interpreter.
That's pretty easy to work around. Just keep a per-connection array
that maps from the SQL string to whatever kind of compiled form you
want. As long as people are using SQL in the form of some type of
prepared statement (which I'd say MUST be supported) then it's pretty
simple to do. That also gives you a spot to hang off any complex
information (like whether this statement returns a value or not, for
DBs that care). At the script level, the differences must be
concealed. The way in which the back-ends achieve this, well, that's a
great topic for ingenuity... :-)
So, choosing what actually should be in there as methods...
$conn close
$conn complete
$conn eval
$conn exists
$conn transaction
$conn version
All with the SQLite semantics (modulo how they treat column types, of
course). This is a subset of the SQLite set, and should be all the
bits that are needed for most DB work (plus a few things that make
life a little easier).
Comments?
Donal.
Sean Woods
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
Darren New
> 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.
1) Separating the list of keys from the list of values is ugly, IMO. Tcl
has dicts, keyed lists, and arrays, all of which have a natural
representation of interleaved keys and values. Most SQL statements don't
care about the order of keys and values as such.
2) I've made good use of writing textual SQL queries out to a file to
later be piped into a SQL engine. Of course, this isn't cross-engine
portable, but perhaps an option to see what's actually happening, or an
underlying driver that can hand back to string it sends to the database
or something for at least the CRUD stuff.
3) What, no events? I have to block my entire process if the database
join takes five seconds? This is actually paining me at this very moment
in my current project. Especially since the connector I'm using isn't
thread-safe either. This is the single biggest bugaboo I've had with
all the current interfaces.
4) Introspection might need to know whether transactions are possible,
or various kinds of locking, or whatever. This can vary table-by-table
as well. Then, of course, you need the begin_transaction,
commit_transaction, and rollback_transaction functions.
5) Not sure the difference between sqlprep and sqlfix. And I'm not sure
why such would be necessary at all, unless you mean they're internal
helper functions.
6) No connection function? Certainly something like
sqlconnect [list name val name val name val]
could be used, where the names and values are DB-specific?
7) Ought to have a way to inject raw SQL into the connection
where necessary, for taking advantage of the bits that *are*
db-specific.
8) I'd ditch the stmt_ off the front of insert, delete, etc.
The package name should serve to disambiguate. Also, for things
like "table_create", why not "create_table", which is in the
same order as the actual SQL statement and also reads more
like English?
9) And speaking of that, what happened to "drop_table", etc?
Why only creates and not drops or alters?
10) I don't see where you have an "as" capabilities. As in,
"select x.id as xid, y.id as yid from x,y where ..."
11) Not sure why the interface needs to distinguish commands
from queries at the script level.
12) What about big result sets where you want to iterate over
it without sucking the entire thing into memory at once?
13) Not sure why you have a way of getting the primary key but
not other unique indecies or foreign key constraints.
14) Nulls are problematic sometimes. A decent way of handling
them (when they are) is to return rows with different numbers
of entries. If the first row comes back {A 0 B 1 C 2} and the
second row is {A 7 C 9} you know that B was null in the second
row. For the cases where there's no valid Tcl value that isn't
a possible column value, this would let you check for nullness.
--
Darren New / San Diego, CA, USA (PST)
Remember the good old days, when we
used to complain about cryptography
being export-restricted?
Donal K. Fellows
> 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:
> 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
Hold it right there, buster! I see PHP-style magic quoting! No! It's so
thoroughly misunderstood that it can only be viewed as an invitation to
SQL-injection attacks. (See http://xkcd.com/327/ for why this matters.)
My proposal was simpler in many ways, in that it *requires* that people
support (at the user Tcl script level if nowhere else) SQL prepared
statements with embedded references to Tcl variables. Internally, if the
DB engine is so broken that it doesn't do prepared statements, then the
DBI has to do the magic quotes stuff. But it hides what it is doing from
the script level.
I want to make databases easy to use. If that means it's a bit harder to
write the interface, so be it.
Donal.
Sean Woods
> Some comments:
>
> 1) Separating the list of keys from the list of values is ugly, IMO. Tcl
> has dicts, keyed lists, and arrays, all of which have a natural
> representation of interleaved keys and values. Most SQL statements don't
> care about the order of keys and values as such.
>
My concern there is that update statements require an
appropriate "WHERE foo='bar'". And if you have a table
with a numerical primary key and/or several unique keys
you may want to reference a record one of several ways
And with dicts and arrays you still have a name for the
enclosing record.
dict set inMemDb key {valuelist}
array set key {valuelist}
For the system to pull the keys from an arbitrary list
of values invites it to be psychic. And I've had bad
experiences using a computer to fill in the gaps.
> 2) I've made good use of writing textual SQL queries out to a file to
> later be piped into a SQL engine. Of course, this isn't cross-engine
> portable, but perhaps an option to see what's actually happening, or an
> underlying driver that can hand back to string it sends to the database
> or something for at least the CRUD stuff.
>
Well the intent is that the stmt_* calls don't make the
query/update/etc directly. They return the text that you
feed back into query or cmnd. So you could write the
result out to a file. (Should make that clearer in the spec)
> 3) What, no events? I have to block my entire process if the database
> join takes five seconds? This is actually paining me at this very moment
> in my current project. Especially since the connector I'm using isn't
> thread-safe either. This is the single biggest bugaboo I've had with
> all the current interfaces.
>
Asynchronous queries and statements... Lets leave that for
rev 2.
> 4) Introspection might need to know whether transactions are possible,
> or various kinds of locking, or whatever. This can vary table-by-table
> as well. Then, of course, you need the begin_transaction,
> commit_transaction, and rollback_transaction functions.
>
Agreed. I've been cheating up until now and using the
native calls. To be really useful our dialect interpreter
should know how to do this.
> 5) Not sure the difference between sqlprep and sqlfix. And I'm not sure
> why such would be necessary at all, unless you mean they're internal
> helper functions.
>
Sqlfix only does escapes. Sqlprep is used to put quotes around
text, or not if it's a number.
> 6) No connection function? Certainly something like
> sqlconnect [list name val name val name val]
> could be used, where the names and values are DB-specific?
>
The idea is that the object constructor does this, which is
why you put the connection data into the object definition.
> 7) Ought to have a way to inject raw SQL into the connection
> where necessary, for taking advantage of the bits that *are*
> db-specific.
>
Um, that exactly what query and stmt do.
> 8) I'd ditch the stmt_ off the front of insert, delete, etc.
> The package name should serve to disambiguate. Also, for things
> like "table_create", why not "create_table", which is in the
> same order as the actual SQL statement and also reads more
> like English?
>
Actually, I'd say we need to split the connector from the
language wrapper.
> 9) And speaking of that, what happened to "drop_table", etc?
> Why only creates and not drops or alters?
>
I'm special. (Hadn't come up for my purposes)
> 10) I don't see where you have an "as" capabilities. As in,
> "select x.id as xid, y.id as yid from x,y where ..."
>
This is for simple queries. If you want to do magic, use
query directly.
> 11) Not sure why the interface needs to distinguish commands
> from queries at the script level.
>
Save that some of the db drivers require you do. Particularly
MysqlTcl
> 12) What about big result sets where you want to iterate over
> it without sucking the entire thing into memory at once?
>
Eval should only read in one line at a time from the
db. So implicit in each call is "limit 1,offset x"
> 13) Not sure why you have a way of getting the primary key but
> not other unique indecies or foreign key constraints.
>
In case I don't know ahead of time
> 14) Nulls are problematic sometimes. A decent way of handling
> them (when they are) is to return rows with different numbers
> of entries. If the first row comes back {A 0 B 1 C 2} and the
> second row is {A 7 C 9} you know that B was null in the second
> row. For the cases where there's no valid Tcl value that isn't
> a possible column value, this would let you check for nullness.
>
Intriguing. I'll try that.
Gerald W. Lester
I whole heartily agree with Donal's comments.
Sean Woods
> Donal K. Fellows wrote:
> > Sean Woods wrote:
> >> 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:
> > [...]
> >> 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
> > [...]
>
> > Hold it right there, buster! I see PHP-style magic quoting! No! It's so
> > thoroughly misunderstood that it can only be viewed as an invitation to
>
> > My proposal was simpler in many ways, in that it *requires* that people
> > support (at the user Tcl script level if nowhere else) SQL prepared
> > statements with embedded references to Tcl variables. Internally, if the
> > DB engine is so broken that it doesn't do prepared statements, then the
> > DBI has to do the magic quotes stuff. But it hides what it is doing from
> > the script level.
>
> > I want to make databases easy to use. If that means it's a bit harder to
> > write the interface, so be it.
>
> I whole heartily agree with Donal's comments.
>
> --
> +--------------------------------+---------------------------------------+
> | Gerald W. Lester |
> |"The man who fights for his ideals is the man who is alive." - Cervantes|
> +------------------------------------------------------------------------+
As do I. I'm suffering from some scope creep. Trying to
outsmart SQL is a larger task best left to the experts.
So if we do the stmt_* stuff, better to leave that as an
add on.
-The Hypnotoad
Darren New
> On Oct 23, 10:35 pm, Darren New <d...@san.rr.com> wrote:
>> Some comments:
>>
>> 1) Separating the list of keys from the list of values is ugly, IMO. Tcl
>> has dicts, keyed lists, and arrays, all of which have a natural
>> representation of interleaved keys and values. Most SQL statements don't
>> care about the order of keys and values as such.
>>
>
> My concern there is that update statements require an
> appropriate "WHERE foo='bar'". And if you have a table
> with a numerical primary key and/or several unique keys
> you may want to reference a record one of several ways
Yes?
The interface I use has this sort of thing:
set sql [build_insert $table [array get rowdata]]
set sql [build_update $table [array get newdata] [array get selections]]
(where all the arrays are indexed by column name with values being the
unquoted values you want to put in.)
Maybe it's just poor documentation on what you intended, but all I
intended is that the list of row names shouldn't be in a different
argument than the list of associated values. Maybe you meant something
different than "keylist" and "valuelist" tho.
> For the system to pull the keys from an arbitrary list
> of values invites it to be psychic. And I've had bad
> experiences using a computer to fill in the gaps.
It's not arbitrary. It's name/value pairs just like you get back from
$dict. That was my intention.
Of course, you're still going to have trouble with statements like
select max(logintime),user as name from users
where user in (...) group by user
I haven't found a good "portable" way of doing something like that.
> Well the intent is that the stmt_* calls don't make the
> query/update/etc directly. They return the text that you
> feed back into query or cmnd. So you could write the
> result out to a file. (Should make that clearer in the spec)
Oh, OK. I'm still not sure why you're separating query and command.
> Asynchronous queries and statements... Lets leave that for
> rev 2.
I disagree. One of my primary peeves with Unix is that a thread isn't a
special case of a process, and asynch I/O isn't a special case of synch
I/O. If you're not going to do events (or at least guarantee thread
safety), then it's not going to be especially useful for production
systems. You're back at mysql v1.2, guaranteeing transactional
consistency by only ever handling one connection at a time. :)
>> 4) Introspection might need to know whether transactions are possible,
>> or various kinds of locking, or whatever. This can vary table-by-table
>> as well. Then, of course, you need the begin_transaction,
>> commit_transaction, and rollback_transaction functions.
>>
>
> Agreed. I've been cheating up until now and using the
> native calls. To be really useful our dialect interpreter
> should know how to do this.
Well, yes. We should work out all the details, methinks.
>> 5) Not sure the difference between sqlprep and sqlfix. And I'm not sure
>> why such would be necessary at all, unless you mean they're internal
>> helper functions.
>>
>
> Sqlfix only does escapes. Sqlprep is used to put quotes around
> text, or not if it's a number.
How do you know? Is 92130 a number? How about 02312? No, they're both
zip codes. :-)
> The idea is that the object constructor does this, which is
> why you put the connection data into the object definition.
OK. I just didn't see it.
> Um, that exactly what query and stmt do.
Got it.
>> 11) Not sure why the interface needs to distinguish commands
>> from queries at the script level.
>>
> Save that some of the db drivers require you do. Particularly
> MysqlTcl
Uh, that's exactly the sort of thing that should be hidden. I use a
mysql Tcl interface that doesn't distinguish, and [numrows] gives you
back the rows selected, or the rows updated, depending on whether the
previous thing was a select or an update. The point of making a portable
interface is to *hide* the db driver details.
>> 12) What about big result sets where you want to iterate over
>> it without sucking the entire thing into memory at once?
>>
> Eval should only read in one line at a time from the
> db. So implicit in each call is "limit 1,offset x"
I'm not sure I follow. You're saying you never select more than one row
from the database at a time? Guh?
>> 13) Not sure why you have a way of getting the primary key but
>> not other unique indecies or foreign key constraints.
>>
> In case I don't know ahead of time
Um, sure. The question is not "why is the primary key there", but "why
are the other keys not there?"
Darren New
> I want to make databases easy to use. If that means it's a bit harder to
> write the interface, so be it.
It would be nice, but how do you handle code like
if ("" != $_GET['name'])
$sql .= "WHERE name like '" . esc($_GET['name']) . "%' AND ";
(Yes, it's PHP. Deal. :-)
I often do this sort of thing with a dozen different form fields,
building up a query clause by clause. If I had only prepared statements,
would I need 2^N statements?
tom.rmadilo
> Donal K. Fellows wrote:
> > I want to make databases easy to use. If that means it's a bit harder to
> > write the interface, so be it.
>
> It would be nice, but how do you handle code like
> if ("" != $_GET['name'])
> $sql .= "WHERE name like '" . esc($_GET['name']) . "%' AND ";
> (Yes, it's PHP. Deal. :-)
>
> I often do this sort of thing with a dozen different form fields,
> building up a query clause by clause. If I had only prepared statements,
> would I need 2^N statements?
I've written several different database abstraction layers. One is
more stored-procedure-like. The other would make happy Donal in shear
simplicity. He would never have to handle 'code' in PHP or any
language. Just submit a form with properly named fields which you have
permission to use, and the backend does the handling for you.
But the stored procedure like code is also simple for the developer.
Here is an example page:
::twt::form::getQuery {
material
unit_of_measure
units
cost
}
set queryName "remodel::InsertIntoMaterials"
::twt::db::initQuery $queryName
::twt::db::query::setParam $queryName material $material
::twt::db::query::setParam $queryName unit_of_measure $unit_of_measure
::twt::db::query::setParam $queryName units $units
::twt::db::query::setParam $queryName cost $cost
::twt::db::execute $queryName
ns_returnredirect materials.tcl
-------------
Note that there are no references to any database, no SQL 'code', no
database handle. All of this is setup for the developer (by another
developer). If the database were to change, even from SQL to csv, the
above code would not change.
Darren New
> Note that there are no references to any database, no SQL 'code', no
> database handle.
Right. Until you need to use "like" for some parameters and "=" for some
and "<" for some. And until some of your queries go to the read-only
replica while changes go to the read-write master.
Sure, the easy stuff is easy. It's the hard stuff that's hard to get right.
tom.rmadilo
> tom.rmadilo wrote:
> > Note that there are no references to any database, no SQL 'code', no
> > database handle.
>
> Right. Until you need to use "like" for some parameters and "=" for some
> and "<" for some. And until some of your queries go to the read-only
> replica while changes go to the read-write master.
>
> Sure, the easy stuff is easy. It's the hard stuff that's hard to get right.
Like I said, this system is the prepared statement system, very good
for select queries which have lots of variability. What isn't shown on
in the example is that each statement has an associated 'default'
datasource, and each datasource has a default provider. These could be
changed on the fly if necessary, so you could in fact, prepare the
statement once and execute it on several different systems, or like I
said, even change the datasource from an SQL type to a CSV type.
There are more examples here:
http://rmadilo.com/m2/servers/rmadilo/pages/remodel/
But the SQL statements are here:
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/remodel/queries/
The datasource, provider links are set here:
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/remodel/tcl/remodel-init.tcl
A provider has a standard interface (What this discussion was about)
which is here:
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/provider/tcl/provider-procs.tcl
Although there are defaults for my preferred type, sql, anything could
replace the standard callbacks.
Examples of different providers (tcl/*-procs.tcl) and use of interface
tcl/*-init.tcl) are here:
ODBC:
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/odbc/
PostgreSQL (driver version 4):
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/pg4/
The datasource itself is a shared resource. What that means is that to
use the datasource, you grab a handle. This is usually automatic
unless you want to do a transaction.
The system is really nice. If you start a transaction and somewhere
along the line an error results, you get automatic rollback. The
transaction callbacks are usually vastly different for different
providers, as is the nature of the handle. For instance if you simply
write to a file, you need a file lock. Obviously this has nothing to
do with database handles, but it is still abstracted out by this
system into a datasource. Both database handles and file locks use the
same system for getting the shared resource.
The example on my system is to use files to store session, form and
application data. The datasource-procs are here:
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/db/tcl/
And an example filesystem use (Datastore):
http://rmadilo.com/m2/servers/rmadilo/modules/tcl/twt/packages/datastore/
In case anyone is wondering why go to all this trouble. Just think of
the issue of security. Dangerous access to the database is restricted
because the sql files are separate from the use of the queries. Even
the developer can't smuggle bad sql into the system. In a similar way,
the data gathering pages are separate from the template pages. The
examples in remodel contain no HTML markup on the tcl pages, but use a
safe templating system (.tmpl files). So Model: in .query files, View:
in .tmpl files and Controller: in .tcl files.
Oh, wait, I didn't know that you could do design patterns in Tcl.
tom.rmadilo
> Sure, the easy stuff is easy. It's the hard stuff that's hard to get right.
Yes, unfortunately a lot of systems make the easy stuff hard and don't
even get that right.
My view is that a lot of OO languages try to 'model' the database and
SQL queries in their own terms and not as a result set (relations).
Everything I have seen built on top of JDBC seems to go this route.
While JDBC is a great system, adding layers of stuff on top of it
doesn't help much.
I divide queries into two classes: modification queries and selection
queries. The power of a database is in selection and is pointless to
model in either OO or procedural languages. I do all selection queries
as SQL, hand written. But insert/update/delete are so easy to
construct if you have the data model and a set of rules you wish to
apply that I use a system that handles all of these details for me
(once I establish the model and rules). But I don't pretend that some
things remain beyond automation. But saving so much time on the simple
stuff, and doing it correctly, I have lots of time to work on the
exceptions.
Donal K. Fellows
> It would be nice, but how do you handle code like
> if ("" != $_GET['name'])
> $sql .= "WHERE name like '" . esc($_GET['name']) . "%' AND ";
> (Yes, it's PHP. Deal. :-)
>
> I often do this sort of thing with a dozen different form fields,
> building up a query clause by clause. If I had only prepared statements,
> would I need 2^N statements?
No, since the interface layer should be hiding all that stuff from
you, and you'd just be writing:
if {$GET(name) ne ""} {
# I can't remember right now if SQLite handles array vars
set paramName $GET(name)
append sql {WHERE name LIKE '$paramName%' AND }
}
Yes, this is the SQLite approach. It gets it *right*.
Donal.
Tom Poindexter
I just hope that implementors of such an interface will first
brush up on SQL Injection attacks, as the above code
demostrates.
--
Tom Poindexter
tpoi...@nyx.net
tom.rmadilo
wrote:
Donal,
Notice the example I gave above. Most of what you do in your example
is also handled completely behind the scenes. In other words, you
define parameters ahead of time, including whether it is required, a
default to use, the type, and for strings a length (because database
chars usually have max lengths). So, on a separate page you define
what a query is and the associated params. Then you can reuse the
query anywhere. Here is the setup page (with still a lot of the magic
hidden behind):
set queryName remodel::InsertIntoMaterials
::twt::db::query::new ${queryName} {
insert into
materials
(material, unit_of_measure, units, cost)
values
($material, $unit_of_measure, $units, $cost)
}
::twt::db::query::setDataSource $queryName pg4.remodel
::twt::db::query::addParam $queryName material varchar "" false 64
::twt::db::query::addParam $queryName unit_of_measure varchar "" false
32
::twt::db::query::addParam $queryName units numeric "" false
::twt::db::query::addParam $queryName cost numeric "" false
When the 'execute' function is run from the previous example, the
passed in params are validated. Depending upon type, they are properly
quoted. This method makes it easy to use what looks line regular Tcl
syntax in defining the query.
However, I will admit my bias against building up complex SQL select
queries in a programmatic style. My opinion is that queries are
valuable documentation. Code which slowly builds up to a huge SQL
query obfuscates the intent of the query. This is not the element of
reuse in SQL. If we did this in Tcl, it would be like building up a
procedure body chunk-by-chunk just to execute it once. Everyone would
know to laugh at such a method of creating and executing code, but it
is okay, for some reason, in SQL.
SQL is not a procedural language, it isn't an OO language. For queries
which return results, it helps to provide the full query in one place.
Otherwise, since the handling of special cases will eventually devolve
into ad-hoc work-arounds, debugging a problem in the future (and there
will be problems), becomes a nightmare. You simply cannot recreate SQL
select queries in any simpler terms.
This is not true for simple, one row, insert/update/delete queries. My
example code just above would be completely unnecessary, as would the
page which inserted the params and ran the execute command, in another
system which I developed. In this other system, the only thing
required, after a mostly declarative setup would be to post an HTML
form to a processing page, for instance you would write, not a bunch
of Tcl code, but one form to do what you guys are after:
<form method="POST" action="/qw/qw.tcl">
Material:<input type="text" name="new.materials.material.0"
value=""><br>
Unit-Of-Measure:<input type="text" name="new.materials.uom.0"
value=""><br>
Units:<input type="text" name="new.materials.unit.0" value=""><br>
Cost:<input type="text" name="new.materials.cost.0" value=""><br>
<input type="submit" value="Insert Material">
</form>
The point of a system like this is to centralize the decision of what
to do with a particular parameter. Since it is part of a database
table, the requirements are always the same no matter how many times
you manipulate the parameter. For instance, if you need to update the
just inserted material, assuming the material_id is 1234:
<input type="text" name="set.materials.material.1234" value="My
Material">
Or delete some rows:
Delete 'My Material': <input type="checkbox" name="del.materials.1234"
value="1"><br>
Delete 'My Other Material': <input type="checkbox" name="del.materials.
5678" value="1">
So, in addition to handling all the boring details, you can do all of
these operations at the same time, in combination, from one form.
tom.rmadilo
> In article <1193568971.539347.229...@y42g2000hsy.googlegroups.com>,
> Donal K. Fellows <donal.k.fell...@man.ac.uk> wrote:
>
>
>
> >Darren New wrote:
> >> It would be nice, but how do you handle code like
> >> if ("" != $_GET['name'])
> >> $sql .= "WHERE name like '" . esc($_GET['name']) . "%' AND ";
> >> (Yes, it's PHP. Deal. :-)
>
> >> I often do this sort of thing with a dozen different form fields,
> >> building up a query clause by clause. If I had only prepared statements,
> >> would I need 2^N statements?
>
> >No, since the interface layer should be hiding all that stuff from
> >you, and you'd just be writing:
>
> > if {$GET(name) ne ""} {
> > # I can't remember right now if SQLite handles array vars
> > set paramName $GET(name)
> > append sql {WHERE name LIKE '$paramName%' AND }
> > }
>
> >Yes, this is the SQLite approach. It gets it *right*.
>
> I just hope that implementors of such an interface will first
> brush up on SQL Injection attacks, as the above code
> demostrates.
Good point, I didn't notice the lack of quoting. I'm not sure how this
is an example of an interface, maybe there is some magic going on
behind the scenes which isn't shown.
Regardless of any magic, parameter checking shouldn't be handled in
every page a parameter is used. It works great for a one programmer
system, but as soon as someone new shows up and starts to add new
queries, they have to figure out how to validate and quote parameters
by looking at example code. Maybe they will do it, maybe they will
think they are doing it correctly, who knows. But...what happens when
you discover two months into a project that a title field needs to be
512 chars long, not 128? Now you need to hunt down and change every
use of that field and update the validation (and you are doing
validation, including length checking, prior to insert, right?) Well,
I wouldn't be surprised if someone skipped it, for now, until time is
available, and that is the exact point. Time isn't available, so why
waste it doing the same thing over and over again, when you could
specify once, in one place, the parameter names, types, default
values, etc.?
Darren New
I would think that's the point, yes? Note that the WHERE clause is in
curly braces, so $paramName is substituted presumedly by whatever
function accepts $sql as its input (the "execute this query" thing).
OK, I wasn't sure that "prepared statements" could be "prepared"
efficiently enough to make that approach work. I only ever met them in
the context of compiled languages where the compiler translates the
"preparation" into actual code generation at compile time.
Darren New
> they have to figure out how to validate and quote parameters
> by looking at example code.
To the extent that the parameter quoting is defined by SQL, you can just
look in the schema. Of course if you need to validate a particular type,
you have a separate function for validating that type.
> validation, including length checking, prior to insert, right?)
Why would I validate length checking prior to insert if it isn't
relevant? Sure, if you pass me too long of a title and I want to make
that an error return instead of truncating it, then I check before the
insert. But that's orthogonal to what's in the database.
I'm not even sure how you got here from there, really. Nobody was
talking about validating values before this.
> waste it doing the same thing over and over again, when you could
> specify once, in one place, the parameter names, types, default
> values, etc.?
And how do you do that? Since the SQL Schema says what the data type is,
but doesn't allow for things like "this string must all be upper case",
how exactly do you do this Once And Only Once?
Darren New
> The point of a system like this is to centralize the decision of what
> to do with a particular parameter. Since it is part of a database
> table, the requirements are always the same no matter how many times
> you manipulate the parameter.
Uh, no. That's BS. *Especially* for queries.
> For instance, if you need to update the
> just inserted material, assuming the material_id is 1234:
>
> <input type="text" name="set.materials.material.1234" value="My
> Material">
And what if you want to update the material_id to the same material_id
used by the most recent project by the same customer that also used
material_id 4567?
> Or delete some rows:
You mean, like, delete rows of samples where we already have enough rows
with samples covering the same label, where the label is marked with how
many samples the label needs? I.e., we have 23 photos of red things,
and "red" things only need 20 sample photos, and another photo shows up,
and so we store it, and a bit later we find out (say, from a human) that
the photo has red in it, but we already have enough red samples? What's
the argument syntax for that?
> So, in addition to handling all the boring details, you can do all of
> these operations at the same time, in combination, from one form.
Making increasingly-less-trivial operations trivial is a good thing.
It's not possible to make all or even most "interesting" operations
trivial, methinks.
tom.rmadilo
> tom.rmadilo wrote:
> > they have to figure out how to validate and quote parameters
> > by looking at example code.
>
> To the extent that the parameter quoting is defined by SQL, you can just
> look in the schema. Of course if you need to validate a particular type,
> you have a separate function for validating that type.
>
> > validation, including length checking, prior to insert, right?)
>
> Why would I validate length checking prior to insert if it isn't
> relevant? Sure, if you pass me too long of a title and I want to make
> that an error return instead of truncating it, then I check before the
> insert. But that's orthogonal to what's in the database.
>
> I'm not even sure how you got here from there, really. Nobody was
> talking about validating values before this.
>
> > waste it doing the same thing over and over again, when you could
> > specify once, in one place, the parameter names, types, default
> > values, etc.?
>
> And how do you do that? Since the SQL Schema says what the data type is,
> but doesn't allow for things like "this string must all be upper case",
> how exactly do you do this Once And Only Once?
Maybe I should preface the answer with an assumption: every parameter
can be validated, that is, you can test if the value is valid or not
(excluding uniqueness constraints or references constraints). Also,
there is an assumption that every parameter can be transformed into
the desired form 'upper(input)'. So, this means that if you at some
future date search the database and find a parameter which is supposed
to be all upper case, and isn't, you know this is a mistake, even if
the database likes it. I'm not talking here about the database
validation.
So, you just apply the transform for the attribute whenever you insert/
update the attribute.
The simple version I outlined above doesn't include everything easily
possible. You can have multi-attribute validation, like date_start <=
date_end. You can also combine sub-attributes into one:
new.note.date.day.0, new.note.date.month.0, new.note.date.year.0, can
be combined once validated as integers, then revalidated as a date.
I am not saying you don't have to write the script to do the
combining, but that is it, you write it (once) and it gets called
after the data is available. The full package already contains this
code for dates, and the basic type checking and length checking is a
little easier to setup. Anyway, here is the callback for what you were
asking about (called after basic validation) (for both insert and
update):
{set my_attr [string toupper $my_attr]}
You might also be able to do it as a filter, not sure at the moment.
More info is here:
and callbacks here:
http://rmadilo.com/files/query-writer/source/query-writer/tcl/query-writer-procs.tcl
Just search for callbacks. The above callback is executed: [uplevel
$callback] in the context of all the attrs.
tom.rmadilo
> tom.rmadilo wrote:
> > The point of a system like this is to centralize the decision of what
> > to do with a particular parameter. Since it is part of a database
> > table, the requirements are always the same no matter how many times
> > you manipulate the parameter.
>
> Uh, no. That's BS. *Especially* for queries.
>
When you say queries, do you mean select queries? The statement above
relates only to insert/update/delete. And the 'no need to program'
syntax using a form and posting only applies to insert/update/delete
of a single row (no where clause beyond the key). If you skip my very
specific limitations, you can easily argue against the idea, and I
would join in 100%. I hand write all select queries, no exceptions. I
think I said somewhere above that I consider SQL queries to be the
best possible documentation. And they are the most compact code form.
I also would never, ever-ever-ever try to replace special cases with a
special layer which tries to turn these into procedural code. Very
ugly, impossible to learn, hard to maintain, big, dumb idea. But
programmers who hate data modeling and SQL always try. I'm not
interested in replicating the SQL layer in Tcl.
> > For instance, if you need to update the
>
> > just inserted material, assuming the material_id is 1234:
>
> > <input type="text" name="set.materials.material.1234" value="My
> > Material">
>
> And what if you want to update the material_id to the same material_id
> used by the most recent project by the same customer that also used
> material_id 4567?
>
Other than why you would do that, you must be saying that something
would automatically choose this information, or is someone going to
choose it from a list? If it is automatic, done behind the scenes, you
don't need to post a form or validate. Instead you use the direct API:
set matArray(material_id) 9876
qw_set 1234 materials matArray
The syntax of the names of the form vars should make it very obvious
that it only applies to single rows, no where clause beyond the key.
> > Or delete some rows:
>
> You mean, like, delete rows of samples where we already have enough rows
> with samples covering the same label, where the label is marked with how
> many samples the label needs? I.e., we have 23 photos of red things,
> and "red" things only need 20 sample photos, and another photo shows up,
> and so we store it, and a bit later we find out (say, from a human) that
> the photo has red in it, but we already have enough red samples? What's
> the argument syntax for that?
>
There are two issues here. One is storage. Do you put the data into
the database? Then select it out, do something with it, then maybe
delete it? Not sure what is so special about this, sounds like a a
human is doing the validation, and then they use a function of some
sort to delete it, if you want to allow GET as well as post
<a href="/qw/qw.tcl?del.photo.5432=1">Delete My Red Photo</a>
No GET, maybe select the ones you don't like from a list:
<form ...>
<li>My Red Photo1 <input name="del.photo.2345" type="checkbox"
value="1"></li>
<li>My Red Photo2 <input name="del.photo.2346" type="checkbox"
value="1"></li>
<li>My Red Photo3 <input name="del.photo.2347" type="checkbox"
value="1"></li>
<br><input type="submit" value="Delete Checked Photos" >
</form>
> > So, in addition to handling all the boring details, you can do all of
> > these operations at the same time, in combination, from one form.
>
> Making increasingly-less-trivial operations trivial is a good thing.
> It's not possible to make all or even most "interesting" operations
> trivial, methinks.
Anything beyond a simple 'where id = $id' is very far from trivial, it
quickly gets way to interesting to trivialize.
Darren New
> Maybe I should preface the answer with an assumption: every parameter
> can be validated, that is, you can test if the value is valid or not
Well, you can validate that it's syntactically correct. You can't really
validate if it's semantically correct or that it corresponds to the real
world. E.g., you can't really validate that the phone number typed is
the phone number that belongs to the person entering the phone number,
or that the date entered is later than the previous date plus the
previous duration.
> (excluding uniqueness constraints or references constraints).
How about constraints like the number of days the employee gets paid for
can't be 7 if those days fall in a range in the "vacation taken" table?
I mean, heck, the difficult validations I run across are the ones that
even SQL isn't really up to: Each hardware port that's active can only
be associated with one plugged-in device. If the device is "inactive",
it can still be associated with whatever port it used to be plugged
into. Haven't figured out how to enforce that with uniqueness in SQL.
> So, you just apply the transform for the attribute whenever you insert/
> update the attribute.
Sure. That doesn't mean the data's right. I'm sure you handle much of
the easy stuff. It's the hard stuff that's hard to get right.
What I was saying is that the SQL has some rules (like referential
integrity) that your code probably can't enforce, and if it could, it
would be pulling it out of the database. But then your code is closely
tied to your database schema, such that renaming the column probably
propagates all through the code.
> The simple version I outlined above doesn't include everything easily
> possible. You can have multi-attribute validation, like date_start <=
> date_end.
But it wouldn't seem to support "start date must be after latest
completion date from same device", right?
Darren New
> On Oct 28, 2:20 pm, Darren New <d...@san.rr.com> wrote:
>> tom.rmadilo wrote:
>>> The point of a system like this is to centralize the decision of what
>>> to do with a particular parameter. Since it is part of a database
>>> table, the requirements are always the same no matter how many times
>>> you manipulate the parameter.
>> Uh, no. That's BS. *Especially* for queries.
>>
>
> When you say queries, do you mean select queries?
Well, sure. CRUD is pretty easy to get right.
> The statement above relates only to insert/update/delete.
More specifically, insert/update/delete of columns without complex
selection info.
> And the 'no need to program'
> syntax using a form and posting only applies to insert/update/delete
> of a single row (no where clause beyond the key).
Oh, OK. I think we've all automated *that* level. :-)
> If you skip my very
> specific limitations, you can easily argue against the idea, and I
> would join in 100%. I hand write all select queries, no exceptions.
That seems counterproductive, too. I don't hand-write the select queries
that are only selecting one unique value based on
"WHERE X='...' AND Y='...'" any more than I do for deletes.
> I also would never, ever-ever-ever try to replace special cases with a
> special layer which tries to turn these into procedural code.
OK. I'd misunderstood your goals.
> There are two issues here. One is storage. Do you put the data into
> the database? Then select it out, do something with it, then maybe
> delete it? Not sure what is so special about this, sounds like a a
> human is doing the validation, and then they use a function of some
> sort to delete it, if you want to allow GET as well as post
> <a href="/qw/qw.tcl?del.photo.5432=1">Delete My Red Photo</a>
No. Something (automated in the real case, "human" in my example because
I'm not allowed to tell you about the real case) decides the photo is
red. Something else decides we already have enough red photos, and
deletes it to save space.
> Anything beyond a simple 'where id = $id' is very far from trivial, it
> quickly gets way to interesting to trivialize.
Yah. I'm just tired of frameworks that try to make it trivial but just
wind up making it difficult. :-)
Donal K. Fellows
> I would think that's the point, yes? Note that the WHERE clause is in
> curly braces, so $paramName is substituted presumedly by whatever
> function accepts $sql as its input (the "execute this query" thing).
Yes. The point is that the "execute this query" part should do the
application of all magical quoting stuff in a way that is correct for
the database in question (AIUI, it varies between them) leaving Tcl
scripts to focus on the more interesting bits. And managing a cache of
compiled queries behind the scenes isn't that difficult.
> OK, I wasn't sure that "prepared statements" could be "prepared"
> efficiently enough to make that approach work. I only ever met them in
> the context of compiled languages where the compiler translates the
> "preparation" into actual code generation at compile time.
That's exactly what SQLite is doing, and it seems very fast to me.
Intelligent behind-the-scenes caching should take care of any slowness
that other databases would otherwise impose.
Donal.
Donal K. Fellows
> Notice the example I gave above. Most of what you do in your example
> is also handled completely behind the scenes. In other words, you
> define parameters ahead of time, including whether it is required, a
> default to use, the type, and for strings a length (because database
> chars usually have max lengths). So, on a separate page you define
> what a query is and the associated params. Then you can reuse the
> query anywhere. Here is the setup page (with still a lot of the magic
> hidden behind):
I don't think what you wrote is incompatible with what I wrote. Just so
wildly different as to be irrelevant. :-)
I'm talking about a framework that lets you deal with making SQL queries
(including inserts, updates, deletes, table definitions, etc.) to a
database without having to deal with the low-level nitty-gritty business
of quoting, results management, etc. I'm not talking about connecting
that basic framework to the Web or stuff like that[*]; it was just an
illustrative example to emphasize why leaving the quoting to the
interface layer is better. (Programmers are lazy, and so forget to do
things like quoting. Better to let the mandatory parts take up the slack
so you can slack off! :-)) My explicit goal is to make it so that users
of the database interface get it right because that is the path of least
resistance. Having SQL be just another embedded language is another
explicit goal (just as C is with critcl). But defining what others do
with the DBI is a Non-Goal.
Donal.
[* Ye gods! No! ]
Larry W. Virden
> My view is that a lot of OO languages try to 'model' the database and
> SQL queries in their own terms and not as a result set (relations).
I think this is a major weakness in many areas of languages- trying to
model the foreign interfaces rather than creating an interface that
reflects the nature of the host language. It is certainly a faster way
to make the functionality available - but in my experience, results in
a programming environment which is more difficult to use.
Look at the Tcl/Tk relationship, comparing it against the way other
languages implement packages for X. So many packages just create a
gluing between the X windowing APIs of a widget set. Same thing
happens for many database or other kinds of libraries.
tun...@yahoo.com
<donal.k.fell...@manchester.ac.uk> wrote:
> tom.rmadilo wrote:
I have followed this thread with a slight interest. As with the
original poster, I find the lack of an ODBC driver on non-Windows
platforms a bit of a concern. The thread, however, seems to have
evolved into something else: an SQL layer over just such an ODBC
driver.
Am I missing something? Why not work on a driver first and the
simplification layer later? While we are at that, what is wrong with
the ODBC interface? (It is not OO is not a valid argument.) ODBC has
worked for ages and is universally available. Further, any attempt to
hide/objectify/replicate the full SQL standards seems besides the
point; especially without also providing the capacity a driver
provides to connect to databases.
I would focus on availability and stability of the basic ODBC driver
first.
PS. Yes, PostgreSQL and SQLite may have native Tcl libraries but they
also have ODBC drivers as well which are used far more often.
tom.rmadilo
> I would focus on availability and stability of the basic ODBC driver
> first.
>
> PS. Yes, PostgreSQL and SQLite may have native Tcl libraries but they
> also have ODBC drivers as well which are used far more often.
Good point, I have to profess my ignorance on the issue. Is it really
true that nobody has connected the unixodbc driver to plain old Tcl,
or is it just not maintained, or what is the issue?
I use the same user level set of commands for every database I access,
that was the point I was trying to make originally. If the database
changes for any reason, like the driver becomes unavailable, or a new
customer wants to use similar code, no need to change the application
at the Tcl level. That is also why people use stored procedures: you
just interface with name and parameters, not DB specific syntax. But
to do any of this, you need a layer between the driver code and Tcl.
No way around that requirement.
tun...@yahoo.com
> Good point, I have to profess my ignorance on the issue. Is it really
> true that nobody has connected the unixodbc driver to plain old Tcl,
> or is it just not maintained, or what is the issue?
A few years ago, it was possible to compile it on Linux after heavy
tweaking. With the recent releases (whether it is Ubuntu or others),
it is now almost impossible (e.g., calls to functions and references
to libraries that are no longer available, etc.). A couple of years
ago, I gave up on it altogether. I have not tried snodbc on Linux but
I have seen a few posts here for similar challenges. It works fine on
Windows though.
> I use the same user level set of commands for every database I access,
> that was the point I was trying to make originally. If the database
Yes, I use my own wrappers around the ODBC calls too and I suspect
most people develop something similar that works best for them. While
there is value in standardizing these efforts, I think the underlying
challenge of a stable driver is more important. Otherwise, the whole
wrapper issue becomes mute.
tom.rmadilo
Okay, but if there is no C level shared library for ODBC on unix/
linux, the Tcl community is not the place to go looking for an answer.
Usually a library file is connected up to Tcl at the C level, which
isn't necessarily hard, just tedious. But if the problem is no library
to hook up to, might as well forget it. How do other scripting
languages do the connection on unix/linux?
Alexandre Ferrieux
>
> [...]
> Usually a library file is connected up to Tcl at the C level, which
> isn't necessarily hard, just tedious. But if the problem is no library
> to hook up to, might as well forget it.
Not necessarily. If some useful piece of software is made available as
just an external stdin/stdout executable (a "shell"), *and* I/O
overheads may be reasonably factored out, then [open "|foo 2>@ stderr"
r+] is a rather elegant binding.
I'm not saying one should struggle to find a unixODBC shell; just
wanted to highlight the fact that having a library was not the alpha
and omega to Tcl's glueing abilities.
-Alex
Larry W. Virden
> But if the problem is no library
> to hook up to, might as well forget it. How do other scripting
> languages do the connection on unix/linux?
I can't speak for how other languages do it. However, if there is no
library, then someone with enough motivation to want one could always
create one. I mean, there wasn't a library like Tk before Dr. O and
his students wrote it...
Kevin Kenny
> I have followed this thread with a slight interest. As with the
> original poster, I find the lack of an ODBC driver on non-Windows
> platforms a bit of a concern. The thread, however, seems to have
> evolved into something else: an SQL layer over just such an ODBC
> driver.
>
> Am I missing something? Why not work on a driver first and the
> simplification layer later? While we are at that, what is wrong with
> the ODBC interface? (It is not OO is not a valid argument.) ODBC has
> worked for ages and is universally available. Further, any attempt to
> hide/objectify/replicate the full SQL standards seems besides the
> point; especially without also providing the capacity a driver
> provides to connect to databases.
I don't quite understand what you're asking for here.
If you're asserting that there is no ODBC on non-Windows platforms,
unixODBC (http://www.unixodbc.org/) appears to be both popular and
well-maintained. There was a release shipped just a couple of weeks
ago. I can't comment on how well recent versions work. I haven't
tried it in several years.
If you're asserting that Tcl entirely lacks an interface to ODBC
on non-Windows platforms, I know that tclodbc has been built on
Unix hosts. Again, I haven't tried it there in quite some time.
(I tend to regard it as a Windows library.) When I have used it,
I've found Roy Nurmi, its manager, to be quite responsive to
bug reports. It's mature enough on Windows that I haven't needed
to report any bugs in several years. For what I use it for,
it just works. If the Unix implementation is less stable,
then you've a legitimate concern -- but please take it up in a
separate thread, because it's a bit off-topic for this one.
I owe you an explanation of why I haven't tried ODBC on Unix
for quite some time. I have always found ODBC, even on its
native Windows, to be slow, and often have stability issues
with it as well. I use it to connect to Jet databases and
SQLServer, for which no really good alternatives are available,
but I don't use them much. My two workhorse databases are
Oracle (for when only Big Iron will do) - which has very
well supported native drivers - and SQLite. I use the latter
because of its "zero configuration" properties. It runs
in-process without a separate server, manages its databases
simply as files in the file system, and is simply "drop in
and go." Having to deal with configuring ODBC data sources,
installing *three* separate ODBC layers on Unix (ODBC
itself, an ODBC driver for the database, and a Tcl-ODBC layer)
would totally destroy that advantage. ODBC therefore
loses out on grounds of both convenience and performance.
Given such constraints, we will be living with multiple database
connectivity API's for some time to come. Our colleagues
in the Java community have faced the same problem, and arrived
at the conclusion that they needed an overarching interface --
JDBC -- that subsumes multiple database APIs; hence, JDBC
can connect to ODBC, to Oracle's native API, to Postgres95's
native API, and so on. It winds up being incumbent upon the
database vendor, or an aftermarket developer, to supply the
bridge that adapts JDBC to whatever API the underlying
database presents.
Most of the discussion in this thread proposes the same for
Tcl: a specification (and possible interface glue) so that
all the databases present a common-denominator API. That
would give you what you ask for: a uniform way to move your
Tcl code from one database to another.
I will admit that the discussions in this thread have been
straying somewhat afield from that goal. I'd ascribe that
to posters floating "blue sky" design ideas - attempting
to improve on interfaces like ODBC and JDBC by making them
"more object-oriented" or whatever. Many of the people who
are likely to do the actual work, however, are reading these
discussions with some interest, mentally filing the wilder
ideas under "interesting idea, might be nice to have in
a later release," and thinking more about the job at hand.
Those individuals also don't post very much - they're
too busy working on the problem.
Bear in mind, though, that we do need this discussion. We
do have to get the interface right. I already see a number
of key ideas in this thread:
- resistance to SQL insertion attacks has to be designed
in and can be helped by integrating the SQL query
tightly to the Tcl layer. SQLite does quite a good
job of this. TclODBC has a more awkward interface,
but at least gets the job done with prepared statements.
Mysqltcl falls out of bed; the idea of providing
a separate "magic quoting" operation to scrub
text for insertion into a query is a non-starter.
- proper handling of NULLs is essential. This idea has
a natural tension against Tcl; in Tcl, "everything is
a string," but the NULL value is not any string.
This has been a problem in the past; TIP 185 proposes
one solution (ill-advised in my personal opinion).
I'm convinced that using dictionaries to represent
rows in result sets is a far better approach. I'll
post more on this in a separate discussion, probably
in the next few days.
- The interface must support result sets that are too
large to fit in memory. Some of us do slog through
multiple gigabytes of data on the client side. Sometimes
there's just no alternative.
- It is highly desirable for result sets to support
external iterators ("cursors") as well as internal
iterators (looping constructs). Once again, some
of us maintain large federated databases, and sometimes
there's no alternative to doing a merge-join on the
client side that involves two or more multi-gigabyte
result sets. Life is hard.
As far as I can tell, none of the existing Tcl database API's
gets all of these completely right. But (unlike object
orientation or XML-ification or Rails-style web interface
generation), these cannot be worked around by adding another
layer on top of the database interfaces. In my view, these
points, and likely a handful of others, are fundamental
to any database interface.
Expect more on this in the coming weeks. I'm rather under
a lot of time pressure lately, so my replies are likely
to have sporadic and unpredicable delays, but I am continuing
to examine these issues.
--
73 de ke9tv/2, Kevin
tom.rmadilo
> On Oct 29, 11:17 am, "tom.rmadilo" <tom.rmad...@gmail.com> wrote:
> > How do other scripting
> > languages do the connection on unix/linux?
>
> I can't speak for how other languages do it.
They you aren't much helping here, what is your point?
> However, if there is no
> library, then someone with enough motivation to want one could always
> create one. I mean, there wasn't a library like Tk before Dr. O and
> his students wrote it...
Umm, there is a C library unixodbc, and another iodbc, plus commercial
ones. As pointed out above, maybe they are no longer maintained. So
the lack of motivation already exists for the library code. If you
want to direct your motivation at the Tcl level, it is totally
pointless without the C library. The lack of motivation at the C
library level is causing understandable concern for the library
users.
And my point was that if someone is motivated, maybe look around and
see how others have done it, but it isn't going to start at the Tcl
level, only an idiot would write a odbc library just for Tcl on non-
windows.
Donal K. Fellows
> As far as I can tell, none of the existing Tcl database API's
> gets all of these completely right. But (unlike object
> orientation or XML-ification or Rails-style web interface
> generation), these cannot be worked around by adding another
> layer on top of the database interfaces. In my view, these
> points, and likely a handful of others, are fundamental
> to any database interface.
Let me state that I thoroughly agree with Kevin here. We are on, if not
the same wavelength, ones that are at least nicely harmonious. :-)
Donal.
Tom Poindexter
Kevin Kenny <ken...@acm.org> wrote:
>
>I owe you an explanation of why I haven't tried ODBC on Unix
>for quite some time. I have always found ODBC, even on its
>native Windows, to be slow, and often have stability issues
>with it as well. I use it to connect to Jet databases and
I've had fairly good luck using ODBC with Tcl on Unix and
Windows, but can't say that for what I was doing that I
noticed any kind of slowness or instability.
A benchmark (ducking for cover [*]) would be better to evaluate
any performance penalty in using ODBC. Ideally, it would test
the three Tcl ODBC drivers with the two open source ODBC
driver managers (UnixODBC and iODBC), Windows and MacOSX ODBC,
against database specific Tcl drivers (i.e., SQLite, Oratcl,
PgTcl, Mysqltcl, etc.)
[*] database benchmarking generally accepted as more of a
black art/Heisenberg science than most other benchmarks.
And we all know about "lies, damn lies, and benchmarks."
>and go." Having to deal with configuring ODBC data sources,
>installing *three* separate ODBC layers on Unix (ODBC
>itself, an ODBC driver for the database, and a Tcl-ODBC layer)
>would totally destroy that advantage. ODBC therefore
>loses out on grounds of both convenience and performance.
Note that ODBC also allows connection strings to be specified
in the connect string, without having to define a datasource
in advance. See the Wiki page for TclODBC for samples. A
small helper Tcl proc could easily build connections strings
for a variety of known databases.
If having a database independent layer is a goal for TCT,
then you could eliminate two of those installations layers
by shipping and compiling the driver manager and Tcl interface
with Tcl. That might be easier than designing and coding
a whole new interface layer.
--
Tom Poindexter
tpoi...@nyx.net
Gerhard Reithofer
> tun...@yahoo.com wrote:
> > I have followed this thread with a slight interest. As with the
...
> I don't quite understand what you're asking for here.
>
> If you're asserting that there is no ODBC on non-Windows platforms,
> unixODBC (http://www.unixodbc.org/) appears to be both popular and
> well-maintained. There was a release shipped just a couple of weeks
> ago. I can't comment on how well recent versions work. I haven't
> tried it in several years.
>
> If you're asserting that Tcl entirely lacks an interface to ODBC
> on non-Windows platforms, I know that tclodbc has been built on
> Unix hosts. Again, I haven't tried it there in quite some time.
I will describe my specific project to bring in some discussion aspect
which is NOT primarily interested in "database objects" or inventing a
new SQL language/syntax (but by the other way I did it). But what I
needed was an abstraction of the data model, "externalization" of the db
logics (server side sql functions with the different server languages)
and many "formatting" and some "introspection" functions.
Currently I'm working on a project which supports the 4 db-backends
PostgreSQL, MySQL, Oracle and MS-SQL. It consists of a web-part
programmed in PHP and many online-modules, all written in TCL.
I'm using the native interfaces pgtcl, mysqltcl, oratcl and tclodbc
(only for Microsoft SQL Server). The software has been tested on
Windows, several Linux distributions and partly on AIX.
As there is a dedicated functionality, it was obvious to implement the
abstraction layer in one TCL and one PHP file, i.e. no line of code
has to be changed by changing the database or - maybe - by implementing
an additional backend.
Typical datebase growth is ca. 20 - 30 million records per year, many
grouping functions are used, like:
give me the daily max-values of a specific month, the month-max values
of a year, all values within a day, all capacity changes of a year...
This was the main reason, why most functionality has been implented as
database procedures which are also more easy to abstract. Also this is
the reason, why additional db-layers (like ODBC) are not relevant as
only the "clean utilization data" must be transferred to the client and
only minimal logics (and data) is necessary on the client side.
The data is used to create graphics, charts and lists/tables which
become displayed on a web sides or on text-oriented and graphical TCL
clients.
The interface has been implemented as some sort of "communication"
meta-language, so no SQL-knowledge on client-programmers side is
necessary - example:
GET DATA PATH[,FROM,UNTIL[,RES]]
GET ... keyword for data retrieval
DATA .. sql data select (also other "data types" are used like INFO)
PATH .. tree like "addressing" server/type/service/resource/...
of the data source
FROM/UNTIL .. time range to extract
RES ... evaluation resolution like, DAY, WEEK, SECOND...
Example:
GET DATA "licsrv/FlexLM/27000","2007-05-02 00:00:00",
"2007-05-03 00:00:00","HOUR"
... retrieves all hour-max values withing one day for all licences of
a the license service 27000.
Typical interface functions are:
list of tables ("user objects")
table description
insert, update, delete record (requires unique ID)
connect/disconnect
sql function calls
etc.
The mainly used objective is to "hide" database specific handling, like
the different syntaxes of SQL function/procedure calls, the absolutely
different date handlings and the methods to retrieve database structures
like table descriptions.
Finaly I show the list of all TCL funtions, which MUST be implemented
for each database. A '+' in front means a namespace-exported function,
a '-' means "internally used only" - some of them are very application-
specific:
# -proc Set_DB_Error {code emsg}
# +proc Get_DB_Error {args}
# -proc Connect {db host user pass}
# -proc Disconnect {}
# +proc OpenConnection {db host user pass}
# +proc CloseConnection {}
# +proc OpenDB {db host user pass}
# +proc CloseDB {}
# -proc ColFormat {dsc}
# +proc Describe {table args}
# -proc DateFormat {date}
# +proc Feed_Snapshot {sqlh query_agent time_stamp value provider
# restype service resource capacity}
# -proc SQL_Select {sqlh stmt head}
# +proc SQL_Execute {sqlh stmt}
# +proc PLSQL_Execute {sqlh stmt}
# +proc Interval_Usage {sqlh provider restype service resource
from until interval}
# +proc Select {sqlh table what where order head args}
# +proc UserObjects {sqlh obj user}
# +proc SetResnameFilter {sqlh rlist}
# +proc EmptyResnameFilter {sqlh}
I hope this may lead to other sights for implementing DBI's which have
not been discussed so far.
--
Gerhard Reithofer
Tech-EDV Support Forum - http://support.tech-edv.co.at
tun...@yahoo.com
> I don't quite understand what you're asking for here.
>
> If you're asserting that there is no ODBC on non-Windows platforms,
> unixODBC (http://www.unixodbc.org/) appears to be both popular and
> well-maintained. There was a release shipped just a couple of weeks
> ago. I can't comment on how well recent versions work. I haven't
> tried it in several years.
I think you are changing the subject a little bit. Can I load
unixODBC, iodbc or other drivers on Linux/Unix from Tcl? Is there a
Tcl command I can use to connect, etc.? I don't think so. (I would
be glad to hear I am mistaken.) This is what tclodbc does and I think
everybody on this thread understands its value.
> If you're asserting that Tcl entirely lacks an interface to ODBC
> on non-Windows platforms, I know that tclodbc has been built on
> Unix hosts. Again, I haven't tried it there in quite some time.
> (I tend to regard it as a Windows library.) When I have used it,
> I've found Roy Nurmi, its manager, to be quite responsive to
> bug reports. It's mature enough on Windows that I haven't needed
> to report any bugs in several years. For what I use it for,
> it just works. If the Unix implementation is less stable,
> then you've a legitimate concern -- but please take it up in a
> separate thread, because it's a bit off-topic for this one.
He replied to you perhaps because he knows you? Some from our company
tried to contact him several years ago. There was no response from
him, which is a more common experience based on similar posts on this
forum.
As I said in my email, I did try to build it on non-Windows systems.
Again, it was possible a few years ago on Linux. My experience with
it says it is quite an accomplishment if you can get it to compile
successfully today, if at all.
> I owe you an explanation of why I haven't tried ODBC on Unix
> for quite some time. I have always found ODBC, even on its
> native Windows, to be slow, and often have stability issues
> with it as well. I use it to connect to Jet databases and
> SQLServer, for which no really good alternatives are available,
> but I don't use them much. My two workhorse databases are
> Oracle (for when only Big Iron will do) - which has very
> well supported native drivers - and SQLite. I use the latter
> because of its "zero configuration" properties. It runs
> in-process without a separate server, manages its databases
> simply as files in the file system, and is simply "drop in
> and go." Having to deal with configuring ODBC data sources,
> installing *three* separate ODBC layers on Unix (ODBC
> itself, an ODBC driver for the database, and a Tcl-ODBC layer)
> would totally destroy that advantage. ODBC therefore
> loses out on grounds of both convenience and performance.
I have used tclodbc on Linux with Oracle, DB2, PostgreSQL and a few
other databases, and even more databases on Windows. I did not find
it to be slow, or have stability issues. If you did, I would guess it
was more due to the app architecture than a simple API to talk to the
database.
In any case, ODBC comes with Windows. It should be similar on Linux.
With modern package managers, it is very simple. And, you only need
to do it once.
> Most of the discussion in this thread proposes the same for
> Tcl: a specification (and possible interface glue) so that
> all the databases present a common-denominator API. That
> would give you what you ask for: a uniform way to move your
> Tcl code from one database to another.
I disagree. It is about the availability of the glue that tclodbc
provides. It provides a nice interface. The additional discussion
has been to build on top of it at a higher level. You may agree or
disagree with the goals of such a project; but the underlying
foundation was a tool like tclodbc or snodbc.
> Bear in mind, though, that we do need this discussion. We
> do have to get the interface right. I already see a number
> of key ideas in this thread:
>
> - resistance to SQL insertion attacks has to be designed
> in and can be helped by integrating the SQL query
> tightly to the Tcl layer. SQLite does quite a good
> job of this. TclODBC has a more awkward interface,
> but at least gets the job done with prepared statements.
> Mysqltcl falls out of bed; the idea of providing
> a separate "magic quoting" operation to scrub
> text for insertion into a query is a non-starter.
The only platform that suffers from magic quoting is MySQL (and
magnified with its use with PHP). I have worked with literally with
more dozen databases and have never had a problem with it after
dealing with it once.
> - proper handling of NULLs is essential. This idea has
> a natural tension against Tcl; in Tcl, "everything is
> a string," but the NULL value is not any string.
> This has been a problem in the past; TIP 185 proposes
> one solution (ill-advised in my personal opinion).
> I'm convinced that using dictionaries to represent
> rows in result sets is a far better approach. I'll
> post more on this in a separate discussion, probably
> in the next few days.
I believe you can set a value of your choosing to to return instead of
NULL's. Same thing is available in oratcl too.
>
> - The interface must support result sets that are too
> large to fit in memory. Some of us do slog through
> multiple gigabytes of data on the client side. Sometimes
> there's just no alternative.
Ahh! Now I see why you have mentioned ODBC performance a few times.
You are transferring large amounts of data back and forth between the
database server and the client. And it would seem that ODBC was not
performing because of all the data transfers back and forth.
I do not think so. You will never have enough memory for your data.
This is one thing I do not like about Sqlite interface. If you are
retrieving plain data rows from the server and doing all the work on
the client (joins, merges, etc.), please read up on databases and
SQL. Or buy our products :-) Otherwise, you are re-implementing what
a database server does.
> - It is highly desirable for result sets to support
> external iterators ("cursors") as well as internal
> iterators (looping constructs). Once again, some
> of us maintain large federated databases, and sometimes
> there's no alternative to doing a merge-join on the
> client side that involves two or more multi-gigabyte
> result sets. Life is hard.
Again, this is database server responsibility; and most do provide
forward cursors at a minimum. ODBC and tclodbc support prepared
statements that handle it quite nicely.
> Expect more on this in the coming weeks. I'm rather under
> a lot of time pressure lately, so my replies are likely
> to have sporadic and unpredicable delays, but I am continuing
> to examine these issues.
If it can let me connect to databases on non-Windows systems, I am
really looking forward to it. I hope it provides at least the same
API that tclodbc does without getting in the way too much.
tun...@yahoo.com
> If having a database independent layer is a goal for TCT,
> then you could eliminate two of those installations layers
> by shipping and compiling the driver manager and Tcl interface
> with Tcl. That might be easier than designing and coding
> a whole new interface layer.
I agree with your comments on benchmarking wholeheartedly. But I also
wanted to emphasize your point here. In the past, we have suggested
that ActiveState do exactly this. At a minimum, ship tclodbc or
something similar with ActiveTcl or TDK like they now do with Oratcl.
The challenge was various licensing restrictions they would face.
> --
> Tom Poindexter
> tpoin...@nyx.net
Kevin Kenny
> As I said in my email, I did try to build [tclodbc] on non-Windows systems.
> Again, it was possible a few years ago on Linux. My experience with
> it says it is quite an accomplishment if you can get it to compile
> successfully today, if at all.
OK, tclodbc appears to have toolchain problems on Unix. Noted.
> Ahh! Now I see why you have mentioned ODBC performance a few times.
> You are transferring large amounts of data back and forth between the
> database server and the client. And it would seem that ODBC was not
> performing because of all the data transfers back and forth.
>
> I do not think so. You will never have enough memory for your data.
> This is one thing I do not like about Sqlite interface. If you are
> retrieving plain data rows from the server and doing all the work on
> the client (joins, merges, etc.), please read up on databases and
> SQL. Or buy our products :-) Otherwise, you are re-implementing what
> a database server does.
When I've been forced into large client-side operations, it's been
because I've been dealing with multiple servers with intransigent
DBA's. When you have some of your data in Oracle, some in SQL Server,
and some in MySQL, and none of the DBA's will allocate you space
to load the other one's data, you're kind of stuck. Perhaps you're
telling me that people in that situation should go find a better grade
of customer? You haven't stated an organizational affiliation,
so it's hard to tell what products you're recommending. But a data
warehousing solution is a bit of overkill for a one-off conversion.
> If it can let me connect to databases on non-Windows systems, I am
> really looking forward to it. I hope it provides at least the same
> API that tclodbc does without getting in the way too much.
What if I were to tell you that tclodbc is one of the model interfaces
being studied? It doesn't get everything quite right, but it comes
very close. Resurrecting it might indeed, as you and Tom Poindexter
observe, be a useful first step.
I'm a little nervous about using it for the long haul without
substantial rework, partly because of your observations that it has
substantial toolchain problems. Even in 2007, I find that C++ code
frequently does. For whatever reason, it simply isn't as easy to get
deployment right for C++ as it is for C. (I salute the people who
manage to do so, and fully concede that they are either smarter
or more industrious than I am.)
Tom Poindexter
I'm not worried about ActiveState, I am thinking about the
TCT Tcl distribution (from http://sf.net/projects/tcl)
As far as licensing goes:
Driver Managers:
UnixODBC - GPL
iODBC - BSD or LGPL (at choice of user)
ODBC Tcl Drivers:
TclODBC (Nurmi) - BSD
TclODBC (Heller) - GPL
SnODBC - public domain
(uses ffidl and libffi, both BSD)
Seems that licensing is not an issue.
Specific database drivers are still required. Some are
open source (BSD/LGPL/GPL), some are not. If you have
a database, you can probably get ODBC drivers for it.
--
Tom Poindexter
tpoi...@nyx.net
Tom Poindexter
>tun...@yahoo.com wrote:
>What if I were to tell you that tclodbc is one of the model interfaces
>being studied? It doesn't get everything quite right, but it comes
>very close. Resurrecting it might indeed, as you and Tom Poindexter
>observe, be a useful first step.
>
>I'm a little nervous about using it for the long haul without
>substantial rework, partly because of your observations that it has
>substantial toolchain problems. Even in 2007, I find that C++ code
>frequently does. For whatever reason, it simply isn't as easy to get
>deployment right for C++ as it is for C. (I salute the people who
>manage to do so, and fully concede that they are either smarter
>or more industrious than I am.)
Agreed, the C++ aspect of TclODBC is a little troublesome, but
there are at least two other implmentations:
Heller's reimplementation, using a SWIG-ified interface.
Kovalenko's interface, using Ffidl.
Heller's TclODBC is GPL'ed. Robert, if you are around, would
you consider re-licensing your code as BSD?
Kovalenko SnODBC is written in Tcl using Ffidl to call out
to the ODBC library. He has disclaimed any copyrights on it:
http://sw4me.com/wiki/LSWL?v=2te
Nurmi's original TclODBC could also be re-written with in C
with a little effort.
--
Tom Poindexter
tpoi...@nyx.net
Jeff Hobbs
LGPL and GPL are both incompatible with core Tcl's licensing as well, in
the way Tcl is presented and known. It has to be BSD or better. In
addition, ffidl may be BSD, but ffcall is GPL if you use that option,
which has more platform support iirc. That leave TclODBC (Nurmi) with
iODBC-BSD as the only choice.
Jeff
Tom Poindexter
Jeff Hobbs <je...@activestate.com> wrote:
>LGPL and GPL are both incompatible with core Tcl's licensing as well, in
>the way Tcl is presented and known. It has to be BSD or better. In
>addition, ffidl may be BSD, but ffcall is GPL if you use that option,
>which has more platform support iirc. That leave TclODBC (Nurmi) with
>iODBC-BSD as the only choice.
Cool, you have winner! Make it so.
Seriously, my whole point is that there alot of value in
reusing the ODBC API, driver manager and drivers. Don't
quite like TclODBC interface? Fine, change or extend it. Still easier
than designing and writing a whole new set of code. And probably not
all that hard to rewrite TclODBC in C, either.
--
Tom Poindexter
tpoi...@nyx.net
tom.rmadilo
> LGPL and GPL are both incompatible with core Tcl's licensing as well, in
> the way Tcl is presented and known. It has to be BSD or better. In
> addition, ffidl may be BSD, but ffcall is GPL if you use that option,
> which has more platform support iirc. That leave TclODBC (Nurmi) with
> iODBC-BSD as the only choice.
Not being an IP lawyer, I have to ask for more information.
My thought was that there would be several layers to this DBI
interface. Briefly:
1. database
2. db supplied adapter (native to odbc,jdbc, etc.)
3. driver adapter (connects db or adapter to generic interface)
4. generic interface (In C, using Tcl conventions, providing tcl
commands)
And, I thought the only thing of interest was #4. For instance in
Java, #4 is JDBC. Depending on the database, some layers are not
necessary, but talking about ODBC and/or a particular implementation
of that protocol, probably shouldn't be addressed within layer #4.
So, even if #4 becomes part of Tcl, only the end user will link that
with code which, might have any type of license (commercial, GPL). Why
should this be of any concern to the DBI developers?
The point should be to provide a layer which not only abstracts the
database differences, but also isolates the licensing stuff.
In general you are looking at writing layer #4 and then a layer #3 for
each database. ODBC is a good first choice because it is already a
generic interface which any sane database vendor provides (regardless
of performance).
I probably missed the point entirely, but if things are structured
like I described, what issues are important?
tclt...@aol.com
> In article <47288E49.5020...@acm.org>, Kevin Kenny <kenn...@acm.org> wrote:
A newbie question. Which is better? TclODBC or SnODBC?
Kevin Kenny
> Nurmi's original TclODBC could also be re-written with in C
> with a little effort.
Indeed; and in fact that is one possibility worth a serious look.
I still think we need to own one interface abstraction as "Tcl
database connectivity" - much as Sun did with JDBC. Presenting that
abstraction, though, might mean only a very thin layer of glue
atop ODBC. The same is true of JDBC's ODBC connectivity; there
isn't much inside it.
Please don't assume that I'm out to reinvent the wheel. I just
want to file off a few of the wheel's corners. :)
Artur
Mysqltcl was implemented to provide access to original C-API functions
from
libclientmysql library, which is part of mysql distribution.
http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html
Mysqltcl reflects this C-API.
I wanted that it is so useful and complete as you use C programming
language directly.
Anyway mysql C-api is also someway specific.
For example it provides are movable cursor.
Mysql API was designed to fit GUI clients in easy way.
Other DBs have not such functionality.
> - resistance to SQL insertion attacks has to be designed
> in and can be helped by integrating the SQL query
> tightly to the Tcl layer. SQLite does quite a good
> job of this. TclODBC has a more awkward interface,
> but at least gets the job done with prepared statements.
> Mysqltclfalls out of bed; the idea of providing
> a separate "magic quoting" operation to scrub
> text for insertion into a query is a non-starter.
>
mysqltcl provide only interface to C-API quoting function
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html
I think that it is the best solution to use original functions from
official API.
The costs of maintance are also low.
> - proper handling of NULLs is essential. This idea has
> a natural tension against Tcl; in Tcl, "everything is
> a string," but the NULL value is not any string.
> This has been a problem in the past; TIP 185 proposes
> one solution (ill-advised in my personal opinion).
> I'm convinced that using dictionaries to represent
> rows in result sets is a far better approach. I'll
> post more on this in a separate discussion, probably
> in the next few days.
In mysqltcl I have implemented new internal type for null value and
2 methods to handle it: mysql::isnull and mysql::newnull
I think also that sqlite way to handle SQL do not fit for all DBs.
Indeed sqlite provide their own sql-dialect, which is brilliant mix of
Tcl and SQL.
It can do it because the client-API parses SQL and so it can
substitute variables.
Sqlite was designed regarding the needs of Tcl and it is not
distributed.
For mysql the SQL parsing is done on server side. There is not so
possibility.
Anyway if someone want to use it, he should use [subst] and do it with
Tcl on his
responsibility.
In my opinion the best solution for this is to have multi layer
architecture.
So it is a problem of next layer to provide standardized way to handle
data access.
There are some example wrappers for it.
It is quite naive to design standard API for Tcl-DBI withoud rigarding
all DB's API, which one want to provide.
There are so many small differences.
I do not suppose that some DB producer would consider needs of Tcl.
Artur
Donal K. Fellows
> I wanted that it is so useful and complete as you use C programming
> language directly.
I want a standard Tcl database interface to make using a SQL DB as easy
as coding other things in Tcl. If I have to stamp on a few toes along
the way to make it happen, look out bunions! Here I come!
> In mysqltcl I have implemented new internal type for null value and
> 2 methods to handle it: mysql::isnull and mysql::newnull
But that's semantically wrong. The right way is to represent nulls as
unset variables or missing dictionary keys, so that I can use something
like [info exists] to do the null check.
> I think also that sqlite way to handle SQL do not fit for all DBs.
> Indeed sqlite provide their own sql-dialect, which is brilliant mix
> of Tcl and SQL. It can do it because the client-API parses SQL and so
> it can substitute variables. Sqlite was designed regarding the needs
> of Tcl and it is not distributed. For mysql the SQL parsing is done
> on server side. There is not so possibility. Anyway if someone want
> to use it, he should use [subst] and do it with Tcl on his
> responsibility.
With magical quoting? Sorry but no; that's a known source of problems in
production code. I believe that even mysql (finally!) supports proper
substitutions in prepared statements (i.e. user scripts no longer have
to do the parameter substitution themselves) so I feel under absolutely
no compulsion to allow support for magical quoting in the Std DB API.
> In my opinion the best solution for this is to have multi layer
> architecture. So it is a problem of next layer to provide
> standardized way to handle data access. There are some example
> wrappers for it.
Maybe, but your proposed layer interfaces are at the wrong place from
what I can tell by your above points.
> It is quite naive to design standard API for Tcl-DBI withoud
> rigarding all DB's API, which one want to provide. There are so many
> small differences. I do not suppose that some DB producer would
> consider needs of Tcl.
The presence of small differences should not be taken as an argument for
inaction. Instead, it should be a call-to-arms! We must define what the
essential parts of a common interface should look like, and then wrestle
with those differences so that they are either implementation details or
DB-specific extensions.
"The reasonable man adapts himself to the world; the unreasonable one
persists in trying to adapt the world to himself. Therefore all
progress depends on the unreasonable man." - George Bernard Shaw
Donal.