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

classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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

craigarno
On 11/7/2018 1:42 PM, Craig Arno wrote:
> Using a queue has other benefits if you find yourself in a situation...

For the "offline" update usecase where the primary database is "server"
based, a local SQLite database could be used for local offline work
(travel receipt entry), which the database Event Write Queue could be
tee'd to a file for later "online" update replay.

When "online" status to the remote server based database is restored,
the "remote write queue" could be played to update the remote/server
database.  This would have the net effect of "mirroring" the remote
database transactions locally for one user's offline work.

In the read direction, a query for all database table entries between
the last successful update/connect and now will have to be performed and
the local database updated with new changes from
User/Bookkeeper/Accountant activity.  If there are a "lot" of changes
and online time is short or connection speed slow, and potential for
interruption high, it might be best to queue up read database changes to
disk until the database read "diff" is complete for all tables.  Then
the "read" half of the Sync can then be completed with local disk based
"complete" diff data.  And give the user a little arrow chasing arrow
button to do a "refresh" compare between local and remote databases, if
sync is ever in doubt.  The "arrow chasing arrow button" is consistent
with how remote calendar/contact based office information systems
operate today.  I do this with local machine Thunderbird Calendar and my
OwnCloud server based system.
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

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

craigarno
In reply to this post by John Ralls
On 11/7/2018 3:43 PM, John Ralls wrote:
> Always keep GnuCash’s target audience (Personal/Small Business) in
> mind: We’re definitely not designing for “heavy database updates”.

"heavy database updates" can be caused in a Personal/Small Business
application when two asynchronous events arrive at the same time from a
"second user".  This can also happen if an Offline->Online transaction
replay/sync occurs, especially if a "second user" is working/involved
during replay.
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

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

John Ralls


> On Nov 8, 2018, at 9:00 AM, Craig Arno <[hidden email]> wrote:
>
> On 11/7/2018 3:43 PM, John Ralls wrote:
>> Always keep GnuCash’s target audience (Personal/Small Business) in mind: We’re definitely not designing for “heavy database updates”.
>
> "heavy database updates" can be caused in a Personal/Small Business application when two asynchronous events arrive at the same time from a "second user".  This can also happen if an Offline->Online transaction replay/sync occurs, especially if a "second user" is working/involved during replay.

Not my understanding of “heavy database updates” (which would be something like > 100K TPS), but OK.

Regards,
John Ralls

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

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

John Ralls
In reply to this post by craigarno


> On Nov 8, 2018, at 8:50 AM, Craig Arno <[hidden email]> wrote:
>
> On 11/7/2018 1:42 PM, Craig Arno wrote:
>> Using a queue has other benefits if you find yourself in a situation...
>
> For the "offline" update usecase where the primary database is "server" based, a local SQLite database could be used for local offline work (travel receipt entry), which the database Event Write Queue could be tee'd to a file for later "online" update replay.
>
> When "online" status to the remote server based database is restored, the "remote write queue" could be played to update the remote/server database.  This would have the net effect of "mirroring" the remote database transactions locally for one user's offline work.
>
> In the read direction, a query for all database table entries between the last successful update/connect and now will have to be performed and the local database updated with new changes from User/Bookkeeper/Accountant activity.  If there are a "lot" of changes and online time is short or connection speed slow, and potential for interruption high, it might be best to queue up read database changes to disk until the database read "diff" is complete for all tables.  Then the "read" half of the Sync can then be completed with local disk based "complete" diff data.  And give the user a little arrow chasing arrow button to do a "refresh" compare between local and remote databases, if sync is ever in doubt.  The "arrow chasing arrow button" is consistent with how remote calendar/contact based office information systems operate today.  I do this with local machine Thunderbird Calendar and my OwnCloud server based system.

That’s a lot more complex than any backend I’d want to implement, but fortunately GnuCash’s backends are plugins so you’re welcome to write a separate one.

Regards,
John Ralls

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

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

craigarno
In reply to this post by John Ralls
On 11/7/2018 4:14 PM, John Ralls wrote:
> Not my understanding of “heavy database updates” (which would be
> something like > 100K TPS), but OK.
Yeah, I may not be using the right terminology, but look at my
suggestions as generally correct if not using the right glossary.  In
the database arena I'm more of a semi-sophisticated user than a domain
expert.  I assume you are a domain expert, at least relative to my
database experience, an opportunity for me to learn.  What may be
confusing is I do have a lot of engineering experience.

In this case I considered processing two asynchronous events (second+
user) arriving at the same time, not raw processing throughput, a
different kind of performance.  Even the "sync data" proposal in a SOHO
environment shouldn't stress today's multi-core, gigabit memory
commodity computers for throughput in a SOHO environment.  I'm thinking
more race conditions caused by async (two+ user) events.  Still thinking
SOHO.

On 11/7/2018 4:16 PM, John Ralls wrote:
> That’s a lot more complex than any backend I’d want to implement, but
> fortunately GnuCash’s backends are plugins so you’re welcome to write
> a separate one.
Fair enough.  Hopefully architecture framework design decisions can
support this sort of future "plugin" expansion.  Guess I'd better look
for "plugin support documentation", see what I can figure out.
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

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

John Ralls


> On Nov 8, 2018, at 9:57 AM, Craig Arno <[hidden email]> wrote:
>
> On 11/7/2018 4:14 PM, John Ralls wrote:
>> Not my understanding of “heavy database updates” (which would be something like > 100K TPS), but OK.
> Yeah, I may not be using the right terminology, but look at my suggestions as generally correct if not using the right glossary.  In the database arena I'm more of a semi-sophisticated user than a domain expert.  I assume you are a domain expert, at least relative to my database experience, an opportunity for me to learn.  What may be confusing is I do have a lot of engineering experience.

I’m nowhere near an expert on multiuser database work, but I have whacked at it a bit over the years.

>
> In this case I considered processing two asynchronous events (second+ user) arriving at the same time, not raw processing throughput, a different kind of performance.  Even the "sync data" proposal in a SOHO environment shouldn't stress today's multi-core, gigabit memory commodity computers for throughput in a SOHO environment.  I'm thinking more race conditions caused by async (two+ user) events.  Still thinking SOHO.

That’s just simple concurrency. The part that may be outside of your experience is that it’s concurrency not just between different processes but between different computers and in your offline use-case it’s perhaps hard to recognize that “concurrent” doesn’t necessarily mean "at the same time”, it just means that there are potentially multiple updates of the same record on the two disconnected instances that need to be resolved somehow.

>
> On 11/7/2018 4:16 PM, John Ralls wrote:
>> That’s a lot more complex than any backend I’d want to implement, but fortunately GnuCash’s backends are plugins so you’re welcome to write a separate one.
> Fair enough.  Hopefully architecture framework design decisions can support this sort of future "plugin" expansion.  Guess I'd better look for "plugin support documentation", see what I can figure out.

Unfortunately there isn’t any good documentation of how to write a plugin. There’s some API documentation at https://code.gnucash.org/docs/MAINT/group__Backend.html <https://code.gnucash.org/docs/MAINT/group__Backend.html> and https://code.gnucash.org/docs/MAINT/group__Object__Private.html, but there’s not much detail and there’s no tutorial. You’ll need to study the code in libgnucash/engine/qof-backend.cpp and libgnucash/backend/dbi to see how to register your new backend.

Regrds,
John Ralls

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
12