SQL backend for GnuCash 2

[Daniel Espinosa]

2006/10/26, Ivars Grinbergs <mestule at inbox.lv>:
> Derek Atkins wrote:
> > "Daniel Espinosa" <esodan at gmail.com> writes:
> >
> >
> >>> 1) We don't need an AccountType table.  AccountTypes are not data,
> >>>    they are encoded in the application.  There's no reason to add
> >>>    them to the database because they are constants.
> >>>
> >>>
> >> If usefull if you want a strong data integrity done by the Database
> >> server, and if you want to share with others programs (I plan to
> >> develop some one for the desktop)
> >>
> >
> > You can't get enough data integrity from the database.  For example,
> > you cannot define the database in a way to enforce balanced transactions.
> >
> >
> Theoretically, it is possible by means of triggers and stored
> procedures. But I'm not sure that many DB engines support them and if
> support, then in different ways and at different degree. Therefore I
> don't think it is worth to bring existing logic (that checks and
> enforces certain integrity) from application tier (single point) to DB
> backend tier (potentially many different  implementations  for different
> backends).

I'd checked the SQLite 3, and it doesn't support foreing keys, but
triggers. Then may be in the future, after finish the SQL backend
support in GC, we can create a "gnc-data-server" a la Evolution, in
order to read, insert and update some records in transactions or even
execute reports.

> > If you prefer, feel free to make the "account type" an "enum".  But I
> > still think an 'integer' is sufficient; gnucash already knows what the
> > account types are.  Keep in mind that there are LOTS of these "enum"
> > types all throughout the code.
> >
> >
> For "enum" enforcement at DB level there are CHECK CONSTRAINTs in
> standard SQL. Of course, those will not give meanings of "integer"
> values, but to solve this, there is option to define VIEWs in SQL.
>

SQLite supports the CHECK constraint, and may you can add a list of
integers or check if fit in a range of numbers.

Even the last and trying not to take too much time, at moment, in the
DB engine specific implementation, I leave out the account_type table,
and just leave the ones that the user could use to create his own,
like invoice_type to allow the user to create a custom list of
invoices he want.

I added the accounts_receivable and accounts_payable, just as a note
to cover in some way, if any want to comment me, I think this is just
a list of transaction_split with just more information, but not sure
becouse they aren't taken in account when GC calculate the Balace.

Attached you'll find the 0.3 version for the schema.

-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (entrámite, pero para los
cuates: LIBRE)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: GnuCashDBSchema-0.3.dia
Type: application/x-dia-diagram
Size: 8627 bytes
Desc: not available
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20061026/771dc47f/attachment.bin