We are in the process of migrating away from TPS to SQLite3.
To keep it short:
- Clarion SQLite3 driver had a bunch of issues that made the migration a no-go in a multi-user environment
- SQLite3 is extremely capable and fast
- SQLite3 adds some extra complexity if you need to use it over LAN due to how file locking works
- Full text search out of the box
I made the necessary plumbing that keeps the SQLite3 databases in sync with the TPS files, allowing us to leverage full SQL reads on TPS databases. That’s been a gamechanger so far, as the replica both acts as a live backup if a TPSfix (TPS file repair tool) fails, and as a massive accelerator when it comes to data retrieval, for the low cost of some overhead in write operations.