Nope, because GnuCash uses the DB only as a data store. All of the business logic and referential integrity are inside GnuCash. If you write the database from outside of that you can far too easily unbalance transactions or break critical references.
Regards, John Ralls > On Jan 21, 2022, at 3:57 PM, Kalpesh Patel <kalpesh.pa...@usa.net> wrote: > > Is any tool, perhaps SQLite studio (https://sqlitestudio.pl/about/ > <https://sqlitestudio.pl/about/>), supported as the alternate to an GNC API > as the Editor that won’t void the warranty? > > From: Thomas Forrester <tlforres...@gmail.com> > Sent: Friday, January 21, 2022 5:03 PM > To: John Ralls <jra...@ceridwen.us> > Cc: Kalpesh Patel <kalpesh.pa...@usa.net>; gnucash-user > <gnucash-user@gnucash.org> > Subject: Re: [GNC] Exiting Gnucash Lock file > > John, > > Haha! Yeah, I'm afraid I've voided that warranty a few times, but only to > correct reconciliation issues that took me moments to correct in Access, yet > seem to cause much hair-pulling and gnashing of teeth with others. > > I don't think it would be any performance or storage issue to simply add an > auto-number integer field and set as the primary key, though. Doesn't affect > your other indices in the least, so I doubt your code even needs to change. > But IDK, and I'm sure making the GnuCash SQLite3 db compatible with ODBC > connectivity to MS Access is no priority at all. Just wishful thinking on my > part. > > On Fri, Jan 21, 2022 at 3:45 PM John Ralls <jra...@ceridwen.us > <mailto:jra...@ceridwen.us>> wrote: >> Having the database read-only in Access sounds like a feature: Writing to >> the GnuCash database using anything except the GnuCash API voids your >> warranty. >> >> Access insisting on a non-text Primary Key an Access bug. All of GnuCash's >> tables have primary keys already, but they're mostly text representations of >> GUIDs because SQLite doesn't support 128-bit unsigned ints nor--unlike MySQL >> and Postgresql--have a UUID field type. >> >> Regards, >> John Ralls >> >> > On Jan 21, 2022, at 1:25 PM, Thomas Forrester <tlforres...@gmail.com >> > <mailto:tlforres...@gmail.com>> wrote: >> > >> > I have tried to switch to SQLite3 this afternoon, and although I cannot >> > answer all your questions, I have observed the following: >> > >> > 1. Regarding your stability on Windows question, Check Wiki on SQLite. >> > It's pretty ubiquitous at this point and extraordinarily stable on all >> > platforms. I don't think you're going to have an issue with stability. >> > >> > 2. There are log files when using SQLite3, but I cannot say if they work >> > as you are asking. >> > >> > 3. Using the recommended ODBC driver, I was able quite easily to connect >> > the GnuCash database to Microsoft Access, but immediately realized there >> > are insurmountable problems that I have not found with ODBC/Access using a >> > MySQL backend for GnuCash. Specifically, although the tables link into >> > Access with the expected number of rows, All column data shows as >> > #DELETED#. According to more research, I find the following >> > incompatibility between the way the GnuCash database is designed and what >> > Access aspects (from stackoverflow): >> > >> > [...] Access (Jet) wants a table to have a unique index in order to be able >> > to insert/update the table if necessary. >> > >> > If your SQLite table doesn't have a unique index (or primary key), then >> > Access will only allow read access to the table -- you can't edit the >> > table's data in Access, but the data displays fine. >> > >> > To make the table updateable you might revise your SQLite code (or using a >> > SQLite tool) to add an index to the table. >> > >> > If your PK/unique index happens to use a TEXT field, that's fine for >> > SQLite. However, when you link to it in Access, Access will show the >> > #DELETED# indications. >> > >> > The chain of events appears to be: >> > >> > Access/Jet notices the unique index, and tries to use it. However, SQLite >> > TEXT fields are variable length and possibly BLOBs. This apparently doesn't >> > fulfill Access's requirements for a unique index field, hence the #DELETED# >> > indication. >> > >> > To avoid that problem, the index has to be a SQLite field type that Access >> > will accept. I don't know the complete list of types that are acceptable, >> > but INTEGER works. >> > >> > >> > 4. I don't hold any hope of this, but it would be truly outstanding if the >> > development team considered this issue (#3 above) and worked to resolve >> > it. It most certainly is an outlier issue and not something germane to >> > GnuCash itself, in fact it is more akin to assisting someone in shooting >> > themselves in the foot. But to someone who understands the proper >> > precautions, it sure would be helpful! (Not sure why ODBC to Access using >> > MySQL works fine. Different db structures for different dbs???) >> > >> > >> > >> > >> > >> > >> > >> > On Fri, Jan 21, 2022 at 2:48 PM Kalpesh Patel <kalpesh.pa...@usa.net >> > <mailto:kalpesh.pa...@usa.net>> wrote: >> > >> >> Few questions in regards to SQLite3 backend recommendation because >> >> currently >> >> XML format for my data is compressed size of 1.82MB so exploring moving to >> >> it. Not sure if this is considered small sized XML file or not but . >> >> >> >> >> >> >> >> 1 - SQLite was natively born on UNIX systems with UNIX systems in mind so >> >> how stable is it for Windows and how well is it supported from the GNC >> >> perspective? >> >> >> >> 2 - Does it still create the log file to be able to replay it like XML >> >> storage creates? >> >> >> >> 3 - How can I roll back transactions (more so related to roll back from >> >> import errors)? With XML I can take the previously created file and make >> >> it >> >> my current file after renaming it. >> >> >> >> >> >> >> >> Any other differences to keep in mind other than backup regiment? >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> ------------------------------ >> >> >> >> >> >> >> >> Message: 8 >> >> >> >> Date: Fri, 21 Jan 2022 08:43:04 -0800 >> >> >> >> From: john <jra...@ceridwen.us <mailto:jra...@ceridwen.us> >> >> <mailto:jra...@ceridwen.us <mailto:jra...@ceridwen.us>> > >> >> >> >> To: Thomas Forrester <tlforres...@gmail.com >> >> <mailto:tlforres...@gmail.com> <mailto:tlforres...@gmail.com >> >> <mailto:tlforres...@gmail.com>> >> >>> >> >> >> >> Cc: Peter <pan...@iinet.net.au <mailto:pan...@iinet.net.au> >> >> <mailto:pan...@iinet.net.au <mailto:pan...@iinet.net.au>> >, >> >> gnucash-user >> >> >> >> <gnucash-user@gnucash.org >> >> <mailto:gnucash-user@gnucash.org> <mailto:gnucash-user@gnucash.org >> >> <mailto:gnucash-user@gnucash.org> >> >>> >> >>> , john.lay...@laymanandlayman.com >> >>> <mailto:john.lay...@laymanandlayman.com> >> >>> <mailto:john.lay...@laymanandlayman.com >> >>> <mailto:john.lay...@laymanandlayman.com>> >> >> >> >> >> >> Subject: Re: [GNC] Exiting GnuCash Lock file Vol 226, Issue 57 >> >> >> >> Message-ID: <e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us >> >> <mailto:e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us> >> >> <mailto:e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us >> >> <mailto:e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us>> > >> >> >> >> Content-Type: text/plain; charset=us-ascii >> >> >> >> >> >> >> >> Very few users will be able to manage a MySQL database server safely. >> >> Fortunately GnuCash provides a SQLite3 backend that doesn't require any >> >> database admin skills. The only caveat is that unlike XML it doesn't write >> >> a >> >> fresh file every session so you need to arrange backups on your own; the >> >> OS's built-in snapshotting facility (rather grandly named Time Machine on >> >> macOS and simply Windows Backup on Windows) is good for this, just make >> >> sure >> >> that your GnuCash data folder is included in its list of things to back >> >> up. >> >> >> >> >> >> >> >> Regards, >> >> >> >> John Ralls >> >> >> >> >> >> >> >> >> >> >> >>> On Jan 21, 2022, at 6:45 AM, Thomas Forrester <tlforres...@gmail.com >> >>> <mailto:tlforres...@gmail.com> >> >> <mailto:tlforres...@gmail.com <mailto:tlforres...@gmail.com>> > wrote: >> >> >> >>> >> >> >> >>> Seems like a lot of work. No doubt you're finding interesting >> >> >> >>> anomalies, but... >> >> >> >>> >> >> >> >>> Rather than using xml data files, which have always seemed odd to me >> >> >> >>> (a la, this thread), if you use a MySQL database instead, all saves >> >> >> >>> are automatic and completely bypass the need for all this testing. >> >> >> >>> You still have to save each transaction, or edits to a transaction >> >> >> >>> (Enter key), but otherwise you can freely close the program without >> >> >> >>> any worries about loosing data, and there is no save prompt on the way >> >> >> >>> out. I've run with a MySQL database from the start without any >> >> >> >>> problems at all. Just a thought, and I recognize some may have >> >> >> >>> reasons not to choose that path, although I'm not sure what they would >> >> be. >> >> >> >>> >> >> >> >>> On Thu, Jan 20, 2022, 11:22 PM Peter <pan...@iinet.net.au >> >>> <mailto:pan...@iinet.net.au> >> >> <mailto:pan...@iinet.net.au <mailto:pan...@iinet.net.au>> > wrote: >> >> >> >>> >> >> >> >>>> John, >> >> >> >>>> Well yes it did terminate or close. >> >> >> >>>> gnucash was closed and was then reopened without any reported issue. >> >> >> >>>> Other than it failed to save the changes and delete the lock file. >> >> >> >>>> Maybe if you could tell me how to confirm it closed or terminated >> >> >> >>>> correctly, I will go and test and see what happens. >> >> >> >>>> This issue I noted in Test#2 was actually caused by me not following >> >> my >> >> >> >>>> own test process. >> >> >> >>>> >> >> >> >>>> On 21/01/2022 04:48, John Layman wrote: >> >> >> >>>> >> >> >> >>>> The test cases that appear to have failed aren't conclusive, however, >> >> >> >>>> without co nfirming that GnuCash had actually terminated, and >> >> >> >>>> terminated normally. >> >> >> >>>> >> >> >> >>>> -----Original Message----- >> >> >> >>>> From: gnucash-user [1]<gnucash-user-bounces+john.layman= >> >> >> >>>> ieee....@gnucash.org <mailto:ieee....@gnucash.org> >> >>>> <mailto:ieee....@gnucash.org <mailto:ieee....@gnucash.org>> > On >> >> >> >>>> Behalf Of Peter >> >> >> >>>> Sent: Thursday, January 20, 2022 9:39 AM >> >> >> >>>> To: [2]gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >> >> >> >>>> Subject: Re: [GNC] Exiting gnucash Lock file Vol 226, Issue 57 >> >> >> >>>> >> >> >> >>>> Here is the testing methods I used to verify the issue I have noted. >> >> >> >>>> >> >> >> >>>> Selected a known small good gnucash data file. >> >> >> >>>> Storage Location: Local D: c: drive is Samsung SSD and D: is Seagate >> >> >> >>>> 2TB GNUCAS H Storage type : XML and compressed Operating system: >> >> >> >>>> Windows 10 GNUCASH versio n 4.9 The opening method in ALL test was to >> >> >> >>>> use the GNUCASH icon on the TASKBAR and then select the recent (test) >> >> >> >>>> file If the LOCK file was not deleted it was r emoved using the >> >> >> >>>> windows File Explorer >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #1: - change, Save Button and then File->Quit >> >> >> >>>> >> >> >> >>>> Result: This was repeated 5 times each time the same result - Lock file >> >> >> >>>> was dele >> >> >> >>>> ted and the change was Saved. >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #2 - change then File>Quit and save at 15 seconds on timer >> >> >> >>>> >> >> >> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT >> >> >> >>>> saved in >> >> >> >>>> the data file This test was actual performed more than 5 times but I did >> >> >> >>>> note 5 >> >> >> >>>> of 5 in one contiguous run. There were a few test that asked to save the >> >> >> >>>> transa >> >> >> >>>> ction prior to the question of save the file I was never able to get it >> >> to >> >> >> >>>> repea >> >> >> >>>> t twice in a row. >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #3 - change and exit File->Quit wait for the Save dialogue then let >> >> >> >>>> it time >> >> >> >>>> out >> >> >> >>>> >> >> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >> >> >>>> the dat >> >> >> >>>> a file >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #4 - change and wait for autosave then File->Quit >> >> >> >>>> >> >> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >> >> >>>> the dat >> >> >> >>>> a file >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #5 - change and wait for autosave then use X method >> >> >> >>>> >> >> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >> >> >>>> the dat >> >> >> >>>> a file >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Test #6 - change then use X method wait for the save dialogue and save >> >> at >> >> >> >>>> 15 sec >> >> >> >>>> onds on timer >> >> >> >>>> >> >> >> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT >> >> >> >>>> saved in >> >> >> >>>> the data file >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >> ---------------------------------------------------------------------------- >> >> ---- >> >> >> >>>> --------------------- >> >> >> >>>> >> >> >> >>>> Conclusion: >> >> >> >>>> >> >> >> >>>> There is an issue in exiting with the SAVE button enabled/Highlighted >> >> and >> >> >> >>>> comply >> >> >> >>>> ing with the dialogue to SAVE the Data file. >> >> >> >>>> Test#2 and Test#6 >> >> >> >>>> >> >> >> >>>> Four methods of exiting that do delete the LOCK file and SAVE the latest >> >> >> >>>> changes >> >> >> >>>> A: Waiting to exit after an Autosave then File->Quit >> >> >> >>>> B: forcing a Save by using the SAVE button then File->Quit >> >> >> >>>> C: Waiting to exit after an Autosave then the X method >> >> >> >>>> D: forcing a Save by using the SAVE button then the X method >> >> >> >>>> >> >> >> >>>> X method works if you do a SAVE using the Save Button or wait till >> >> after >> >> >> >>>> an Au >> >> >> >>>> tosave then exit via the X method >> >> >> >>>> >> >> >> >>>> There is an issue with exiting and relying on gnucash to save the data >> >> if >> >> >> >>>> gnucas >> >> >> >>>> h produces the dialogue to SAVE the data then File->Quit or the X method >> >> >> >>>> The Sav >> >> >> >>>> e does not work and the LOCK file is not deleted. >> >> >> >>>> >> >> >> >>>> I cannot safely say I do not use the X method as I now have doubts >> >> about >> >> >> >>>> it, I >> >> >> >>>> may use it or I may not. >> >> >> >>>> >> >> >> >>>> Hope this helps someone as now I understand why sometimes it does work >> >> and >> >> >> >>>> does >> >> >> >>>> NOT work, there is a bug in it or is it a misinterpreted enhancement. >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> >>>> _______________________________________________ >> >> >> >>>> gnucash-user mailing list >> >> >> >>>> [3]gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >> >> >> >>>> To update your subscription preferences or to unsubscribe: >> >> >> >>>> [4]https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> >> >> >>>> If you are using Nabble or Gmane, please see [5] >> >> >> >>>> https://wiki.gnucash.org/wiki/Ma <https://wiki.gnucash.org/wiki/Ma> >> >> >> >>>> iling_Lists for more information. >> >> >> >>>> ----- >> >> >> >>>> Please remember to CC this list on all your replies. >> >> >> >>>> You can do this by using Reply-To-List or Reply-All. >> >> >> >>>> >> >> >> >>>> References >> >> >> >>>> >> >> >> >>>> 1. mailto:gnucash-user-bounces+john.layman >> >>>> <mailto:gnucash-user-bounces%2Bjohn.layman>=ieee....@gnucash.org >> >>>> <mailto:ieee....@gnucash.org> >> >> >> >>>> 2. mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >> >> >>>> 3. mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >> >> >>>> 4. https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> >> >> >>>> 5. https://wiki.gnucash.org/wiki/Mailing_Lists >> >>>> <https://wiki.gnucash.org/wiki/Mailing_Lists> >> >> >> >>>> _______________________________________________ >> >> >> >>>> gnucash-user mailing list >> >> >> >>>> gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >> >> >> >>>> To update your subscription preferences or to unsubscribe: >> >> >> >>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> >> >> >>>> If you are using Nabble or Gmane, please see >> >> >> >>>> https://wiki.gnucash.org/wiki/Mailing_Lists >> >>>> <https://wiki.gnucash.org/wiki/Mailing_Lists> for more information. >> >> >> >>>> ----- >> >> >> >>>> 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-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>> <mailto:gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org>> >> >> >> >>> To update your subscription preferences or to unsubscribe: >> >> >> >>> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>> <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> >> >> >>> If you are using Nabble or Gmane, please see >> >> https://wiki.gnucash.org/wiki/Mailing_Lists >> >> <https://wiki.gnucash.org/wiki/Mailing_Lists> for more information. >> >> >> >>> ----- >> >> >> >>> 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-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >> To update your subscription preferences or to unsubscribe: >> >> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >> <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> >> If you are using Nabble or Gmane, please see >> >> https://wiki.gnucash.org/wiki/Mailing_Lists >> >> <https://wiki.gnucash.org/wiki/Mailing_Lists> for more information. >> >> ----- >> >> Please remember to CC this list on all your replies. >> >> You can do this by using Reply-To-List or Reply-All. >> >> >> > >> > >> > -- >> > Tom >> > Thomas L. Forrester >> > 3211 Patty Lane >> > Middleton, WI 53562-1652 USA >> > 608-831-0769 >> > _______________________________________________ >> > gnucash-user mailing list >> > gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> > To update your subscription preferences or to unsubscribe: >> > https://lists.gnucash.org/mailman/listinfo/gnucash-user >> > <https://lists.gnucash.org/mailman/listinfo/gnucash-user> >> > If you are using Nabble or Gmane, please see >> > https://wiki.gnucash.org/wiki/Mailing_Lists >> > <https://wiki.gnucash.org/wiki/Mailing_Lists> for more information. >> > ----- >> > Please remember to CC this list on all your replies. >> > You can do this by using Reply-To-List or Reply-All. >> > > > > -- > Tom > Thomas L. Forrester > 3211 Patty Lane > Middleton, WI 53562-1652 USA > 608-831-0769 _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
