CubeSQL vs SQLite

I am currently running several web apps which use SQLite databases. These are not heavily used, but maybe around 1000 user transactions per week, maybe 5 - 10 concurrent users at peak times.

SQLite performance has been excellent - no issues at all.

However I have studied CubeSQL and I am wondering what are the advantages of using Cube with XOJO instead of plain SQLite. Does anyone have views on this?

For a web app, the sqlite database resides on the web server and is seen as a “local” file to your code. CubeSQL solves a different problem - multiple clients accessing a sqlite database that resides on a server that is “remote” to your code.

Where CubeSQL might come in handy is if you have a web app that needs it to serve up data to web users but you also have an internal desktop (even web) app that accesses it too. Otherwise, your single SQLite database is fine and should be able to handle that level of traffic.

It’s also useful in scenarios where you are doing load balancing of Xojo Web apps. You don’t want multiple apps trying to write to the SQLite file at the same time.

Even in a single-instance web app, I wouldn’t dream of using SQLite unless you’re absolutely certain you’ve got your transactions and locking down perfect. If you screw up your locking, you can either get weird database commands or cause the app to hang entirely.

Since all web user events run in a thread, you need to be absolutely certain that Thread A is the only thread executing anything on the database once a transaction is started, until it completes. For example:

  • Thread A starts a transaction
  • Starts working in a loop. Context switch triggers
  • Thread B starts a transaction - error, a transaction has already been started by Thread A.
  • If Thread B does not check for this error, its work would continue blissfully unaware.
  • No context switch while Thread B works. Thread B commits.
  • Switch back to Thread A. Work continues. Thread A commits - error, there is no transaction. It was committed by Thread B.

Let’s say you’ve added a CriticalSection to prevent this scenario. Perfect. But with that lock held, accessing any built-in property or method of the database from the main thread will cause the application to hang. You need to lock ALL access to the database.

These are potential problems of desktop apps, but often do not get triggered due to the single-user nature of the app. But on the web, you are FAR more likely to run into these problems.

While you can make it work, I’d very strongly advise using a true multi-user database such as CubeSQL, MySQL, or my personal favorite, PostgreSQL.

[quote=206583:@Tony Davies]I am currently running several web apps which use SQLite databases. These are not heavily used, but maybe around 1000 user transactions per week, maybe 5 - 10 concurrent users at peak times.

SQLite performance has been excellent - no issues at all.

However I have studied CubeSQL and I am wondering what are the advantages of using Cube with XOJO instead of plain SQLite. Does anyone have views on this?[/quote]

I have the beta web version of an SQL Server over at http://simulanics.com:8899 if you’d be interested in trying it out. It’s basically a CubeSQL clone that allows multiuser sqlite databases, only with a web interface, custom user API development interface, and some other features that testers have requested to add (Some still being added, but the SQL portion is stable and in place). I can send the connector class to you if you wish. Once complete, the web software will be available for developers to put on their own servers. :slight_smile:

API Test:
http://simulanics.com:8899/api/mcombatti/phonenumberlookup?AreaCode=802&Region=345&Identifier=3802

I don’t think you will get very good performance if you build a SQL server out of Xojo Web. It is fun to extend databases though.

For load balancing on one computer (multiple web apps on the same server), Sqlite allows multiple threads and applications to work simultaneously on the same database : http://www.sqlite.org/faq.html#q5
If multiple applications try to write at the same time, some will be queued with the timeout.

CubeSQL doesn’t work the same way?
Since the WAL mode, I’m not sure CubeSQL brings a lot of value here.

I think that the strong point of CubeSQL is that several computers can access the database from another machine via a port. For example, actually, for the load balancing across several machines. But if there are as many simultaneous users, CubeSQL/Sqlite does make sense? On the other hand, for a traditional client/server, I guess that CubeSQl is interesting in a small company.

[quote=206751:@Thom McGrath]Since all web user events run in a thread, you need to be absolutely certain that Thread A is the only thread executing anything on the database once a transaction is started, until it completes. For example:

Thread A starts a transaction
Starts working in a loop. Context switch triggers
Thread B starts a transaction - error, a transaction has already been started by Thread A.
If Thread B does not check for this error, its work would continue blissfully unaware.
No context switch while Thread B works. Thread B commits.
Switch back to Thread A. Work continues. Thread A commits - error, there is no transaction. It was committed by Thread B.[/quote]

