Re: [GNC-dev] [GNC] mysql backend, second user (lock, for example)

[John Ralls]

> On Nov 6, 2018, at 4:47 PM, Craig Arno  wrote:
> 
> Phil,
> 
> I did more investigation of SQLite v3 today and found a few impressive
> things to summarize and pass along, which you may already know, but if not:
> 
> 1. SQLite is "/full featured/", providing almost all of the features of
>a "Server" based database installation; *triggers* being the notable
>one for this discussion
> 2. Stable, enduring file format
>.  Attention is paid to
>backward compatibility.  If you pull a 30 year old SQLite database
>file [GnuCash file] out of the archives, it should work just like
>the day it was checked into the archives. Developer intent is to
>support SQLite file backward compatibility through the year 2050. 
>This is a feature I'd like in GnuCash
> 3. A SQLite database file is the recommended storage format
> by the US Library of Congress
>for database files. The reasons in my mind place it up there with
>"international standard ISO/IEC 26300 – Open Document Format for
>Office Applications" used by LibreOffice/OpenOffice.  It still isn't
>a real standard, but has some of the same desirable features, by design
> 4. ACID transactions, even after power loss
>.  Atomic transactions
>greatly reduce the possibility of database corruption or data loss
>from power/system failure "fault tolerance".  Features desirable for
>a financial application database, like I'd want for GnuCash.
> 5. Aviation-grade quality and testing
>.  I come from high reliability
>"Aviation" and "Medical" development environments.  This is a
>powerful statement about the software's ability to perform as intended
> 6. Zero-configuration . No
>"login", "permissions", "processes", like there are in a server
>application installation, yet #1 - it provides all the database API
>features
> 7. SQLite can be 35% faster than direct filesystem I/O
>
> 8. and of course, SQLite is cross platform
> 
> I found a non-commercial Windows ODBC driver
>  with source for SQLite3 database
> files.  This let me open and work with a GnuCash SQLite3 database file
> like I currently can with MySQL and phpMyAdmin on my server.  I used
> LibreOffice-Base.  The peripheral tools are available today to support
> full development access to data contained in GnuCash SQLite3 files.
> 
> I'd like to see SQLite used as local database cache for GnuCash
> connection to a server based database, similar to how Git operates. 
> This will give a business user boarding an airplane the ability to enter
> a folder full of travel receipts into a local GnuCash database for
> upload/synchronization to a server based database when Internet access
> is restored.  This would also cover bad internet situations like Hotels,
> third world countries, and secure site (network blackout)
> installations.  And SQLite provides for full featured standalone GnuCash
> installations opening the possibility to connect to a GnuCash peer, for
> networked peer to peer database access to a single SQLite database
> residing on either machine using most of the same software as accessing
> a remote server installation.
> 
> I'm quite impressed with what I read about SQLite v3.

Yeah, SQLite3 is a pretty darn good job. It’s well written, well supported and 
consequently really widely used.

We intend to convert the XML backend to loading a SQLite3 in-memory database at 
session startup so that we can query against it instead of the current 
QOFQuery. I’m still working out how to handle the transition and how to 
prioritize it relative to GObject->C++ in the core engine objects.

I hadn’t considered doing that as a shim for a server-based DB. I’m not sure 
that it would be a real benefit and it could get pretty ugly to implement. I 
think the shared SQLite3 file will work OK with a low-latency LAN file share 
(e.g. NFS or SMB), but I’m also pretty sure that it won’t for a high-latency 
share like Dropbox or Google Drive. IIRC SQLite3 locks tables not rows, which 
is a serious limitation for multi-user uses.

Regards,
John Ralls

___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

Re: [GNC-dev] [GNC] mysql backend, second user (lock, for example)

[Craig Arno]

On 11/6/2018 4:19 AM, John Ralls wrote:
> We intend to convert the XML backend to loading a SQLite3 in-memory
> database at session startup so that we can query against it instead of
> the current QOFQuery. I’m still working out how to handle the
> transition and how to prioritize it relative to GObject->C++ in the
> core engine objects. 
Glad to hear this.  I didn't want to propose this fundamental change
being the "new kid" with outrageous ideas.
I hope this change will make the rest of the GnuCash code consistent. 
Being able to use a text editor on "optional" XML is a nice alternative
to SQLite for users who aren't comfortable with databases and trying to
correct "deleted date-posted element" internal type problems.

