queries on mysql

[Derek Atkins]

Phil,

Phil Longstaff <plongstaff at rogers.com> writes:

> I will put some info on the wiki.  Give me a day or so.

I would prefer you didn't.  If you supply instructions then that implies
an implicit acceptance in the practice.  I believe we should continue to
have a hardline stance that we do not support modification of the
database from under the GnuCash API.  More below..

> From: Fred Verschueren <fvsc at fremar.be>
>
> Ls,
>
> I'm currently working with version 2.3.17 with mysql as database.
>
> I need to do a lot of changes in my accounts, moving a lot of entries from one 
> account to another.
>
> Now I was wondering if this could be done with some queries (SQL) on the 
> database.
>
> I'm wiling to investigate this and if I have a solution give this back to the 
> community but to do this investigation I need to know the layout of the DB: what 
> is the meaning of each table, the links between the tables aso.
>
> My questions:
>
> - is this investigation already done and what is the outcome?
> - where can I find the needed information about the DB.

We do not support modifications to the database from outside the GnuCash
API.  The reasoning is that we have a bunch of data constraints that are
not encoded in the database (and indeed cannot be encoded in the
database).  For example, how do you encode that all transactions must be
balanced?  Or that the parent of an account must have an appropriate
account type?

While it *is* possible to modify the data directly (this is a computer,
afterall -- the data is there), we neither recommend nor support doing
so, because it's quite possible to make your data unreadable, or worse,
readable with subtle errors.

I hope this answers your question, even if it isn't what you wanted to
hear.

Now, you COULD write a scheme or python script (or a C program) to make
the changes for you....  This would allow you to use the GnuCash API and
not violate the constraints.

Another thing:  if you want to move *all* the entries from one account
to another then just delete the account and GnuCash will ask you where
you want to move them.

Good Luck,

> Kind regards,
> Fred Verschueren.

-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