if 0 {
[Brian Theado] - 20May2006 - Implementation of [ratcl]-like "views" using the
[sqlite] engine. I really like the [ratcl] API and I wanted to see how far I
could take it with an [sqlite] backend.  It seems to have turned out pretty
well.  I think I would choose this api over the default [sqlite] api.  It's
still very rough in some places.

It pales in comparison to all the things [ratcl] can do (and will be able to do),
but it is still nice.

Every view is backed by an actual [sqlite] table.  Derived views consist of sql
queries.  A pipeline of sql queries gets converted into nested subqueries.

The supported sql is similar to normal sql
(http://www.sqlite.org/lang_select.html), except the from clause should be
omitted.  The from clause is automatically added in:

 set v [vdef a b {1 2 3 4 5 6}] ;# Executes create table and 3 inserts
 view $v get  ;# Equivilent to "select * from $table"
 view $v select a+b as s | get  ;# select a+b as s from $table
 view $v select a+b as s where s > 5 | dump  ;# select a+b as s from $table where s > 5
 view $v select a,count(a) group by a | get ;# select a,count(a) from $table group by a 
 view $v select a,b | select a | get ;# select a from (select a,b from $table)

Pipeline commands exist for "where" and "order by" as shortcuts for "select *":
 view $v where a > 1 | dump ;# select * from $table where a > 1
 view $v order by b desc | dump ;# select * from $table order by b desc

Union, intersect, and except operate on multiple views
 set v2 [vdef a b {7 8 9 10}]
 view $v union $v2 | get

Join also operates on multiple views.  Only a subset of the join syntax is
functional.  The "on" clause doesn't work because it requires knowing table
names and the [ratcl]-like api hides table names.  However, the "using" clause
only identifies column names and will work:
 set v3 [vdef a c {1 7 3 8 9 10}]
 view $v join $v3 using (a) | dump  ;# select * from $t1 join (select * from $t2) using (a)

The sql that will be used for a given derived view can be inspected:
 view $v tosql

'''Resource management'''

The underlying [sqlite] table never gets cleaned up, so resources are not managed
as nicely as for [ratcl].  However, derived views are simple Tcl lists and so are
automatically "garbage collected".

vdef and freeze results in the creation of an [sqlite] table

The vdef command uses an in memory [sqlite] database to store the underlying tables.
The vopen command can be used to create a view from an arbitrary sqlite database

'''Mutable views'''

Underived views are mutable from the start.  Calling a mutatable operation on
a derived view will result in a "freeze" before applying the operation.  Update,
insert and delete are supported:
 view $v set a=55,b=56 where a=1 | dump ;# update $table set a=55,b=56 where a=1
 view $v delete where a=55 | dump ;# delete from $table where a=55
 view $v insert values (7,8) | dump ;# insert into $table values (7,8)

'''No subviews'''

Sqlite deals only with flat tables and so subviews aren't supported

'''Other operations''': get, clone, dump, html, do, concat, first, last, info, open, freeze, each
}
 source sqlite-3_3_5.kit  ;# sqlite.org offers this kit with Windows, linux and Mac binaries
 package require sqlite3

 # Adapted from ratcl's m_dump
 proc dump {v {maxrows 20}} {
  set data [view $v first $maxrows | get]
  set colnames [colnames $v]
  set numcols [llength $colnames]
  set numrows [expr [llength $data] / $numcols]

  # Calculate column widths
  for {set col 0} {$col < $numcols} {incr col} {
    set w [string length [lindex $colnames $col]]
    for {set row 0} {$row < $numrows} {incr row} {
      set idx [expr ($row * $numrows) + $col]
      set cell [lindex $data $idx]
      if {[string length $cell] > $w} {set w [string length $cell]}
    }
    if {$w > 50} {set w 50}
    append fmt "  " %-$w.${w}s
    append hdr "  " [format %-${w}s [lindex $colnames $col]]
    append bar "  " [string repeat - $w]
  }

  # Add the header and a formatted version of each row to the output
  set r [list $hdr $bar]
  for {set row 0} {$row < $numrows} {incr row} {
    set cmd [list format $fmt]
    foreach cell [lrange $data [expr $row*$numcols] [expr ($row+1)*$numcols - 1]] {
      lappend cmd [regsub -all {[^ -~]} $cell .]
    }
    lappend r [eval $cmd]
  }

  # Add footer dots if the entire view was not displayed
  set fullrowcount [view $v select count(*) | get]
  if {$fullrowcount > $maxrows} {lappend r [string map {- .} $bar]}
  join $r \n
 }

 # Adapted from ratcl's m_html
 proc html v {
  set names [colnames $v]
  set o <table>
  append o {<style type="text/css"><!--\
    table {border-spacing:0; border:1px solid #aaa; margin:0 0 2px 0}\
    th {background-color:#eee; font-weight:normal}\
    td {vertical-align:top}\
    th,td {padding:0 2px}\
    th.row,td.row {color:#aaa; font-size:75%}\
  --></style>}
  append o \n <tr>
  foreach x $names { append o <th> $x </th> }
  append o </tr>\n
  view $v each c {
    append o <tr>
    foreach x $names {
        set z [string map {& &amp\; < &lt\; > &gt\;} $c($x)] 
        append o {<td align="right">}
        append o $z </td>
    }
    append o </tr>\n
  }
  append o </table>\n
 }

 # TODO: should take optional db as input? And the table variable
 # should be a counter stored in a database table?
 proc vdef args {
    variable table
    if {![info exists table]} {
        sqlite [namespace current]::db :memory:
        set table 0
    } else {
        incr table
    }
    
    # Adapted from ratcl's vdef
    set data [lindex $args end]
    set args [lrange $args 0 end-1]
    set d [llength $data]
    set c [llength $args]
    if {$d > 0} {
        if {$c == 0} { error "no args defined" }
        if {$d%$c != 0} { error "data is not an exact number of rows" }
        set n [expr {$d/$c}]
    } else {
        set n 0
    }

    # Create the sqlite table and insert the data
    db eval "create table t$table ([join $args ,])"
    foreach $args $data { 
        set row {}
        foreach col $args {
            lappend row [set $col]
        }
        db eval "insert into t$table values ([join $row ,])"
    }

    # A basic view is just a list of the sqlite db and a table name
    return [list [namespace current]::db t$table]
 }
 proc vopen {db table} {return [list $db $table]}
 proc freeze v {
    variable table
    set db [lindex $v 0]
    incr table
    $db eval "create table t$table as [createQuery $v]"
    return [list $db t$table]
 }
    
 # Adapted from ratcl's m_do
 proc do {v cmds} {
  set r [list view $v]
  foreach x [split $cmds \n] {
    if {![regexp {^\s*#} $x]} { append r " | " $x }
  }
  uplevel 1 $r
 }

 # Retreive column names for the given view
 # TODO: Doesn't work for table with no rows (returns empty list) 
 # I haven't found any other way in sqlite's Tcl API to query 
 # column names from an arbitrary query.  I haven't checked the
 # C API yet to see if it is possible there.
 proc colnames v {
    set colnames {}
    view $v first 1 | each r {
        set colnames $r(*)
    }
    return $colnames
 }

 # TODO: omitcols, vfun, save?
 # vfun - sqlite has a subcommand "function" that could accomplish something similar
 # insert - allow multiple rows by using the same format as for vdef?
 # commit - sqlite autocommits unless the sql is surrounded by begin/commit
 proc view {v args} {
    while {[llength $args]} {
        set n [lsearch -exact $args |]
        if {$n < 0} { set n [llength $args]; lappend args | }
        set cmdAndArgs [lreplace $args $n end]
        set db [lindex $v 0]
        set table [lindex $v 1]
        if {[llength $cmdAndArgs]} {
            switch [lindex $cmdAndArgs 0] {
                select - where - order -
                join - union - intersect - except -
                first - concat - last {
                    # Derived view operations--just add them to the list--
                    # they will be processed when createQuery is called
                    lappend v $cmdAndArgs
                }
                clone {
                   set v [eval vdef [colnames $v] {{}}]
                }
                get {set v [$db eval [createQuery $v]]}
                each {
                    set aname [lindex $cmdAndArgs 1]
                    set body [lindex $cmdAndArgs 2]
                    set v [uplevel [list $db eval [createQuery $v] $aname $body]]
                }
                tosql {
                    set v [createQuery $v]
                }
                do {set v [do $v [lindex $cmdAndArgs 1]]}
                freeze {set v [freeze $v]}
                open {
                    set v [vopen $db [lindex $cmdAndArgs 1]]
                }
                info {
                    switch [lindex $cmdAndArgs 1] {
                        names {set v [colnames $v]}
                        db {set v $db}
                        table {set v $table}
                        default {error "'[lindex $cmdAndArgs 1]' should be one of names,db,table"}
                    }
                }
                dump {
                  if {[llength $cmdAndArgs] == 2} {
                    set v [dump $v [lindex $cmdAndArgs 1]]
                  } else {
                    set v [dump $v]
                  }
                }
                html {set v [html $v]}
                insert {
                    if {[llength $v] > 2} {set v [freeze $v]}
                    # Insert the "into table" part of the delete statement and execute
                    set sql [concat insert into $table [lrange $cmdAndArgs 1 end]]
                    $db eval $sql
                }
                delete {
                    if {[llength $v] > 2} {set v [freeze $v]}
                    # Insert the "from table" part of the delete statement and execute
                    set sql [concat delete from $table [lrange $cmdAndArgs 1 end]]
                    $db eval $sql
                }
                set {
                    if {[llength $v] > 2} {set v [freeze $v]}
                    # Insert the "table" part of the update statement and execute
                    set sql [concat update $table [lrange $cmdAndArgs 0 end]]
                    $db eval $sql
                }
                default {
                    error "Invalid subcommand: '[lindex $cmdAndArgs 0]'"
                }
            }
        }
        set args [lreplace $args 0 $n]
    }
    return $v
 }

 proc createQuery v {
    set db [lindex $v 0]
    set table [lindex $v 1]
    set query "select * from $table"  ;# return full table if commands are empty
    foreach cmd [lrange $v 2 end] {
        # Preprocess some special cases
        switch [lindex $cmd 0] {
            concat {
                set cmd [list {union all} [lindex $cmd 1]]
            }
            first {
                set cmd [list limit [lindex $cmd 1]]
            }
        }
        switch [lindex $cmd 0] {
            select {
                # The select can have anything except for the from clause
                # Insert the from clause just before the first "where", "group",
                # or "order" clause or at the end if no clauses found
                if {![regsub {where|group|order} $cmd "from $table &" query]} {
                    set query [concat $cmd from $table]
                }
            }
            where - order - limit {
                set query [concat select * from $table $cmd]
            }
            last {
                set limit [lindex $cmd 1]
                set totalrows [db eval [concat select count(*) from $table]]
                set offset [expr $totalrows - $limit]
                set query [concat select * from $table limit $limit offset $offset]
            }
            join {
                # First argument is the view to join with, so just do an lreplace of that
                # arg with createQuery called on that arg
                set cmd [join [lreplace $cmd 1 1 ([createQuery [lindex $cmd 1]])] " "]
                set query [concat select * from $table $cmd]
            }
            intersect - union - except - "union all" {
                # First argument is the view to join with, so just do an lreplace of that
                # arg with createQuery called on that arg
                set cmd [join [lreplace $cmd 1 1 [createQuery [lindex $cmd 1]]] " "]
                set query [concat select * from $table $cmd]
            }
            default {error "invalid cmd [lindex $cmd 0]"}
        }
        set table ($query)
    }
    return $query
 }
 if 0 { # tests
 set v [vdef a b {1 2 3 4 5 6}]
 puts [view $v get]
 puts [view $v select a+b | get]
 puts [view $v union [vdef a b {7 8 9 10} ] | get]
 puts [view $v select a,b,a-b | tosql]
 puts [view $v order by b desc | get]
 puts [view $v insert values (7,8) | get]
 puts [view $v set a=55 where a=1 | get]
 puts [view $v delete where a=55 | get]
 puts [view $v select a,b where a=5 order by b | dump]
 puts [view $v html]
 puts [view $v do {
    select a,b,a-b order by b desc
    first 1
    dump
    }]
 puts [view $v clone | insert values (1,2) | dump]
 puts [view $v open sqlite_master | dump]
 }
----
[Category Database] | [Category Example]