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)

Geert Janssens-4
Op maandag 5 november 2018 08:36:25 CET schreef craigarno:

> Geert Janssens-4 wrote
>
> > Op zondag 4 november 2018 14:49:22 CET schreef craigarno:
> >> Geert Janssens-4 wrote
> >
> > Whether it can work depends on
> > whether the db layer we rely on has a notification mechanism for db
> > changes we
> > can hook into. The current db layer is provided by libdbi, which is not
> > managed by the gnucash team and I haven't looked into this yet.
>
> If your call to libdbi is in the same thread and is a blocking call for
> synchronous operation, then there shouldn't be a problem with updating views
> after successful return from the libdbi call.  Otherwise you may have to
> look for semaphors to signal.

This discussion really belongs on gnucash-devel. It's way to technical for the
gnucash-user list. So I have moved it, please follow up on the gnucash-devel
list.


It looks like we are having a different idea of the network model gnucash will
use. I'm getting the feeling your idea is that gnucash will turn into a
client-server model, with the server part running on a server and a  client
interacting with this server. This is not the current design idea.

The current plan is to have a desktop application that can connect to a
database using database semantics (row level locking, ACID support). But there
will be no gnucash server component as such. That database can be accessible
over the network from different gnucash programs installed on different
computers (and ideally devices). But that's where our current plan ends. And
in this design I don't think there's an easy way to manage coordinated view
updating across devices. I don't think you can have for example mysql signal
changes to a client application (though I may be mistaken). So while the
design will allow concurrent db access and modification, it will not allow
keeping views in sync without active db polling.

But perhaps if we get to the point where the core functionality is separated
in it's own library, it should at least be easier for someone to write a
client server system on top of that.

Regards,

Geert


_______________________________________________
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
Geert,

I just subscribed to the gnucash-devel mailing list.  Where do I find
the list so I can find and respond to your message?  I also need to
respond to Phil's message, in gnucash-devel.

Short answer, no we do not have different ideas.  I need to help you
understand my answer.  Looks like Phil is sharing the same confusion as
you, so must have been my wording.  I'd be happy to straighten this out
in gnucash-devel as soon as I can figure out how to get there, and spare
all the good users here from technobabble.

Craig


On 11/5/2018 1:49 AM, Geert Janssens wrote:

> Op maandag 5 november 2018 08:36:25 CET schreef craigarno:
>> Geert Janssens-4 wrote
>>
>>> Op zondag 4 november 2018 14:49:22 CET schreef craigarno:
>>>> Geert Janssens-4 wrote
>>> Whether it can work depends on
>>> whether the db layer we rely on has a notification mechanism for db
>>> changes we
>>> can hook into. The current db layer is provided by libdbi, which is not
>>> managed by the gnucash team and I haven't looked into this yet.
>> If your call to libdbi is in the same thread and is a blocking call for
>> synchronous operation, then there shouldn't be a problem with updating views
>> after successful return from the libdbi call.  Otherwise you may have to
>> look for semaphors to signal.
> This discussion really belongs on gnucash-devel. It's way to technical for the
> gnucash-user list. So I have moved it, please follow up on the gnucash-devel
> list.
>
>
> It looks like we are having a different idea of the network model gnucash will
> use. I'm getting the feeling your idea is that gnucash will turn into a
> client-server model, with the server part running on a server and a  client
> interacting with this server. This is not the current design idea.
>
> The current plan is to have a desktop application that can connect to a
> database using database semantics (row level locking, ACID support). But there
> will be no gnucash server component as such. That database can be accessible
> over the network from different gnucash programs installed on different
> computers (and ideally devices). But that's where our current plan ends. And
> in this design I don't think there's an easy way to manage coordinated view
> updating across devices. I don't think you can have for example mysql signal
> changes to a client application (though I may be mistaken). So while the
> design will allow concurrent db access and modification, it will not allow
> keeping views in sync without active db polling.
>
> But perhaps if we get to the point where the core functionality is separated
> in it's own library, it should at least be easier for someone to write a
> client server system on top of that.
>
> Regards,
>
> Geert
>

