SQLite

An SQLite database is one file

A really cool feature of SQLite is that a database is really just one ordinary file.

This file can be copied from one computer to another, even from Windows to Linux and vice versa.

At times, there is no need to even have a file. In such cases, a transient SQLite database can be created completely in memory.

SQLite runs without server

SQLite does not require a server installation.

Opening/connecting to other SQLite databases

Use .databases to show currently opened (attached) databases

sqlite> .databases
main: /home/rene/my.db r/w

Attach another database:

sqlite> attach database '/home/rene/foo.db' as foo;

We operate on two databases now:

sqlite> .databases
main: /home/rene/my.db r/w
foo: /home/rene/foo.db r/w

Create table in foo.db:

sqlite> create table foo.x as select 42 num, 'hello world' txt;

sqlite> detach database foo;

.quit

$ sqlite3 /home/rene/foo.db 'select * from x;'
42|hello world

Turning off autocommit

SQLite commits every statement by default (so called auto commit).

To turn this behaviour off (which increases performance greatly when lots of statements are executed in sequence), the statements must be enclosed in begin transaction .. commit transaction:

begin transaction;

insert into tab_something values ('foo', 'bar', 'baz');
insert into tab_something values ('one', 'two',  null);
…
…

commit transaction;

pragma

pragma quick_check;

Compiling the amalgamation

«Default» (no optimization etc.) compilation Shell:

Compiling object files from sqlite3.c

First, we need a few variables:

AMALDIR=sqlite
OBJDIR=/tmp
BINDIR=~/bin

We're now ready to create the object files:

gcc -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_THREADSAFE=0 -Ofast -c $AMALDIR/sqlite3.c -o $OBJDIR/sqlite3-thread-0.o
gcc -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_THREADSAFE=1 -Ofast -c $AMALDIR/sqlite3.c -o $OBJDIR/sqlite3-thread-1.o
gcc -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_THREADSAFE=2 -Ofast -c $AMALDIR/sqlite3.c -o $OBJDIR/sqlite3-thread-2.o

Compiling the shell executable

Note, -lpthread is required if the object file was created with the macro SQLITE_THREADSAFE set to 1 or 2:

gcc $AMALDIR/shell.c $OBJDIR/sqlite3-thread-0.o -ldl           -o $BINDIR/sqlite3-shell-thread-0
gcc $AMALDIR/shell.c $OBJDIR/sqlite3-thread-1.o -ldl -lpthread -o $BINDIR/sqlite3-shell-thread-2
gcc $AMALDIR/shell.c $OBJDIR/sqlite3-thread-2.o -ldl -lpthread -o $BINDIR/sqlite3-shell-thread-2

Make a particular executable the default:

ln -s $BINDIR/sqlite3-shell-thread-0 $BINDIR/sqlite3-shell

sqlite3-shell :memory: 'pragma compile_options'

No readline support

Unfortunately, when compiled as outlined above, there is no readline support.

It seems it needs to be enabled already when configuring the project.

PHP

It seems sqlite can be accessed (in Ubuntu) from PHP with

sudo apt-get install php-sqlite3

Using PowerShell to find SQLite databases in a file system

The following simple PowerShell pipeline finds SQLite databases below the current directory in a file system:

get-childItem  -recurse -attributes !directory |
   foreach-object {
      $firstCharacters = ([char[]] (get-Content $_.fullName -encoding byte -totalCount 15)) -join ''
      if ($firstCharacters -eq 'SQLite format 3') {$_}
   }

Trivia

The default prefix for temporary files used to be sqlite_ but was changed to etilqs_ so that annoyed users wouldn't find the phone numbers of the SQLite developers when googling for sqlite.

TODO

Forking, threads and caching statements across processes

The FAQuestion Is SQLite threadsafe recommends not to fork() an open SQLite database into a child process.

sqlite3_open_v2 called with with one of the following flags:

Flag	threading mode
`SQLITE_OPEN_NOMUTEX`	multi-thread	Multiple threads can use the same database provided each thread has its own database connection.
`SQLITE_OPEN_FULLMUTEX`	serialzed	Multiple threads can use the same database, critical code is secured with mutexes.

The value define/macro SQLITE_THREADSAFE specfies the threading mode at compile time.

This thread has some interesting insights.

Links

SQLiteForExcel (on github) is a small, easy-to-use, open source SQL library to give access to sqlite from VBA.

Open Source SQLite ODBC Driver

SQLite archives might be a really cool replacement for zip or tar files.

The .NET namespace System.Data.SQLite

An experiment that tries to load the OpenStreetMap database into SQLite.