Databases 101

Author: Dwight Evers
email: dmevers@bitstream.net
date: June 20, 2000

INTRODUCTION

Most, if not all, unix platforms support some form of storage mechanism. What you put on your storage device is up to you. How you store it, be it flat files that you parse with grep and sort, or a fully Relational Database Management System (i.e. Oracle, Sybase..etc) is up to you.

What we will look at here are 4 of the more "popular" and "inexpensive" databases for storing information.

QUALIFICATIONS

When I talk about a qualified database, I am using 3 criteria. 1. Client/Server accesible 2. Perl/Tcl add-on available 3. Accepts standard ANSI SQL commands (ASNI-SQL92 compliance not required)

Whis this in mind, I have selected 4 databases to review that work well on SCO as well as Linux. MiniSQL2, MySQL, Postgresql, and Interbase.

Although Interbase is going "opensource", the edition available at this time for OpenServer is still the 90 day eval version of 5.5. Linux was tested with 6.0.

Postgresql recently hit rev 7, which "officially" supports SCO, but only with its C API (this includes the PerlDBD/DBI interface) and with special compiler options.

MiniSQL2 (v2.0.11) is opensource, but not "free". See its license agreement for specifics. It does NOT have a MS-Windows ODBC driver available.

MySQL3 is opensource (just like MiniSQL2) but is "free". See its license agreement for specifics. An ODBC driver is available.

INSTALLATION

Of the database discussed, all but Interbase are available as both source and binary online. If you can, get a binary and install it, unless you have a compiler and developer tools available.

MySQL requires that FSUthreads be installed so that multithreading can take place on the server. This is available seperately at www.sco.com/skunkware.

Disk Requirements: MiniSQL2 = 12MB MySQL = 15MB Postgresql7 = 40MB Interbaase = 65MB

Setup/Configuration: The databases listed all require a specific userid for the database to be run as. Generally speaking, it is best to have this setup and in its own group before proceding with the install. (i.e. MiniSQL2 runs as "msql")

To configure a database, all of them use an interactive sql tool, similar to oracle's sqlplus or sybases isql.

Creating a database is as simple as issueing the SQL command to do so.

DATABASE ACCESS

MiniSQL2 uses an access control list (ACL) stored in an external file to handle access control. This file contains database users, database names, and hosts/IP's allowable. Simple and effective. It does not infer group priveledges or multiple roles.

MySQL uses a dictionary table, like sybase and oracle, to determine access and priveledges. It understands users and groups, but, does not use roles, but does understand procedures.

Postgresql7 understands users, groups, roles, and privledges. It shares many of the same "high end" features of Informix, Oracle, and Sybase. Such as begin/commit and transaction processing. It does NOT allow you to perform joins across databases. This could SERIOUSLY limit it funtionality in a production environment.

Interbase is a true ANSI SQL92 compliant database. It supports SMP as well as supporting advanced groups/users and handles privledges in a more mature manner as well. Additionally, it can be accessed from both ODBC as well as JDBC. It has a C api and can communicate via TCP/IP as well as IPX. (although, IPX support is questionable for the future)

COMPARISON

Where these databases are useful is dependant on how you implement them. Here is a short list of Pro's/Con's for each.

Attribute       Interbase   MiniSQL2  MySQL3  Postgresql7  Oracle8i  Sybase11
SQL89           mostly      mostly    mostly  mostly       yes       yes
ANSI SQL92      yes         no        no      mostly       yes       yes
BLOBS           yes         no        no      yes          yes       yes
inner JOINS     yes         yes       yes     yes          yes       yes
outer JOINS     yes         no        no      yes          yes       yes
table JOINS     yes         yes       yes     yes          yes       yes
DB JOINS        yes         yes       yes     no           yes       yes
Caches          no          no        no      no           yes       yes
SMP Support     yes*        no        yes**   no           yes       yes
Procedures      yes         no        no      yes          yes       yes
transactions    yes         no        no      yes          yes       yes
ODBC            yes         no        no      yes          yes       yes
JDBC            yes         yes       yes     yes          yes       yes
hot backups     no          no        no      no           yes       yes
Cost/server     $750        $250      free    free         $1250     $750
Cost/client     $85         none      none    none         $50       $50
Cost/CPU        none        none      none    none         $1000     $1000