_______________________________________________
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)

Geert Janssens-4
Op maandag 5 november 2018 20:16:04 CET schreef Craig Arno:

> Geert,
>
> I just subscribed to the gnucash-devel mailing list.  Where do I find
> the list so I can find and respond to your message?  I also need to
> respond to Phil's message, in gnucash-devel.
>
> Short answer, no we do not have different ideas.  I need to help you
> understand my answer.  Looks like Phil is sharing the same confusion as
> you, so must have been my wording.  I'd be happy to straighten this out
> in gnucash-devel as soon as I can figure out how to get there, and spare
> all the good users here from technobabble.
>
> Craig

You have found us :)

Just reply to this e-mail message (use reply-to-all or reply-to-list in your
mail client) and your answer will appear on gnucash-devel.

Regards,

Geert

>
> On 11/5/2018 1:49 AM, Geert Janssens wrote:
> > Op maandag 5 november 2018 08:36:25 CET schreef craigarno:
> >> Geert Janssens-4 wrote
> >>
> >>> Op zondag 4 november 2018 14:49:22 CET schreef craigarno:
> >>>> Geert Janssens-4 wrote
> >>>
> >>> Whether it can work depends on
> >>> whether the db layer we rely on has a notification mechanism for db
> >>> changes we
> >>> can hook into. The current db layer is provided by libdbi, which is not
> >>> managed by the gnucash team and I haven't looked into this yet.
> >>
> >> If your call to libdbi is in the same thread and is a blocking call for
> >> synchronous operation, then there shouldn't be a problem with updating
> >> views after successful return from the libdbi call.  Otherwise you may
> >> have to look for semaphors to signal.
> >
> > This discussion really belongs on gnucash-devel. It's way to technical for
> > the gnucash-user list. So I have moved it, please follow up on the
> > gnucash-devel list.
> >
> >
> > It looks like we are having a different idea of the network model gnucash
> > will use. I'm getting the feeling your idea is that gnucash will turn
> > into a client-server model, with the server part running on a server and
> > a  client interacting with this server. This is not the current design
> > idea.
> >
> > The current plan is to have a desktop application that can connect to a
> > database using database semantics (row level locking, ACID support). But
> > there will be no gnucash server component as such. That database can be
> > accessible over the network from different gnucash programs installed on
> > different computers (and ideally devices). But that's where our current
> > plan ends. And in this design I don't think there's an easy way to manage
> > coordinated view updating across devices. I don't think you can have for
> > example mysql signal changes to a client application (though I may be
> > mistaken). So while the design will allow concurrent db access and
> > modification, it will not allow keeping views in sync without active db
> > polling.
> >
> > But perhaps if we get to the point where the core functionality is
> > separated in it's own library, it should at least be easier for someone
> > to write a client server system on top of that.
> >
> > Regards,
> >
> > Geert




_______________________________________________
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 Geert Janssens-4
Ok, think I see how GnuCash User/devel lists are organized, think I'm on dev
right now???

Phil's point is a valid one, Observer pattern works fine in local code on
the local machine, but how do you get SQL database notification of an update
to "trigger" an update cycle in other remote machines so a brute force
polling mechanism isn't required?  i.e. don't want to burn up expensive
internet data/bandwidth and potentially suffer poor gnucash performance due
to "modem link" type network latency/speeds from a machine which is
potentially half way around the world in a hotel room in India with poor
connectivity issues (better be TCP for error detection/correction)?

Maybe  this link
<https://solutioncenter.apexsql.com/get-an-alert-when-a-certain-record-changes/>  
will provide clues?
https://solutioncenter.apexsql.com/get-an-alert-when-a-certain-record-changes/

Otherwise, no as a user, I don't want additional server software and
complication.  This should be done completely within the GnuCash application
with nothing more than a stock database (MySQL/MariaDB) in a LAMP setup (and
maybe Windows), and stock libdbi API's.

