Chapter 1 provided an overview of relational databases and SQL that applies to most modern databases regardless of the environment in which you find them (Oracle, MySQL, Microsoft SQL Server, or others). That generality is important because SQLite is part of that picture. In this chapter, however, the focus is just on SQLite and the features of SQLite that you may or may not find in other database environments. Whereas a lot of Chapter 1 may be familiar to long-time database users, much of this chapter may not be because even if it is not SQLite-only, many of the features described in this chapter are not present in other database environments. (To be quite fair, many features of other database environments are not found in SQLite.)

Putting a Database in Perspective

A relational database typically lives inside some kind of containing object. Often, the container is a database management system (DBMS). (You may sometimes see references to a relational database management system (RDBMS). For most purposes in today’s world, most DBMSs are RDBMSs.)

A database management system such as Oracle provides functionality that goes beyond the database itself, such as user interfaces or developer interfaces; diagnostic, debugging, and maintenance tools; and even sophisticated data display functionality.

In some cases, a database is part of a language, object-oriented class, or framework that, itself, may or may not be a DBMS. For example, PHP has a database class just as does the Android SDK (Yes, one is a language and one is an operating system, but each can provide an object-oriented database class.) You’ll find more about these classes in Chapters 6 and 7.

On the other hand, Cocoa and Cocoa Touch provide a Core Data framework for both iOS and Mac. I describe Core Data later in Chapters 8 and 9.

For most of this book, the focus is on SQLite itself, but beginning with Chapter 6, you’ll see how to access it when it is inside a DBMS, class, or framework.

Defining SQLite

SQLite is a software library written in C. It was developed by D. Richard Hipp in 2000 originally as part of a contract with the U.S. Navy that was implemented by General Dynamics. Today, it consists of some 184,000 lines of code.

SQLite is in the public domain, so it can be used by anyone. Further details and links to download the source code are available at sqlite.org.

You can compile the source code into a library that, in turn, you can use in an application program. Although you may find references to SQLite as a DBMS, it is, strictly speaking, just this library. The container in which that library is compiled (a class, a framework, or a full-fledged DBMS) provides the larger DBMS functionalities.

