Article

dec2005.tar

Generate Lightweight Web Database Applications Automatically with Formgen

Robert J. Bond 3rd

As a systems administrator, I often have a need for a simple database application accessible through a Web interface. A utility called FormGen allows you to automatically generate such applications quickly and easily. You may need to create a database from scratch, or you may only need a Web interface into an existing database. FormGen handles either scenario with ease.

FormGen is written in Perl and has been tested on Solaris, FreeBSD, and Linux. Out of the box, it uses the MySQL database but is easily modified to work with other Perl-supported SQL databases. FormGen was originally created in 2003 and was designed to automate the creation of very large HTML forms. In 2004, it was extended to output the SQL required to create the supporting table(s), and in 2005, it was extended to output the Perl code required to manage the supporting table(s). In this article, I'll first tell you how to use FormGen then offer additional information that you may find useful.

You can download the latest version of FormGen here:

http://www.everypageinc.com/sysadmin

Overview

Some databases have only one table, while others may have as many as a hundred or more. With FormGen, regardless of the total number of tables, you generate your application one table at a time. For each table, you create one configuration file, with the name <table>.formgen (where <table> is the name of your table -- no spaces). FormGen then reads the configuration file and generates a fully functional Perl CGI script with the ability to browse, edit, add, and delete records.

A typical database consists of several related tables, which means that the value stored in one table is derived from a list of values in another table. FormGen handles related tables through the use of lookups (more on this later).

Getting Started

To generate a Web database application, do the following:

# ./formgen <database> <table>

where <database> is the name of the database, and <table> is the name of your database table. FormGen will look in the current directory for a special configuration file called <table>.formgen. Assuming the configuration file is found, FormGen will create two new files in the current directory:

<table>.pl -- The Perl CGI script to manage your data.

<table>.sql -- A text file containing the SQL to create the table (if necessary).

All we need to do is create the Forgmen configuration file, which is easy.

Creating a Configuration File

If you're eager to get started, simply take a look at the sample configuration files on the Sys Admin Web site (http://www.sysadminmag.com), which are commented. If you want a more in-depth explanation, please read on.

A FormGen configuration file ends with the extension ".formgen". One .formgen file creates a script for one table, so if you have multiple tables (which is likely), create a separate configuration file for each table. The .formgen configuration file is a tab-delimited text file. Comments begin with a # character. Each line provides information for a single column in the table. The information is in the format:

Label <tab> Column <tab> Type <tab> Extra Info

where:

Label is a nice name for the column (spaces allowed).
Column is the actual column name (no spaces).
Type is one of: hidden, text, textarea, select, lookup (explained later).
Extra Info is dependent on the value of Type.

Example: The Tech Table

Let's assume we need a database called "workorders" with a table called "tech", which will store each tech's name, email, and a comment field. Here's the configuration file, tech.formgen:

ID <tab> id <tab> hidden
Name <tab> name <tab> text
Email <tab> email <tab> text

That's it -- just three lines to create a table with three columns. The first column (id) has a type "hidden", which indicates that we don't want the user to change this value (because it's created and managed by the database itself). The second and third columns are of type "text", which means they are simple entry fields. (We'll look at more complex types below.)

If our database doesn't exist yet, we create it. That means going into the MySQL command-line client:

# mysql -u <username> -p

The client prompts us for our password. Now we are at the mysql prompt. We create the database and grant permissions to a user (internal to MySQL, not a Unix user):

mysql> create database workorders;
mysql> grant all on workorders.* to 'workorders' identified by \
  'workorders'
mysql> quit;

By default, we've set the database username and password to the same value -- obviously something you'll want to change!

FormGen generates two files for the "tech" table:

tech.pl -- The Perl CGI script.

tech.sql -- Which can create the new table.

Let's create the new "tech" table:

# mysql -u <username> -p < tech.sql

Next, let's move the tech.pl to a directory that is configured for CGI scripts. We also have to set the execute bit on the script so that Apache will execute it:

# mv tech.pl /usr/local/apache/cgi-bin
# cd /usr/local/apache/cgi-bin
# chmod +x tech.pl

