Re[2]: Database design goals...

["John C. Peterson" <jcp eskimo com>]

On Fri, Nov 09, 2001 at 11:26:54AM -0500, mike_phillips urscorp com wrote:
> > DESIGN GOALS: The functions that read or perform searches of the
> > reformatted (or fplan format) files should be efficient in both
> > speed and storage resource requirement terms. [...snip...]
> 
> Agreed. I've started off using Postgres because that's the database
> I know the most. Once I've got the the conversions done I can start
> to trim down the information to the subset that we need. 

Mike,

  I'm not too worried about this issue, (you did say that you
would design the functions to access the fplan format database in
a manner where they were independent of the package, library
being used). After re-read of some of the other posts, and
thinking more about the requirements, I think several of us are
skeptical about the benefits of using PostgreSQL for the fplan
side of things (I agree it makes *perfect* sense for working with
the DAFIF, NASD files itself).

  IMO the biggest negative of any of these large client->
server based packages (PostgreSQL, etc) is the heavy burden
it places on the end users during the installation process. I
think it's above the abilities of many users that happen to
be newbies to Linux, and many experienced users will just say
"forget it, too much trouble". Starting and configuring the
server usually requires a root login, so many users can forget
the idea of having a copy at work. These penalties are hard to
justify when you take a look at the real requirements (IMO they
are pretty basic, a small fraction of what these big packages
provide). Resource requirements another big negative. I'd bet
money that it will be several years yet before it is possible to
"shoe horn" Postgress into a plamtop, (speaking as an owner,
I know the answer in the case of the Agenda VR3 is no way).

  I think it's much better to put priority on support for some
type of fully embedded approach (meaning everything needed can
be linked into fplan, no server required). There are multiple
solutions that can do everything that's required, place no
installation burden on the end user, and have very minimal
resource requirements.

  Anyhow, I'm not cemented to the idea that Berkeley DB is
needed for this, there's plenty of other workable solutions.
I did some more research, and rewrote the implementation section
for the fplan side, a summary follows. Let me know what you
(all) think of these options...

================================================================================

 Database files:

 A) The raw data file consists of the concatenation of
    all the the airport, navaid, airway, airspace boundary, ...
    records (variable length, 0 byte terminated). To help
    distinguish the type, the leading field of each record
    is a type code (such as APT: NAV: AWY: etc.)

 B) Index file(s) that consist of pairs of keys (corresponding
    to fields from the raw data like; identifier or common name)
    and the associated pointers (disk address) into the raw data
    file described in A. In the lingo of some database packages
    these files(s) *are* the "database files". The package is
    used to lookup a desired (key, pointer) pair, and then the
    pointer is used to read the associated record from the raw
    data. Depending on the package used, and the design of the
    syntax of the keys themselves there could be several or
    perhaps just one index file.

 DB access functions (options)

 1) The "home brew" approach, improving on what's already in
    fplan. (The current code is equivalent in concept to a
    btree index, where the database *itself* also serves as
    the index). I had leaned towards this at first. Something
    a little better than this might be helpful because the app
    designed for users to build thier own personal dB will need
    the ability to insert new and delete old records from their
    personal dB. This is still "do-able" but there's so much
    out there that already does this, why re-invent the wheel?

 2) Use one of the existing open source B-tree libraries. (For
    example, see the btree package from vol3 of comp.sources.misc
    and the patches from vol10.  Small in size, < 1000 lines or
    so of C.  In my mind, this is the minimal solution).

 3) Use the GNU dbm (gdbm). Available on most Linux based
    systems, so building from source is still convenient.

 4) Use Berkeley DB (developed after dbm, ndbm, gdbm). Widely
    used, so it has been thoroughly tested. The original release
    1 is available on most Linux systems, so building from source
    is still reasonably convenient (note the internal formats
    of DB1, DB2, DB3 are NOT compatible with each other).
    Probably the best performance of all options mentioned
    (both btree and hash access methods are supported).

================================================================================

If anyone just wants something to fiddle with to get the idea,
I would suggest playing with the code from option #2. I can e-mail
the shar files to anyone interested.

Regards, John

-- 
 ___|___ | John C. Peterson, <jcp eskimo com> | "Once you have flown, you
  -(*)-  | will walk the earth with your eyes turned skyward, for there you
  o/ \o  | have been, there you long to return." -- Leonardo da Vinci.