Introducing fsql and chart

These are two command-line utilities to help you slice/dice and visualize data on the console. fsql utilizes DBD::CSV and a few other modules to let you perform SQL queries against CSV/TSV/LTSV/JSON/YAML files. chart generates simple ASCII charts. I'll give an example for using these tools.

Viewing monthly CPAN releases activity

I maintain a file called releases.txt in the LTSV format. Whenever I do a release, this file gets updated with a new entry.

Finding out how many releases I've done, or even how many releases for a specific year/month/day, is easy. The good ol' Unix commands like grep and wc suffice:

% wc -l < releases.txt
2746

% grep date:2014-03 releases.txt | wc -l
91

But what about something a little more advanced? What if I want to know which month I've done the most releases? Enter fsql:

% fsql --add-ltsv releases.txt 'SELECT SUBSTR(date,1,7) month,COUNT(*) n FROM releases GROUP BY month ORDER BY n DESC LIMIT 10'
.---------------.
| month       n |
|               |
| 2012-08   212 |
| 2012-07   159 |
| 2013-09   157 |
| 2013-11   140 |
| 2012-03   139 |
| 2013-10   117 |
| 2013-04   106 |
| 2014-04    93 |
| 2014-03    91 |
| 2011-02    90 |
`---------------'

So I did 212 CPAN releases in Aug 2012. And this month is not bad at all, 93 so far and we've still got about 2 weeks to go.

How about creating some charts:

% fsql --add-ltsv releases.txt 'SELECT SUBSTR(date,1,7) month,COUNT(*) n FROM releases GROUP BY month ORDER BY n DESC LIMIT 10' --json | chart -d n -l month
2012-08 :*********************************************  (212)
2012-07 :*************************                      (159)
2013-09 :*************************                      (157)
2013-11 :******************                             (140)
2012-03 :******************                             (139)
2013-10 :**********                                     (117)
2013-04 :******                                         (106)
2014-04 :*                                              (93)
2014-03 :                                               (91)
2011-02 :                                               (90)

Yup, there seems to be a bug in the scales. And I plan to make the charts more beautiful, e.g. with ANSI colors and Unicode characters, which probably means finding or writing a replacement backend for Text::Graph. Stay tuned.

Combined with tools like jq and pretty, I can do a whole lot of slicing and dicing without even going to the editor. Everything on the command-line prompt. Sweet.

Leave a comment

About Steven Haryanto

user-pic A programmer (mostly Perl 5 nowadays). My CPAN ID: SHARYANTO. I'm sedusedan on perlmonks. My twitter is stevenharyanto (but I don't tweet much). Follow me on github: sharyanto.