Are you using the SQLite or SQLite2 driver?
/WAL is supported by SQLite2, not by SQLite.
If using SQLite you can use a simple prop:sql, or even an external client, to set the database into WAL mode. Its a change to the SQLite file, so is persistent znd only needs to be done once. It applies to all programs reading that file.
It can only be used if ALL processes accessing the data file are on the same PC.
That said, the locks used by the SQLite driver are very aggressive- the SQLite2 driver is far more friendly to multiple threads and processes.
send(‘PRAGMA journal_mode=WAL’) in driver options is how to set WAL in the original driver. Bruce’s “very aggressive” basically means that the original driver locks the file when it shouldn’t, so doing practically anything it locks itself out, let alone any other process. I’m hoping to check out the SQLite2 driver in the next few days.
The current solution is undergoing a complete rewrite to Postgres while the TPS version needs to be on life support
Chose SQLite as the lightweight SQL option (we ported key features and normalized the db) for Nettalk Webserver primarily reports and analytics sorely cumbersome or lacking in the TPS version
Having the power of SQL was needed and employing views would be as simple as re-creating the views verbatim in Postgres - thus leveraging code and time…hence the single process to write and multiple processes to read only
I have a similar use case for SQLite. Our TPS application can’t be reasonably ported to a client/server DBMS, but SQLite works as a great replacement for it.
There are, however, some dragons you should consider:
1. Network Access Limitations
If your app needs to run over a network (which is common for TPS-based apps), SQLite3’s WAL mode is not the solution. It will corrupt as soon as two stations try to access the database.
2. Clarion Driver Limitations
It’s not that SQLite has multi-user limitations per se (that’s critical to understand). The issue is with the Clarion SQLite3 driver implementation. In non-WAL mode, a read statement blocks any writes. Since an operation like SET(x); NEXT(x) creates an open-ended statement that is never consumed, you get locking errors in any multi-tenant setting.
Manual resets or run-to-completion both solve this issue, but since the low-level API isn’t exposed, you’re screwed in the Clarion ecosystem unless the SQLite2 driver by CapeSoft solves the issue.
Solution
I hand-rolled a SQLite3 wrapper and created a mapping layer between SQLite3 and Clarion record structures. The result is that I don’t get concurrency issues from SQLite3 while being free to slowly port read-heavy or complex workloads from TPS to SQLite3 proper.
While I think I agree with what you are saying, I think it could be expressed more clearly:
In non-WAL mode, a Clarion read (such as a SET/NEXT to fill a browse) locks the database. Since this is an open-ended statement that is never consumed (until the browse is closed), you get locking errors even in a single user setting – the form cannot get a lock on the database to write its changes back because the browse already (unnecessarily) locked the database.
You should not be getting situations where driver locks the db and does not allow you to write back to it. I mean, a SET/NEXT loop should acquire a read lock to the db and self-escalating to an intent-to-write to exclusive should not cause issues if they are all done within the same app (thread?) and the reader and the writer share the owner string.
It’s just that sqlite3 engine (not driver) won’t allow any connection to mutate the database while it’s being read by another.
It does. On the first NEXT the result set is read into the driver, which NEXT then iterates theough. It does this on a “page” basis (you can set the page size.)
So the “first NEXT” does what it needs to do, then releases the database for others to use.
