What is SQLite?
SQLite is an embedded, relational database management system (RDBMS). Most relational databases (Oracle and MySQL being prime examples) are standalone server processes that run independently, and in cooperation with, applications that require database access. SQLite is referred to as embedded because it is provided in the form of a library that is linked into applications. As such, there is no standalone database server running in the background. All database operations are handled internally within the application through calls to functions contained in the SQLite library.
The developers of SQLite have placed the technology into the public domain with the result that it is now a widely deployed database solution.
SQLite is written in the C programming language and therefore using SQLite on the iPhone involves direct calls to C functions and access to C data structures. In order to bridge the differences between Objective-C and the C based SQLite library it will be necessary, for example, to convert any NSString objects to UTF8 format before passing them through as arguments to these functions.
For additional information about SQLite refer to http://www.sqlite.org.
Structured Query Language (SQL)
Data is accessed in SQLite databases using a high level language known as Structured Query Language. This usually abbreviated to SQL and typical pronounced sequel. SQL is a standard language used by most relational database management systems. SQLite conforms mostly to the SQL-92 standard.
Whilst use of some basic SQL statements will be used within this chapter, a detailed overview of SQL is beyond the scope of this book. There are, however, many other resources that provide a far better overview of SQL that we could ever hope to provide in a single chapter here.
Trying SQLite on MacOS X
For readers unfamiliar with databases in general and SQLite in particular, diving right into creating an iPhone application that uses SQLite can be a little intimidating. Fortunately, MacOS X is shipped with SQLite pre-installed, including an interactive environment for issuing SQL commands from within a Terminal window. This is both a useful way to learn about SQLite and SQL, and also an invaluable tool for identifying problems with databases created by applications in the iOS simulator.
To launch an interactive SQLite session, open a Terminal window on your Mac OS X system, change directory to a suitable location and run the following command:
sqlite3 ./mydatbase.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
At the sqlite> prompt, commands may be entered to perform tasks such as creating tables and inserting and retrieving data. For example, to create a new table in our database with fields to hold ID, name, address and phone number fields the following statement is required:
create table contacts (id integer primary key autoincrement, name text, address text, phone text);
Note that each row in a table must have a primary key that is unique to that row. In the above example we have designated the ID field as the primary key, declared it as being of type integer and asked SQLite to automatically increment the number each time a row is added. This is a common way to make sure that each row has a unique primary key. The remaining fields are each declared as being of type text.
To list the tables in the currently selected database use the .tables statement:
sqlite> .tables
contacts
To insert records into the table:
sqlite> insert into contacts (name, address, phone) values ("Bill Smith", "123 main Street, California", "123-555-2323");
sqlite> insert into contacts (name, address, phone) values ("Mike Parks", "10 Upping Street, Idaho", "444-444-1212");
To retrieve all rows from a table:
sqlite> select * from contacts;
1|Bill Smith|123 main Street, California|123-555-2323
2|Mike Parks|10 Upping Street, Idaho|444-444-1212
To extract a row that meets specific criteria:
sqlite> select * from contacts where name="Mike Parks";
2|Mike Parks|10 Upping Street, Idaho|444-444-1212
To exit from the sqlite3 interactive environment:
sqlite> .exit
When running an iPhone application in the iOS Simulator environment, any database files will be created on the file system of the computer on which the simulator is running. This has the advantage that you can navigate to the location of the database file, load it into the sqlite3 interactive tool and perform tasks on the data to identify possible problems occurring in the application code. If, for example, an application creates a database file named contacts.db in its documents directory, the file will be located on the host system in the following folder:
/Users/<user>/Library/Application Support/iPhone Simulator/<sdk version>/Applications/<id>/Documents
Where <user> is the login name of the user running the iOS Simulator session, <sdk version> is the version of the iOS SDK used to build the application and <id> is the unique ID of the application.
Preparing an iOS 4 Application Project for SQLite Integration
By default, the Xcode environment does not assume that you will be including SQLite in your application. When developing SQLite based applications a few additional steps are required to ensure the code will compile when the application is built. Firstly, the project needs to be configured to include the libsqlite3.dylib dynamic library during the link phase of the build process. To achieve this, hold down the Ctrl key and click with the mouse on the Frameworks entry of the Groups and Files panel located on the left hand side of the Xcode project window. From the resulting menu, select the Add -> Existing Frameworks… option. From the list of framework options, scroll down to the libsqlite3.dylib entry, select it and click on the Add button.
Secondly, the sqlite3.h include file must be imported into any files where references are made to SQLite definitions, declarations or functions. This file in located in the /usr/include directory and may be imported when needed as follows:
#import "/usr/include/sqlite3.h"
Key SQLite Functions
<a href="http://ad.yieldmanager.com/imageclick?Z=300x250&s=256571&t=2" target="parent"><img border="0" src="http://ad.yieldmanager.com/imp?Z=300x250&s=256571&t=2"></img></a>
<a href="http://www.burstnet.com/ads/ad12897b-map.cgi/ns/v=2.3S/sz=300x250A/" target="_top"> <img src="http://www.burstnet.com/cgi-bin/ads/ad12897b.cgi/ns/v=2.3S/sz=300x250A/" border="0" alt="Click Here" /></a>
When implementing a database using SQLite it will be necessary to utilize a number of C functions contained within the libsqlite3.dylib library. A summary of the most commonly used functions is as follows:
- sqlite3_open() - Opens specified database file. If the database file does not already exist, it is created.
- sqlite3_close() - Closes a previously opened database file.
- sqlite3_prepare_v2() - Prepares a SQL statement ready for execution.
- sqlite3_step() - Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.
- sqlite3_column_<type>() - Returns a data field from the results of a SQL retrieval operation where <type> is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc).
- sqlite3_finalize() - Deletes a previously prepared SQL statement from memory.
- sqlite3_exec() - Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.
This, of course, represents only a small subset of the complete range of functions available with SQLite. A full list can be found at http://www.sqlite.org/c3ref/funclist.html.
Declaring a SQLite Database
Before any tasks can be performed on database, it must first be declared. To do so it is necessary to declare a variable that points to an instance of a structure of type sqlite3 (the sqlite3 structure is defined in the sqlite3.h include file). For example:
sqlite3 *contactDB; //Declare a pointer to sqlite database structure
Opening or Creating a Database
Once declared, a database file may be opened using the sqlite3_open() function. If the specified database file does not already exist it is first created before being opened. The syntax for this function is as follows:
int sqlite3_open(const char *filename, sqlite3 **database);
In the above syntax, filename is the path to the database file in the form of a UTF-8 character string and database is the reference to the database structure. The result of the operation is returned as an int. Various definitions for result values are defined in the include file such as SQLITE_OK for a successful operation.
For example, the code to open a database file named contacts.db in the Documents directory of an iPhone application might read as follows. Note that the code assumes an NSString variable named databasePath contains the path to the database file:
sqlite3 *contactDB; //Declare a pointer to sqlite database structure
const char *dbpath = [databasePath UTF8String]; // Convert NSString to UTF-8
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
//Database opened successfully
} else {
//Failed to open database
}
The key point to note in the above example is that the string contained in the NSString object was converted to a UTF-8 string before being passed to the function to open the database. This is a common activity that you will see performed frequently when working with SQLite in Objective-C.
Preparing and Executing a SQL Statement
SQL statements are prepared and stored in a structure of type sqlite3_stmt using the sqlite3_prepare_v2() function. For example:
sqlite3_stmt *statement;
NSString *querySQL = @"SELECT address, phone FROM contacts”;
const char *query_stmt = [querySQL UTF8String];
if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
//Statement prepared successfully
} else {
//Statement preparation failed
}
A prepared SQL statement may subsequently be executed using a call to the sqlite3_step() function, passing through the sqlite3_stmt variable as the sole argument:
sqlite3_step(statement);
sqlite3_finalize(statement);
Note that the sqlite3_step() function also returns a result value. The value returned, however, will depend on the nature of the statement being executed. For example, a successful insertion of data into a database table will return a SQLITE_OK result, whilst the successful retrieval of data will return SQLITE_ROW.
Alternatively, the same results may be achieved with a single call to the sqlite3_exec() function as illustrated in the following section.
Creating a Database Table
Database data is organized into tables. Before data can be stored into a database, therefore, a table must first be created. This is achieved using the SQL CREATE TABLE statement. The following code example illustrates the creation of a table named contacts, the preparation and execution of which is performed using the sqlite3_exec() function:
const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) == SQLITE_OK)
{
// SQL statement execution succeeded
}
Extracting Data from a Database Table
Those familiar with SQL will be aware that data is retrieved from databases using the SELECT statement. Depending on the criteria defined in the statement, it is typical for more than one data row to be returned. It is important, therefore, to learn how to retrieve data from a database using the SQLite C function calls.
As with previous examples, the SQL statement must first be prepared. In the following code excerpt, a SQL statement to extract the address and phone fields from all the rows of a database table named contacts is prepared:
sqlite3_stmt *statement;
NSString *querySQL = @"SELECT address, phone FROM contacts”;
const char *query_stmt = [querySQL UTF8String];
sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL);
The statement subsequently needs to be executed. If a row of data matching the selection criteria is found in the database table, the sqlite3_step() function returns a SQLITE_ROW result. The data for the matching row is stored in the sqlite3_stmt structure and may be extracted using the sqlite3_column_<type>() function call, where <type> is replaced by the type of data being extracted from the corresponding field of data. Through the implementation of a while loop, the sqlite3_step() function can be called repeatedly to cycle through multiple matching rows of data until all the matches have been extracted. Note that the sqlite_column_<type>() function takes as its second argument the number of the column to be extracted. In the case of our example, column 0 contains the address and column 1 the phone number:
while (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *addressField = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 0)];
NSString *phoneField = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 1)];
// Code to do something with extracted data here
[phoneField release];
[addressField release];
}
sqlite3_finalize(statement);
Closing a SQLite Database
When an application has finished working on a database it is important that the database be closed. This is achieved with a call to the sqlite3_close() function, passing through a pointer to the database to be closed: sqlite3_close(contactDB);
Summary
In this chapter we have looked at the basics of implementing a database within an iPhone application using the embedded SQLite relational database management system. In the next chapter we will put this theory into practice and work through an example that creates a functional iPhone application that is designed to store data in a database.
