Re: python GnuCash interface to SQL backend
Sébastien de Menten sdemen...@gmail.com writes: On Monday, November 17, 2014, Derek Atkins warl...@mit.edu wrote: I think most of our beef against your project is that you're making it read-write. If it was read-only then nobody here would care. Yes indeed. Me first needs are a) to read a GnuCash boom from python and b) to create some new objects (accounts, commodities, transactions splits, prices). This is what is implemented and works today. Updating/deleting existing objects is delicate as is the creation of more complex business objects (relying on the kvp) - not in scope The problem is that you cannot skip this complexity, because you run the risk of creating an object that fails the unwritten gnucash data invariants. There are expected data entries that gnucash makes and will cause... confusion... if they are not there. (E.g. the date in the kvp) So I should have say that it is a CR (not UD) interface to GnuCash books. R is fine. C, U, and D are dangerous. I still encourage you to make the SWIG python bindings more pythonic and use that instead of trying to reverse-engineer the gnucash business logic, especially if you want C, U, and D. Sebastien -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: python GnuCash interface to SQL backend
Sébastien de Menten sdemen...@gmail.com writes: On Fri, Nov 14, 2014 at 3:33 AM, Derek Atkins warl...@mit.edu wrote: John Ralls jra...@ceridwen.us writes: What’s your goal here? I don’t think that reimplementing GnuCash in Python with GnuCash’s SQL schema is a particularly good approach: It’s not exactly the most efficient design. Rather, it’s designed to mirror the XML schema. You’ll have a better design if you relegate GnuCash SQL to import/export. I agree wholeheartedly. Don't do it this way. Use the GnuCash Python bindings. If you don't like the current structure of them, then fix that. This way your apps will always work because the bindings will stay in lockstep with any changes that get made. Hello Derek, The GnuCash python bindings are C/SWIG based. This causes some issues on windows, and requires deep knowledge of C, SWIG and the GnuCash C api to contribute to. You're right, it causes issues on windows. That's because the bindings are built against a specific version of Python and windows doesn't have a canonical version. So we would have to build the wrappers for a specific version, and possibly even ship it. Not worth it for the very few people that actually care about Python on Windows. As for requiring deep knowledge of C and SWIG, that's completely false. You need a little knowledge about it, sure. But if your goal was to make the Python bindings more Pythonic, I believe you could do that by wrapping the existing SWIGified APIs with more Pythonic wrappers. That would definitely NOT require a deep knowledge of C nor SWIG, although yes, it would require a deeper knowledge of the GnuCash C API. The piecash python bindings are a pure python package (pip install piecash and you're up and running) and works on the SQL tables through the SQL Alchemy library. It is only 500 SLOC today (and may grow in the future but not by an order of magnitude). As it is short and in python, it is rather easy to contribute/hack/extend. There's another project, jGnuCashLib, that's a pure Java implementation. So there is precedent for outside projects to go behind GnuCash's back to the data file. However, I don't know if jGnuCashLib is read-write or read-only. However there have been issues in that it only implements the XML backend, doesn't support compression, and has had issues across GnuCash releases. I think most of our beef against your project is that you're making it read-write. If it was read-only then nobody here would care. -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: python GnuCash interface to SQL backend
On Monday, November 17, 2014, Derek Atkins warl...@mit.edu wrote: I think most of our beef against your project is that you're making it read-write. If it was read-only then nobody here would care. Yes indeed. Me first needs are a) to read a GnuCash boom from python and b) to create some new objects (accounts, commodities, transactions splits, prices). This is what is implemented and works today. Updating/deleting existing objects is delicate as is the creation of more complex business objects (relying on the kvp) - not in scope So I should have say that it is a CR (not UD) interface to GnuCash books. Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Saturday, November 15, 2014, Christian Stimming christ...@cstimming.de wrote: Dear Sébastien, I really try not to be rude, but a little bit it seems to me as if you don't accept no as an answer here. You asked whether the gnucash developers support an alternative SQL access layer written in python from scratch, and John's and other answers clearly said no. What else are you looking for? Hello Christian, I don't think I have asked the question you point here above but I admit I have asked a lot of other questions (to say the least ;-) ) and I got the answers of John and Derek right : they do not think it can be successful to write an external application/library that works directly on the SQL database due mainly to the complexity of the GnuCash engine (and how it handles/keeps the data in sync) John as already outlined many important aspects about our object model. In case you haven't see this so far, some current documentation is also here http://wiki.gnucash.org/wiki/C_API and the linked Entity-Relationship Diagram there, http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png . Thank you for the links (I had read these pages before starting working on the project and there were very useful). But let's just make this clear: You asked whether your idea would be endorsed and supported by us, and the answer was clearly a no. If you like to continue your idea, feel free to do so. But just don't repeatedly discuss here whether we want to change our answer (at this point in time). Thanks! As written here above, I have not asked this question. To recap, I could/can not find a satisfying solution to my needs (work on GnuCash books from python): - going the XML way (instead of SQL) has been suggested but suffers from all the drawbacks from the SQL way (data corruption) - using the official python binding is painful for me (swig/C code, install on windows) I have done in the summer a pure python package that worked for my needs. As this was very useful, I planned to make it more robust and release it. Before doing that I wanted to notify you/the core devs and get some advice, which I got thanks to the time John and Derek spent on this thread (and I can't be more grateful to them). I continue to work on piecash and it is going smoothly but I have been warned of the difficulties lying ahead. Kind regards Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
Hello John, I have put at this address https://github.com/sdementen/piecash/blob/master/docs/source/object_model.rst what I understood from the object model of GnuCash (schema/fields/invariants). I have also added some questions regarding the objects for which you may have the answer... (or these may be somewhere in GnuCash docs I could not find) I would be keen to get your opinion on this document. kind regards, Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Nov 15, 2014, at 7:08 AM, Sébastien de Menten sdemen...@gmail.com wrote: Hello John, I have put at this address https://github.com/sdementen/piecash/blob/master/docs/source/object_model.rst https://github.com/sdementen/piecash/blob/master/docs/source/object_model.rst what I understood from the object model of GnuCash (schema/fields/invariants). I have also added some questions regarding the objects for which you may have the answer... (or these may be somewhere in GnuCash docs I could not find) I would be keen to get your opinion on this document. Sébastien, It’s a gross simplification which is wrong in several important respects. The most significant error is the Price table, which is not a central data object, it’s just a record of prices/exchange rates at particular times. Before GnuCash 2.6 it didn’t even contain actual prices booked from transactions. Prices from the price table are used to calculate the default currency value in the Accounts page and on the summary bar, and for some reports. Prices in the register are calculated by the register code at the time of display, and editing the price in the Transfer Dialog changes the value. Another: Transactions *must* balance on value; if a submitted transaction doesn’t balance GnuCash will create a balancing split debited to Imbalance-XXX where XXX is the appropriate currency code for the side of the transaction that’s out of balance. While it’s true that the SQL schema for Account doesn’t include a direct book reference, it does have a parent-GUID field which eventually ties it to the root account which, as you noted, is referenced by the book. While the stored state allows only one book per file or database, the importers work by creating a second book in memory, writing the imported records to that book, then merging the second book into the primary one. That would fail if the accounts couldn’t be traced back to the right book. There’s more, but I’m no more inclined to do a detailed review of and response to your document than I am to review your code. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
Dear Sébastien, I really try not to be rude, but a little bit it seems to me as if you don't accept no as an answer here. You asked whether the gnucash developers support an alternative SQL access layer written in python from scratch, and John's and other answers clearly said no. What else are you looking for? John as already outlined many important aspects about our object model. In case you haven't see this so far, some current documentation is also here http://wiki.gnucash.org/wiki/C_API and the linked Entity-Relationship Diagram there, http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png . But let's just make this clear: You asked whether your idea would be endorsed and supported by us, and the answer was clearly a no. If you like to continue your idea, feel free to do so. But just don't repeatedly discuss here whether we want to change our answer (at this point in time). Thanks! Regards, Christian ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
First of all, thank you John for taking the time to answer to this thread ! If you see GnuCash from the (limited) perspective of an editor for a Book document (as LibreOffice Writer is an editor for a ODT document), object persistence is central. And luckily we have both a very clean and well designed object model in GnuCash as well as standard persistence formats (xml, SQL and not an obscure binary format). I don’t share either opinion, especially about the object model in GnuCash. When talking about the object model, I am referring to the entities (Account, Book, Split, ...), their relation and attributes. And for most of the core objects (excluding company, invoice, ...) it is rather clean and efficient. Personnally, I think it could be a great basis for an open document format for this domain of application (better than QIF co). In terms of the implementation itself of the object model, the main things I see not that clean are: - KVP vs field representation - XXX_denom / XXX_num split (instead of using a Decimal type) But these two points should be hidden for the user/developper in python bindings (official or piecash). The GnuCash documentation states that the XML document can be used to get the GnuCash data in whatever other format (see Converting XML GnuCash File http://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html) through XLST transformation and vice-versa (so generating a new/transformed gnucash XML file from a LibreOffice spreadsheet). No matter what the GnuCash documentation might say, only someone with a deep understanding of GnuCash could successfully create a correct GnuCash data file from a spreadsheet document using XSLT. I’m not familiar with LibreOffice’s ODS format, but I expect that it would take deep knowledge of that format to successfully extract useful data from it with XSLT as well. I agree that creating GnuCash file through XSLT is probably clause to be a nightmare ... that is why I quickly dropped the XML route (on ODS, I just took what was written in the GnuCash doc, and indeed interacting with such document require some library). PieCash (I like your name !) aims to fulfill exactly this purpose, no more, no less. As with the XLST transforms, it allows to do CRUD operations on the objects within a GnuCash Book. CRUD operations on a GnuCash database will corrupt it. The GnuCash schema is not normalized, and not all of the necessary data is stored in the table associated with the objects. CRUD operations is not to be taken as pure CRUD operations, there should always be in piecash a check that the set of objects is self-consistent (but this is really simple, obvious, natural constrains if one know a bit the GnuCash object model) Could you point me to 2 or 3 real case example we would nevertheless end up corrupting the database ? It would really help me to materialise this risk. With my practical experience with piecash (creating a full account tree structure and importing thousands of transactions from csv file), I haven't found any case of corruption (except when developing the API and having obvious bugs). Has this stance on the manipulation outside GnuCash of a GnuCash document evolved since it was written ? Would this still be supported after the C++ rewrite ? It isn’t supported now. It never has, and it is unlikely that it ever will be, even if we are able, after several development cycles, to actually migrate to a 3N database schema. There is too much logic that is encoded in the program and which cannot be portably encoded in a SQL database, to make that feasible. For piecash, there is no need for a 3N db schema and there is no need to write any logic in SQL ! It is through SQLAlchemy that we can handle/encapsulate the extra bit of logic (which is, as far as I am in the development of piecash and given piecash scope, rather limited). Nothing is done in SQL itself. All the rest is handled automatically thanks to the SQLAlchemy layer (link between objects, cascade delete, locking and transactions, generation of GUID key, …). Won’t work. SQLAlchemy can’t automatically generate a correct class from the schema nor can it derive a correct table description from the C headers. The table schema is exactly the only piece of code that needs to be written (piecash is in fact just that, table schemas with some metadata). I do not hope/expect/think to generate this code automatically from C headers (as SWIG does). You’ll get closer working with the XML schema. There’s a reasonably up-to-date version in src/doc/xml/gnucash-v2.rnc. As written here above, XML is not easy to work with (at least for me) and does not save me of anything you said before (corruption, etc). So, I admit, the main effort in this project consisted in writing for each entity (book, account, etc) the equivalent here above code as well as the relationships between entities. The later is done with a syntax
Re: python GnuCash interface to SQL backend
On Fri, Nov 14, 2014 at 3:33 AM, Derek Atkins warl...@mit.edu wrote: John Ralls jra...@ceridwen.us writes: What’s your goal here? I don’t think that reimplementing GnuCash in Python with GnuCash’s SQL schema is a particularly good approach: It’s not exactly the most efficient design. Rather, it’s designed to mirror the XML schema. You’ll have a better design if you relegate GnuCash SQL to import/export. I agree wholeheartedly. Don't do it this way. Use the GnuCash Python bindings. If you don't like the current structure of them, then fix that. This way your apps will always work because the bindings will stay in lockstep with any changes that get made. Hello Derek, The GnuCash python bindings are C/SWIG based. This causes some issues on windows, and requires deep knowledge of C, SWIG and the GnuCash C api to contribute to. The piecash python bindings are a pure python package (pip install piecash and you're up and running) and works on the SQL tables through the SQL Alchemy library. It is only 500 SLOC today (and may grow in the future but not by an order of magnitude). As it is short and in python, it is rather easy to contribute/hack/extend. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Fri, Nov 14, 2014 at 3:31 AM, Derek Atkins warl...@mit.edu wrote: Sébastien de Menten sdemen...@gmail.com writes: Where could I find detailed documentation on the GnuCash engine (and the constrains/invariants GnuCash enforces) ? Or would there be some code/program to check a GnuCash file is sane/consistent ? Only in the implementation. There is no documentation per se on this, because we do not support modification of the database from outside the GnuCash APIs. This also allows us to change the underlying storage mechanisms without breaking things, because it's all abstracted. By re-implementing it you're basically binding yourself to a particular version of the database schema, which can (and will) change over time, requiring you to duplicate the effort already happening in the gnucash code. Moreover, you're also tied to a particular backend, which isn't very nice. Indeed, piecash is only applicable to the SQL backend and depends on the version of the SQL schema used by GnuCash. But as GnuCash is rather good at keeping backward compatibility, my fears are not so high in this respect (we can a minor changes but that is ok). I can add a check on the different rows of the VERSION table to ensure explicitly that the schema has not changed. A major rewrite of GnuCash and/or the SQL backend would require a major rewrite of piecash, but that's OK, we are talking about 500 SLOC. If you want to modify the gnucash database, you really should use the exported GnuCash APIs. If the current python bindings aren't pythonic enough for you, then I urge you to spend the time to fix that instead of reimplementing something that will absolutely break some time in the future. When it will break I cannot tell you, but I can assure you it WILL break at some point. I agree, it requires indeed to follow the evolution of GnuCash SQL schema. But these changes are well documented in the changelog. The official python bindings are C/SWIG based and are more complex to understand than the 500 SLOC of piecash. I have a preference to work with piecash even if I understand that it fragments the python bindings situation. But ok, I can't forbid myself to scratch this itch :-) kind regards, Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Nov 14, 2014, at 4:28 AM, Sébastien de Menten sdemen...@gmail.com wrote: First of all, thank you John for taking the time to answer to this thread ! If you see GnuCash from the (limited) perspective of an editor for a Book document (as LibreOffice Writer is an editor for a ODT document), object persistence is central. And luckily we have both a very clean and well designed object model in GnuCash as well as standard persistence formats (xml, SQL and not an obscure binary format). I don’t share either opinion, especially about the object model in GnuCash. When talking about the object model, I am referring to the entities (Account, Book, Split, ...), their relation and attributes. And for most of the core objects (excluding company, invoice, ...) it is rather clean and efficient. Personnally, I think it could be a great basis for an open document format for this domain of application (better than QIF co). In terms of the implementation itself of the object model, the main things I see not that clean are: - KVP vs field representation - XXX_denom / XXX_num split (instead of using a Decimal type) But these two points should be hidden for the user/developper in python bindings (official or piecash). I experimented with the available decimal libraries over the summer. They’re mostly too slow and they don’t afford enough control over rounding to be sufficiently accurate for accounting use. The object model as it stands now has too much interdependence between classes, especially the transaction, split, account, and commodity classes. The implementation has a lot of The GnuCash documentation states that the XML document can be used to get the GnuCash data in whatever other format (see Converting XML GnuCash File http://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html http://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html) through XLST transformation and vice-versa (so generating a new/transformed gnucash XML file from a LibreOffice spreadsheet). No matter what the GnuCash documentation might say, only someone with a deep understanding of GnuCash could successfully create a correct GnuCash data file from a spreadsheet document using XSLT. I’m not familiar with LibreOffice’s ODS format, but I expect that it would take deep knowledge of that format to successfully extract useful data from it with XSLT as well. I agree that creating GnuCash file through XSLT is probably clause to be a nightmare ... that is why I quickly dropped the XML route (on ODS, I just took what was written in the GnuCash doc, and indeed interacting with such document require some library). PieCash (I like your name !) aims to fulfill exactly this purpose, no more, no less. As with the XLST transforms, it allows to do CRUD operations on the objects within a GnuCash Book. CRUD operations on a GnuCash database will corrupt it. The GnuCash schema is not normalized, and not all of the necessary data is stored in the table associated with the objects. CRUD operations is not to be taken as pure CRUD operations, there should always be in piecash a check that the set of objects is self-consistent (but this is really simple, obvious, natural constrains if one know a bit the GnuCash object model) Could you point me to 2 or 3 real case example we would nevertheless end up corrupting the database ? It would really help me to materialise this risk. With my practical experience with piecash (creating a full account tree structure and importing thousands of transactions from csv file), I haven't found any case of corruption (except when developing the API and having obvious bugs). I can’t provide concrete examples without doing an extensive code review of piecash, for which I have neither the time nor the inclination. Some obvious trouble spots include cross-commodity transactions, especially involving lots or trading accounts. Have you tested with bad data to see if piecash rejects it? Did you thoroughly analyze the ways that bad data could be created and ensure that you have test cases proving that piecash rejects all of them? Has this stance on the manipulation outside GnuCash of a GnuCash document evolved since it was written ? Would this still be supported after the C++ rewrite ? It isn’t supported now. It never has, and it is unlikely that it ever will be, even if we are able, after several development cycles, to actually migrate to a 3N database schema. There is too much logic that is encoded in the program and which cannot be portably encoded in a SQL database, to make that feasible. For piecash, there is no need for a 3N db schema and there is no need to write any logic in SQL ! It is through SQLAlchemy that we can handle/encapsulate the extra bit of logic (which is, as far as I am in the development of piecash and given
Re: python GnuCash interface to SQL backend
On Friday, November 14, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 14, 2014, at 4:28 AM, Sébastien de Menten sdemen...@gmail.com javascript:_e(%7B%7D,'cvml','sdemen...@gmail.com'); wrote: In terms of the implementation itself of the object model, the main things I see not that clean are: - KVP vs field representation - XXX_denom / XXX_num split (instead of using a Decimal type) But these two points should be hidden for the user/developper in python bindings (official or piecash). I experimented with the available decimal libraries over the summer. They’re mostly too slow and they don’t afford enough control over rounding to be sufficiently accurate for accounting use. The object model as it stands now has too much interdependence between classes, especially the transaction, split, account, and commodity classes. The implementation has a lot of When looking at the SQL/XML document, what would be the unneeded interdependencies ? The links between account-split-transaction looks meaningful to me. The links transaction-currency and account-commodity also. Is it the issues with the scu/denom/num that is cumbersome to handle (if there is a change in the scu of a currency, all splits related to it should be updated) ? Could you point me to 2 or 3 real case example we would nevertheless end up corrupting the database ? It would really help me to materialise this risk. With my practical experience with piecash (creating a full account tree structure and importing thousands of transactions from csv file), I haven't found any case of corruption (except when developing the API and having obvious bugs). I can’t provide concrete examples without doing an extensive code review of piecash, for which I have neither the time nor the inclination. Some obvious trouble spots include cross-commodity transactions, especially involving lots or trading accounts. Have you tested with bad data to see if piecash rejects it? Did you thoroughly analyze the ways that bad data could be created and ensure that you have test cases proving that piecash rejects all of them? Well, at first, I wanted to have a simple way to extract data out of GnuCash (a read-only mode). This was easy to do with SQL alchemy and is safe. Afterwards, I wanted to be able to modify a GnuCash book knowing what I was doing (ie being cautious to not create inconsistent objects. This was also ok (as said, I have used piecash to create automatically a complex account structure from an excel file and to import thousands of records without errors). Next is the ability to ensure consistency checks/error detection when the user does changes. This is the less mature part and I hear well your warnings about the complexity of this part. For piecash, there is no need for a 3N db schema and there is no need to write any logic in SQL ! It is through SQLAlchemy that we can handle/encapsulate the extra bit of logic (which is, as far as I am in the development of piecash and given piecash scope, rather limited). Nothing is done in SQL itself. I have trouble believing that an ORM will generate a correct implementation with a non-normal schema. In piecash, the ORM does not generate the SQL schema as it already exists (it is the one defined by GnuCash). It just maps the existing schema (that needs to be redescribed in python) to python objects transparently handling type conversion, transactions, guids, relationships, etc The table schema is exactly the only piece of code that needs to be written (piecash is in fact just that, table schemas with some metadata). I do not hope/expect/think to generate this code automatically from C headers (as SWIG does). You’re not being consistent. Are you using SQLAlchemy as an ORM or simply as a SQL abstraction layer? You said above that nothing is done in SQL, and earlier that you are doing consistency checks, but here you’re saying that all you’ve written is a table-schema, which would imply that you’re relying on the database to do all of the work. I am using sqlalchemy as a mapper and as a session manager (unit of work pattern). Nothing is done in SQL (meaning I do not write SQL queries nor SQL stored procedures). A cascade delete constrain (if I remove a transaction, all related splits are automatically deleted) is also managed by SA. So I am essentially writing a mapping and then adding some methods to either create objects in a consistent way (like having a function create_transaction(from_acc, to_acc, amount, date, description) that create always correct objects) or to check objects are consistent before commit (this is not yet done) You’ll get closer working with the XML schema. There’s a reasonably up-to-date version in src/doc/xml/gnucash-v2.rnc. As written here above, XML is not easy to work with (at least for me) and does not save me of anything you said before (corruption, etc). Sounds like a learning opportunity. But will this save me
Re: python GnuCash interface to SQL backend
On Wednesday, November 12, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com javascript:; wrote: I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book. The risk of writing to the database outside of GnuCash, whether in SQL or XML, is that unless you are very careful and have a deep understanding of how GnuCash works that you will irretrievably corrupt your accounting data. There is no business logic encoded in the SQL database, so your code must replicate the GnuCsah engine code to ensure that all required fields are computed and stored correctly. Much of GnuCash is neither straightforward nor obvious and some critical data are stored outside of the primary tables, usually to preserve backward compatibility with previous versions. Regards, John Ralls I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a wrong impression ? Where could I find detailed documentation on the GnuCash engine (and the constrains/invariants GnuCash enforces) ? Or would there be some code/program to check a GnuCash file is sane/consistent ? Regards Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Thursday, November 13, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 12, 2014, at 12:08 PM, Sébastien de Menten sdemen...@gmail.com javascript:_e(%7B%7D,'cvml','sdemen...@gmail.com'); wrote: On Wednesday, November 12, 2014, John Ralls jra...@ceridwen.us javascript:_e(%7B%7D,'cvml','jra...@ceridwen.us'); wrote: On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com wrote: I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book. It might be, but I doubt it. You won’t be able to implement the business logic in SQL alone. The python interface uses SQLAlchemy (an ORM) only to handle the backend (retrieve and save objects), all business logic is in the python code. For instance, when creating a transaction and the related splits, it is the python code that ensures the business logic (for instance that the sum of the splits = 0). This is close to what GnuCash does. Moreover, there are some basic SQL integrity constrains (we cannot remove a split without removing the related transaction) that are added in the ORM layer as they do not exist in the SQL backend. I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a wrong impression ? I’m not sure I follow you about calculations when the book is opened that aren’t saved. I was thinking about temporary results/cached calculations/etc that are not saved to the back ends (if there are any). With the SQL backend, everything is written back to the database when a change is committed, so the *results* of the calculations are immediately saved. What GnuCash doesn’t do is *read* the database after the initial load, nor does it use any database concurrency control, so there are two potential ways to screw things up with two programs (even two instances of GnuCash) using the same database: A change made by one instance could be overwritten by a change made in the other or, much worse, the two instances could try writing the same records at the same time, corrupting those records. Indeed, if GnuCash has opened the file and is using it (ie there is a lock in the table gnc_lock), we are almost 100% sure to have the issues you mentions if we change the file in parallel through SQL. There is a check in pyscash that raises an exception in this case (it can be overruled but at user's own risk. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Nov 12, 2014, at 10:12 PM, Sébastien de Menten sdemen...@gmail.com wrote: On Thursday, November 13, 2014, John Ralls jra...@ceridwen.us mailto:jra...@ceridwen.us wrote: On Nov 12, 2014, at 12:08 PM, Sébastien de Menten sdemen...@gmail.com wrote: On Wednesday, November 12, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com wrote: I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book. It might be, but I doubt it. You won’t be able to implement the business logic in SQL alone. The python interface uses SQLAlchemy (an ORM) only to handle the backend (retrieve and save objects), all business logic is in the python code. For instance, when creating a transaction and the related splits, it is the python code that ensures the business logic (for instance that the sum of the splits = 0). This is close to what GnuCash does. Moreover, there are some basic SQL integrity constrains (we cannot remove a split without removing the related transaction) that are added in the ORM layer as they do not exist in the SQL backend. I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a wrong impression ? I’m not sure I follow you about calculations when the book is opened that aren’t saved. I was thinking about temporary results/cached calculations/etc that are not saved to the back ends (if there are any). With the SQL backend, everything is written back to the database when a change is committed, so the *results* of the calculations are immediately saved. What GnuCash doesn’t do is *read* the database after the initial load, nor does it use any database concurrency control, so there are two potential ways to screw things up with two programs (even two instances of GnuCash) using the same database: A change made by one instance could be overwritten by a change made in the other or, much worse, the two instances could try writing the same records at the same time, corrupting those records. Indeed, if GnuCash has opened the file and is using it (ie there is a lock in the table gnc_lock), we are almost 100% sure to have the issues you mentions if we change the file in parallel through SQL. There is a check in pyscash that raises an exception in this case (it can be overruled but at user's own risk. What’s your goal here? I don’t think that reimplementing GnuCash in Python with GnuCash’s SQL schema is a particularly good approach: It’s not exactly the most efficient design. Rather, it’s designed to mirror the XML schema. You’ll have a better design if you relegate GnuCash SQL to import/export. An aside about the name: Pyscash is likely to be pronounced by English speakers with a short “i” sound where the y goes. That conveys a rather unfortunate meaning. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
the risk of introducing corrupt data. As for the name, how about PieCash? It doesn’t get the SQL element in there, but it avoids mispronunciation. Regards, John Ralls On Thu, Nov 13, 2014 at 4:28 PM, John Ralls jra...@ceridwen.us wrote: On Nov 12, 2014, at 10:12 PM, Sébastien de Menten sdemen...@gmail.com wrote: On Thursday, November 13, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 12, 2014, at 12:08 PM, Sébastien de Menten sdemen...@gmail.com wrote: On Wednesday, November 12, 2014, John Ralls jra...@ceridwen.us wrote: On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com wrote: I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book. It might be, but I doubt it. You won’t be able to implement the business logic in SQL alone. The python interface uses SQLAlchemy (an ORM) only to handle the backend (retrieve and save objects), all business logic is in the python code. For instance, when creating a transaction and the related splits, it is the python code that ensures the business logic (for instance that the sum of the splits = 0). This is close to what GnuCash does. Moreover, there are some basic SQL integrity constrains (we cannot remove a split without removing the related transaction) that are added in the ORM layer as they do not exist in the SQL backend. I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a wrong impression ? I’m not sure I follow you about calculations when the book is opened that aren’t saved. I was thinking about temporary results/cached calculations/etc that are not saved to the back ends (if there are any). With the SQL backend, everything is written back to the database when a change is committed, so the *results* of the calculations are immediately saved. What GnuCash doesn’t do is *read* the database after the initial load, nor does it use any database concurrency control, so there are two potential ways to screw things up with two programs (even two instances of GnuCash) using the same database: A change made by one instance could be overwritten by a change made in the other or, much worse, the two instances could try writing the same records at the same time, corrupting those records. Indeed, if GnuCash has opened the file and is using it (ie there is a lock in the table gnc_lock), we are almost 100% sure to have the issues you mentions if we change the file in parallel through SQL. There is a check in pyscash that raises an exception in this case (it can be overruled but at user's own risk. What’s your goal here? I don’t think that reimplementing GnuCash in Python with GnuCash’s SQL schema is a particularly good approach: It’s not exactly the most efficient design. Rather, it’s designed to mirror the XML schema. You’ll have a better design if you relegate GnuCash SQL to import/export. An aside about the name: Pyscash is likely to be pronounced by English speakers with a short “i” sound where the y goes. That conveys a rather unfortunate meaning. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On 2014-11-13 19:25, John Ralls wrote:
On Nov 13, 2014, at 9:31 AM, Sébastien de Menten sdemen...@gmail.com wrote:
Indeed, it may be worth to explain what are the goals (and the limits).
I have tried to use the official python bindings and had the following issues:
- need swig + compilations to make them work = pyscash is pure python and has
only sqlalchemy as main dependency (which is rather supported/standard package)
- python binding is a mapping if C API with a thin layer python friendly layer
= I do not find the resulting python scripts very pythonic
[...]
So, to sum up, goals of pyscash :
- easy installation as pure python module (no compilation, no swig, ...)
- easy to contribute as pure python and based on de facto standard ORM for SQL
in python (sql alchemy)
- pythonic implementation by leveraging SQL Alchemy features (transparent
handling of guid, free commit/rollback/transaction support, automatic
parent-children relation handling, etc)
- pythonic interface for CRUD operations on a GnuCash Book
The last point is important as the goal is not at all to reimplement the whole
GnuCash engine but only to be able to manipulate (CRUD operations) the
different GnuCash entities (book, account, currency, etc) in an easy pythonic
way.
For instance, I do not plan to reimplement functions like GetBalance,
GetReconciledBalance, GetPresentBalanceInCurrency, GetAccountBalance, etc
Is the goal of the project clearer ?
Do you see complexities in reimplementing the pure CRUD operations (except the
basic complexities of leaving a GnuCash book in a consistent status) ?
On the name issue, you are definitely right ! I had in mind a pie-ess-cash
pronounciation (the S for SQL) as pygnucash was already taken. Any suggestion ?
pysacash (python sqlalchemy gnucash interface) ? or is it even worse :-) ?
kind regards,
Sebastien
ps: and thank you for clarifying the announce on the user mailing list ! I
should have thought about that myself to avoid confusion…
Sébastien,
Please remember to copy the list on all replies.
The goal of the project is clearer but is in my mind severely misguided. Object
persistence is always a side effect of a non-trivial program, and GnuCash is a
non-trivial program.
If you see GnuCash from the (limited) perspective of an editor for a
Book document (as LibreOffice Writer is an editor for a ODT document),
object persistence is central. And luckily we have both a very clean and
well designed object model in GnuCash as well as standard persistence
formats (xml, SQL and not an obscure binary format).
The GnuCash documentation states that the XML document can be used to
get the GnuCash data in whatever other format (see Converting XML
GnuCash File
http://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html)
through XLST transformation and vice-versa (so generating a
new/transformed gnucash XML file from a LibreOffice spreadsheet).
PieCash (I like your name !) aims to fulfill exactly this purpose, no
more, no less. As with the XLST transforms, it allows to do CRUD
operations on the objects within a GnuCash Book.
Has this stance on the manipulation outside GnuCash of a GnuCash
document evolved since it was written ? Would this still be supported
after the C++ rewrite ?
your complaint about the python bindings is that they’re not pythonic I think
that your efforts would be better spent improving that. It should become easier
as we progress through the C++ rewrite which will produce a much more
object-oriented architecture where that’s appropriate. That said, it’s also
worth noting that C++ is a far more versatile language than either C or Python
in that it supports generic and functional programming as well as the
structured and OO paradigms that Python supports. In moving away from GObject
we’ll also be enforcing type safety much more rigorously. Since that’s a
notorious weakness (though casual hackers think it’s a feature) of Python,
having thin Python wrappers around C++ objects will provide for much safer
Python add ons than does the current code base.
The python binding are not pure python bindings (and will probably never
be as they should interface C or C++ code). This makes them not easily
accessible on Windows platforms (complexity in compilation) and more
complex to hack (for non C/C++ programmers). However, they offer the
ability to call any function of the engine and to be independent of the
backend (XML, SQL,...) as long as it is interfaced so may be the only
solution in some cases.
On the other side, PieCash is pure python. The code required to
interface an entity is almost trivial. For instance, the full interface
to the Commodity entity is
class Commodity(DeclarativeBaseGuid):
__tablename__ = 'commodities'
__table_args__ = {}
# column definitions
cusip = Column('cusip', TEXT(length=2048))
fraction = Column('fraction', INTEGER(), nullable=False)
Re: python GnuCash interface to SQL backend
On Nov 13, 2014, at 12:44 PM, Sébastien de Menten sdemen...@gmail.com wrote: On 2014-11-13 19:25, John Ralls wrote: On Nov 13, 2014, at 9:31 AM, Sébastien de Menten sdemen...@gmail.com wrote: Indeed, it may be worth to explain what are the goals (and the limits). I have tried to use the official python bindings and had the following issues: - need swig + compilations to make them work = pyscash is pure python and has only sqlalchemy as main dependency (which is rather supported/standard package) - python binding is a mapping if C API with a thin layer python friendly layer = I do not find the resulting python scripts very pythonic [...] So, to sum up, goals of pyscash : - easy installation as pure python module (no compilation, no swig, ...) - easy to contribute as pure python and based on de facto standard ORM for SQL in python (sql alchemy) - pythonic implementation by leveraging SQL Alchemy features (transparent handling of guid, free commit/rollback/transaction support, automatic parent-children relation handling, etc) - pythonic interface for CRUD operations on a GnuCash Book The last point is important as the goal is not at all to reimplement the whole GnuCash engine but only to be able to manipulate (CRUD operations) the different GnuCash entities (book, account, currency, etc) in an easy pythonic way. For instance, I do not plan to reimplement functions like GetBalance, GetReconciledBalance, GetPresentBalanceInCurrency, GetAccountBalance, etc Is the goal of the project clearer ? Do you see complexities in reimplementing the pure CRUD operations (except the basic complexities of leaving a GnuCash book in a consistent status) ? On the name issue, you are definitely right ! I had in mind a pie-ess-cash pronounciation (the S for SQL) as pygnucash was already taken. Any suggestion ? pysacash (python sqlalchemy gnucash interface) ? or is it even worse :-) ? kind regards, Sebastien ps: and thank you for clarifying the announce on the user mailing list ! I should have thought about that myself to avoid confusion… Sébastien, Please remember to copy the list on all replies. The goal of the project is clearer but is in my mind severely misguided. Object persistence is always a side effect of a non-trivial program, and GnuCash is a non-trivial program. If you see GnuCash from the (limited) perspective of an editor for a Book document (as LibreOffice Writer is an editor for a ODT document), object persistence is central. And luckily we have both a very clean and well designed object model in GnuCash as well as standard persistence formats (xml, SQL and not an obscure binary format). I don’t share either opinion, especially about the object model in GnuCash. The GnuCash documentation states that the XML document can be used to get the GnuCash data in whatever other format (see Converting XML GnuCash File http://www.gnucash.org/docs/v2.6/C/gnucash-guide/appendixa_xmlconvert1.html) through XLST transformation and vice-versa (so generating a new/transformed gnucash XML file from a LibreOffice spreadsheet). No matter what the GnuCash documentation might say, only someone with a deep understanding of GnuCash could successfully create a correct GnuCash data file from a spreadsheet document using XSLT. I’m not familiar with LibreOffice’s ODS format, but I expect that it would take deep knowledge of that format to successfully extract useful data from it with XSLT as well. PieCash (I like your name !) aims to fulfill exactly this purpose, no more, no less. As with the XLST transforms, it allows to do CRUD operations on the objects within a GnuCash Book. CRUD operations on a GnuCash database will corrupt it. The GnuCash schema is not normalized, and not all of the necessary data is stored in the table associated with the objects. Has this stance on the manipulation outside GnuCash of a GnuCash document evolved since it was written ? Would this still be supported after the C++ rewrite ? It isn’t supported now. It never has, and it is unlikely that it ever will be, even if we are able, after several development cycles, to actually migrate to a 3N database schema. There is too much logic that is encoded in the program and which cannot be portably encoded in a SQL database, to make that feasible. your complaint about the python bindings is that they’re not pythonic I think that your efforts would be better spent improving that. It should become easier as we progress through the C++ rewrite which will produce a much more object-oriented architecture where that’s appropriate. That said, it’s also worth noting that C++ is a far more versatile language than either C or Python in that it supports generic and functional programming as well as the structured and OO paradigms that Python supports. In moving away from GObject we’ll also be enforcing type safety much more
Re: python GnuCash interface to SQL backend
Sébastien de Menten sdemen...@gmail.com writes: Where could I find detailed documentation on the GnuCash engine (and the constrains/invariants GnuCash enforces) ? Or would there be some code/program to check a GnuCash file is sane/consistent ? Only in the implementation. There is no documentation per se on this, because we do not support modification of the database from outside the GnuCash APIs. This also allows us to change the underlying storage mechanisms without breaking things, because it's all abstracted. By re-implementing it you're basically binding yourself to a particular version of the database schema, which can (and will) change over time, requiring you to duplicate the effort already happening in the gnucash code. Moreover, you're also tied to a particular backend, which isn't very nice. If you want to modify the gnucash database, you really should use the exported GnuCash APIs. If the current python bindings aren't pythonic enough for you, then I urge you to spend the time to fix that instead of reimplementing something that will absolutely break some time in the future. When it will break I cannot tell you, but I can assure you it WILL break at some point. Regards Sebastien -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
python GnuCash interface to SQL backend
Hello, After trying multiple times to work with GnuCash from python (via xml, via the python bindings, via sql), I finally had a try to use SQLAlchemy to handle the GnuCash Books saved through the SQL backend (sqlite3 and postgres). I have a release on PyPI the package pyscash installable through 'pip install pyscash' (see some raw documentation on https://github.com/sdementen/pyscash). It is 'alpha' quality. While it opens by default the Book in read only mode to be able to do reporting or extract data from a GnuCash Book, I also succeeded in doing more elaborate scripts that change a Book : creating new accounts/sub-accounts, creating new transactions, uploading quotes for stocks, etc. I read that the SQL backend is just a backend to save the data and that GnuCash is not a DB application and that the preferred way to program GnuCash in python is through the python bindings. However, I found it much easier to work with this pyscash package, at least as long as it is done offline (i.e. not modifying a Book that is at the same time opened by GnuCash). I would be genuinely interested to have more specific documentation on the risks of going the SQL way. Sebastien ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com wrote: Hello, After trying multiple times to work with GnuCash from python (via xml, via the python bindings, via sql), I finally had a try to use SQLAlchemy to handle the GnuCash Books saved through the SQL backend (sqlite3 and postgres). I have a release on PyPI the package pyscash installable through 'pip install pyscash' (see some raw documentation on https://github.com/sdementen/pyscash). It is 'alpha' quality. While it opens by default the Book in read only mode to be able to do reporting or extract data from a GnuCash Book, I also succeeded in doing more elaborate scripts that change a Book : creating new accounts/sub-accounts, creating new transactions, uploading quotes for stocks, etc. I read that the SQL backend is just a backend to save the data and that GnuCash is not a DB application and that the preferred way to program GnuCash in python is through the python bindings. However, I found it much easier to work with this pyscash package, at least as long as it is done offline (i.e. not modifying a Book that is at the same time opened by GnuCash). I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. The risk of writing to the database outside of GnuCash, whether in SQL or XML, is that unless you are very careful and have a deep understanding of how GnuCash works that you will irretrievably corrupt your accounting data. There is no business logic encoded in the SQL database, so your code must replicate the GnuCsah engine code to ensure that all required fields are computed and stored correctly. Much of GnuCash is neither straightforward nor obvious and some critical data are stored outside of the primary tables, usually to preserve backward compatibility with previous versions. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: python GnuCash interface to SQL backend
On Nov 12, 2014, at 12:08 PM, Sébastien de Menten sdemen...@gmail.com wrote: On Wednesday, November 12, 2014, John Ralls jra...@ceridwen.us mailto:jra...@ceridwen.us wrote: On Nov 11, 2014, at 1:10 PM, Sébastien de Menten sdemen...@gmail.com javascript:; wrote: I would be genuinely interested to have more specific documentation on the risks of going the SQL way. There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme. It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book. It might be, but I doubt it. You won’t be able to implement the business logic in SQL alone. The risk of writing to the database outside of GnuCash, whether in SQL or XML, is that unless you are very careful and have a deep understanding of how GnuCash works that you will irretrievably corrupt your accounting data. There is no business logic encoded in the SQL database, so your code must replicate the GnuCsah engine code to ensure that all required fields are computed and stored correctly. Much of GnuCash is neither straightforward nor obvious and some critical data are stored outside of the primary tables, usually to preserve backward compatibility with previous versions. Regards, John Ralls I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a wrong impression ? Where could I find detailed documentation on the GnuCash engine (and the constrains/invariants GnuCash enforces) ? Or would there be some code/program to check a GnuCash file is sane/consistent” ? The developer documentation is in the sources in doxygen format; the current master documentation is compiled nightly and served at http://code.gnucash.org/docs/HEAD/ http://code.gnucash.org/docs/HEAD/. GnuCash has a check and repair facility built in, most of which is run at file load. It does some sanity checking but won’t necessarily correct every possible error that an external program could make. I’m not sure I follow you about calculations when the book is opened that aren’t saved. With the SQL backend, everything is written back to the database when a change is committed, so the *results* of the calculations are immediately saved. What GnuCash doesn’t do is *read* the database after the initial load, nor does it use any database concurrency control, so there are two potential ways to screw things up with two programs (even two instances of GnuCash) using the same database: A change made by one instance could be overwritten by a change made in the other or, much worse, the two instances could try writing the same records at the same time, corrupting those records. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
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
SQL backend: Where do we store the password?
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
Creating new customer with Python bindings and SQL backend
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello guys,
I am trying to create a new customer using the Python bindings. I am
using an SQL backend (PostgreSQL).
I follow the examples and do
new_customer = Customer(book, '0', USD, 'foo bar')
Of course, the currency USD is correctly defined. This gives my the
following SQL errors
* 00:57:26 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error:
ERROR: null value in column currency violates not-null constraint
* 00:57:26 CRIT gnc.backend.dbi
[conn_execute_nonselect_statement()] Error executing SQL INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.backend.sql [gnc_sql_do_db_operation()] SQL
error: INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.business [gncCustomerOnError()] Customer
QofBackend Failure: 12
* 00:57:26 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error:
ERROR: null value in column currency violates not-null constraint
* 00:57:26 CRIT gnc.backend.dbi
[conn_execute_nonselect_statement()] Error executing SQL INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','foo
bar','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.backend.sql [gnc_sql_do_db_operation()] SQL
error: INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','foo
bar','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.business [gncCustomerOnError()] Customer
QofBackend Failure: 12
Looking at the Python code, it seems that the Customer object is first
created without passing the value of its ID, currency or name. Thus
the SQL error is legitimate.
When I execute the exact same code on an XML backend, it works as
expected.
So now my question is, how do I create a new customer on an SQL backend?
Thanks for the help,
Jonas
- --
My email is signed and I encrypt email on request.
To verify my signature or send me encrypted email,
get my public key: http://lippuner.ca/key
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJRsZfLAAoJELH92qHy0gntDvMQANXpnVv8QSJU8YdEOxqlT4mA
lkSCvw19F8QXsYIArQ1TR7UTDa1Rwtr0RPzuaqY+wXFLTCv7GnToJzT1f88T36OX
1si6BCQZALGwYh1gvraLMD0Y8JjMW8sQySFjDRO5FpBKLzZ0X4GZq64H/nZvt3dl
TsRYZmsQ61rjg87+HUT1XH6ROsIvyso0lw9fQ1Ju6PxUuk7e2ybheaZjzvEDmgJu
4zZkVB53+QR7sZacE74S2I09ffoMLQ8Xd8+EEIkZsdNiGFQtlVZpQlXqQH1w9PE4
pTuyY1l0vTmNZQkxyzo51VBO98KH+l13uomnhAPY5e/64SUBJKV578NM9qqFR+2E
YgRlrBMJz/jvk88TfT5eGPsCaqmq20Ka5s2Ozqsz7RCA4W6beBYU7vq3BN030GVt
Q49galjp8CsX9z5RkNc61g2Uq2yVp9bSn3CFnjSYO5LWDdmdj2UCiy7CIu6+b77V
Ldy/qX1LIgl3kQX+bv5uJ70nRrBcC4D2IzPIkXOeG7poqZ587zSsYyq9p2OZCqHE
uhzT+jgN+pqJbZvo3sZBEZvTuIk1h2ID12ev3HWKqslh2zwOWUG154BH5lEKEJ/Y
QOL0Hlzsj5AdBAayevS8IqahQRuAOjzeJBqU9xQdt+DBMG9Q4cPTsNys0p7XR4Lf
Egs4tloSIgdaREkKoetO
=J+xU
-END PGP SIGNATURE-
___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
RE: Creating new customer with Python bindings and SQL backend
Hi Jonas,
I believe this issue relates to a bug in the Python bindings discussed here:
https://bugzilla.gnome.org/show_bug.cgi?id=700197 and resolved Guncash 2.5.2
As the bug relates to one of the Python files you should be able to resolve it
by patching the relevent file in your 2.4 installation rather than upgrading.
If you need any more information on this please let me know.
Kind regards,
Tom
-Original Message-
From: gnucash-devel-bounces+dev=loftx.co...@gnucash.org
[mailto:gnucash-devel-bounces+dev=loftx.co...@gnucash.org] On Behalf Of Jonas
Lippuner
Sent: 07 June 2013 09:21
To: gnucash-devel@gnucash.org
Subject: Creating new customer with Python bindings and SQL backend
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello guys,
I am trying to create a new customer using the Python bindings. I am using an
SQL backend (PostgreSQL).
I follow the examples and do
new_customer = Customer(book, '0', USD, 'foo bar')
Of course, the currency USD is correctly defined. This gives my the following
SQL errors
* 00:57:26 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error:
ERROR: null value in column currency violates not-null constraint
* 00:57:26 CRIT gnc.backend.dbi
[conn_execute_nonselect_statement()] Error executing SQL INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.backend.sql [gnc_sql_do_db_operation()] SQL
error: INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.business [gncCustomerOnError()] Customer QofBackend
Failure: 12
* 00:57:26 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error:
ERROR: null value in column currency violates not-null constraint
* 00:57:26 CRIT gnc.backend.dbi
[conn_execute_nonselect_statement()] Error executing SQL INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','foo
bar','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.backend.sql [gnc_sql_do_db_operation()] SQL
error: INSERT INTO
customers(guid,name,id,notes,active,discount_num,discount_denom,credit_num,credit_denom,currency,tax_override,addr_name,addr_addr1,addr_addr2,addr_addr3,addr_addr4,addr_phone,addr_fax,addr_email,shipaddr_name,shipaddr_addr1,shipaddr_addr2,shipaddr_addr3,shipaddr_addr4,shipaddr_phone,shipaddr_fax,shipaddr_email,terms,tax_included,taxtable)
VALUES('daf13116dcc180990154e1652c3cab09','foo
bar','10','',1,0,1,0,1,NULL,0,'','','','','','','','','','','','','','','','',NULL,3,NULL)
* 00:57:26 CRIT gnc.business [gncCustomerOnError()] Customer QofBackend
Failure: 12
Looking at the Python code, it seems that the Customer object is first created
without passing the value of its ID, currency or name. Thus the SQL error is
legitimate.
When I execute the exact same code on an XML backend, it works as expected.
So now my question is, how do I create a new customer on an SQL backend?
Thanks for the help,
Jonas
- --
My email is signed and I encrypt email on request.
To verify my signature or send me encrypted email, get my public key:
http://lippuner.ca/key -BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJRsZfLAAoJELH92qHy0gntDvMQANXpnVv8QSJU8YdEOxqlT4mA
lkSCvw19F8QXsYIArQ1TR7UTDa1Rwtr0RPzuaqY+wXFLTCv7GnToJzT1f88T36OX
1si6BCQZALGwYh1gvraLMD0Y8JjMW8sQySFjDRO5FpBKLzZ0X4GZq64H/nZvt3dl
TsRYZmsQ61rjg87+HUT1XH6ROsIvyso0lw9fQ1Ju6PxUuk7e2ybheaZjzvEDmgJu
4zZkVB53+QR7sZacE74S2I09ffoMLQ8Xd8+EEIkZsdNiGFQtlVZpQlXqQH1w9PE4
pTuyY1l0vTmNZQkxyzo51VBO98KH+l13uomnhAPY5e/64SUBJKV578NM9qqFR+2E
YgRlrBMJz/jvk88TfT5eGPsCaqmq20Ka5s2Ozqsz7RCA4W6beBYU7vq3BN030GVt
Q49galjp8CsX9z5RkNc61g2Uq2yVp9bSn3CFnjSYO5LWDdmdj2UCiy7CIu6+b77V
Ldy/qX1LIgl3kQX+bv5uJ70nRrBcC4D2IzPIkXOeG7poqZ587zSsYyq9p2OZCqHE
uhzT+jgN
Re: [Bug 632166] sql backend silently fails and loses data
On Oct 18, 2010, at 10:30 AM, Donald Allen wrote: John -- This is reminiscent of a bug, 609583, that I reported and you and I worked on earlier this year. I just wanted to remind you of it in case the issues are the same (I haven't read the two bug reports carefully) and, if so, there might be something of use to you in the older report.. /Don On Sun, Oct 17, 2010 at 10:54 PM, John Ralls jra...@ceridwen.us wrote: I've been working on https://bugzilla.gnome.org/show_bug.cgi?id=632166 this afternoon. I can reproduce the bug, and I've added (but not yet checked in) a log message with more detail than the g_return_val_if_fail message. There should probably be one for each failure reason. But to really address this, we need to pop up a message dialog telling the user that the save as didn't go well and that she can't rely on the saved database. The problem is that that's at least one new string to translate. The choices are to break the string freeze (my preference, because I don't think that we should release 2.4.0 with the possibility of screwing up the user's accounts) or take this off the milestone list for 2.4. Don, Good catch. It is the same problem exactly. When you closed the bug, you said that 2.2.9's Check and Repair fixed the missing currency field, which is interesting because 2.3.15's doesn't -- so that's a regression that needs to be fixed on top of everything else. On the other hand, I'm leaning towards making the sql backend just note the missing currency in the log. That seems to be what the xml backend does. Even better, I suppose, would be to simply fix the missing field. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
[Bug 632166] sql backend silently fails and loses data
I've been working on https://bugzilla.gnome.org/show_bug.cgi?id=632166 this afternoon. I can reproduce the bug, and I've added (but not yet checked in) a log message with more detail than the g_return_val_if_fail message. There should probably be one for each failure reason. But to really address this, we need to pop up a message dialog telling the user that the save as didn't go well and that she can't rely on the saved database. The problem is that that's at least one new string to translate. The choices are to break the string freeze (my preference, because I don't think that we should release 2.4.0 with the possibility of screwing up the user's accounts) or take this off the milestone list for 2.4. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend - What about Python access ?
Hello ! I guess a python-script using the python-bindings which accesses the SQL-Database would be the same as another user accessing it ? Gnucash and my script coexist peacefully but i decided to not run them the same time for i feared data destruction. It would be nice, though. bye, C. Holtermann ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend - What about Python access ?
Christoph Holtermann c.holterm...@gmx.de writes: Hello ! I guess a python-script using the python-bindings which accesses the SQL-Database would be the same as another user accessing it ? Yes. Gnucash and my script coexist peacefully but i decided to not run them the same time for i feared data destruction. It would be nice, though. of course it would. So would true multi-user. But for now we don't have it. bye, C. Holtermann -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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
On 21 May 2010, at 6:04 PM, Derek Atkins wrote: Because a situation arises when both of you need to make writes. Which copy is the authoritative copy? Using svn alleviates this somewhat, but isn't ideal. The authoritative copy belongs to whomever has the write token. Otherwise known as the svn lock. Sharing access is a lot simpler than people think. It is not strictly necessary for gnucash instance A to be 100% up to date with gnucash instance B. The only time it does need to be up to date is when an amendment is made to a transaction, and at that point you construct the update query in that it replaces what gnucash instance A believes was the previous version of the transaction. If the query was successful, we're done. If the query touched no rows, we know there was a conflict, and the user will need to be asked to make their amendment again, resolving the conflict. To keep registers up to date, keep a table containing a last updated timestamp on each account. The timestamp's value isn't important, only that it has changed. Instance A and instance B polls the table from time to time, and reloads any register as necessary. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend
On Thu, 20 May 2010 12:55:01 -0400 Derek == Derek Atkins warl...@mit.edu wrote: Derek IMNSHO, adding MySQL and PG support was a mistake; we should Derek have stuck with just SQLite. +1 Sincerely, Gour -- Gour | Hlapicina, Croatia | GPG key: F96FF5F6 signature.asc Description: PGP signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Graham Leggett minf...@sharp.fm writes: Why not? Because a situation arises when both of you need to make writes. Which copy is the authoritative copy? Using svn alleviates this somewhat, but isn't ideal. The authoritative copy belongs to whomever has the write token. It is really easy to silently lose transactions, if a mixup occurs over who holds the master copy of the data file. Indeed, which is why the here be dragons! warnings.. The fact that gnucash can be asked to save the file in text/xml helps, because you can version this in something like svn. But versioning a database isn't easy at all. Why do you need versioning? Versioning is overkill for data sharing. It prevents the situation where I add a transaction, then you add a transaction, silently overwriting mine. That shouldn't happen if only one person can access the data file. Not only is the data wrong, it is silently wrong without warning. Well, it shouldn't be silent.. You should have been warned that the data file was in use. -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: We need to prevent multi-user access to the SQL backend
On 20 May 2010 19:48, Gour g...@gour-nitai.com wrote: On Thu, 20 May 2010 12:55:01 -0400 Derek == Derek Atkins warl...@mit.edu wrote: Derek IMNSHO, adding MySQL and PG support was a mistake; we should Derek have stuck with just SQLite. +1 -1, I am very grateful for MySQL. I am generating web based reports from the db and sqlite would not be suitable. Keep up the great work. There is nothing stopping those who want just sqlite (or xml) from using that. Colin L ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
z33...@gmail.com writes: I think I understand at least some of the reasons for GC being single-user, but I'm curious if there's been any more thought lately to building multi-user functionality into GnuCash in future? I believe there is an unstated goal of getting minimal multi-user support working. However doing it right is a major undertaking, because it involves lots of cache consistency issues. You have to make sure that updates at one client affect changes in all the clients. That's a lot of work and restructuring for an application that wasn't designed for that. Repeat after me: GnuCash is NOT a DATABASE Application. It just uses a database as a backing-store. Cheers, -- Erik Anderson -derek On Wed, May 19, 2010 at 1:58 AM, Christian Stimming stimm...@tuhh.dewrote: Thanks, Herbert, for the link. The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Also, it's amusing to me to see the cutecash project already mentioned in there, even though it's only accessible through the SVN sources :-) Regards, Christian Zitat von Herbert Thoma herbert.th...@iis.fraunhofer.de: http://lwn.net/SubscriberLink/387967/cc502c0dc33d97d3/ -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: t...@iis.fhg.de www: http://www.iis.fhg.de/ ___ 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 ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- 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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Graham Leggett minf...@sharp.fm writes: On 19 May 2010, at 11:47 PM, Per Kjeldaas wrote: Your solution to block the whole database is good enough for me. It's a real shame that a system fundamentally designed to offer multi user access to data should be crippled in such a fashion. In the process, virtually all reasons to use a SQL database are lost. What was fundamentally designed to offer multi user access? GnuCash most certainly was not, even when it's using a SQL Database for data storage. Repeat after me: GnuCash is NOT a Database Application. It's a standalone application that happens to be able to use a database instead of SQL, but fundamentally it's still a standalone application. The fact that the DATABASE can be accessed multi-user has nothing to do with the fact that GnuCash was NOT designed to handle that and therefore needs to protect its data from users who try to do it. We use the XML backend, and share it by versioning it in source control with mandatory locks. It's not perfect, and requires discipline to respect the lock, but it works between three different people sharing the responsibility to keep the accounts up to date. And using SQL should be better -- but we still need to try to enforce the sequentiality in order to prevent data loss. Regards, Graham -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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Valdis Vītoliņš valdis.vitol...@odo.lv writes: I vote for not over-engineered solution and, I suppose simple lock for all database (only one can write) is OK. My clients/partners will not use SQL backend. They use Gnucash because it can be installed and used as many other standalone applications. It is important, that Gnucash save data in file, which can be easy copied, opened from shared filesystem, and it can be easily opened by doubleclick similarly to other used files in office applications. FWIW, SQLite provides that. There are plenty of client-server accounting applications, I have looked at LedgerSMB and even play around Openbravo. But these are much more complicated, than Gnucash (especially on Linux) which can install every accountant himself. I believe most of Gnucash users are still private, not business users, so please don't rate easiness too outdated and/or unimportant. Valdis -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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
On 20 May 2010, at 6:32 PM, Derek Atkins wrote: Your solution to block the whole database is good enough for me. It's a real shame that a system fundamentally designed to offer multi user access to data should be crippled in such a fashion. In the process, virtually all reasons to use a SQL database are lost. What was fundamentally designed to offer multi user access? SQL databases. GnuCash most certainly was not, even when it's using a SQL Database for data storage. Repeat after me: GnuCash is NOT a Database Application. It's a standalone application that happens to be able to use a database instead of SQL, but fundamentally it's still a standalone application. The fact that the DATABASE can be accessed multi-user has nothing to do with the fact that GnuCash was NOT designed to handle that and therefore needs to protect its data from users who try to do it. Which over time pretty much renders it useless. In any practical usage, even in it's simplest form, you start off small and simple, and then eventually you reach a point where you want to share the file between two people, or share the file with an accountant, and you can't. Which is a real pity, because in my experience gnucash gives about 95% of what a small business needs, tripping up on that last little bit. The fact that gnucash can be asked to save the file in text/xml helps, because you can version this in something like svn. But versioning a database isn't easy at all. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Graham Leggett minf...@sharp.fm writes: On 20 May 2010, at 6:32 PM, Derek Atkins wrote: Your solution to block the whole database is good enough for me. It's a real shame that a system fundamentally designed to offer multi user access to data should be crippled in such a fashion. In the process, virtually all reasons to use a SQL database are lost. What was fundamentally designed to offer multi user access? SQL databases. Sure. But GnuCash isn't a Database Application, even if it happens to be able to use a SQL Database to store its data. GnuCash most certainly was not, even when it's using a SQL Database for data storage. Repeat after me: GnuCash is NOT a Database Application. It's a standalone application that happens to be able to use a database instead of SQL, but fundamentally it's still a standalone application. The fact that the DATABASE can be accessed multi-user has nothing to do with the fact that GnuCash was NOT designed to handle that and therefore needs to protect its data from users who try to do it. Which over time pretty much renders it useless. Well, over time one would hope that we can slowly rearchitect gnucash to be more aware of multi-user situations. In any practical usage, even in it's simplest form, you start off small and simple, and then eventually you reach a point where you want to share the file between two people, or share the file with an accountant, and you can't. Why not? Which is a real pity, because in my experience gnucash gives about 95% of what a small business needs, tripping up on that last little bit. I dont understand... Where can you not share your data? The fact that gnucash can be asked to save the file in text/xml helps, because you can version this in something like svn. But versioning a database isn't easy at all. Why do you need versioning? Versioning is overkill for data sharing. I suppose you could use it for auditing, but wouldn't it be better to have the audit trail inside the database itself? This is part of what a multi-user system would entail. On the other hand, once you start going down that road you really start getting well past what GnuCash was designed for: Home Users and Small Businesses. It's a Quicken/Quickbooks replacement, not a Peachtree or SAP replacement. IMNSHO, adding MySQL and PG support was a mistake; we should have stuck with just SQLite. Regards, Graham -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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
On 20 May 2010, at 6:55 PM, Derek Atkins wrote: Well, over time one would hope that we can slowly rearchitect gnucash to be more aware of multi-user situations. I'm keen to do some of that work, as I need it directly. In any practical usage, even in it's simplest form, you start off small and simple, and then eventually you reach a point where you want to share the file between two people, or share the file with an accountant, and you can't. Why not? Because a situation arises when both of you need to make writes. Which copy is the authoritative copy? Using svn alleviates this somewhat, but isn't ideal. It is really easy to silently lose transactions, if a mixup occurs over who holds the master copy of the data file. The fact that gnucash can be asked to save the file in text/xml helps, because you can version this in something like svn. But versioning a database isn't easy at all. Why do you need versioning? Versioning is overkill for data sharing. It prevents the situation where I add a transaction, then you add a transaction, silently overwriting mine. Not only is the data wrong, it is silently wrong without warning. On the other hand, once you start going down that road you really start getting well past what GnuCash was designed for: Home Users and Small Businesses. It's a Quicken/Quickbooks replacement, not a Peachtree or SAP replacement. I am a small business, there are two partners and an accountant. We are nowhere even close to needing SAP. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Thanks, Herbert, for the link. The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Also, it's amusing to me to see the cutecash project already mentioned in there, even though it's only accessible through the SVN sources :-) Regards, Christian Zitat von Herbert Thoma herbert.th...@iis.fraunhofer.de: http://lwn.net/SubscriberLink/387967/cc502c0dc33d97d3/ -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: t...@iis.fhg.de www: http://www.iis.fhg.de/ ___ 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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
I think I understand at least some of the reasons for GC being single-user, but I'm curious if there's been any more thought lately to building multi-user functionality into GnuCash in future? Cheers, -- Erik Anderson On Wed, May 19, 2010 at 1:58 AM, Christian Stimming stimm...@tuhh.dewrote: Thanks, Herbert, for the link. The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Also, it's amusing to me to see the cutecash project already mentioned in there, even though it's only accessible through the SVN sources :-) Regards, Christian Zitat von Herbert Thoma herbert.th...@iis.fraunhofer.de: http://lwn.net/SubscriberLink/387967/cc502c0dc33d97d3/ -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: t...@iis.fhg.de www: http://www.iis.fhg.de/ ___ 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 ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
I think there is a big difference between concurrent multi use and serial multi use. I would like to be able to use GnuCash with the same remote database, but from different computers at different times. This would be serial multi use. The system should ideally protect against any use that could damage the database. Per. On May 19, 2010, at 1:09 PM, z33...@gmail.com wrote: I think I understand at least some of the reasons for GC being single-user, but I'm curious if there's been any more thought lately to building multi-user functionality into GnuCash in future? Cheers, -- Erik Anderson On Wed, May 19, 2010 at 1:58 AM, Christian Stimming stimm...@tuhh.dewrote: Thanks, Herbert, for the link. The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Also, it's amusing to me to see the cutecash project already mentioned in there, even though it's only accessible through the SVN sources :-) Regards, Christian Zitat von Herbert Thoma herbert.th...@iis.fraunhofer.de: http://lwn.net/SubscriberLink/387967/cc502c0dc33d97d3/ -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: t...@iis.fhg.de www: http://www.iis.fhg.de/ ___ 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 ___ 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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
On Wed, May 19, 2010 at 6:58 PM, Christian Stimming stimm...@tuhh.de wrote: The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Create a table in database, and 1) insert a entry contains a GUID of current user/process/computer when GnuCash is loading the database. 2) remove the entry when it exit. 3) Let user decide whether force to overwrite the entry the entry exist, such as last time GnuCash crashed, or there is another process is using it. 4) Read the entry everytime GnuCash is accessing the database, to make sure it's not overwrite by others. Instead of the lock of whole database, another solution is recording locks of each entity/object in the table, everytime user trying to edit something, gnucash create a lock(a entry of the lock_table) on the object, and any other user trying to edit that object will get a warning, and will not be able to edit the object unless the previous user release the lock. -- Regards Tao Wang ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
I vote for not over-engineered solution and, I suppose simple lock for all database (only one can write) is OK. My clients/partners will not use SQL backend. They use Gnucash because it can be installed and used as many other standalone applications. It is important, that Gnucash save data in file, which can be easy copied, opened from shared filesystem, and it can be easily opened by doubleclick similarly to other used files in office applications. There are plenty of client-server accounting applications, I have looked at LedgerSMB and even play around Openbravo. But these are much more complicated, than Gnucash (especially on Linux) which can install every accountant himself. I believe most of Gnucash users are still private, not business users, so please don't rate easiness too outdated and/or unimportant. Valdis ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
Tao, Your solution to block the whole database is good enough for me. Thanks, Per. On May 19, 2010, at 2:26 PM, Tao Wang wrote: On Wed, May 19, 2010 at 6:58 PM, Christian Stimming stimm...@tuhh.de wrote: The article raises one valid serious concern: The database backend does nothing to prevent multiple user access. This is bad because simultaneous access to the SQL database from multiple users will almost surely cause data loss. On the other hand, the technology term SQL backend will almost surely raise the user expectation that multiple user access were possible, so for sure people will give it a try. Since it will corrupt their data, we need to build in some prevention measure - the equivalent of the lock file, but inside the SQL database. Ideas, anyone? Create a table in database, and 1) insert a entry contains a GUID of current user/process/computer when GnuCash is loading the database. 2) remove the entry when it exit. 3) Let user decide whether force to overwrite the entry the entry exist, such as last time GnuCash crashed, or there is another process is using it. 4) Read the entry everytime GnuCash is accessing the database, to make sure it's not overwrite by others. Instead of the lock of whole database, another solution is recording locks of each entity/object in the table, everytime user trying to edit something, gnucash create a lock(a entry of the lock_table) on the object, and any other user trying to edit that object will get a warning, and will not be able to edit the object unless the previous user release the lock. -- Regards Tao Wang ___ 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: We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)
On 19 May 2010, at 11:47 PM, Per Kjeldaas wrote: Your solution to block the whole database is good enough for me. It's a real shame that a system fundamentally designed to offer multi user access to data should be crippled in such a fashion. In the process, virtually all reasons to use a SQL database are lost. We use the XML backend, and share it by versioning it in source control with mandatory locks. It's not perfect, and requires discipline to respect the lock, but it works between three different people sharing the responsibility to keep the accounts up to date. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: problems with sql-backend
Rolf Leggewie no2s...@nospam.arcornews.de writes: Phil Longstaff wrote: After the update to r18792, one of my sqlite3-based gnucash files which was fixed in r18805. You should update to at least 18855 If that doesn't fix your problem, let me know. I first noticed the problem with r18792 which is past the revisions you mentioned. Last I checked, 18805 18792, which means the bug was FIXED after you noticed the problem. Have you tried building with current SVN Trunk? gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -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
problems with sql-backend
Hi, I've been using the sql-backend for quite some time. I've been compiling my deb packages from svn to do that. I recently upgraded my machine from lucid to karmic and cooked up a new svn snapshot at about the same time. I believe I had previously been running r18249 for quite a while without any trouble. After the update to r18792, one of my sqlite3-based gnucash files started acting up about a scheduled transaction which was due. After acknowleding it, I got a message unable to save database. The file itself would not open. Other gnucash databases were still fine. I tried going back to 18249, but for unfathomable reasons it was quite difficult to get it back to compile. In the end, I had to disable python-bindings because that just would not work. I don't think this will be an issue, though. Unfortunately, I still can't open my problem file in gnucash, because gnucash now chooses to crash. terminal: http://paste.debian.net/64798/ gdb trace: http://paste.debian.net/64797/ I hope this helps you guys and me. Phil, do you have any idea what's going on here? Any suggestion how I can get back to work? The database itself seems to be fine. I can see the data without any problem in the sqlite CLI client or ooffice. Regards Rolf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: problems with sql-backend
On Fri, 2010-03-19 at 01:29 +0100, Rolf Leggewie wrote: Hi, I've been using the sql-backend for quite some time. I've been compiling my deb packages from svn to do that. I recently upgraded my machine from lucid to karmic and cooked up a new svn snapshot at about the same time. I believe I had previously been running r18249 for quite a while without any trouble. After the update to r18792, one of my sqlite3-based gnucash files started acting up about a scheduled transaction which was due. After acknowleding it, I got a message unable to save database. The file itself would not open. Other gnucash databases were still fine. I tried going back to 18249, but for unfathomable reasons it was quite difficult to get it back to compile. In the end, I had to disable python-bindings because that just would not work. I don't think this will be an issue, though. Unfortunately, I still can't open my problem file in gnucash, because gnucash now chooses to crash. terminal: http://paste.debian.net/64798/ gdb trace: http://paste.debian.net/64797/ I hope this helps you guys and me. Phil, do you have any idea what's going on here? Any suggestion how I can get back to work? The database itself seems to be fine. I can see the data without any problem in the sqlite CLI client or ooffice. There were a number of changes I made to simplify how objects were loaded. I added gobject parameters to some of the engine objects, then used those when loading the objects. This allowed me to remove some custom code. I also think that having these gobject parameters will make it easier to have a general csv importer to import all object types. In any case, there was a bug in importing dates used by sxes which was fixed in r18805. You should update to at least 18855 (and probably all the way to 18920 (2.3.11 tag). If that doesn't fix your problem, let me know. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: problems with sql-backend
On Sat, 2010-03-20 at 17:28 +0100, Rolf Leggewie wrote: Phil Longstaff wrote: After the update to r18792, one of my sqlite3-based gnucash files which was fixed in r18805. You should update to at least 18855 If that doesn't fix your problem, let me know. I first noticed the problem with r18792 which is past the revisions you mentioned. ??? 18792 is before 18805. 18792 is in the middle of my changes, after I made the changes in how objects are loaded but before I got all of the bugs out. Phil ___ 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
Scheme formatting with astyle [Was: SQL backend performance]
On 2/24/2010 11:50 AM, Christian Stimming wrote: 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. which links to previous As a one-liner: $ astyle --indent=spaces=4 --brackets=break --pad-oper *.[hc] Is there a project standard for formatting scheme code as well? ___ 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
Re: Scheme formatting with astyle [Was: SQL backend performance]
On Wed, Feb 24, 2010 at 12:09 PM, Jeff Kletsky gnuc...@allycomm.com wrote: On 2/24/2010 11:50 AM, Christian Stimming wrote: 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. which links to previous As a one-liner: $ astyle --indent=spaces=4 --brackets=break --pad-oper *.[hc] its --pad=oper so $ astyle --indent=spaces=4 --brackets=break --pad=oper *.[hc] Is there a project standard for formatting scheme code as well? ___ 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
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: Re: Another problem with scheme query and SQL backend (Phil Longstaff)
Am Samstag, den 20.06.2009, 12:00 -0400 schrieb gnucash-devel-requ...@gnucash.org: Message: 1 Date: Fri, 19 Jun 2009 12:52:37 -0400 From: Phil Longstaff plongst...@rogers.com Subject: Re: Another problem with scheme query and SQL backend To: gnucash-devel@gnucash.org Message-ID: 200906191252.37634.plongst...@rogers.com Content-Type: text/plain; charset=iso-8859-1 On May 31, 2009 12:41:13 pm Phil Longstaff wrote: On May 31, 2009 06:53:23 am Erwin Rieger wrote: Hi, i have another funny problem with querying data from scheme with the (my)sql backend. The query from my previous posts do only return transactions that are/were shown in the gui (ledger)! Steps to reproduce: * install config.user from my previous post in ~/.gnucash * open a nonempty sql-book with gnucash (with --extra command line option), all accounts closed, only account-tree visible * run scheme query from the Extensions - AQueryTest1 menu * watch output on console: got 0 transactions... * open a account with transactions in it * run scheme query from the Extensions - AQueryTest1 menu a second time * watch output on console, now it reports a number of returned transactions * close ledger view of account * re-run query, console still reports a number of returned transactions Yes, there definitely isn't support for general queries, yet. I *do* have something in there which is commented out, so maybe I need to get it working. I wasn't aware of the extensions menu, so didn't have a way of creating a general query. All I had was the ones built into gnucash. The main one is a split query for a single account, used to populate a register, and at this point, the assumption is that that is the only query used. This explains why your BQueryTest fails (isn't split query for a single account) and why this test here fails (it might load a single account, but not all accounts). However, any accounts which have a ledger open would have had those splits/transactions already loaded. Fixed (basically) in r18136 - still not support for complete queries, but these queries work. Phil I took a first look at the new revision - and it seems to be good. Now i am able to continue the work on my gnucash extensions. Thank you very much! -- Mit freundlichen Gruessen Erwin Rieger | Ingenieurbuero Rieger, Software Entwicklung | EMail: erwin.rie...@ibrieger.de ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Another problem with scheme query and SQL backend
On May 31, 2009 12:41:13 pm Phil Longstaff wrote: On May 31, 2009 06:53:23 am Erwin Rieger wrote: Hi, i have another funny problem with querying data from scheme with the (my)sql backend. The query from my previous posts do only return transactions that are/were shown in the gui (ledger)! Steps to reproduce: * install config.user from my previous post in ~/.gnucash * open a nonempty sql-book with gnucash (with --extra command line option), all accounts closed, only account-tree visible * run scheme query from the Extensions - AQueryTest1 menu * watch output on console: got 0 transactions... * open a account with transactions in it * run scheme query from the Extensions - AQueryTest1 menu a second time * watch output on console, now it reports a number of returned transactions * close ledger view of account * re-run query, console still reports a number of returned transactions Yes, there definitely isn't support for general queries, yet. I *do* have something in there which is commented out, so maybe I need to get it working. I wasn't aware of the extensions menu, so didn't have a way of creating a general query. All I had was the ones built into gnucash. The main one is a split query for a single account, used to populate a register, and at this point, the assumption is that that is the only query used. This explains why your BQueryTest fails (isn't split query for a single account) and why this test here fails (it might load a single account, but not all accounts). However, any accounts which have a ledger open would have had those splits/transactions already loaded. Fixed (basically) in r18136 - still not support for complete queries, but these queries work. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
How to integrate with new SQL backend
Seeking advice and opinions ... I have an application that I wrote that reads the GnuCash XML file and exports that data into an SQLite database. From there, it performs budgeting and reporting stuff against the SQLite database. So, now that GnuCash has an SQLite backend, I'm thinking about my options to remove the need to read and export the data from GnuCash into a separate database. My first question is - is it insane to want to have one data source? Are the risks of messing something up are too great, so leave it as is? Second question - if it isn't insane, then what changes can I make to the GnuCash SQLite database without causing problems? Can I add tables? Can I add columns to the GnuCash tables? Any other suggestions or opinions? Brian Pelton bpel...@jgboswell.com mailto:bpel...@jgboswell.com ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: How to integrate with new SQL backend
Hi, Quoting Pelton, Brian bpel...@jgboswell.com: [snip] My first question is - is it insane to want to have one data source? Not insane, but not necessarily a good idea.. Depends how the data is to be shared. Are the risks of messing something up are too great, so leave it as is? IMNSHO, yes. Second question - if it isn't insane, then what changes can I make to the GnuCash SQLite database without causing problems? None. Can I add tables? MAYBE, but I wouldn't recommend it. Can I add columns to the GnuCash tables? No. Any other suggestions or opinions? You can use the database as a readonly data source, but modifications from outside GnuCash are not supported. -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: gnucash-2.3 + sql backend
On Sat, 6 Jun 2009 13:09:58 -0400 Phil Longstaff plongst...@rogers.com wrote: On June 6, 2009 12:38:14 pm Klaus Dahlke wrote: Hi Phil, I digged around a bit and found the following statement in gnucash.trace: * 18:31:18 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 18:31:18 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 0? the server is up and running, started even with the -i option: postgres 22560 1 0 18:30 ?00:00:00 /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true -i The server listens to the default port :5432 and works normal. Any ideas? Yes, there's still a problem, and I just fixed it (r18108). Phil Hi Phil, in meanwhile, I figured out to correct that myself: I changed the line to gnc-backend-dbi.c: result = dbi_conn_set_option_numeric( be-conn, port, 5432 ); which works for me in the moment (having an hard coded port number is probably not the best way). With that, saving into an existing empty postgres database works fine. But I have a delicate problem when it comes to re-open the database. a) I used 'save as postres' to save foo.xac into the postgres. Works fine. b) quitting gnucash c) starting gnucash without any other command d) it then loads postgres://localhost/dbname:user:password quit fast e) unfortunately, some accounts show wrong balances If an account shows a wrong balances, then gnucash has calculated a balance less then zero for the time of when the first entry occurs. Example: received an interest payment for a savings account. The first line the account shows then: date: 1.1.2002, income=3.67; balance=-44.11 Thus, gnucash has calculated a balance of 47.78 being present on 31.12.2000. Surprisingly, this is not the case when using sqlite3. The file got saved and after quitting gnucash and reading the sqlite3 database, the values are the same as before and as in the .xac file. When further investigating the case, I saw that the differences might be linked to having not reconciled entries. On the command line I set all entires to be reconsiled: gnucash=# update splits set reconcile_date= '2009060612' where reconcile_date='1970010100'; UPDATE 9539 gnucash=# update splits set reconcile_state= 'y' where reconcile_state='n'; UPDATE 9539 After reading the so modified gnucash database, the values are correct. Again, it surprises me that when reading a postgres database the sorting via reconcile_date has an impact and for sqlite3 not. BTW: there is an entry in gnucash.trace regarding the timestamp: * 23:11:13 WARN qof.engine [gnc_iso8601_to_timespec_gmt()] mktime failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0 tm_isdst=-1 for string=1970-01-01 00:00:00 I hope that helps a bit. Best regards, Klaus ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: gnucash-2.3 + sql backend
On June 6, 2009 12:38:14 pm Klaus Dahlke wrote: Hi Phil, I digged around a bit and found the following statement in gnucash.trace: * 18:31:18 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 18:31:18 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 0? the server is up and running, started even with the -i option: postgres 22560 1 0 18:30 ?00:00:00 /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true -i The server listens to the default port :5432 and works normal. Any ideas? Yes, there's still a problem, and I just fixed it (r18108). Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: gnucash-2.3 + sql backend
On June 6, 2009 05:16:33 pm Klaus Dahlke wrote: On Sat, 6 Jun 2009 13:09:58 -0400 Phil Longstaff plongst...@rogers.com wrote: On June 6, 2009 12:38:14 pm Klaus Dahlke wrote: Hi Phil, I digged around a bit and found the following statement in gnucash.trace: * 18:31:18 INFO qof.session [qof_session_load_backend] selected GnuCash Libdbi (POSTGRESQL) Backend * 18:31:18 CRIT gnc.backend.dbi [pgsql_error_fn()] DBI error: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 0? the server is up and running, started even with the -i option: postgres 22560 1 0 18:30 ?00:00:00 /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true -i The server listens to the default port :5432 and works normal. Any ideas? Yes, there's still a problem, and I just fixed it (r18108). Phil Hi Phil, in meanwhile, I figured out to correct that myself: I changed the line to gnc-backend-dbi.c: result = dbi_conn_set_option_numeric( be-conn, port, 5432 ); which works for me in the moment (having an hard coded port number is probably not the best way). With that, saving into an existing empty postgres database works fine. But I have a delicate problem when it comes to re-open the database. a) I used 'save as postres' to save foo.xac into the postgres. Works fine. b) quitting gnucash c) starting gnucash without any other command d) it then loads postgres://localhost/dbname:user:password quit fast e) unfortunately, some accounts show wrong balances If an account shows a wrong balances, then gnucash has calculated a balance less then zero for the time of when the first entry occurs. Example: received an interest payment for a savings account. The first line the account shows then: date: 1.1.2002, income=3.67; balance=-44.11 Thus, gnucash has calculated a balance of 47.78 being present on 31.12.2000. Surprisingly, this is not the case when using sqlite3. The file got saved and after quitting gnucash and reading the sqlite3 database, the values are the same as before and as in the .xac file. When further investigating the case, I saw that the differences might be linked to having not reconciled entries. On the command line I set all entires to be reconsiled: gnucash=# update splits set reconcile_date= '2009060612' where reconcile_date='1970010100'; UPDATE 9539 gnucash=# update splits set reconcile_state= 'y' where reconcile_state='n'; UPDATE 9539 After reading the so modified gnucash database, the values are correct. Again, it surprises me that when reading a postgres database the sorting via reconcile_date has an impact and for sqlite3 not. BTW: there is an entry in gnucash.trace regarding the timestamp: * 23:11:13 WARN qof.engine [gnc_iso8601_to_timespec_gmt()] mktime failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0 tm_isdst=-1 for string=1970-01-01 00:00:00 I use the statement SELECT account_guid, reconcile_state, sum(quantity_num) as quantity_num, quantity_denom FROM splits GROUP BY account_guid, reconcile_state, quantity_denom to calculate and load the account balances. SQLite3 returns the list sorted by account_guid and reconcile_state, but postgresql doesn't. Turns out the list needs to be sorted, so I just added ORDER BY account_guid, reconcile_state. Committed as r18109. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Fw: Re: gnucash-2.3 + sql backend
Should go to devel list as well - Forwarded Message From: Phil Longstaff plongst...@rogers.com To: gnucash-list gnucash-u...@lists.gnucash.org Sent: Monday, June 1, 2009 10:26:45 AM Subject: Re: Re: gnucash-2.3 + sql backend This should be fixed for 2.3.1. For postgres, if the db did not exist, it didn't create it (and unfortunately, didn't indicate no such db). For 2.3.1, it will create the db, but will also, of course, require that the user have enough privilege to create a database. With this change in 2.3.1, it will also pop up a the db is not empty, do you want to overwrite it dialog box, and if you say yes, it will log some messages in the gnucash.trace log file. There is an issue in libdbi that returns some virtual tables from a postgres db (the postgres db engine makes these tables visible in each db, and libdbi doesn't provide any way of screening them out). It is currently being sorted out on the libdbi devel mailing list whether a new api will be added, or whether gnucash should just ignore these virtual tables. Eventually, gc might want to make use of the info in the tables (it provides information on the db server's sql capabilities). Phil From: Klaus Dahlke klaus.dah...@gmx.de To: gnucash-list gnucash-u...@lists.gnucash.org Sent: Monday, June 1, 2009 5:48:14 AM Subject: Fw: Re: gnucash-2.3 + sql backend Hi, sorry forget to send to mailing list as well. Cheersm Klaus On Mon, 25 May 2009 17:36:31 -0400 Phil Longstaff plongst...@rogers.com wrote: Hi Klaus, thanks for reporting these. Can't parse the URL postgres://localhost:dbname:user:. Is the password blank (in otherwords, you enter a host=localhost, db=dbname and user=user but leave the password field empty)? Hi Phil, I tried both, with password field empty (postgres configured to 'trust' all users on the local machine = not passowrd required) and with password field filled (postgres configured to require a password (md5 encrypted)). In both cases the above error comes up. Cheers, Klaus ___ gnucash-user mailing list gnucash-u...@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. ___ gnucash-user mailing list gnucash-u...@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Another problem with scheme query and SQL backend
Hi, i have another funny problem with querying data from scheme with the (my)sql backend. The query from my previous posts do only return transactions that are/were shown in the gui (ledger)! Steps to reproduce: * install config.user from my previous post in ~/.gnucash * open a nonempty sql-book with gnucash (with --extra command line option), all accounts closed, only account-tree visible * run scheme query from the Extensions - AQueryTest1 menu * watch output on console: got 0 transactions... * open a account with transactions in it * run scheme query from the Extensions - AQueryTest1 menu a second time * watch output on console, now it reports a number of returned transactions * close ledger view of account * re-run query, console still reports a number of returned transactions -- Mit freundlichen Gruessen Erwin Rieger | Ingenieurbuero Rieger, Software Entwicklung | EMail: erwin.rie...@ibrieger.de ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Another problem with scheme query and SQL backend
On May 31, 2009 06:53:23 am Erwin Rieger wrote: Hi, i have another funny problem with querying data from scheme with the (my)sql backend. The query from my previous posts do only return transactions that are/were shown in the gui (ledger)! Steps to reproduce: * install config.user from my previous post in ~/.gnucash * open a nonempty sql-book with gnucash (with --extra command line option), all accounts closed, only account-tree visible * run scheme query from the Extensions - AQueryTest1 menu * watch output on console: got 0 transactions... * open a account with transactions in it * run scheme query from the Extensions - AQueryTest1 menu a second time * watch output on console, now it reports a number of returned transactions * close ledger view of account * re-run query, console still reports a number of returned transactions Yes, there definitely isn't support for general queries, yet. I *do* have something in there which is commented out, so maybe I need to get it working. I wasn't aware of the extensions menu, so didn't have a way of creating a general query. All I had was the ones built into gnucash. The main one is a split query for a single account, used to populate a register, and at this point, the assumption is that that is the only query used. This explains why your BQueryTest fails (isn't split query for a single account) and why this test here fails (it might load a single account, but not all accounts). However, any accounts which have a ledger open would have had those splits/transactions already loaded. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Scheme query, difference between XML and SQL backend, coredump
Hi, i've noticed a different behavior of gnucash-backends when running querys from scheme. If a query is setup without accounts the xml backend selects data from all accounts, the sql backend coredumps with the same query (see backtrace below). If the query is setup with accounts, the query works with both the xml and sql backend. Scheme code to reproduce the error, this config.user adds two entries to the Extensions menu. AQueryTest1 runs a query with accounts (xaccQueryAddAccountMatch), AqueryTest2 runs a query without accounts (this is the one that crashes the sql backend): ## config.user ## (printf Start config.user\n) (gnc-add-scm-extension ( gnc:make-menu-item (N_ AQueryTest1) (N_ AQueryTest1) (list (N_ Extensions)) (lambda (window) (let ( (book (gnc-get-current-book)) (query (qof-query-create-for-splits)) (accounts (gnc-account-get-descendants-sorted (gnc-get-current-root-account))) ) (printf querytest 1 ...\n) (qof-query-set-book query book) (printf querytest 2 ...\n) (xaccQueryAddAccountMatch query accounts QOF-GUID-MATCH-ANY QOF-QUERY-AND) (printf querytest 3 ...\n) (printf got %d transactions...\n (length (xaccQueryGetTransactions query QUERY-TXN-MATCH-ANY))) (printf querytest 4 ...\n) (qof-query-destroy query) (printf querytest 5 ...\n) ) ) ) ) (gnc-add-scm-extension ( gnc:make-menu-item (N_ BQueryTest2) (N_ BQueryTest2) (list (N_ Extensions)) (lambda (window) (let ( (book (gnc-get-current-book)) (query (qof-query-create-for-splits)) (accounts (gnc-account-get-descendants-sorted (gnc-get-current-root-account))) ) (printf querytest 1 ...\n) (qof-query-set-book query book) ; (printf querytest 2 ...\n) ; (xaccQueryAddAccountMatch query accounts QOF-GUID-MATCH-ANY QOF-QUERY-AND) (printf querytest 2 ...\n) (printf got %d transactions...\n (length (xaccQueryGetTransactions query QUERY-TXN-MATCH-ANY))) (printf querytest 3 ...\n) (qof-query-destroy query) (printf querytest 4 ...\n) ) ) ) ) (printf End config.user\n) ## config.user ## GDB backtrace from the query without accounts on a sql backend. Note: andTerm is NULL, so accessing andTerm-data is not a good idea ;-) ## gdb backtrace ## Loaded symbols for /usr/lib/libgvfscommon.so.0 Reading symbols from /home/bh/gnucash_mysql_install/lib/gnucash/libgncqof-backend-qsf.so...done. Loaded symbols for /home/bh/gnucash_mysql_install/lib/gnucash/libgncqof-backend-qsf.so 0xe424 in __kernel_vsyscall () (gdb) c Continuing. Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0xb680b700 (LWP 24145)] 0xb49914d3 in get_guid_from_query (pQuery=0x83a7140) at gnc-transaction-sql.c:642 642 pTerm = (QofQueryTerm*)andTerm-data; (gdb) bt #0 0xb49914d3 in get_guid_from_query (pQuery=0x83a7140) at gnc-transaction-sql.c:642 #1 0xb49917b5 in compile_split_query (be=0x837df90, pQuery=0x83a7140) at gnc-transaction-sql.c:725 #2 0xb4982891 in compile_query_cb (type=0xb4994c19 Split, data_p=0xb4997980, be_data_p=0xbf7fde4c) at gnc-backend-sql.c:678 #3 0xb7ecd81d in foreach_backend (key=0xb4994c19, be_item=0xb4997980, arg=0xbf7fde08) at qofobject.c:339 #4 0xb69ba931 in g_hash_table_foreach () from /usr/lib/libglib-2.0.so.0 #5 0xb3e1a6de in ?? () from /home/bh/gnucash_mysql_install/lib/gnucash/libgncmod-business-backend-sql.so #6 0x in ?? () (gdb) p andTerm $1 = (GList *) 0x0 (gdb) ## gdb backtrace ## -- Mit freundlichen Gruessen Erwin Rieger | Ingenieurbuero Rieger, Software Entwicklung | EMail: erwin.rie...@ibrieger.de ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Scheme query, difference between XML and SQL backend, coredump
Version i've testet ist latest svn, 18095. -- Mit freundlichen Gruessen Erwin Rieger | Ingenieurbuero Rieger, Software Entwicklung | EMail: erwin.rie...@ibrieger.de ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Scheme query, difference between XML and SQL backend, coredump
Hi Erwin, how do you install and use these? Phil On May 30, 2009 11:30:02 am Erwin Rieger wrote: Hi, i've noticed a different behavior of gnucash-backends when running querys from scheme. If a query is setup without accounts the xml backend selects data from all accounts, the sql backend coredumps with the same query (see backtrace below). If the query is setup with accounts, the query works with both the xml and sql backend. Scheme code to reproduce the error, this config.user adds two entries to the Extensions menu. AQueryTest1 runs a query with accounts (xaccQueryAddAccountMatch), AqueryTest2 runs a query without accounts (this is the one that crashes the sql backend): ## config.user ## (printf Start config.user\n) (gnc-add-scm-extension ( gnc:make-menu-item (N_ AQueryTest1) (N_ AQueryTest1) (list (N_ Extensions)) (lambda (window) (let ( (book (gnc-get-current-book)) (query (qof-query-create-for-splits)) (accounts (gnc-account-get-descendants-sorted (gnc-get-current-root-account))) ) (printf querytest 1 ...\n) (qof-query-set-book query book) (printf querytest 2 ...\n) (xaccQueryAddAccountMatch query accounts QOF-GUID-MATCH-ANY QOF-QUERY-AND) (printf querytest 3 ...\n) (printf got %d transactions...\n (length (xaccQueryGetTransactions query QUERY-TXN-MATCH-ANY))) (printf querytest 4 ...\n) (qof-query-destroy query) (printf querytest 5 ...\n) ) ) ) ) (gnc-add-scm-extension ( gnc:make-menu-item (N_ BQueryTest2) (N_ BQueryTest2) (list (N_ Extensions)) (lambda (window) (let ( (book (gnc-get-current-book)) (query (qof-query-create-for-splits)) (accounts (gnc-account-get-descendants-sorted (gnc-get-current-root-account))) ) (printf querytest 1 ...\n) (qof-query-set-book query book) ; (printf querytest 2 ...\n) ; (xaccQueryAddAccountMatch query accounts QOF-GUID-MATCH-ANY QOF-QUERY-AND) (printf querytest 2 ...\n) (printf got %d transactions...\n (length (xaccQueryGetTransactions query QUERY-TXN-MATCH-ANY))) (printf querytest 3 ...\n) (qof-query-destroy query) (printf querytest 4 ...\n) ) ) ) ) (printf End config.user\n) ## config.user ## GDB backtrace from the query without accounts on a sql backend. Note: andTerm is NULL, so accessing andTerm-data is not a good idea ;-) ## gdb backtrace ## Loaded symbols for /usr/lib/libgvfscommon.so.0 Reading symbols from /home/bh/gnucash_mysql_install/lib/gnucash/libgncqof-backend-qsf.so...done. Loaded symbols for /home/bh/gnucash_mysql_install/lib/gnucash/libgncqof-backend-qsf.so 0xe424 in __kernel_vsyscall () (gdb) c Continuing. Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0xb680b700 (LWP 24145)] 0xb49914d3 in get_guid_from_query (pQuery=0x83a7140) at gnc-transaction-sql.c:642 642 pTerm = (QofQueryTerm*)andTerm-data; (gdb) bt #0 0xb49914d3 in get_guid_from_query (pQuery=0x83a7140) at gnc-transaction-sql.c:642 #1 0xb49917b5 in compile_split_query (be=0x837df90, pQuery=0x83a7140) at gnc-transaction-sql.c:725 #2 0xb4982891 in compile_query_cb (type=0xb4994c19 Split, data_p=0xb4997980, be_data_p=0xbf7fde4c) at gnc-backend-sql.c:678 #3 0xb7ecd81d in foreach_backend (key=0xb4994c19, be_item=0xb4997980, arg=0xbf7fde08) at qofobject.c:339 #4 0xb69ba931 in g_hash_table_foreach () from /usr/lib/libglib-2.0.so.0 #5 0xb3e1a6de in ?? () from /home/bh/gnucash_mysql_install/lib/gnucash/libgncmod-business-backend-sql.s o #6 0x in ?? () (gdb) p andTerm $1 = (GList *) 0x0 (gdb) ## gdb backtrace ## ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: gnucash-2.3 + sql backend
Hi Klaus, thanks for reporting these. On May 25, 2009 09:48:52 am Klaus Dahlke wrote: Hi, I'd like to test gnucash-2.3, in particular the sql backend (with Postgres). I reading some older post I figured out to use --enable-dbi to build the proper backend functionality. gnucash-2.3.0 compiled fine (gentoo, amd64). a) sqlite3 works out of the box. But reading seems a bit slow (the file is ~ 7MB) b) With mysql some messages are displayed during creation of the database: I'm aware of the problems with mysql and they should be fixed with 2.3.1. I hadn't thought of modifying the password box so that it wasn't clear text. I'll do that as well. c) When trying to save to a postgres database the following error comes up: Can't parse the URL postgres://localhost:dbname:user:. Is the password blank (in otherwords, you enter a host=localhost, db=dbname and user=user but leave the password field empty)? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Problem with SQL backend on MacOX on PowerPC
Mike Alexander has discovered that 2.3.0 using a libdbi-based SQL backend does not work on a PowerPC using MacOX but probably other OSs as well. The problem is that libdbi assumes the wrong endianness. He has submitted a patch to the libdbi project to fix it and attached it to bug #583150 (http://bugzilla.gnome.org/show_bug.cgi?id=583150). Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Problem with SQL backend on MacOX on PowerPC
On Fri, May 22, 2009 at 9:12 AM, Phil Longstaff plongst...@rogers.com wrote: Mike Alexander has discovered that 2.3.0 using a libdbi-based SQL backend does not work on a PowerPC using MacOX but probably other OSs as well. The problem is that libdbi assumes the wrong endianness. He has submitted a patch to the libdbi project to fix it and attached it to bug #583150 (http://bugzilla.gnome.org/show_bug.cgi?id=583150). Unfortunately I discovered a problem in the patch and just attached a new version to that ticket. The driver API in libdbi supports 3 byte integers which are a rather unusual data type. I'm not sure how much they are used, but my previous patch didn't handle them consistently with the rest of the code. I also discovered another endian bug in the Oracle driver which I didn't fix, but I doubt many people are using Oracle with GnuCash yet. Mike ___ 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
SQL Backend can't parse URL
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
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: Load/Save As dialog with SQL backend
Hi Phil, Am Sunday 11 January 2009 21:37:51 schrieb Phil Longstaff: : Possible solution: provide a dialog similar in appearance to the preferences dialog. on the left side is a list with entries sqlite, xml, mysql and postgresql. If sqlite or xml is selected, the left side is a standard Gtk open or save widget. If mysql or postgresql is selected, the left side is the database connection dialog which allows the user to enter host/database/username/password. Sounds good to me, but it would probably be more userfriendly if you only put the list (xml, sqlite ...) on the left side and the other stuff on the RIGHT side. ;-) Thoughts? I'll probably implement this and add to the menus (while keeping the current load/save as) so people can use and provide feedback. Phil Frank ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Load/Save As dialog with SQL backend
Bugzilla bug 562848 (http://bugzilla.gnome.org/show_bug.cgi?id=562848) and its duplicate 562841 (http://bugzilla.gnome.org/show_bug.cgi?id=562841) request that 1) the file load/save dialog be merged with the database connection dialog and 2) some method be provided to save as a new XML file (currently, save as will save as sqlite and not provide a way to specify XML). Possible solution: provide a dialog similar in appearance to the preferences dialog. on the left side is a list with entries sqlite, xml, mysql and postgresql. If sqlite or xml is selected, the left side is a standard Gtk open or save widget. If mysql or postgresql is selected, the left side is the database connection dialog which allows the user to enter host/database/username/password. Thoughts? I'll probably implement this and add to the menus (while keeping the current load/save as) so people can use and provide feedback. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Having said that, we shouldn't rule out users accessing the data out from under GnuCash, at least in a read-only means. I still believe that external tools should not be writing data into the GnuCash database. Have you thought about how allow other applications could use GnuCash engine to write data into the GC's database? -- Trabajar, la mejor arma para tu superación de grano en grano, se hace la arena (R) (en trámite, pero para los cuates: LIBRE) ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Quoting Daniel Espinosa [EMAIL PROTECTED]: Having said that, we shouldn't rule out users accessing the data out from under GnuCash, at least in a read-only means. I still believe that external tools should not be writing data into the GnuCash database. Have you thought about how allow other applications could use GnuCash engine to write data into the GC's database? Absolutely. They should use the GnuCash API. However the GnuCash API is currently not exported in a way to make this easy. However in the long term that should get made easier. In the short term it's not important. -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: Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 20, 2008 11:27:03 pm Daniel Espinosa wrote: If you look at libqof's code you'll find that all GC's QOF implementation is based in store *all* data object in memory using a GHashTable using a string for the object type and GUID for identification, any search for any object is a foreach routine. This implementation is good for XML data store, but for DB is quite poore feature, becouse hides all DB's data manipulation and data integrity checks. If GC could allow any other to access the data from the DB in a way of SQL queries or DataModels, taking care about Accounts, Transactions, and other objects for data consistency and integrity, could allows others to take lot advantages. Hi Daniel, I *completely* agree with you that I would like to see gnucash become a true database app. At this point, however, it isn't, and will take a lot of work to get there. It is a change, not only in the back end storage, but also in how the data is accessed. Perhaps, once the db back end is released, work can happen to make those other changes. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 20, 2008 06:38:14 pm Phil Longstaff wrote: On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. OK. I have modified code (not committed yet) which doesn't read tx/splits at startup. At startup, it executes: SELECT account_guid, reconcile_state, sum(quantity_num) AS quantity_num, quantity_denom FROM splits GROUP BY account_guid, reconcile_state, quantity_denom This gives me 1 row per guid/reconcile_state/quantity_denom triplet. From these, I set the start balance, start cleared balance and start reconciled balance for each account. When the backend receives a query to load splits for an account, it uses this algorithm: 1: save start and end balance, cleared balance and reconciled balance for each account 2: stop qof events 3: load all transactions which contains splits for the target account. This loads all splits for those transactions, even those in other accounts. 4: for each account, get new end balances. If end balance doesn't match end balance from step 1, calculate old_start_balance-(new_end_balance- old_end_balance) and set this as the new start balance. 5: resume qof events IOW, if the end balance for an account increases by X because of newly loaded splits, step 4 decreases the start balance by X to make the end balance match what is should be. Eventually, once all tx/splits are loaded, all start balances are 0. Each account that is the target of a query for all splits is marked so that it's splits are never reloaded. Done as a performance improvement but would need to be revisited for a multi-user back end. I need a bit more testing, but once I'm happy with this, I'll commit it. It does lead to a delay when opening an account with a lot of splits. I may look at adding a progress bar or other indication that the app is busy. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reading whole db at startup (was Re: String lengths in the SQL backend)
If you look at libqof's code you'll find that all GC's QOF implementation is based in store *all* data object in memory using a GHashTable using a string for the object type and GUID for identification, any search for any object is a foreach routine. This implementation is good for XML data store, but for DB is quite poore feature, becouse hides all DB's data manipulation and data integrity checks. If GC could allow any other to access the data from the DB in a way of SQL queries or DataModels, taking care about Accounts, Transactions, and other objects for data consistency and integrity, could allows others to take lot advantages. 2008/11/20 Phil Longstaff [EMAIL PROTECTED] On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. Phil ___ 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) (en trámite, pero para los cuates: LIBRE) ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
