Version 0 of 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}} {
    data        set [split "[string repeat " " [columns]] " ""]
    topdata     set [data get]
    topcapsep   set [split "[string repeat " =" [columns]] " ""]
    topcapsep enable
    tcaption $n
}

::struct::matrix m
set thisrow 0
ssdb eval "select * from sqlite_master;" x {
    set thiscol 0
    if { $thisrow == 0 } {
        foreach col $x(*) {
            incr thiscol
        }
        set ncols $thiscol
        set thiscol 0
        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