Just Curious, but could you safely use SQLite as a READ ONLY database from multiple applications concurrently? The Blog talks about “locking” and the potential for update collisions resulting in corruption. While I expect that ANY time you want multiple apps to communicate with a database, a SERVER is better equipt, I’m just wondering if read only queries are potentially dangerous as well (not sure what SQLite does behind the scenes in memory that could be compromised by multiple concurrent “connections”).
@Robert Bednar As specified on the SQLite website (see #5), Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however..
So there shouldn’t be any problem using the database from multiple applications in Read-Only mode.
Note, however, that some OSes may be problematic (see the link above).
Thanks Stephane, I see several conditionals about non native file (NFS) and older (FAT) file systems. Additionally I see warnings about using connections across forked processes – all which all makes perfect sense to me. I am amazed that SQLite is as robust for multiple connections as it is. But if you have a largish database and you need READ ONLY access… looks like this is a potentially viable solution. Thanks!
@Robert Bednar Yes and I love SQLite for its versatility, ease of use AND using a single file (more or less) AND you can add custom functions in Xojo code…
Though over the network, I may be tempted to convert an SQLite database to postgreSQL or mySQL which are much faster. But of course it depends on your particular DB and how it will be used.
The answer is in the title. SQLite is Great for its intended use.
Can you even open an SQLite file in read-only mode in Xojo?
I’m not sure you can, but he may be controlling this at the application level; the app never writes to DB, only reads.
There should be no problem doing this. We have a DB that has over 100K rows and multiple users reading from it on a daily basis. We are on Windows 10 for the workstations and server.
I’ve created a JSON-RPC server that makes use of a SQLite database and that’s worked out really well. The clients communicate with the server using JSON over a TCP socket and the server handles the reads/writes from/to the database.
I didn’t think you could… but I expermented… took a DB set the file system to ReadOnly, and it still worked, got an error of course if I attempted to Write… but I don’t think it can be opened in WAL mode
with MBS Xojo SQL Plugin for SQLDatabaseMBS or SQLConnectionMBS class you can set SQLiteVFSFlags option to 1 to open SQLite database in read only:
// for SQLite, set flag to open database file read only:
c.Option("SQLiteVFSFlags") = "1"
Yes, of course.
We even use occasionally the databases read/write for multiple apps. IF you have a lot of reads, but only a little number of changes, this works fantastically. Don’t forget to use a big cache size, so the database fits in memory.
And to avoid lock trouble, you can add logic to app to retry writes if needed.