Fwd: PostgreSQL on Parrot

SFPUGers -- all of yous!

I heard back from Dan Sugalski about how he's using PostgreSQL with
Parrot, the VM that will be used by Perl 6. Here is the description he
sent me. Enjoy!

David

Begin forwarded message:

> From: Dan Sugalski <dan(at)sidhe(dot)org>
> Date: January 29, 2004 1:31:18 AM PST
> To: David Wheeler <david(at)kineticode(dot)com>
> Subject: Re: PostgreSQL on Parrot
>
> At 5:48 PM -0800 1/26/04, David Wheeler wrote:
>> Hi Dan,
>>
>> I'm helping the San Francisco PostgreSQL users group to give a
>> presentation to the SF Perl Mongers, and thought it'd be cool to tell
>> them a bit about how you're using PostgreSQL with Parrot. Can you
>> spare a few cycles to give me the overview and highlights that I
>> could use in a five minute talk? All credit goes to you, and I think
>> the Perlers would find it really cool!
>
> Sure.
>
> What I'm doing is completely rewriting the back end and compiler for
> an 80s vintage 4GL called DecisionPlus. This thing has an integrated
> character terminal interface and ISAM database. That DB has some
> limits we're hitting at work, mainly filesize and keycount problems.
> (We max out at 2M records in our biggest database)
>
> So, since we needed to replace the database, we went looking for
> something that failed to suck, and that we could easily layer on an
> ISAM interface so we could easily write the compiler. All the programs
> depend heavily on the ISAM nature of the database, and there's no way
> we were going to rewrite all the programs.
>
> We originally looked at the Berkeley database, as it was ISAM-ish, and
> had some failover capabilities that we liked. (Failover and
> replication are nice things :) Unfortunately it isn't fielded, which
> made it a pain to use in other programs--we'd need to have library
> code enforce proper fielding and index updates, and that was more
> fragile than we were comfortable with. It also made multi-language
> access really difficult, since we'd have to do the fielding and index
> updating in libraries for any other language that'd access the
> database.
>
> Since that wouldn't work, we had to find something else that did. We
> figured out how to make an SQL database act like an ISAM database by
> playing games with triggers and synthesized key fields which let us
> actually use an SQL database. Each table in the old system had a set
> of keys and one single index on that set of keys, and used that index
> to move forward and backward record by record in index order. What we
> did is added a synthesized key field in the SQL database that was made
> up of the key fields the old system used all slammed together. We
> could search forward and backward in order by single record by just
> doing selects ordered by the synthesized key, and as long as the
> runtime system the compiler used kept track of the synthesized key for
> the last record it saw, we could use that for the select to move
> forward and backward (looking for records, in synthesized key order,
> whose key was larger or smaller than the key of the last record we
> loaded).
>
> We needed the triggers because we needed to guarantee that the
> synthesized field was always correct. While we could use the database
> library to guarantee that, one of the things we were going to get from
> using an SQL database was that we could *stop* using the old language
> in places (reports and web stuff mainly) but since that field
> absolutely had to be correct so that the old stuff still worked
> properly.
>
> The old system we were using also had alternate naming systems for
> records and a couple of files that had multiple hard-linked names (the
> old language only allowed one record loaded per file, so when we
> needed two, we put in a hardlink so we could load in two records from
> the file, as the system thought they were different files. Nasty, but
> functional. Barely) so we also needed writable views.
>
> Writable views and triggers meant that we had two choices--one of the
> commercial DBs or PostgreSQL. We generally prefer the open source
> solutions, so PostgreSQL was the way we decided to go.
>
> What I did was to use the postgres.pasm that comes with parrot, that
> uses parrot's NCI interface to connect to libpq. Then I wrote a
> higher-level runtime library that presented the old ISAM interface on
> one end and used the routines that postgres.pasm exposed on the other.
>
> --
> Dan
>
> --------------------------------------"it's like
> this"-------------------
> Dan Sugalski even samurai
> dan(at)sidhe(dot)org have teddy bears and even
> teddy bears get drunk