* Interbase runs a copy of itself each time you specify a processor/cpu
** MySQL3 requires the use of FPUthreads, but, this lib does not fully support
   SMP on SCO or on other some other operating systems.
 

NOTE: Oracle and Sybase11 charge a cost per "power point", which is dependant how fast your server is. So, Oracle on an E10000 from Sun would be more than Oracle on an E250. Pricing and availability subject to change.

USEFULNESS

Below, I generalize where things are better off getting implemented.

MiniSQL2: Web databases and tracking. Good speed and C-API. Perl and Tcl are supported. Flat files that are generated limit size of tables to 2GB, but, virtually unlimited number of tables per database allowed. Small size and simplicity makes it great for embeded applications.

MySQL3: Good SMP support, but, not consistent across platforms. Excellent speed and access. No support for transactions limit functionality. Great for Web databases as well as departmental tracking/access.

Postgresql7: No support for Multi database joins SEVERELY limits its production appeal. Excellent stability and API make it an (otherwise) high end solution. Lack of hot backups is limiting as well. Great for large databases and simple warehousing.

Interbase6: Great for Web databases and scales all the way up to several GB in size. Stability and SMP support are getting better, allowing for consistent performance. Memory issues resolved with v6. Lack of hot backups and replication make for questionable use in high volume traffic situations.

Oracle8i: The "All Mighty" of databases. Hot backups, parallel servers, replication, and logging make it the most widely distributed RDBMS in the World. The leader in Very Large DataBase (VLDB) and data warehousing.

Sybase11: Support for both datacache and table caches make it excellent for high performance tuning. Support for SMP and hot backups and roll back segments allow for full production use. Its replication server is not as mature, but is improving. Great for datawarehousing and transaction processing, but, does not lend itself to VLDB use.

SUMMARY

After installing and looking at these 4 databases, I have found that I prefer to use MiniSQL2, as it does not require the use of threads or limit me to just a C-API.

MySQL is always undergoing HEAVY developement, so some releases are more stable than others. Its support for SMP and speed makes it a great choice for more heavily used environments.

Postgresql7 has really taken off as far as support and features. With the addition of multi database joins and a hot backup solution, it could contend with the likes of Oracle and Sybase.

Interbase 6 (5.5 on SCO) has stepped up to the plate and delivered everything they have promised. Support for SCO is off-again/on-again, but, it has ANSI SQL92 support as well as transaction capabilites. Speed plagues it, still. Costs also inhibit its exceptance in our Opensource world, but, users/developers have been promised that Borland/Inprise will deliver.

At some later date, we shall revisit the latest in opensource database for both SCO and Linux.

Publish your articles, comments, book reviews or opinions here!

© June 2000 Dwight Evers. All rights reserved

If this page was useful to you, please click to help others find it:  
Your +1's can help friends, contacts, and others on the web find the best stuff when they search.

Comments?

More Articles by Dwight Evers

Site map | Disclaimer
"Databases 101 " Copyright June 2000 Dwight Evers

Jump to Comments

Many of the products and books I review are things I purchased for my own use. Some were given to me specifically for the purpose of reviewing them. I resell or can earn commissions from the sale of some of these items. Links within these pages may be affiliate links that pay me for referring you to them. That's mostly insignificant amounts of money; whenever it is not I have made my relationship plain. I also may own stock in companies mentioned here. If you have any question, please do feel free to contact me.

Specific links that take you to pages that allow you to purchase the item I reviewed are very likely to pay me a commission. Many of the books I review were given to me by the publishers specifically for the purpose of writing a review. These gifts and referral fees do not affect my opinions; I often give bad reviews anyway.

We use Google third-party advertising companies to serve ads when you visit our website. These companies may use information (not including your name, address, email address, or telephone number) about your visits to this and other websites in order to provide advertisements about goods and services of interest to you. If you would like more information about this practice and to know your choices about not having this information used by these companies, click here.