View source for IOS 6 iPhone Database Implementation using SQLite

<table border="0" cellspacing="0">
<tr>
<td width="20%">[[iOS 6 iPhone Data Persistence using Archiving|Previous]]<td align="center">[[iPhone iOS 6 Development Essentials|Table of Contents]]<td Width="20%" align="right">[[An Example SQLite based iOS 6 iPhone Application|Next]]</td>
<tr>
<td width="20%">iOS 6 iPhone Data Persistence using Archiving<td align="center"><td width="20%" align="right">An Example SQLite based iOS 6 iPhone Application</td>
</table>
<hr>


<htmlet>ios9_upgrade</htmlet>


Whilst the preceding chapters of this book have looked at data storage within the context of iOS 6 iPhone based applications, this coverage has been limited to using basic file and directory handling and object archiving. In many instances, by far the most effective data storage and retrieval strategy requires the use of some form of database management system.

In order to address this need, the iOS 6 SDK includes everything necessary to integrate SQLite based databases into iPhone applications. The goal of this chapter, therefore, is to provide an overview of how to use SQLite to perform basic database operations within your iPhone application. Once the basics have been covered, the next chapter (entitled [[An Example SQLite based iOS 6 iPhone Application]]) will work through the creation of an actual application that uses a SQLite database to store and retrieve data.

== 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 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 is usually abbreviated to SQL and pronounced sequel. SQL is a standard language used by most relational database management systems. SQLite conforms mostly to the SQL-92 standard.

Whilst 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 than 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 may seem 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:

<pre>
sqlite3 ./mydatbase.db

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
</pre>

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:

<pre>
create table contacts (id integer primary key autoincrement, name text, address text, phone text);
</pre>

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:

<pre>
sqlite> .tables
contacts
</pre>

To insert records into the table:

<pre>
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");
</pre>

To retrieve all rows from a table:

<pre>
sqlite> select * from contacts;
1|Bill Smith|123 Main Street, California|123-555-2323
2|Mike Parks|10 Upping Street, Idaho|444-444-1212
</pre>

To extract a row that meets specific criteria:

<pre>
sqlite> select * from contacts where name="Mike Parks";
2|Mike Parks|10 Upping Street, Idaho|444-444-1212
</pre>

To exit from the sqlite3 interactive environment:

<pre>
sqlite> .exit
</pre>

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:

<pre>
/Users/<user>/Library/Application Support/iPhone Simulator/<sdk version>/Applications/<id>/Documents
</pre>

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 iPhone 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 select the target entry in the Xcode project navigator (the top entry with the product name) to display the summary information. Select the Build Phases tab to display the build information:


[[Image:xcode_4.2_add_build_library.jpg]]
 
Figure 39-1


The Link Binary with Libraries section lists the libraries and frameworks already included in the project. To add another library or framework click on the ‘+’ button to display the full list. From this list, select the required item (in this case libsqlite3.dylib) and click Add. 

Secondly, the sqlite3.h include file must be imported into any files where references are made to SQLite definitions, declarations or functions. This file may be imported when needed as follows:

<pre>
#import <sqlite3.h>
</pre>

== Key SQLite Functions ==
<htmlet>adsdaqbox_flow</htmlet>
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 http://www.sqlite.org/c3ref/funclist.html].

== Declaring a SQLite Database ==

Before any tasks can be performed on a 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:

<pre>
sqlite3 *contactDB; //Declare a pointer to sqlite database structure
</pre>

== 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:

<pre>
int sqlite3_open(const char *filename, sqlite3 **database);
</pre>

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 value. 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:

<pre>
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
}
</pre>

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:

<pre>
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
}
</pre>

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:

<pre>
sqlite3_step(statement);
sqlite3_finalize(statement);
</pre>

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:

<pre>
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
}
</pre>

== 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:

<pre>
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);
</pre>

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:

<pre>
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

}
sqlite3_finalize(statement);
</pre>

== 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:

<pre>
sqlite3_close(contactDB);
</pre>

== 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.


<htmlet>ios9_upgrade</htmlet>


<table border="0" cellspacing="0">
<tr>
<td width="20%">[[iOS 6 iPhone Data Persistence using Archiving|Previous]]<td align="center">[[iPhone iOS 6 Development Essentials|Table of Contents]]<td Width="20%" align="right">[[An Example SQLite based iOS 6 iPhone Application|Next]]</td>
<tr>
<td width="20%">iOS 6 iPhone Data Persistence using Archiving<td align="center"><td width="20%" align="right">An Example SQLite based iOS 6 iPhone Application</td>
</table>