Acquisitions Database

Developer's Guide

by Chad Wallace, 2003

 

Contents

Introduction

This document is intended to familiarize a developer with the Acquisitions Database application.  It will outline the general architecture of the application as well as some tools that are used by it.

Much knowledge can also be gleaned from examining the source code for examples of the usage of some functions or modules.

Architecture

Since this script is object-oriented, almost all of the code appears in a module file (modules/Acquis/App.pm). The script file (index.plx) that is executed by the web server serves only to call the module, passing it configuration information.

As a web-based application, the Acquisitions Database consists of a number of web pages. Each page is controlled by two things:

  1. The content of the page is provided by a function in the script, which queries the database and compiles the data. Each of these functions appears in its own file in the modules/auto/Acquis/App subdirectory.
  2. The appearance of the page is provided by the HTML template that the function loads. All of these templates appear in the Forms subdirectory.

The main file for the application is App.pm, containing the Acquis::App package. In it are the @menu array that defines the run modes and their handler functions, the setup and teardown routines that are called by CGI::Application, and various general-purpose functions that are used by the run mode handlers. Acquis::App uses the AutoLoader module to load other subs from .al files in the modules/auto/Acquis/App subdirectory.

Directory Hierarchy

The acquis directory contains the following subdirectories:

Subdirectory

Contains

auth

Data files for CGI::Auth

auth/sess

Session files for CGI::Auth

devdocs

Documentation for developers

devdocs/FileStructure

Flowcharts showing the relationships between groups of .al and .html files.

Forms

HTML templates and include files

Forms/style

Style sheet, acquis.css

modules

Modules that are used by the application.

modules/Acquis

Acquis::* modules, including Acquis::App

modules/auto/Acquis/App

Autoloader files for Acquis::App (*.al and autosplit.ix)

util

Useful scripts

Perl Modules

The Acquisitions Database application makes use of a few Perl modules:

In addition to the information given here, the documentation for these modules would be quite useful.  The documentation for a module can generally be found with the 'perldoc' command, for example 'perldoc HTML::Template'.

AutoLoader

For the sake of speed, the AutoLoader module has been used. It looks for subs for Acquis::App inside .al files in the directory auto/Acquis/App that must be somewhere in the Perl include path. These files used to be created using AutoSplit, but to aid development we decided to split the file permanently and work on the .al files separately. To add a new method (sub) to Acquis::App, a .al file must be created with the sub declaration, and a 'stub' must be added to autosplit.ix. These steps are described next.

Creating a .al file for Acquis::App

Each .al file contains one and only one sub. The name of the sub is the same as the name of the file (e.g., sub SupplierList is in SupplierList.al).

Each file should look roughly like this:

package Acquis::App;
use strict;


sub SubName
{
	my $self = shift;


	# Insert code here...
}


1;

Notice that every sub is in the Acquis::App package and is passed the 'self' object reference as its first parameter. In other words, every sub is a method of the Acquis::App class.

Adding a stub to autosplit.ix

So that AutoLoader knows at-a-glance which subs exist, they must all be declared in autosplit.ix. This is done simply by adding a stub (e.g., a line that reads sub SupplierList;) to the file. The stubs are grouped by their functionality, in the same way that the run modes in Acquis/App.pm are grouped.

CGI::Application

AcqApp.pm is an object-oriented package (a class) that uses CGI::Application as its base class. It inherits a lot of behaviour from CGI::Application.

When the script is called, there is a CGI parameter named 'rm' (included on every form or URL that accesses the application), which gives the current run mode.  Each run mode is associated with a specific handler function. This function processes the CGI forms and parameters and generates HTML output that is sent back to the web browser. Essentially, each run mode [almost] corresponds to a single web page.

The functions are all object methods, so they are passed a self reference as their first parameter.  Thus, the first line of every function is usually

my $self = shift;

to retrieve the reference. Then, in the style of object-oriented Perl, other object methods can be accessed using $self, such as 

	$self->AddNotice( error => 'Your shoe is not tied.' );

or

	$self->isValidISBN( '0764533428' );

CGI::Application provides a few object methods of its own:

param - Holds parameters for the application (in lieu of global variables or object properties). The following parameters are used:

Parameters from new (beginning with a dash)

Internal Parameters (without a dash)

query - Returns the CGI object reference for calling its methods. For instance, to fetch a parameter from the submitted form:

	$self->query->param( 'ItemID' );

Here, the query method of $self is called, then the CGI object reference that it returns is invoked to call its param method and fetch the value of the 'ItemID' parameter.

NOTE: The param method of CGI should not be confused with the param method of CGI::Application, discussed above. They are separate functions acting on different objects.

For more information on CGI object methods, see the perl module documentation for CGI.

DBI

In the setup routine, a connection to the database is made, and the resulting database handle is stored in the 'DBH' application parameter. See the perl module documentation for DBI and DBD-mysql to see how this handle can be used to access the database.

HTML::Template

