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.

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;

PHP

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

sudo apt-get install php-sqlite3

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 etilqs.

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