Interfacing Relational Databases to the Web

Why would one want to link a database to the web? A better question might be, “Why wouldn't one want to link a database to the web?” Static HTML pages are good for many things: documentation, hypertext books, personal pages and other unchanging information. However, static pages present a few problems:

I hope this quick rundown is enough to convince you that you really want to use a database for your dynamic web site. In this article, I will present instructions for installing PHP3 (http://www.php.net/) and PostgreSQL (http://www.postgresql.org/), a little bit of theory, some instructions for using SQL and PHP3, and an example application.

This description assumes you are running Red Hat, but most of these instructions will be applicable to other distributions; these tools are fairly painless to install from source, anyway.

Here's a list of what you'll need to run the example application and develop your own applications:

To rebuild PHP3 for PostgreSQL support, take the following steps:

PostgreSQL uses a different access system than the rest of your system; oddly enough, not even root has access to the database by default. The database system has its own user system and passwords, and postgres is the database administrator account by default. The advantage to the separate access system is that one can create database users who do not have UNIX accounts; this way, the database for your web application can specify access control without creating a potential security hole for your system. To add your web administrator (web) as a database user, use createuser (as root):

# su postgres -c createuser
Enter name of user to add ---> web
Enter user's postgres ID, or RETURN to use UNIX user id: 542 -> 542
Is user "web" allowed to create databases (y/n) y
Is user "web" allowed to add users? (y/n) y
createuser: web was successfully added

Then, as web (or whatever account you used), you'll be able to create a database with createdb foo and then try some queries on foo using psql foo.

You'll also need to set up PostgreSQL to accept incoming TCP/IP connections so your PHP3 pages can access it. Fortunately, System V init makes this easy. Simply open the file /etc/rc.d/init.d/postgresql and change the line

su postgres -l -c \
 'usr/bin/postmaster -S -D/var/lib/pgsql'

so that it reads

su postgres -l -c \
 '/usr/bin/postmaster -S -D/var/lib/pgsql -i'

Just about every relational database in the world uses SQL (or some extended version of SQL) as its query language. SQL allows you to define tables, select records based on given criteria, update values in one or many records and delete records. This is just a brief introduction to SQL; for more complete references, see Resources.

To create a table, one uses the CREATE TABLE statement. Its syntax is as follows:

CREATE TABLE tablename (field-1 type-1, ..., field-n type-n)

In psql, you will need to end each statement with a semicolon. These semicolons are not part of the SQL language, but rather for the benefit of psql's lexer.

You may also declare fields as NOT NULL, UNIQUE or PRIMARY KEY, or specify a value as DEFAULT to a field. PostgreSQL will create an index on primary key fields. Unfortunately, as of version 6.4, PostgreSQL does not support foreign keys, but at least the parser will not choke on the SQL REFERENCES keyword.

Here's an example, akin to the UNIX password file:

CREATE TABLE passwd
   (username    varchar(8) PRIMARY KEY,
   -- PRIMARY KEY implies UNIQUE
    cryptedpass char(13),
    uid         int UNIQUE NOT NULL,
    gid         int NOT NULL,
    gecos       varchar(80),
    -- the GECOS field (real name, office, etc.)
    homedir     varchar(80),
    shell       varchar(50) DEFAULT '/bin/sh');

Note that SQL uses a double-dash to begin comments, which are terminated by a newline.

To insert data into a table, use the INSERT statement:

INSERT INTO tablename (field-1, ..., field-n)
VALUES (value-1, ..., value-n/)

You needn't specify field names if you are inserting values into every field. Here's an example for the table we just created:

INSERT INTO passwd (username, cryptedpass,
   uid, gid, gecos, homedir, shell)
VALUES ('fred', '37MniLTaiPLaL', 42, 500,
   'Fred Mbogo', '/home/fred/', '/bin/sh');

The SQL SELECT statement returns records where values meet a certain criteria. Here are some examples of SELECT in action:

SELECT * FROM passwd;
-- returns all fields of all records
SELECT username FROM passwd;
-- returns all usernames
SELECT * FROM passwd WHERE username = 'fred';
SELECT * FROM passwd ORDER BY username, shell;
SELECT * FROM passwd
     WHERE homedir LIKE '/home%'
     -- % is the SQL wildcard character
     AND   shell = '/bin/sh'
     ORDER BY username;
SELECT homedir, projectname
     FROM passwd, projects
     -- assuming we have a projects
     table
     WHERE
     -- this will return the home directory of
       passwd.username = projects.leader;
       -- each project leader for each project

To change field values in records, use UPDATE:

UPDATE tablename SET field-1 =
value-n WHERE qualification

The WHERE is optional, but if you don't specify a WHERE clause, SQL will update all the records, which is clearly the “Wrong Thing”.

Let's say Fred Mbogo wants to change his shell. This script will do it:

UPDATE passwd SET shell = '/bin/tcsh' WHERE username = 'fred';

To delete records, simply use DELETE:

DELETE FROM tablename WHERE qualifier

Just like UPDATE, the WHERE is optional, but you probably want it anyway. Let's say Fred has offended his sysadmin one too many times:

DELETE FROM passwd WHERE username = 'fred';

The on-line PHP3 manual, http://www.php.net/manual/, is an excellent reference and will be necessary reading before you create your own database web application. Furthermore, it is a database-backed web site and has lots of user comments. Here, we will examine just the most basic PHP3 features.

Here is a simple PHP3 program, which demonstrates some basic features. Note the separate HTML and PHP3 blocks:

<title>Hello, world!</title>
<body>
<?php
  echo("Hello, world!\n");
  echo("<p>\nWhat a <b>bold</b> move this is!\n");
?>
</body>

This program will send the following HTML to the remote browser:

<title>Hello, world!</title>
<body>
Hello, world!
<p>
What a <b>bold</b> move this is!
</body>

<title>Hello, world!</title>
<body>
<?php
  echo("Hello, $name!\n");
  echo("<p>\nWhat a <b>bold</b> move this is!\n");
?>
</body>

PHP3 provides a number of useful functions for connecting to databases; the best place to read up on these is www.php.net/manual/ref.pgsql.php3, and we shall examine a few of them.

int pg_connect(host, port, options, tty, dbname);

This function returns an integer, the “connection index”, which you will need for all operations on this connection. If a connection can't be established, it returns zero.

Our example application is an address book that one can access over the Internet. A user logs in with her name and password and is presented with a menu of options, including browsing and searching the address book and adding a new person. For each person in the address book, the database stores an arbitrary number of e-mail addresses, telephone numbers, URLs and postal addresses. This address book also has some nifty features like mailing passwords to new accounts and automatic mailto and href links for e-mail and web addresses.

We have already completed the first step of the design process—deciding what we need our application to do. What remains is defining the data that our application will access in three iterations:

Generally, one initially models databases in an entity-relationship diagram, which represents each table as an entity with a set of attributes, and a “join”, or query, spanning multiple tables is represented by a relationship. Even if you're not going to go to the trouble of making up an E-R diagram, you should at least consider these concepts; we shall examine the entities and relationships for the address book in prose.

The low-level model we shall be using is the relational model, which has been around since 1970 and is the model used by most commercial relational databases, including Oracle, Sybase and Informix.

Finally, we shall define the ways in which our users can see the data. Once we've completed this, we're done with the hard part and can move on to the tedious part—the implementation.

Since the entity-relationship model is such a high-level model, some of the hairier issues of the low-level model are not yet apparent, and our data model looks quite simple. The main advantage of the E-R model is that it presents a clear picture of the database miniworld—the segment of the real world that we're modeling—and is easily comprehensible to both laypersons and software engineers. Entities are defined as follows:

Relationships among entities are defined in this way:

Now we need to move our high-level model (which people can understand) to a low-level model (which our database management system can understand). This process is quite straightforward, although the database designer still has some options at this point, involving normalization. Normalization (see Resources) is a formal process to quantify and measure the quality of a relational model; as always, the tradeoff is theoretical quality versus performance.

The quick-and-dirty algorithm for moving your data model from the entity-relationship to the relational model is this:

This is necessarily a simplified version of the process; it does not deal with m:n relationships or some other details. For a more complete discussion of data model conversion, see a textbook on database theory.

SQL code for the “finished” relational model can be found at ftp.linuxjournal.com/pub/lj/listings/issue67/3475.tgz. All code is released under the GNU GPL.

The remaining design step is deciding what sort of capabilities we wish to grant users to access and update the data. This is perhaps not as much of a problem in our database, but what if we were designing a database of employees? It might cause great discord in the office if everyone knew the salary of the guy who spends every day surfing the Web and taking two-hour lunch breaks; however, they should be able to access his name, department and extension. Likewise, they shouldn't be able to change that information unless they are the department secretary or manager.

We do want some protection on our address book, so that you can type in your grandmother's e-mail address with the peace of mind that a spammer can't get it just by accessing your web server. We also don't want to bother the user with implementation details like unique ID numbers on each record—this should be a user-friendly address book. Therefore, we will allow the following:

To this end, we create views. A view can be just a few columns of a table or a few columns of a join. In SQL, a view is defined with the CREATE VIEW statement, which creates a view from a SELECT statement. A view can be accessed just like a table, except you can't perform inserts, updates or deletes on it. Some of the views in our example application also use PostgreSQL functions to make the final application programming easier, i.e., “make a mailto URL from this e-mail address”.

We also make note of the constraints which we cannot enforce with views: for example, the consideration that one may view only her own address book. We must implement these constraints in the application program.

Implementation in PHP3 is quite straightforward; many things in the example code speak for themselves, and others are well-commented.

The source code for the example application is intended to be more of a teaching tool than a finished product. It works well, but you would certainly want to add features before making a large-scale service from it. I have released it under the GNU GPL, so feel free to modify my code and share your modifications with others. This code is also on the FTP site shown above.

Resources