With HTML::Template, the layout details of a form are separated from the gathering of the data for that form. The templates include tags that are replaced with data using the param method.

As mentioned above, each run mode handler function has its own template. It loads the template by creating an HTML::Template object, and then populates it with data that it retrieved from the database.

There is an example template in the Forms directory named Template.html.

CGI::Auth

This module is used in the setup routine, where it is called to ensure that the user is authenticated properly.  It is always executed once each time the script is run.

PreInsert

Pre-inserts a row into a table and also into an 'Invalid' table and returns the ID. This is useful with web applications in the event that the user presses the Refresh button after submitting some data. If the submission caused an insert each time it was submitted, there would be duplicate data. But with pre-insertion, the insert is not duplicated. Instead, a duplicate update is performed, which is harmless.

PreInsert has three main functions:

  1. prepare - Pre-inserts a blank row into the table and returns its primary key (ID).
  2. keep - Finalizes the insert by deleting the 'Invalid' row. Call this after updating the row with data.
  3. cancel - Cancels the insert. Deletes the row and the 'Invalid' row.

Example:

Inserting an Item - First do the pre-insert:

	my $pins = new PreInsert( -dbh => $self->param('DBH'), -table => 'Item' );
	$itemid = $pins->prepare;

and send a form to the user for the data. Then, when the data is submitted and is valid, finalize the row:

	my $pins = new PreInsert( -dbh => $self->param('DBH'), -table => 'Item' );
	$pins->prepared( $itemid );
	$pins->keep;

If something goes wrong, cancel the row instead of keeping it:

	my $pins = new PreInsert( -dbh => $self->param('DBH'), -table => 'Item' );
	$pins->prepared( $itemid );
	$pins->cancel;

NOTE: The item ID that is returned by prepare will have to be passed back and forth between the form and the script by hidden input controls because it is needed by both the keep and cancel methods of PreInsert.

If a row is pre-inserted and then abandoned (without a subsequent call to keep or cancel), the empty row will remain in the database, and so will a corresponding row in the 'Invalid' database. These rows will have to be occasionally cleaned up to avoid unnecessarily bloating the database. The following statements will perform this task:

DELETE FROM Item_t, InvalidItem_t WHERE ItemID IN (SELECT * FROM InvalidItem_t);

TODO: Add a timestamp to 'Invalid' table so that this DELETE can be done on only old rows.

The Acquis::App Package

The Acquis::App package in modules/Acquis/App.pm is the core of Acquis. It defines the menu system, the valid run modes and some often-used functions, and, with the use of AutoLoader, brings together all the subs in .al files.

Defining Menus

The @menus array defines what will appear on the user's menu in the title bar. @menus is an array of hashes, each hash defining a menu with the following properties:

Defining Run Modes

The @runmodes array defines the run modes that are available to the user. It is an array of hashes, each with the following properties:

General-Purpose Functions

App.pm contains a few useful functions that are used by most of the run mode handlers.

PopulateSelect

Parameters: $tmpl, $sel_name, $selected, $datasource

Populates a <SELECT> control inside a template.

LoadTemplate

Parameters: $file

Loads an HTML::Template by filename ($file, relative to the application root), setting the 'tmpl' application parameter. For convenience, the value of the 'tmpl' parameter is returned by LoadTemplate.

LoadTemplate calls SetStdVars on every template it loads. Since SetStdVars reads the script warnings at this point, LoadTemplate should be called near the end of the function.

SetStdVars

Parameters: none

Sets standard variables that should appear in all templates:

  1. Navigation menu
  2. Auth fields
  3. Debug warnings

File

Parameters: $file

Prepends the application root directory to the given filename ($file) and returns the result.

Example:

	$self->File('Forms/index.html')

returns '/usr/local/apache/htdocs/acquis/Forms/index.html'.

ErrorForm

Parameters: $err_msg

Sends an error page to the user with the message given ($err_msg).

isValidISBN

Parameters: $isbn

Checks whether the given ISBN is valid. Returns true if it is, false if not.

This sub appears in isValidISBN.al instead of in App.pm because it is not likely to be used all the time.

Frames and TitleBar.inc

When frames are used, there are a few things to consider:

An example of this is when items are being added to a PO (in the sub ItemOrderAddItems). The main frame is produced by ItemOrderListFrame and the subordinate frame is produced by ItemOrderPreAddFrame and others.

Data Import Script

util/DataImport.pl

This script is used to import data from the .DBF files of the old database into a new MySQL database. Because of the amount of data involved (over 70,000 items and 80,000 orders), it takes between 4 and 8 hours to complete, depending on the speed of the local machine and the connection to the database.

It is run as a command-line application, so it can be run either in a shell on the server or on another machine, as long as the correct database parameters are specified. Before running the script, ensure that the destination database is empty. The best way to do this is to run acquis-new.sql on the database to drop and re-create the tables.

