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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
