path: root/support/ratexdb/README

ratexdb Version 0.14
Database Access in LaTeX
Copyright (C) 2007-2010 Robin Höns, Integranova GmbH

#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#
#    For more information see the web pages
#
#    Robin Höns: http://www.hoens.net/robin
#    Integranova and the Programming Machine:
#                http://www.programmiermaschine.de
#                http://www.care-t.com


1. Why ratexdb?

So there I was, wanting to do database reports. MS reporting services
gave me tendovaginitis, so why not use Latex, I thought? Quickly I
found latexdb by Hans-Georg Eßer, and I liked the idea very much.
(http://privat.hgesser.com/software/latexdb/)

However, I needed it under Windows with an ODBC data source, and 
latexdb is written in Python and Bash. My attempts to get it to run
failed, and I decided it would be more fun to rewrite it in Ruby and
include some improvements on the way.


2. I know latexdb, and I'm in a hurry. What are the improvements over
   latexdb?

- You can connect to a wide variety of data sources, as supported by
  the ruby-dbi package; see http://ruby-dbi.rubyforge.org/

- One source code file only, one language, should port easily to other
  systems.

- Latexdb writes a series of output files for nested queries. Ratexdb
  writes one output file only, nested queries are done by recursion.

- Added the \texdbif{##query}{latex stuff} command: Include "latex
  stuff" only if the query returns at least one row. This is useful
  for table headings, which can be omitted if there is no data anyway.

- In database results, Latex special characters (\, _, $, &, #, {, }
  and %) are replaced by appropriate Latex commands.

- Every variable can be included in the Latex source in one of three
  flavours:
  ##varname in Latex source, replaces Latex special characters,
  $$varname in SQL queries, replaces ' by '',
  &&varname in SQL queries, accepts numeric input only.
  The latter two are helpful to protect against SQL injection. The
  $$varname variant is meant for string constants in SQL, the
  &&varname variant for integer constants (like primary keys).

- The database results can be post-processed by regular expression
  search and replace.

- Ratexdb allows command line arguments, which can be referenced in
  the Latex source by variable names ##1, ##2, etc.

- Added the \texdbcommand{database command}, which sends SQL commands
  directly to the database.


3. What do I need?

You just need Latex and Ruby. I used Miktex and Ruby 1.8.6.


4. I am not in a hurry. How do I use it?

It's not difficult. I propose you read the very good tutorial of
latexdb at http://privat.hgesser.com/software/latexdb/ first.
There are only a few differences:

4.1. \texdbconnection

As in Latexdb, this command should not be split among several lines.
The syntax is different, though: It is the same syntax as in the Ruby
DBI package, e.g.:
\texdbconnection{dbi:odbc:mydatasource}
\texdbconnection{dbi:mysql:database,username,password}
\texdbconnection{dbi:sqlite:database}

Where mydatasource is the name of the ODBC data source or database is
the name of the database.
See http://www.kitebird.com/articles/ruby-dbi.html

I only used ODBC. I'd be happy to hear about your experience with
other databases.


4.2. \texdbdef

This command works like in Latexdb. It may be split among more than
one line, with the exception, that a closing brace should be
immediately followed by the opening brace of the following block. So,
no line breaks or other white space between them.

Also, like in Latexdb, variables should be prefix free, to avoid
confusions.

There are some additions:

4.2.1. Reference to variables in SQL

If you reference a previous database result (in nested queries) or a
command line parameter, you should not refer to it as ##var, as in
Latexdb, because then all Latex special characters will be replaced. 
But you are not in Latex, you are in SQL! So, you should write $$var
or &&var, depending on the syntax.

&&var should be used for numerical or GUID values. For example:

select first_name, last_name from person where id_person = &&idperson

The variable may contain an optional sign (+ or -), followed by
hexadecimal digits. If it contains other characters, an error is
issued.

$$var should be used for strings. E.g.:

select first_name, last_name from person where last_name ='$$lastname'

It can contain any value, but the apostroph ' will be replaced by ''. 
So, if your person is from the O';Delete family, coming from the
village of Person;--, it may well be that last_name is "O';Delete from
person;--", which could have disastrous effects when the ' is not
doubled. Search the web for SQL injection for more information.

If possible, you should prefer the &&var syntax, which is probably
safer than $$var.

4.2.2. Regular expression postprocessing

When defining a variable name, you can append a series of
"/regexp/replacement" pairs. These replacements are performed when
inserting the value in the Latex file.

Suppose your table contains a column "sex", which contains a single
"m" or "f". But you'd like to see "male" or "female" in the Latex
result. You can refer to the variable like this:

\texdbdef{##sexample}{select sex from person}{##sex/m/male/f/female}

Another example: Say, the database contains timestamp values, which
are returned in this format: 2007-11-12 10:33:36.807000000

A bit too much information, isn't it? At least, we'd like to cut off
at the point. This is done by defining the variable like this:

##timestamp/(.*)\.(.*)/\1

(To be read: First any string, then a point, then a second string, to
be replaced by the first string only.)

Regular expressions are a very powerful tool. If you don't know them,
look them up on the web.

Since version 0.12, the regular expression replace strings may contain
Latex commands. The special characters in these will not be replaced.
For example:

\texdbdef{##sexample}{select sex from 
person}{##sex/\Am\Z/\textbf{male}/\Af\Z/\textbf{female}}

(Note the \A and \Z which stand for beginning and end of the
string. Without these, "m" would be replaced by "\textbf{male}" and
afterwards by "\textb\textbf{female}{male}"!)

4.3. \texdbfor

This works just like in Latexdb.

4.4. \texdbif

This is a new command that does not exist in Latexdb. It processes a
Latex block if the given query returns at least one row. This is
useful for tabular heads which should not even appear when there is no
data at all.

Here's an example:

\texdbif{##q3}{
\subsection*{File attachments}

\begin{tabular}{lr}
  Name & Size \\
\hline
\texdbfor{##q3}{##att_name & ##att_size \\ }
\end{tabular}
}

4.5. \texdbcommand

This allows to send non-query SQL commands to the database. It is
especially useful for "SET" statements. E.g. if you wish to access a
MySQL database in UTF-8 mode, you can add this line to the TeX file:

\texdbcommand{SET NAMES utf8}

Of course, this command is only allowed after the \texdbconnection,
otherwise an error is issued.


5. Invoking ratexdb.rb

Ratexdb is called like this:

ratexdb.rb [-l|-p] [-d] <texfile.tex> [par1] [par2]...

If you give the -l parameter, the resulting texfile1.tex is processed
with latex, and the resulting texfile1.dvi is renamed as texfile.dvi.

If you give the -p parameter, the resulting texfile1.tex is processed
with pdflatex, and the resulting texfile1.pdf is renamed as
texfile.pdf.

If you give neither of these, only texfile1.tex is generated.

The -d argument turns on a rudimentary debug mode; some debug output
will be written to the file ratexdb.txt.

The source file name should always end with .tex!

Behind the name of the .tex file, you can give optional parameters,
which can be referred to in the tex file as ##1 (or $$1, &&1), ##2
etc. This is useful to generate a report for a special row, the ID of
which can be given on the command line.


6. Contact

Please, if you have a question, find a bug or like to propose an
improvement, contact me! See my web page at http://www.hoens.net/robin
for my mail address. It is really not difficult to guess, just my
first name "@" the domain mentioned above. Sorry for the guessing
game, you know what those spambots are like.


7. Acknowledgements

Many thanks to my employer, Integranova, for making software
development a breeze with the programming machine, and for letting me
publish this script under GPL.

Many thanks to Hans-Georg Eßer for LatexDb.

Many thanks to Oscar van Eijk and his SQLTeX 
http://oveas.com/freeware/overige/sqltex
for inspiring the LaTeX characters change in version 0.13.

Many thanks to François Boone for helpful comments and bugfixes.

8. Release history

2010-09-30 ratexdb 0.14

Added \texdbcommand.
Replaced ftools by FileUtils.
Removed an umlaut which seemed to give trouble.

2010-03-03 ratexdb 0.13

Added "~" to LaTeX special characters, and changed "\" replacement
from "$\backslash$" to "\ensuremath{\backslash}".

After a \texdb... command, the first character after the last closing
} was lost. Fixed.

2008-08-01 ratexdb 0.12

Changed ordering in postprocessing: First replace the special
characters, then the regular expressions. Like this, the regular
expressions can contain LaTeX commands.

Fixed bug in command line arguments parsing.

2008-06-24 ratexdb 0.11

Changed line breaks from "\r\n" to "\n" to make it work on Unix and Mac.

2007-11-29 ratexdb 0.1

Initial release.