Running SQLite on Andrew File System (AFS)

For my project CAMxRunner, I needed fast and reliable storage for runtime data. I started out with simple files, but it became cumbersome.

SQLite is a nice alternative, especially because it supports standard SQL and beyond. However, there are a number of things to keep in mind when running it on AFS, because AFS has no support for byte-range locks currently. This means that SQLite needs to be compiled in such a way that file locks (flock) are used:

	ifeq ($(strip $(DESTINATION)),)
	DESTINATION = ../..
	endif
	# Compiler to use
	CC = gcc
	CFLAGS = -O2
	all: sqlite3 libsqlitefunctions.so
	# Standard compilation
	sqlite3: shell.c sqlite3.c
	$(CC) $(CFLAGS) $^ -o $(DESTINATION)/$@-$(HOSTTYPE)$(SUFFIX) -DDSQLITE_ENABLE_LOCKING_STYLE -DSQLITE_FIXED_LOCKING_STYLE=flockLockingStyle -ldl -lpthread
	# The math library
	libsqlitefunctions.so: extension-functions.c
	$(CC) -fPIC -lm -shared $^ -o $(LIBDIR)/$@$(SUFFIX)
	clean:
	rm -f *.o $(DESTINATION)/sqlite3-$(HOSTTYPE)$(SUFFIX) $(LIBDIR)/libsqlitefunctions.so$(SUFFIX)

view raw gistfile1.mak hosted with ❤ by GitHub

Scary (and wrong) error message
When SQLite was compiled as shown, it has the nasty habit of issuing the error "I/O error" instead of "Database locked". If you are data-sensitive like me, this error may scare you somewhat, but my tests so far have shown that it is just the wrong text being issued.

Maintenance tasks
Like any database, SQLite needs a bit of maintenance (of course, considerable less than, say, Oracle).
What I do in my application at every startup:

• Issue PRAGMA integrity_check; 
• Rebuild of indexes    

Both operations are very fast, but I think it is better to do it regularly. Index rebuild can be done very easily in SQLite - this is how to get the SQL:

	-- SQLite keeps all DDL code in its data dictionary table "sqlite_master"
	SELECT sql FROM sqlite_master WHERE type='index';

view raw SQLite rebuid indexes hosted with ❤ by GitHub

SQLite extensions 
The above makefile also shows how to compile the extension-functions.c.
This file provides functions otherwise not known to most relational databases (from the source):

Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference,
degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp,
log, log10, power, sign, sqrt, square, ceil, floor, pi.

String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim,
replace, reverse, proper, padl, padr, padc, strfilter.

Aggregate: stdev, variance, mode, median, lower_quartile,
upper_quartile.

Conclusion

SQLite is a neat database system that provides much more than simple configuration files. It is almost maintenance-free (besides the index issue).