EMJ 2017-11-16 The result of any SQL query is a number of rows each containing a fixed number of columns. Just like a matrix. So using Tcllib's
struct::matrix and its companion
report seemed like a good idea for formatting some SQLite query results.
I wanted a simple interface, so a proc that has the sqlite3 connection, the actual query, and some parameters as arguments seems to be simple enough. The parameter argument is a list with an even number of elements, each pair being a name and the corresponding value. (This means that it is also a valid dict). The parameters are meant to be used in the query, so I could have something like:
dsp_mode_column db {
SELECT name
, height
FROM people
WHERE height > :minheight;
} {minheight 180}So the actual proc looks like this:
proc dsp_mode_column {db sqlstmt params {wraplast false}} {I will get to the fourth argument later.
The next bit is the template for the
report package. It might need its own explanation, but see
the report documentation
for now.
set reslist_body {
set templ_d [lreplace [lreplace \
[split "[string repeat " x" [columns]] " x] \
0 0 {}] end end {}]
set templ_tc [lreplace [lreplace \
[split "[string repeat " x=x" [columns]] " x] \
0 0 {}] end end {}]
data set $templ_d
topdata set [data get]
topcapsep set $templ_tc
topcapsep enable
tcaption $n
}And on with the main part of the proc...
# get the parameters into their variables
dict for {k v} $params {
set $k $v
}
try {
# try is just to ensure cleanup, no errors will be handled
::struct::matrix ssresm
set thisrow 0
set nrows 0
$db eval $sqlstmt x {Now we are looking at one row of the query result, with the array
x providing the list of column names and the actual values.
set thiscol 0
if { $thisrow == 0 } {
# first time here, so create a matrix row to be the
# header containing the column names
set ncols [llength $x(*)]
ssresm add columns $ncols
ssresm add row
foreach col $x(*) {
ssresm set cell $thiscol $thisrow $col
incr thiscol
}
incr thisrow
set thiscol 0
}
# now do one row of data
ssresm add row
foreach col $x(*) {
ssresm set cell $thiscol $thisrow $x($col)
incr thiscol
}
set nrows $thisrow
incr thisrow
}The next bit only happens if the fourth argument to the proc is true. Ignore it for now!
if { $wraplast } {
set cols [ssresm columns]
set pre_last 0
for { set i 0 } { $i < [expr { $cols - 1 }] } { incr i } {
incr pre_last [ssresm columnwidth $i]
incr pre_last 2
}
set lastw [expr { 78 - $pre_last }]
set lastcol [expr { $ncols - 1 }]
for { set r 1 } { $r <= $nrows } { incr r } {
set str [ssresm get cell $lastcol $r]
set paras [textutil::split::splitx $str {\n\n}]
set newstr [list]
foreach p $paras {
lappend newstr \
[textutil::adjust $p -length $lastw \
-justify left -strictlength true]
}
ssresm set cell $lastcol $r [join $newstr "\n\n"]
}
}Stop ignoring!
if { $nrows > 0 } {
# got at least one row, so
# set the template for the report
::report::defstyle resultlist {{n 1}} $reslist_body
::report::report ssresr $ncols style resultlist
# and use it to print the matrix
puts [::ssresr printmatrix ssresm]
# print the number of rows
set out "$nrows row"
if { $nrows != 1 } {
append out "s"
}
append out " found\n"
puts $out
} else {
puts "no rows found\n"
}
} finally {
# note no handlers, this is just for cleanup
catch {ssresm destroy}
catch {::ssresr destroy}
catch {::report::rmstyle resultlist}
}
}So for my trivial query above, I would get something like:
name height
======= ======
Fred 182
Michael 181
Justine 189
3 rows found
It may seem a little bit long, but it can be reused over and over again for any query at all.
By the way,
struct::matrix has the ability to have data arrays which shadow the matrix and can be used to update it, but I didn't see any point in using them for this.
