dtF-SQL
Volume Number: 14
Issue Number: 4
Column Tag: Tools Of The Trade
dtF/SQL -- The Little Engine That Could
by William A. Gilbert, Ph.D.
sLab makes there powerful SQL database engine available
for anyone to use, at little or no cost
File formats, saving data, relating fields, multiple files versus single file, backward
compatibility... this is the struggle of all programmers with each new project. One
could always use a third party database product such as FileMaker Pro or 4D but that
only increases the complexity of development, deployment, and support. Another
alternative is to use a centralized database system such as Sybase, Oracle, or Microsoft
SQL Server, but often these solutions go beyond the scope of the project and introduce
even more complexity and support problems.
Often what is needed is a small library which can store data, allow for the addition of
new data definitions while being able to provide reasonable backward compatibility and
to allow data protection from your competition. Yet at the same time it must allow
access by those very special people -- the clients and customers!
Enter dtF/SQL, the little relational database engine that not only could but does achieve
these goals and many others. dtF/SQL, a relational database engine for Mac OS,
Windows 95/NT, and several Unix platforms from sLAB http://www.slab.com/, is
provided as a small (403 Kbytes) linkable library which provides pure performance
and pure pleasure for any developer who requires data storage in a file.
Having worked extensively with large scale SQL databases I had always wanted a
product such as dtF/SQL for the "little" projects. With this library linked directly
into my application, I can store both data and program parameters, access that data
using simple or complex queries, and extend the data format by either adding additional
fields to an existing table or by creating new tables. Using Finder file type and creator
signature tags I can create double-clickable documents which launch the appropriate
application under the Finder, as well as create custom icons for these documents. And
the best part... it's free to non-commercial users and has reasonable licensing for
commercial use. This is explained comprehensively at their web site.
dtF/SQL implements an impressive set of ANSI SQL functionality. It also supports the
BLOB data type which is an arbitrarily large binary stream of data such as an image or
a numeric array of data values. Both local and remote retrieval is fast. This subjective
statement is based on experience with other database engines as well as experience
with reading and parsing other file formats.
Hiding Complexity
Our goal as programmers is to provide our clients and customers with a product which
performs both to specifications and expectations, but is also easy to use. Grady Booch
has put it well: "The task of the software development team is to engineer the illusion
of simplicity." This statement is particularly true for Macintosh applications, where
end users have come to expect an environment in which one copies the application with
some documents from the distribution disk and just expects to double-click a document
and go. Using the dtF/SQL engine there are no extensions to load and configure, there is
no restarting the computer, and there is no performance overhead imposed by
switching processes or drivers. Additionally, there is no middleware to install with
additional royalties. The bottom line is that the developer is able to deploy
self-contained double-clickable database applications and documents, runnable even
directly from a CD-ROM.
One Programmer's experience
I found the dtF/SQL API definitions and documentation, provided in PDF format, to be
very readable. There are over 400 pages which describe the installation, data types,
high level API, and low level API. Each API has a usage example as well as any
restriction or limitation of the API.
The Macintosh distribution comes with the application, dtF/Admin, which allows one to
create and experiment with a database to see the effects of SQL queries. This tool is a
great way for beginners to learn how to create database schema or test SQL queries on
real data before committing these queries to source code. If you mess up the database,
just throw it in the trash and start over; you won't have an enraged database
administrator breathing down your cubicle. The dtF/Admin application allows you to
save scripts so that the database can be recreated and repopulated in seconds.
Of course, this can be done under program control as well. I keep my database schema
scripts as STR# resources, then, with a New command, a new database can be created
anytime.
At first I was disappointed that I couldn't write the equivalent of Sybase stored
procedures to facilitate queries. I then realized that I was equally well off not being
able to write and invoke stored procedures, because I was able to implement these
procedures as C or C++ routines. I found that where I had become accustomed to
writing complex SQL select statements with extensive join clauses, I could, with
dtF/SQL, create a couple of separate workspaces, then use nested for loops to perform
the same queries in a faster, much more logical fashion.
For those of you not familiar with SQL, the implications of this are significant. The
SQL join command to resolve relationships takes lots of memory. In contrast,
successive select statements which use the results of one SQL statement as input to the
next SQL select statement takes almost no memory at all and with dtF/SQL is probably
faster than doing the original join. The bottom line here is that one does not have to
become an SQL guru to use dtF/SQL effectively. Arguably, such expertise may even
work against you. The web-based, downloadable distribution comes with several
complete, non-trivial example programs which demonstrate the usage of almost all of
the APIs.
If Only...
dtF/SQL's implementation departs from the Macintosh application & document
paradigm in one respect. In release 1.6 the database is actually two files, a database
file and a separate file used to store BLOB data. This can present some challenges to the
double-clickable document interface. The application must determine the location of
the other file and then open it. For situations where a BLOB file is not required, an
empty BLOB file can be created on the fly and opened, then deleted when the database
session is over. If a BLOB file is required, one can use a Windows and Unix trick and
require that the data file and BLOB file have the same name but require that the BLOB
file end with the word "BLOB". Not truly the Macintosh way of doing things, but it
works well enough.
The requirement of a separate BLOB file will be removed in release 1.8 which is
expect to be available in early 1998. Then BLOB data will stored with all other data in
the single file.
Availability
The dtF/SQL object library is available for Macintosh System 7.x and Mac OS 8 (68K
and native PowerPC). MPW C/C++ , Symantec C/C++ and CodeWarrior C/C++ are
supported. Separate versions for HyperCard, SuperCard, AppleScript, MacHTTP CGI,
Smalltalk Agents and other environments are available. dtF/SQL also is an integral
component of the Internet-enabled client/server development system from Pictorius,
Inc. and the Oberon/F-based client/server system from microsystems. The dtF/SQL
engine is also available for DOS, Win16, Win32, NT, OS/2, SUN OS, HP/UX and Linux.
You must purchase support separately. It is provided by e-mail and is timely and
informative.
The documentation is supplied as PDF files which can be read with Adobe Acrobat. I
would have preferred hard copy, especially for the API definitions when first learning
the library. On the other hand, these APIs are few and are learned quickly. The code
examples are excellent and non-trivial. There also are some third party examples
which range from to obvious to obfuscated.
The CodeWarrior CD-ROM distribution contains a demo version of this product, dtF
Lite, as well as some experimental PowerPlant classes which encapsulate the dtF/SQL
API. The standalone database tool, dtF/Admin, also is provided and is very useful of
anyone who wants to learn more about relational databases and the SQL syntax in
general
The database and BLOB files are binary compatible between Macintosh, PC, and Unix
platforms, making the dtF/SQL engine and database files a perfect candidate for
distribution on CD-ROM.
There are ODBC drivers for dtF/SQL for Win16 and Win32; Macintosh ODBC drivers
will be ported shortly. Support for Java JDBC is under development and should be part
of the upcoming version 1.7 release.
Scalability
Once you have deployed your application, your users may come back and ask about
scaling up so that the members of their organizations can use your application to
examine and modify shared data. Does this mean that you will have scrap your work
and learn to use Sybase, Oracle, or ODBC libraries? No! dtF/SQL is available in a
client/server configuration; so, as a developer, you add a few lines of connection code
and relink your existing source with the dtF client library. Your customer purchases
and runs the dtF/SQL server for their database files (licensed separately from sLAB,
downloaded from the web with a password). They can now use your newly relinked
application, with its familiar UI, to access and share their data. The dtF/SQL LAN
server runs on Macintosh, Windows 95/NT, and some Unix platforms. Version 1.7
will allow any dtF/SQL client to connect to any dtF/SQL server.
Recommendation
I can unconditionally recommend the dtF library to any programmer who wants to
develop and deploy solutions which deliver performance and simplicity to the
customer. As for robustness, let me add in conclusion that I have crashed about a
thousand times during development and have never had a dtF/SQL database file become
corrupt as a result.
Pricing
All single user non-commercial dtF/SQL products are free and can be downloaded from
the web, including documentation. There are no restrictions such as time-outs or
maximum record sizes with the downloaded product. Non-commercial uses of the
engine are also free. Distribution of commercial products are priced on a unit basis.
Large volume pricing can bring the price down to 1 DM (about $0.65 US) per unit.
For server pricing contact sLAB directly by e-mail or phone, available from their
web site.
Useful URL's
http://www.slab.de/uk/homepage/index.html is sLab's home page, in English. All dtF
products, commercial and non-commercial, are available from this site.
______________________________
Editor's Note
sLab has made an astonishing offer to the programming community at large - a free,
high end SQL relational database for non-commercial use. After I read this review, I
trotted over to their home page and downloaded all the stuff I needed to look at this
product. As Dr. Gilbert indicates, this is a super package. This is a great opportunity to
learn and have fun with no investment, and presumably sLab will reap the appropriate
benefits when we all write our killer apps based on their libraries.
I had never dubbed around with an SQL-based package before, and asked Will to
recommend an introduction to the language. The book he recommended is quite readable
and informative. It's only shortcoming is that the accompanying CD is Windows only,
but many of us now have a Wintel machine or an emulator program, and I won't tell
anyone if you won't. ;-)
The Practical SQL Handbook, third ed., 1996, by Bowman, Emerson, and Darnovsky.
Addison Wesley Developers Press, Reading MA. ISBN 0-201-44787-8.
-Ed Ringel
______________________________
William Gilbert, http://www.informagen.com/cv/Gilbert.html, is a biochemist by
training as well as a computer professional. He provides informatics solutions and
scientific consulting to startup biotechnology companies. He also holds a research
professorship at the University of New Hampshire in the Department of Biochemistry
and Molecular Biology.