SQLite:
What happens if two (or more) people want to access to the same data base file ?
Either in read or write ?
I just learn minutes ago that the client think about around 20 people can access to the server where the data base resides from my application ( potentially at the same time ! ).
That was not in the original plan. The amount of data in each access (either in read or write) is low (Name, address, Phone + eMail and an eventual small photo ID).
Informations, Idea(s), Clues to unusual approach are welcome.
SQLite is not designed to be multi-user…
If you have 20 people that need concurrent access, I highly suggest finding an alternative (I think cubeSQL support multiple users and is SQLite compatible)… but mySQL, Postgre etc might be better…
If user “A” reads record 43, and user “B” reads record 43, the user “A” updates it, followed by user “B” updating it… whatever user “A” did would be gone and over-written by user “B”… assuming there was no active record/table/database locks, and that the transactions did not occur at the same or overlapping times.
Two extra files are created (*.sqlite-wal and *.sqlite-shm) alongside the main database file
The processes using the database must be on the same host computer
WAL does not work over a network filesystem
#1… this will make it difficult to put the application in the Apple Store, as you have no control over these two files (no user permission is ever asked), therefore it violates the “sandbox” rules
#2… kinda of difficult these days to have multiple concurrent users on the same host computer
#3… since it doesn’t work over a LAN/WAN, then this pre-assumes that (#2) is on the same desktop
And I have found that #1 happens sometimes even if you do not have this flag set.
The process is so fast, we have hard time to see it, excepted if the data base file is in a USB MemoryStick. I saw wal file in the above condition (I think).
OK, I will explorate PostgreSQL a bit more. At first sight, and for CREATE TABLE, the code seems to be nearly the same. Id better create a new TABLE and learn from there (I have a bit of time, so I can do that).
(Full disclosure, I wasn’t using Xojo nor SQLite…)
For a small app in our department where 2-20 people might be accessing it at the same time to update records, I implemented a simple “reserve” system – when you wanted to edit a record it checks a RecordLocks table to see if someone else is editing it and if so, tells the user who has the record open and what time that happened. If nobody has that record open it puts that record ID, the ID of the editor, and the time in the table.
When they save the edited record the lock in the table for that record is deleted. There’s also a timeout of 2 hours so that if someone opens a record for editing and then decides to go home early everybody isn’t screwed until they come back.
I based that off an article I saw somewhere online, but at the time I wasn’t so careful to copy the source reference into a comment in my code. But hopefully that may give you another idea on how you can handle your problem.
I have, that’s why I ask. XOJO is marketing XOXJO Cloud as a platform for multi-access Web Apps using SQLite. I am wondering if they have a DB Server in between like CubeSQL or they have done something to mitigate this happening when multiple instances are trying to access and write to the DB at the same time.
It’s OK to share a SQLite database so long as the processes accessing the database are on the same computer as the database itself.
As far as conflicting writes are concerned if you start a transaction with “BEGIN IMMEDIATE” any other process attempting to start a write to the database will just get the SQLITE_BUSY error until your transaction is completed. Other processes can continue to read from the database though even during your update.
If you want to share a SQLite database on a network and you don’t want the cost or licensing hassles of Valentina or CubeSQL you could use pypy to create a JSON-RPC server (I know you could do that in Xojo too but imo pypy makes this particular task easier) that performs the database updates and your client app interacts with that using a httpsocket. This is often faster than the client app interacting with a remote database server using SQL as there is only one round trip to the server rather than multiple. As an example let’s say your app is an accounting app and your running a routine to post an invoice for which you have the header and 10 invoice lines, with a JSON-RPC server that invoice detail would all be wrapped up in one JSON object and sent to an insertInvoice method in one shot rather than being 11 db.SQLExecute() round trips.
And the reason for this restriction is that network file systems are apparently buggy and/or dishonest about whether they have actually completed the data write.
[quote]
As far as conflicting writes are concerned if you start a transaction with “BEGIN IMMEDIATE” any other process attempting to start a write to the database will just get the SQLITE_BUSY error until your transaction is completed. Other processes can continue to read from the database though even during your update.[/quote]
SQLite sharing of databases probably works well enough provided that each process follows some simple rules and implements the SQLITE_BUSY timer to handle the SQLITE_BUSY situation properly. This tends to imply that the set of processes should all be written by one person or a small team.
).