Author: Dwight Evers
email: [email protected]
date: June 20, 2000
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.
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.
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.
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)
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.
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.
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.
Got something to add? Send me email.
Increase ad revenue 50-250% with Ezoic
More Articles by Dwight Evers © 2009-11-07 Dwight Evers