Relational sugar

Ever wanted to know why everyone talks about SQLite? Well, it’s simple like that: SQLite is damn small and very very fast. As a Senior Developer for Mayflower/Würzburg it was my pleasure (uhuum) to lead the Spare Parts Project for Vaillant Group. 100.000 CD’s are already shipped running PHP with SQLite locally – mind the licenses one have to pay choosing MySQL for the job… Anyway, figuring out the differences between MySQL (which is the storage layer for the Spare Parts web application) and SQLite (which is used for the CD variant) was pretty hard. This is what it’s all about: There are virtually many shops with different sets of tables in the background for country specific pricing data. If a country administrator wants a cd variant of the current shop, he simply starts the following process in a web form: import the MySQL tables in a SQLite file, package a minimalistic version of chairman together with the relevant databases, pass the whole bunch to an installer working on command line. The CD performance is overwhelming, it is even faster than the web version. SQLite works best in environments with very few DELETE/UPDATE/INSERT-Statements and much more SELECT statements. Think of a blog system, or *tadaaa* a Spare Parts Shop. I went through hell to figure out the following things which nowadays a very good documented, but not back in the days we started the project. You have to keep in mind:

SQLite is not a server process. „Good“, you might say, „that is what we all expected.“ The problem is that SQLite is not that scalable like – let’s say – MySQL. Missing a Query cache or the locking of tables makes it hard to implement applications which are concurrently used. Remote control also is insecure and slow in lack of a socket connection.

Forget about handling binary data the way you know it from your favorite database. You are forced to encode the data before you insert it and you’re also on your own with decoding.

Using transactions has a trivial consequence: All tables in the database file are locked, not only the one you’re working on.

Anyway, SQLite shipped with PHP 5 is a technological hootie. You can use it procedurally (if you were born before World War I) but it is also oo flavoured. Very nice as well: SQLite supports triggers und user defined functions. There are also several methods available to speed up SQLite. If you want to insert a whole lotta data you can use the statement „PRAGMA count_changes = 0“ which forces SQLite to skip the calculation of affected sets. Let me state: SQLite is „relational sugar“!