Database Report Generator

Introduction

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:

Generating Reports

Overview

Database reports are generated by filling in a form and clicking the submit button.

The form controls:

The details follow.

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.

Regular Expressions

Expression Description
. Matches any character except newline
[a-z0-9] Matches any single character of set
[^a-z0-9] Matches any single character not in set
\d Matches a digit, same as [0-9]
\D Matches a non-digit, same as [^0-9]
\w Matches an alphanumeric (word) character [a-zA-Z0-9_]
\W Matches a non-word character [^a-zA-Z0-9_]
\s Matches a whitespace character (space, tab, newline...)
\S Matches a non-whitespace character
\n Matches a newline
\r Matches a return
\t Matches a tab
\f Matches a formfeed
\b Matches a backspace (inside [] only)
\0 Matches a null character
\000 Also matches a null character because...
\nnn Matches an ASCII character of that octal value
\Xnn Matches an ASCII character of that hexadecimal value
\cX Matches an ASCII control character
\metachar Matches the character itself (\|,\.,\*...)
(abc) Remembers the match for later backreferences
\1 Matches whatever first of parenthesis matched
\2 Matches whatever second of parenthesis matched
\3 and so on...
x? Matches 0 or 1 x's, where x is any of the above
x* Matches 0 or more x's
x+ Matches 1 or more x's
x{m,n} Matches at least m x's but no more than n
abc Matches all of a, b and c in order
fee|fie|foe Matches one of fee, fie or foe
\b Matches a word boundary (outside [] only)
\B Matches a non-word boundary
^ Anchors match to the beginning of field
$ Anchors match to the end of a field

Condition Filtering

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.

Sorting

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.

Status

Some additional examples may be examined. These are:
  1. Oregon Counties
  2. Country Codes
  3. US Cities
  4. US Metropolitan Areas
  5. US States
The perl script is here .

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 bprentice@webenet.net.