SQLite is a C library that implements a self-contained, serverless SQL database engine. Data is stored in a single file per database. Permissions are left completely to the filesysem. SQLite is popular for embedded and single-user applications. There is also a command-line client program.
Used by FireFox and Skype, and a version ships with OSX.
Because SQLite writes temporary files, any process writing to a database file also needs to have write privileges on the directory that database file is in.
SQLite databases can be very fast and efficient in applications that mostly involve reading. Simultaneous writes can be problematic because each write locks the database file.
SQLite is very permissive. Column types and foreign keys are more suggestions than constraints. Queries with erroneous grouping may still return results, though they can be incorrect. As a result, more rigour is required in the application.
SQLite's query planner doesn't seem to take the selectivity of an index into account, hence dropping indices with low selectivity may speed up queries (e.g. if you have an index for the color of eyes of a person and another for the name, SQLite may well use the eye color index instead of the name index on SELECT eye_color, name FROM people WHERE name = 'Richardson' and eye_color = 'brown'.)
Watch out when porting code between Win32 & OSX. A wchar_t is 16 bytes on Win32 and “just works” with the sqlite *text16 functions. On OSX, a wchar_t is 32 bytes, which obviously won't work quite as simply. My advice – store everything in utf8 in the database and convert it to whatever wide char representation you want when you remove it.
SQLite v2 uses a file format that cannot be read by SQLite v3. Conversion:
sqlite2 old.db .dump | sqlite3 new.db
BerkeleyDB, MySQL, PostgreSQL, DBM, flat files
