Dr. Dobb's Journal December, 2004
Not long ago, my frustration with our existing defect tracking and requirement documentation methods led me to write a unified Requirements And Issues Database program, appropriately named "RAID."
Because our products are written in Delphi and most of the programming I've done has been in Delphi and Borland Pascal (all the way back to Turbo Pascal 1.0 for CP/M), selecting Delphi as my development tool was easy. The harder question was which database to use. I needed something that was free to deploy so I wouldn't have to request funding or official MIS approval. I also needed to be able to install it on my home machine, since that's where I would be doing most of the programming. That meant the DBMS couldn't require a Windows Server edition or dedicated Linux box.
At first, this sounded like a job for an Access ".mdb" file. However, the program also had to be scaleable. I knew from experience (our existing defect tracking system used Access tables) that you can't put more than about five users on a system built with Access or Paradox tables without hearing those dreaded words "would everyone please get out of the program so we can rebuild the database" a couple of times a week.
As it turned out, the choice for a database also ended up being easy—I decided on Firebird. Descended from Borland's InterBase 6 (released as open source in 2000), Firebird is a freely available relational database currently under the auspices of the Firebird Foundation (http://www.firebirdsql.org/). Because of its Borland roots, Firebird is easy to work with from native Delphi components (including IBX, DB Express, SQL Links, and third-party libraries) and other languages (it has ODBC, JDBC, .NET, OLE-DB, DB Express drivers, and works with many InterBase-compatible drivers, too). Firebird runs fine on Windows 98 and upwards, doesn't need a Windows server edition (XP Pro is fine), and runs on Linux, UNIX, and Mac OS X.
Moreover, Firebird is straightforward to deploy. The installer is a 3-MB download, and takes less than a minute to run; clients can be installed with as little as a single DLL file. There's even an embedded version. All you have to do is replace "FBClient.dll" with the embedded database DLL, and your application works with a local database file without installing the server version at all.
Best of all, Firebird requires little administration because there's really nothing to "tune." All you need to do is put the backup program in the scheduled tasks list, then forget about it. Nor, for that matter, are there nontechnical hassles such as hidden gotchas in the license terms. Unlike some other "open-source" DBMSs that want you to pay for if you ever decide to distribute your application, Firebird is free, with no strings attached. That was important to me because I wanted to keep my options open for selling RAID as shareware.
The first thing I discovered was that I needed some tools to define and alter the database. Firebird doesn't come with administrative tools because the Firebird Foundation doesn't want to undercut third-party tool developers. Instead, the "Quick Start Guide" (installed with the server) directs you to the IBPhoenix web site (http://www.ibphoenix.com/) to download tools. I followed the link and found at least a dozen administration tools, many of which are freeware, open source, or commercial tools with free (slightly limited) versions. I settled on IBExpert (http://www.ibexpert.com/).
Before starting to write RAID, I did some reading and experimenting and found that Firebird has just about every common DBMS feature—views, stored procedures, triggers, declarative referential integrity, domains, constraints, the works—and even some that aren't so common. For instance, Firebird has "Events" that let you tell connected application programs that something has changed. It also supports User Defined Functions (UDFs) that you create as ".dll" or ".so" files for adding string manipulation, math, date/time, and any other specialized functions you might need.
In RAID, it's important to keep track of when requirements and issues are first entered and when they're modified. In the old Access database, I'd have had to write code in each of the datasets' BeforePost events to assign the timestamp values.
In Firebird, you just declare a trigger:
CREATE TRIGGER REQ_TIMESTAMP
FOR REQ
ACTIVE BEFORE INSERT
OR UPDATE
POSITION 0
AS
begin
new.Modified = 'NOW';
if (inserting) then
new.Logged = 'NOW';
end.
Requirements in RAID belong to Deliverables, which belong to Projects. Since Deliverables have start dates, due dates, completion status, and so on, they need to be actual "entities" in the database. The problem is that until a project design is complete, you don't really know exactly what deliverables you're going to break the project into, so you need to be able to add codes for them on the fly and change them as needed.
In my initial design, it was awkward to have to define a new deliverable record every time I wanted to (perhaps temporarily) create a new deliverable code. However, when I changed it to allow deliverable codes to be entered into requirements manually, I couldn't use a "foreign key" to keep the codes in the Requirements synchronized with the codes in the Deliverable records. Still, I wanted to be able to rename a Deliverable record and have all the Requirements using it to have their DELIV field changed at the same time.
This problem was easy to solve using triggers:
CREATE TRIGGER DELIV_RENAME
FOR DELIV
ACTIVE AFTER UPDATE
POSITION 0
AS
begin
If (New.Deliv<>Old.Deliv)
Then begin
update REQ
set Deliv=New.Deliv
where Deliv=Old.Deliv
and Proj=Old.Proj
end
end
This code updates the REQ (requirement) table's Deliv reference to the new Deliv code wherever it finds the old one in a requirement of the same project. The only part of this I had to write was the stuff between the outer begin/end block; the rest was created by IBExpert.
Events are another use for triggers, letting Firebird notify the connected clients when something happens. For instance, RAID has lists of Projects and Users, and various lookup lists for things like Issue Frequency (how often an Issue is encountered by a customer—once per day, once per month, or whatever). While it didn't make sense to requery these (mostly) static tables every time I needed to reference them in a drop-down listbox, I also did not want everyone to have to shut down to make changes take effect whenever we added a new project or user to the system. The solution was to use Events to tell the clients about the changes:
CREATE TRIGGER PROJ_MODIFIED
FOR PROJ
ACTIVE AFTER INSERT
OR UPDATE OR DELETE
POSITION 1
AS
begin
post_event 'PROJ_MODIFIED';
end
What this does is it detects any changes to the Project table and alerts all connected clients of the change by posting the event.
To handle this on the Delphi side, you drop a TIBEvents component on the form, type PROJ_MODIFIED in its Events property, double-click on the OnEventAlert event, and, in the event handler Delphi creates for you, type:
qryProj.Close;
qryProj.Open;
qryProj.FetchAll;
Presto. The qryProj component automatically maintains a current list of all the projects, updating only when needed.
Obviously, I'm only scratching the surface of what you can do with Triggers and Events, but you can see just how easy this is with Firebird, especially with a powerful tool such as IBExpert writing half the code.
If you are experienced with other client/server databases, you might notice the POSITION field, which lets you control the order these happen in, and the fact that you can make them ACTIVE BEFORE or ACTIVE AFTER the changes they trigger on. As I understand it, the lack of these features is an annoying aspect of some commercial SQL Servers.
Firebird supports two different kinds of stored procedures—those that you invoke through Execute statements to do some work on the server, and procedures that can be used like a table in a select statement, returning result sets.
Here's a stored procedure that returns the same data as the PROJ table itself:
CREATE PROCEDURE PROJ_PROC
RETURNS (
PROJ VARCHAR(16),
DESCR VARCHAR(120),
USERS VARCHAR(1000),
BASELINED DATE,
COMPLETED DATE,
MODIFIED TIMESTAMP)
AS
BEGIN
FOR SELECT PROJ,
DESCR,
USERS,
BASELINED,
COMPLETED,
MODIFIED
FROM PROJ
INTO :PROJ,
:DESCR,
:USERS,
:BASELINED,
:COMPLETED,
:MODIFIED
DO
BEGIN
SUSPEND;
END
END
(Again, I didn't write any of this—IBExpert did it for me.)
The first thing you should ask is "what's the point?" Why write:
Select * from PROJ_PROC
instead of just
Select * from PROJ ?
The answer is that there's a rich procedural language you can use between the begin/end pairs to give you more interesting result sets. For example, I wanted the Project grid-view to be able to show in a single field a list of the deliverables that belong to that project. (There might be some way to do that in a query, but I don't know what it is.) This is easy with the following procedure (added code is in bold):
CREATE PROCEDURE PROJ_PROC
RETURNS (
PROJ VARCHAR(16),
DELIVS VARCHAR(1000),
DESCR VARCHAR(120),
USERS VARCHAR(1000),
BASELINED DATE,
COMPLETED DATE,
MODIFIED TIMESTAMP)
AS
declare variable
TmpDeliv VarChar(12);
BEGIN
FOR SELECT PROJ,
DESCR,
USERS,
BASELINED,
COMPLETED,
MODIFIED
FROM PROJ
INTO :PROJ,
:DESCR,
:USERS,
:BASELINED,
:COMPLETED,
:MODIFIED
DO
BEGIN
DELIVS = '';
For
select DELIV
from DELIV
where PROJ = :PROJ
order by DELIV
into :TmpDeliv
do
begin
if (Delivs > '') then
Delivs = Delivs||',';
Delivs=Delivs||TmpDeliv;
end
SUSPEND;
END
END.
Now, writing Select * from ProjProc" includes a column named Delivs with a comma-separated list of all of the deliverable codes in each project.
One of the major improvements of the my RAID program over the defect-tracking system it replaced is that I used a Rich Text (RTF) memo component for the note editors—one that can embed graphics such as screen captures. (For this, I used WPTools, http://www.wpcubed.com/.) Consequently, instead of trying to describe some strange visual anomaly, the support staff can paste screen captures into the Issue notes. We also use this in requirement definitions to embed prototypes of dialogs, reports, and the like. Plus, it's nice to be able to use strikeout and underline attributes to indicate parts of requirement definitions that have been changed.
However, RTF embeds a bunch of "annotation" around text, which makes it difficult to search the memo fields for specific text strings. For example, if you describe a menu option with hot keys shown with underlines:
"File > Backup"
you end up with ASCII data that looks something like:
\ul\f0\fs20 F\ulnone ile > \ul B\ulnone ackup\par
in the blob field of the database. Obviously, if you search for "backup," you won't find it in that. What I needed to be able to do was search RTF Blob fields by converting them into plain text first. The source code to do that comes with the professional version of WPTools, in a function named WPToolsRTFtoANSI. The trick was to get that into the Firebird engine so RAID didn't have to pull 800 MB of RTF blobs over the network to search them locally. This turns out to be straightforward.
On the IBPhoenix site, I found 14 different UDF libraries, all open source or freeware, and all with source code included. The FreeUDFLib library was written in Delphi and contained lots of blob functions, so it was a perfect starting point.
After a bit of study, I was able to add the following code to it:
function BlobRTFSearch
(Blob: PBlob; sz: PChar):
Integer; cdecl;
var
St:String;
begin
Result := 0;
St := GetBlob(Blob);
WPToolsRTFtoANSI(St, TRUE);
If (StrPos(PChar(St),
StrUpper(Sz)) <> NIL)
then Result := 1;
end;
And of course, I had to declare the function in the Exports clause in the main project file by inserting:
BlobRTFSearch,
in the list with all the other exported functions.
Interestingly, I discovered that converting a blob to a string and searching it is about twice as fast as using the built-in Containing function (most likely because Containing has to worry about international character sets, while StrPos doesn't).
With this in mind, I added this function for cases where I needed to search plain-text blobs:
function BlobSearch
(Blob: PBlob; sz: PChar):
Integer; cdecl;
var
St: String;
begin
Result := 0;
St := GetBlob(Blob);
St := UpperCase(St);
If (StrPos(PChar(St),
StrUpper(Sz)) <> NIL)
then Result := 1;
end;
I've thought of other interesting ways to use this functionality. For example, in my next version of RAID, I'm adding the ability to count the number of matches, rather than just returning a "1" if any match is found. That would let me do this:
Select
BlobSearchCt(Notes,'DB') +
BlobSearchCt(Definition,'DB') as matches, *
from REQ
order by 1 desc
to return the requirements containing the word DB in their Definition and Notes fields, sorted by the ones with the most references first.
RAID has been running for over a year. We've entered over 6500 requirements and almost 3000 issues/defects. The database file is now over 800 MB (we really like embedding those screen shots!), but it backs up in under 30 seconds. (You don't have to kick off other users while you do a backup, by the way.) We haven't had any database corruption problems, and it has only been shutdown to upgrade to newer versions of Firebird and replace the server computer.
We started out on a 233-MHz Pentium II with 64 MB of RAM, and it ran just fine. But with 15-25 concurrent users, we decided it was worth $1000 to buy a 2.8-GHz Pentium 4 machine with a GB of RAM and a Western Digital Raptor drive. Performance is now great. RAID can return a list view of all 6541 requirements (without their blob fields) in under a half second. Typical lists of a few hundred records at a time are so fast you can't even time them. Even RTF Blob searches of the entire database come back in under 10 seconds.
Overall, I haven't found any reason to regret my choice of Firebird for this project. Granted, many other database engines support User-Defined Functions in one form or another, can do Stored Procedures that return result sets, and support Triggers; although other than InterBase itself, I haven't seen another one that supports Events, one of Firebird's coolest features.
However, I don't think there are any others that feature Firebird's simplicity, power, ease of installation and deployment, liberal license terms, and price/performance ratio as well as the genuinely friendly, supportive, and responsive nature of the members of the Firebird community.
DDJ