Formatting SQLite Query Output

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 resultlist

Results 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

Category Package

Category tcllib