[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:
   * Tcl 8.5
   * TclOO extension
   * SQLite3 extension
======
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}
}
proc in {key list} {expr {$key in $list}}
namespace eval tableview {
	
	namespace import ::oo::*
	
	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}

======

----
!!!!!!
%| [Category Database] |%
!!!!!!