The code has been designed from the beginning to be compact and reusable (that reference to its origin with the U.S. Navy is relevant here—when you’re on a ship at sea, every resource is limited, including power, space, and weight. Furthermore, in modern ships, technological components must function together even if their origins are with multiple vendors. That’s the SQLite environment.

Whether you are worried about the limited resources on a ship, on a mobile device running an operating system such as Android or iOS, or on a small mobile device such as a programmable beacon . . . or even the limited resources on a supercomputer (“limited” is always relative), SQLite is a good choice in many cases.

The following sections highlight some of the major features of SQLite that implement this mandate and that are important to you as you use it in whatever container you choose (DBMS, class, framework, or basic library). These features are important to you as you undertake a SQLite-based project for a mobile device.

The features discussed in this section actually all revolve around the fact that SQLite is a library that is designed to be used by a single user to handle database functionality. (Before you throw up your hands, read on to see how SQLite can function very well in a multiuser and multiprocess environment.) The following are the features to consider:

• SQLite is designed for a single user
• SQLite is self-contained
• SQLite supports transactions and is ACID-compliant

SQLite Is Designed for a Single User

One of the biggest differences between SQLite and most DBMSs is that SQLite is designed for a single user. Most DBMSs manage multiple users including various security features that allow or block access to specific SQL commands and features. Even more important to many people, a DBMS manages contention for resources so that several users can apparently use the same data concurrently.

Apparently is the key word here because although each user typically thinks that he or she has unique access to the database, in fact, behind the scenes the DBMS manages concurrency so that in some cases, it has locked a record to prevent access to it by a second user while another user is updating it.

Single User Doesn’t Mean Single-Thread

SQLite manages concurrency within its own environment. This means that it may have multiple threads running at the same time to perform its own tasks, but those threads are managed within the SQLite environment itself. They do not represent separate users.

Using SQLite with Multiple Users

How can you have multiple users when SQLite is designed for a single user? The answer is simple: you manage multiple users yourself. There are a number of ways of doing this, but in general, what you do is to push the multiuser management onto the app, class, or language into which SQLite is embedded. Apps typically have the ability to communicate with one another (subject to security and platform constraints). Thus, although SQLite is not going to manage the case of User A and User B attempting to modify the same data at the same time, your app can do so.

Typical concurrency strategies involve either having a master process that manages the concurrency or having a mechanism whereby multiple independent processes communicate without a master process. You find many examples of multiple independent processes in apps such as Dropbox and in many cloud-based apps. (In the case of Dropbox and cloud-based apps, there may be a process continually running somewhere in the cloud and, perhaps separate processes running on active clients. Whether or not control resides in a central process or is distributed among the clients (and master) varies depending on the specific implementation.)

Thus, SQLite is perfectly capable of functioning in a multiuser world; it just needs to be running inside apps or other processes that themselves implement the multiuser features.

SQLite Is Self-Contained

SQLite is self-contained in two ways.

• The code itself is self-contained ANSI-C code. It makes minimal use of C libraries. In fact, the only ones it uses are
  • memset()
  • memcpy()
  • memcmp()
  • strcmp()
  • malloc()
  • free()
  • realloc()
• The data store itself is self-contained, portable, and platform-agnostic.

Self-Contained Code

Self-contained code means that when you include the SQLite library in your project (either directly or through a language, class, or framework), you have everything you need. You don’t need to include additional libraries.

You don’t have to worry about versions, and, once you have a compiled SQLite library, you can generally reuse it without being dependent on changes in components. (Remember, though, that in most cases SQLite is embedded in a language, class, framework, or DBMS so the container is what you will need to update from time to time.)

Because SQLite is in the public domain, you don’t have to worry about licenses or license fees. (You can, however, obtain a license as described on sqlite.org. That option is provided for some users who need to demonstrate to their management that they actually have the right to use SQLite.)

Remember that SQLite is often contained within a class, framework, or language that you are using so this feature (which to a large extent makes such containability possible) may not be visible to you.

Self-Contained Data

The data store that SQLite uses for a database is designed to be self-contained and cross-platform. This means that you can transfer a SQLite file from one environment to another without problems in most cases. (That caveat is necessary because you may encounter issues in specific configurations, but for many if not most SQLite users, the first step in verifying that a database can be moved is to actually move it—this usually works.)

In its most basic (and original) form, each SQLite database is stored in a single file. The file is readable and writable on any platform (subject to environmental constraints on the file). For example, if you place the file on a read-only disk, you can’t write to it with SQLite. (This may sound far-fetched, but such things do happen.)

With the database in a single file, users can see it and move it around if they want. (If they do so, the app that you are building must be able to find the file—perhaps with the user’s help.) Users can also delete the database file. Because it is self-contained, that’s very simple, and it doesn’t corrupt other SQLite databases. Of course, the fact that a user may be able to delete the database means that in your implementation, you may want to hide it or place it in protected places. If you do that, you often take advantage of platform-specific features such as hidden folders, but the SQLite file itself remains an ordinary file that can be moved around and copied to other devices.

Because the database file is self-contained, it contains both the database data and its schema (structure if you’re not from the database world). In part because each database is self-contained, SQLite is referred to as serverless (there is no separate server process).

The fact that a SQLite database can be stored in a single file leads to a common use of SQLite to implement structure and database functionality inside what appears to the user to be a simple file.

SQLite Supports Transactions and Is ACID-Compliant

Transaction in the world of databases has a very specific meaning over and above its regular meaning in English. It refers to a set of steps that are done together (usually in sequence, but not necessarily so). Together, these steps are a transaction, and what’s important is that the transaction as a whole either succeeds or fails. If any of the steps fails, the entire transaction fails. As a result of transaction failure, the database is said to be rolled back to its condition before the transaction began.

When a database supports transactions, the failure of any part of a transaction means the entire transaction fails and the database is set to whatever it was before the transaction started. All of this happens in an ACID-compliant database such as SQLite. As you saw in Chapter 1, there’s a lot of code you don’t have to write when you’re using a database.

ACID is the standard by which most databases are judged today to determine if they support transactions. ACID is an acronym.

• Atomicity. This is the all-or-nothing aspect of a transaction. It succeeds or fails as a whole and can never be partially successful.
• Consistency. In processing a transaction, the database will start from a valid state and end in a valid state. In other words, a transaction will not violate database rules such as the optionality of attributes as a result of a transaction, but those rules may be violated during the course of the transaction.
• Isolation. This means that transactions can be run sequentially or concurrently. They will not interfere with one another. (Isolation requires that a database implement a method for managing concurrency so the operations can be either sequential or concurrent.)
• Durability. This means that, on completion, the transaction persists. In practical terms, this generally means that it is committed to disk.

In SQLite (as in many other implementations of SQL), a transaction consists of a number of SQL statements bracketed by

BEGIN TRANSACTION

and

END TRANSACTION

There is more on transactions in Chapter 4. You’ll see how to define them and how to specify the point to which a failed transaction is rolled back. In that chapter, you’ll also see a larger description of how WAL implements atomicity and durability.