One of the most common questions I receive nowadays is how to make a simple database program in perl. Therefore, I have decided to make a step-by-step tutorial on how to do so.
Before I continue though, the text file containing the full script is available at database.txt
Though it is very possible (and common) to create powerful database programs using SQL, this tutorial will be based on using pipe-delimited flat file databases. What does all this jargon mean? It just simply states that records in the database will be separated by newlines, and each 'column' in each line is separated using a pipe, or the "|" character. These files are just simple text files as well.
What are the benefits of using perl as your database program? It's extremely portable. You can create database programs in any language really, but web based applications are the best. Why? Try running a database program made for Windows on a Unix machine, and you will know what I mean.
Now we have to think about the design of our database. The application I am creating will be customizable, as all fields can be created as needed. These are things you have to think about before creating the program. If you are the only one using the program, and you are likely not to change the available fields, then making it this customizable might be a waste of time.
Each key, or ID number for each database entry has to be unique. There are several ways you could to this, but the most common ways are grabbing the output of time(), which is the timestamp, something that is never repeated, or, having a separate file with the last key number created in it. This textfile will increment on each entry. This is the format we will use.
Ok, time to dive into the code.
First off, we have to specify the fields being used in this database.
%fields = (
ID => [0, 'Link ID:', 'text'],
Name => [1, 'Name:', 'text'],
Email => [2, 'E-Mail Address:', 'text'],
Address => [3, 'Street Address:', 'textarea']
);
This hash now holds the field names, and various specifications about each field. The format is
Field name => [ID, 'Readable Version', 'type of field']
Just a note, only text and textarea are supported for the type of field.
After this is out of the way, several global variables must be declared.
$db_key = 'ID';
This is the Hash ID for your 'key' for the database, I would recommend keeping it in this format.
$delimeter = "|";
The delimeter is what separates all of your record fields, as explained above.
$file = "./file.cgi";
The location of your database file. Preferably I would use a full path, but this is your option, as you are making this database application.
$numberfile = "./num.cgi";
Similar to above, except this variable holds the location of the file holding the last key generated.... very important.
$flock = 1;
What the &*$% is this for? Well, $flock tells the script whether or not you will be using flock when modifying files. Flock is the function perl uses to execute file locking. File locking prevents your files from being corrupted and messed up if many invocations of this script occur at once. If your system doesn't support flock though, like any Windows 95/98 system, then you must set this to 0.
Now its time to do some work to gather the data on the fields specified. The reason for this is so that we can access the field information later on, quickly and easily.
@db_fields = ();
This makes sure the @db_fields array is empty.
foreach $field (sort { $fields{$a}[0] <=> $fields{$b}[0] }
keys %fields) {
$db_id{$field} = $fields{$field}[0];
$db_name{$field} = $fields{$field}[1];
$db_type{$field} = $fields{$field}[2];
push @db_fields, $field;
}
Youch! What was that? The first line of the foreach statement is rather important. It sorts the %fields hash into proper order. Usually the keys %hashname function will spit out the keys in a random order. This line makes sure everything you made appears as you made it. Very important for later on, you will know what I mean.