In Sqlite, from the moment where transactions are wisely used and where the errors are checked for each query/writing, there should not be problems?

The advantage of SQLITE is its simplicity. With the WAL mode, there may be simultaneously a single writing and numerous readings. Once a write transaction starts, the complete database is locked for other writings.
Other writings must therefore wait and are queued. The downside is obvious. But the advantage is that the situation is clear, the operations are serialized. This is an important security.
mySql is much more powerful, but also has much more subtleties. There are several ways to lock, and at different levels, multiple engines can be used, etc. It is more complex, and therefore more difficult to analyze if a problem, blocking, etc., right?

For sharing a SQLite database in a client/server set up it’s possible to use NodeJS + SQLite3. I’ve been experimenting with this because I’ve become a bit of an enthusiast for RPC servers.

Instead of sending each individual SQL statement to the server, waiting and checking the response before sending the next you can send a JSON object that represents the data you want inserted/updated and handle all the database work server side. For example if you were writing an accounting package and you had a routine to create an invoice your client program could create a JSON object containing all the data you’d expect to find on an invoice: the date, customer id, delivery address, an array of items and their unit prices, etc… and then send that to the server. The server validates your JSON object, extracts the data from it, handles all the necessary SQL commands to write the data to the database and then returns a JSON object to the client to confirm the routines success or failure.

Out of curiosity I tried creating an equivalent of these benchmarks using NodeJS + SQLite3 and the methodology I’ve described above:

[quote=201396:@Ruslan Zasukhin]

We have spend some time making simple Xojo project which do N inserts into:
a) Valentina SQLite LOCAL db using our copy in V4RB;
b) Valentina Sqlite SERVER;
c) cubeSQL Sqlite SERVER;

It has simplest Table T1 with 2 integer fields f1 and f2. No indexes during inserts.

Results for 100K records INSERTS, for LOCALHOST servers (Macbook, i7, 8GB):

LOCAL SQLite – about 0.5 - 1 second on different runs.
Valentina Sqlite Server – 6 seconds.
cubeSQL Server – about 65-70 seconds.

Results for 100K SELECTs, each SELECT finds 1 record.

LOCAL SQLite – about 3-4 seconds
Valentina Sqlite Server – about 23 seconds.
cubeSQL Server – about 60 seconds.[/quote]

For both of these benchmarks NodeJS+SQLite3 delivered in < 4 seconds (Win 7 64bit, AMD 3.4GHz, 12GB).

Now I confess in this scenario most of the saving is likely due to be the massive reduction in network traversal but I think it’s interesting nonetheless.

For clarity my implementation of the insert benchmark creates a JSON array of arrays of the 100,000 items to be inserted ([ [1,0],[2,1],[3,2],… ]) on the client and sends that to the ‘insertBench’ method of the server, the server performs the 100,000 inserts and sends an acknowledgement to the client that the job is done. The clock starts before the array is created and stops when the response is received.

Likewise with the select benchmark: the clock starts, the client creates an array containing 100,000 record ids, sends those to the ‘selectBench’ method of the server, the server performs the 100,000 selects by looping over the array finding 1 row for each element and then sends the whole lot back to the client in one shot and the clock is stopped on receipt.

Of course you could argue that it’s an apples and oranges comparison (and there’s some truth in that) but I tried to remain as faithful as possible to the benchmark without sacrificing one of the big advantages of the methodology I was testing.

Working this way does require properly learning NodeJS (atm I’m still having to look at the api ref at every step) and doing the server side programming in JavaScript but beyond the impressive performance I’ve described above a couple of other advantages spring to mind:

Your front end can be anything that can send JSON over HTTP, be it a Xojo desktop app, an iOS app or even a standard web page.
NodeJS + SQLite3 are free and open source.

[quote=206809:@olivier vidal]For load balancing on one computer (multiple web apps on the same server), Sqlite allows multiple threads and applications to work simultaneously on the same database : http://www.sqlite.org/faq.html#q5
If multiple applications try to write at the same time, some will be queued with the timeout.

CubeSQL doesn’t work the same way?
Since the WAL mode, I’m not sure CubeSQL brings a lot of value here.

