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... :-)
To be used with A SQLite table widget.
Here is the code (last update 2008-01-26)
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
}
}
}
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}
RFox - 2012-09-17 17:07:19
tdbc anybody?
rattleCAD - 2018-07-26 01:31:19
could you please explain the usage/architecture of your classes?
e.g. the constructor of the class table does not create or modify any table or column
please add a running example