How about a super-simple tool for making, pretty-printed PDF research reports from your usual mess of intricate SQL queries?

There are times when you have to let the rest of your news room into what your’re up to, spending hours and days drilling into large data sets pulled and tweaked by complex SQL queries.

Reporting preliminary research results in table form can be a daunting task, specially when you’re trying to make sense of what your SQL queries actually does. You don’t want your editor to set the story off in the wrong direction just because you forgot to point out some crucial piece of information in the table description.

Should have put some comments in that SQL file, huh? Well, that’s what the mkres utility makes you do: Write comments above your SQL statements, explaining exactly what went through your head while writing the query behind a specific table. Your comments will the be nicely embedded into a human-readable PDF report.

PDF report from SQL script

In an effort to reduce the time spent on creating long paste-up’s of ASCII-art tables from mysql’s command-line client, i wrote a simple script that would generate a legible PDF document directly from the SQL script, using the content in the comment blocks as the source for structure and textual content.

In fact, mkres needs those comments to work well, because they will be nicely displayed alongside your output tables when redering the PDF document from your SQL file.

It sometimes happens that such trivial problem-solving scripts grow into full-blown GitHub projects. Not quite so yet, but I do believe mkres might come in handy for anyone doing quick-and-dirty database research with SQL scripts. So, I’ve published the bash script for you to try it out.

Basically, after installing, all you have to do is to run mkres from the directory where you put your SQL file. The file title (without .sql extension) is all you have to specify. For example, if your SQL script is /path/to/sqlscript_dir/report.sql:

# cd /path/to/sqlscript_dir
# mkres report

Mkres will probably fail at first pass, because it doesn’t know your MySQL login. However, a configuration file is created and placed in the same folder as your SQL file. It will be named [your_sql_file_title].conf. Simply edit this file and include MySQL server name/IP address and your login before running mkres again. Also, the document language, title and author is specified here:
DOCUMENT_LANGUAGE="english"
DOCUMENT_TITLE="My research report"
DOCUMENT_AUTHOR="Espen Andersen"
MYSQL_HOST="mysql"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASS="secret"

How it works
It’s all based on good old LaTEX, a widely used document preparation system. You will have to learn some basic LaTEX tags, but that shouldn’t intimidate anyone familiar with scripting and query languages in general.
Simply write your LaTEX code in a comment block above the query, along with section and subsection titles. The LaTEX block must be wrapped in html-like <LATEX> tags, like this:
/***************************************
  &amp;amp;lt;LATEX&amp;amp;gt;
  \section{This is a main section}
  Here's the main secion text, you
  don't need any special codes for
  this part.
  &amp;amp;lt;/LATEX&amp;amp;gt;
***************************************/
SELECT
  name,
  id,
  birth_date
FROM
  users; /* &amp;amp;lt;COLS&amp;amp;gt;Xrr&amp;amp;lt;/COLS&amp;amp;gt; */

There are some basic rules to follow:

One comment block per SELECT query, should be embraced by /******* and ********/ style lines.
Only one <LATEX> tag per comment block. Everyting that should be included in the PDF goes there. Other comments are put outside this element.
You may have several queries after a comment block, but only one of them is allowed to output a result set (the table contents).
Include a <COLS> tag (see below) for each SELECT query to have your table correctly formatted.
Column headings (field name aliases) should not contain anything but ASCII characters and numbers. No symbols, no white spaces. No nordic characters.

The <COLS> element
You must specify the extent and horizontal alignment of each table cell by including a simple tag right after your SELECT query:
SELECT
  name,
  id,
  birth_date
FROM
  users; /* &amp;amp;lt;COLS&amp;amp;gt;Xrr&amp;amp;lt;/COLS&amp;amp;gt; */

This element consists of one letter for each column in the output table, specifying how LaTEX should render that column. You will get an error if the <COLS> element is missing, or there’s a mismatch between the number of columns returned by your query and the number of letters in the tag. Each letter can be one of the following:

X Content is left-aligned, and the width of the column is expanded so that the table fits the page width. One of the columns (usually the first one) should be formatted with an X.
l Content is left-aligned.
c Content is centered.
r Content is right-aligned.

Wrapped up in a container
I’m not a bash programmer. Gotta be lots of WTF’s in the code, so feel free to submit your pull requests! The script is published under GPLv3.
The script works on Linux systems, but it has lots of dependencies. Running it in my espena/latex Docker container should be the preferred way.
The Dockerfile for the image is found here, if you’d rather build it yourself or modify it in any way.
The following debian packages are required to make mkres work:

texlive
texlive-fonts-extra
texlive-lang-[your preferred language]
pcregrep
mysql-client