Re: SQL backend: Where do we store the password?
On 02-07-13 20:53, Christian Stimming wrote: Dear Geert or John or whoever knows this, where does gnucash store the database password for MySQL or PostgreSQL backend? It stores the database name, host, and username directly in the URI, which is also visible in the file history. The URI (without the password) is also stored in gconf and can be observed there, as well in ~/.gnucash/books as a file name. But where is the password? It is obviously stored somewhere, because it will not be asked for next time, and when I change the database password separately, gnucash will no longer open the book. But where...? Take this question as a confirmation that this fact isn't documented well enough. At least I didn't find anything in the wiki or in the source code. Regards, Christian ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel On platforms that have a system wide pasword manager, the password is stored in there. On linux systems, the gnome keyring is used. On OS X, Apple's keychain is used. On Windows I couldn't find any system wide password manager so there the password is not stored at all. The functions that handle storing and retrieving the passwords are in src/gnome-utils/gnc-keyring.[ch] They have proper doxygen descriptions which can be found here: http://svn.gnucash.org/docs/HEAD/group__GUIUtility.html Probably the choice of doxygen group is not too good and makes it harder to find these. GnuCash attempts to read the password from the keychain when a user attempts to open a database backed book and didn't specify a password. This happens in src/gnome-utils/gnc-file.c:675 If no keychain is available or no password could be retrieved from it, the user is presented with a password prompt. GnuCash attempts to store/update a password near the end of a open or save as post processing step. This is after the database calls to load or save the db were verified to be successful. This happens in src/gnome-utils/gnc-file.c:856 and src/gnome-utils/gnc-file.c:1481 This allows the user to open the database again in the future with the same password automatically. Until now that was in line with our security policy, but if you intend to protect the db with a password, it obviously shouldn't be stored by default. Instead I think this should be an option in the save/open dialogs. Does this help ? Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
Donald Allen donaldcal...@gmail.com writes: Some good news: Doing this the easy way first, I did a little manual pc sampling. I ran gnucash (today's trunk) under gdb, let it get to the point where it begins to load my data from postgresql, and periodically ctrl-c'd in gdb and copied the interrupted location and a backtrace to an emacs buffer. In the interest of saving space in this message, rather than just copying the result into the message (I assume that attachments are not permitted in emails to the gnucash mailing lists), I'll tell you that I interrupted execution 7 times and the first 6 were identical to this: 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 (gdb) bt #0 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 [snip] (gdb) bt #0 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 #1 0x7fffe86d55c0 in load_splits_for_tx_list (be=0xa5a300, stmt=value optimized out) at gnc-transaction-sql.c:238 [snip] Yeah, we should almost NEVER use g_list_append!! I still see 206 references to g_list_append in the source tree. We might want to take a look at all of those to see if they are ever handling lists of size 10. -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 warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Code formatting Re: SQL backend performance
Zitat von Phil Longstaff plongst...@rogers.com: http://lists.gnucash.org/pipermail/gnucash-devel/2009-August/026121.html and my commit r18675 recently. I didn't apply this to the full source tree so far in order not to destroy some people's diffs which are still waiting to be applied... I think the directory you're mentioning wasn't run through astyle recently, so that would explain this issue. I think the best way to handle this formatting is to create an options file (perhaps src/c-code.style) which is then used everywhere: astyle --options=~/gnucash/src/c-code.style *.[ch] Christian, could you create such a central file with the options you are using? Thanks for the idea. Indeed I could create such a file; however, the astyle program unfortunately behaves slightly differently between versions (which is why I mentioned usage of 1.24 in r18675). Hence, the options itself are not enough; you would have to use the exact same version of astyle as well every time. The options file for our current style would contain these four lines: indent-spaces=4 brackets=break pad-oper pad-header Does anyone know of a scheme formatter? No. Currently we just use emacs' scheme indentation. Regards, Christian ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Code formatting Re: SQL backend performance
On Thu, 2010-02-25 at 09:49 +0100, Christian Stimming wrote: Zitat von Phil Longstaff plongst...@rogers.com: http://lists.gnucash.org/pipermail/gnucash-devel/2009-August/026121.html and my commit r18675 recently. I didn't apply this to the full source tree so far in order not to destroy some people's diffs which are still waiting to be applied... I think the directory you're mentioning wasn't run through astyle recently, so that would explain this issue. I think the best way to handle this formatting is to create an options file (perhaps src/c-code.style) which is then used everywhere: astyle --options=~/gnucash/src/c-code.style *.[ch] Christian, could you create such a central file with the options you are using? Thanks for the idea. Indeed I could create such a file; however, the astyle program unfortunately behaves slightly differently between versions (which is why I mentioned usage of 1.24 in r18675). Hence, the options itself are not enough; you would have to use the exact same version of astyle as well every time. The options file for our current style would contain these four lines: indent-spaces=4 brackets=break pad-oper pad-header Hmmm... I have Ubuntu 9.10 with astyle 1.22 which doesn't even have pad-header. Does anyone know of a scheme formatter? No. Currently we just use emacs' scheme indentation. Can that be run from the command line (e.g. emacs -x scheme-indentation file.scm) with standard options so that anyone creating or modifying an scm file can check it's indented before commit? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Tue, Feb 23, 2010 at 11:40 AM, Geert Janssens janssens-ge...@telenet.be wrote: On Tuesday 23 February 2010, Donald Allen wrote: On Tue, Feb 23, 2010 at 9:15 AM, Geert Janssens Your assumptions on how things work are correct. And I noticed this performance decrease as well. There is one difference between the xml and the sql backends that may influence this (at least in part): the sql backend writes lots of debug information to gnucash.trace at present. I don't know how much impact this has, I haven't tested without debug information, but if we disable the debug information before the 2.4 release, it will surely narrow the gap. I'm seeing trace files on the order of .5 Mb. As I mentioned earlier, saving my xml file takes about 2 seconds. It's about 2.5 Mb (over 20 Mb uncompressed) and the 2 seconds includes the time to compress it. Writing the trace file is not nearly as hard a job and the periodic writes should be to the buffer cache on any reasonable machine. So I'll guess (again) that the gap-narrowing won't amount to much. I hope I'm wrong :-) I think true measurements will be the only way to find out what causes delays where. Of course. I spent a big chunk of my career doing performance analysis on various bits of complicated software and learned very young (the hard way) that if you think you know how your software behaves and where the time is going, you are probably wrong. Measurement, done correctly, is the only way to get to the truth reliably. I sometimes had to insist on measurement by people who worked for me who were as cocky (and wrong) as I was when I was young :-) But until the measurements are done, there's no harm in doing some educated guessing, so long as the guessing doesn't replace the measuring. If you are frequently right, it can help you set your measurement priorities. If you are frequently wrong, it reminds you that you aren't too good at modeling the behavior of software in your head. /Don But it's clear there's still room for performance improvements. Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
Donald Allen donaldcal...@gmail.com writes: I think true measurements will be the only way to find out what causes delays where. Of course. I spent a big chunk of my career doing performance analysis on various bits of complicated software and learned very young (the hard way) that if you think you know how your software behaves and where the time is going, you are probably wrong. Measurement, done correctly, is the only way to get to the truth reliably. I sometimes had to insist on measurement by people who worked for me who were as cocky (and wrong) as I was when I was young :-) But until the measurements are done, there's no harm in doing some educated guessing, so long as the guessing doesn't replace the measuring. If you are frequently right, it can help you set your measurement priorities. If you are frequently wrong, it reminds you that you aren't too good at modeling the behavior of software in your head. For what it's worth, the old Postgres backend was dog slow too. I certainly encourage you to perform profiling to determine where our bottlenecks are. -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 warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Wed, 2010-02-24 at 09:59 -0500, Derek Atkins wrote: Donald Allen donaldcal...@gmail.com writes: I think true measurements will be the only way to find out what causes delays where. Of course. I spent a big chunk of my career doing performance analysis on various bits of complicated software and learned very young (the hard way) that if you think you know how your software behaves and where the time is going, you are probably wrong. Measurement, done correctly, is the only way to get to the truth reliably. I sometimes had to insist on measurement by people who worked for me who were as cocky (and wrong) as I was when I was young :-) But until the measurements are done, there's no harm in doing some educated guessing, so long as the guessing doesn't replace the measuring. If you are frequently right, it can help you set your measurement priorities. If you are frequently wrong, it reminds you that you aren't too good at modeling the behavior of software in your head. For what it's worth, the old Postgres backend was dog slow too. I certainly encourage you to perform profiling to determine where our bottlenecks are. Another thing that I haven't done too much of is trying to add extra indexes or optimize queries. All SQL statements are logged to gnucash.trace. Feel free to add indexes and/or change queries to improve performance. In general, one major problem is that certain areas of the code just assume that the data is loaded. Until we remove those assumptions or provide alternatives, it seemed the safer route to just load all data at start time. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Wed, Feb 24, 2010 at 10:32 AM, Phil Longstaff plongst...@rogers.com wrote: On Wed, 2010-02-24 at 09:59 -0500, Derek Atkins wrote: Donald Allen donaldcal...@gmail.com writes: I think true measurements will be the only way to find out what causes delays where. Of course. I spent a big chunk of my career doing performance analysis on various bits of complicated software and learned very young (the hard way) that if you think you know how your software behaves and where the time is going, you are probably wrong. Measurement, done correctly, is the only way to get to the truth reliably. I sometimes had to insist on measurement by people who worked for me who were as cocky (and wrong) as I was when I was young :-) But until the measurements are done, there's no harm in doing some educated guessing, so long as the guessing doesn't replace the measuring. If you are frequently right, it can help you set your measurement priorities. If you are frequently wrong, it reminds you that you aren't too good at modeling the behavior of software in your head. For what it's worth, the old Postgres backend was dog slow too. I certainly encourage you to perform profiling to determine where our bottlenecks are. Another thing that I haven't done too much of is trying to add extra indexes or optimize queries. All SQL statements are logged to gnucash.trace. Feel free to add indexes and/or change queries to improve performance. In general, one major problem is that certain areas of the code just assume that the data is loaded. Until we remove those assumptions or provide alternatives, it seemed the safer route to just load all data at start time. I have one quick data point for you: I ran 'top' while loading my data a few times from Postgresql. 'top' is not exactly a surgical measurement tool, but it can get you started in the right direction by letting you know what the bottleneck resource is, e.g., I/O-limited, cpu-limited, etc. What I'm seeing is that for the vast majority of the time while the data is loading, gnucash-bin is using 100% of a processor (2 core system). A postgres server process shows up a distant second occasionally, and then there's a brief period at the end of the loading where there's a burst of cpu activity by the postgres server process. But most of the time is spent waiting while the gnucash-bin process computes like crazy. This is 99% user-mode time. Now the trick is to get more specific about where the time is going. I will offer one of my usual guesses: I don't *think* that missing indices (resulting in full-table scans) would produce behavior like this, because I believe the query processing is done on the server side, so I'm postulating that in that situation, you would see high cpu utilization by the server, which is not the case. If I'm right, then this might be good news, if the bulk of the time is being spent in actual gnucash code (which can be improved once you understand the problem), as opposed, say, to libpq code. Anyway, as we discussed earlier, my guessing is not a substitute for actual measurement. /Don Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
Some good news: Doing this the easy way first, I did a little manual pc sampling. I ran gnucash (today's trunk) under gdb, let it get to the point where it begins to load my data from postgresql, and periodically ctrl-c'd in gdb and copied the interrupted location and a backtrace to an emacs buffer. In the interest of saving space in this message, rather than just copying the result into the message (I assume that attachments are not permitted in emails to the gnucash mailing lists), I'll tell you that I interrupted execution 7 times and the first 6 were identical to this: 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 (gdb) bt #0 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 #1 0x7fffe86d5625 in query_transactions (be=0xa58150, stmt=value optimized out) at gnc-transaction-sql.c:385 #2 0x7fffe86d5919 in gnc_sql_transaction_load_all_tx (be=0xa58150) at gnc-transaction-sql.c:768 #3 0x70464643 in g_hash_table_foreach () from /usr/lib/libglib-2.0.so.0 #4 0x77bc69e3 in qof_object_foreach_backend (backend_name=value optimized out, cb=0x7fffe86cd220 initial_load_cb, user_data=value optimized out) at qofobject.c:373 #5 0x7fffe86cd1f1 in gnc_sql_load (be=0xa58150, book=0x9cfc80, loadType=value optimized out) at gnc-backend-sql.c:193 #6 0x7fffe88e172b in gnc_dbi_load (qbe=0xa58150, book=0x9cfc80, loadType=LOAD_TYPE_INITIAL_LOAD) at gnc-backend-dbi.c:800 #7 0x77bccf5c in qof_session_load (session=0xa34ec0, percentage_func=0x765a2d10 gnc_window_show_progress) at qofsession.c:1320 #8 0x7656dd57 in gnc_post_file_open (filename=value optimized out) at gnc-file.c:758 #9 0x0040368c in inner_main (closure=value optimized out, argc=value optimized out, argv=value optimized out) at gnucash-bin.c:521 #10 0x701aa1cf in invoke_main_func () from /usr/lib/libguile.so.17 #11 0x7018056a in c_body () from /usr/lib/libguile.so.17 #12 0x701e6cbd in scm_c_catch () from /usr/lib/libguile.so.17 #13 0x70180a17 in scm_i_with_continuation_barrier () from /usr/lib/libguile.so.17 #14 0x70180ab0 in scm_c_with_continuation_barrier () from /usr/lib/libguile.so.17 #15 0x701e5c14 in scm_i_with_guile_and_parent () from /usr/lib/libguile.so.17 #16 0x701aa185 in scm_boot_guile () from /usr/lib/libguile.so.17 #17 0x004032f6 in main (argc=1, argv=0x7fffe798) at gnucash-bin.c:672 I got myself a copy of the glib source code. In the comments preceding g_list_append, this appears: * Note that g_list_append() has to traverse the entire list * to find the end, which is inefficient when adding multiple * elements. A common idiom to avoid the inefficiency is to prepend * the elements and reverse the list when all elements have been added. This is good advice that is familiar to any of us who have done a lot of Lisp/Scheme work. So I changed query_transactions to call g_list_prepend inside the transaction loop and reverse the list after the loop complete. Now my data loads in about 30 seconds, whereas it was 45 or so previously (my checking account register looks ok, so I don't think I broke anything). So I repeated the process, periodically interrupting data loading in gdb. Now I started to see things like this: (gdb) bt #0 0x70471187 in g_list_append () from /usr/lib/libglib-2.0.so.0 #1 0x7fffe86d55c0 in load_splits_for_tx_list (be=0xa5a300, stmt=value optimized out) at gnc-transaction-sql.c:238 #2 query_transactions (be=0xa5a300, stmt=value optimized out) at gnc-transaction-sql.c:395 #3 0x7fffe86d5969 in gnc_sql_transaction_load_all_tx (be=0xa5a300) at gnc-transaction-sql.c:769 #4 0x70464643 in g_hash_table_foreach () from /usr/lib/libglib-2.0.so.0 #5 0x77bc69e3 in qof_object_foreach_backend (backend_name=value optimized out, cb=0x7fffe86cd270 initial_load_cb, user_data=value optimized out) at qofobject.c:373 #6 0x7fffe86cd241 in gnc_sql_load (be=0xa5a300, book=0x9d2480, loadType=value optimized out) at gnc-backend-sql.c:193 #7 0x7fffe88e172b in gnc_dbi_load (qbe=0xa5a300, book=0x9d2480, loadType=LOAD_TYPE_INITIAL_LOAD) at gnc-backend-dbi.c:800 #8 0x77bccf5c in qof_session_load (session=0xa36b80, percentage_func=0x765a2d10 gnc_window_show_progress) at qofsession.c:1320 #9 0x7656dd57 in gnc_post_file_open (filename=value optimized out) at gnc-file.c:758 #10 0x0040368c in inner_main (closure=value optimized out, argc=value optimized out, argv=value optimized out) at gnucash-bin.c:521 #11 0x701aa1cf in invoke_main_func () from /usr/lib/libguile.so.17 #12 0x7018056a in c_body () from /usr/lib/libguile.so.17 #13 0x701e6cbd in scm_c_catch () from /usr/lib/libguile.so.17 #14 0x70180a17 in scm_i_with_continuation_barrier () from /usr/lib/libguile.so.17 #15 0x70180ab0 in scm_c_with_continuation_barrier () from /usr/lib/libguile.so.17 #16 0x701e5c14 in
Re: SQL backend performance
So I applied the same treatment to load_splits_for_tx_list, substituting g_list_prepend for g_list_append inside the split-fetching loop and reversing the list on completion of the loop. I rebuilt and tried again and now my data loads in about 9 seconds, approximately the same as the xml file and about a factor of 5 improvement! I haven't tested it yet, but I believe we'll see a nice improvement with sqlite3 as well, since this code is not specific to either database. This is extremely good news! Thanks a lot for looking into this and reminding anyone here to read the docs of the container types we're using... I guess the same (g_list_prepend vs. append) might show up in the XML backend as well... As for the patch and indentation: Indeed we discussed and agreed on some common indentation last summer, and we agreed on using the tool astyle to reformat the code as decided. See http://lists.gnucash.org/pipermail/gnucash-devel/2009-August/026121.html and my commit r18675 recently. I didn't apply this to the full source tree so far in order not to destroy some people's diffs which are still waiting to be applied... I think the directory you're mentioning wasn't run through astyle recently, so that would explain this issue. Regards, Christian ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Wed, 2010-02-24 at 14:37 -0500, Donald Allen wrote: Great! I'll apply the patch. There are probable other places which would benefit from this. There might also be places where the order is unimportant so that the list doesn't need to be reversed. BTW, I found the indentation/formatting of this (and other) files to be very odd, and it rendered the code pretty unreadable for me. What you see above is a replication of the changes I made to the original file. In order to do this work, though, I had to format the two procedures involved (in emacs with meta-ctrl-q) so I could read them (I could have cleaned them up some more to eliminate unnecessary pointy-brackets, but didn't bother). It was that code that I actually tested, so there could be a typo in the above. I attribute great importance to code readability and have some strong opinions about how C code should look. I saw something on gnucash-devel earlier that gave me the impression there is something afoot to clean up the code. Is that so? If yes, I'd like to put my $.02 into that pot. Yes, there is a code formatting effort being made using a standard set of options to a code formatter. They don't match my ideal preferences, but given N people, there will N different tastes. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Code formatting Re: SQL backend performance
On Wed, 2010-02-24 at 20:50 +0100, Christian Stimming wrote: So I applied the same treatment to load_splits_for_tx_list, substituting g_list_prepend for g_list_append inside the split-fetching loop and reversing the list on completion of the loop. I rebuilt and tried again and now my data loads in about 9 seconds, approximately the same as the xml file and about a factor of 5 improvement! I haven't tested it yet, but I believe we'll see a nice improvement with sqlite3 as well, since this code is not specific to either database. This is extremely good news! Thanks a lot for looking into this and reminding anyone here to read the docs of the container types we're using... I guess the same (g_list_prepend vs. append) might show up in the XML backend as well... As for the patch and indentation: Indeed we discussed and agreed on some common indentation last summer, and we agreed on using the tool astyle to reformat the code as decided. See http://lists.gnucash.org/pipermail/gnucash-devel/2009-August/026121.html and my commit r18675 recently. I didn't apply this to the full source tree so far in order not to destroy some people's diffs which are still waiting to be applied... I think the directory you're mentioning wasn't run through astyle recently, so that would explain this issue. I think the best way to handle this formatting is to create an options file (perhaps src/c-code.style) which is then used everywhere: astyle --options=~/gnucash/src/c-code.style *.[ch] Christian, could you create such a central file with the options you are using? Does anyone know of a scheme formatter? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Code formatting Re: SQL backend performance
On Wed, 2010-02-24 at 22:18 +0100, Geert Janssens wrote: On Wednesday 24 February 2010, Phil Longstaff wrote: On Wed, 2010-02-24 at 20:50 +0100, Christian Stimming wrote: Christian, could you create such a central file with the options you are using? Agreed on the idea of an options file, but it should IMHO not be in src. I'd prefer to see it in util. util sounds good. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Tuesday 23 February 2010, Donald Allen wrote: As I've mentioned in other posts, I have a pretty large gnucash datafile -- more than 20 Mb uncompressed. I've been testing the SQL backend and I'm concerned about the performance, particularly startup performance. I've been doing this testing on an inexpensive little HP desktop machine, dual-core 2.8 Ghz AMD Athlon, 2 Gb memory, 300 Gb 7200 rpm SATA drive, Arch Linux, 2.6.32 kernel. It may have been cheap, but it's damned fast (I've been in the computer business since 1964 until I retired last Oct. and the cost-performance of today's hardware just boggles my mind, especially when I think about what we put up with 20 or 30 years ago; you haven't lived until you've tried getting your work done *and* stayed sane while sitting in front of a VT100 talking to an overloaded Vax 780 running 4.1BSD; motto: It was hard to build, it ought to be hard to use). From my gnucash xml file, I've created sqlite3 and postgresql databases containing the same data. Here are the average data-load timings, in seconds, from just a couple of tests per storage method (this is from the point gnucash says reading file until it's up): xml 9.10 sqlite3 45.41 postgresql45.46 My mental model, which may be wrong, of what the SQL backend is doing is that it is reading the entire database at startup into memory, as opposed to retrieving the data as-needed (which is the way I'd guess gnucash+database would be architected if it was being written from scratch; I'll guess further that given the file-based history of gnucash, it's existing architecture precluded using the incremental approach when adding the database backend). I'm guessing this because of the amount of time it takes to start up when pointed either at postgresql or sqlite3 versions of the same data. I am further assuming that, with the SQL backend, as the user adds new things (accounts, transactions, etc.), new objects get inserted into the database and modified objects get updated, on the spot. I'm guessing this because as I make changes, the 'save' button remains grayed-out. So the primary advantage of the database backend is that file saves are not necessary. But, at least in my case, I am paying about a 36 second price at startup to avoid the file saves. File saves on my machine take about 2 seconds, much faster than reading the file, probably because the data is being written to the buffer cache. So I'd need to do 18 file saves during a gnucash session to be worse off (kept waiting) with the xml file than with the data in either flavor of database. And that assumes that I am always waiting for file saves to complete, which is not always the case (I frequently do them after completing a chunk of work, e.g., entering an investment transaction from a statement, and the file-save happens while I turn my attention to the paperwork to figure out what I need to do next). While I didn't do a lot of timed tests and I've used the word guess an awful lot above (corrections to what I've said above from someone who actually knows what (s)he is talking about are perfectly welcome), the numbers confirm what I already knew from many more untimed tests, that at least in my case, startup takes an unacceptably long time. If this is the way the released version performs and my back-of-the-envelope analysis of the tradeoff is correct, I don't think I'd use the database stuff and just continue with the xml file. But if you think I've missed something here, please feel free. Your assumptions on how things work are correct. And I noticed this performance decrease as well. There is one difference between the xml and the sql backends that may influence this (at least in part): the sql backend writes lots of debug information to gnucash.trace at present. I don't know how much impact this has, I haven't tested without debug information, but if we disable the debug information before the 2.4 release, it will surely narrow the gap. In the future this may still be improved such that data is only queried for when needed, but this requires a lot of internal modifications. This would not have been possible for the next stable release. Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend performance
On Tue, Feb 23, 2010 at 9:15 AM, Geert Janssens janssens-ge...@telenet.be wrote: On Tuesday 23 February 2010, Donald Allen wrote: As I've mentioned in other posts, I have a pretty large gnucash datafile -- more than 20 Mb uncompressed. I've been testing the SQL backend and I'm concerned about the performance, particularly startup performance. I've been doing this testing on an inexpensive little HP desktop machine, dual-core 2.8 Ghz AMD Athlon, 2 Gb memory, 300 Gb 7200 rpm SATA drive, Arch Linux, 2.6.32 kernel. It may have been cheap, but it's damned fast (I've been in the computer business since 1964 until I retired last Oct. and the cost-performance of today's hardware just boggles my mind, especially when I think about what we put up with 20 or 30 years ago; you haven't lived until you've tried getting your work done *and* stayed sane while sitting in front of a VT100 talking to an overloaded Vax 780 running 4.1BSD; motto: It was hard to build, it ought to be hard to use). From my gnucash xml file, I've created sqlite3 and postgresql databases containing the same data. Here are the average data-load timings, in seconds, from just a couple of tests per storage method (this is from the point gnucash says reading file until it's up): xml 9.10 sqlite3 45.41 postgresql 45.46 My mental model, which may be wrong, of what the SQL backend is doing is that it is reading the entire database at startup into memory, as opposed to retrieving the data as-needed (which is the way I'd guess gnucash+database would be architected if it was being written from scratch; I'll guess further that given the file-based history of gnucash, it's existing architecture precluded using the incremental approach when adding the database backend). I'm guessing this because of the amount of time it takes to start up when pointed either at postgresql or sqlite3 versions of the same data. I am further assuming that, with the SQL backend, as the user adds new things (accounts, transactions, etc.), new objects get inserted into the database and modified objects get updated, on the spot. I'm guessing this because as I make changes, the 'save' button remains grayed-out. So the primary advantage of the database backend is that file saves are not necessary. But, at least in my case, I am paying about a 36 second price at startup to avoid the file saves. File saves on my machine take about 2 seconds, much faster than reading the file, probably because the data is being written to the buffer cache. So I'd need to do 18 file saves during a gnucash session to be worse off (kept waiting) with the xml file than with the data in either flavor of database. And that assumes that I am always waiting for file saves to complete, which is not always the case (I frequently do them after completing a chunk of work, e.g., entering an investment transaction from a statement, and the file-save happens while I turn my attention to the paperwork to figure out what I need to do next). While I didn't do a lot of timed tests and I've used the word guess an awful lot above (corrections to what I've said above from someone who actually knows what (s)he is talking about are perfectly welcome), the numbers confirm what I already knew from many more untimed tests, that at least in my case, startup takes an unacceptably long time. If this is the way the released version performs and my back-of-the-envelope analysis of the tradeoff is correct, I don't think I'd use the database stuff and just continue with the xml file. But if you think I've missed something here, please feel free. Your assumptions on how things work are correct. And I noticed this performance decrease as well. There is one difference between the xml and the sql backends that may influence this (at least in part): the sql backend writes lots of debug information to gnucash.trace at present. I don't know how much impact this has, I haven't tested without debug information, but if we disable the debug information before the 2.4 release, it will surely narrow the gap. I'm seeing trace files on the order of .5 Mb. As I mentioned earlier, saving my xml file takes about 2 seconds. It's about 2.5 Mb (over 20 Mb uncompressed) and the 2 seconds includes the time to compress it. Writing the trace file is not nearly as hard a job and the periodic writes should be to the buffer cache on any reasonable machine. So I'll guess (again) that the gap-narrowing won't amount to much. I hope I'm wrong :-) In the future this may still be improved such that data is only queried for when needed, but this requires a lot of internal modifications. This would not have been possible for the next stable release. I understand why this was done as it was; I said so in my previous email. If I had been running this project, I'm quite sure I'd have done the same thing, so I'm not at all critical. I also note that I'm a bit of an extreme case. I've been
Re: SQL backend performance
On Tuesday 23 February 2010, Donald Allen wrote: On Tue, Feb 23, 2010 at 9:15 AM, Geert Janssens Your assumptions on how things work are correct. And I noticed this performance decrease as well. There is one difference between the xml and the sql backends that may influence this (at least in part): the sql backend writes lots of debug information to gnucash.trace at present. I don't know how much impact this has, I haven't tested without debug information, but if we disable the debug information before the 2.4 release, it will surely narrow the gap. I'm seeing trace files on the order of .5 Mb. As I mentioned earlier, saving my xml file takes about 2 seconds. It's about 2.5 Mb (over 20 Mb uncompressed) and the 2 seconds includes the time to compress it. Writing the trace file is not nearly as hard a job and the periodic writes should be to the buffer cache on any reasonable machine. So I'll guess (again) that the gap-narrowing won't amount to much. I hope I'm wrong :-) I think true measurements will be the only way to find out what causes delays where. But it's clear there's still room for performance improvements. Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
Phil Longstaff wrote: The interesting line in your log is: * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found which means that there was some problem initializing libdbi. Your listing above shows that the dbd file are in /usr/local/lib/dbd. My packages (ubuntu) put them in /usr/lib/dbd, so that is what I made the default. I would assume that /usr/lib/dbd doesn't exist on your machine, which is why dbi_initialize() returns -1. Try adding '--with-dbi-dbd-dir=/usr/local/lib/dbd' to your configure command. Phil This has led to some progress. I built rev 17871 with this option and confirmed that the gnucash script contained the correct path to the dbd directory. Now the gnucash trace has the following: * 20:43:12 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 20:43:13 INFO gnc.backend.dbi [init_sql_backend] 3 DBD drivers found * 20:43:13 INFO gnc.backend.dbi [init_sql_backend] Driver: mysql * 20:43:13 INFO gnc.backend.dbi [init_sql_backend] Driver: pgsql * 20:43:13 INFO gnc.backend.dbi [init_sql_backend] Driver: sqlite3 * However, I still get the can't parse URL error. I've checked with ldd and my libdbdpgsql.so is built against the correct version of PostgreSql - it's linked to the install directory of the version actually running. I can connect to that with psql. I have version 0.8.3 of libdbi and 0.8.3.1 of the drivers. These are the most recent versions. Any suggestions? Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
On January 31, 2009 11:07:17 pm Mark Johnson wrote: Phil Longstaff wrote: Hmmm... 'configure' does allow any wrong options and does not seem to flag it. What is *supposed* to happen (and what happens for me) is that the 'Database Connection' menu item will be there, but insensitive unless '--enable-dbi' is specified. Can you send me your config.log and config.h? Phil On January 31, 2009 09:34:32 am Mark Johnson wrote: I have built trunk rev 17855 with the wrong configure options. I accidentally used the old --enable-gda instead of the correct --enable-dbi. The file menu has a Database Connection option and when I filled in its dialog's fields and clicked OK, I got a can't parse URL error. If I haven't enabled the dbi backend, should this menu option even be there? Here is the relevant portion of gnucash.trace: * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_begin()] sess=0x8512e68 ignore_lock=0, book-id=postgres://localhost:gnucash:gnucash_user:kirk * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=6, initted=true * 07:22:41 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_string registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_boolean registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int64 registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_double registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_guid registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_timespec registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_gdate registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_numeric registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_commodityref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_accountref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_txref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_lotref registered * 07:22:41 DEBUG qof.session [leave qof_session_load_backend()] * 07:22:41 DEBUG gnc.backend.dbi [enter gnc-backend-dbi.c:gnc_dbi_postgres_session_begin()] * 07:22:41 CRIT gnc.backend.dbi [gnc_dbi_postgres_session_begin()] Unable to create pgsql dbi connection * 07:22:41 DEBUG gnc.backend.dbi [leave gnc_dbi_postgres_session_begin()] * 07:22:41 INFO qof.session [qof_session_begin] Done running session_begin on backend * 07:22:41 DEBUG qof.session [leave qof_session_begin()] backend error 3 (null) * It seems odd to me that it is loading a postgresql backend, when I did not specify --enable-dbi. Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel Aha, I found the problem. I executed the right build script (with the --enable-dbi), but looked at the wrong one (with the --enable-gda). So I did pass --enable-gda after all! Now, my question is: what do I do about this URL parsing problem? I have confirmed all the values I filled into the dialog box. I built rev 1864 and still have the problem. Mark Besides libdbi, you also need the low level postgresql driver for libdbi. On my system, these are in /usr/lib/dbd. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
Phil Longstaff wrote: On January 31, 2009 11:07:17 pm Mark Johnson wrote: Phil Longstaff wrote: Hmmm... 'configure' does allow any wrong options and does not seem to flag it. What is *supposed* to happen (and what happens for me) is that the 'Database Connection' menu item will be there, but insensitive unless '--enable-dbi' is specified. Can you send me your config.log and config.h? Phil On January 31, 2009 09:34:32 am Mark Johnson wrote: I have built trunk rev 17855 with the wrong configure options. I accidentally used the old --enable-gda instead of the correct --enable-dbi. The file menu has a Database Connection option and when I filled in its dialog's fields and clicked OK, I got a can't parse URL error. If I haven't enabled the dbi backend, should this menu option even be there? Here is the relevant portion of gnucash.trace: * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_begin()] sess=0x8512e68 ignore_lock=0, book-id=postgres://localhost:gnucash:gnucash_user:kirk * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=6, initted=true * 07:22:41 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_string registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_boolean registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int64 registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_double registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_guid registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_timespec registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_gdate registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_numeric registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_commodityref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_accountref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_txref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_lotref registered * 07:22:41 DEBUG qof.session [leave qof_session_load_backend()] * 07:22:41 DEBUG gnc.backend.dbi [enter gnc-backend-dbi.c:gnc_dbi_postgres_session_begin()] * 07:22:41 CRIT gnc.backend.dbi [gnc_dbi_postgres_session_begin()] Unable to create pgsql dbi connection * 07:22:41 DEBUG gnc.backend.dbi [leave gnc_dbi_postgres_session_begin()] * 07:22:41 INFO qof.session [qof_session_begin] Done running session_begin on backend * 07:22:41 DEBUG qof.session [leave qof_session_begin()] backend error 3 (null) * It seems odd to me that it is loading a postgresql backend, when I did not specify --enable-dbi. Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel Aha, I found the problem. I executed the right build script (with the --enable-dbi), but looked at the wrong one (with the --enable-gda). So I did pass --enable-gda after all! Now, my question is: what do I do about this URL parsing problem? I have confirmed all the values I filled into the dialog box. I built rev 1864 and still have the problem. Mark Besides libdbi, you also need the low level postgresql driver for libdbi. On my system, these are in /usr/lib/dbd. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel I have it: m...@ds9:/usr/local/lib/dbd$ ls libdbdmysql.alibdbdpgsql.alibdbdsqlite3.a libdbdmysql.lalibdbdpgsql.lalibdbdsqlite3.la libdbdmysql.solibdbdpgsql.solibdbdsqlite3.so I was pretty sure I compiled dbd with it, and I did. Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
On February 1, 2009 12:08:40 pm Mark Johnson wrote: On January 31, 2009 09:34:32 am Mark Johnson wrote: I have built trunk rev 17855 with the wrong configure options. I accidentally used the old --enable-gda instead of the correct --enable-dbi. The file menu has a Database Connection option and when I filled in its dialog's fields and clicked OK, I got a can't parse URL error. If I haven't enabled the dbi backend, should this menu option even be there? Here is the relevant portion of gnucash.trace: * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_begin()] sess=0x8512e68 ignore_lock=0, book-id=postgres://localhost:gnucash:gnucash_user:kirk * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=6, initted=true * 07:22:41 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found I have it: m...@ds9:/usr/local/lib/dbd$ ls libdbdmysql.alibdbdpgsql.alibdbdsqlite3.a libdbdmysql.lalibdbdpgsql.lalibdbdsqlite3.la libdbdmysql.solibdbdpgsql.solibdbdsqlite3.so I was pretty sure I compiled dbd with it, and I did. Mark The interesting line in your log is: * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found which means that there was some problem initializing libdbi. Your listing above shows that the dbd file are in /usr/local/lib/dbd. My packages (ubuntu) put them in /usr/lib/dbd, so that is what I made the default. I would assume that /usr/lib/dbd doesn't exist on your machine, which is why dbi_initialize() returns -1. Try adding '--with-dbi-dbd-dir=/usr/local/lib/dbd' to your configure command. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
Phil, Quoting Phil Longstaff plongst...@rogers.com: which means that there was some problem initializing libdbi. Your listing above shows that the dbd file are in /usr/local/lib/dbd. My packages (ubuntu) put them in /usr/lib/dbd, so that is what I made the default. I would assume that /usr/lib/dbd doesn't exist on your machine, which is why dbi_initialize() returns -1. Try adding '--with-dbi-dbd-dir=/usr/local/lib/dbd' to your configure command. Why does GnuCash depend on the way that DBI was built? I'm not familiar with the DBI API, but... does the application need to tell DBI where to find the DBD plugins? If yes, is there some way we could discover that (maybe via pkg-config)? If no, why do you need to know the DBD dir? Phil -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 warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
On February 1, 2009 02:33:24 pm Derek Atkins wrote: Phil, Quoting Phil Longstaff plongst...@rogers.com: which means that there was some problem initializing libdbi. Your listing above shows that the dbd file are in /usr/local/lib/dbd. My packages (ubuntu) put them in /usr/lib/dbd, so that is what I made the default. I would assume that /usr/lib/dbd doesn't exist on your machine, which is why dbi_initialize() returns -1. Try adding '--with-dbi-dbd-dir=/usr/local/lib/dbd' to your configure command. Why does GnuCash depend on the way that DBI was built? I'm not familiar with the DBI API, but... does the application need to tell DBI where to find the DBD plugins? If yes, is there some way we could discover that (maybe via pkg-config)? If no, why do you need to know the DBD dir? The relevant API is int dbi_initialize(const char *driverdir) The return code is the number of dbd plugins, -1 if an error. If driverdir is NULL, libdbi assumes '/usr/local/lib/dbd'. The dbi backend uses the environment variable GNC_DBD_DIR to tell it what the driver dir is. If it is not set, it assumes /usr/lib/dbd (since that is where the dbd plugins are put when I apt-get install them). The configure option provides the value for the environment variable in the 'gnucash' command file. I haven't looked around to see if pkg-config would work. On sourceforge, libdbi and libdbi-drivers are separate projects. I just looked through the source code for libdbi-drivers and didn't see any .pc files. Phil Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend can't parse URL
Hmmm... 'configure' does allow any wrong options and does not seem to flag it. What is *supposed* to happen (and what happens for me) is that the 'Database Connection' menu item will be there, but insensitive unless '--enable-dbi' is specified. Can you send me your config.log and config.h? Phil On January 31, 2009 09:34:32 am Mark Johnson wrote: I have built trunk rev 17855 with the wrong configure options. I accidentally used the old --enable-gda instead of the correct --enable-dbi. The file menu has a Database Connection option and when I filled in its dialog's fields and clicked OK, I got a can't parse URL error. If I haven't enabled the dbi backend, should this menu option even be there? Here is the relevant portion of gnucash.trace: * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_begin()] sess=0x8512e68 ignore_lock=0, book-id=postgres://localhost:gnucash:gnucash_user:kirk * 07:22:41 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=6, initted=true * 07:22:41 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 07:22:41 INFO gnc.backend.dbi [init_sql_backend] -1 DBD drivers found * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_string registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_boolean registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_int64 registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_double registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_guid registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_timespec registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_gdate registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_numeric registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_commodityref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_accountref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_txref registered * 07:22:41 DEBUG gnc.backend.sql [gnc_sql_register_col_type_handler] Col type ct_lotref registered * 07:22:41 DEBUG qof.session [leave qof_session_load_backend()] * 07:22:41 DEBUG gnc.backend.dbi [enter gnc-backend-dbi.c:gnc_dbi_postgres_session_begin()] * 07:22:41 CRIT gnc.backend.dbi [gnc_dbi_postgres_session_begin()] Unable to create pgsql dbi connection * 07:22:41 DEBUG gnc.backend.dbi [leave gnc_dbi_postgres_session_begin()] * 07:22:41 INFO qof.session [qof_session_begin] Done running session_begin on backend * 07:22:41 DEBUG qof.session [leave qof_session_begin()] backend error 3 (null) * It seems odd to me that it is loading a postgresql backend, when I did not specify --enable-dbi. Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
On October 29, 2008 10:14:40 am Derek Atkins wrote: Yeah, it should get fixed.. If the user specifies --enable-dbi then it should AC_MSG_ERROR() if configure cannot find it. It already does. I don't understand how it would compile. For me, configure aborts if the necessary include file doesn't exist. Even if it did continue, the compilation would fail. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Derek Atkins wrote: DBI. When you --enable-dbi what does the config.log say about it finding DBI? I don't understand too much about it, but I don't think it looks good. $ grep -i dbi config.log $ ./configure --prefix=/export/gnucash --enable-debug --enable-doxygen --enable-locale-specific-tax --enable-error-on-warning --enable-compile-warnings --enable-aqbanking --enable-python-bindings --enable-dbi --enable-sql configure:28222: checking dbi/dbi.h usability conftest.c:108:21: error: dbi/dbi.h: No such file or directory | #include dbi/dbi.h configure:28263: checking dbi/dbi.h presence conftest.c:75:21: error: dbi/dbi.h: No such file or directory | #include dbi/dbi.h configure:28331: checking for dbi/dbi.h configure:28365: error: Unable to find dbi/dbi.h ac_cv_header_dbi_dbi_h=no DBI_DIR='' LIBDBI_LIBS='' Full log is at http://oss.leggewie.org/wip/config.log If it is what I think it is (I was missing some packages/libs at compile time) then maybe the configure checks could be tuned to detect this better? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff wrote: I'm using kubuntu 8.04. I have libdbi0, libdbi0-dev, libdbd-mysql and libdbd-sqlite3 (among other) packages installed. I'm on Ubuntu as well. I was missing all of the above packages except libdbi0. I have installed them now and will retry a compile. BTW, when I run configure, at the end, as optional components, it lists dbi. Does it do that when you run it? I believe that information is in my reply to Derek's mail which I just sent. If there is more, let me know. I do think that your hunch about missing packages at compile time might be right. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie wrote: Phil Longstaff wrote: I'm using kubuntu 8.04. I have libdbi0, libdbi0-dev, libdbd-mysql and libdbd-sqlite3 (among other) packages installed. I'm on Ubuntu as well. I was missing all of the above packages except libdbi0. I have installed them now and will retry a compile. This is great. It is indeed working quite nicely now after I installed those packages and recompiled. Thank you so much for this. It will make my life a LOT easier, preparing reports and stuff (which I will do from Openoffice Base now). Can the configure step somehow be finetuned to detect the missing libs and abort if --enable-dbi was given? I also think the menu entry File - Database Connection is present whether or not that flag is given. Shouldn't it be only available when the switch was turned on? Are you sure that Umlauts and other non-ASCII characters are correctly saved in MySQL? Regards Rolf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
On Wed, 2008-10-29 at 13:39 +0100, Rolf Leggewie wrote: Rolf Leggewie wrote: Phil Longstaff wrote: I'm using kubuntu 8.04. I have libdbi0, libdbi0-dev, libdbd-mysql and libdbd-sqlite3 (among other) packages installed. I'm on Ubuntu as well. I was missing all of the above packages except libdbi0. I have installed them now and will retry a compile. This is great. It is indeed working quite nicely now after I installed those packages and recompiled. Thank you so much for this. It will make my life a LOT easier, preparing reports and stuff (which I will do from Openoffice Base now). Can the configure step somehow be finetuned to detect the missing libs and abort if --enable-dbi was given? I also think the menu entry File - Database Connection is present whether or not that flag is given. Shouldn't it be only available when the switch was turned on? Are you sure that Umlauts and other non-ASCII characters are correctly saved in MySQL? I have done no testing on chars other than those normally found in English. Please test. I will look at the documentation for mysql and other databases to see if I need to modify anything. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie [EMAIL PROTECTED] writes: Rolf Leggewie wrote: Phil Longstaff wrote: I'm using kubuntu 8.04. I have libdbi0, libdbi0-dev, libdbd-mysql and libdbd-sqlite3 (among other) packages installed. I'm on Ubuntu as well. I was missing all of the above packages except libdbi0. I have installed them now and will retry a compile. This is great. It is indeed working quite nicely now after I installed those packages and recompiled. Thank you so much for this. It will make my life a LOT easier, preparing reports and stuff (which I will do From Openoffice Base now). Can the configure step somehow be finetuned to detect the missing libs and abort if --enable-dbi was given? I also think the menu entry File - Database Connection is present whether or not that flag is given. Shouldn't it be only available when the switch was turned on? Yeah, it should get fixed.. If the user specifies --enable-dbi then it should AC_MSG_ERROR() if configure cannot find it. Are you sure that Umlauts and other non-ASCII characters are correctly saved in MySQL? We should test whether MySQL is using ISO or UTF8. I think it's a configuration setting, and we should make sure we set it explicitly. Regards Rolf -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Derek Atkins wrote: lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql I rebuilt r17663 with --enable-sql and the situation is completely unchanged. What do I do now? I really want SQL support. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff [EMAIL PROTECTED] writes: On Mon, 2008-10-27 at 15:36 -0400, Derek Atkins wrote: Quoting Phil Longstaff [EMAIL PROTECTED]: lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql When I made the switch from libgda to libdbi, I actually split the backend into a core sql library which does the bulk of the work, and a library-dependent part (dbi vs gda). libgnc-backend-sql is the core library. I can modify the build rules so that it is only built if the dbi backend is also built. Sorry, you are right. --enable-sql builds libgncmod-backend-postgres. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie [EMAIL PROTECTED] writes: Derek Atkins wrote: lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql I rebuilt r17663 with --enable-sql and the situation is completely unchanged. What do I do now? I really want SQL support. Sorry, --enable-sql is the old PG backend. It has nothing to do with DBI. When you --enable-dbi what does the config.log say about it finding DBI? -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
On Tue, 2008-10-28 at 15:32 +0100, Rolf Leggewie wrote: Derek Atkins wrote: lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql I rebuilt r17663 with --enable-sql and the situation is completely unchanged. What do I do now? I really want SQL support. I'm using kubuntu 8.04. I have libdbi0, libdbi0-dev, libdbd-mysql and libdbd-sqlite3 (among other) packages installed. I checkout trunk, then type: ./autogen.sh ./configure --prefix=/opt/gnucash2-dbi --enable-debug --enable-dbi --enable-compiler-warnings=yes make all install I can then run /opt/gnucash2-dbi/bin/gnucash and use xml, sqlite3 and mysql. /usr/lib/dbd contains libdbdmysql.so and libdbdsqlite3.so which are the dbi backends I need for mysql and sqlite3 (I didn't bother with postgresql right now). Perhaps you need to be a bit more explicit with what you use. BTW, when I run configure, at the end, as optional components, it lists dbi. Does it do that when you run it? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff wrote: The only think I can think of is that the dbi module isn't being loaded. Phil, thank you for your continued support. So, this just may be a case of incorrectly compiling gnucash? I sure hope I wasn't wasting everybody's time because of my stupidity. There is no file libgncmod-backend-dbi.so below /export/gnucash, but there is one in /usr/lib/gnucash/gnucash. That one is from a self-compiled deb package. Why was this file not created for the installation below /export/gnucash? /export/gnucash/lib/libgnc-backend-sql.so does exist, btw. strings /export/gnucash/lib/gnucash/libgncmod-engine.so | fgrep dbi returns nothing, but the file itself exists. As another way to look for the root of the problem; can you provide me with a working sqlite file of a sample gnucash file? I'd like to see if I can read it in. Regards Rolf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Quoting Phil Longstaff [EMAIL PROTECTED]: On Mon, 2008-10-27 at 18:39 +0100, Rolf Leggewie wrote: Phil Longstaff wrote: The only think I can think of is that the dbi module isn't being loaded. Phil, thank you for your continued support. So, this just may be a case of incorrectly compiling gnucash? I sure hope I wasn't wasting everybody's time because of my stupidity. There is no file libgncmod-backend-dbi.so below /export/gnucash, but there is one in /usr/lib/gnucash/gnucash. That one is from a self-compiled deb package. Why was this file not created for the installation below /export/gnucash? /export/gnucash/lib/libgnc-backend-sql.so does exist, btw. strings /export/gnucash/lib/gnucash/libgncmod-engine.so | fgrep dbi returns nothing, but the file itself exists. lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql libgncmod-backend-dbi.so will only be built it --enable-dbi is provided when gnucash is configured. The string I was asking you to search for in libgncmod-engine.so is used to load the dbi backend at run time. It is only loaded if configure could find dbi/dbi.h (usually in /usr/include). As another way to look for the root of the problem; can you provide me with a working sqlite file of a sample gnucash file? I'd like to see if I can read it in. I'll send one to you directly so that it doesn't clog the mailing list. Regards Rolf -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
On Mon, 2008-10-27 at 15:36 -0400, Derek Atkins wrote: Quoting Phil Longstaff [EMAIL PROTECTED]: lib/libgnc-backend-sql.so will always be built. Actually, it should only be built if you --enable-sql When I made the switch from libgda to libdbi, I actually split the backend into a core sql library which does the bulk of the work, and a library-dependent part (dbi vs gda). libgnc-backend-sql is the core library. I can modify the build rules so that it is only built if the dbi backend is also built. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
The only think I can think of is that the dbi module isn't being loaded. From your log: * 12:09:52 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=2, initted=false From my log: * 18:03:14 DEBUG qof.session [enter qofsession.c:qof_session_load_backend()] list=5, initted=false The interesting item is list= and the number. The xml backend contributes 2 providers (for file:// and xml:// uris), and the dbi backend contributes 3 providers (for sqlite3://, mysql:// and postgres://). In my case, all 5 uri types have providers, while in yours, it's only the 2 from the xml backend. For the dbi backend, you need libgncmod-backend-dbi.so in xxx/lib/gnucash and libgnc-backend-sql.so in xxx/lib (where xxx is the path to your gnucash install). In addition, the command strings libgncmod-engine.so | fgrep dbi should return a single line gncmod-backend-dbi if run in xxx/lib/gnucash. Phil On Mon, 2008-09-29 at 12:19 +0200, Rolf Leggewie wrote: Phil Longstaff wrote: See http://wiki.gnucash.org/wiki/Logging. Phil, thank you for your continued support. I hope you can help me decipher http://oss.leggewie.org/wip/gnucash.log which does contain some sql-related messages now. ~/.gnucash/log.conf is at http://oss.leggewie.org/wip/log.conf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.7.5/1697 - Release Date: 9/29/2008 7:40 AM ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff wrote: See http://wiki.gnucash.org/wiki/Logging. Phil, thank you for your continued support. I hope you can help me decipher http://oss.leggewie.org/wip/gnucash.log which does contain some sql-related messages now. ~/.gnucash/log.conf is at http://oss.leggewie.org/wip/log.conf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff [EMAIL PROTECTED] writes: Rolf Leggewie wrote: Phil Longstaff wrote: What is in /tmp/gnucash.trace? * 23:19:48 WARN gnc.app-utils /home/rolf/.gnucash/config-1.8.auto:13:15: While evaluating arguments to gnc:lookup-option in expression (gnc:lookup-option gnc:*options-entries* __new_user ...): /home/rolf/.gnucash/config-1.8.auto:13:15: Unbound variable: gnc:*options-entries* In /home/rolf/.gnucash/config-1.8.auto: 13: 0* (let ((option #)) ((lambda # #) option)) 13: 1* [gnc:lookup-option ... This is all there is? Nothing related to SQL or backend? This error is related to reports.. Perhaps trying to open a custom report that no longer exists? Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie wrote: Phil Longstaff wrote: This is all there is? Nothing related to SQL or backend? Today I have one more line, but I don't think it is what you are looking for, either. * 13:52:01 CRIT Gtk gtk_widget_event: assertion `WIDGET_REALIZED_FOR_EVENT (widget, event)' failed What do we do now? ___ What platform? When I tried to connect to a mysql db, I got the same popup, but my /tmp/gnucash.trace contained: * 13:29:46 CRIT gnc.backend.dbi [error_fn()] DBI error: 1044: Access denied for user ''@'localhost' to database 'gnucash' * 13:29:46 CRIT gnc.backend.dbi [gnc_dbi_mysql_session_begin()] Unable to connect to mysql://localhost:gnucash::: -2 Of course, if, for some reason, it didn't build the backend as expected, then it might not understand the URL and would fail without providing gnucash.trace messages. I assume you have an up-to-date trunk. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff wrote: When I tried to connect to a mysql db, I got the same popup, OK. So it did not work in your case, either? I assume you have an up-to-date trunk. Rebuilt today. r17608. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie wrote: Phil Longstaff wrote: When I tried to connect to a mysql db, I got the same popup, OK. So it did not work in your case, either? I assume you have an up-to-date trunk. Rebuilt today. r17608. ___ Yes, but I also had error messages in /tmp/gnucash.trace which outlined the problem. See http://wiki.gnucash.org/wiki/Logging.Try running again after you have set up a logging file with gnc.backend.dbi=debug, gnc.backend.sql=debug and qof.session=debug. That should produce more information. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
What is in /tmp/gnucash.trace? Rolf Leggewie wrote: Rolf Leggewie wrote: The result was an error message Can't parse the URL /home/rolf/.gnucash/data/mysql:,,$server:gnucash:$user:$pw. What am I doing wrong? By reading some older messages from Phil to this list, I found out that overwriting an existing sqlite3 file with Save As might be another way to export the gnucash data to SQL. The result was very similar to the above. Can't parse the URL /tmp/test.db. There is some output on the console from where I started gnucash: /tmp/test.db:1: parser error : Start tag expected, '' not found SQLite format 3 ^ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.7.3/1693 - Release Date: 9/26/2008 7:35 AM -- The results of this were not altogether what had been hoped for, and further dissensions ensued. Marguerite Block, The New Church in the New World referring to the 'Charitable Association' which was formed among members to tell each other their faults as an aid to more rapid regeneration. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Phil Longstaff wrote: What is in /tmp/gnucash.trace? * 23:19:48 WARN gnc.app-utils /home/rolf/.gnucash/config-1.8.auto:13:15: While evaluating arguments to gnc:lookup-option in expression (gnc:lookup-option gnc:*options-entries* __new_user ...): /home/rolf/.gnucash/config-1.8.auto:13:15: Unbound variable: gnc:*options-entries* In /home/rolf/.gnucash/config-1.8.auto: 13: 0* (let ((option #)) ((lambda # #) option)) 13: 1* [gnc:lookup-option ... ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie wrote: Phil Longstaff wrote: What is in /tmp/gnucash.trace? * 23:19:48 WARN gnc.app-utils /home/rolf/.gnucash/config-1.8.auto:13:15: While evaluating arguments to gnc:lookup-option in expression (gnc:lookup-option gnc:*options-entries* __new_user ...): /home/rolf/.gnucash/config-1.8.auto:13:15: Unbound variable: gnc:*options-entries* In /home/rolf/.gnucash/config-1.8.auto: 13: 0* (let ((option #)) ((lambda # #) option)) 13: 1* [gnc:lookup-option ... This is all there is? Nothing related to SQL or backend? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend
Rolf Leggewie wrote: The result was an error message Can't parse the URL /home/rolf/.gnucash/data/mysql:,,$server:gnucash:$user:$pw. What am I doing wrong? By reading some older messages from Phil to this list, I found out that overwriting an existing sqlite3 file with Save As might be another way to export the gnucash data to SQL. The result was very similar to the above. Can't parse the URL /tmp/test.db. There is some output on the console from where I started gnucash: /tmp/test.db:1: parser error : Start tag expected, '' not found SQLite format 3 ^ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend and dirty books
Phil, Quoting Phil Longstaff [EMAIL PROTECTED]: I've investigated my transaction/split issue further and it appears as though the problem is in how qof determines that the books are dirty. There is an alt_dirty_mode flag in qof and the gc engine sets it to TRUE. When this flag is set, *after* the backend is called during the second part of a qof_commit_edit() call, the collection and book are marked dirty. While this may be fine for an XML backend where the commit does not really save anything and therefore the books really *are* dirty, it is not OK for an SQL backend. If the concept of the books being dirty means that there is unsaved content, then the backend should have more control over that status. Derek's vision seems to be that sqlite replaces xml as the standard backend, and that xml might still exist as an import/export mechanism. If that is what is adopted (seems fine to me, but some concensus would be good), then I'll need to look at and possible clean up the whole dirty mechanism. It would be nice if there were more consensus on that. :) For now, I think I've fixed my problem by pushing the problem code down into the xml backend and out of qof. This means that a commit using the file backend *does* mark the books as dirty, whereas a commit using the sql backend does not. I think it depends on how you did it. I haven't looked at your changeset yet, but how I think it should work is: 1) In the first part of the commit, qof (or above) sets the object dirty. 2) The backend is called. The backend can clear the dirty flag if necessary. One issue is that there are still ways to dirty the book outside of a begin/commit. In particular modifications to a KVP Frame don't take a begin/commit and don't dirty the book. I don't see why the book would be dirtied AFTER the commit. You are correct that THAT is just broken. Phil -derek PS: I'm sorry I haven't responded to BOTH of your queries on this topic. I've been so busy with real work and some house construction that I've dropped lots of things on the floor, this being one of them. All I can say is I'm Sorry, and I'll try to be a little more responsive going forward. On the other hand, I start one month of travel in about 13 hours so I dont know how responsive I'll be while on the road, either. I'll do what I can, and maybe respond from 35,000 feet. ;) -- 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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend and dirty books
Hi Derek, I know you're not a fan of top posting, but that's how Yahoo mail does it. I assume that the only meaning to dirty is in the engine but not yet saved to permanent storage. Part of my frustration is tripping over *magic* stuff that is not adequately documented. The dirty/clean state of transactions and splits controls a lot of different activity during the transaction commit process, for example. I'd love to see sequence diagrams for the whole transaction commit process (hmm, maybe I should create them). Stuff happens in the background because there is a fairly intricate architecture. On a slight tangent, the UI and engine don't have the concept of a group of objects needing to be modified together. For example, if I scrub an account, I might create some lots, and modify some splits to refer to them. In the database, creating a lot and modifying the splits for the lot should be done atomically. Similarly, when a lot is deleted, clearing the split's lot_guid and deleting the lot should be done atomically, and in that order. When deleting a transaction, the splits should be deleted first. This is all in the name of 1) total data correctness, and 2) referential integrity. Since sql databases have a transaction idea, perhaps there should be some sort of gnc_engine_begin_edit()/gnc_engine_rollback_edit()/gnc_engine_commit_edit() to control a group of changes in multiple objects which need to be kept in sync (suggest alternate routine names if you want). Phil - Original Message From: Derek Atkins [EMAIL PROTECTED] To: Phil Longstaff [EMAIL PROTECTED] Cc: GnuCash development list gnucash-devel@gnucash.org Sent: Friday, July 11, 2008 9:43:42 PM Subject: Re: SQL backend and dirty books Phil, Quoting Phil Longstaff [EMAIL PROTECTED]: I've investigated my transaction/split issue further and it appears as though the problem is in how qof determines that the books are dirty. There is an alt_dirty_mode flag in qof and the gc engine sets it to TRUE. When this flag is set, *after* the backend is called during the second part of a qof_commit_edit() call, the collection and book are marked dirty. While this may be fine for an XML backend where the commit does not really save anything and therefore the books really *are* dirty, it is not OK for an SQL backend. If the concept of the books being dirty means that there is unsaved content, then the backend should have more control over that status. Derek's vision seems to be that sqlite replaces xml as the standard backend, and that xml might still exist as an import/export mechanism. If that is what is adopted (seems fine to me, but some concensus would be good), then I'll need to look at and possible clean up the whole dirty mechanism. It would be nice if there were more consensus on that. :) For now, I think I've fixed my problem by pushing the problem code down into the xml backend and out of qof. This means that a commit using the file backend *does* mark the books as dirty, whereas a commit using the sql backend does not. I think it depends on how you did it. I haven't looked at your changeset yet, but how I think it should work is: 1) In the first part of the commit, qof (or above) sets the object dirty. 2) The backend is called. The backend can clear the dirty flag if necessary. One issue is that there are still ways to dirty the book outside of a begin/commit. In particular modifications to a KVP Frame don't take a begin/commit and don't dirty the book. I don't see why the book would be dirtied AFTER the commit. You are correct that THAT is just broken. Phil -derek PS: I'm sorry I haven't responded to BOTH of your queries on this topic. I've been so busy with real work and some house construction that I've dropped lots of things on the floor, this being one of them. All I can say is I'm Sorry, and I'll try to be a little more responsive going forward. On the other hand, I start one month of travel in about 13 hours so I dont know how responsive I'll be while on the road, either. I'll do what I can, and maybe respond from 35,000 feet. ;) -- 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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Albert Lash [EMAIL PROTECTED] writes: That's right; each backend is basically stand-alone. Of course it shares a rough outline with the runtime object model and XML data-model, just by nature. Hmm, I'm a little confused now. The code referenced earlier had to do with GDM, right? And GDM was replaced by QOF? Or is it that they two are different, QOF is used for managing an XML flat file database, and GDM is used for SQL connectivity? I was thinking that QOF could be connected to a SQL backend via ODBC or something. (Not GDM... GDA, the GNOME Data Access layer. :) QOF is the Query Object Framework. It is, roughly: - a C-based object-model like thing (similar to gobject) - a lot of app utility code (similar to some of glib) - a facility for constructing queries that can be evaluated over the in-memory object graph, or translated into a form appropriate for a backend (such as a SQL-speaking relational DB). - a neutral backend layer, allowing a QOF object graph to be persisted either because of runtime object-field update events (i.., mutate the database bit by bit as things change), or on request (i.e., serialize the whole object graph to a file). We currently have a few backends: - src/backend/file: the gnucash XML file backend. - src/backend/postgres/: the historical gnucash PG DB backend. - src/backend/gda/: the in-development gnucash GDA-based DB backend in http://svn.gnucash.org/repo/gnucash/branches/gda-dev/. - lib/libqof/backend/file/: the QOF-provided, application-generic QOF Serialization Format (QSF) backend. - src/backend/dwi/: Linas' DWI/DUI-based backend; not used. Here's some links I found helpful in researching XBRL: [snip] Thanks for those. Is there a basic description of the GnuCash data model around anywhere? Is this information only accessible in the code at the moment? I've read about the accounts, transactions, and splits, and these coincide fairly well with pbooks' accounts, entries, entry_amounts, and transactions. http://svn.gnucash.org/docs/HEAD/ is the root of nightly-generated runs of doxygen over the source tree. http://svn.gnucash.org/docs/HEAD/group__Engine.html looks like a good entry point to talking about the Engine object model. If you grok XML and Relax-NG, then the XML file schema http://svn.gnucash.org/repo/gnucash/trunk/src/doc/xml/gnucash-v2.rnc might be useful ... the structure there maps pretty directly to the object model. -- ...jsled http://asynchronous.org/ - a=jsled; b=asynchronous.org; echo [EMAIL PROTECTED] pgpGsgjEeBg8n.pgp Description: PGP signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Albert Lash [EMAIL PROTECTED] writes: Am I correct in my assumption that this code isn't actually used with the current, non-SQL, backend? That's right; each backend is basically stand-alone. Of course it shares a rough outline with the runtime object model and XML data-model, just by nature. Thoughts? I'm doing a bunch of work on pbooks right now and if any of it can benefit gnucash, I'll try to make it to happen. If you see concrete opportunities for sharing, by all means write it up. I'm sure it's going to come down to the models being just different enough, either because of historical warts or just reasonably different application functionality, that they data models will want to be different. Not to discourage, but it feels like an extra level of effort to try to keep two application's data models in some sort of sync. At the same time, I'd be really interested to know how different from the XRBL model gnucash is. I could just look at the specs, I guess. -- ...jsled http://asynchronous.org/ - a=jsled; b=asynchronous.org; echo [EMAIL PROTECTED] pgpSr8JdDrfQ0.pgp Description: PGP signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
That's right; each backend is basically stand-alone. Of course it shares a rough outline with the runtime object model and XML data-model, just by nature. Hmm, I'm a little confused now. The code referenced earlier had to do with GDM, right? And GDM was replaced by QOF? Or is it that they two are different, QOF is used for managing an XML flat file database, and GDM is used for SQL connectivity? I was thinking that QOF could be connected to a SQL backend via ODBC or something. If you see concrete opportunities for sharing, by all means write it up. Definitely. I still have a lot of learning about how GnuCash works though, as well as work to do on PBooks. I'm sure it's going to come down to the models being just different enough, either because of historical warts or just reasonably different application functionality, that they data models will want to be different. Not to discourage, but it feels like an extra level of effort to try to keep two application's data models in some sort of sync. It very well could be more work than its worth, but its at least worth finding out! :-) It is doubtful that the fringe data required by a bookkeeping system (like customers, vendors, and inventory) could be kept in sync, but it might be possible and worthwhile for the core (financial data only - accounts and transactions) to be interoperable. At the same time, I'd be really interested to know how different from the XRBL model gnucash is. I could just look at the specs, I guess. Here's some links I found helpful in researching XBRL: http://gl.iphix.net/ http://xbrl.emporia.edu/2006/index.php Is there a basic description of the GnuCash data model around anywhere? Is this information only accessible in the code at the moment? I've read about the accounts, transactions, and splits, and these coincide fairly well with pbooks' accounts, entries, entry_amounts, and transactions. - Albert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Albert Lash [EMAIL PROTECTED] writes: [snip] Am I correct in my assumption that this code isn't actually used with the current, non-SQL, backend? I did a little reading on the GnuCash backend (QOF) and it seemed to me that there was a fair amount of abstraction going on. Correct, this binding is only part of the GDA backend. On the other hand I DO believe that (eventually) we might want to try to merge the column typing information into the QofParam definitions, but that's a little longer away. JMHO. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
keith [EMAIL PROTECTED] writes: I know there was discussion on the list last year of a data model that was being tested in pg and mysql. But I haven't seen it yet. As Derek says, it appears not to have made it into the branch. (Unless is has some other sneaky name.) It looks like the table and column schema is encoded in the sources. http://svn.gnucash.org/trac/browser/gnucash/branches/gda-dev/src/backend/gda/gnc-transaction-gda.c#L73 -- ...jsled http://asynchronous.org/ - a=jsled; b=asynchronous.org; echo [EMAIL PROTECTED] pgpa9Zmwfmpw1.pgp Description: PGP signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Yep, thanks for digging the gnc-transaction-gda.c code up. This is
sort of what I was looking for:
107 static col_cvt_t split_col_table[] =
108 {
109 { guid,CT_GUID, 0, COL_NNUL|COL_PKEY,NULL,
110 get_guid, set_guid },
111 { tx_guid,CT_GUID, 0, COL_NNUL,NULL,
112 get_split_tx_guid, set_split_tx_guid },
113 { memo,CT_STRING, SPLIT_MAX_MEMO_LEN,
COL_NNUL,SPLIT_MEMO },
114 { action,CT_STRING, SPLIT_MAX_ACTION_LEN,
COL_NNUL,SPLIT_ACTION },
115 { reconcile_state, CT_STRING, 1, COL_NNUL,NULL,
116 get_split_reconcile_state, set_split_reconcile_state },
117 { reconcile_date,CT_TIMESPEC, 0, COL_NNUL,NULL,
118 get_split_reconcile_date, set_split_reconcile_date },
119 { value,CT_NUMERIC, 0, COL_NNUL,NULL,
120 get_split_value, set_split_value },
121 { quantity,CT_NUMERIC, 0, COL_NNUL,NULL,
122 get_split_quantity, set_split_quantity },
123 { account_guid,CT_GUID, 0, COL_NNUL,NULL,
124 get_split_account_guid, set_split_account_guid },
125 { NULL }
126 };
127
128 static col_cvt_t guid_col_table[] =
129 {
130 { tx_guid, CT_GUID, 0, 0, NULL, get_guid, set_guid },
131 { NULL }
132 };
Am I correct in my assumption that this code isn't actually used with
the current, non-SQL, backend? I did a little reading on the GnuCash
backend (QOF) and it seemed to me that there was a fair amount of
abstraction going on.
My feelings about the bookkeeping model are that given its maturity
(several hundred years old now) and simplicity, there isn't really
much use to variations on a theme, so it should be feasible to have
a agnostic backend. That sounds kind of funny. :-)
Anyway, along these lines, I've been reading the xbrl specs (very good
stuff) for inspiration in the pbooks data model. They've done a great
job of modeling the bookkeeping and accounting concepts and although
I've merged some of their XML schema into the pbooks SQL model, it
would be nice to have some more convergence on these well defined data
structures.
Thoughts? I'm doing a bunch of work on pbooks right now and if any of
it can benefit gnucash, I'll try to make it to happen.
Albert
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
RE: SQL Backend?
Albert Lash wrote: Is the data model that is / was used for the SQL backend around anywhere? I don't know the answer to that question but there was a recent addition to contrib that has a PostgreSQL schema and a perl program for loading a gnucash datafile. K. This email communication and any files transmitted with it may contain confidential and or proprietary information and is provided for the use of the intended recipient only. Any review, retransmission or dissemination of this information by anyone other than the intended recipient is prohibited. If you receive this email in error, please contact the sender and delete this communication and any copies immediately. Thank you. http://www.encana.com ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Very interesting, that's probably what I'm looking for so I'll try to dig that up. On 7/20/07, Hale Boyes, Kevin [EMAIL PROTECTED] wrote: Albert Lash wrote: Is the data model that is / was used for the SQL backend around anywhere? I don't know the answer to that question but there was a recent addition to contrib that has a PostgreSQL schema and a perl program for loading a gnucash datafile. K. This email communication and any files transmitted with it may contain confidential and or proprietary information and is provided for the use of the intended recipient only. Any review, retransmission or dissemination of this information by anyone other than the intended recipient is prohibited. If you receive this email in error, please contact the sender and delete this communication and any copies immediately. Thank you. http://www.encana.com -- My Blogs: http://www.docunext.com/ http://www.albertlash.com/ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Albert Lash [EMAIL PROTECTED] writes: Is the data model that is / was used for the SQL backend around anywhere? Check the email archives. Phil had sent in some designs about a year ago or so.. But I dont think those designs made it into the gda-dev branch anywhere. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
The sql script for creating tables has lived at /src/backend/postgres/table-create.sql The version in the gda-dev branch was last changed in 2003, so it is somewhat poisonous. I know there was discussion on the list last year of a data model that was being tested in pg and mysql. But I haven't seen it yet. As Derek says, it appears not to have made it into the branch. (Unless is has some other sneaky name.) Derek Atkins wrote: Albert Lash [EMAIL PROTECTED] writes: Is the data model that is / was used for the SQL backend around anywhere? Check the email archives. Phil had sent in some designs about a year ago or so.. But I dont think those designs made it into the gda-dev branch anywhere. -derek ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
keith [EMAIL PROTECTED] writes: There was a thread going last year on the SQL backend. I'd be interested in testing/helping. I'm currently doing a lot of work in (shudder) Oracle, but I can install pg too. I checked out the svn, but haven't dug much further. It's the 'gda-dev' branch, but I dont think Phil's put much time into it recently. Keith -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
On 7/19/07, Derek Atkins [EMAIL PROTECTED] wrote: keith [EMAIL PROTECTED] writes: There was a thread going last year on the SQL backend. I'd be interested in testing/helping. I'm currently doing a lot of work in (shudder) Oracle, but I can install pg too. I checked out the svn, but haven't dug much further. It's the 'gda-dev' branch, but I dont think Phil's put much time into it recently. I know this is a feature I'm eager to have as well, but I can't devote any time to it right now. If you can help Phil with what he's got, that would be great! Nathan Keith -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Got Mole problems? Call Avogadro at 6.02 x 10^23. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL Backend?
Is the data model that is / was used for the SQL backend around anywhere? On 7/19/07, Nathan Buchanan [EMAIL PROTECTED] wrote: On 7/19/07, Derek Atkins [EMAIL PROTECTED] wrote: keith [EMAIL PROTECTED] writes: There was a thread going last year on the SQL backend. I'd be interested in testing/helping. I'm currently doing a lot of work in (shudder) Oracle, but I can install pg too. I checked out the svn, but haven't dug much further. It's the 'gda-dev' branch, but I dont think Phil's put much time into it recently. I know this is a feature I'm eager to have as well, but I can't devote any time to it right now. If you can help Phil with what he's got, that would be great! Nathan Keith -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Got Mole problems? Call Avogadro at 6.02 x 10^23. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- My Blogs: http://www.docunext.com/ http://www.albertlash.com/ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
Phil Longstaff [EMAIL PROTECTED] writes: Sorry, what I meant was that I didn't understand what you meant by small add-on.. I don't know GDA well-enough to know what that means or how that would work. Phil I haven't thought this through entirely, but these are my first thoughts. In order to specify the provider, we'll need the data URL to be sqlite://... or mysql://... or ... The current backend architecture is already plugin. Therefore, if I build a core backend built around libgda, then small wrapper backends for each sql db type, the core libgda piece can handle most of the heavy lifting, and the wrappers can adjust things as required (connection string, data type differences, ...). Ahh, so what you mean is that we'd have a libgncgda and then we'd implement a libgncmod-backend-sqlite and libgncmod-backend-mysql and libgncmod-backend-pg which all link against libgncgda? Or perhaps libgncgda would have multiple QOF backends, one for each of SQLite, MySQL, and PG? *ponders* So long as we can still have most of the code be shared, I suppose that works.. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
2006/10/30, Derek Atkins [EMAIL PROTECTED]: Phil Longstaff [EMAIL PROTECTED] writes: Sorry, what I meant was that I didn't understand what you meant by small add-on.. I don't know GDA well-enough to know what that means or how that would work. Phil I haven't thought this through entirely, but these are my first thoughts. In order to specify the provider, we'll need the data URL to be sqlite://... or mysql://... or ... The current backend architecture is already plugin. Therefore, if I build a core backend built around libgda, then small wrapper backends for each sql db type, the core libgda piece can handle most of the heavy lifting, and the wrappers can adjust things as required (connection string, data type differences, ...). Ahh, so what you mean is that we'd have a libgncgda and then we'd implement a libgncmod-backend-sqlite and libgncmod-backend-mysql and libgncmod-backend-pg which all link against libgncgda? Or perhaps libgncgda would have multiple QOF backends, one for each of SQLite, MySQL, and PG? *ponders* So long as we can still have most of the code be shared, I suppose that works.. Well, if you have a preconfigured DSN in gda (may using the graphical tool in libgnomedb) you just need to pass the name of the DSN, all the parameters is pre-difined when you create the datasource, even you can test and inspect the DSN using a GUI in libgnomedb. Then you can create a connection to a DB, and pass to GC that DSN using a sintax like: gda://database, then if you avoid any specific SQL's or use just the API in gda you can access, read/write, and update any data in the database, even this is true for Oracle, MS SQL Server, MDB or any provider supported by GDA. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- Trabajar, la mejor arma para tu superación de grano en grano, se hace la arena (R) (entrámite, pero para los cuates: LIBRE) ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Fri, 2006-10-27 at 12:56 -0400, Derek Atkins wrote: Frequency specs are another example, because a compound fs can have fs's as children. I dont know enough about FS to know whether they are also atomic objects like KVPs. They are, in the composite case. -- ...jsled http://asynchronous.org/ - `a=jsled; b=asynchronous.org; echo [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
Phil Longstaff [EMAIL PROTECTED] writes: Since the connection string will be db-specific, we may want a db core built around libgda (see libgda vs libdbi e-mail) with a small add-on to handle the connection string formatting and how guid's will be handled (as well as adding db-specific indexes, stored procedures, ...). Hmm.. I dont know how that might work.. But maybe that's because I don't know GDA well enough. Well, with libgda, you can make a connection in 2 ways. The first is to call an API and pass a pre-defined dataset name (dsn) which contains the provider name, connection string, and other info needed. This predefined dsn is a section in ~/.libgda/config. The other way is to call a different API and pass the provider name and connection string. The connection strings are similar but will contain different info. An sqlite connection string should contain a file name, while a mysql or pgsql string should contain a host name, port number and database name. Sorry, what I meant was that I didn't understand what you meant by small add-on.. I don't know GDA well-enough to know what that means or how that would work. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
On Sat, 2006-28-10 at 11:46 -0400, Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Since the connection string will be db-specific, we may want a db core built around libgda (see libgda vs libdbi e-mail) with a small add-on to handle the connection string formatting and how guid's will be handled (as well as adding db-specific indexes, stored procedures, ...). Hmm.. I dont know how that might work.. But maybe that's because I don't know GDA well enough. Well, with libgda, you can make a connection in 2 ways. The first is to call an API and pass a pre-defined dataset name (dsn) which contains the provider name, connection string, and other info needed. This predefined dsn is a section in ~/.libgda/config. The other way is to call a different API and pass the provider name and connection string. The connection strings are similar but will contain different info. An sqlite connection string should contain a file name, while a mysql or pgsql string should contain a host name, port number and database name. Sorry, what I meant was that I didn't understand what you meant by small add-on.. I don't know GDA well-enough to know what that means or how that would work. Phil I haven't thought this through entirely, but these are my first thoughts. In order to specify the provider, we'll need the data URL to be sqlite://... or mysql://... or ... The current backend architecture is already plugin. Therefore, if I build a core backend built around libgda, then small wrapper backends for each sql db type, the core libgda piece can handle most of the heavy lifting, and the wrappers can adjust things as required (connection string, data type differences, ...). Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Derek Atkins wrote: Mark Johnson [EMAIL PROTECTED] writes: I may be getting a bit ahead of the play here, since you are talking about design still, and I am thinking about implementation of it in a MySql backend. I was recently doing some research on storing GUIDs in MySQL. There is no guid type in MySql. There seems to be two different ways people store them - as a string (varchar) and as a varbinary. I suppose we could use varchar; the guid has a fixed size so we know exactly how much space it requires. It's just an MD5 hash, so it's 128 bits, which is 16 bytes of binary or 32 bytes of Hexstring. Varchar has the advantage of being human-readable. This may be very helpful during development. One may have to issue ad-hoc queries to check whether something worked or to help find out what went wrong. Currently, (MySql 5.0.x) only the InnoDb storage engine enforces foreign keys. This enforcement is especially important during development, when one expects to find and fix referential integrity bugs. However, using a GUID as a primary key in InnoDB has poor performance - the indices get very large. The recommendation for working around this is to use an auto-increment integer as the primary key and a guid as a secondary key. Also, MySql has a bug which recently cost me some time. Foreign keys expressed as column constraints rather than table constraints are silently ignored! It has been reported multiple times (MySql bugs 11049, 7427, 4919, 13301, 18917 and probably more). Honestly, for our usage I don't care so much about the database doing enforcement for us. All (write) access to the database should be through the GnuCash API; the database is just a data store. There's lots of referential logic that CANT be implement in the database, such as enforcing balanced transactions. I would suggest the use of foreign keys during development at least. I find it helpful in finding bugs in my code. Once something is debugged it should never violate referential integrity, and one could drop the foreign keys for production code. (For example, by using a different MySql storage engine with better performance on indexing GUIDs.) Besides, I don't care about performance of MySQL. I DO care about performance in SQLite. The GnuCash engine has no space to use a db-specific reference to an object; the engine always uses the GUID for that. So all the queries are GOING to be based on GUID. You're welcome to add an auto-increment integer primary key, but the queries necessarily cannot use it because that information just isn't available in the engine. C.f.: Database is just a data store. Hope this is helpful, Mark -derek Mark ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Thu, Oct 26, 2006 at 05:49:36PM +0300, Ivars Grinbergs wrote: Derek Atkins wrote: Daniel Espinosa [EMAIL PROTECTED] 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). You'd be better off creating a stored-procedure-based interface and having it enforce the semantics. -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Wed, Oct 25, 2006 at 08:39:18PM -0600, Mark Johnson wrote: Also, MySql has a bug which recently cost me some time. Foreign keys expressed as column constraints rather than table constraints are silently ignored! It has been reported multiple times (MySql bugs 11049, 7427, 4919, 13301, 18917 and probably more). See also http://sql-info.de/mysql/gotchas.html -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Thu, Oct 26, 2006 at 03:33:37PM -0400, Derek Atkins wrote: the list of requirements. Also, I dont think we can depend on stored procedures; SQLite doesn't support them. Dumb question... why would someone want to run SQLite over the existing storage mechanism? -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Fri, October 27, 2006 6:57 am, Jim C. Nasby wrote: Dumb question... why would someone want to run SQLite over the existing storage mechanism? Because it's the same database engine for both local file and remote server? Having said that I hope that the current XML file format be maintained as an export format - we check keep the thing in source control this way. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Quoting Jim C. Nasby [EMAIL PROTECTED]: On Thu, Oct 26, 2006 at 03:33:37PM -0400, Derek Atkins wrote: the list of requirements. Also, I dont think we can depend on stored procedures; SQLite doesn't support them. Dumb question... why would someone want to run SQLite over the existing storage mechanism? Because it would allow us to implenment save on commit -- so you would never ever lose data because it would be saved to the database every time you commit a new transaction? Or it that feature not something you care about? Also, moving to SQLite could make startup faster because it might not have to read the full dataset into RAM. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Jim C. Nasby [EMAIL PROTECTED] 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. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Graham Leggett [EMAIL PROTECTED] writes: Having said that I hope that the current XML file format be maintained as an export format - we check keep the thing in source control this way. That's the plan. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Benoit Gregoire [EMAIL PROTECTED] writes: Depends what you use them for. Stored procedure used for additional referential integrity check are not a problem, and saved my ass more time than I can count. Stored procedures that write to the database and actually implement business logics operations are (generally) not a very good idea. Fair enough, but that means you need to know what DB you're working with so you could send it the DB-specific code (or not).. Stored procedures are a double edged sword - very powerful, at the cost of database vendor lockin. For a multi platform application like Gnucash, vendor lockin makes no sense. What we need is to come up with a comon data model, and a set of features we decide to depend on (ex: Do we depend on the database supporting cascade of DELETE operations? Transactions (and to what level)?etc.). That's our baseline, the code will target that baseline (ie: note depend on any behaviour not provided by that baseline). However the opposite should also be true, the code should not depend on specific quiks of that baseline. Anything above can (and should) be handled for that specific database. Sure.. I think we absolutely MUST support SQLite, and we SHOULD support MySQL and PG. Does someone want to volunteer to find the LCD features of those three DBs? I'm not sure what you mean by cascade of DELETE operations. I think we CAN depend on the DB supporting transactions, but it might depend on what level of TXN support we want/need. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
Phil Longstaff [EMAIL PROTECTED] writes:
I suppose we could use varchar; the guid has a fixed size so we know
exactly how much space it requires. It's just an MD5 hash, so it's
128 bits, which is 16 bytes of binary or 32 bytes of Hexstring.
I am looking at having an int as the primary key for references to a
table. The GUID would be stored with the row as 4 ints (guid_1, guid_2,
guid_3 and guid_4). If we need to search a lot by GUID, we could have
an index which spans those 4 columns. I don't see there is a lot of
need, though.
I still don't understand why you want to do this. What does it buy
us? It seems to add a LOT of complexity on the GnuCash side when
building up SQL queries. Instead of just being able to print out
$table.${object}_id='$guid' we'd need a much more complicated SQL
generator routine.
-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
[EMAIL PROTECTED]PGP key available
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
On Fri, 2006-27-10 at 12:01 -0400, Derek Atkins wrote:
Phil Longstaff [EMAIL PROTECTED] writes:
I suppose we could use varchar; the guid has a fixed size so we know
exactly how much space it requires. It's just an MD5 hash, so it's
128 bits, which is 16 bytes of binary or 32 bytes of Hexstring.
I am looking at having an int as the primary key for references to a
table. The GUID would be stored with the row as 4 ints (guid_1, guid_2,
guid_3 and guid_4). If we need to search a lot by GUID, we could have
an index which spans those 4 columns. I don't see there is a lot of
need, though.
I still don't understand why you want to do this. What does it buy
us? It seems to add a LOT of complexity on the GnuCash side when
building up SQL queries. Instead of just being able to print out
$table.${object}_id='$guid' we'd need a much more complicated SQL
generator routine.
I'm changing this to char(16). I'm still concerned about 0x00 in a
GUID. The SQLite documentation says that memcmp is used to compare char
strings, so that is OK, but I don't know about other db's. MySQL
supports binary(16), but other db's may not.
Since the connection string will be db-specific, we may want a db core
built around libgda (see libgda vs libdbi e-mail) with a small add-on to
handle the connection string formatting and how guid's will be handled
(as well as adding db-specific indexes, stored procedures, ...).
Phil
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Fri, 2006-27-10 at 11:58 -0400, Derek Atkins wrote: I'm not sure what you mean by cascade of DELETE operations. I think we CAN depend on the DB supporting transactions, but it might depend on what level of TXN support we want/need. With foreign keys, you can specify what should happen if a row in another table is changed when the first table refers to the second one. For example, we could set it up that since a transaction split refers to the account it is in (foreign key), if an account is deleted, all splits referring to that account are also deleted. Or, if the account GUID is changed, all splits referring to that account will have the foreign key GUID changed. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
Quoting Phil Longstaff [EMAIL PROTECTED]:
I still don't understand why you want to do this. What does it buy
us? It seems to add a LOT of complexity on the GnuCash side when
building up SQL queries. Instead of just being able to print out
$table.${object}_id='$guid' we'd need a much more complicated SQL
generator routine.
I'm changing this to char(16). I'm still concerned about 0x00 in a
GUID. The SQLite documentation says that memcmp is used to compare char
strings, so that is OK, but I don't know about other db's. MySQL
supports binary(16), but other db's may not.
Make it char(32) and use the ascii hex-string representation.
Since the connection string will be db-specific, we may want a db core
built around libgda (see libgda vs libdbi e-mail) with a small add-on to
handle the connection string formatting and how guid's will be handled
(as well as adding db-specific indexes, stored procedures, ...).
Hmm.. I dont know how that might work.. But maybe that's because I
don't know GDA well enough.
Phil
-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
[EMAIL PROTECTED]PGP key available
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Quoting Phil Longstaff [EMAIL PROTECTED]: On Fri, 2006-27-10 at 11:58 -0400, Derek Atkins wrote: I'm not sure what you mean by cascade of DELETE operations. I think we CAN depend on the DB supporting transactions, but it might depend on what level of TXN support we want/need. With foreign keys, you can specify what should happen if a row in another table is changed when the first table refers to the second one. For example, we could set it up that since a transaction split refers to the account it is in (foreign key), if an account is deleted, all splits referring to that account are also deleted. Or, if the account GUID is changed, all splits referring to that account will have the foreign key GUID changed. Well, GnuCash already handles these cases. It wont let you delete an account if there are splits in it. Also, GUIDs cannot change, so that's never going to happen, either. I.e., gnucash itself already performs all these referential tests for you; the DB just needs to be a pure data store. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Fri, 2006-27-10 at 12:37 -0400, Derek Atkins wrote: Quoting Phil Longstaff [EMAIL PROTECTED]: On Fri, 2006-27-10 at 11:58 -0400, Derek Atkins wrote: I'm not sure what you mean by cascade of DELETE operations. I think we CAN depend on the DB supporting transactions, but it might depend on what level of TXN support we want/need. With foreign keys, you can specify what should happen if a row in another table is changed when the first table refers to the second one. For example, we could set it up that since a transaction split refers to the account it is in (foreign key), if an account is deleted, all splits referring to that account are also deleted. Or, if the account GUID is changed, all splits referring to that account will have the foreign key GUID changed. Well, GnuCash already handles these cases. It wont let you delete an account if there are splits in it. Also, GUIDs cannot change, so that's never going to happen, either. I.e., gnucash itself already performs all these referential tests for you; the DB just needs to be a pure data store. OK. I was just giving an example. A better one which comes to mind is with kvp frames. A kvp frame can be a number, string, date, list, or subframe. Each element of a list is in itself, a kvp frame, so there's a hierarchy here (and Josh mentioned how bad RDBMS's are with these). In this case, a slot can have a parent slot, either because it's a subframe or a member of a list. In this case, cascaded deletes help clean things up because the members of the list or subframes can be set to be automatically deleted when the parent is deleted. Now, if I want to delete a complete slot named X, I just delete the top level row in the table, and cascaded deletes take care of the others. Frequency specs are another example, because a compound fs can have fs's as children. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Fri, Oct 27, 2006 at 11:51:50AM -0400, Derek Atkins wrote: Jim C. Nasby [EMAIL PROTECTED] 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. 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. -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Quoting Jim C. Nasby [EMAIL PROTECTED]: On Fri, Oct 27, 2006 at 11:51:50AM -0400, Derek Atkins wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Quoting Phil Longstaff [EMAIL PROTECTED]: OK. I was just giving an example. A better one which comes to mind is with kvp frames. A kvp frame can be a number, string, date, list, or subframe. Each element of a list is in itself, a kvp frame, so there's a hierarchy here (and Josh mentioned how bad RDBMS's are with these). In this case, a slot can have a parent slot, either because it's a subframe or a member of a list. In this case, cascaded deletes help clean things up because the members of the list or subframes can be set to be automatically deleted when the parent is deleted. Now, if I want to delete a complete slot named X, I just delete the top level row in the table, and cascaded deletes take care of the others. KVP frames are easy to handle. KVPs are always loaded and stored as an atomic unit. You just delete the whole object and then write out the whole object every time: DELETE * from kvps where object_id=$guid; store_kvp; Frequency specs are another example, because a compound fs can have fs's as children. I dont know enough about FS to know whether they are also atomic objects like KVPs. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
On Fri, 2006-27-10 at 12:33 -0400, Derek Atkins wrote:
Quoting Phil Longstaff [EMAIL PROTECTED]:
I still don't understand why you want to do this. What does it buy
us? It seems to add a LOT of complexity on the GnuCash side when
building up SQL queries. Instead of just being able to print out
$table.${object}_id='$guid' we'd need a much more complicated SQL
generator routine.
I'm changing this to char(16). I'm still concerned about 0x00 in a
GUID. The SQLite documentation says that memcmp is used to compare char
strings, so that is OK, but I don't know about other db's. MySQL
supports binary(16), but other db's may not.
Make it char(32) and use the ascii hex-string representation.
OK
Since the connection string will be db-specific, we may want a db core
built around libgda (see libgda vs libdbi e-mail) with a small add-on to
handle the connection string formatting and how guid's will be handled
(as well as adding db-specific indexes, stored procedures, ...).
Hmm.. I dont know how that might work.. But maybe that's because I
don't know GDA well enough.
Well, with libgda, you can make a connection in 2 ways. The first is to
call an API and pass a pre-defined dataset name (dsn) which contains the
provider name, connection string, and other info needed. This
predefined dsn is a section in ~/.libgda/config. The other way is to
call a different API and pass the provider name and connection string.
The connection strings are similar but will contain different info. An
sqlite connection string should contain a file name, while a mysql or
pgsql string should contain a host name, port number and database name.
Phil
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
I think we absolutely MUST support SQLite, and we SHOULD support MySQL and PG. Does someone want to volunteer to find the LCD features of those three DBs? Here it is for SQLLite: http://www.sqlite.org/omitted.html Main areas that may bite us (i owuld think): -You can't drop a column (really annoying for schema updates) -No foreign key constraints (not a killer for us, but it means no ON DELETE CASCADE or UPDATE anywhere) But at least it has decent support for transactions, and some trigger support which is probably the only advanced features we would care about. MySQL (5+ using innodb) has fixed many of it's chronical shortcomings, an now has reasonnalbe support for transactions, and just got triggers. MySql won't be a problem, especially considering SQLLite's limitations. Postgresql has support for everything and the kitchen sink. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Wednesday 25 October 2006 15:57, Phil Longstaff wrote: I'd be happy to look at what you have. It seems to me that there will be enough differences that I won't be able to use much. I assume you used the Qt SQL classes rather than libgda or libdbi so I can't use much (if anything) of your implementation. Attached a copy of the DDL and a bit of description, mostly usage oriented. I agree that it's not a realistic goal to create a common database format; I was only proposing a possible exchange of ideas. If you think that any of the code could be of use, I'm happy to send it, or you can get it from CVS (sorry, don't use SVN yet) from Sourceforge via the KMM home page. You'll note that the design is pretty simplistic. This was a deliberate decision, in an attempt to make it as compatible as possible across various RDB implementations, especially 'lite' ones. For that reason, you won't find any CONSTRAINTS, or INDEXES (savvy users can always create their own for performance in their particular scenario; also, the user manual will be used to provide some advice on this). As Phil says, we use the Qt classes. Sadly, these don't provide any support for design changes, presumably because of the differences in implementation by the various RDB providers. This is probably going to make future extensions to the structure a bit of a problem. I'm hoping that the design of the KVP table will enable simple extensions to be implemented, but it's difficult to say without knowing what they'll be! As for larger changes, my current implementation allows for creating new tables without too much difficulty, but I'm not sure just how compatible are DDL statements to, for example, add a column to a table. Is it best to try to do this in code, or to provide separate 'database upgrade' scripts (which may need to be tailored) with new releases? Finally, the present KMM implementation merely treats the database as if it were a serial file, with no in-place update. I'm working on a 'proper' implementation as time permits, but if I understand correctly about your QOF framework, you'll probably have a head start on me. Good luck. -- Cheers, TonyB #[1]The KMyMoney Handbook [2]The KMyMoney Handbook [3]Anonymous file [4]Chapterà 21.à Reference Database Usage [5]Prev [6]Next Chapter 20. Database Usage Tony Bloomfield [EMAIL PROTECTED] Revision 0.9 (2005-12-16) Database usage Caution At the time of writing, the software described in this chapter is still in an experimental stage, and loss of data is possible. Please ensure that you have an alternative backup copy of your data before committing to database usage. Introduction As of release 1.0, KMyMoney allows you to hold your data in a relational database. One of the advantages of using this industry-standard format is that it may allow you to view your data using one of the graphic front ends such as OpenOffice.org©, perhaps in some format that KMyMoney currently doesn't provide. Also, a little knowledge of SQL (Structured Query Language, the language used world-wide to access relational databases) should enable you more easily to extract data to feed into some external program, as for example a budgeting application. It should be noted that the current release of the program merely treats the database in the same fashion as an ordinary file, in that it reads all the data into memory when opened, and writes updated data back when closed. A more conventional method of database access, i.e. reading and updating data only as required, is planned for a future release. Preparation To access the database, KMyMoney uses the SQL module supplied by Trolltech® as part of their Qt® programming system. This module supports a number of different database systems through a collection of drivers. Among the more popular open-source systems for which drivers are available are MySQL®, SQLite and PostgreSQL The module also supports the 'heavier', more industrial, systems such as Oracle® and IBM DB2® With the exception of SQLite, these systems use a client/server model, where the 'client' software sits on 'your' machine, whilst the server lives on the same machine as the database itself, which may be elsewhere on a network. Of course, in the normal scenario for a personal finance application such as KMyMoney, 'your' machine acts as both client and server. Your first task therefore, having decided which database system you wish to use, is to install the client, and most probably server, software. (SQLite does not operate on a client/server model; each database is held in a regular file, local or remote, accessed using the normal methods supplied by the underlying operating system. In this case, therefore, there is only one software package to install.) In addition to the database software itself, you must also install
Re: SQL backend for GnuCash 2
Mark Johnson [EMAIL PROTECTED] writes: I may be getting a bit ahead of the play here, since you are talking about design still, and I am thinking about implementation of it in a MySql backend. I was recently doing some research on storing GUIDs in MySQL. There is no guid type in MySql. There seems to be two different ways people store them - as a string (varchar) and as a varbinary. I suppose we could use varchar; the guid has a fixed size so we know exactly how much space it requires. It's just an MD5 hash, so it's 128 bits, which is 16 bytes of binary or 32 bytes of Hexstring. Currently, (MySql 5.0.x) only the InnoDb storage engine enforces foreign keys. This enforcement is especially important during development, when one expects to find and fix referential integrity bugs. However, using a GUID as a primary key in InnoDB has poor performance - the indices get very large. The recommendation for working around this is to use an auto-increment integer as the primary key and a guid as a secondary key. Also, MySql has a bug which recently cost me some time. Foreign keys expressed as column constraints rather than table constraints are silently ignored! It has been reported multiple times (MySql bugs 11049, 7427, 4919, 13301, 18917 and probably more). Honestly, for our usage I don't care so much about the database doing enforcement for us. All (write) access to the database should be through the GnuCash API; the database is just a data store. There's lots of referential logic that CANT be implement in the database, such as enforcing balanced transactions. Besides, I don't care about performance of MySQL. I DO care about performance in SQLite. The GnuCash engine has no space to use a db-specific reference to an object; the engine always uses the GUID for that. So all the queries are GOING to be based on GUID. You're welcome to add an auto-increment integer primary key, but the queries necessarily cannot use it because that information just isn't available in the engine. C.f.: Database is just a data store. Hope this is helpful, Mark -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Daniel Espinosa [EMAIL PROTECTED] writes: 1) Accounts: parent is not null. However, the top level accounts (Assets, Liabilities, ...) don't have a parent. In addition, the parent should be a foreign key reference back to the Accounts table. parent could be set to NULL if the acount is a 'top level'. Not in your design; you have it declared NOT NULL. 2) You have a Currencys (should be Currencies) table. However, tables which should have a foreign key reference to it just have type integer. Table is named 'currencies' now. The reference, if I understand you, is by the ID in the currencies table, and it is a integer. References should be by GUID. In addition, this should probably be a Commodities table to include stocks and mutual funds. I originaly included the StockTransactions, as a table derived from Transactions, now I renamed both to: 'commodities' (StockTransactions) and 'trasactions' (Transactions). Remember that in the 'commodities' table will have the same columns that 'transactions'. You need three tables: Transactions Splits Commodities 3) An account can have a type which is a commodity (for stock or mutual fund accounts). I have modified the types in the 'account_type' table, and now I have substituted the 'stock' and 'mutual found' types by 'commodity' type You don't need this table, and indeed including it could cause confusion. This table is already defined in src/engine/Account.h, making sure they keep in sync would be a major nightmare. 4) You need to split Transactions in Transactions and Splits. A Transaction will include the date (currently, both entry date and posting date (the one you see)), description, commodity (currency?). A I have renamed the Transactions table to 'transactions' and added the columns you refer, and the 'reconsilation_date' and 'reconsilation_state' No, you don't understand.. You really need two tables. And the reconciliation are on the Split, not the Tranasction. Thanks! -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Daniel Espinosa [EMAIL PROTECTED] writes: 2006/10/25, Josh Sled [EMAIL PROTECTED]: On Wed, 2006-10-25 at 14:52 -0500, Daniel Espinosa wrote: Let me study this items, but at first, I could say that the way GnuCash handles the numeric values and representation, could be out the scope of the schema becouse it will show how the values are stored in the database and how they are related with others. Well, to some degree ... e.g., we shouldn't try to pack a gnc_numeric structure into a blob or anything. But we shouldn't try to get around representing a gnc_numeric, either. Speaking of which... Values in GnuCash are generally represented as a rational value, represented by a numerator and denominator, each a 64bit integer. This GncNumeric is coupled with a specific commodity, usually implicitly, from the Account of the Split that the GncNumeric value is part of. That makes them able to store both currency (123.45 USD) as well as stocks and mutual funds (7.429 shares of fund XYZ). There's a bit more detail than that, but that's the core part. It looks like the PG Backend just stored the gnc_numeric Numerator, on the theory that the denominator was already known based on the Account and Commodity. At least this is what I see in src/backend/postgres/table.m4 I'm not sure if that's the right approach... [snip] Of course the efort must be to have working GnuCash database backend, the other is just an idea turning my mind, but nothing to worry about. Go to make this to work. Good to hear. :) -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Daniel Espinosa [EMAIL PROTECTED] 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. I also don't care about sharing with other programs. I /AM/ worried about making sure the SQL code works across multiple databases (SQLite is primary), and I'm worried about that table, which really is just a copy of the data in src/engine/Account.h, changing out from under gnucash and then destroying the data because someone thought they could change that table. 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. 2) You don't need a StockTransaction table. You should model the Transaction and Split tables like the current PG backend. Please see the version 0.2. I'll take a look and respond shortly.. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Derek Atkins wrote: Daniel Espinosa [EMAIL PROTECTED] 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). 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. Ivars ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
Quoting Phil Longstaff [EMAIL PROTECTED]: I am looking at having an int as the primary key for references to a table. The GUID would be stored with the row as 4 ints (guid_1, guid_2, guid_3 and guid_4). If we need to search a lot by GUID, we could have an index which spans those 4 columns. I don't see there is a lot of need, though. GUIDs are currently used as the reference id in the XML format. In the SQL format, though, int keys should work fine. The only need for GUIDs that I can see is to retain a common set of object ids with past copies of a file i.e. if I archive some data, the set of accounts and tx data in the SQL database must have the same ids as the set of accounts and tx data in the archive. Let me rephrase. QOF uses the GUIDs for references! Yes, it's used in XML because it can, but QOF specifically uses it, too. When you want to reference a QOF object all you have is the type and GUID. Phil -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Thursday 26 October 2006 10:02, Derek Atkins wrote: Daniel Espinosa [EMAIL PROTECTED] 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. Yes you can (whith a real database). Checking that the sum of the transaction's splits is 0 it trivial if all splits use the same commodity, and mostly irrelevent if they don't. That's a really simple stored procedure. I also don't care about sharing with other programs. While I fully understand your feeling, sharing with other programs is probably the primary reason why users want a SQL in the first place. Since we KNOW people will use it this way, we should try design it in such a way that it can be made as safe as possible. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Quoting Benoit Gregoire [EMAIL PROTECTED]: You can't get enough data integrity from the database. For example, you cannot define the database in a way to enforce balanced transactions. Yes you can (whith a real database). Checking that the sum of the transaction's splits is 0 it trivial if all splits use the same commodity, and mostly irrelevent if they don't. That's a really simple stored procedure. I also don't care about sharing with other programs. While I fully understand your feeling, sharing with other programs is probably the primary reason why users want a SQL in the first place. Since we KNOW people will use it this way, we should try design it in such a way that it can be made as safe as possible. Actually, I think the primary reason users want SQL are to be able to run their own reports, multi-user, and automatic commits (saves on commit). I dont think that sharing the data read/write is high on the list of requirements. Also, I dont think we can depend on stored procedures; SQLite doesn't support them. -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 [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GUIDs (was Re: SQL backend for GnuCash 2)
On Thursday 26 October 2006 14:49, Phil Longstaff wrote: On Thu, 2006-26-10 at 09:53 -0400, Derek Atkins wrote: Mark Johnson [EMAIL PROTECTED] writes: I may be getting a bit ahead of the play here, since you are talking about design still, and I am thinking about implementation of it in a MySql backend. I was recently doing some research on storing GUIDs in MySQL. There is no guid type in MySql. There seems to be two different ways people store them - as a string (varchar) and as a varbinary. I suppose we could use varchar; the guid has a fixed size so we know exactly how much space it requires. It's just an MD5 hash, so it's 128 bits, which is 16 bytes of binary or 32 bytes of Hexstring. I am looking at having an int as the primary key for references to a table. The GUID would be stored with the row as 4 ints (guid_1, guid_2, guid_3 and guid_4). If we need to search a lot by GUID, we could have an index which spans those 4 columns. I don't see there is a lot of need, though. Seriously, the GUID is the real primary key to every object. The SQL schema should reflect that. Besides, that MySql is slow on varchars is quite frankly not our problem, they'll fix it eventually. Not to mention that dealing with serials across databases will add totally useless complexity. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Thursday 26 October 2006 10:49, Ivars Grinbergs wrote: Derek Atkins wrote: Daniel Espinosa [EMAIL PROTECTED] 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). Perfectly true, but that's not e reasaon to designe our schema to make it more difficult thatn it can be. 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. Enums are NOT standard SQL. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Actually, I think the primary reason users want SQL are to be able to run their own reports, multi-user, and automatic commits (saves on commit). I dont think that sharing the data read/write is high on the list of requirements. Also, I dont think we can depend on stored procedures; SQLite doesn't support them. We obviously can't depend on them. That doesn't mean having them in Postgres (for example) isn't a usefull thing. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
Benoit Gregoire wrote: Yes you can (whith a real database). Checking that the sum of the transaction's splits is 0 it trivial if all splits use the same commodity, and mostly irrelevent if they don't. That's a really simple stored procedure. And in the process, you restrict people to using just one real database, instead of any database convenient to them. Stored procedures are a double edged sword - very powerful, at the cost of database vendor lockin. For a multi platform application like Gnucash, vendor lockin makes no sense. Regards, Graham -- smime.p7s Description: S/MIME Cryptographic Signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: SQL backend for GnuCash 2
On Thursday 26 October 2006 15:51, Graham Leggett wrote: Benoit Gregoire wrote: Yes you can (whith a real database). Checking that the sum of the transaction's splits is 0 it trivial if all splits use the same commodity, and mostly irrelevent if they don't. That's a really simple stored procedure. And in the process, you restrict people to using just one real database, instead of any database convenient to them. Depends what you use them for. Stored procedure used for additional referential integrity check are not a problem, and saved my ass more time than I can count. Stored procedures that write to the database and actually implement business logics operations are (generally) not a very good idea. Stored procedures are a double edged sword - very powerful, at the cost of database vendor lockin. For a multi platform application like Gnucash, vendor lockin makes no sense. What we need is to come up with a comon data model, and a set of features we decide to depend on (ex: Do we depend on the database supporting cascade of DELETE operations? Transactions (and to what level)?etc.). That's our baseline, the code will target that baseline (ie: note depend on any behaviour not provided by that baseline). However the opposite should also be true, the code should not depend on specific quiks of that baseline. Anything above can (and should) be handled for that specific database. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
