Chapter 15

Managing a Database


CONTENTS

Of the various Web site add-ons discussed within the chapters of this book, many rely on the ability to store, access, and modify information that remains in a central repository on the server-a database. Whether you're dealing with customer orders, address lists, advertiser indexes, or a collection of your favorite Cajun recipes, you're going to need to understand how to create and manipulate databases.

Server-Side Databases

The simplest database structure is that of a "flat file," which is nothing more than a plain ASCII (text) file. The trick with flat file databases is in defining how the individual fields and records are identified. For flat files, this is commonly done by:

Declaring each record to occupy one physical line of the file. This means the "newline" character serves as the delimiter between records.
Defining a character to serve as a "field delimiter" to separate individual fields within a record. The field delimiter must be a character that doesn't exist within the actual data elements themselves.

TIP
For several excellent examples complete with source code of server-side (Perl-based) databases, stop by "Matt's Script Archive" at http://www.worldwidemart.com/scripts/ or "Selena Sol's Public Domain CGI Script Archive and Resource Library" at http://www.eff.org/~erict/Scripts/. Both sites also have many links to other sites that implement the various scripts in different ways.

For example, assuming that the | character is used as a delimiter, a sample database would look something like:

Record1Field1|Record1Field2|...|Record1FieldM
Record2Field1|Record2Field2|...|Record2FieldM
...
RecordNField1|RecordNField2|...|RecordNFieldM

This assumes that each record within the database will have M fields. In the event that a particular field doesn't exist for a record, meaning that field would be empty, it still must exist within the database structure as two delimiters with nothing between them, as in:

Field1||Field3

which indicates that Field2 is empty.

To load a flat file database into Perl for processing, you parse it just like any other input file and store the field information in a collection of lists. A Perl fragment that demonstrates how to load such a database into three Perl arrays is shown in listing 15.1.


Listing 15.1  Parsing the Database
open (DATABASE, 'databasefile');

while (<DATABASE>) {
   ($field1, $field2, field3) = split (/\|/, $_);
   chop $field3;
   push (@list1, $field1);
   push (@list2, $field2);
   push (@list3, $field3);
}

close (DATABASE);

The chop statement cuts off the tail end of the last field, eliminating the newline character (\n) that Perl treats as part of the input stream. This is because it's actually stored within the file.

Once the while loop finishes, the internal lists hold all the data from the database. At that point, accessing a particular record is done by indexing the respective fields.

TIP
Remember, Perl can look at file handles much the same way it looks at Boolean values. The statement:
while (<DATABASE>)
is the same as saying, "while <DATABASE> isn't empty or at the end of the file."
This trick isn't limited to while loops. Any Perl statement that requires a Boolean check of a file handle can use it. For example:
if (<DATABASE>) {
# There's something to process
} else {
# Either the file's empty, not opened, or
# the pointer is at the end of the file
}

Client-Side Databases

Depending on the size of your database, it may be more practical to incorporate it directly into the HTML documents through a form. This brings the data to the user's local drive and greatly speeds up any accessing/processing that is performed.

There are several things to remember when planning a client-side database:

  1. This trick is limited to browsers that support client scripting. Depending on your target audience, this may not be a practical method. It would not be a good method, for example, if the bulk of your visitors surf with Mosaic or Lynx.
  2. Unless you submit the form containing the database to a server-side application, any changes made to its data will exist only for as long as the user has your page(s) loaded.
  3. The database itself must be relatively small. The actual size depends on the kind and quantity of the data. Remember that the entire database transfers from the server to the user's browser when your page loads.

While these may seem to limit the usefulness of a client-side database, there are many valid applications, such as:

Lists of favorite links for creating a customized "go to" form.
Small product catalogs with only a handful of items.
Advertiser information to dynamically change ads on your page. By having the database local, the browser doesn't have to reload new graphics from the server.

In other words, whenever you wish to work quickly from the browser's point of view with a collection of data, it may be worthwhile to construct a client-side database and let the browser do the bulk of the data crunching.

TIP
An excellent source of scripts and examples that demonstrate client-side databases is "Cut-N-Paste JavaScript" at http://www.infohiway.com/javascript/.

A Form-Based Database

The hidden object of an HTML form is a powerful little field. With it, you can pass information to a server-side program, such as predefined values for a script, or you can store a large quantity of data as a pseudo-database for manipulation within the client. Because hidden fields never display to the user, they make great "pigeon holes," i.e., small storage spaces, for databases.

As far as the structure goes, the data stored within a form's hidden field can be in the same format as that stored within a server-side flat-file, with one little addition-the definition of a second delimiter to identify the end of a data record. For example, a server's database might look like:

R1F1|R1F2|...|R1FM
R2F1|R2F2|...|R2FM

If you use the asterisk (*) as a record delimiter, the structure would look like:

R1F1|R1F2|...|R1FM*R2F1|R2F2|...|R2FM

that could then be stored in a form within a hidden field as the field's VALUE attribute, as shown in listing 15.2. For example, if you wanted to create a database of your favorite URLs, you would create something like this:

Vector|www.visi.com*LANscape|www.lanscape.com*Mello Smello|www.mellosmello.com

This specifies a database of three records, each record having two fields: one being the name of the site and the second its URL.


Listing 15.2  A Form-Based Database
<FORM ...>
   <INPUT TYPE=HIDDEN NAME="DB" 
          VALUE="R1F1|R1F2|...|R1FM*R2F1|R2F2|...|R2FM">
</FORM>

Loading the Database

Once the data has been defined within the form, it still needs to be in a format that can be managed. This is a job best left to JavaScript, which can easily convert the field's data into an internal array. This process happens in two phases:

  1. The form field divides into individual records.
  2. The records divide into fields.

Both phases perform the same operation-they search through the field data looking for delimiters. The only difference lies in which delimiter is being looked for-a record or a field delimiter.

To divide the field data into records, a code fragment similar to that shown in listing 15.3 is used.


Listing 15.3  Creating Records from a Form Field
strDB  = new MakeArray();
formDB = Forms[0].DB.value;
index  = 0;

while(true) {
   var pos = formDB.indexOf("*");

   if(pos == -1) {
      // no more records
      if(formDB.length) {
         strDB[index] = formDB;
      }

      break;
   }

   strDB[index++] = formDB.substring(0, pos);

   formDB = formDB.substring(pos + 1, formDB.length);
}

The indexOf() method returns the first occurrence of the given string. In this case, it is the record delimiter. If a delimiter can't be found, and the end of the database string has been reached, the code checks to see if there's a delimiter after the last record.

If a delimiter is found, its position is used by the substring() method to pull the characters from the beginning of the string up to, but not including, the delimiter and store this new substring in the next index in the database array. Once stored, substring() is used again, this time to retrieve the rest of the database, starting after the delimiter and continuing to the end of the database string. This new substring becomes the new database string and the process continues.

NOTE
JavaScript arrays are dynamically sizable-you can make them larger simply by referencing an index outside the array's previous dimensions.
There are limits to this, though. While you can make an array larger, you cannot make it smaller.

With the field data divided into individual records, breaking it into fields is done much the same way. The only difference is that each database record has more than one field, so an array of user-defined objects is needed to store each record.

NOTE
User-defined objects were first introduced back in chapter 8, "Advertising with Billboards."

Start with defining the object's properties (listing 15.4), and then use a while loop similar to that in listing 15.3 to load the database.


Listing 15.4  Defining a User Object
function UserObject(strField1, strField2) {
   this.Field1 = strField1;
   this.Field2 = strField2;
   return this;
}

Generic User Objects

An interesting, and more flexible, alternative to having to create custom object definition functions each time you create a new object type is to take advantage of the JavaScript eval() method. The eval() method takes one parameter, a string, and tries to interpret it as a JavaScript statement. Because the parameter is a string, you can build a statement dynamically within JavaScript, such as:

eval("this." + strFieldName + " = strFieldValue");

If strFieldName contained Name, it would be interpreted by JavaScript as:

this.Name = strFieldValue;

where strFieldValue is a variable in itself, containing the value to store in the field.

NOTE
The property name, indicated by strFieldName, needs to be outside of quotation marks so that it can be processed properly by the JavaScript string handler. The variable name for the field's value, however, needs to be within quotation marks, so it doesn't get evaluated until the entire string is handed off to JavaScript for interpretation.

Different types of objects will probably have different numbers of fields, so there needs to be a way to handle a variable number of parameters within the object creation function. JavaScript provides the arguments property to allow this. Every function you create in JavaScript has an arguments property, an array of the parameters that are passed to the function. As with other JavaScript arrays, arguments has a length property (the number of parameters) and is indexed to get the value of each parameter.

The code shown in listing 15.5 uses arguments and eval() to extend the ability of the object creation function to handle objects of different types.


Listing 15.5  Variable-Type Object Creation
function UserObject() {
   argc = UserObject.arguments.length;
   argv = UserObject.arguments;

   for(var i=0; i<argc; i+=2) {
      var strNewValue = argv[i+1];
      var strNewField = "this." + argv[i] 
                      + " = strNewValue;";

      eval(strNewField);
   }

   return this;
}

Parameters are passed to the UserObject() method in pairs: the name of the property and the value it contains. The Billboard() object from chapter 8, for example, could be rewritten using the UserObject() function like this:

function Billboard(strImageURL, strLinkURL) {
   return UserObject("imageURL", strImageURL,
                     "linkURL", strLinkURL);
}

At first glance, this trick may not appear to be useful. Suppose your database is modified so that it contains field names as well as field data. You could then load the information, including specific property names. You can then use them within a customized search form that modifies its behavior based on the fields in the database.

A real-world example of this little trick is shown in chapter 18, "Online Store Product Searches."

From Here…

This chapter introduces several tricks for manipulating databases from the server (through Perl) and the browser (through JavaScript). Many of these methods are the basis for techniques used by other chapters in this book. To see examples of these tricks in action, check out: