R E L A T I O N A L D A T A B A S E S
2 Aug 2000
JPDeni
This modification is different from my others. It is much longer and
there is no code for you to copy and paste into your own script. It is
an explanation of the concepts involved in making DBMan act as though
it was a relational database. There is code here, but it is only to be
used as a pattern. You will have to adjust all the code to match your
own databases.
Be very clear. DBMan is *not* a relational database script like, for
example, MSAccess is. It is a flat-file database. Any relationships
between records must be coded in separately from the script.
One other thing before we get started. This modification is not
recommended for beginners to attempt. It is complex and you need to
have at least some understanding of how DBMan works. It is even better
if you have experience with other database programs and, the best of
all possible worlds would be if you have at least a nodding
acquaintance with the Perl programming language.
The first thing to talk about is what a relational database is. In a
flat-file database, each record has a key value (in DBMan, this is
the $db_key field) and the information can be accessed by referring
to that key. In a relational database, there are two tables, each one
with a field that is common between the two. Possibly an illustration
will help to explain.
Let's say I want to have a relational database manage an online
classified ads service. I could set it up as a flat-file database,
with the following fields:
ItemID -- key field
UserID
UserFirstName
UserLastName
UserEmail
UserCity
UserState
UserCountry
Item
Classification
Price
Description
DateAdded
That would give me close to what I want, but not exactly. There are
several problems with this setup:
1 -- The user would have to enter her information with each item that
she adds to the database, resulting in a greater chance of typing
errors and a .db file that duplicates data unnecessarily.
2 -- If the user changes her email address or moves to a new city, she
must go through each of her items and change them all.
3 -- In order to get a list of all the user currently has in the
database, the user information will be duplicated, making for a
less-than-attractive display of search results.
To overcome this, I would use two .db files, as follows:
Item database
UserID -- common field between the two databases
ItemID -- key field
Item
Classification
Price
Description
DateAdded
User database
UserID -- key field and common field between the two databases
UserFirstName
UserLastName
UserEmail
UserCity
UserState
UserCountry
Note:
1 -- This only will work for a "one-to-many" relationship. Each user
can have many items, but each item is related to only one user.
2 -- The common field between the databases must be the key field
from the "one" side of the relationship.
3 -- I have put the UserID field in the same position in both of the
databases.
4 -- The only field that has the same name in the two databases is
UserID. This is important. Do not have any fields with the same
name in your two databases except for the common field. The
common field *must* have the same name.
5 -- It is easiest to set up a relational database where the users
enter their records, since the userid is automatically added to
all records.
Now, when the user needs to change some of her information, she need
only change it once. The user's information is not duplicated, which
will keep the size of the .db file smaller and there is greater
control over the display of the information.
Now to the nitty-gritty of the whole thing.
Set up your databases with the fields you want. I will continue to
use the ones that I used above to keep things consistent. For each
database, you will need a .cfg file, a .db file and an html.pl file.
You will probably only need a .count file for your "many" database
(in my example, the "item" database). Be sure to use the same .pass
file for both databases. It is a good idea to give the files
descriptive names so you will know which file goes with which
database. It also would be better not to have either one of the
databases named "default." Save the "default" name for files the two
have in common.
default.log
default.pass
item.cfg
item.count
item.db
item_html.pl
user.cfg
user.db
user_html.pl
Set up the *_html.pl files like you would for a regular database. You
can not use the autogenerate feature for this modification.
Add two subroutines to your db.cgi file to switch between the two
databases. I'll show you the subroutines for my databases first and
then explain them.
##########################################################
sub switch_to_item {
#-----------------------------------------------------
$cols = 'UserID,ItemID,Item,Classification,Price,Description,DateAdded';
@db_cols = split /,/,$cols;
$db_file_name = $db_script_path . "/item.db";
$db_sort{'DateAdded'} = 'date';
}
sub switch_to_user {
#-----------------------------------------------------
$cols = 'UserID,UserFirstName,UserLastName,UserEmail,UserCity,UserState,UserCountry';
@db_cols = split /,/,$cols;
$db_file_name = $db_script_path . "/user.db";
$db_key_pos = 0;
}
##########################################################
The names of the subroutines are arbitrary, but I think it's
important that you make them something you can remember as you're
working with the script. Use the names of your own databases, instead
of the ones that I used for my databases.
The first line lists all of the fields in the database, in the
correct order. Be certain that you separate the field names with
commas. As always in Perl, pay strict attention to the spelling and
case of the field names.
The second line breaks up the list of field names into an array.
The third line is the name of the .db file for the database.
The fourth line in the first subroutine is optional. In my example,
when I list out items that a given user has added to the database, I
want them sorted by date. I need to tell DBMan what type of sort I
want it to do on that field. You only need to add the sort type for
one field -- the field you intend to sort by. If you don't want your
"many" records to be sorted, you can ignore this line.
In the second subroutine -- the one that refers to the "one" side of
the relationship -- add the field number of the key as the
$db_key_pos.
There's one other thing to do in the db.cgi script. Look for
sub get_record
and delete the line
($restricted = 1) if ($auth_modify_own and !$per_admin);
The rest of the work will take place in the html.pl files.
We'll start with the user_html.pl file -- the file for the "one" side
of the relationship.
In sub html_record, I want to list the user's information, plus the
information about any items she has added. If you are using the
short/long display mod, you would make these changes to
sub html_record_long.
I already have set up sub html_record to display my user information:
------------------------------------------------
sub html_record {
# --------------------------------------------------------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.
my (%rec) = @_; # Load any defaults to put in the VALUE field.
($db_auto_generate and print &build_html_record(%rec) and return);
my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';
print qq|
|;
if ($per_admin) {
print qq|
| <$font_color>UserID: |
<$font>$rec{'UserID'} |
|;
}
print qq|
| <$font_color>UserFirstName: |
<$font>$rec{'UserFirstName'} |
| <$font_color>UserLastName: |
<$font>$rec{'UserLastName'} |
| <$font_color>UserEmail: |
<$font>$rec{'UserEmail'}
|
| <$font_color>UserCity: |
<$font>$rec{'UserCity'} |
| <$font_color>UserState: |
<$font>$rec{'UserState'} |
| <$font_color>UserCountry: |
<$font>$rec{'UserCountry'} |
|;
}
------------------------------------------------------------------------
I've decided that what I want to do is to just list the names of the
items the user has added to the database, with a link to the full
display of the description of the item.
After the |; above, I add the following:
------------------------------------------------------------------------
# Tell DBMan to look in the other database
&switch_to_item;
# Clear out any previous search terms
undef %in;
# Get rid of any bold tags that might be in the userid field
$rec{'UserID'} =~ s/<.?B>//g;
# Look for items added by this user
$in{'UserID'} = $rec{'UserID'};
# Sort the results by the DateAdded field, in descending order
$in{'sb'} = 6;
$in{'so'} = 'descend';
# Set the maximum number of hits per page to a large enough number to
# guarantee that all records will be returned
$in{'mh'} = 100;
# Do the search
my ($status2,@hits2) = &query("view");
# If there are any search results
if ($status2 eq "ok") {
# Calculate the number of records returned
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
# Set up a table for the display
print qq||;
# Go through each hit and print out the results
for (0 .. $numhits2 - 1) {
# Break the array into a hash for printing
%rec2 = &array_to_hash($_, @hits2);
# Print out the link to the full item record
# Note that I am using $db_script_url and *not* $db_script_link_url.
print qq|
|
$rec2{'Item'}
|
|;
}
print qq|
|;
}
# Tell DBMan to go back to the original database
&switch_to_user;
----------------------------------------------------------
So, taking out all the explanatory comments, my subroutine looks like
this:
sub html_record {
# --------------------------------------------------------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.
my (%rec) = @_; # Load any defaults to put in the VALUE field.
($db_auto_generate and print &build_html_record(%rec) and return);
my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';
print qq|
|;
if ($per_admin) {
print qq|
| <$font_color>UserID: |
<$font>$rec{'UserID'} |
|;
}
print qq|
| <$font_color>UserFirstName: |
<$font>$rec{'UserFirstName'} |
| <$font_color>UserLastName: |
<$font>$rec{'UserLastName'} |
| <$font_color>UserEmail: |
<$font>$rec{'UserEmail'}
|
| <$font_color>UserCity: |
<$font>$rec{'UserCity'} |
| <$font_color>UserState: |
<$font>$rec{'UserState'} |
| <$font_color>UserCountry: |
<$font>$rec{'UserCountry'} |
|;
&switch_to_item;
undef %in;
$rec{'UserID'} =~ s/<.?B>//g;
$in{'UserID'} = $rec{'UserID'};
$in{'sb'} = 6;
$in{'so'} = 'descend';
$in{'mh'} = 100;
my ($status2,@hits2) = &query("view");
if ($status2 eq "ok") {
my ($numhits2) = ($#hits2+1) / ($#db_cols+1);
print qq||;
for (0 .. $numhits2 - 1) {
%rec2 = &array_to_hash($_, @hits2);
print qq|
|
$rec2{'Item'}
|
|;
}
print qq|
|;
}
&switch_to_user;
}
-----------------------------------------------------------------------
Now for the item_html.pl file -- the "many" side of the relationship.
Again, you would start out with your normal sub html_record
(or sub html_record_long, if you're using the short/long display mod).
-----------------------------------------------------------------------------------
sub html_record {
# --------------------------------------------------------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.
my (%rec) = @_; # Load any defaults to put in the VALUE field.
($db_auto_generate and print &build_html_record(%rec) and return);
my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';
print qq|
|;
if ($per_admin) {
print qq|
| <$font_color>UserID: |
<$font>$rec{'UserID'} |
|;
}
print qq|
| <$font_color>Item: |
<$font>$rec{'Item'} |
| <$font_color>Classification: |
<$font>$rec{'Classification'} |
| <$font_color>Price: |
<$font>$rec{'Price'} |
| <$font_color>Description: |
<$font>$rec{'Description'} |
|;
}
------------------------------------------------------------------------
Since there is only one "external" record that is associated with
this record, you can get a little fancier with it.
Right after
my (%rec) = @_;
add
---------------------------------------------
# Tell DBMan which database to look in
&switch_to_user;
# Get rid of any bold tags that might be in the userid field
$rec{'UserID'} =~ s/<.?B>//g;
# Get the associated record
%rec2 = &get_record($rec{'UserID'});
# Tell DBMan to go back to the original database
&switch_to_item;
--------------------------------------------
At this point, you can use any of the fields you want to from your
"one" database. Instead of using $rec{'FieldName'}, for the values
from the external database, use $rec2{'FieldName'}.
To print out the user info along with the item info, I might add
something like:
| Offered by: |
$rec2{'UserFirstName'} $rec{'UserLastName'} |
| From: |
$rec2{'UserCity'} $rec2{'UserState'} $rec2{'UserCountry'} |
So that my entire subroutine would be
sub html_record {
# --------------------------------------------------------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.
my (%rec) = @_; # Load any defaults to put in the VALUE field.
&switch_to_user;
$rec{'UserID'} =~ s/<.?B>//g;
%rec2 = &get_record($rec{'UserID'});
&switch_to_item;
($db_auto_generate and print &build_html_record(%rec) and return);
my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#003399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';
print qq|
|;
if ($per_admin) {
print qq|
| <$font_color>UserID: |
<$font>$rec{'UserID'} |
|;
}
print qq|
| <$font_color>Item: |
<$font>$rec{'Item'} |
| <$font_color>Classification: |
<$font>$rec{'Classification'} |
| <$font_color>Price: |
<$font>$rec{'Price'} |
| <$font_color>Description: |
<$font>$rec{'Description'} |
| Offered by: |
$rec2{'UserFirstName'} $rec{'UserLastName'} |
| From: |
$rec2{'UserCity'} $rec2{'UserState'} $rec2{'UserCountry'} |
|;
}
You will probably want to have links in the footer to go back and
forth between the databases. You can copy the links that are in
sub html_footer, except that you will need to change
$db_script_link_url
to
$db_script_url?db=database name&uid=$db_uid