DB design document

[linas@linas.org]

It's been rumoured that David Merrill said:
> 
> On Sat, Dec 16, 2000 at 09:52:14AM -0600, Rob Browning wrote:
> > David Merrill <dmerrill@lupercalia.net> writes:
> > 
> > > Please take a look at the latest revision of my design document and
> > > give me some feedback.
> 
> >     * design an API that's exactly what we need, regardless of the DB
> >       involved, and plan to just have a server-side gnucash daemon
> >       that manages the DB interface, dealing with any limitations of
> >       the DB on the server-side before forwarding data back and forth
> >       -- for example, this means that you can pretend like you have a
> >       "rational" implementation.  It also means that you can have more
> >       flexibility in other areas because you can perform actions on
> >       the server side.  This approach, does, however, have plenty of
> >       drawbacks...
> 
> My thoughts, how they ramble...
> 
> I'm leaning toward using the approach of #3, to implement an API that
> could have any db engine behind it. Initially the db would be Postgres
> to avoid the difficulty of a db abstraction layer. A year from now I
> hope we will have more powerful db-agnostic layers available. We can
> revisit the topic then.

I strongly urge an examination of GEAS
http://gnue.org/index.cgi/arch.geas?package=.GEAS
as an abstract data interface.

It used to be called 'libdbi', and was a part of a gnome sql-browsing
tool (that was sql-server agnostic).  It was re-hacked to get rid of 
gnome-isms and gui-isms, so that it could be used on servers.  Now
renamed to geas.

One additional 'bonus', in my mind, is that it is abstract to
interface to any data sources, not just sql.  This might be useful
when accessing e.g. stock price data from a variety of sources.
This may be overkill for gnucash, but fundamental for data-movement
business applications.

disclaimer: I've never coded with geas ...

> This allows me to focus on the api, and what's behind that api can be
> improved and expanded later for multiple databases.

If geas is overkill or too beta to use, then we should re-examine
the linux equivalents of ODBC, and use those.  That should cover both
mysql & postgresql in one shot.

> >   [ parens group things in a single process, double bars indicate
> >     physical machine boundaries, and dotted lines indicate remote
> >     conections. ]
> > 
> >     (server) <-> (gnc_proxy) || <- - - - - -> || (gnc_engine <-> ui)

In this one, the dotted line requires the invention of a gnc protocol
to send over the wire.  Will it be xml?  Will it be corba?   
will it use the http transport mechanism, and have a gnc mime-type
for the data?  Is someone going to (gasp) open a socket, and write 
stuff across it (ietf RFC coming soon)?  Ugh.

If this is the route, I really, really, really encourage not
reinventing the wheel:  i.e. use http/xml combo, or use corba.

> >     (server) || <- - - - - -> || (gnc_engine <-> ui)

here, the  dotted line is just sql.  Don't have to invent anything to
make this work.

> >   and we need to be thinking about what we *require* be done where.
> >   I.e. does the DB just serve up raw records or do we require it to be
> >   able to "do the math" in some cases?

well, you do have to ship the query (or set of queries) to the db:
you don't want it returning a millions 'raw' records.

I was envisioning having the engine act as a kind of 'cache' for the
db backend; its not a bad place to 'do the math', and validate data
integrity, before storing stuff back to the DB.  It can give instant
feedback to the user if something is wrong, instead of waiting for a 
round-trip or two to the server.

> Then we can do whatever we need to wrt securing
> the data over the wire, too.

If its http, then we have ssl built in.  If it sorba, then there are
security modules, but I don't know the  status.  If its sql, and
whatever odbc library doesn't already have ssll built in, I can show 
you how.  Adding ssl support is pretty durned easy. 

Authentication is harder.

--linas