Hi all,
while looking for a database abstraction layer i had a look at
nstcl-database (http://nstcl.sourceforge.net), basically a compatibility
package to aolservers database api, and also looked at the rivet DIO
package. I also took a cursory look at XOSql
(http://www.xdobry.de/xosql/index.html)
So i started with a simple features checklist:
Supported Databases:
--------------------------------------------
MySQL nstcl, DIO, XOSql
PostgreSQL nstcl, DIO, XOSql
Sybase (equiv to MS SQL) nstcl
SQLite nstcl, DIO, XOSql
Oracle nstcl, XOSql
Solid nstcl
ODBC nstcl, XOSql
use Perl DBI XOSQL
--------------------------------------------
Not much of a concern, as adding new databases to DIO looks as easy as
it is for nstcl, just adding some small wrapper procs.
License:
--------------------------------------------
DIO Apache License
nstcl MIT/X11 License
XOSql GNU General Public License
--------------------------------------------
OO Style:
--------------------------------------------
DIO incrTcl
nstcl none
XOSql XOTcl
--------------------------------------------
Basic Functionalities:
executing a SQL query
--------------------------------------------
DIO $db exec $query
nstcl db_dml statement1 $query (*)
XOSql $db execute $query
--------------------------------------------
(* This is for a query modifing a value)
getting a single value from an SQL query
--------------------------------------------
DIO set string [$db string $query]
nstcl set string [database_to_tcl_string $dbhandle $query]
XOSql set string [lindex [[$db query $query] fetch] 0]
--------------------------------------------
getting one row of a SQL query into an array
--------------------------------------------
DIO $db array $query $arrayVar
nstcl* db_1row statement2 $query -columnVar $arrayVar
XOSql set rObj [$db query $query]
set keys [$rObj columNames]
set values [$rObj fetch]
foreach key $keys value $values {
set $arrayVar($key) $value
}
--------------------------------------------
(* nstcl errors out if 0 or more than one row is returned)
loop over the rows of a SQL query
(Example: set query "SELECT id,name FROM person")
--------------------------------------------
DIO set rObj [$db exec $query]
$rObj forall -array result {
puts "$result(id) -> $result(name)"
}
nstcl db_foreach statement3 $query {
puts "$id -> $name"
}
XOSql set rObj [$db query $query]
while {[llength [set row [$rObj fetch]]]} {
puts "[lindex $row 0] -> [lindex $row 1]"
}
--------------------------------------------
nstcl's style risks accidental overwriting of variables,
which could lead to security problems.
getting the first column of a SQL query as list
-----------------------------------------------
DIO set list [$db list $query]
nstcl set list [db_list statement4 $query]
XOSql set rObj [$db query $query]
set list [list]
while {[llength [set row [$rObj fetch]]]} {
lappend list [lindex $row 0]
}
-----------------------------------------------
Getting the whole result as nested list of lists
------------------------------------------------
DIO set llist [list]
set rObj [$db exec $query]
$rObj forall -list row {lappend llist $row}
nstcl set llist [db_list_of_lists statement5 $query]
XOSql set llist [$db queryList $query]
------------------------------------------------
Get the number of affected rows for a query
------------------------------------------------
DIO set rObj [$db exec $query]
set numRows [$rObj numrows]
nstcl* ?
XOSql set rObj [$db execute $query]
set numRows [$rObj rows]
------------------------------------------------
(* no idea after just looking at the docs)
Inserting a new row into the database
------------------------------------------------
DIO $db insert $arrayVar -table demo
nstcl* set id $arrayVar(id)
set name $arrayVar(id)
db_dml statement {
insert into demo (id , name) values (:id, :name)
}
XOSql set keys [array names $arrayVar]
set values [list]
foreach key $keys {lappend values $arrayVar($key)}
$db inserRow demo $keys $values
--------------------------------------------------------
(* not sure if the bind variable feature supports arrays,
the first two lines may be superfluous.)
Inserting a new row with automatic id
--------------------------------------------------------
DIO $db insert $arrayVar -table demo \
-keyfield id -autokey 1 -sequence demo_seq
nstcl* set name $arrayVar(name)
db_dml statement {
insert into demo (id, name)
values (
(select * FROM nextval(demo_seq)
), :name)
}
XOSql set keys [array names $arrayVar]
set values [list]
foreach key $keys {lappend values $arrayVar($key)}
$db rowInsertAutoId demo $keys $values id $sequencer **
--------------------------------------------------------
(* basically no support for automatic ids, use what the
underlying database provides)
(** I couldn't figure out from the docs what exactly has to be provided
by sequencer)
Delete a record from the database by primary key
--------------------------------------------------------
DIO $db delete $key -table demo -keyfield id
nstcl db_dml statement {
delete from demo where id = :id
}
XOSql* $db execute "delete from demo where id = $id"
--------------------------------------------------------
(* not sure if any quoting is done, may be a security problem)
Transaction support
--------------------------------------------------------
DIO $db exec {BEGIN TRANSACTION}
...
# do some operations
...
$db exec {COMMIT TRANSACTION}
nstcl* db_transaction {
...
# do some operations
...
}
XOSql $db execute {BEGIN TRANSACTION}
...
# do some operations
...
$db execute {COMMIT TRANSACTION}
---------------------------------------------------------
Basically neither DIO nor XOSql seem to have any real
transaction support.nstcl supports optional code to eval in case
of errors during a transaction to decide on commit or rollback.
Quoting support, for dynamic queries
---------------------------------------------------------
DIO* attempts autoquoting of values in queries,
XOSql provides escape method for simple value quoting
nstcl binding variables for queries with autoquoting
functions to quote identifiers and values
---------------------------------------------------------
* The current practice in DIO is insecure and is an SQL injection attack
vector. Only values are quoted. Identifiers (table and field names) are
passed to the database without any quoting.
Value quoting seems broken and incomplete. It does not take SQL quoting
rules into account, which may lead to data inconsistencies.
Example of SQL Injection:
set table users
set match {'\' OR 1==1; --}
$obj exec "SELECT * FROM $table WHERE id = $match AND password = $passwd;"
Similar issues may be present in XOSql and nstcl but i did not take a
closer look.
Conclusions:
-------------------------------------------------------------
All three interfaces behave quite similar for simple queries,
only differing in style of commands (OO vs. procedural) and
arguments/results available (objs, lists, nested lists, arrays).
Mostly a matter of taste.
XOSql offers the interesting option to use the Perl DBI drivers with
the help of an extension of the same author. Similar things could
probably easily be done with TclBlend and JDBC drivers.
For complexer scenarios both XOSql and DIO are missing support for
handling transactions.
nstcl's database support adds support for bind variables for all
databases, based on some simple tcl procs, which could be adopted by DIO
and XOSql. Even better would be to use the binding functions and support
for prepared statements of the underlying database. At least SQLlite and
newer Pgtcl versions support some form of binding variables.
To provide appropriate quoting support, all layers should look at the db
interfaces and use the database specific quoting functions (available at
least for mysql, postgres) and provide a compatibility function based on
the SQL standard + variations for each db driver for all others.
I have not yet looked at the DIODisplay and the nstcl multirow
datasource features, which look interesting.
Hope this is interesting or informative for some of you
Michael Schlenker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
