- EMJ 2017-03-28
- I needed a window for ad-hoc SQL queries in a sqlite-based application - easy enough, except for formatting the output neatly. As usual, I wanted to avoid totally re-inventing the wheel, so when I found Animations in user interfaces considered harmful I decided to see if the matrix/report combination of Tcllib utilities would work for this. They do work, rather nicely I think, you don't even need to link the matrix to an array.
package require sqlite3
package require struct::matrix
package require report
package require textutil
sqlite3 ssdb stats.db
::report::defstyle resultlist {{n 1}} {
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
}
::struct::matrix m
set thisrow 0
ssdb eval "select * from sqlite_master;" x {
set thiscol 0
if { $thisrow == 0 } {
set ncols [llength $x(*)]
m add columns $ncols
m add row
foreach col $x(*) {
m set cell $thiscol $thisrow $col
incr thiscol
}
incr thisrow
set thiscol 0
}
m add row
foreach col $x(*) {
m set cell $thiscol $thisrow [::textutil::untabify2 $x($col) 4]
incr thiscol
}
incr thisrow
set nrows $thisrow
}
::report::report r $ncols style resultlist
puts [r printmatrix m]
m destroy
r destroy
::report::rmstyle resultlistResults in (for an odd little database I happen to have lying around):type name tbl_name rootpage sql
===== =========================== ======== ======== =======================================
table rainfall rainfall 2 CREATE TABLE rainfall (
day VARCHAR PRIMARY KEY
, mmrain FLOAT
)
index sqlite_autoindex_rainfall_1 rainfall 3
table weight weight 4 CREATE TABLE weight (
person VARCHAR
, day VARCHAR
, kg FLOAT
, PRIMARY KEY (
person
, day)
)
index sqlite_autoindex_weight_1 weight 5
table bp bp 6 CREATE TABLE bp (
person VARCHAR
, day VARCHAR
, seq INTEGER
, sys FLOAT
, dia FLOAT
, PRIMARY KEY (
person
, day
, seq
)
)
index sqlite_autoindex_bp_1 bp 7 