CHAPTER 6 – SQLITE

PHP 5 introduced a new bundled and, by default, an available "database" engine called SQLite. 6.3.1 SQLite Strengths and Weaknesses This section describes the characteristics of SQLite compared to other DBM- Ses. 6.3.1.1 Strength: Self-Contained, No Server Required SQLite does not use a client/server model. It is embedded in your application, and only requires access to the database files. This makes integrating SQLite into other applica- tions easier because there is no dependency on an external service. 6.3.1.2 Strength: Easy to Get Started S e t t i n g u p a n e w d a t a b a s e w i t h SQLite is easy and requires no intervention from system administrators. 6.3.1.3 Strength: Bundled with PHP 5 The entire SQLite engine is bundled with PHP 5. There is no need to install extra packages to make it available to PHP developers.

6.3.1.4 Strength: Lightweight and Fast The newest of the databases covered in this chapter, SQLite has little compatibility baggage and still has a lean and light design. For most queries, it is on par with or exceeds the perfor- mance of MySQL. 6.3.1.5 Strength: Both a Procedural and an OO Interface SQLite's PHP ex- tension features both procedural interfaces and an object-oriented interface. The latter makes it possible to have less code, and is, in some cases, faster than its procedural alternative. 6.3.1.6 Weakness: No Server Process Although this is one of SQLite's strong points, the fact that SQLite has no server process leads to a series of scaling difficulties: file locking and concurrency issues, lack of persistent query caches, and scaling problems when handling very large data volumes. Also, the only way to share a database between hosts is to share the file system with the database file. This way of running remote queries is much slower than sending queries and responses through a network socket, as well as less reliable. 6.3.1.7 Weakness: Not Binary Safe SQLite does not handle binary data natively. To put binary data in a SQLite database, you first need to encode it. Likewise, after a SELECT, you need to decode the encoded binary data. 6.3.1.8 Weakness: Transactions Lock All Tables Most databases lock indi- vidual tables (or even only rows) during transactions, but because of its imple- mentation, SQLite locks the whole database on inserts, which makes concurrent read/write access dramatically slow. 6.3.2 Best Areas of Use SQLite's primary point of excellence is that it is stand alone and extremely well suited for web-hosting environments. Because the SQLite client works on files, there is no need to maintain a second set of credentials for database access; if you can write to the database file, you can make changes in the data- base. Hosting companies just need to support the SQLite PHP extension, and their customers can take care of the rest. A hosting company can limit the maximum size of databases (in combi- nation with other data in the web space) easily because the SQLite database is just a file that takes space inside the web space of its customer. SQLite excels at stand alone applications. Especially in web-hosting environments where there are many read queries and little write queries, the speed of SQLite is fully shown. An example of such an application might be a weblog where all hits pull out comments from the database, but where only a few comments are added.

6.3.3 PHP Interface In this section, we present a full-fledged example using most of SQLite's fea- ture sets. Each subsection introduces you to a new step in building an auto- matic indexed email storage system. We use the OO-based API in the examples, but also mention the procedural equivalent. The way this works is similar to the MySQLi extension.