Version 3 of Object-oriented SQLite wrapper

Updated 2008-01-06 12:48:08 by dkf

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)

Requirements


Source Code

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
		}
	}
}
	

Usage

Create a SQL table EMP with two columns, EMP_NAME and EMP_AGE.

  • tableview::table create instance_name sqlite_cmd table_name
  • inst add column name ?-key bool?
  • inst get index int|end?-int?
  • inst get range startidx endidx
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}