[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''')

**Requirements**
   * Tcl 8.5
   * [TclOO] extension
   * [SQLite]3 extension
----
**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}

======

----
'''[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
                



<<categories>> SQLite