Volterra Laws Database :: Design and Issues 


Jump back to query page

How this example was done:

Convert the database: The main Volterra database is in Access.  The single “laws” table was exported to a simple text file (looking like ‘“field 1”, “field 2”, …’ ).  This text file was edited in Word using the global search and replace facilities to get rid of the quotation marks and replace the commas between fields with paragraph marks (looking like ‘field 1 <p> field 2 <p> …’). The layout of memo fields was preserved by converting the paragraph marks to "<br>" which displays as a newline in html, if this may be used in the memo text an alternative may be "!br!" which can be converted when the memo field needs to be displayed.

Set up the PERL scripts:  Using the prohosting web site (one of the few free public sites that allow PERL scripts but suffers from forced banner advertising) scripts were designed to open the “laws.txt” file, and run queries input from a web page.  These scripts are more properly known as CGI scripts when running in this way and work by processing files on the host server and then passing back a new static web page to the client browser.  Consequently the user only sees the results of their query rather than having the large main database passed back down to their PC. The scripts and "flat text" form of the database are uploaded to a special cgi-bin directory with permission to execute. These scripts can then be called from any web page, should work in any browser and do not require MS Access or any other application on the user's PC.  The scripts are placed on the UCL servers once final testing is complete and the scripts reviewed for security purposes.

Supporting technology:

1st page used to edit the web pages and perl scripts (something as basic as notepad can be used but with a great deal more pain).  This is available free from http://www.evrsoft.com/1stpage/

Winsock FTP light used to upload the scripts and webpages to the server.  This is also available free from www.ipswitch.com

Outstanding issues.

The true relational abilities of Access would be more complex to reflect in PERL when considering reporting “joins”.

Using PERL scripts rather than asp pages generated by Access gives a long term maintenance problem, firstly the difficulty of maintaining the PERL scripts and secondly the fiddly process of editing the raw data.

Outstanding improvements.

The PERL scripts could be improved to (a) allow filtering against specific fields, (b) sorting results by chosen fields, (c) switchably case sensitive (currently matches are made by comparing everything in lower case) and (d) compound searches.

Memo fields could be improved to show text in italics, bold, colours etc. based on the conventions currently used even though this is not possible to show directly in the original access database (eg. text in square brackets could be shown as italic). Rare errors may occur with the current memo fields where brackets look like html tags (eg. "&alpha" displays α ), "<" and ">" have already been allowed for but further refinements should be based on problems as they arise.

Notes about the design of the application.

Rather than designing a simple hierarchy of web pages to access the data, a more flexible database search was required.  Due to UCL servers not supporting Access over the internet, specific PERL scripts were needed.  These PERL scripts allow the user to query the database and have the results passed back as a simple webpage table of results.

The database has been designed to allow the database text files to be kept in a different area of the UCL servers than the scripts.  This allows the Volterra researchers to update and edit the underlying data as required (for security reasons the scripts must be reviewed by UCL IT services before being released to an area where they have permission to be run as executables).

To avoid maintenance problems there are a minimum number of scripts:

Query script (advanced.cgi) – Imports the names of the databases and looks up the database headers (from a text file) and creates a query page for any database chosen.  This is the main script.

Results script (query.cgi) – Displays the results based on the variables passed from the query (importing the data from the right text file) and gives options to fold columns or go back and amend the query.

Record display script (query1.cgi) – Display one record from the database, formatting in such a way that memo fields are neatly displayed.

Shared modules (volterra.pm) – Any variables and script modules used in more than one place are made available in this script.  In particular the database full names and which fields are memos are declared here (some of these functions may be based on a text file for easy maintenance).