Database Report Generator
A perl script that supports the generation of reports from a database has been installed on our server. To use this script two files must be generated and installed in your public_html directory. The first file is the database itself and the second file is an html file which contains a form which specifies the information to be included in the report. The perl script generates a virtual html file consisting of a table containing the required report. This table is displayed by the client browser.
Details of how to construct the database, how to write the report form and how to generate reports using the form are described below.
Constructing the Database
The database is a simple text file consisting of a sequence of records. Each record is a separate line in this file. These records or lines consist of a sequence of fields separated by tab characters. Each record in the database must have the same number of fields. As an example consider an Oregon Cities database. Such a database might contain the name, population and County for each city in Oregon. The record for Coos Bay might look like Coos Bay\t15065\tCoos where \t represents the tab character. An Oregon Cities database can be examined here.
Writing the Report Form
An example of a form is shown below.
This form corresponds to a database containing records for each member of the Reedsport Chamber of Commerce and can be examined here. This file may be downloaded and used as a template for any report form file.
The database consists of records containing Name, Address, City, Phone, Contact and Category fields. The report form is arranged in a table and contains a row for each field in the corresponding database. Each row is named with the name of the corresponding field. Each row contains three text inputs which are named positionn, patternn, and conditionn where n is the row number. These rows are followed by a sorting input and the two control buttons which are the same for all databases. These are followed by two required hidden inputs and an optional hidden input. The first hidden input specifies a list of headers separated by commas. These headers are used in the generated reports. The second hidden input specifies the full path of the database. The optional hidden input specifies case insensitive pattern matching and/or record numbering and for both options would be entered as:
<INPUT TYPE=hidden NAME=options VALUE="case insensitive, record numbers">
The Oregon Cities database
here has such an entry.
To write a report form for a new database do the following:
- Download an existing example of a form html file.
- Edit the file so that the table contains a row for each field in the database.
- Change the names of each row to correspond to the fields in the database records.
- Number the names of the inputs consecutively (position1, pattern1, condition1; position2, pattern2, condition2 ...)
- Edit the first hidden input to include the desired headers for each field.
- Edit the second hidden input to specify the full path to the database.
- Add the options hidden input if case insensitive pattern matching or record numbering is required.
Database reports are generated by filling in a form and clicking the submit button.
The form controls:
The details follow.
- The selection of fields to be displayed.
- The order of displayed fields.
- Which records are to be displayed using a pattern match filter.
- Which records are to be displayed using a condition filter.
- How the records are to be sorted.
Field Selection and Ordering
Fields are selected for display by entering a number in each position field to be displayed. The numbers entered should be in the range 1 thru n, where n is the number of fields to be displayed. Consider the form above. If you wish to display the Category field followed by the Name field followed by the Address field, then enter a 1 in the Category Position field, a 2 in the Name Position field and a 3 in the Address Position field.
Pattern Match Filtering
Records may be filtered by entering patterns in the appropriate pattern fields. For example, if you wish to display only information about Elkton then enter Elkton in the City Pattern field. For a more powerful example, if you wish to display information about contacts whose last name starts with P then enter \sP\w*$ in the Contact Pattern field. A complete list of the patterns available is shown in the table below.
||Matches any character except newline
||Matches any single character of set
||Matches any single character not in set
||Matches a digit, same as [0-9]
||Matches a non-digit, same as [^0-9]
||Matches an alphanumeric (word) character [a-zA-Z0-9_]
||Matches a non-word character [^a-zA-Z0-9_]
||Matches a whitespace character (space, tab, newline...)
||Matches a non-whitespace character
||Matches a newline
||Matches a return
||Matches a tab
||Matches a formfeed
||Matches a backspace (inside  only)
||Matches a null character
||Also matches a null character because...
||Matches an ASCII character of that octal value
||Matches an ASCII character of that hexadecimal value
||Matches an ASCII control character
||Matches the character itself (\|,\.,\*...)
||Remembers the match for later backreferences
||Matches whatever first of parenthesis matched
||Matches whatever second of parenthesis matched
|| and so on...
||Matches 0 or 1 x's, where x is any of the above
||Matches 0 or more x's
||Matches 1 or more x's
||Matches at least m x's but no more than n
||Matches all of a, b and c in order
||Matches one of fee, fie or foe
||Matches a word boundary (outside  only)
||Matches a non-word boundary
||Anchors match to the beginning of field
||Anchors match to the end of a field
Records may also be filtered using conditions which compare the value of a field with a number. This can only be done for fields which contain numbers. Assume that a data base contains populations of cities, then if you wish to display a list of cities with populations greater than 20000 enter v>20000 in the Population Condition field. If you wish to display a list of cities with populations in the range 10000 to 20000 then enter (v>=10000)&(v<=20000) in the Population Condition field. Conditions are comprised of expressions of the form 'v operator number' where operator is one of <, >, >=, <=, != or ==. These expressions can be combined with either the & (and) or | (or) operators. Such combined expressions require that the sub-expressions be enclosed in parenthesis as in the second example above.
Fields may be sorted by entering one or more sort keys separated by spaces in the Sorting field. Sort keys consist of field numbers followed by a modifier. For example, if you wish to sort first by category and then by name, enter 6 1 in the Sorting field. The field numbers may be followed by the modifiers r (reverse sort) and/or n (numeric sort). For example assume that a cities database contained records consisting of a Name field followed by a Population field, then to generate a report showing the cities in order of their population size, enter 2rn in the Sorting field.
Some additional examples may be examined. These are:
The perl script is
- US Cities
US Metropolitan Areas
- US States
This script was written by Brian Prentice. It is currently considered to be a prototype. Any suggestions for how it might be improved to be more useful should be sent to email@example.com.