Built-in formatters

The formatter is responsible for printing the result sets and/or updates result (count of inserted/updated rows). The formatter can produce output in arbitrary format – text, some markup or even binary data. SQL-DK has several built-in formatters and you can also write your own and plug it in.

Built-in formatters bundled with SQL-DK are:

config name full name description
tabular Tabular Formatter nice tables for humans
xml XML Formatter machine-readable data
xhtml XHTML Formatter webpages
silent Silent Formatter like /dev/null
single Single-value Formatter just concatenated values

You can specify the default formatter in your configuration or ad-hoc on command line:

$ sql-dk --formatter "xml"

Formatters can be parametrized with properties – either in the configuration or ad-hoc by CLI option:

$ sql-dk --formatter-property "trim" "true"

Tabular Formatter

Prints human-readable output – tables of result sets and text messages with update counts.

Tabular can be parametrized:

property default description
color true colorful output
trim false trim long values (will not break tables)
ascii false ASCII nostalgia mode (no unicode table borders)

There are several variants of the Tabular formatter:

Tabular Basic

Basic variant; longer values might break table (overflow the cells).

Tabular Trimming

Basic with option trim=true. Long values are trimmed to the column width and thus will not break the table.

Tabular Wrapping

Longer values are line-wrapped – the cell then contains multiple lines. Marks are added to signalize forced line ends (not present in original data).

Tabular Prefetching

This formatter does not support streaming – whole result set is prefetched and needed space (width) for each column is computed. Table cannot be broken by long values. But consumes more memory for huge result sets.

XML Formatter

Prints machine-readable output – XML document containing resultsets and updates count. Good choice for further processing – e.g. XSL transformation.

XML can be parametrized:

property default description
indent TAB (whitespace) characters used for indentation
indentText true whether text with line breaks should be indented
labeledColumns false whether to add the label attribute to each cell
color true colorful output

n.b. XML parsers will vomit if eat colorful outptut – it is not valid XML and is intended only for humans (review & debug purposes)

XHTML Formatter

Prints result sets and parameters as tables, SQL as preformatted and updates counts as paragraphs. You can pick XHTML fragments (usually tabular data) and use it on your website or use whole output as preview or report.

XHTML can be parametrized in same way as XML

Silent Formatter

Does not print anything. Just like redirecting the output to /dev/null but negligibly faster because no formatting have to be done.

Single-value Formatter

Useful when you need just a single value like sum or count and want to work with it easily in your shell script. This formatter outputs just the value without any formatting.

$ echo "DB says: `sql-dk --db MyDatabase --sql 'SELECT 123' --formatter single`."
DB says: 123.

If there are more values (columns, rows or result sets or updates results) they are simply concatenated.

(La)TeX Formatter

Generates result sets in (La)TeX format. Currently only \begin{tabular}…\end{tabular} tables are supported. Example output:

\usepackage[pdfauthor={https://sql-dk.globalcode.info/}, bookmarks=true,unicode,colorlinks=true,linkcolor=black,urlcolor=blue,citecolor=blue]{hyperref}
\textbf{a} & \textbf{b} \\
x & y \\
z & null \\

Which was generated using JDBC loopback driver:

$ sql-dk --db loopback --sql "…" --formatter tex --data 2 "a" "b" "x" "y" "z"

Using this formatter you can easily get PDF output from your database.

SQL-DK, free software © 2013-2020 GlobalCode