Difference between revisions of "IOS 9 Database Implementation using SQLite"

Latest revision as of 20:02, 27 October 2016

Whilst the preceding chapters of this book have looked at data storage within the context of iOS 9 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 9 SDK includes everything necessary to integrate SQLite based databases into iOS applications. The goal of this chapter, therefore, is to provide an overview of how to use SQLite to perform basic database operations within your iOS application. Once the basics have been covered, the next chapter (entitled An Example SQLite based iOS 9 Application using Swift and FMDB) 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 from within Swift code either requires some complex handling of C function calls, data types and pointers, or the easier approach of using an existing SQLite wrapper as a layer between SQLite and Swift. In this chapter we will look at one such wrapper in the form of FMDB.

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

sqlite3 ./mydatabase.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 iOS 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/Developer/CoreSimulator/Devices/<simulator id>/data/Containers/Data/Application/<id>/Documents

Where <user> is the login name of the user logged into the Mac OS X system, <simulator id> is the id of the simulator session and <id> is the unique ID of the application.

Preparing an iOS 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. First, the project needs to be configured to include the libsqlite3.tbd 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.

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.tbd) and click Add.

SQLite, Swift and Wrappers

As previously discussed, SQLite is written in the C programming language. Whilst it was still possible to use the C-based SQLite API from within Objective-C code with relative ease, this is not the case when programming in Swift without dealing with complex issues when bridging the gap between C and Swift. A common solution to this dilemma involves the use of a SQLite “wrapper”. A number of wrappers are now available for this purpose many of which show considerable potential. For the purposes of this book, however, we will be working with the FMDB wrapper. Although this is essentially an Objective-C wrapper, it can be used easily from within Swift code. FMDB has been chosen for the examples in this book because it has been available for some time, is considered to be stable and feature rich and will be familiar to the many developers who have previously used with it Objective-C. FMDB is an open-source project released under the terms of the MIT license.

Details on how to obtain FMDB and incorporate it into an iOS Xcode project are covered in detail in the next chapter (An Example SQLite based iOS 9 Application using Swift and FMDB).

Key FMDB Classes

When implementing a database using SQLite with FMDB it will be necessary to utilize a number of FMDB classes contained within the wrapper. A summary of the most commonly used classes is as follows:

• FMDatabase – Used to represent a single SQLite database. The object on which SQL statements are executed from within code.
• FMResultSet – Used to hold the results of a SQL query operation on an FMDatabase instance.
• FMDatabaseQueue – A version of FMDatabase designed to allow database queries to be performed from multiple threads.

For more detailed information, the FMDB Class Reference documentation is available online at:

http://ccgus.github.io/fmdb/html/Classes/FMDatabase.html

Creating and Opening a Database

Before work can commence on a database it must first be created and opened. The following code opens the database file at the path specified by <database file path>. If the database file does not already exist it will be created when the FMDatabase instance is initialized:

let myDatabase = FMDatabase(path: <database file path>)

if myDatabase == nil {
    // Database could not be found or created
} else {
    if myDatabase.open() {
        // Database is ready 
    }
}

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 using FMDB:

let sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)"

if !myDatabase.executeStatements(sql_stmt) {
     // Table creation failed
}

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 FMDB wrapper.

In the following code excerpt, a SQL SELECT statement is used to extract the address and phone fields from all the rows of a database table named contacts via a call to the executeQuery method of the FMDatabase instance:

let querySQL = "SELECT address, phone FROM CONTACTS WHERE name = '\(name.text)'"

let results:FMResultSet? = myDatabase.executeQuery(querySQL, 
		withArgumentsInArray: nil)

On completion of the query execution, the FMResults object returned from the method call contains the results of the query. Regardless of whether one or more results are expected, the next method of the returned FMResultSet object must be called. A false return value from the next method call indicates either that no results were returned, or that the end of the result set has been reached.

In the event that results were returned, the data can be accessed using the column name as a key. The following code, for example, outputs the “address” and “phone” values for all of the matching records returned as the result of the above query operation:

while results?.next() == true {
    print(results?.stringForColumn("address"))
    print(results?.stringForColumn("phone"))
}

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 close method of the FMDatabase instance:

myDatabase.close()

Summary

In this chapter we have looked at the basics of implementing a database within an iOS application using the embedded SQLite relational database management system together with the FMDB wrapper to make access to the database possible from within Swift code. In the next chapter we will put this theory into practice and work through an example that creates a functional iOS application that is designed to store data in a database.