> I hadn’t considered doing that as a shim for a server-based DB. I’m not sure 
> that it would be a real benefit and it could get pretty ugly to implement. I 
> think the shared SQLite3 file will work OK with a low-latency LAN file share 
> (e.g. NFS or SMB), but I’m also pretty sure that it won’t for a high-latency 
> share like Dropbox or Google Drive.
I'm mostly interested in the Usecase where a user is configured to use a
Server based database as their backend, then is disconnected from the
network and wants to keep working against the same database.  For me
this is usually travel and while Internet may be unavailable (might be
quite expensive, unreliable, and insecure).  Usually this means I have
dead time which can be used to catch up on paperwork as long as I'm not
the one piloting or driving our conveyance, or just wanting to look out
the window while I collect my thoughts.  This feature is what makes me
prefer GIT over SVN even though in reality I currently have to use
both.  What I'm trying to get away from is the SVN model where if there
is no server, no work can be performed.  GIT gets around this with a
local DB for work and push/pull for server updates.  I'm hoping for
something a little more automated to hide GnuCash internals from the
user.  GnuCash I suspect isn't operated by a highly technical audience,
as GIT is.  Details of working with local and remote databases could get
quite confusing for most users who's experience goes as far as
"thumbdrive" and "WiFi".

>  IIRC SQLite3 locks tables not rows, which is a serious limitation for 
> multi-user uses
Good to know, thanks, but you also have all the SQLite code running on
the local machine for "peer" access which means you have the potential
to do pretty much anything you need.  Does ODB provide compensation for
individual record locking locally for SQLite?

Otherwise individual record locking could be provided in a local API
expander "peer helper" class which communicates with SQLite for local
record updates between ODB and SQLite.  Then if SQLite should later
implement record locking as part of their API this "helper" class could
be depreciated/removed.  This will help the ODB interface remain
consistent to GnuCash architecture, unless ODB already has a way to work
around the lack of individual record locking in SQLite v3.

It might be worth asking the SQLite team for a record locking API.  They
may tell you it's already on their roadmap, or ask "why" it should be? 
If asked "why", then you can share the peer-to-peer Usecase scenario.

___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

Re: [GNC-dev] gnucash maint: Bug 795080 - Some dates reset to 01/01/1970

[Geert Janssens]

Op vrijdag 2 november 2018 19:28:40 CET schreef John Ralls:
> > On Nov 2, 2018, at 11:10 AM, Geert Janssens 
> > wrote:> 
> > Op vrijdag 2 november 2018 18:45:52 CET schreef John Ralls:
> >> --- a/po/POTFILES.in
> >> +++ b/po/POTFILES.in
> >> @@ -616,6 +616,7 @@ libgnucash/core-utils/gnc-locale-utils.c
> >> libgnucash/core-utils/gnc-path.c
> >> libgnucash/core-utils/gnc-prefs.c
> >> libgnucash/doc/doxygen_main_page.c
> >> +libgnucash/engine/.#gnc-datetime.cpp
> > 
> > What is this file and does it have translatable strings ? Or did an
> > editor's swap file sneak in here ?
> 
> That's an emacs artifact of some sort. It seems to be a symlink that exists
> when there are unsaved changes in the file.
> 
> I've removed it from POTFILES.in, but what are we doing wrong in building it
> that it's picking up hidden files?

I have updated the POTFILES.in generation code to skip hidden files.

Geert


___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

Re: [GNC-dev] Convert Imap to Flat

[Geert Janssens]

