Ask people to tell you words they associate with database and you’ll probably get big among the responses. Databases handle large amounts of data, and everyone knows that. How big? It depends whether you count the number of items in a database or the size of the items. The Library of Congress has 160 million items on 858 miles of shelving as of this writing. The catalog, which is based on a database, is available online at www.loc.gov/about/fascinating-facts/. There’s no question among people who are interested in databases that the Library of Congress catalog is far from the largest database in the world. (In all likelihood, the largest databases are not visible to the public because they contain classified corporate and governmental information.)

This chapter provides an introduction or reminder about databases today and how they are used. If you’ve used databases in the past, much has changed, and if you haven’t used them much in the past, this chapter will give you a quick overview.

Moving Beyond Big

Beyond big, you should start thinking about databases as being structured and organized. In fact, as a mobile developer, the structured and organized aspects of databases are much more important than their size. From the earliest days of the Web in the early 1990s, web browsers have used databases to store and organize their data. What data, you may wonder? How do you think your browser is able to store your passwords for the web sites you visit? How do you think browsers store web pages in a cache so that they can be retrieved without a new network access when possible? And what about your preferences for default font sizes? Or the download folder for files you can change when you feel like it, and even, on many browsers, restrictions on certain users or types of users? These are just simple examples, but the principles apply even to the largest (and smallest) databases today.

Databases Are Structured and Organized

All those examples can be implemented with a database because all those items need to be structured and organized. Some of the items can turn into large amounts of data (particularly a browser history if you don’t choose an option to clear out the old information occasionally—another option that can be stored in a preference database), but some of the preferences listed are very small amounts of data: even with a fully modified file name for your download folder, 128 characters is enough to store that data element. Furthermore, most browsers store the name of your preferred download folder, but they don’t store the history of download folders (last week’s preferred download folder, the folder you used last month, etc.).

In thinking about databases, “big” is often relevant, but very frequently it’s misleading. It is structure and organization that matter most.

And they matter a great deal to a mobile developer.

Precisely because mobile devices need to function in a world of constrained resources (e.g., limited storage space and battery power), structuring and organizing the data that is stored is particularly important. Built into a data management system are various optimizations that matter to databases large and small. For example, many databases can store character or string data. Such data is typically quite variable. Even if a database designer specifies that a text field can contain 50, 500, or 50,000 characters, behind the scenes trailing blanks are often discarded. The user (and even the developer) may never know this, but it makes the entire database function more efficiently. (Features like this function behind the scenes and they can be accessed and turned on or off by the database designer in many cases.)

Databases Are Smart

Being able to optimize storage to work with the presence or absence of characters in a text field is a powerful behind-the-scenes tool, but databases have intelligence built into them.

In most database engines today, the designer can specify many attributes of a field such as the following:

• Name. A name that is used internally is often quite different from the name that appears in the user interface.
• Type. You can specify that a certain database field must be an integer or a string or any other type that the database supports.
• Optionality. Some fields are optional, and you can specify that when you set up a database. (Many people have a car with a license number; many other people do not own cars.)
• Default values. A database can be set to provide a default value—either a simple value or a calculated value based on other data in the database.
• Cardinality. Sometimes, there are multiple values for part of the database. (To continue the example, some people have no car, other people own a car, and others own several cars. Furthermore, some people own a car and a bike . . . you get the idea.)
• Value ranges. When the database designer specifies the database structure, a range of values for each field can be set. A driver’s license identifier that consists of characters other than letters and numbers (depending on the locality) may not be allowed.
• Value relationships. You can move beyond value ranges in designing a database. You can create combinations of attributes and values so that, for example, a database can enforce a rule that an employee may be married (to one person or no one), but if the employee is married, the spouse’s employer cannot be the same as the employee’s employee. These restrictions in the database reflect policies, but there well may be exceptions (a supervisor’s approval), and the database can be designed to support a case like that.

These are just a few of the ways in which a database can be set up to enforce logical rules.

Perhaps at this point you’re tempted to say, “But I can do each of those things with a line or two of code.” That’s absolutely true. But there are a couple of points to bear in mind.

Writing Code Is Just the Beginning

You can write the code to implement any of those conditions, but that’s just the start. Over time, conditions change, users have suggestions, and the Power That Be (whoever that (or “they”) may be in your life) decides to change the rules for employment, cars, the conditions that apply to license plate numbers, or any other things that strike his or her fancy.

The code that took only a few lines of code and a couple of minutes to write needs to be rewritten. And, although there is no clear research on the matter, it seems that these changes take place at the last minute before a product (or version) launch and they must be done immediately.

Parlez-vous Python? Sprechen Sie Scala?

The line or two that implement the rules for one of the data conditions may have been written by someone who’s no longer around. Even if you wrote them, time may have passed and you may not remember exactly what you were trying to implement (yes, commenting your code is a desirable feature, but life happens . . .)

Those few lines of code may have been modeled on some code from a friend that does exactly (or almost) what you want. Maintaining even the best-written code is a challenge for everyone involved from the first author to the updater to the manager and—eventually the user or manager who asks for “just a little tweak.”

