SQLite Application Server Concurrency

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Application Server Concurrency

James Colehan
Hi,

I am looking for some advice on using SQLite for my given situation.

My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.

My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.

Any thoughts would be appreciated.

Regards,

James


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

Simon Slavin-3
On 11 Jan 2018, at 9:06am, James Colehan <[hidden email]> wrote:

> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

Can you set up a web-facing interface to do this ?  Do you know web programming ?

It can be done in many langauges.  A PHP backend which queries the database and makes up a web page with the results is a very common way of doing it.  But you can use node.js instead.

The idea is that instead of querying the database directly, the remote computers pass the query to the host computer.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

James Colehan
Hi Simon,

Thanks for getting back so quickly.

Web programming is not something I am familiar with. My application is desktop based mostly written in C#.

However, your suggestion is something I can look deeper into.

James

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: 11 January 2018 09:13
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLite Application Server Concurrency

On 11 Jan 2018, at 9:06am, James Colehan <[hidden email]> wrote:

> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

Can you set up a web-facing interface to do this ?  Do you know web programming ?

It can be done in many langauges.  A PHP backend which queries the database and makes up a web page with the results is a very common way of doing it.  But you can use node.js instead.

The idea is that instead of querying the database directly, the remote computers pass the query to the host computer.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite Application Server Concurrency

Hick Gunter
In reply to this post by James Colehan
Accessing SQLite files via network filesystems is high on the list of "how to corrupt SQLite databases", see section 2.1 in http://sqlite.org/howtocorrupt.html

WAL mode requires shared memory. How are you going to do that with processes running on different PCs? See sections 4 and 6 of http://sqlite.org/wal.html.



-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von James Colehan
Gesendet: Donnerstag, 11. Jänner 2018 10:07
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] SQLite Application Server Concurrency

Hi,

I am looking for some advice on using SQLite for my given situation.

My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.

My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.

Any thoughts would be appreciated.

Regards,

James


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLite Application Server Concurrency

James Colehan
Thanks Hick, points noted.

James

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: 11 January 2018 09:23
To: 'SQLite mailing list' <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] SQLite Application Server Concurrency

Accessing SQLite files via network filesystems is high on the list of "how to corrupt SQLite databases", see section 2.1 in http://sqlite.org/howtocorrupt.html

WAL mode requires shared memory. How are you going to do that with processes running on different PCs? See sections 4 and 6 of http://sqlite.org/wal.html.



-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von James Colehan
Gesendet: Donnerstag, 11. Jänner 2018 10:07
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] SQLite Application Server Concurrency

Hi,

I am looking for some advice on using SQLite for my given situation.

My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.

My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.

Any thoughts would be appreciated.

Regards,

James


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

R Smith-2
In reply to this post by James Colehan

On 2018/01/11 11:06 AM, James Colehan wrote:
> Hi,
>
> I am looking for some advice on using SQLite for my given situation.
>
> My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.
>
> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.
>
> At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.

If only basic query (reading) is involved, this should be fine. though
not sure if WAL mode is best (but haven't tested it, so won't offer an
opinion), BUT, as soon as you do a transaction or a SELECT query that
takes a while to complete, chances are the locks may not kick in and the
query may read an inconsistent dataset if local writes happen
simultaneously. You have to determine how harmful that is to your
application.
It shouldn't cause corruption though, only writing over the network
should carry that risk.

Apart from Simon's web api idea, you can obviously simply make a
C/C#/C++/whatever service that connects to the DB on the local machine
and then allows your client applications to connect to it via pipes or
whatever you like, it's just that a php web service is a hundred times
easier to do (it handles all the connecty things for you).

I would be remiss not to mention that whether you do your own connector
service or a php web service, you are wandering into very high
effort-to-pleasure ratio territory, it's suddenly far far easier to run
a client-server database (MySQL is an easy one, Postgres is a good one,
MSSQL is... well, also one) on the local machine and port the tables and
queries.  There are even client-server things for SQLite available - a
quick google search would reveal, but I think they are mostly commercial
- money that would be well spent in the case that you have invested a
lot of time in thousands of sqlite queries and this porting to another
DB thing would be too much of a time drain.

Best of luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

John Found
In reply to this post by James Colehan
I have several such applications, running in industrial environment.

I always use a TCP server on 'X' and TCP client on 'Y' with some simple, specialized http-like protocol on top.

All this can be implemented very easy, especially if you don't need a top performance and millions of clients. In addition such solution allows to avoid complex settings with web servers, back-front ends etc. The server and the client are built in the desktop application and the communication is transparent for the users.