Op maandag 5 november 2018 23:27:31 CET schreef John Ralls:
> > On Nov 6, 2018, at 2:50 AM, Geert Janssens 
> > wrote:> 
> > Op maandag 5 november 2018 17:07:25 CET schreef Robert Fewell:
> >> Hi,
> >> I was poking around with the CSV importer and I noticed the following,
> >> this
> >> may also be an issue with other importers on first use after creating a
> >> new
> >> file...
> >> With a new empty xml file, I used a one line transaction csv file with
> >> appropriate settings and the 'Account' set to 'Assets:Current
> >> Assets:Checking Account' and observed the following when I got to the
> >> match
> >> page...
> >> 
> >> With the 'Checking Account' register open it would partly show the
> >> imported
> >> transactions, (this can be fixed by suspending GUI changes which I was
> >> going to propose in a PR) and the register would reload seven times.
> >> This reloading is caused by the triggering of the
> >> 'imap_convert_bayes_to_flat' function and as it is a new file you would
> >> not
> >> expect it to do any thing but it does. Adding a few print statements I
> >> get
> >> the following...
> >> 
> >> matchmap_find_destination
> >> imap_convert_bayes_to_flat
> >> convert_imap_account_bayes_to_flat 'Assets'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Current Assets'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Checking Account'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Liabilities'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Income'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Expenses'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> convert_imap_account_bayes_to_flat 'Equity'
> >> 
> >>  gnc_split_register_load called for account 'Assets:Current
> >> 
> >> Assets:Checking Account' with list of 1
> >> 
> >> As you can see, seven accounts get updated forcing the register reload
> >> seven times, (not sure why those other accounts force a reload either),
> >> and
> >> this gets even worse if this first import is 100 transactions which would
> >> equate to 700 reloads. I have not worked out why all these accounts are
> >> updated or why after the first pass the converted flag is not set/noticed
> >> there by eliminating the convert for the rest of the transactions, it
> >> only
> >> seems to be noticed on subsequent imports.
> >> 
> >> You also get this behaviour if you start the 'Import Map Dialogue' which
> >> may be the source of a report about that dialogue freezing but that needs
> >> more investigating.
> > 
> > This calls gnc_account_imap_get_info_bayes, which also calls
> > imap_convert_bayes_to_flat so the it will trigger the same account
> > refreshes.> 
> >> Any idea why these accounts are updated and why it runs on every import
> >> transaction row ?
> > 
> > Why the accounts are updated: while only a run in the debugger will verify
> > it, this is what I have gathered from reading the code:
> > 
> > imap_convert_bayes_to_flat's sub functions will call xaccAccountBeginEdit
> > and xaccAccountCommitEdit at some point. This happens because it changes
> > the account's kvp frames that store the import maps.
> > 
> > On the other side, the register code has set a watch on the register's
> > account(s) via the component manager. So each time the account signals a
> > change (or more precisely a successful run of xaccAccountCommitEdit) the
> > component manager will tell the register to refresh itself.
> > 
> > As you suggest you can probably disable this by a call to
> > gnc_suspend_gui_refresh.
> > 
> > Why it runs on every import transaction row ? I suspect this is because
> > there are no imap records stored yet and hence the feature flag that
> > blocks the conversion is not set yet. So for each transaction it will try
> > to do the conversion, find there's no converted imap record to store and
> > skip setting the feature flag. This will probably continue forever if the
> > user doesn't use bayesian matching at all.
> > This is a difficult issue to solve. We don't want to set the flat_bayes
> > conversion flag if there are no bayes maps because that would needlessly
> > break backwards compatibility. We could make the conversion code more
> > careful and have it only commit to accounts if there really are changes
> > to commit. And add a run time flag that signals the conversion has run
> > already once. With that conversion should

Re: [GNC-dev] [GNC] mysql backend, second user (lock, for example)

[John Ralls]

