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 . # # # 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] [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.