SpatiaLite 2.3.1 Architecture

If you are already familiar with some DBMS [MySQL, PostgreSQL ...], may be you already know they use a co-called client-server architecture.

Following the client-server model we have:

some kind of storage used to memorize any data handled by the DBMS.
usually this actually means disk storage, and requires a very complex layout using many files and many directories ...
it's very easy that such complex layout will change when upgrading to some newest version, forcing to follow some quite elaborate migration path in order to recover your data
an unique server-side process acting as the DBMS engine
any physical interaction involving the storage [reading/writing] is then delegated to this process
User applications can then interact with the DBMS server establishing a network connection managed by some appropriate client-side process
quite obviously connections are accepted by the server on the basis of some sophisticated authentication schema, and each user has its own specific permissions profile

Well, forget all this ...
SQLite adopts a completely different architecture.
As much as the other DBMSs tends toward increasing complexity, as much SQLite tends to be elementary simple.
This does not means less powerful; what this really means is:

do you absolutely need to support a client-server arch ?
all right, use a complex DBMS such as MySQL or PostgreSQL, and never think to adopt SQLite
Obviously SQLite isn't what you are searching for ...
do you simply need a simple but robust DBMS for your personal use only ?
immediately adopt SQLite; you'll be fond discovering how it can be simple and effective.

The architecture adopted by SQLite is really simple:

we already need some storage to memorize any data handled by SQLite.
but this time a simple file contains a complete DB.
and we can simply copy this database file from one location to another.
actually, we can even transfer a database file from one platform to a different one, regardless of their kind; i.e. we are allowed to produce some database on Windows, and then we can use it on a Mac or Linux box, because database files are universally portables and implements a cross platform architecture.
and, even better, SQLite supports memory storage as well, this allowing for ultra-fast performance.
obviously, a DBMS engine is still required in order to access the storage
and this time this is not a separate process, but is implemented as a very simple C library
Consequently, user applications simply needs to link the SQLite library in order to be immediately connected to the DBMS, with no other frill.
And that's not all; the SQLite library is incredibly compact and light-weight; just some hundredths kilobytes.
actually, the complete SQLite DBMS engine is by far smallest than the client libraries alone as required by other popular client-server DBMSs.

SQLite implements a powerful extension mechanism.
This allows developing further libraries implementing some useful feature, and they will become immediately accessible as SQL functions.
This one is the way SpatiaLite is implemented, simply as an extension library based on the top of the SQLite library. SpatiaLite implements a Geometry data-type and adds any spatial SQL function as required by the OGC SFS standard.
So you can use both them, i.e. SQLite and SpatiaLite, in order to implement a complete Spatial DBMS engine.

And SQLite supports a further extension mechanism, supporting Virtual Tables.
i.e. this allows the DBMS engine to access any kind of exotic data sources as if they where standard SQL tables, or to implement peculiar logics.
SpatiaLite widely supports Virtual Tables:

Shapefiles can be directly accessed using VirtualShape
Plaint text files can be directly accessed using VirtualText
Routing is supported using VirtualNetwork
Spatial indexing is implemented directly in SQLite using R*Tree VirtualTables
and the alternative MBR memory-caching is implemented by SpatiaLite as MbrCache VirtualTables

Deploying the whole Spatial DBMS basically is a very simple task, but some unexpected surprise may occur as well.

Static linkage: why not, after all ?
this one absolutely is the simplest and most effective solution:
- for sure your application will now directly include the complete Spatial DBMS engine.
- this adds less than 1MB code to the executable binary.
- both SQLite and SpatiaLite surely are the expected version, and built using any appropriate settings.
- to get this, you simply have to link the static spatialite library; this includes sqlite as well.
- and if you are going the quick and dirty way, you can simply add both the amalgamation C sources to your project.
Dynamic linkage (1): directly using libspatialite
- this will add some further complexity, but if anything is properly set, this will work without any problem.
- the dynamic spatialite library includes sqlite as well, so you can use it in order to implement the complete Spatial DBMS engine.
Dynamic linkage (2): using libsqlite and then trying to load spatialite as an extension
- this one may often be an impossible solution; i.e. this may not work, leaving you in pain and trouble.
- and this is what actually happens to lots of Python, PHP, (and so on) unhappy and frustrated users.
- let see in short where the trap is ...