> On Nov 7, 2018, at 3:16 AM, Craig Arno  wrote:
> 
> On 11/6/2018 4:19 AM, John Ralls wrote:
>> We intend to convert the XML backend to loading a SQLite3 in-memory database 
>> at session startup so that we can query against it instead of the current 
>> QOFQuery. I’m still working out how to handle the transition and how to 
>> prioritize it relative to GObject->C++ in the core engine objects.
> Glad to hear this.  I didn't want to propose this fundamental change being 
> the "new kid" with outrageous ideas.
> I hope this change will make the rest of the GnuCash code consistent.  Being 
> able to use a text editor on "optional" XML is a nice alternative to SQLite 
> for users who aren't comfortable with databases and trying to correct 
> "deleted date-posted element" internal type problems.
> 
>> I hadn’t considered doing that as a shim for a server-based DB. I’m not sure 
>> that it would be a real benefit and it could get pretty ugly to implement. I 
>> think the shared SQLite3 file will work OK with a low-latency LAN file share 
>> (e.g. NFS or SMB), but I’m also pretty sure that it won’t for a high-latency 
>> share like Dropbox or Google Drive.
> I'm mostly interested in the Usecase where a user is configured to use a 
> Server based database as their backend, then is disconnected from the network 
> and wants to keep working against the same database.  For me this is usually 
> travel and while Internet may be unavailable (might be quite expensive, 
> unreliable, and insecure).  Usually this means I have dead time which can be 
> used to catch up on paperwork as long as I'm not the one piloting or driving 
> our conveyance, or just wanting to look out the window while I collect my 
> thoughts.  This feature is what makes me prefer GIT over SVN even though in 
> reality I currently have to use both.  What I'm trying to get away from is 
> the SVN model where if there is no server, no work can be performed.  GIT 
> gets around this with a local DB for work and push/pull for server updates.  
> I'm hoping for something a little more automated to hide GnuCash internals 
> from the user.  GnuCash I suspect isn't operated by a highly technical 
> audience, as GIT is.  Details of working with local and remote databases 
> could get quite confusing for most users who's experience goes as far as 
> "thumbdrive" and "WiFi".
> 
>>  IIRC SQLite3 locks tables not rows, which is a serious limitation for 
>> multi-user uses
> Good to know, thanks, but you also have all the SQLite code running on the 
> local machine for "peer" access which means you have the potential to do 
> pretty much anything you need.  Does ODB provide compensation for individual 
> record locking locally for SQLite?
> 
> Otherwise individual record locking could be provided in a local API expander 
> "peer helper" class which communicates with SQLite for local record updates 
> between ODB and SQLite.  Then if SQLite should later implement record locking 
> as part of their API this "helper" class could be depreciated/removed.  This 
> will help the ODB interface remain consistent to GnuCash architecture, unless 
> ODB already has a way to work around the lack of individual record locking in 
> SQLite v3.
> 
> It might be worth asking the SQLite team for a record locking API.  They may 
> tell you it's already on their roadmap, or ask "why" it should be?  If asked 
> "why", then you can share the peer-to-peer Usecase scenario.
> 

Read about SQLite3 locking: https://www.sqlite.org/lockingv3.html 
. They’re locking virtual memory pages, 
totally independent of table or record structure. In practice what that means 
is that at the application level only using the SQL Transaction API makes 
sense, the application doesn’t have enough visibility of the internals to be 
able to implement finer-grained controls.

Your use-case for a SQLite3 shim reminds me of IBM’s old Remote Job Entry. It 
won’t work because someone else might change the server database while you’re 
working offline and unlike git SQL has no conflict resolution facility. 
Individual database servers often have a replication facility (I’m pretty sure 
Postgres does, MySQLs depends on the database backend--ISAM no, Berkeley DB 
yes, InnoDB don’t know) that can do limited conflict resolution, but it’s 
completely internal to the database engine. It won’t work between engines. In 
fact the only way I know of to transfer between engines is to dump SQL 
statements out of one and play them back into the other.

I think you’re expecting too much by calling a shared SQLite3 file a “peer to 
peer scenario”. It’s shared-file IPC. As I said before, it will work in a 
low-latency situation but will fail in a high-latency one like DropBox where 
file changes can take several seconds to propagate.

Regards,
John Ralls


___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/

Re: [GNC-dev] gnucash maint: Bug 795080 - Some dates reset to 01/01/1970

[John Ralls]

> On Nov 7, 2018, at 5:41 AM, Geert Janssens  wrote:
> 
> Op vrijdag 2 november 2018 19:28:40 CET schreef John Ralls:
>>> On Nov 2, 2018, at 11:10 AM, Geert Janssens 
>>> wrote:> 
>>> Op vrijdag 2 november 2018 18:45:52 CET schreef John Ralls:
 --- a/po/POTFILES.in
 +++ b/po/POTFILES.in
 @@ -616,6 +616,7 @@ libgnucash/core-utils/gnc-locale-utils.c
 libgnucash/core-utils/gnc-path.c
 libgnucash/core-utils/gnc-prefs.c
 libgnucash/doc/doxygen_main_page.c
 +libgnucash/engine/.#gnc-datetime.cpp
