Sometimes I need to query a database, using SQL for that, and get the results to process them with OOo Calc o any other data analysis tool (for example GNU Plot).
The most easy way to import data into OOo Calc is using CSV, but the SQL clients I usually have at hand doesn’t support that output format and I always end using some AWK magic to get a correct CSV.
It’s not a big deal, but last week I had a different problem at work: what if you have to do different queries in different databases depending on a main table?
In fact, it was a multi-tenant application with one database for each instance, that can be in any of machine in our backend cluster, so having the list of the databases wouldn’t help.
Due to this I had to execute a query to get the database name and the connection information for each instance, and then run the query I actually needed for my report.
I did the work, and the report it’s being generated automatically as the customer requested, but I wasn’t satisfied with the resulting code (you know what happens when you don’t have enough time to do your work), so I’ve spent some of my spare time programming a tool to make easier this kind of reports: YAML Simple SQL Reports (yes, I know… what a fancy name!).
The idea is to specify the report in a YAML file. For example:
report:
name: first report
connect:
type: mysql
database: dbname
username: user
password: secret
query: select foo, foo2 as whatever from bar
output: file.csv
This will generate a CSV file into file.csv, with foo, whatever header and one line for each row resulting of the query.
At this point, al least, I got rid of the AWK post-processing. But, what about the multi-database problem? Easy peasy with subreports!
report:
name: first report
connect:
type: mysql
database: dbname
username: user
password: secret
query: select dbname, dbhost, dbpasswd, dbuser from masterdb
report:
name: sub report
connect:
type: mysql
dabase: {dbname}
hostname: {dbhost}
username: {dbuser}
password: {dbpasswd}
query: select * from table
output: file.xml
The query in first report is executed, and for each row in the result, the report named sub report is executed (after replacing the {KEY} stuff in each loop with the value from the result row). The result of all the queries in sub report are concatenated and used as output (in this example as XML).
It’s not the ultimate tool to make reports, but it will work for me next time I’m asked to do a report ASAP from any SQL database.
Update: you can get the stable versions from the downloads section of github. PostgreSQL it’s being tested, and will be version 0.3.
