SQLite: Advantages and Disadvantages

by StevenP

SQLite.org claims that SQLite is the most widely deployed SQL database engine (careful choice of words, there – see http://www.sqlite.org/mostdeployed.html). They also claim an impressive user list (see http://www.sqlite.org/famous.html).

I had heard the claim that SQLite was the most widely used (or “deployed,” as SQLite.org asserts, probably with greater accuracy). But that wasn't the reason I was excited by the new driver.

Of course, SQLite offers SQL syntax for database manipulation. The driver wouldn't have any attraction at all otherwise. But SQLite offers two unique features that I find very appealing. SQLite is:

“Server-less”

and

Requires no configuration

Server-less

SQLite.org claims this “server-less-ness” as one of the main advantages of SQLite. They say:

The main advantage is that there is no separate server process to install, setup, configure, initialize, manage, and troubleshoot.

But notes, in discussing “server-less-ness:”

For safe operation in multi-threaded environments, SQLite requires the use of mutexes.... Mutexes are only required if SQLite is used by more than one thread at a time.

No Configuration Required

The SQLite developers themselves write:

SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.

SQLite just works.

No DBA … well, we've all got war stories about deal with DBA's. Cutting them out of the loop is no cause for regret. On the other hand, SQLite is clearly not client-server. Just as Condor3 was SQL, while using ASCII files for storage, SQLite responds to SQL commands but no more than that. In other words, expectations must be adjusted on hearing “SQL.”

More importantly, the implication is that Clarion's CREATE statement should work correctly. The issue with creating a SQL db using the Clarion CREATE statement is that Clarion's CREATE cannot do the job completely. As Scott Ferret noted back in 1998:

If you want to create primary key constraints you need to use PROP:SQL to do the create. Note that you should always use PROP:SQL to do a create as the Clarion CREATE statement cannot handle all the features of the SQL CREATE TABLE statement. Eg constraints on fields and foreign keys.

Getting a bit ahead of the story, CREATE does work for SQLite databases and tables. SoftVelocity supplies an example app that does just this, CREATE( file ).

SQLite simply does not require scripts to be written or to be run to create databases and/or tables. Certainly, you can use scripts; SQLite Database Browser (http://sqlitebrowser.sourceforge.net) displays scripts (though it doesn't write them to the clipboard like MS' SQL Server Management Studio) on opening a database:

Figure 1: Scripts displayed in db browser

SQLite is Transactional

Soon after SV's announcement, I found out something else at SQLite's web site:

A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

We here restate and amplify the previous sentence for emphasis: All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

- a program crash,
- an operating system crash, or
- a power failure.

Serialization has been discussed, at some length, in various Clarion Magazine articles. What I take away from this is that the integrity of write operations is heavily safeguarded. This should reduce some of the impact of user stupidity, like turning off machines with programs running and files opened. I find this a very desirable feature.

This feature, however, leads to concerns about SQLite's suitability for use in multi-user apps or, even, multi-threaded apps. The concern is that the database is locked, not as some have said, whenever there is db activity but only when there is a db write. In fact, the SQLite folks themselves talk about the db being locked and only later clarifying that it is during write activity, reads are not blocked.

Or so they say:

SQLite Transactions

SQLIte implements this simple design by locking the entire database file during writing. SQLite read operations can be multitasked, though writes are only performed sequentially. In other words, multiple write requests must queue up, to be executed only when the previous write request has completed.

Apropos of SQLite's implementation of transactionality, the SV docs state:

SQLite is not inherently designed for multi-user access. It is not a client/server system like most classic SQL systems.

For this reason, SV also states:

It is much more like the TopSpeed Driver than MSSQL.

For what SQLite.org says about multi-user or multi-thread access, see Can multiple applications or multiple instances of the same application access a single database file at the same time?

The Test Plan

SV provides an example app, a variation on the school.app. I was unable to make the blobs work on the student update form – they work fine in the shipping TPS version of the app – and one of the relation trees fails for me. In any case, I don't use relation trees much and I don't use blobs at all.

The app, however, is worth a look because a simple way of converting to SQLite files is demonstrated.

But, I do have an old Clarion Template chain app, using .DAT files. It is a checkbook app which I used to learn Clarion for Windows (though, in production, I still use the CfD version). This app is perfect for testing things and is a prime candidate for “conversion” to a new file system. I also have an app created originally in C7, using TPS files, with which I can test.

Next time, my adventures moving these apps to SQLite.