I’ve been using cubeSQLServer now for a few years with Xojo Web apps, mainly because its Admin console is a very convenient manager of backup schedules of my various Xojo Web apps’ SQLite files, and it comes free with unlimited connections with ServerWarp. I have a fundamental question about it.
When a SQLite file is hosted by cubeSQLServer, that file is still only going to allow one writer at any moment in time. So what does cubeSQLServer bring to the multiuser environment here that SQLite running in WAL Mode does not do alone?
Until now, each SQLite file of mine has been accessed by a single CGI app, so I guess that really hasn’t been a multiuser environment. But I’m now considering having a particular SQLite file (hosted by cubeSQLServer) being written to by two different CGI apps. So this would be a multiuser environment now, that supposedly cubeSQLServer makes okay. I just don’t see why SQLite in WAL Mode couldn’t do this itself.
Is this just a performance issue that cubeSQLServer solves, in a high concurrency rate situation? That’s all that seems to be at issue here: https://www.sqlite.org/whentouse.html.
I don’t see how potential corruption can be an issue that cubeSQLServer solves here, as SQLite itself won’t allow more than one writer at any moment in time.
Yes, if your database needs are predominantly writes, then cubeSQL might not be for you as the writes are journaled one at a time.
My database usage is 95% reads, which CubeSQL can do in parallel and supports multiple CPUs (which Xojo cannot) and lots of RAM. Any large reads (pictures) I cache locally to a local SQLite file for faster access (wiping it on startup and shutdown) to speed up the server.
Unlike others, I use a different and new CubeSQL database connection every time I need one in a method, so when I need the server I really tax it, but when I’m not accessing the server, there are zero connections. With SQLite I tend to open it when I open the app and leave it open for the duration of the app, so I’m not sure how this affects performance.
CubeSQL gives me privileges, so I have two users defined: an admin and a client account. The client account cannot DROP or DELETE from most tables, even if you try. If someone finds out my client password, they can read most tables, but not destroy anything. You don’t get this with SQLite.
My desktop and web apps that access CubeSQL can be on entirely different servers essential to balance the load. SQLite can’t do this.
CubeSQL can communicate with the database over encrypted AES256 connections (without pesky certificates), so no one can sniff the network traffic to see what I’m sending. SQLite can’t do this.
CubeSQL can hold an unlimited number of databases, each as a separate file. To jump from a particular client database to a shared common database I just say “USE DATABASE EmailSettings” and I’m there on the same connection. I can jump back too. Not sure if you can do this easily on SQLite (with MySQL and PostgreSQL I have to create a new database connection.
Maybe none of these are relevant to you, but they’re things I’ve done in my latest project.
As for WAL it is great adition and allows [quote=419077:@Ralph Alvy]Is this just a performance issue that cubeSQLServer solves, in a high concurrency rate situation? That’s all that seems to be at issue here: https://www.sqlite.org/whentouse.html .[/quote]
Not really, cubeSQLServer just emulates multiuser with a “queue”, so, it is only one user at a time, so, performance is not really great.
It can work if your apps have a couple of user at a time. For more than that, just consider that SQLITE IS NOT designed for that, no matter that you have a middleware server. As SQLITE site states: “Think of SQLite not as a replacement for Oracle but as a replacement for fopen()”
If you plan to have dozens of users, is better to use a real database management system.
[quote=419178:@Pedro Ivan Tellez Corella]As for WAL it is great adition and allows
Not really, cubeSQLServer just emulates multiuser with a “queue”, so, it is only one user at a time, so, performance is not really great.
It can work if your apps have a couple of user at a time. For more than that, just consider that SQLITE IS NOT designed for that, no matter that you have a middleware server. As SQLITE site states: “Think of SQLite not as a replacement for Oracle but as a replacement for fopen()”
If you plan to have dozens of users, is better to use a real database management system.[/quote]
I won’t have more than two CGI apps accessing any SQLite file at the same time. And no more than a handful of Sessions writing to any SQLite file at the same time.
[quote=419124:@David Cox] …
CubeSQL can hold an unlimited number of databases, each as a separate file. To jump from a particular client database to a shared common database I just say “USE DATABASE EmailSettings” and I’m there on the same connection. I can jump back too. Not sure if you can do this easily on SQLite (with MySQL and PostgreSQL I have to create a new database connection…[/quote]
Love the time you took on this David. Very thorough reply. I find the above section most interesting and am thinking about where that might come in handy for me.
Part of what sparked my initial question is that I frequently hear people saying that SQLite shouldn’t be used in a multiuser environment because it can corrupt the database. I just don’t see how that happens if it serializes writers. If it’s a performance issue, that’s another matter.
Well, that was true on really old versions of sqlite. cubeSQLServer jus emulated multiuser with a queue. Nowadays, sqlite is more robust and you can use wal, just be sure the 2 apps are on the same server…
And you are right, the issue is just performance, not corruption. With no more than a handful of Sessions it should work fine.
[quote=419221:@Pedro Ivan Tellez Corella]Well, that was true on really old versions of sqlite. cubeSQLServer jus emulated multiuser with a queue. Nowadays, sqlite is more robust and you can use wal, just be sure the 2 apps are on the same server…
And you are right, the issue is just performance, not corruption. With no more than a handful of Sessions it should work fine.[/quote]
Thanks, Pedro. Yes, the 2 apps are on the same server, overseen by the same cubeSQL installation.