Craig



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
_______________________________________________
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
And here is an example for PostgreSQL
https://www.postgresql.org/docs/9.6/trigger-example.html



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
_______________________________________________
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 craigarno
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 craigarno
Here is an outline for high performance record lock/update/release activity,
"Consumer/Producer" model.
https://www.xaprb.com/blog/2007/08/29/how-to-notify-event-listeners-in-mysql/

Good discussion of the problems surrounding multiple source remote data
updates, which definitely will go along with this "second user" discussion.
We don't want to introduce "windows of vulnerability" in a
multi-user/multi-thread situation.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
_______________________________________________
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)

Phil Longstaff-5
In reply to this post by craigarno
I would assume postgresql and mysql would be more likely to provide this
kind of notification because they have a central server. sqlite does not.
I'm not sure how it could have 2 instances notify each other if they are
just accessing the same sqlite file.

On Mon, Nov 5, 2018 at 3:47 PM craigarno <[hidden email]> wrote:

> Here is an example for MySQL
> http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx
>
>
>
> --
> Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
> _______________________________________________
> gnucash-devel mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>
_______________________________________________
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
Isn't SQLite for "single" application use?  Or are you thinking GnuCash will
be operating "peer-to-peer" (even more interesting thought for tiny
organizations)?

Maybe conditional code with SQLite being a third database type.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
_______________________________________________
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 Phil Longstaff-5
SQLite "trigger" example:

http://www.sqlitetutorial.net/sqlite-trigger/





--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html
_______________________________________________
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 6, 2018, at 6:18 AM, craigarno <[hidden email]> wrote:
>
> Isn't SQLite for "single" application use?  Or are you thinking GnuCash will
> be operating "peer-to-peer" (even more interesting thought for tiny
> organizations)?
>
> Maybe conditional code with SQLite being a third database type.

To answer Phil’s original question, no, libdbi doesn’t support triggers.