DataImport.pl uses the DBI for the interface to both databases, using DBD::mysql for the new database and DBD::XBase for the old database. The DBD::XBase driver is somewhat limited. It allows only single-table selects, and only a few SQL functions. The script also uses Progress.pm for visual progress indicators because the import process takes so long.

A few parameters can be passed to DataImport.pl on the command line.

  1. Working directory where the .DBF files can be found (default: current directory)

  2. Width of the screen (default: 80)

  3. Database driver to use for destination of import. This is a string containing the driver, host name and database name (e.g., 'DBI:mysql:database=acquis;host=testbed').

  4. Database username

  5. Database password

The database driver, username and password can be coded into DataImport.pl, but they are not by default to avoid accidentally clobbering someone else's database.

File Naming Convention

With so many files in both the Forms and modules/auto/Acquis/App directories, files should be named carefully to ensure proper grouping and to avoid clutter.

All files should be prefixed with a module name, such as 'ItemOrder' or 'Request' so that similar files are grouped together in an alphabetical listing. Subs that perform updates to the database are prefixed with 'DB'.

Since a run-mode handler often uses just one form, it is best if the .html file and the .al file have the same name.

Some files currently do not follow this convention, since they have already been checked into CVS and renaming the files now would mean losing their revision history since we don't have direct access to the CVS repository. SourceForge does allow access to the repository for occasions such as this, but the changes will all have to be done at once.

Printable Reports

Reports should not be simply generated on the screen just like other screens in the program because of two reasons: 1. the titlebar would be shown on the report and would be printed along with it, and 2. the colours used for text in the program show up as greyscales when printed and are hard to read.

Here is an outline of the changes needed to make a report use the "PrintResults" pop-up. This assumes that your report consists of four files with the following functions: PrintXXX.al loads PrintXXX.html to receive the parameters for the report. Then PrintXXXResults.al receives the user input and loads PrintXXXResults.html to print the report. The main difference is to have PrintXXX.html call PrintResults.al so that PrintXXXResults.html will show up in a pop-up window.

PrintXXX.al:

No changes.

PrintXXX.html:

Change the value of the 'rm' hidden input to 'PRN_Results':

Add a hidden input called 'target_rm' with the run mode of your results page (e.g., 'PRN_XXX_Results')

NOTE: If the form has a cancel button, its name MUST be 'cancel'. Its value can be any true value.

PrintXXXResults.al:

Remove references to 'cancel' button because this is taken care of in PrintResults.al (It returns to 'start' runmode with a message 'Cancelled by user').

There is no need to check for the submit button. However, if there are multiple submit buttons (other than 'cancel'), you can check for these and modify the report based on which button was clicked.

Add 'SUBFRAME => 1' to your global template parameters.

Your run mode should not hand off to any others. (i.e., it shouldn't do a 'return $self->Start;' or anything like that). Since the report will show up in a popup, so will any other run mode you call. This makes user input validation a bit tricky because you can't just print a message and return the user to the 'PrintXXX.html' form.

If there are errors in user input (e.g., they didn't enter a PO number), just print a message telling the user what they missed and that they need to close the report window and go back to the data entry form. You could also include a Close button or link that has 'onClick="window.close();"'.

NOTE: This Close button or link should not appear on a successful report because it would be printed along with the rest of the report.

PrintXXXResults.html:

Include 'class="print"' in the <body> tag.

That's it. :-)

Portability Concerns

Perl itself is extremely portable, and the scripts do not do any non-portable things as far as operating systems go. The portability problems mostly involve the database. The DBI goes a long way in making database access more portable, but since different database systems have different SQL syntaxes, some things will need to be changed if the system is migrated to a database other than MySQL.

SQL Syntax

SQL API Portability

Table and Column Names

To avoid problems with table and column names conflicting with SQL and DBMS reserved words, special suffixes are used:

AutoSplit Subroutine Names

Many subroutine names are not unique when truncated to 8 characters. This generates warnings when the file is AutoSplit for the AutoLoader because if the application is run under an operating system, such as MS-DOS, that only allows 8-character filenames, there will be conflicts. However, it is much more important for subroutines to have meaningful, readable names than it is to be able to use the application on such a limited platform.

So don't use this application on a web server running DOS! ;-)

Testbed Host Access

SourceForge CVS Access

We use TortoiseCVS and PuTTY to access the CVS repository for the Acquisitions project. The procedure listed below outlines one way of doing this. You may find a better way, but this is what works for me.

SourceForge SSH and PuTTY Setup

TortoiseCVS Setup

Notes

Miscellaneous notes about the project...

CVS (on SourceForge) [Done]

Facilitates concurrent development by more than one person and revision control.

CVS for new users

AutoLoader without AutoSplit [Done]

To make concurrent development easier, split AcqApp.pm up permanently into the .al files. Then each developer can work on a different .al file (one sub per file), and there's no need to run AutoSplit all the time. Each file in auto/AcqApp can be kept in CVS independently.

We'd have to maintain stubs for all of these subs—should be simple just to edit autosplit.ix.

Valid HTML 4.01!