|
|
|
Linux, SQL, and the WebHistorically, the construction of SQL-enabled Web applications has not been easy. Such systems usually required not only expensive database software, but also programmers familiar with C, the CGI specification, and dialects of embedded SQL. PHP 3.0 and PostgreSQL stand to change this state of affairs. PHP allows SQL scripting to be embedded in HTML files, and PostgreSQL brings a reasonably powerful database to the masses. This article presents a complete Linux-based, SQL-enabled Web application, hiding none of the details.
Questions regarding this article should be directed to the author at cfisher@netexpress.net How You Can Use This InformationHypertext Markup Language was a tremendous innovation, but its principle weakness is its static nature. HTML was intended to be a method for sharing documents, not an interactive medium. However, if your Information Systems needs require general global visibility, the Web is an ob vious choice. The Web has achieved such ubiquity that efforts to overcome its limitations are a primary developer concern. Technologies -- such as Java, ActiveX, and XML -- represent the recent attempts to extend the power of the Web. PostgreSQL is a successor to the Postgres DataBase Management System (DBMS). A DBMS enables the fast storage and retrieval of large amounts of information. The interface that is used to insert, manipulate, and extract data is called the Structured Query Language (SQL), which was developed by IBM for their DB2 database product in the 1970s. PostgreSQL implements a subset of ANSI-standard SQL (most notably, the version discussed here does not implement sub-queries or outer joins). PostgreSQL runs on a variety of Unix platforms. Precompiled binaries for PostgreSQL are now shipped standard in Red Hat Linux 5.0. Without an interface to link it to the Web, however, a SQL database will be of little use. Middlewa re software is required to meld HTML and SQL into a single format. PHP (Professional Home Pages), the successor to PHP/FI, is a utility that has enjoyed a great deal of popularity. It allows a C-like programming language with SQL extensions to be directly embedded within HTML documents. The software has recently been through a complete rewrite with a development emphasis on performance. PHP requires no extensions to the browser as it relies upon the CGI interface. Please note that PHP and PostgreSQL do not provide a total electronic commerce solution. The Apache Web Server , included with Linux, does not provide SSL encryption. Some sources for SSL extensions for Apache are the commercial Stronghold version of Apache with SSL support and SSLeay . Web sites might als o need credit-card validation software. CyberCash is one such vendor. The information presented within this document is intended for Intel-based systems running Red Hat Linux 5.0. Much of the same information will be applicable, however, to other platforms as all of the software discussed here (PostgreSQL, PHP 3.0) has been ported to a wide variety of Unix implementations. Major ChallengesIf you are running Red Hat Linux 5.0, the task before you is easiest. An older version of the PostgreSQL database is included in the binary distribution. It can easily be loaded when the system is installed by selecting it from the component menu. It is also possible to install PostgreSQL on a running system with RPM commands. If you are running an earlier release of Red Hat Linux (4.2 or below), you can install an RPM containing PostgreSQL. However, if you are running anothe r Unix variant, you must download and compile PostgreSQL. PHP is not included in Red Hat Linux 5.0. It must be downloaded and installed regardless of the Unix version that is being used. There are a number of performance options that can be selected at compile time, which will be discussed in a later step. Installing PostgreSQLPostgreSQL has a Web site with links to the latest versions of the database and a complete library of documentation. The Web site is in need of funding . That is, although PostgreSQL is a free object-oriented RDBMS, the developers are asking for a small donation to be used to upgrade their development system so they can continue development of this software. If you are preparing a fresh copy of Red Hat Linux 5.0,
installation of PostgreSQL is simple. Just select the SQL server
from the ``Compon
ents to Install'' menu at setup time, then later
in the setup indicate that the PostgreSQL server should be
started at boot in the ``Services'' menu. The software will be
installed, a ``postgres'' user will be added as a DataBase
Administrator (DBA), and startup scripts and links will be
installed under
If you installed a copy of Red Hat Linux 5.0 without loading the database components, you can install them at a later time if you have the RPMs containing PostgreSQL. You can find the RPMs on your distribution CD or off the Red Hat FTP site. If you have them, they can be installed and configured by running the following commands as root:
The
If you are running an earlier release of Red Hat Linux (4.2 or below), you can still install an RPM containing PostgreSQL. Look for postgresql-6.2.1-1.i386.rpm in the contrib areas on the ftp.redhat.com site. If you are running another Unix variant, you must download and compile PostgreSQL from their Web site's download areas . This may not be such a bad thing because a newer version of the database is available that now supports a larger subset of ANSI SQL (specifi cally, it now implements subselects). An ANSI C compiler is required for the preparation of PostgreSQL. Most modern versions of commercial UNIX do not include such compilers (down with the un-bundling of UNIX!). If you do not have a C compiler for your proprietary UNIX system, Linux presents a simpler solution. GNU Gcc is available for many platforms, and it can be used to prepare PHP. Check the Frequently Asked Questions list (FAQ) for your operating system to see if Gcc is available (such FAQs are easily found with the common internet search engines). Please also note that binaries produced from C code compiled by Gcc will rarely be as fast as binares produced from the same code but compiled by the OS vendor's native compiler. Performance under high utilization conditions may increase with such native compilers. Installing PHPPHP is available from the
PHP 3.0 Site
. The code only
recently emerged fro
m beta testing, and is available for
download. The name of the file is
PHP supports a number of database servers in addition to PostgreSQL. These include Adabas, mSQL, MySQL, Oracle, Sybase, and ODBC. PHP now also supports LDAP directory services and the IMAP mail protocol. More information about PHP is available in the FAQ at their site . There are two ways to configure PHP. It can be used either as a CGI binary, or as a module loaded at run-time for a supported Web server (Apache, the Netscape servers, and Microsoft IIS). Loading PHP as an Apache module has a number of benefits. With the CGI approach, the entire PHP parser is loaded, executed, then terminated every time a browser accesses PHP on the server. When loaded as a module, the PHP parser becomes an integrated part of the Apache run-time environment. Because the PHP mem ory image is not loaded and destroyed by each access, the performance of the module configuration is significantly greater. However, there have been a number of updates to the Apache Web server issued by Red Hat. Each time such an update occurs, the PHP-enabled Web server must be rebuilt from source. Such an arrangement can quickly become tedious. In compiling PHP as a CGI binary, PHP is built in such a way that it is entirely separate from the Web server installation. Updates to the Web server can be applied without fear of disturbing PHP. This configuration should be appropriate for all but the most high-traffic Web sites. Running PHP as a CGI binary could create a serious security
problem. Apache allows files -- named
Ultimately, users who are not overly concerned with the security of their Web server's document root directory tree and who do not anticipate high volume usage of PHP-enabled Web pages, should install the CGI version of the parser. All others should take the extra time to install PHP as a module. To install PHP as a CGI binary on a Red Hat Linux 5.0 system, issue the following commands as root (superuser):
The rest of this document assumes the use of the CGI version. If you wish to prepare the Apache module version of PHP, you
must obtain the
source for the latest Apache RPM
(<URL:ftp://ftp.redhat.com/pub/redhat/redhat-5.0/updates/SRPMS/apache-1.2.5-1.src.rpm>).
To compile and install the modified
A slightly smaller binary image will be created if the
Establishing a DatabaseThe following set of examples will create a database that implements a Web-enabled grocery shopping list. Once PostgreSQL is installed, users should be created that will maintain the databases. Those users must also be registered with the PostgreSQL server. The commands below will add a user named
If you want to use a different account name than
As superuser, issue the following commands: # useradd luser # su - postgres # createuser luser Enter user's postgres ID or RETURN to use unix user ID: 500 -> (enter) Is user "luser" allowed to create databases (y/n) y Is user "luser" allowed to add users? (y/n) n createuser: luser was successfully added # createuser nobody Enter user's postgres ID or RETURN to use unix user ID: 99 -> (enter) Is user "nobody" allowed to create databases (y/n) n Is user "nobody" allowed to add users? (y/n) n createuser: nobody was successfully added don't forget to create a database for nobody # exit (The text in
Don't forget to set the UNIX password for the ``l
user''
account with the
The user
Next, login as ``luser'' and run
Now, while logged in as ``luser,'' enter the command to
initiate the interactive SQL interpreter, namely:
The SQL interpreter will print a welcome message: Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: grocery grocery=> SQL commands may be entered directly at the prompt. They may span many lines if necessary. The command is not issued until a semicolon is encountered. Enter the following text at the prompt to create a table to hold the grocery list:
CREATE TABLE list (
item TEXT,
vendorcode INT,
quantity INT );
This command creates a table named ``list,'' which is composed of three fields. The fields correspond to columns in a spreadsheet (although a PostgreSQL database can be magnitudes larger than the largest spreadsheets). The names of the fields are ``item,'' ` `vendorcode,'' and ``quantity.'' The fields have an associated data type . For instance, the vendorcode and quantity fields are both defined as type integer (they can only contain numbers, not text). The item field is of type text, and its size can be of any length. SQL is not case-sensitive so any combination of upper- and lower-case characters can be used with its commands. The style used here follows that of most popular tutorial texts on database design, which allows one to easily distinguishe the SQL reserved words from the variable names. The following command creates another table named ``vendors'' with two fields, one for integers, the other for text.
CREATE TABLE vendors (
vendorcode INT,
vendorname TEXT );
The tables have been created and they are empty. The SQL INSERT command can be used to populate the tables with several values, as shown:
INSERT INTO vendors VA
LUES (100, 'Super Grocer');
INSERT INTO vendors VALUES (101, 'General Department Store');
INSERT INTO vendors VALUES (102, 'General Auto Parts');
INSERT INTO list VALUES ('Root Beer', 100, 3);
INSERT INTO list VALUES ('Ice Cream', 100, 1);
INSERT INTO list VALUES ('Napkins', 101, 50);
INSERT INTO list VALUES ('Spark Plugs', 102, 4);
The data can be examined with the SQL SELECT command, shown here: SELECT item, vendorcode, quantity FROM list; The SQL interpreter should respond with: item |vendorcode|quantity -----------+----------+-------- Root Beer | 100| 3 Ice Cream | 100| 1 Napkins | 101| 50 Spark Plugs| 102| 4 (4 rows) Fields can be rearranged or omitted from the SELECT command by modifying the field names:
SELECT item, quantity FROM list;
item |quantity
-----------+--------
Root Beer | 3
Ice Cream |
1
Napkins | 50
Spark Plugs| 4
(4 rows)
As an alternative, an asterisk can be used to indicate that all fields are desired from the SELECT query: SELECT * FROM list; item |vendorcode|quantity -----------+----------+-------- Root Beer | 100| 3 Ice Cream | 100| 1 Napkins | 101| 50 Spark Plugs| 102| 4 (4 rows) Notice in the previous table that the numerical vendorcodes are printed rather than the more useful vendornames. The latter are actually contained within the ``vendors'' table:
SELECT * FROM vendors;
vendorcode|vendorname
----------+------------------------
100|Super Grocer
101|General Department Store
102|General Auto Parts
(3 rows)
In the case of these two tables, however, the vendorcode field is not very useful. It would be much more appropriate to list the vendorname field from the vendors ta ble when printing the list table. Such a thing is possible by establishing a relation :
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode;
item |vendorname |quantity
-----------+------------------------+--------
Root Beer |Super Grocer | 3
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Spark Plugs|General Auto Parts | 4
(4 rows)
In the example above, the FROM clause specifies that two tables are to be used. The WHERE clause specifies the conditions for the relation. Relations such as these are called joins in SQL. When two tables are used in a SELECT statement, such as the one above, the tablename . fieldname syntax is used to distinguish between the tables and fields. There are many more options to the SQL SELECT statement; so many tha t SELECT is the most powerful command in the SQL language. Here is a slight variation on the previous example:
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;
item |vendorname |quantity
-----------+------------------------+--------
Ice Cream |Super Grocer | 1
Napkins |General Department Store| 50
Root Beer |Super Grocer | 3
Spark Plugs|General Auto Parts | 4
(4 rows)
Here the ORDER BY clause causes the output to be sorted alphabetically by the item field. One interesting point about SQL join operations is that records in one table that will not join with records in the other are omitted. If you run the following INSERT command:
INSERT INTO list VALUES ('African Violet', 103, 1);
And then immediately follow it with the previous SELECT statement:
SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY item;
You will notice that the ``African Violet'' row in the ``list'' table was not printed. However, if you run the following command (and then re-run the SELECT): INSERT INTO vendors VALUES (103, 'ACME Plant Store'); It will appear. If you wish to delete rows from the database, you can use the SQL DELETE command: DELETE FROM list WHERE item = 'African Violet'; DELETE FROM vendors WHERE vendorcode = 103; Be careful, because ``DELETE FROM list;'' would wipe out all the data, leaving the table empty. In a production environment -- where tables contain a large number of rows -- SQL operations may be faster if an ``index'' is defined. In this case, the commands to create the indexes are: CREATE INDEX list tab ON list (vendorcode); CREATE UNIQUE INDEX vendortab ON vendors (vendorcode); Notice that a unique index is created on the ``vendors'' table, because each vendor will have a unique vendor code. PostgreSQL keeps copies of modified or deleted rows in a database. This allows the database to be restored to the state it had at a previous date. Unfortunately, this also means that a large amount of storage could be consumed by inactive data. To clean your database of such inactive data, use the commands: VACUUM list; VACUUM vendors; In the next section, the Web server will be connecting to the database to perform SELECT operations. Under Red Hat Linux, the Web server process runs under user identity ``nobody.'' To grant this user permission to SELECT from the database, run the following commands: REVOKE ALL ON list FROM nobody; GRANT SELECT ON list TO nobody; REVOKE ALL ON vendors FROM nobody; GRANT SELECT ON vend ors TO nobody; The REVOKE ALL commands above remove all access permissions. Using such a REVOKE before a GRANT ensures that no previous permissions remain to the user. If you are finished with the SQL interpreter, you can log out
of it by typing:
The
This file can be manipulated with a normal text editor. To
reload the database, enter the shell command:
As a last point,
Using SELECT from the WebThis section assumes that you have a firm working knowledge of HTML. If this is not the case, you might want to review the NCSA Beginner's Guide to HTML . If you have installed your PHP binary and have entered the database commands described above, you are ready to build Web pages that use SQL. PHP, and its earlier namesake, PHP/FI, use a C-like structured programming language that is embedded directly within HTML. PHP is used as a document preprocessor (much like t he preprocessing stage of a C compiler, except that it is much more powerful). Copy the following HTML into a file on your system named
<HTML>
<HEAD>
<TITLE>View Database Records</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<P>View Database Records</P>
<TABLE BORDER="0">
<TR>
<TH>item</TH>
<TH>vendorname</TH>
<TH>quantity</TH>
</TR>
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn,
"SELECT list.item, vendors.vendorname, list.quantity
FROM list, vendors
WHERE list.vendorcode = vendors.vendorcode
ORDER BY list.item;");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$num = pg_NumRows($result);
$i = 0;
while ($i < $num) {
echo "<TR><TD>";
echo pg_Result($result, $i, "item");
echo "</TD><TD>";
echo pg_Result($result, $i, "vendorname");
echo "</TD><TD>";
echo pg_Result($result, $i, "quantity");
echo "</TD></TR>";
$i++;
}
pg_FreeResult($result);
pg_Close($conn);
?>
</TABLE>
</BODY>
</HTML>
If you are running Red Hat Linux and you have installed PHP as
If you have installed the Apache module version of PHP, instead
use
the URL:
You can substitute your Fully Qualified Domain Name (FQDN) for localhost if you want to view the page from browsers that are running on different hosts. Assuming that everything runs smoothly, You should see an HTML table that looks like this: View Database Records
The ``.phtml'' file-name extension is a normal convention for
files marked with PHP tags. It is not, however, a requirement in
this case; the files could have had an
PHP also has a directive that is similar to Server Side
Includes (SSI) supported by most popular Web servers. If the
command
PHP will also read files in users' home directories, following
the normal conventions of Unix Web servers. For example, if you
copied
The algorithm and syntax of the above HTML example are relatively simple. They are taken almost directly from the PHP/FI documentation. Notice fi
rst the enclosing
Here, these PHP statements do the following:
Using INSERT from the WebThis section assumes that you have a firm working knowledge of HTML forms. If this is not the case, you might want to review the NCSA Guide to Fill-Out Forms . There are two main sections of HTML that will be used to add records to the database. First, an HTML form must be constructed so that the information can be easily entered by the browser. Second, the information must be returned and processed by PHP in order to add it to the table. The implementation of the form, and specifically the vendor field, presents a design dilemma. The SELECT form tag is the most obvious HTML form element to use to present a pre-defined vendor list. Should this lis t be hard-coded in the HTML? If another vendor is added to the ``vendors'' table, the HTML will not be automatically updated, and users will not be able to enter data against the new vendor. The form elements for the vendor SELECT tag could alternately be generated by PHP each time the form is accessed. The drawback to this method is that the server load will be increased. Another approach would be to generate the HTML for the form whenever the ``vendors'' table is updated. This could be accomplished in a variety of ways, some of which stem from the fact that PHP can be called from a Unix shell prompt as well as from a CGI environment. Below, I'm presenting an alternative; each time the page for the data-entry form is accessed, PHP inserts data from the ``vendors'' table to build the SELECT list. Copy the following HTML into a file on your system named
<HTML>
<HEAD>
<TITLE>Insert Database Record</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<H1>Add Database Record</H1>
<FORM METHOD="post" ACTION="/cgi-bin/php/dbinsert.phtml">
<TABLE BORDER="0">
<TR>
<TD>New Item:</TD>
<TD><INPUT NAME="item">
</TR>
<TR>
<TD>Vendor:</TD>
<TD><SELECT NAME="vendor" SIZE="1">
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn,
"SELECT vendorname
FROM vendors
ORDER BY vendorname;");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$num = pg_NumRows($result);
$i = 0;
while ($i < $num) {
echo "
<OPTION>";
echo pg_Result($result, $i, "vendorname");
$i++;
}
pg_FreeResult($result);
pg_Close($conn);
?>
</SELECT></TD>
</TR>
<TR>
<TD>Quantity:</TD>
<TD><INPUT NAME="quantity"></TD>
</TR>
</TABLE>
<INPUT TYPE="submit">
</FORM>
</BODY>
</HTML>
Noticing the ORDER BY clause in the above SELECT statement, an index on ``vendors.vendorname'' might be useful if the ``vendors'' table grows large. The above PHP code extracts each vendorname in alphabetical order and prints it to the browser with a prefix of OPTION (as is required by the SELECT tag. You can now view your form with the URL:
Assuming that everything runs smoothly, you should see an HTML form that looks like this: (This form is not active, and is not attached to a database server.) Now that the ``front end'' is complete, a ``back end'' must be implemented. Copy the following HTML into a file on your system named
<HTML>
<HEAD>
<TITLE>Confirm Database Insert</TITLE>
</HEAD>
<BODY>
<DIV ALIGN="center">
<?PHP
$conn = pg_Connect("localhost", "5432", "", "", "grocery");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_Exec($conn
,
"SELECT vendorcode
FROM vendors
WHERE vendorname='$vendor';");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$vendorcode = pg_Result($result, 0, "vendorcode");
pg_FreeResult($result);
pg_Exec($conn,
"INSERT INTO list
VALUES ('$item', $vendorcode, $quantity);");
pg_Close($conn);
?>
Database Updated
</BODY>
</HTML>
The first pg_Exec() call above runs a SELECT statement that locates the vendorcode given the vendorname. The resulting code is stored in the PHP variable $vendorcode. Notice that the variable name given to the HTML SELECT tag in
With complete information for the item, vendorcode and quantit y fields of the ``list'' table, the data can then be inserted by the last pg_Exec() call. The last sections of code wrap up the PHP database
transactions and send a confirmation message to the browser. If
these
ConclusionIn spite of claims made by Microsoft, a Linux database server is actually much less expensive than SQL under NT: A Windows NT server costs approximately $1,000 (or more, depending upon user licenses). The Microsoft Backoffice package can add between $5,000 to $10,000 to the price, and what you've bought is a proprietary system with standards dictated by this monopolistic vendor. The GNU Public License (GPL) release of Red Hat Linux is available from Linux Systems Labs for the cost of the distribution media ($1.95). If you don't like Linux, all of the software mentioned above can be ported to other Unix platforms (some of it even runs on NT). Linux also includes an unlimited-seat SMB server, SQL server, dialup-server capability, FTP, WWW, Telnet, X Window System, NFS, and electronic mail servers. Some of these items are very pricey on Windows NT. If you need an inexpensive, reliable Web database solution that offers the flexibility of Unix, our combination of Red Hat Linux, PHP, and PostgreSQL is one excellent way to go. Author BiographyCharles Fisher is a writer and consultant who specializes in Linux. He has a home page that describes his personal and professional interests.
|
||||||||||||||||||||||||||||||||||||||||||||||
|
With any suggestions or questions please feel free to contact us | ||||||||||||||||||||||||||||||||||||||||||||||||