GNOME Bugzilla – Bug 653147
RFE: reorder operations when deleting a transaction to allow foreign keys
Last modified: 2018-06-29 22:59:26 UTC
I'm using GnuCash 2.4.5 (svn r20528) on Windows and using the PostgreSQL back end. Since I'm a bit of a database geek, I added foreign keys to the schema just to see if they'd work (I understand that they're not supported). When deleting a transaction, I get an error saying "couldn't save to the database". I inspected the PostgreSQL logs (I've got statement logging turned on) and I see that GnuCash has tried to delete the transaction record before deleting the splits or their slots, which violates the foreign key constraint I added to the schema. This is the first error I've seen while using GnuCash with foreign keys in place, so I think you're close to being able to support foreign keys, assuming that's desirable to the project. The solution in this case, I think, is to just reorder the operations to delete the splits' slots, then the splits, then the transaction slots, and finally the transaction. (My instinct is to look at the code before filing this bug to see if such a reordering is even feasible but my employer frowns on such activities lest I become "tainted" so I haven't looked at the source. Sorry for the spam if there's some sort of structural problem preventing such a change.)
You're *way* ahead of us. We've just begun to write full test coverage of what one might call the "business logic" so that we can safely extract a proper database layer with a schema that we can normalize and set up with referential constraints like that. If you could check back in a year or so, I'm sure that your experience will be very helpful in redesigning the schema.
(In reply to comment #1) > You're *way* ahead of us. That's too bad. > We've just begun to write full test coverage of what one might call the > "business logic" so that we can safely extract a proper database layer with a > schema that we can normalize and set up with referential constraints like that. > If you could check back in a year or so, I'm sure that your experience will be > very helpful in redesigning the schema. Please involve me whenever it would be useful. I've been using GnuCash since version 1.something in the 2000s, first on Linux and now on Windows. I think it's great software and I'd love to be able to contribute to improving it. I was ecstatic when I discovered that the 2.4 series supports writing to a DB because I plan to query the DB from Excel to make tax time easier (my finances are complicated...); it would make me very happy to contribute to the longevity and quality of the PostgreSQL backend. Thanks, Ian PS I'm actually taking full advantage of PostgreSQL's rules system to normalize the schema without touching GnuCash's code: I've converted the accounts, books, commodities, lots, prices, slots, splits, and transactions tables to views on more-normalized tables in a different schema with rewrite rules to make the change transparent (yes, I realize this is an unsupported scenario). If you're a year away from normalizing the default schema, please reach out to me when you get there--I may have a normalized schema ready to go for you, at least for the parts of the system I use (personal bookkeeping, no business stuff).
Interesting approach. I didn't know you could do that in Postgresql. Have you looked at the ERD linked off of http://wiki.gnucash.org/wiki/SQL (Direct link: https://github.com/downloads/jralls/gnucash/gnucash_erd.png)? I've broken out all of the KVM uses into separate table entities and drawn as foreign keys the relations created in code. I'll leave this ticket open as a channel to let you know when we're ready to start normalizing.
(In reply to comment #3) > Have you looked at the ERD linked off of http://wiki.gnucash.org/wiki/SQL > (Direct link: https://github.com/downloads/jralls/gnucash/gnucash_erd.png)? > I've broken out all of the KVM uses into separate table entities and drawn as > foreign keys the relations created in code. Yes, I looked at it, but it was a little overwhelming--I only use the bookkeeping aspects of GnuCash, I don't use any of the business-related stuff, and I don't auto-import anything, so most of that diagram covers stuff I'm not using. > I'll leave this ticket open as a channel to let you know when we're ready to > start normalizing. Great. I look forward to hearing from you.
triage: perhaps we should close this "ticket" and point things at a more general place ? John, where do you feel is the right place today (presuming all of www, irc, usenet, mail, etc)
(In reply to Wm from comment #5) > triage: perhaps we should close this "ticket" and point things at a more > general place ? John, where do you feel is the right place today (presuming > all of www, irc, usenet, mail, etc) Right here is the right place. This is the only forum we use that has the requisite persistence.
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=653147. Please continue processing the bug there and please update any external references or bookmarks.