SQLite 3 Notes

Some notes when working with SQLite 3

SQLite 3 Compile Time Options And Pragma

SQLite has multiple build flag and pragmas that can be used to enable / disable certain functionality. Due to this, it's important to remember if certain functionality exist on your target servers

For example, on my local machine:

sqlite> PRAGMA compile_options;
COMPILER=gcc-9.3.0
ENABLE_DBPAGE_VTAB
ENABLE_DBSTAT_VTAB
ENABLE_EXPLAIN_COMMENTS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
ENABLE_STMTVTAB
SYSTEM_MALLOC
THREADSAFE=1

On hostinger (shared hosting):

sqlite> PRAGMA compile_options;
DISABLE_DIRSYNC
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
SECURE_DELETE
TEMP_STORE=1
THREADSAFE=1

No JSON1 and FTS5 is probably problematic. Sadly on Shared Hosting you can't change system components... so you're stuck

In case you have control over the system, in order to make sure additional flags is enabled, do it on the configure step. Here's the recommended one that has plenty of features for concurrent server user-cases (PHP)

CFLAGS="$CFLAGS -DHAVE_READLINE \
-DSQLITE_THREADSAFE=2 \
-DSQLITE_DEFAULT_MEMSTATUS=0 \
-DSQLITE_DEFAULT_SYNCHRONOUS=1 \
-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 \
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS \
-DSQLITE_MAX_EXPR_DEPTH=0 \
-DSQLITE_USE_ALLOCA \
-DSQLITE_OMIT_GET_TABLE \
-DSQLITE_OMIT_TCL_VARIABLE \
-DSQLITE_OMIT_DEPRECATED \
-DSQLITE_OMIT_PROGRESS_CALLBACK \
-DSQLITE_DEFAULT_CACHE_SIZE=-64000 \
-DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
-DSQLITE_ENABLE_STAT4 \
-DSQLITE_ENABLE_UNLOCK_NOTIFY \
-DSQLITE_TEMP_STORE=2 \
-DSQLITE_DEFAULT_MMAP_SIZE=268435456" \
./configure \
--prefix=$HOME/package/sqlite-3.41.1 \
--disable-fts4

make -j $(nproc)
make install

PRAGMA

Use WAL mode (writers don't block readers):

PRAGMA journal_mode = 'WAL'

Use memory as temporary storage:

PRAGMA temp_store = MEMORY

Faster synchronization that still keeps the data safe:

PRAGMA synchronous = NORMAL

Increase cache size (in this case to 64MB), the default is 2MB

PRAGMA cache_size = -64000

Update page size

PRAGMA schema.page_size = 4096

Enable Foreign Keys optimization

PRAGMA foreign_keys = true

Set busy timeout, otherwise writes will fail

PRAGMA busy_timeout = 5000

Enable mmap

PRAGMA mmap_size = 268435456

Ruby sqlite3 gem using your own compilation

Using gem only

gem install sqlite3 -- --with-sqlite3-dir=/home/rizalp/package/sqlite

Or if using bundler

bundle config build.sqlite3 --with-sqlite3-dir=$HOME/.local

Ruby sqlite3 gem using homebrew version

gem install sqlite3 -- --with-sqlite3-include=/usr/local/Cellar/sqlite/3.27.1/include --with-sqlite3-lib=/usr/local/Cellar/sqlite/3.27.1/lib

Building sqlitebrowser With Preinstalled / Your Custom Sqlite

In case you built your own sqlite, thorough manually building it (configure, make, make install) and want to link sqlitebrowser against it, you have to build sqlitebrowser yourself.

Download sqlitebrowser

This will install dependencies for this build process and download latest master branch from github

sudo apt install aria2c build-essential git-core cmake qt5-default qttools5-dev-tools \
    qtbase5-dev libqt5scintilla2-dev libqcustomplot-dev qttools5-dev
aria2c https://github.com/sqlitebrowser/sqlitebrowser/archive/refs/heads/master.zip
unzip sqlitebrowser-master.zip -d .

Configure sqlitebrowser

cd sqlitebrowser-master

You need to edit the find_library of the CMakeLists.txt to add hints on where to look for the sqlite lib and headers. Modify this to wherever your sqlite build is installed

	find_library(LIBSQLITE ${LIBSQLITE_NAME} HINTS /usr/local/lib /usr/local/opt/sqlite/lib /home/rizalp/package/sqlite/lib)
	set(ADDITIONAL_INCLUDE_PATHS /usr/local/include /usr/local/opt/sqlite/include /home/rizalp/package/sqlite/include)

Build sqlitebrowser

Finally, you could build it. Modify CMAKE_INSTALL_PREFIX:PATH to install it on wherever directory you desire. I simply install it on my home dir to not having to use sudo when doing make install

mkdir build
cd build
cmake -Dsqlcipher=0 -DCMAKE_INSTALL_PREFIX:PATH=/home/rizalp/package/sqlitebrowser-master -Wno-dev ..
make -j $(nproc)
make install

Run sqlitebrowser

The final result will be executable on the CMAKE_INSTALL_PREFIX:PATH/bin/sqlitebrowser. You can add it to $PATH.

Desktop Integration

To add the generated binary to start menu for easy access, do:

xdg-desktop-menu install --novendor share/applications/sqlitebrowser.desktop
xdg-icon-resource install --size 256 icons/hicolor/256x256/apps/sqlitebrowser.png

rizalp/sqlite3-notes.md