Back to files

FileExtender - Version 0.2 (2001-11-14)

Installation

Requirements

UN*X/Win32:

• Perl 5
• DBI and a DBI compatible database driver module (DBD) installed (see DBI installation)

DBI installation:

        UNX: 
            - on UNX systems it should be enough the call

              # perl -MCPAN -e 'install Bundle::DBI'
            
              which will use the CPAN module to receive a copy of the DBI
              module and installs it on your system. You will need a
              C-Compiler to compile it for your system.
            
            - to connect to a specific database system you might also need
              the specific DBI driver for the database systen you want to
              use. See the CPAN archive for more details
              (http://www.cpan.org/). 
              
              Example:
              
              For a DBI driver for the MySQL database system you need
              the MySQL-header files and the MySQL client library on your
              system. To build the driver just run

              # perl -MCPAN -e 'install DBD::mysql'

        Win32:
            - on Win32 systems using the ActiveState Perl build you can use
              the Perl Package Manager to install DBI. Just open the
              PPM using the Start-menu entry and run the following command

              ppm> install DBI

              and the current DBI build for you platform will be installed
              on the system.
            
            - if you want to use an ODBC data source instead of a direct
              database connection you might also have to install the ODBC
              driver for DBI DBD-ODBC. Just run the following command in
              the Perl Package Manager to install a binary version of the
              driver on you Win32 system:

              ppm> install DBD-ODBC

Unpacking

UN*X:

• unpack the compressed package FileExtender-0.2.tar.gz in your favorit location using:

  # tar xfz FileExtender-0.2.tar.gz

• the FileExtender script is now located in

  /you/favorit/location/FileExtender-0.2/FileExtender.pl

• you also might change the path to the Perl interpreter at the beginning of the script file. The default is /usr/bin/perl
• you can also copy the script file to /usr/bin or create a link from an other location to the script file
• the script does NOT use any external files which have to be in a special location

  Win32

• unpack the compressed ZIP package FileExtender-0.2.zip using WinZIP (http://www.winzip.com/) or WinRAR (http://www.rarsoft.com/) to your favorit directory.
• after that the script file is located in

  c:\Your\Favorit\Directory\FileExtender-0.2\FileExtender.pl

• if you have enabled Perl file extension association you also might want to put this path into you PATH environment variable to call the script without the whole absolute path

Usage

How to run the script:

On UN*X and Windows systems you can call the FileExtender script in three ways:

1. If you set the mode of the script on UN*X systems to executable or if you have enabled the Perl file extension association on Win32 systems you can call the script from the command line with its full path like:

   # /opt/FileExtender-0.2/FileExtender.pl

or

C:\>c:\Programms\FileExtender-0.2\FileExtender.pl

        2. If this works you can also put the path to the script file in
           your search path environment variable and you are able to call
           the script without the whole path name:
        
           # FileExtender.pl
           
           or 

           C:\>FileExtender.pl

        3. If neither 1 nor 2 are working you will need to call the script
           with directly with the Perl interpreter. To do so call:
           
           # perl /opt/FileExtender-0.2/FileExtender.pl
           
           or

           C:\>perl c:\Programms\FileExtender-0.2\FileExtender.pl

Command line options:

There are some command line options you need to run the script:

        FileExtender.pl [-help] [-v]
                        [-u username [-p password]] [-o outfile] 
                        -s datasource -i infile

            -s datasource
                
                (REQUIRED)
                The DBI connect string to connect to the appropriate DBI
                driver an the database. You might need to quote it on some
                command line interpreters/shells. Example: DBI:ODBC:DSN
            
            -i infile

                (REQUIRED)
                The template file which should be processed, it can be also
                a batch file containing several ``*createfile''
                instructions.

            -o outfile

                (OPTIONAL)
                The file to which the processing output of the template
                file should be written. (default is STDOUT: ``-'')

            -u username

                (OPTIONAL)
                The username which should be used to connect to the
                database system.

            -p password

                (OPTIONAL only in combination with username)
                The password which should be used to connect to the
                database system in addition to the username.

            -v
           
                (OPTIONAL)
                Verbose mode prints out additional information to STDERR.
           
            -help
        
                Prints additional information on command line options.

Template File Elements

Variable

A variable is a name for a value. Variables can be defined by a database query in a Query-Block where each column of a result row can be referenced by a variable or they can be defined in the extension part of a Query-Block. In general a variable is referenced using a format like ${variablename} where ``variablename'' is a case insesitive name for the variable. Variables are valid in the whole block where they are defined also in nested blocks. They can be also used in conditional block or in SQL statements. There are some standard variables defined which are globaly available. These are:

        ${DATE_NOW} - the current date in format yyyy-mm-dd
        ${TIME_NOW} - the current time in format hh:mm:ss

Query-Block:

A Query-Block is used to perform database queries and to produce an output for each result of the query. It is introduced by a line just containing the keyword STARTBLOCK. The Query-Block is divided into three parts each one separated by the keyword SEP in a separate line. The first part is the SQL query string. It should contain a valid SQL statement which will be executed with the selected database system. The SQL statement can contain references to defined variables which will be evaluated before the statement will be executed. So it is possible to use SQL queries which are depending on the values of variables.
Example:

        STARTBLOCK
         SELECT surname, firstname, uid as userid, home FROM usertable
                WHERE username='${USERNAME}'
        SEP

The second part can be used to define new variables or add variable depending functions. It is possible to assign the value of a variable to an other variable. You can also extend the value of a variable by just adding additional information to the value.
Example:

        SEP
         varname2 = ${varname1}
         fullname = ${firstname} ${surname}
         profile = ${home}/.profile
        SEP

Currently there is one variable depending function defined. It is used to substitute a pattern in the value of a variable by an other value. It is using Perl regular expressions and the same syntax like the substitution function of Perl (s///). The function will be evaluated for each usage of the variable.
Example:

        SEP
         shortname = ${fullname}
         shortname =~ s/()*([a-zA-Z])[a-z]* /$1$2\. /g
        SEP
        
        Description: all firstnames will be abriviated with their starting
                     letters.

The third part is the template area which produces the output for each result of the database query. This part can also contain nested Query-Blocks or Condition-Blocks. All defined variables will be evaluated in this part after all nested blocks were executed. The defined variables are also valid in the nested blocks but can be overwritten. Not defined variables will be evalueted as empty string so they produce no output. The third part of a Query-Block always ends with the keyword ENDBLOCK which again stands in a separate line.
Example for a third part:

        SEP
         <tr>
          <td>Name: ${shortname}</td>
          <td>User ID: ${userid}</td>
          <td>Home: ${home}</td>
          <td>Groups:
           <ul>
            STARTBLOCK
             SELECT DESTINCT name FROM group WHERE uid = ${userid}
            SEP
            SEP
            <li>${name}</li>
            ENDBLOCK
           </ul>
          </td>
         </tr>
        ENDBLOCK

Each Query-Block defines a counter which counts the results of the query. The variable name of the counter for the top level Query-Block is ${COUNTER_1} for a Query-Block in a second level is ${COUNTER_2} and so on. A counter starts with 1 for the first row of the query result in the appropriate block and will increased for each following row. You can use the counters to number you rows.
Example:

        STARTBLOCK
         SELECT name FROM user
        SEP
        SEP
         STARTBLOCK
          SELECT name FROM user
         SEP
         SEP
          Row Level 1: ${COUNTER_1} Row Level 2: ${COUNTER_2}
         ENDBLOCK
        ENDBLOCK

Conditional-Block:

A Conditional-Block is introduced by a line with a format like ``IF cond THEN'' where ``cond'' is Perl conditional expression. Variables used in the condition will be evaluated before the conditional expression will be evaluated. All values except whole numbers will be automatically setted between quotes and handled as strings. If you want to deal with numbers also as strings you need to put the variable between quotes by yourself like ("${COUNTER_1}"). If the expression is true the part between the IF ... THEN line and the ELSE line will be processed otherwise the part between the ELSE line and the ENDIF line will be processed. Both of these parts can contain nested Query- or Conditional-Blocks again or output data.
Example:

        STARBLOCK
         SELECT name FROM user
        SEP
        SEP
         IF ${NAME} eq "Smith" THEN
         ELSE
          ${NAME} is not Smith.
         ENDIF
         IF ${COUNTER_1} < 10 THEN
          The counter is lower than 10.
         ELSE
          The counter is higher than 10.
         ENDIF
        ENDBLOCK

Other Functions:

Currently there are two functions defined you can call everywhere you would put output data. These functions are functions to process external template files and they are called ``createfile'' and ``includefile''. ``includefile'' processes a template file with the currently known variables and includes the result in the current template at the position where the function was called. ``createfile'' does the same but write the result to a new file with the specified name. Variables used in the function call are evaluated before the function will be called. So it is possible to create files depending on the database content. The templates itself can also contain any kind of block again and they are full featured templates. Functions always occur in a separate line with a leading asterix directly in front of the function name. The function arguments are separated by colons. The detailed syntax for the both implemented functions is:

        includefile templatefilename
        createfile templatefilename, outputfilename

Example

It will create a file for each row of the query result.

basetemplate.txt:

STARTBLOCK

SELECT Firstname, Surname, Birthday FROM Person

        SEP
         filename = ${COUNTER_1}_${SURNAME}.txt
        SEP
         Create file ${FILENAME}:
         createfile template.txt, ${FILENAME}
         The file contains:
         includefile template.txt
        ENDBLOCK

template.txt

${FIRSTNAME}, ${SURNAME}, ${BIRTHDAY}

REMARKS

For backward compatibility the top level of Query-Blocks of each template file can start, be separated and end with the escape sequences specified in the variable $ESC at the begin of the FileExtender script file. To adjust it to you convenience please use Perl regular expressions. The current escape sequences are ##, <!--, -->.A

Version and Testing

The original version 0.1 (2001-06-11) was written for Win32 using ActiveState Perl and the Win32::ODBC Perl module.

This version 0.2 is an extension of version 0.1 and was developed on Debian GNU/Linux using Perl 5.6.1 and the DBI Perl module.

The software was tested on Linux using a DBI MySQL driver to access a MySQL database and it was also tested with ActiveState Perl 5.6.1 build 630 on Microsoft Windows 2000 using the DBI ODBC driver to access a MS Access and a MySQL database.

Bugs and Suggestions

Please send any bug reports or suggestions to Oliver Baltzer <ob@racon.net>. Most bugs will be fixed in the next release 0.3 or higher.

Known bugs:

• While processing an external template the counter variables will be not resetted so that the counter level in the external template starts with the counter level of the block which invokes the template processing.

License

Copyright (C) 2001 Oliver Baltzer

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 2 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, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.