Hi,
Never gave SQLite a thought, always thought when I jump to SQL it would be postgres.
5 desktop users working on windows server 2022 in data centre via RDP + a NTWS , on the same dataset
The NTWS might ramp up to quite a few users, reading and writing.
Is SQLite a realistic option?
Reading through the docs can’t make up my mind, 90% still with Postgres.
But also would like to know more about SQlite implementations
thanks for any info
Johan
My opinion:
With the Clarion SQLite driver, SQLite is unusable for just about anything. SQLite locks the entire database when it is being written to, and for whatever reason, Clarion locks the database when it doesn’t need to, and this brings everything to a halt for everyone.
There is an ODBC driver for SQLite, and Clarion works fine with that. Mike Duglas has a driver that I have not used, and which I think he mostly markets as a way to do SQL queries of ISAM data on the fly (load your ISAM data into a SQLite database, do your SQL query on the SQLite data), and Capesoft have a driver they just released developed using their new driver development kit, both of which I expect work properly.
With one of those other access options you might consider SQLite for a couple of handfuls of people not doing anything too strenuous (i.e. not doing data entry, but doing other work in between and doing a little documentation of what they’ve been doing). That is, not too many people trying to do writes at the same time. Speed is good, can have large databases, can have views for reporting. On the downside, SQLite is not a full-featured database. No stored procedures, for example. It’s somewhat sloppy about data types, which might or might not bother you (dates in particular I don’t like). No installation hassle: ship the DLL, make sure everyone is pointed to the same SQLite database.
If the SQLite driver worked properly, it would have worked well for the situation I was using it for: eight or so user answering emails and documenting what they have done (where the email came from, what it was about, what they did about it) for reporting purposes. Since it didn’t, they write temporarily to a Topspeed file and that information is transferred to a a SQLite database for reporting.
Hi Jon
Thank you for detailed reply, much appreciated.
cheers,
Johan
SQLite is perfect for single user, or small groups who aren’t hammering the database. I would never use it for heavy multi-user access, going instead with something else like PostgreSQL or SQLServer.
Hi Mike
Thanks for that,
I’m probably on the edge with my current requirements,
so it’s a no brainer that if I do the move to SQL ,
it might as well be to Postgres.
Which has been the plan but SQLite just came onto my radar after the CS driver product launch.
thanks
Johan
Hi Johan,
I only use SQLite for archiving in a system with Topspeed files.
Sent emails and their attachments, archived documents and images. That type of data.
It’s fast enough for reading by multiple users but it’s not designed for multi-user write access.
I’m using it successfully with the IP Driver.
It’s a great option in the scenario I’ve outlined. If you’re going to use it I strongly recommend using the WAL option (Write-Ahead Logging)
I don’t use it with any of my apps that use SQL at the backend. (although you could I guess)
Cheers
Rohan
yes, SQLite is a realistic option. As all the processes accessing the database are on the same machine then WAL mode (/WAL in the driver options) can be used. This improves performance, especially write performance, and also allows for a much faster write-locking system - which in turn means more users are reasonable.
Of course Postgres is still a much more powerful server. But conversly consumes more server resources (Ram, Cpu etc). So there’s no “perfect” answer here, it’s a case of “your context matters”.
Moving apps from SQLite to Postgres (and separately, moving data from one to the other) is now a lot easier (or will be once the Postgres driver ships.) The whole idea behind the increased functionality in the new drivers to to reduce the need to bypass the drivers. This in turn makes moving the app from one data engine to another a LOT easier.
This opens the door to you creating a SQLite version of your system, and simply trying it out. If it works, great. If you find there are performance issues, then moving to Postgres will take minutes and hours, not days. The more you stay “inside” the driver, the faster this process.
Ultimately your outcomes will depend on your context, and it’s impossible to be definitive without giving it a go.
There’s a fair bit of truth in this, but also it’s not completely true (although you need to know your SQLite reasonably well to use it well with the traditional SQLite driver.) WAL mode for example reduces lock times a lot.
It has a “typeless” data system - any data can go into any cell. My driver allows for this with a fairly intense function doing the necessary translations in both directions. Dates in particular can be tricky to deal with in traditional clarion - it’s a lot easier now.
Agreed. There are definitely limitations - although they’re further away then one first thinks.
From my testing (example in PTSS 43433) one user having a browse (on any table) open, will prevent any other user from making and committing a change to any table. I’d call that unusable. I tried WAL, made no difference.
In the little app I ended up building, the program grabbed allowable values for dropdowns from SQLite, though the data being worked on was written to Topspeed, and even there I felt constrained. The dropdown lists were read into queues and the connection with SQLite was closed as quickly as possible.
It’s not quite correct: I have not a driver (in Clarion terms) but a class that performs direct calls to the sqlite3.dll. SQL queries to ISAM is a bonus feature, as well as superfast export to csv and some others.
@JonW Did you checked https://github.com/Bruce511/DriverKit?
Hi Daniel,
I’ve been looking at the driver kit, just need to bite the bullet and get StringTheory. Since everything works perfectly using the ODBC driver for SQLite I’m pretty certain that the problem is the way that the Clarion SQLite driver is written, not any inherent problem with SQLite, so I expect both Mike Duglas’s class and Bruce’s driver fix the problem.
Jon
Hi @JonW
Same here 
Over the years I’ve installed anything and everything.
Then I realized that it is not worth buying something just to try it and then go back to the old one that is tested and safe.
In my case, I use a MariaDB database (named pipe) for a web server projects. Everything is set and tested with hundreds of simultaneous users (using Locust) and now I don’t know if there is any need to look further. I was careful when writing it not to use prop:sql in a way that could be abused.
Installation of the MariaDB database takes literally 2 minutes (a few exe files and editing my.ini), but the installation is done once per server, so very rarely.
For desktop projects (local network), I have been using the MS SQL Express version for years.
Today, in order to go in a new direction, I first need to know exactly what benefits I get from the change and I’m waiting to see the reactions of others who have tried everything and anything like myself years ago
I use it to export a tps file to DB Browser for SQLite, for the user to do SQL queries. It’s a browser like TopScan, except it’s SQL and a much nicer and richer user interface.
Typically, I export only part of a tps file. For example, I have a date range (e.g. last year) of invoices. It’s easy to export to SQLite: you have a SQLite table in your dictionary and do a deep assignment to it.
With DB Browser, the user can sort or filter them any way they want. There is an easy search for text fields. It shows results immediately as you type.
You can use SQL wildcard % or comparison operators, even a range ~ operator.
You can do SQL commands. There is reporting, plotting, and other things.
This is on my cloud server, so my users don’t have to install DB Browser themselves.
I wish a Clarion browse list had all these options and a friendly interactive interface.
I guess my point is, DB Browser for SQLite is worth looking into if you use tps files.