[Interest] sqlite

[André Somers]

Bo Thorsen schreef op 17-4-2015 om 14:24:
> On 04/17/2015 02:10 PM, André Somers wrote:
>> Bo Thorsen schreef op 17-4-2015 om 13:48:
>>> On 04/17/2015 10:25 AM, Graham Labdon wrote:
>>>> Hi
>>>> My application makes use of an sqlite database that needs to be shared amongst
>>>> multiple instantiations of the application.
>>>> Can  anyone please suggest how I can implement this such that if one
>>>> instantiation of the application updates the database then any other
>>>> instantiations that are running get notified.
>>> You can't do this. There is no mechanism in sqlite to notify other
>>> applications that something has changed. You are going to have to
>>> implement an IPC system for this.
>>>
>> In fact: don't do that at all. Don't use sqlite for simultanious access
>> by several processes. We did (against recommendation of the engineers,
>> management forced a 'quick fix' to get a feature in), and we're still
>> sorry for it. You'll end up in hack-upon-hack to make it sort-of-work,
>> but it is no end of pain and sooner or later will end up in data
>> corruption in your data base.
> This contradicts the info on the sqlite website. They claim that it's
> possible to do exactly this. Interesting that you disagree.
In practice, we found that it results in problems in practise, even if 
it may work in theory. Perhaps it is due to the actual sqlite, perhaps 
it is due to the fact that it really is just a file and it is the 
(networked) file access that is to blame in the end in our case, but the 
problems are real.
>
>> Sqlite is very nice as a small, local,
>> simple database, but it is no replacement for running a real database
>> server. If you need concurrent access by multiple processes, use a
>> database that is built for that kind of thing. MySql, PostgreSql, whatever.
> And in that case the OP is back to the original question and answer.
Not really. You suggested some IPC notification service would be needed 
to send notification of data changes. That (to me) suggests that you 
could still use the sqlite db, as long as you then notifiy other users 
that the database has changed (no matter how fine-grained you'd like to 
make that). I claim that that is not a good solution.

Instead, I would suggest to get rid of the sqlite database to begin 
with, and use a database that just supports this out of the box. Or 
alternatively, create your own server application that acts as the 
central data store, and handles the notifications as well as the reads 
and writes. Such an application could then use any backend again, 
including sqlite. You'll probably end up duplicating much of what a real 
database client/server setup already provides though.

André