the pre-packaged 'libsqlite' trap

SQLite is very quickly evolving; more or less every month they release a new version, and this very easily introduces some interesting new feature.
As a rule of the thumb, SpatiaLite tends to support such cool new options.
But even worst, many things absolutely critical for SpatiaLite are quite recent ones: Extensions, VirtualTables, R*Tree ...

Very often pre-packaged distributions are quite slow to release; so it's very alike you are actually using some really obsolescent libsqlite version.
And this, quite obviously, can cause some form of incompatibility, more or less severe.
But all this doesn't tells the full story; we have already to begin the bitter part ...

SQLite introduced the extension mechanism since version 3.3.6 [June 2006]. So you are allowed to load any available extension simply using the following SQL statement:

SELECT load_extension('filename');

extensions actually are dynamic libraries adopting a conventional interface, and SQLite implements dynamic loading at run time simply invoking an SQL statement. Simple and nice, isn't it ?
And all this seems to open a very easy way to integrate SpatiaLite (and any other available extension as well) with any existing SQLite wrapper or language binding.
Really a smart design, do you agree ?

NO Very often this solution completely fails, and you'll get some puzzling error.
Let examine quite in-depth what is really happening:

when introduced for the first time the extensions where (correctly) classified as experimental
consequently, sqlite kept them disabled by default
and, in this case, disabled really means completely suppressed, in the most radical way.
in order to support extensions, -DSQLITE_ENABLE_LOAD_EXTENSION=1 has to be defined at compile time
if this is not true, then the resulting libsqlite will be one absolutely and irreversibly unable to supports extensions.
in earliest SQLite versions the ./configure was set so to disable extensions by default
the most recent SQLite versions inverted their standard behaviour, and now ./configure enables extensions by default

End of the story ? Not exactly ...
In recent times we have lots of pre-packaged distributions intentionally keeping extensions disabled.
Reasons accounting for this are easy to explain: security concerns prevail [after all, dynamically load extension may actually allow malicious software to be executed], and on this basis many packagers opt to eradicate the whole sqlite extension mechanism from scratch ... just to feel really sure.

I personally judge such security concerns to be a little bit paranoid ones.
Imagine a malicious hacker attempting to attack your system exploiting Sqlite and its dynamic extensions:

At first he has to develop an extension containing some malicious and harmful code.
developing a working sqlite extension isn't a so easy-to-do task; anyway may be he'll try to attempt anyway ...
Then he has to deploy the extension [technically a DLL or some other kind of shared library] on your system.
this doesn't seems an easy task, but if he is a really skilled hacker may well be ...
To bring the malicious code in execution, now you (or some application) have to perform two distinct actions:
- load the incriminated extension
  but this is not sufficient by itself, because simply loading the extension doesn't cause any code to be executed.
- so you are kindly required to collaborate again another time, invoking some SQL function supported by the malicious extension;
  and finally the harmful code injected by the hacker (with so many efforts) can gain execution ...

I can easily imagine lots of more simple and direct ways to attack a system.

Anyway, if this one is your case, I'm really sorry, but there is no possible solution.
Since the maintainer of your preferred sqlite-based software doesn't agree to support extensions, you will be completely stuck.
There is no way to interoperate such gelded sqlite and extensions; forget using SpatiaLite at all.

Happily it seems things are slowly going better; I recently tested the latest pysqlite-2 and I discovered it supports SpatiaLite with no problems.
Let us hope in the very next future enlightening will prevail over security concerns, so to allow the vast majority of sqlite-related software to support extension (and, consequently, SpatiaLite).

back