Generic function to print a SQLite query

[Cecil Westerhof]

I have some code to print a SQLite query as a table. Like:
+------------+-----+
| 2018-04-27 | 2 |
| 2018-04-30 | 10 |
| 2018-05-01 | 14 |
| 2018-05-02 | 13 |
| 2018-09-03 | 14 |
| notUsed | 177 |
+------------+-----+


The code that does the work:
set divider [string map {" " - | +} [format ${formatStr} "" ""]]


sqlite3 db ${database} -create False -readonly True
puts ${divider}
db eval ${query} {
puts [format ${formatStr} ${used} ${count}]
}
puts ${divider}
db close


This is something I want to do for a lot of queries. So it would be
nice to make a generic function. This should get the database, the
query and the format string.

The only problem is the line that creates the divider and the line in
the eval loop. Both are dependent on the number of selected fields.
And the last one on the names of the fields.

Is there a nice way to make this generic function I like to have?

--
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[Cecil Westerhof]

The eval loop can be rewritten as:
db eval ${query} values {
set list {}
foreach field ${values(*)} {
lappend list $values($field)
}
puts [format ${formatStr} {*}${list}]
}

The divider problem I could solve with an extra parameter that tells
how many strings the format needs. Or is there a better way?

[Cecil Westerhof]

I think this works nicely:

sqlite3 db ${database} -create False -readonly True

set fields {}
foreach field [db eval "SELECT * FROM (${query}) LIMIT 1"] {
lappend fields ""
}
set divider [string map {" " - | +} [format ${formatStr} {*}${fields}]]
puts ${divider}
db eval ${query} values {
set fields {}
foreach field ${values(*)} {
lappend fields $values($field)
}
puts [format ${formatStr} {*}${fields}]

}
puts ${divider}
db close

I probably should change db to something else. In this way the risk of
a conflict is quite high.

If I would call the proc SQLiteQueryToTable, I could use:
sqlite3 SQLiteQueryToTable ${database} -create False -readonly True

Would that be a good idea?

[Rich]

Cecil Westerhof <Ce...@decebal.nl> wrote:
> I have some code to print a SQLite query as a table. Like:
> +------------+-----+
> | 2018-04-27 | 2 |
> | 2018-04-30 | 10 |
> | 2018-05-01 | 14 |
> | 2018-05-02 | 13 |
> | 2018-09-03 | 14 |
> | notUsed | 177 |
> +------------+-----+

>...

You are, somewhat, moving towards recreating the 'report' module from
Tcllib that consumes the 'struct::matrix' data structure from Tcllib
and can generate these types of reports.

You might want to go look at both, as the 'report' module has lots of
fancy stuff already built in.

[Cecil Westerhof]

It is nice to play a little with TCL, but if I am reinventing the
wheel …

I will look into it. Thanks.