On Thu, 11 Jan 2018 09:06:32 +0000
James Colehan <[hidden email]> wrote:

> Hi,
>
> I am looking for some advice on using SQLite for my given situation.
>
> My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.
>
> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.
>
> At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.
>
> Any thoughts would be appreciated.
>
> Regards,
>
> James
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

Lee Gray
In reply to this post by James Colehan
Depending on your level of expertise with C#, you could create a .NET Windows service to run on machine X that contains a WCF service which controls the database access. The Y and Z clients would be WCF clients that talk to the WCF service on X. They could communicate via http or TCP.

From: sqlite-users [mailto:[hidden email]] On Behalf Of James Colehan
Sent: Thursday, January 11, 2018 3:21 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLite Application Server Concurrency

Hi Simon,

Thanks for getting back so quickly.

Web programming is not something I am familiar with. My application is desktop based mostly written in C#.

However, your suggestion is something I can look deeper into.

James

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: 11 January 2018 09:13
To: SQLite mailing list <[hidden email]<mailto:[hidden email]>>
Subject: Re: [sqlite] SQLite Application Server Concurrency

On 11 Jan 2018, at 9:06am, James Colehan <[hidden email]<mailto:[hidden email]>> wrote:

> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.

Can you set up a web-facing interface to do this ? Do you know web programming ?

It can be done in many langauges. A PHP backend which queries the database and makes up a web page with the results is a very common way of doing it. But you can use node.js instead.

The idea is that instead of querying the database directly, the remote computers pass the query to the host computer.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]<mailto:[hidden email]>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
_______________________________________________
sqlite-users mailing list
[hidden email]<mailto:[hidden email]>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

James Colehan
In reply to this post by R Smith-2
Thanks Ryan John and Lee.

Quite a few options. I will look at a 'server service' in C# using WCF as I am familiar with it. Also, investigate the MySQL avenue.

Much obliged for your help.

James

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: 11 January 2018 10:45
To: [hidden email]
Subject: Re: [sqlite] SQLite Application Server Concurrency


On 2018/01/11 11:06 AM, James Colehan wrote:
> Hi,
>
> I am looking for some advice on using SQLite for my given situation.
>
> My system involves a windows network. On PC 'X', I have an SQLite database with a process that handles amendment's to the database ie INSERTS, DELETES and UPDATES. Also, on PC 'X' I have some other processes running that are requesting data (SELECT) form the database. I have the Journal Mode set to WAL. This all appears to work fine.
>
> My concern is that I have a requirement to query the database on PC 'X' from other PC's. Basically, performing (SELECT) statements from stations 'Y' and 'Z'. This creates a client/server situation that I appreciate SQLite is not appropriate choice of database for. I hope doing the (SELECT) only from the client will be a workable solution.
>
> At the moment I have not encountered any problems. However, my concern is that this maybe just luck and there is a potential of locking issues or database corruption.

If only basic query (reading) is involved, this should be fine. though not sure if WAL mode is best (but haven't tested it, so won't offer an opinion), BUT, as soon as you do a transaction or a SELECT query that takes a while to complete, chances are the locks may not kick in and the query may read an inconsistent dataset if local writes happen simultaneously. You have to determine how harmful that is to your application.
It shouldn't cause corruption though, only writing over the network should carry that risk.

Apart from Simon's web api idea, you can obviously simply make a C/C#/C++/whatever service that connects to the DB on the local machine and then allows your client applications to connect to it via pipes or whatever you like, it's just that a php web service is a hundred times easier to do (it handles all the connecty things for you).

I would be remiss not to mention that whether you do your own connector service or a php web service, you are wandering into very high effort-to-pleasure ratio territory, it's suddenly far far easier to run a client-server database (MySQL is an easy one, Postgres is a good one, MSSQL is... well, also one) on the local machine and port the tables and queries.  There are even client-server things for SQLite available - a quick google search would reveal, but I think they are mostly commercial
- money that would be well spent in the case that you have invested a lot of time in thousands of sqlite queries and this porting to another DB thing would be too much of a time drain.

Best of luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Application Server Concurrency

Dinu
In reply to this post by James Colehan
James,
You don't need to go all the length to creating a sqlite proxy RPC as has
been suggested.
Your service could implement one thing only - a lock to the database file
(external to the SQLite locking mechanism, of course). So it only needs to
serialize concurrency; the client does the RPC (via either protocol handy to
you - http, WCF, whatever) to obtain the lock; then it can safely write to
the DB over NFS. As long as all clients pass through the locking mechanism,
you are safe.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users