>>> 
>>> What is this file and does it have translatable strings ? Or did an
>>> editor's swap file sneak in here ?
>> 
>> That's an emacs artifact of some sort. It seems to be a symlink that exists
>> when there are unsaved changes in the file.
>> 
>> I've removed it from POTFILES.in, but what are we doing wrong in building it
>> that it's picking up hidden files?
> 
> I have updated the POTFILES.in generation code to skip hidden files.

Thanks.

Regards,
John Ralls

___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

Re: [GNC-dev] Convert Imap to Flat

[John Ralls]

> On Nov 7, 2018, at 5:52 AM, Geert Janssens  wrote:
> 
> Op maandag 5 november 2018 23:27:31 CET schreef John Ralls:
>>> On Nov 6, 2018, at 2:50 AM, Geert Janssens 
>>> wrote:> 
>>> Op maandag 5 november 2018 17:07:25 CET schreef Robert Fewell:
 Hi,
 I was poking around with the CSV importer and I noticed the following,
 this
 may also be an issue with other importers on first use after creating a
 new
 file...
 With a new empty xml file, I used a one line transaction csv file with
 appropriate settings and the 'Account' set to 'Assets:Current
 Assets:Checking Account' and observed the following when I got to the
 match
 page...
 
 With the 'Checking Account' register open it would partly show the
 imported
 transactions, (this can be fixed by suspending GUI changes which I was
 going to propose in a PR) and the register would reload seven times.
 This reloading is caused by the triggering of the
 'imap_convert_bayes_to_flat' function and as it is a new file you would
 not
 expect it to do any thing but it does. Adding a few print statements I
 get
 the following...
 
 matchmap_find_destination
 imap_convert_bayes_to_flat
 convert_imap_account_bayes_to_flat 'Assets'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Current Assets'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Checking Account'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Liabilities'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Income'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Expenses'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 convert_imap_account_bayes_to_flat 'Equity'
 
 gnc_split_register_load called for account 'Assets:Current
 
 Assets:Checking Account' with list of 1
 
 As you can see, seven accounts get updated forcing the register reload
 seven times, (not sure why those other accounts force a reload either),
 and
 this gets even worse if this first import is 100 transactions which would
 equate to 700 reloads. I have not worked out why all these accounts are
 updated or why after the first pass the converted flag is not set/noticed
 there by eliminating the convert for the rest of the transactions, it
 only
 seems to be noticed on subsequent imports.
 
 You also get this behaviour if you start the 'Import Map Dialogue' which
 may be the source of a report about that dialogue freezing but that needs
 more investigating.
>>> 
>>> This calls gnc_account_imap_get_info_bayes, which also calls
>>> imap_convert_bayes_to_flat so the it will trigger the same account
>>> refreshes.> 
 Any idea why these accounts are updated and why it runs on every import
 transaction row ?
>>> 
>>> Why the accounts are updated: while only a run in the debugger will verify
>>> it, this is what I have gathered from reading the code:
>>> 
>>> imap_convert_bayes_to_flat's sub functions will call xaccAccountBeginEdit
>>> and xaccAccountCommitEdit at some point. This happens because it changes
>>> the account's kvp frames that store the import maps.
>>> 
>>> On the other side, the register code has set a watch on the register's
>>> account(s) via the component manager. So each time the account signals a
>>> change (or more precisely a successful run of xaccAccountCommitEdit) the
>>> component manager will tell the register to refresh itself.
>>> 
>>> As you suggest you can probably disable this by a call to
>>> gnc_suspend_gui_refresh.
>>> 
>>> Why it runs on every import transaction row ? I suspect this is because
>>> there are no imap records stored yet and hence the feature flag that
>>> blocks the conversion is not set yet. So for each transaction it will try
>>> to do the conversion, find there's no converted imap record to store and
>>> skip setting the feature flag. This will probably continue forever if the
>>> user doesn't use bayesian matching at all.
>>> This is a difficult issue to solve. We don't want to set the flat_bayes
>>> conversion flag if there are no bayes maps because that would needlessly
>>> break backwards compatibility. We could make the conversion code more
>>> careful and have it only commit to accounts if there really are changes
>>> to commit. And add a run time flag that signals the conversion