Web server analysis logs and mailing list management is made easy by using the /rdb database system--here's how to do it.
by Ed Petron
The /rdb database system from Revolutionary Software is a compact, low-cost relational database management system that has been available for Linux and other Unix variants for several years. It distinguishes itself from most (if not all) other Unix database systems by functioning as an extension of the Unix shell.
The typical Unix distribution contains a rich set of shell commands (often referred to as filters) such as awk, grep and sed that operate primarily on text data. In addition, pipes can be used to route the output from one command into the input of another command. These commands can either be standard commands like those mentioned above or be created by the user. This system of pipes and filters simplifies the development of complex applications by enabling the reuse of components that have already been tested and proven.
Most filters are designed to operate on ASCII text data, so it is natural to represent logical data records as ASCII text lines. Data elements within the records are separated by a field delimiter character (such as a colon or tab) specified by the application. Alphanumeric data elements such as names, addresses and descriptions can be as long or as short as needed. Numeric data elements can have unlimited magnitude and precision, and their representation is architecture independent. This scheme provides complete portability of data between platforms.
Approaches to application design inherited from other platforms typically use fixed-length fields with predetermined field lengths. These lengths result in wasted file space due to the unpredictability of an application's actual storage requirements. Numeric data is often stored in binary format which makes it highly platform specific and nonportable. Data elements such as ``description'' or ``comment'' fields can vary greatly in size, and the use of fixed-length fields forces the application designer to either allocate enough storage to cover all cases (often impossible) or to dedicate separate data files to their storage (often making application logic clumsy, complex and difficult to manage).
The result of these approaches is the allocation of disk space that is never used and application programs that are difficult to maintain. Although recent innovations such as SQL have simplified the problem of data access from applications, many programs are tightly bound to storage layouts determined at compile time. This binding of code to data forces recompilation and relinking of application code each time a change in record format is required. Worse yet, many applications use ``filler fields'' to avoid recompilation, wasting even more disk space and I/O bandwidth.
While a great many Unix systems (database systems included) use the approach outlined in the last section, /rdb augments the capabilities already provided by standard Unix shell commands. Many /rdb commands operate as front ends for standard Unix commands such as awk. This enables users already familiar with Unix to create /rdb applications without learning a new programming language.
The format of /rdb tables is extremely simple. They are ASCII text files in which a row is a line of data. The column elements within each row are delimited by a column separation character (a tab by default but any other character except the newline can be used). /rdb commands use a -f command line switch to specify a column separator in cases where it is not a tab. Note the similarity between this approach and typical Unix system configuration data files such as /etc/passwd. The only difference is that the first line of /rdb tables is a delimited set of field names and the second line is a line of dashes. These first two lines are known as a header.
Part of the beauty of /rdb tables is that, for some applications, they need not even be stored in disk files. Instead, they can be passed between commands using pipes. These commands can be /rdb commands or standard Unix shell commands. The /rdb commands are designed to output tables through stdout, and most accept input tables through stdin. Although some standard Unix commands will be ``confused'' by the header lines, /rdb provides commands for removing and reattaching these headers (headoff and headon, respectively).
Most /rdb commands operate on tables that are read through stdin. The resulting table is written to stdout. Here are a few examples:
row 'Cost < 50' inventoryselects the rows in the inventory table where the Cost field is less than 50.
column 'Description' < inventoryreads the inventory table and outputs a table containing only the column Description.
addcol 'Value' < inventoryreads inventory and outputs a new table that is identical to inventory except for the addition of a new column called Value. At this point, all of the fields in the Value column are initialized to empty strings.
compute 'Value = Cost * Quantity'reads an input table and, on each row, sets Value to Cost * Quantity. If Value is a column name, the computed value is stored in the table; otherwise, it could still be referenced in other statements within the same compute statement.
Putting these commands together, the Bourne shell script in Listing 1 will output a table describing inventory items which cost less than $50 US and showing the value of each.
These commands are only a few of the filters that /rdb provides. For a complete listing, see the on-line man pages listed in Resources.
The row and compute filters are actually front ends for awk. Since awk doesn't understand field names as defined by /rdb, these commands convert field names into column numbers which awk does understand. As a result, the user who is familiar with awk can immediately use these commands and many others provided by /rdb.
Because /rdb tables are ASCII files, they can be created and maintained by any text editor. This will work in many cases but will be awkward in others, in particular, with wide tables or those containing a large number of columns. /rdb provides two table editors, ve and jve for this purpose. A complete description of these editors is beyond the scope of this article, but the following is a partial list of the capabilities provided:
In order to illustrate how /rdb operates in conjunction with other Unix shell commands, the script in Listing 2 shows how a log file can be analyzed to report a server's top five accessors. This script assumes that stdin is a log file with space delimited fields with the host name as the first field (this is typical for Apache and several other web servers). The following is a step by step description of the process:
The script in Listing 2 produces the following output:
host n wilbur.leba.net 106 gateway.amat.com 24 208.219.77.9 20 ras3.fsxnet.com 14 ohnp6.m50.nrc.ca 14Actually, it wasn't necessary to create and store any /rdb tables.
Now we look at a slightly more complex example: the generation of mailing labels. Assuming that stdin is an /rdb table with fields named Salut (Mr., Mrs., etc.), Fname, Lname, Address1, Address2 (sometimes blank), City, State and Zip, we create the script shown in Listing 3.
For each row in the table, this script creates a mailing label with either three or four lines (depending on whether or not the second address line was used). After adding columns to represent the lines in our final output, the compute command determines their contents. The resulting table is sorted by zip code so that the labels are grouped for bulk mailing purposes. The report command formats the output using the report.frm file shown here:
<Line1> <Line2> <Line3> <Line4>This file is a very simple example of a report form file used by /rdb to generate reports. A field name within brackets indicates the substitution of the contents of the named field.
Other report file options are available as well, although we won't describe them here. One of the most significant options is the placement of output from shell commands. In this example, the output from report is a series of labels in a single column. This data is piped into pr which arranges it into two columns.
There are five access methods provided by /rdb: sequential (used by the above examples), record, binary, inverted and hashed. An index command is provided to build index files, and a search command is used to execute keyed retrievals.
A set of library functions, librdb.a, is provided for use by those who require access through C programs. Complete C source code for these functions is also included.
/rdb is a powerful but low cost package suitable for a large variety of database management tasks. It offers the following benefits:
The only disadvantage is that platform-independent systems generally run slower than those that are machine specific. Also, variable- length records tend to be more difficult to manage than fixed-length ones. Even so, /rdb performs well even with large databases (see Resources for pointers to demos available on the Web). Even in the extreme cases where a more traditional database system may be needed, /rdb can still be used as a front end as shown in Listing 2.
Finally, the economic considerations involved in the hardware independence versus performance tradeoff are clear. Hardware price/performance ratios are dropping rapidly while the cost of programmer time is constantly rising. This being the case, hardware independence is the uncontested winner.
Ed Petron is a computer consultant interested in heterogeneous computing. He holds a Bachelor of Music from Indiana University and a Bachelor of Science in computer science from Chapman College. His home page, The Technical and Network Computing Home Page at http://www.leba.net/~epetron, is dedicated to Linux, The X Window System, heterogeneous computing and free software. Ed can be reached via e-mail at epetron@wilbur.leba.net.