I think that the strong point of CubeSQL is that several computers can access the database from another machine via a port. For example, actually, for the load balancing across several machines. But if there are as many simultaneous users, CubeSQL/Sqlite does make sense? On the other hand, for a traditional client/server, I guess that CubeSQl is interesting in a small company.[/quote]

It would depend a lot on how you load balance. Our latest platform individual processes run in a Docker container. The load balancer also runs in a docker container. So using cubeSQL over TCP/IP is much more preferable than opening/closing SQLite directly. When you scale up not all of your processes may even be on the same machine. I have an app that is load balanced across several VM’s, there’s no way to talk to the SQLite file directly in that scenario.

[quote=206816:@olivier vidal]In Sqlite, from the moment where transactions are wisely used and where the errors are checked for each query/writing, there should not be problems?

The advantage of SQLITE is its simplicity. With the WAL mode, there may be simultaneously a single writing and numerous readings. Once a write transaction starts, the complete database is locked for other writings.
Other writings must therefore wait and are queued. The downside is obvious. But the advantage is that the situation is clear, the operations are serialized. This is an important security.
mySql is much more powerful, but also has much more subtleties. There are several ways to lock, and at different levels, multiple engines can be used, etc. It is more complex, and therefore more difficult to analyze if a problem, blocking, etc., right?[/quote]
SQLite’s locking, WAL, etc won’t actually help you here. To the engine, my sequence of events looks like intentional code. It isn’t aware of Xojo’s threading or your intentions.

I’m really pleased I asked this question as it has raised a number of interesting responses and potential ideas.
So far I have not encountered any multiuser issues, however that could be due to light traffic and most transactions being “reads” with only a small number of “writes”

Also, I can see merit in Phillip’s idea to use Cube to enable clients running on multiple servers to access SQLite running on its own server - and even better if Cube also handles concurrent writes from Xojo Apps in a manner that is more robust than native SQLite.

Many thanks to all for participating / sharing.

[quote=206841:@Steve Wilson]Instead of sending each individual SQL statement to the server, waiting and checking the response before sending the next you can send a JSON object that represents the data you want inserted/updated and handle all the database work server side. For example if you were writing an accounting package and you had a routine to create an invoice your client program could create a JSON object containing all the data you’d expect to find on an invoice: the date, customer id, delivery address, an array of items and their unit prices, etc… and then send that to the server. The server validates your JSON object, extracts the data from it, handles all the necessary SQL commands to write the data to the database and then returns a JSON object to the client to confirm the routines success or failure.
[/quote]

Hi Guys,
Hi Steve,

You have write it in the interesting way :slight_smile: “sending … waiting … check response …”

Actually, it is good to understand that these “JSON games” have came from JS-lovers, just because JSON is more native format for JS.

What you describe is
CLIENT:(data -> JSON STRING) -> send STR to SERVER: (PARSE JSON STRING -> build SQL STRING - PARSE SQL STRING -> DB disk)

Standard way to work with SQL DBMS is simpler:
CLIENT:(data -> BUILD SQL STRING) -> send STR to SERVER: (PARSER SQL STRING -> DB disk)

So you have one more “LAYER” between data and DB.
JSON, XML, REST, ORM – all these things adds OVERHEAD over SQL way.

You can perfectly with SQL do the same:

  • provide validation on server-side with triggers and SP. And this is even better - use DBMS for this.
  • send few SQL commands if you want and can.

But yes of course, that layers brings some other benefits.

[quote=206841:@Steve Wilson]For both of these benchmarks NodeJS+SQLite3 delivered in < 4 seconds (Win 7 64bit, AMD 3.4GHz, 12GB).

Now I confess in this scenario most of the saving is likely due to be the massive reduction in network traversal but I think it’s interesting nonetheless.
[/quote]

Steve, few days later I have add info to that thread:
– 100,000 records inserted in 5.4 - 5.7 seconds. Prev result was 18-20 seconds.

And this was thanks to the same trick as you describe with JSON Arrays.
We have made a single SQL command with 100K records bound, so only one sending to Valentina SQLite Server.
Difference in 1.5 sec comes most probably from difference in computer power, OS, …

But you are right about JSON/REST opens one more door to work with DB. This is why we have add REST into Valentina Server for both Valentina DB Server and SQLite Server.