Sarnold: Here is a tiny wrapper for SQLite. The goal of this project is to build a SQL Table editor mega-widget, similar in features to OpenOffice.org's forms.
This would lead to something like an object-oriented framework based on the MVC pattern. Well, at least I am trying to do so... :-)
Here is the code (last update 2008-01-06)
package provide tableview 0.1 package require Tcl 8.5 package require TclOO proc assert {expr {msg {Assertion failed}}} { if {![uplevel 1 expr $expr]} {error $msg} } namespace eval tableview { namespace path {::oo ::tcl::mathop} namespace export table class create table { constructor {sqlhandler name} { my variable tablename my variable sql my variable colopts my variable columns my variable length set colopts {-key -type -width} set tablename $name set sql $sqlhandler set columns "" set length -1 } method sqleval {string} { my variable sql $sql eval $string } method add {type args} { my add_$type {*}$args } method add_column {name args} { my variable colopts my variable columns assert {![dict exists $columns $name]} set opts [dict create] foreach {key val} $args { assert {[in $key $colopts]} dict lappend opts $key $val } dict set columns $name $opts } method get {param args} { my get_$param {*}$args } method refresh {{force no}} { my variable length my variable tablename if {$force eq "-force"} {set force yes} if {$length>=0 && !$force} {return} set length [my sqleval "select count(*) from $tablename"] } method columns_list {} { my variable columns dict keys $columns } method get_index {idx} { my variable length my refresh set idx [my end_index $idx] my variable tablename set cols [my columns_list] my sqleval "select [join $cols ,] from $tablename where rowid=$idx" } method end_index {end} { my variable length if {[string is digit $end]} { assert {$end > 0 && $end <= $length} return $end } assert {[regexp {end(-\d+)?} $end]} if {$end eq "end"} {return $length} expr {$length-[string map {end- ""} $end]} } method sqldata {data} { foreach datum $data col [my columns_list] { # TODO : take care of column types if {[string is integer $datum] || [string is double $datum]} { lappend res $datum continue } # we have to encode the string lappend res '[string map {' ''} $datum]' } set res } method get_range {start end} { my variable length my refresh assert {$start>0 && $start <= $length} set end [my end_index $end] my variable tablename set cols [my columns_list] my sqleval "select [join $cols ,] from $tablename where rowid>=$start and rowid<=$end" } method insert {data} { my variable tablename my sqleval "insert into $tablename values ([join [my sqldata $data] ,])" my refresh -force } } }
Create a SQL table EMP with two columns, EMP_NAME and EMP_AGE.
package require sqlite3 sqlite3 db my.db tableview::table create tbl db emp tbl add column emp_name -key yes tbl add column emp_age tbl insert {Martine 36}