Version 4 of Object-oriented SQLite wrapper

Updated 2008-01-26 20:22:22 by sarnold

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_sql 0.1
package require Tcl 8.5
package require TclOO
	
namespace eval tableview {
	
	namespace import ::oo::*
	# here some really useful procs, keeping them private
	proc assert {expr {msg {Assertion failed}}} {
		if {![uplevel 1 expr $expr]} {error $msg}
	}
	proc in {key list} {expr {$key in $list}}
	proc map {var body list} {
		set o {}
		foreach $var $list {lappend o [eval $body]}
		set o
	}
	proc default {dict key default} {
		if {[dict exists $dict $key]} {
			return [dict get $dict $key]
		}
		set default
	}
	class create table {
		constructor {sqlhandler name} {
			my variable tablename
			my variable sql
			my variable colopts 
			my variable columns
			my variable length
			my variable filters
			set colopts {-key -type -width}
			set tablename $name
			set sql $sqlhandler
			set columns ""
			set length -1
			set filters ""
		}
		method sqleval {string} {
			my variable sql
			$sql eval $string
		}
		method column {type args} {
			my column_$type {*}$args
		}
		method column_info {sub args} {
			my column_info_$sub {*}$args
		}
		method column_info_options {} {
			my variable colopts
			set colopts
		}
		method column_add {name args} {
			my variable colopts
			my variable columns
			tableview::assert {![dict exists $columns $name]}
			set opts [dict create -type string]
			foreach {key val} $args {
				tableview::assert {[tableview::in $key $colopts]}
				dict set opts $key $val
			}
			dict set columns $name $opts
		}
		method filter {name args} {
			my filter_$name {*}$args
		}
		method filter_delete {name} {
			my variable filters
			set res ""
			foreach elt $filters {
				if {![string equal [lindex $elt 0] $name]} {lappend res $elt}
			}
			set filters $res
			my refresh -force
		}
		method filter_add {name op value} {
			my variable columns
			set matches {== != < <= > >= begin end}
			tableview::assert {[tableview::in $op $matches]} "unknown operator $op"
			my variable filters
			tableview::assert {[tableview::in $name [dict keys $columns]]} "unknown column name $name"
			lappend filters [list $name $op $value]
			my refresh -force
		}
		method where_subclause {name op val} {
			switch -- $op {
				begin {
					append val %
					set op like
				}
				end {
					set val "%$val"
					set op like
				}
			}
			return "$name $op [my encode $name $val]"
		}
		method where_clause {} {
			my variable filters
			set clause [join [tableview::map x {uplevel 1 my where_subclause {*}$x} $filters] " AND "]
			if {$clause eq ""} {return 1}
			set clause
		}
			
		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 $length}
			set length [my sqleval "select count(*) from $tablename where [my where_clause]"]
		}
		method length {} {
			my refresh
		}
		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 variable sql
			set i 1
			$sql eval "select [join $cols ,] from $tablename where [my where_clause]" data {
				if {$i == $idx} {return [array get data]}
				incr i
			}
			error "no such index"
		}
		method end_index {end} {
			my variable length
			if {[string is digit $end]} {
				tableview::assert {$end > 0 && $end <= $length}
				return $end
			}
			tableview::assert {[regexp {end(-\d+)?} $end]}
			if {$end eq "end"} {return $length}
			expr {$length-[string map {end- ""} $end]}
		}
		method encode_str {value} {
			return '[string map {' ''} $value]'
		}
		method encode {name datum} {
			my variable columns
			set type [dict get $columns $name -type]
			switch -glob -- $type {
				int* {
					return $datum
				}
				default {
					return '[string map {' ''} $datum]'
				}
			}
		}
		method sqldata {data} {
			set res ""
			foreach datum $data col [my columns_list] {
				# encode strings with quotes
				lappend res [my encode $col $datum]
			}
			set res
		}
		method get_range {start end} {
			my variable length
			my refresh
			set start [my end_index $start]
			set end [my end_index $end]
			my variable tablename
			my variable sql
			set cols [my columns_list]
			set i 1
			$sql eval "select [join $cols ,] from $tablename where [my where_clause]" data {
				if {$i >= $start && $i <= $end} {lappend res [array get data]}
				incr i
			}
			set res
		}
		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}