And now we can test it. Try adding records to the table. If you get "Internal Error", perhaps you have not allowed the script to execute. Alternatively, the first line of the script assumes the location of your Perl -- if it's not /usr/bin/perl, edit the first line so that it accurately reflects the location of Perl on your system.

Additional Types

In addition to the two types listed above, FormGen can create the following field types:

"textarea" type for long, scrollable comment fields.
"select" type for simple dropdown values.
"lookup" type for dropdown values in a separate table.

Let's look at each of these in turn.

The type "textarea" is simply a scrollable text input field. Whereas "text" is stored in the database with up to 255 characters, "textarea" is significantly larger.

The type "select" will produce a drop-down containing a list of values (using the HTML select tag). How do you tell FormGen which values to use? Simply add another tab and list them, separated by spaces. For example:

Location <tab> location <tab> select <tab> Springfield Santa_Fe

Convert any spaces within a value to underscores -- the user won't see the underscores.

The type "lookup" is fairly sophisticated. It's just like select, except that it gets its values from another table in the same database. So to look up "location" in the "location" table, using that table's "city" column, we would add this line to the configuration file:

Location <tab> location <tab> lookup <tab> location <tab> city

This presumes the existence of a table named "location", which contains a column "city" (a unique identifier/auto-incrementing key named "id" is also assumed).

While the "lookup" type is somewhat complex compared to the "select" type, it allows you to build multiple FormGen scripts that can serve as the backbone of a very large application with numerous related tables.

Sample Application

The Sys Admin Web site contains all the files related to a simple workorder application. This application tracks techs, locations, and requests.

The application has three tables:

tech -- Stores the list of techs.

location -- Stores the various locations.

request -- Stores each work request, along with location and the tech assigned.

A help desk person enters requests as they come in, assigning the tech and location. Later, the tech accesses the request, marks the request as resolved, and re-submits.

This trivial application is intended only to demonstrate the ease of creating multi-table Web applications with table lookups (request looks up the location and the technician).

Modifying the Code

If you have some Perl experience, you can modify the generated code very easily. If you want to back out of your changes, you can always regenerate the code.

Two modifications most people will want to make are as follows:

1. Database username/password: FormGen connects to the database via the db_connect subroutine, which contains the database username and password, which are both set by default to the database name.

2. If your application has multiple tables, you will probably want to change the links at the top of the script to include links to all your scripts; just edit the HTML in the header subroutine.

You can also modify the internal code templates inside FormGen itself, and your modifications will be reflected in every script you generate. Because FormGen is a Perl script, you simply edit the executable directly in a text editor. If you are really creative, you can get FormGen to output code in other languages by creating the appropriate templates. (See the FormGen code for the list of tokens it uses, which are uppercase strings enclosed in pound signs.)

Caveats

FormGen is intended for generating internal applications used by a trusted audience. Do not put unmodified FormGen scripts on the Internet for the following reason: any Web application that re-displays what the user submits is vulnerable to "cross-site scripting" in which specially crafted HTML and Javascript entered by users can be used to trick browsers into revealing sensitive information. You must strip out such information using regular expressions before re-displaying user-submitted data.

Also, consider modifying the #!/usr/bin/perl -w line at the top of the code. For example, consider removing warnings by removing the -w, which are written to your error_log file. Also consider adding taint checking by adding -T, which ensures that the script will not do anything unsafe with user-supplied data that has not been filtered ("untainted") through regular expressions.

Conclusion

FormGen provides a quick and easy way to automatically generate Web database applications using a simple text configuration file. Multi-table applications can be generated using lookups. By editing the generated Perl scripts, sophisticated custom functionality can be added (but obviously, such changes will not carry over if you re-generate the script). Ambitious users can also readily modify FormGen's internal code templates, to change the code that FormGen generates.

Robert J. Bond 3rd serves as Web/database programmer at College of Santa Fe in New Mexico, where he is also instructor and advisor in computer science. He thanks David Bond of EveryPage, Inc. for two years of support in the development of FormGen.