1.  Loading Sqlite into a Tree

The tree sqlload sub-command can efficiently load sqlite3 database rows into a tree object.

Here is an example:

#!/usr/bin/env wize

proc SqlView {w file {table sqlite_master} {fields *}} {
  # Mini SQL-viewer
  set t [tree create]
  $t sqlload $file "select $fields from $table"
  treeview $w -tree $t
  pack $w -fill both -expand y
  eval $w col insert end [lsort [$t keys all]]
}

eval SqlView .db $argv

Calling this with just a db file name will display the sqlite table index. Add a table name to view a particular table, eg.

 ./sqlview.tcl mydata.db customers

Displays

2.  Handling Edits

One advantage of using a tree is that a write trace can be used to mirror changes back to sqlite. Thus the following creates an interactive sql table editor in 20 lines of code:

#!/usr/bin/env wize

proc SqlEdit {w file {table sqlite_master} {fields *}} {
  # Mini SQL-editor.
  set t [tree create]
  $t sqlload $file "select $fields from $table"
  treeview $w -tree $t
  pack $w -fill both -expand y
  eval $w col insert end [lsort [$t keys all]] -edit 1
  sqlite3 $file $file
  $t trace create all * w [list SyncEdit $file $table]
}

proc SyncEdit {dbh tbl t id key op} {
   # Sync tree change back to sqlite.
   set val [$t get $id $key {}]
   $dbh eval "UPDATE '$tbl' SET '$key' = \$val WHERE rowid == \$id;"
}

eval SqlEdit .db $argv

(See also TreeSqlSync).

3.  Dumping a Tree To Sqlite

Although tree provides no builtin sqldump command , Wize provides a scripted one via tclsqlite. The following is the implementation of the utility *sqldump.

    proc *sqldump {t db table {ids {}}} {
        # Dump nodes from tree t into table in open sqlite database db.
        if {$ids == {}} { set ids [$t children root] }
        set keys [$t keys $ids]
        catch { $db eval "CREATE TABLE $table ( [join $keys ,] )" }
        $db eval {BEGIN;}
        set rc [catch {
            $t with s $ids {
                set nams {}
                set vals {}
                foreach nam $s(*) {
                    lappend vals \$s($nam)
                    lappend nams $nam
                }
                set vals [join $vals ,]
                set nams [join $nams ,]
                set q [format {INSERT INTO %s (%s) VALUES (%s)} $table $nams $vals]
                $db eval $q
            }
        } rv]
        if {$rc} {
            $db eval {ROLLBACK;}
        } else {
            $db eval {COMMIT;}
        }
        return -code $rc $rv
    }

    set db mydb.dat
    sqlite3 $db $db
    sqldump $t $db cust2

4.  Performance

The sqlload command is quite fast. It can populate a tree with 10k nodes in about 100 milliseconds. This is about 7 times faster than can be achieved using a Tcl script with sqlite3 eval.

Sqlload provides other advantages. It preserves NULL values (as unset keys). It also preserves the object types that are used internally by sqlite (eg. int or double). This avoids later reconversion which improves efficiency even further.

© 2008 Peter MacDonald