Formatting SQLite query results with struct::matrix

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.