diff options
author | Norbert Preining <norbert@preining.info> | 2019-09-02 13:46:59 +0900 |
---|---|---|
committer | Norbert Preining <norbert@preining.info> | 2019-09-02 13:46:59 +0900 |
commit | e0c6872cf40896c7be36b11dcc744620f10adf1d (patch) | |
tree | 60335e10d2f4354b0674ec22d7b53f0f8abee672 /support/nlatexdb/README |
Initial commit
Diffstat (limited to 'support/nlatexdb/README')
-rw-r--r-- | support/nlatexdb/README | 451 |
1 files changed, 451 insertions, 0 deletions
diff --git a/support/nlatexdb/README b/support/nlatexdb/README new file mode 100644 index 0000000000..1bd0071bd2 --- /dev/null +++ b/support/nlatexdb/README @@ -0,0 +1,451 @@ +Nlatexdb Version 0.03 +Database Access in LaTeX +Copyright (C) 2011 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 page http://www.hoens.net/robin + + +1. Why nlatexdb? The history... + +A few years ago, I wanted to do database reports, and MS Reporting +Services appeared rather inconvenient to me. So I figured I'd use the +typesetting power of Latex, and I found latexdb by Hans-Georg Eßer +(http://privat.hgesser.com/software/latexdb/). The idea was great, but +unfortunately I couldn't make it run under Windows, so I wrote a clone +in Ruby, called ratexdb. + +At the time, Ruby had a convenient and powerful database access +package called DBI, so I really didn't have to write much code in +order to support a whole bunch of data sources. Great! In the next few +years, I fixed some bugs, added some nice features at the request of +helpful users (thank you!), -- and then I got a new computer. + +I installed Latex, I installed Ruby, and it turned out DBI wouldn't +work any more. It seems that people have chosen than from now on DBI +shall be deprecated, there is a new thing called RDBI, and everybody +should use that. I tried it, it didn't work. It depended on some +packages, and those packages on some other packages, and I can't +really claim I had a good time installing all these packages. + +So what did I do? Well, I had a good time rewriting the whole thing in +C# -- since my Ruby code was rather ugly anyway, I decided this time +I'd do it a bit more cleanly. Most of the code was written in a +delayed train, so thanks to the Deutsche Bahn for making this program +possible. + + +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 + .Net DbProviderFactories. + +- One executable only, one language. Works under Microsoft .Net 2.0 or + above as well as under Mono. + +- Latexdb writes a series of output files for nested queries. Nlatexdb + 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. + +- If variables are used in nested SQL queries, they are protected + against SQL injection. + +- The database results can be post-processed by regular expression + search and replace. + +- Nlatexdb 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. I know ratexdb. What are the differences? + +- The command \texdbconnection has been replaced by + \texdbconnectionnet{provider}{connectionstring} + where provider is a database provider (e.g. "System.Data.SqlClient" + for MS SQLServer, or "System.Data.OracleClient" for Oracle; calling + "nlatexdb.exe -P" will give you a list of all installed providers) + and connectionstring is the connection string. + +- In ratexdb, variables came in one of three flavours: + ##varname, $$varname, and &&varname. This doesn't exist any more. + Nlatexdb, unlike ratexdb, knows whether we are in Latex or in SQL, + and behaves accordingly. + In fact, query or variable names don't even have to start with "##" + any more (except for the command line variables, which are still + called "##1", "##2" etc.), but it is still recommended to name them + like this, so there is no danger of confusion. + +- The parser has less restrictions (I hope). You can split all + commands among several lines. You should only watch out that if a + command has several blocks of braces, they follow each other + immediately; so please always write "\texdbfor{##query}{Hello + world!}", not "\texdbfor{##query} {Hello world!}". + +- The Latex comment character "%" is honoured more or less correctly. + So you can comment out your \texdb stuff, and nlatexdb should ignore + it. "\%" will be recognized as no comment, but please don't write + "\\%": The comment will not be recognized. Write "\\ %" or "\\%%" or + something like that. That should work. + + +3. What do I need? + +3.1. How do I install nlatexdb? + +You don't. Just copy nlatexdb.exe from the bin\Debug path to wherever +you feel it's convenient. If you want to change the default settings +(see section 6 of this README), copy the nlatexdb.exe.config, too; but +you don't have to. + +3.2. What else is required? + +You need Latex (I used Miktex 2.9) and a .Net runtime version 2.0 or +above. Under Windows, install the .Net runtime if it isn't already +installed, available here: +http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=19 +Under Linux or MacOS, install Mono: http://www.mono-project.com + +For using your favourite database, you might need to install an +appropriate database provider. Mono installs a whole bunch of database +providers. Microsoft .Net has fewer, but usually when installing a +database client, a DbPRovider is installed, too. Maybe you'll have to +add it to your machine.config or to nlatexdb.exe.config: +http://msdn.microsoft.com/en-us/library/dd0w4a2z.aspx + + +4. How do I use it? + +It works almost identical to Latexdb. There is a very good tutorial of +Latexdb at http://privat.hgesser.com/software/latexdb/. + +Here is a step for step description of how to use Nlatexdb. + +4.1. Get the .Net runtime + +Well, if you have Windows, not too old hopefully, you probably already +have it. If you don't, go to Microsoft and get it for free. + +If you have Linux or MacOS, install Mono. + +4.2. Get your DbProvider + +You need an ADO.Net database provider for your database. Usually, if +you install a database, it will be installed automatically. If you use +Mono, a whole bunch of the most important database providers will be +installed by default. + +Call "nlatexdb -P" to see a list of the known providers. Remember the +InvariantName of your provider. + +If you are under Windows and you have never used a command-line tool: +Open a DOS box: Push <Windows-key>+R, type "cmd", and a black window +appears. Now find Nlatexdb.exe in the Windows Explorer, and drag&drop +it to the black window. Its complete path should appear. Add " -P" and +press Enter: The list of DbProviders will be shown. Or, like in the +old DOS days, use the "cd" command to move to the correct directory, +and just type "nlatexdb -P". A third possibility is to create a text +file called "getproviders.bat" with the following two lines: +nlatexdb -P +pause +If you double-click it, the black DOS box with the list of providers +opens for you to read. + +4.3. Create your Latex input + +Just write your normal Latex file with your favourite Latex editor. +There are a few examples in the examples directory. + +If you use a wysiwyg frondend for Latex, it might get confused by the +unknown new commands. Therefore, you might add the following lines to +your file: + +\newcommand{\texdbconnectionnet}[2]{} +\newcommand{\texdbdef}[3]{} +\newcommand{\texdbfor}[2]{#2} +\newcommand{\texdbforfile}[3]{#3} +\newcommand{\texdbif}[2]{#2} +\newcommand{\texdbcommand}[1]{} + +Like this, Latex will ignore all the SQL stuff. + +4.3.1. \texdbconnectionnet + +Now, first you have to define the database connection. The syntax is: + +\texdbconnectionnet{provider}{connectionstring} + +where provider is the database provider you found out in 4.2. +Examples are: +"System.Data.SqlClient" for MS SQLServer +"System.Data.OracleClient" for Oracle +"Mono.Data.SQLite" for SQLite under Mono + +The second pait of braces contains the connection string. This one +depends on the database you use. Search the internet for the correct +connection string of your database. There are whole web pages about +nothing but connection strings! + + +4.3.2. \texdbdef + +Next, you define the SQL queries that you need. This works with the +\texdbdef command. It takes three parameters: +1. The name of the query +2. The SQL select command +3. The variable names for referencing the columns + +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.3.2.1. Reference to variables in SQL + +Just like in latexdb, you can refer to the variables in nested SQL +queries as well. Unlike ratexdb, there are no different flavours to +worry about. The parameters will be added as DbParameters, no SQL +injection danger here. Also, in SQL no Latex special characters or +regex postprocessing will be done; these happen only in the Latex +part. + +4.3.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 "gender", 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 gender from person}{##gender/m/male/f/female} + +Another example: Say, the database contained timestamp values, which +were 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.) + +(Note: In ratexdb the regex syntax was a bit different. E.g. referring +to a matched group was done with "\1", not "$1". Go figure.) + +Regular expressions are a very powerful tool. If you don't know them, +look them up on the web. + +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.3. \texdbfor + +This works just like in Latexdb. + +\texdbfor{queryname}{latex stuff} will connect to the database, +execute the query (previously defined with \texdbdef) and for each +returned row write the latex stuff to the output. + +All variables defined for the query will be replaced by the values +read from the database. They can be used in Latex as well as in nested +SQL queries. + +4.3.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} +} + +If there are no file attachments, the whole subsection will be +omitted. + +4.3.5. \texdbforfile + +Usually, nlatexdb writes only one input file. If you need to produce +multiple output files, the command \texdbforfile is just for you. If +not, disregard this section. + +It takes three parameters: +\texdbforfile{queryname}{filename pattern}{latex stuff} + +The queryname and latex stuff work just like in \texdbfor. The +filename pattern contains the paths for the output files. It should +contain at least one variable from the query to be inserted into the +file name (Otherwise the same file will be overwritten again and +again... not good). + +Note that as opposed to \texdbfor, the "latex stuff" should contain a +complete Latex file, from "\documentclass" till "\end{document}". +Of course, this will definitely confuse any Latex frondend you might +use. If you rely on one of those, I hope you don't need multiple +output files. + +There is a small, clarifying example for this in the "examples" +directory, called testfile.tex. + +This command works fine with the arguments -l and -p for Latex +postprocessing. Each output file will be processed by Latex. But of +course, unlike the normal way, the resulting dvi/pdf won't be renamed +to match the input file name. + +4.3.6. \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 nlatexdb + +nlatexdb is called like this: + +nlatexdb [-l|-p] [-v] [-o filename] [-e encoding] [-h] [-P] + <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. + +If you don't like the name texfile1.tex, you can give a different +output file name using the argument -o <filename>. + +By default, encoding of input and output file is set to CP-1252. You +can change it with the -e argument. E.g. "-e utf-8" sets it to UTF-8. +In this case, don't forget to add "\usepackage[utf8]{inputenc}" to the +input file. + +The -v argument turns on a rudimentary debug mode; some debug output +will be written to the standard console. + +Behind the name of the .tex file, you can give optional parameters, +which can be referred to in the tex file as ##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. Of course only up to nine parameters are +possible, since the parameter names must be prefix-free. + +6. Configuration + +You can use the nlatexdb.exe.config file to change a few settings. +Currently there are the following possibilities: + +The attribute "CmdLineArgumentVarPrefix" defines the prefix for +referencing the command-line arguments in the tex file. E.g. is you +set CmdLineArgumentVarPrefix="??", they will be referenced by ??1, +??2, etc. + +The attribute "RegexSplitter" defines the character to separate the +variable names and the Regex search/replace pairs. By default this is +the character "/", but if you need it in one of your regular +expressions, you can change the splitter to something you don't need. + +Similarly, the variables in the \texdbdef variable list are separated +by commas, but you can change that, too. Just change the attribute +"VariableSplitter" in nlatexdb.exe.config, if you need the comma in +one of your regular expressions. + +But please, watch out you don't wreak havoc with these settings. If +you make them too crazy, you might not understand anything any more. + +The <LatexCharReplace> section allows to add more characters to be +replaced by Latex commands. Thus, if the database contains characters +which will break Latex, you can add them here, so they will be +replaced by appropriate commands, like this: +<LatexCharReplace> +<add char="¤" replace="\euro{}" /> +<add char="ß" replace="\ss{}" /> +</LatexCharReplace> + + +7. Contact + +Please, if you have a question, find a bug or like to propose an +improvement, contact me at robin@hoens.net. + + +8. Acknowledgements + +Many thanks to Hans-Georg Eßer for LatexDb. + +9. Release history + +2011-08-16 nlatexdb 0.03 + +Added variables RegexSplitter and VariableSplitter. + +Added command \texdbforfile. + +More debug output. + + +2011-08-16 nlatexdb 0.02 + +Compiled under Mono for .Net 2.0. Should be more platform-independent. + +Added exe.config file for some configuration possibilities. + +Renamed from natexdb to nlatexdb (turns out there is somebody calling +himself natexdb... no need to create name clashes) + + +2011-08-05 natexdb 0.01 + +Initial release. |