From: scott@corybant.rmnug.org (Scott Meyer)
Newsgroups: comp.lang.perl
Subject: Using perl for databases - experiences requested
Keywords: database
Date: 5 Sep 92 04:08:19 GMT
Organization: Rocky Mountain NeXT Users' Group


My question is this:

What sort of experience have people had using perl to implement databases?   
What pitfalls (performance, etc.) have been discovered, how were they  
surmounted.  The motivation for this posting is my need to decide between  
continuing with an ad hoc perl-based approach (which has been quite effective  
to date) or scrapping it and going with a commercial solution.  Unfortunately,  
things seem to be pretty polarized between those who refuse to consider  
anything other than a "real" database and those who can't conceive of a reason  
not to use a bunch of scripts... :-(

To guide the discussion a little bit, I'll describe the database that
I have implemented in perl.  Basically, about 8 months ago, I needed a system  
to keep track of bugs (sad but true).  Begin familiar with perl and unix tools,  
I was able to cobble together a database which solved my problems and did so  
nicely enough that most of the other developers at work adopted it too.   
Basically, each bug (read record) was kept in a separate file under RCS.  The  
name of the file (a number) was the bug id.  Inside the file, things looked  
something like this:

%{  # beginning of record
%STATE open   # the bug is open
%TITLE This is a sample bug
%DESCRIPTION
	This is a description of the sample bug, it can go on
	for several lines and may contain arbitrarily formatted
	ASCII text
%END_DESCRIPTION
... # lots more fields elided
%}  #end of record

The way in which one could parse this stuff using perl is fairly obvious: in  
perl the record is just a set of variables, $bug_state, $bug_description, etc.
when you parse the beginning-of-record sentinal, these variables are set to
their default values, as records are read, the field is parsed and the  
corresponding variable set.  At the end of the record, all the record variables  
are set and the program gets to do whatever it wants to with the record  
(usually write it out or discard it based on some criteria).  Reading a record  
is encapsulated by a function called get_rec which retreives the next record on  
the input stream, writing by a function called put_rec which writes the current  
record out (to standard output).

Using this basic read/write functionality, it is easy to construct filters  and  
formatters which can be piped together.  The pipe

	dump_bdb | f_open | f_after 92 08 01 | fmt_brief

would create a brief report of all open bugs filed after August 1, 1992.  The  
filters represent ANDs in the query.  One can use back quotes (or temp files)  
to handle ors.  The filter mechanism is not particularly fast but can generate  
moderately complex reports from a database with several hundred records in a  
matter of seconds.  There is plenty of room for optimization (using assoc.  
arrays or linking "interesting" records (files remember) into separate  
directories so it seems like this scheme could be made to work reasonably well  
for thousands of records.  Modifying records is very simple, just check out the  
file and edit it.  There are convenience scripts that make certain  
modifications automatically or allow you to edit only certain fields.

The problem is that we now are in need of more than just bug databases.   
Specifically, we need a whole set of integrated databases to handle, bugs,  
tech-support contacts, technical issues, etc.

The commercial systems I have seen are far from plug-and-play, are hard to  
change (add a new field, etc.), and are decidedly unix-hostile (no more grep,  
vi, etc.).  The up-side is that they can be much more powerful (nice gui  
interface, very tight integration) than my home-brew, and, they are implemented  
by *someone else* (I have no intention of spending my life maintaining  
databases).

So, I have lots of questions:

1. Has anyone used perl to implement a multi-user database that handles  
thousands of records?  How well did it work?  Do you spend your whole life  
maintaining the thing.

2. Does anyone know of a really bang-up bug/issue/call tracking database on any  
platform?  If there were such a thing in the PC world we could easily be  
convinced to put a PC on everyone's desk.

3. Where will my scheme break when I extend it from hundreds of records to  
thousands?

4. Other comments, experiences?

Please email me, I'll summarize responses.
Thanks in advance,

-Scott

PS. If anyone wants my current bug DB code, this can probably be arranged.   
Before everyone requests it, I should point out that it is really very simple  
and probably quite boring reading to boot.
-- 
Scott Meyer
scott@corybant.rmNUG.ORG - NeXT mail is welcome.
Newsgroups: comp.lang.perl