SQL backend for GnuCash 2

[Derek Atkins]

Quoting "Jim C. Nasby" <decibel at decibel.org>:

> On Fri, Oct 27, 2006 at 11:51:50AM -0400, Derek Atkins wrote:
>> "Jim C. Nasby" <decibel at decibel.org> writes:
>>
>> > You'd be better off creating a stored-procedure-based interface and
>> > having it enforce the semantics.
>>
>> Except not all DBs that we want to support have stored-procedures,
>> so unfortunately that's a non-starter.
>
> Well, it depends on how you structure the code. If you put the business
> logic checks near the database layer (which seems the best place to put
> them), then it's a fairly simple matter of having that code pass right
> through to the database for databases that support stored procedures.

The code is already structured.  It's not like we're building a new
application from scratch -- we're retrofitting an existing application
with a database-storage backend.   The logic checks already exist in the
code.

> Yes... it's more code, but here's something else that no one's mentioned
> yet: if you're going to support multiple databases, you're going to have
> a bunch of duplicated code. Even if you try and go for the lowest common
> denominator (which is seldom a wise idea), there's enough
> non-standardness between databases that you can't avoid duplicated code.
> Just trying to get simple schema DDL to work across three different
> databases can be tricky. And if you're not ready to have per-database
> code, you can pretty much kiss performance goodbye, because what works
> well on A will work poorly on B (witness the recent debate about GUIDs).
>
> So, something else you should really be looking into are tools that
> allow you to write your definition code once and then have it spit out
> all the database-dependant code you need. I believe there's some stuff
> out there now that does this, though it's also not super-hard to create
> We did it at a former employer by defining things in XML and then using
> XSLT to generate database code (including stored procs) for Oracle, DB2
> and PostgreSQL, C interface code, documentation, and some other things.

Keep in mind that gnucash already has a Postgres backend.  While I
wouldn't use it for real data, and it HAS bitrotted, it shows
that gnucash CAN have a DB Backend.  It was a proof of concept.
The problem, of course, is that the existing code wasn't extensible
(so when I added the business features there was no way to "plug them
in" to the existing SQL backend).

As for supporting multiple DBs, I disagree that it's an unreasonable goal.
MANY applications use DB-abtraction layers to let you work against multiple
DBs.

As for performance...   I think that's something to worry about later.

I think the #1 DB target is SQLite, and the #2 targets are split between
PG and MySQL.  *shrugs*

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available