Relational Databases and SQL to the Rescue

Today, databases are remarkably standardized in their structure—perhaps more so than almost any other concept in the world of information technology. The idea of a relational database was first proposed in 1970 by Edgar Codd, of IBM’s San Jose Research Laboratory, and his proposal has become the basis for modern database implementations (see article titled “A Relational Model of Data for Large Shared Data Banks,” Communications of the ACM, 13(6), 377–387 (June 1970)).

In 1974, Donald D. Chamberlin and Raymond F. Boyce (also from IBM) wrote a paper titled “SEQUEL: A Structured English Query Language” for the ACM (Association for Computing Machinery) SIGFIDET Workshop on Data Description, Access and Control.

The relational model (proposed by Codd), together with SQL, has become the basis for modern database implementations. Books by C. J. Date and others together with Codd’s original work on the relational model remain the cornerstones of relational database theory today.

The basic concepts of SQL are few. You can visualize them by thinking of a spreadsheet with its rows and columns. (You can also conceptualize them using mathematical set theory and other concepts, but spreadsheets are easier for many people to think about today.)

Following are the basic SQL concepts:

• Table. A table is much like a simple spreadsheet with rows and columns. (It’s not like a complex spreadsheet that may include several tables. For this discussion, think simple—just rows and columns in a single table.) A table is sometimes referred to as a relation, but more often table is used.
• Column. A column represents a single data element such as “address” or “name.” In the database world, a column may be called a field. In the world of programming, a column may be called an attribute or property.
• Row. A row represents single observations or set of values with one for each column. Thus, a row in this imaginary simple spreadsheet might represent a person’s data. In the database world, a row may be called a record or tuple.

To retrieve data from a relational database table, you run a query. A query is a set of logical instructions that manipulate a given table in such a way as to retrieve the data that you want. The result of a query is another table. The resulting table may be empty (if no data satisfies the query), it may be some or all of the data in the table, and, in some complex queries, it may be larger than the basic table. The results of a query are sometimes called a view or a result set.

These concepts and terms apply to most databases today regardless of the language in which they are implemented and, perhaps more important, regardless of the database engine or database management system (DBMS) in which they are implemented. Furthermore, databases are sometimes implemented as part of frameworks and languages. Thus, in PHP, you can use PDO (PHP Data Objects) starting with PHP 5.1 to access SQLite. For iOS, you can use the Core Data framework to work with SQLite and other data managers. In these cases as well as others, the idea has been to abstract as much as possible into a framework or other wrapper so that switching databases does not require massive rewrites of code.

SQLite implements most of the SQL standard. The exceptions are listed here https://sqlite.org/omitted.html.

Looking Inside a Relational Table and Query

For the remainder of this chapter and several that follow, we are going to use a simple example to learn how to create SQLtables and queries. Table 1-1 shows a simple table that can be used to demonstrate the basic concepts of SQL, queries, and relational tables. It shows some data for a few people and their country of origin. The names of the columns (fields) are PK, Name, and Origin. The values of Name are Cecelia, Leif, and Charlotte; the values for Origin are Australia, Iceland, and United States.

You can retrieve data from a table by using a query. As noted previously, the result of a query is a table—perhaps an empty one, perhaps some of the data of the original table, and, in some cases, more data than in the original table. This section shows some typical basic queries. In later chapters, you’ll see more about queries as well as details about how to structure them. For now, this is just a taste of what queries can be and do.

Basic Query Structure

In their simplest form, queries consist of three sections.

SQL Action: SELECT

There are a number of verbs in SQL, but SELECT is used to select data from a table. Commands start with that word.

SQL Data to Select: List of Column Names

You can start to form a SELECT statement by listing the names of the columns you want to select. Thus, to retrieve data from the Name column, you would start the SELECT statement with

SELECT Name

To select data from several columns, you can specify a comma-delimited list as in the following case:

SELECT Name, Origin

You can select all columns with an asterisk as in

SELECT *

SQL Data Source: Table Name

You can specify the data source using a clause beginning with FROM. Following is an example:

SELECT Name FROM SimpleTable

SQL Condition: WHERE

You can specify the condition you want to use for selecting data with a clause that begins with WHERE. For example, you could use that first column (PK) to select a single row.

SELECT Name FROM SimpleTable WHERE PK = 2

You can also form a condition that is more complex:

SELECT Name FROM SimpletTable WHERE PK < 3

Looking at Other Query Choices

These are the simplest possible examples: more complex ones will be shown throughout the book, but the basic pattern will remain as follows:

SELECT this FROM table WHERE condition

You can use several tables as your data source. Thus, if you have names and origins as shown in Table 1-1 in a single table, you could have another table with each person’s birthday. You could create a single SELECT statement to retrieve both the name and birthday for anyone; to do so you would have to use two tables. (Don’t worry, this is an example you’ll see in Chapter 4.)

As noted previously, the result of a SELECT query is itself a table. Thus, you can write a SELECT query that executes a SELECT statement to create a table and then selects data from that resulting table.

With this brief overview of relational databases and SQL, it’s time to move on to SQLite itself.