SQLite is the most popular tiny database engine and has made its way into almost every computing device. Smartphones, either running Android or iOS, are likely to have apps that use it to store data. This article explores SQLite and how it can be used to store your application data.
Although RDBMS’ are now four decades old and have matured, things are beginning to change in the realm of database management systems, with the entry of NoSQL and NewSQL data management paradigms. However, enterprise efforts to shift to these new paradigms for their exploding data have been slow, owing to the relative maturity of RDBMSs. At the other end of the spectrum is this tiny SQL db engine that has been dominating its space. SQLite started out as a project to develop an embedded database with a design motive of not requiring any installation or administrator for maintenance, for a United States Navy program. It’s now widely used in mobile and Web-based desktop applications. The database engine is as small as 350 KB and supports most of the SQL 92 standard. It was developed by D Richard Hipp, and later the code was released to the public, making it one of the highly active open source projects. Its relatively small size and the wide open source community support have been very appealing for developers, leading them to embed it in their applications. Although the database has official bindings for C/C++ and TCL only, the community-supported bindings are available for as many as 25 languages.
Distinctive features
The database requires no configuration to set up, and the entire data, as well as any configuration needed, is stored in a single flat file on the disk. This was the main motive behind its development. Also, the database can explode up to the maximum file size on the underlying filesystem.
It is serverless and does not require any process of its own running and so can be statically linked or embedded in the application itself. It can also be used as an in-memory database.
It supports full text search and is also ACID (atomicity, consistency, isolation, and durability) compliant. It is implemented in C, and works on any platform. It is available as part of the OS in smartphone OSs like iOS and Android.
SQLite has gained a large user base because of its large community and the availability of bindings for all the recent popular languages.
For Android, Google has embedded the database engine in the OS itself, and has a Java API for applications to use. Apple also adopted it for iOS and made it a part of the core Data API in both iOS as well as in Mac OS X 10.4, which enables developers to use it in both iOS as well as desktop apps. In the case of desktop Web apps, Firefox uses SQLite widely for storing bookmarks and cookies. Chrome and Opera use it for storing internal configuration and WebSQL databases. Skype and Adobe also use it for storing internal data. There is a Firefox add-on, SQLite Manager, to view, create, edit, and update SQLite databases, which is available from the Firefox add-ons site. And an Android application to view, create and edit SQLite databases is also available in the Play store.
Using SQLite in lightweight PHP Web apps
SQLite is part of PHP releases from 5.0 till 5.3. From version 5.4 onwards, SQLite is available as a PECL extension. SQLite and PDO drivers must be installed and enabled (extension=php_pdo.so, extension=php_sqlite.so in /etc/php.ini). To check the status, use the following code:
$ echo '<?php phpinfo() ?>' > test.php $ php test.php | grep -i sqlite PDO Driver for SQLite 3.x => enabled SQLite Library => 3.6.20 sqlite3 SQLite3 support => enabled
Step 1: Create a databaseeither as a file or in-memory. If created in a file, ensure that the apache/www user has RW access to the file.
//any name other than ‘:memory:’ is identified
//as a database in a file.
$in_mem_db = new PDO(‘sqlite::memory:’);
Step 2: Create tables in the DB to track OSFY visitors and the number of their visits:
$in_mem_db->exec("CREATE TABLE IF NOT EXISTS osfyReaders(
id INTEGER PRIMARY KEY,
name TEXT,
visits INTEGER)");
Step 3: Create an array containing a list of users and bind the variables to the columns of the table, and fill in the details:
// prepare insert statement
$insert = "INSERT INTO osfyReaders (name, visits)
VALUES (:name, :visits)";
$stmt = $in_mem_db->prepare($insert);
// Bind parameters to statement variables
$stmt->bindParam(':name', $name);
$stmt->bindParam(':visits', $visits);
// Loop through all visitors and execute prepared insert statement
foreach ($readers as $r) {
// Set values to bound variables
$name = $r['name'];
$visits = $r['visits'];
// Execute statement
$stmt->execute();
}
Step 4: Now retrieve the data from the table with a query, and display it:
// Select all data from in memory db osfyReaders table
$result = $in_mem_db->query('SELECT * FROM osfyReaders');
foreach($result as $row) {
echo "Id: " . $row['id'] . "\n";
echo "Name: " . $row['name'] . "\n";
echo "Visits: " . $row['visits'] . "\n";
echo "\n";
}
Step 5: Close the connection to the database.
$in_mem_db = null;
The SQLite package also provides a command-line tool, sqlite3, which can be used to explore all the capabilities of the database. To list all available options, enter.help’ at the prompt.
Limitations
SQLite does not implement the complete SQL 92 standard and hence it has some limitations:
ALTER: Altering the table is not completely supported. Only renaming and adding columns to the table is possible, which makes sense as the schema remains the same for the lifetime of the embedded application. As the database is not expected to be large, to overcome this limitation, a new database can be created and data can be copied.
JOIN: Only the left outer join is supported. The unsupported right outer join can be done by executing a left outer join on the reversed list of tables in pairs.
Triggers: Triggers are supported only for rows.
Views: The Insert, Update and Delete operations are not permitted on views in SQLite, which can be overcome by triggers. Search the SQLite website for more details on this limitation.
SQLite is optimised for performance by the imposition of certain limits, both at compile time and runtime, like BLOB size, number of columns, number of tables in a join, etc.
Older versions of SQLite may have more limitations, like foreign key constraints are supported only from 3.6.19, etc.
Alternatives
Some of the alternatives, though not as lightweight as SQLite, are:
HSQLDB: Written in Java, and so requires JRE.
Firebird: A cross-platform RDB that has full SQL92 conformance; it is lightweight, but not an embedded database.
Ultralite: A cross-platform commercial product from Sybase.
References
[1] http://www.sqlite.org
[2] http://www.if-not-true-then-false.com/2012/php-pdo-sqlite3-example/
[3] A complete comparison of the known databases are maintained at http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
[4] Android tutorial on SQLite https://www.opensourceforu.com/2010/12/android-application-development-playing-with-sqlite-database/
