Multiuser DB - update data

Databases
1

Hey, I know maybe this is not the best or the recommended method for a multiuser DB… but I am in the process of learning.

I have a Sqlite DB, and two windows pc’s accessing it. (they are on the same network, obviously).

That seems to work fine (until now). Eventually I may try CubeSQL… I’ll see… but now…, The problem I am facing is that when one client modifies data on the DB, that is not reflected on the other client unless the user reloads the window/control showing the data.

For now I have two options… either leave it like that and let the refresh be done when the user closes/reopens the window or… insert a timer and reload the data every… second ? That smells bad to me… So how is this achieved… ? How do I tell the other PC “hey there’s new data. load it!” ?

As usual, thanks
R

2

you have two conflicting statements

by its very nature SQLite is not a true multiuser database :slight_smile:

3

Hey Dave, thanks for the reply…

what about using cubeSQL ? Is there not a way to make it work ? It’s just two pc’s… I don’t want to start from scratch… and also it is not such a critical app I am trying to do… (or maybe I really have to start from scratch ?!)

4

Why not, given it is only 2 machines, set up IPC sockets and sent notification messages between them.

( It might be a bit alarming for the users suddenly having something like a listbox reload on them out of the blue, but maybe that does not apply ).

5

Using one SQLite database file simultaneously from two computers is like trying to write into the same word document, from two computers at the same time. It will mess up the file.

My suggestion: get the free 2-user license of cubesql and install this server on one of your computers. Load your SQLite database file with cubesql administrator program.

Cubesql server will control access to the database file.

Now for the second question: trying to send an updated record to the user-interface of any other connected user creates a huge overhead in network traffic and database activity and last but not least programming efforts to achieve this.

A better strategy is to simply maintain a time stamp for the last update of a record and when a user tries to update a record, then you compare the time stamp on the database with the one which was selected before the beginning of the update.

If someone had updated the same record in the meantime, then you can react on it: warn the user, discard his changes and reload the newest record again, or leave the choice to the user…

This will rarely ever occur, but then you have something in place to handle the situation.

Use update- and insert triggers on the database to maintain time stamps .

6

But less than for example the second client getting all records in a listbox over the network when the other user yells to him: I have changed one record, press “Reload”.

What I’do is to open a TCPSocket on the clients, which listens to the other computer. When the other computer updates a record, it will send the rowId to the client, which then can load this record.

7

If you are not committed to a particular db, I’d check out PostgreSQL and its notification system. See: http://www.postgresql.org/docs/9.3/static/sql-notify.html . Xojo’s Postgres plugin supports this feature very well.

9

Just had a rethink about my post, Oliver has already covered it perfectly well with a timestamp on changes.

Its a shame that Data Change Notification Callbacks aren’t supported.

10

Maybe the update_hook is a good one for Christian to add to his SQLite Driver plugin. I understand that his plugin also properly handles lock timeouts.

Oh, as for the clients; what Oliver says and as an easy solution, maybe let the clients poll the update timestamp and ignore if it’s the same or refresh the listbox when they’re out of sync.