Portable Database Management with /rdb
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' inventory
selects 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:
Forms based data entry: Screen files, ASCII files describing data-entry screen layout, can be specified using the -s option and are used to associate screen field positions with table column names and to provide initialization and write protection for certain fields.
Data validation: The -v option is used to specify a validation file to provide data edits based on allowed and disallowed ranges of characters, column length limits, table lookup and command-based validation. With command-based validation, the values can be passed to any arbitrary command or shell script. The exit status indicates to jve whether or not the data is considered valid (zero for success, non-zero for failure). If a non-zero status is received, jve will beep and display the first line of standard error at the bottom of the screen.
Audit file creation and maintenance: An audit file tracks changes to a table and can be used by the rollback command to restore a table to a specific point in time.
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 combination of awk and the header command produces a table with host names in order of their appearance in the log file. The output from awk is not in /rdb table format, so header is used to complete the production of the table.
The addcol command followed by compute is used to add a column named n and initialize its value to 1.
The table is then sorted by host and the n values are totaled using host as a control break to produce a table of the host names and the total access counts for each, i.e., the value of n.
Since we are interested in seeing the five hosts with the highest access counts, the table produced in step 3 is sorted by n.
The top seven lines from the table in step 4 (two header lines plus five rows) are filtered out by the head command and passed to the justify command which vertically aligns the header and columns and produces a format better suited to printing.
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 14
Actually, 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:
Low cost: Only $149US for Linux, SCO and other Intel Unix implementations, $495US for RISC platforms.
Short learning curve: Users already familiar with Unix become proficient quickly.
Low Hardware Requirements: /rdb requires under 5MB of disc space to install. /rdb commands are compact since they function mostly as front ends for standard Unix tools. The mailing list application presented above is a “real world” program that was previously done using a dBase file. Since the dBase file used fixed-length fields, they contained a lot of trailing blanks which were eliminated when converted to an /rdb table. As a result, the /rdb table was roughly one third the size of the original dBase file.
Integrates well with other Unix commands: This point is illustrated in the web server log analysis example. One could easily create generic interface programs for other systems (including other databases) and using /rdb to provide data entry and validation on the input side and report generation on the output side.
Portability of data and code: This is becoming increasingly important as networked computing environments become more common.
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.