I’m not at all fond of libdbi and I’d like to switch to a better abstraction library. The last time I looked ODB (https://www.codesynthesis.com/products/odb/ <https://www.codesynthesis.com/products/odb/>) appeared to be the most capable and I think that it does support triggers.

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
I like John's idea,

Here is potentially another big group of GnuCash customers/users,
Microsoft product users (also supported by ODB)

Microsoft SQL Server (MSDN Trigger documentation)
https://msdn.microsoft.com/en-us/library/sdk3bcyw.aspx?f=255&MSPPError=-2147217396

Craig

On 11/5/2018 2:08 PM, John Ralls wrote:

>
>
>> On Nov 6, 2018, at 6:18 AM, craigarno <[hidden email]
>> <mailto:[hidden email]>> wrote:
>>
>> Isn't SQLite for "single" application use?  Or are you thinking
>> GnuCash will
>> be operating "peer-to-peer" (even more interesting thought for tiny
>> organizations)?
>>
>> Maybe conditional code with SQLite being a third database type.
>
> To answer Phil’s original question, no, libdbi doesn’t support triggers. 
>
> I’m not at all fond of libdbi and I’d like to switch to a better
> abstraction library. The last time I looked ODB
> (https://www.codesynthesis.com/products/odb/) appeared to be the most
> capable and I think that it does support triggers.
>
> 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


> On Nov 6, 2018, at 7:19 AM, Craig Arno <[hidden email]> wrote:
>
> I like John's idea,
>
> Here is potentially another big group of GnuCash customers/users, Microsoft product users (also supported by ODB)
>
> Microsoft SQL Server (MSDN Trigger documentation)
> https://msdn.microsoft.com/en-us/library/sdk3bcyw.aspx?f=255&MSPPError=-2147217396 <https://msdn.microsoft.com/en-us/library/sdk3bcyw.aspx?f=255&MSPPError=-2147217396>

SQL Server isn’t Free Software, so we won’t support using it. Same for Oracle, DB2, and any other commercial database engines.

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
Got it, and noted.  "Won't support" isn't the same as "won't work" (user
beware).  But I get not putting free effort into a product someone else
profits from.

On 11/5/2018 2:30 PM, John Ralls wrote:
> SQL Server isn’t Free Software, so we won’t support using it. Same for
> Oracle, DB2, and any other commercial database engines.
_______________________________________________
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 Phil Longstaff-5
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
    <https://www.sqlite.org/fileformat.html>.  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
    <https://www.sqlite.org/locrsf.html> 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
    <https://www.sqlite.org/transactional.html>.  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
    <https://www.sqlite.org/testing.html>.  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 <https://www.sqlite.org/zeroconf.html>. 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
    <https://www.sqlite.org/fasterthanfs.html>
 8. and of course, SQLite is cross platform

I found a non-commercial Windows ODBC driver
<http://www.ch-werner.de/sqliteodbc/> 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.

Craig


On 11/5/2018 1:06 PM, Phil Longstaff wrote:
> I would assume postgresql and mysql would be more likely to provide
> this kind of notification because they have a central server. sqlite
> does not. I'm not sure how it could have 2 instances notify each other
> if they are just accessing the same sqlite file.
_______________________________________________
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 6, 2018, at 4:47 PM, Craig Arno <[hidden email]> 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
>    <https://www.sqlite.org/fileformat.html>.  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
>    <https://www.sqlite.org/locrsf.html> 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
>    <https://www.sqlite.org/transactional.html>.  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
>    <https://www.sqlite.org/testing.html>.  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 <https://www.sqlite.org/zeroconf.html>. 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
>    <https://www.sqlite.org/fasterthanfs.html>
> 8. and of course, SQLite is cross platform
>
> I found a non-commercial Windows ODBC driver
> <http://www.ch-werner.de/sqliteodbc/> 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
[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
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
[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 7, 2018, at 3:16 AM, Craig Arno <[hidden email]> 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 <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
[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
On 11/6/2018 3:54 PM, John Ralls wrote:
> 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.

Then record locking may not be the best approach to solving the
multi-source SQLite database issue.  For asynchronous concurrency caused
by peer or multi-thread database request input sources either an Event
Queue or Message Queue design pattern could be built as a serializing
front-end for database transactions.

Using a queue has other benefits if you find yourself in a situation
where there are heavy database updates.  As a for instance "Write
updates" could be given priority over "Read requests" so Read Requests
can be assured of returning the latest and most relevant results.  For
SQLite this might be because the user put their database on a slow
device, like a USB stick, or as you suggested earlier, DropBox.

Interesting read on SQLite record locking.  I had no idea.  Thanks!

Craig
_______________________________________________
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 6:42 AM, Craig Arno <[hidden email]> wrote:
>
> On 11/6/2018 3:54 PM, John Ralls wrote:
>> Read about SQLite3 locking: https://www.sqlite.org/lockingv3.html <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.
>
> Then record locking may not be the best approach to solving the multi-source SQLite database issue.  For asynchronous concurrency caused by peer or multi-thread database request input sources either an Event Queue or Message Queue design pattern could be built as a serializing front-end for database transactions.
>
> Using a queue has other benefits if you find yourself in a situation where there are heavy database updates.  As a for instance "Write updates" could be given priority over "Read requests" so Read Requests can be assured of returning the latest and most relevant results.  For SQLite this might be because the user put their database on a slow device, like a USB stick, or as you suggested earlier, DropBox.
>
> Interesting read on SQLite record locking.  I had no idea.  Thanks!

Always keep GnuCash’s target audience (Personal/Small Business) in mind: We’re definitely not designing for “heavy database updates”. Any business that needs an auditor won’t be allowed to use GnuCash because of the complete absence of internal controls. In order to use a queue serialization model we’d have to disable update queries and allow only inserts for the offline session. Remember, no conflict resolution: An update query just overwrites the record, there’s no check to see if the old value is what was expected. In GnuCash terms that means that when you click on an existing transaction in the register or open an Edit Foo dialog box the session needs to acquire an exclusive lock on the record *in the database* and hold it until you exit the transaction or close the dialog box.

Regards,
John Ralls

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