Using SQLite by Jay A. Kreibich
I’ve used SQLite in multiple projects and loved it. It’s fast enough for my applications: Appropriate Uses for SQLite.
Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
I first started using SQLite with Python because I thought there must be a better alternative to writing/reading a file for storage. That was SQLite which comes included with Python. Also I use SQLite basically for every web project I start – just because normally the project doesn’t scale over 50-100k hits, so I don’t really care about my DB as long as it works and is easy to set up.
I noticed in the last few months that I never really dove into databases. I want to change that and finally read a book from cover to cover about a database. I decided to choose SQLite just because I use it mainly. However, I still have one book in my reading list which goes into more depth regarding database design.
In-memory databases are often used to cache results pulled from a more traditional RDBMS server. An application may pull a subset of data from the remote database, place it into a temporary database, and then process multiple detailed searches and refinements against the local copy. This is particularly useful when processing type- ahead suggestions, or any other interactive element that requires very quick response times.
I never thought about doing this but I think it’s a nifty idea. You can easily create in-memory databases with SQLite and the interface can remain the same.
SQLite offers automatic indexing if you define a explicit primary key. You can still define other indexes for yourself.
I also learned that you can do the following:
INSERT INTO table_name (col1, ...) SELECT ...
Which allows you to copy a database or create a new one on the fly.
Also I learned a bit more about transactions. I only read about it especially in regarding to Clojure and STM funnily enough. But it was interesting to see that you can use savepoints, releases and different locking mechanism with SQLite.
There’s one chapter about nested structures in SQL. The author presents two possible approaches:
* Parent – Child relationship, basically [node_id, some attributes, parent_id] – easier to maintain, harder to traverse
* Otherwise using enumeration of depth-first traversal, basically [lower and upper bounds for each tree, which gets smaller in sub trees] – harder to maintain, easier to traverse
Plugins / Virtual table
The ability to write plugins rather easily is also one of SQLite special attributes. It allows you to write custom scalar and aggregate functions which can then be used in SELECT queries.
The idea of virtual tables are pretty cool. You can use SQLite with any format you want without importing the underlying data directly. For example, you could write a virtual table to handle logs or csv files.
The only problem I have is that it takes a while to write these and then you could either just dump them into the database or you would use a faster DB for bigger files. So I’m not quite sure when there’s a good application for that.
All in all, the book was an interesting read. I think the biggest payoff is for people working with SQLite and C.