Using Sqlite3 in your IOS application part 1


This blog entry covers how to incorporate a SQL database into your IOS application using Sqlite.  In my previous blog entry “Building SQLite into your IOS applications”, we built a static library from the latest SQLite Amalgamation source code.  Now we will build a test application to test the library.

xcode project setup

For this sample we will be using a simple UITableView. each row of the table will contain a record retrieved using a standard SQL query against a locally installed SQLite database.

project requirements

The database will be located within the applications folder space on the device.  It will contain a single table that will hold the following records

  1. first name
  2. last name
  3. phone number

Each table entry will be represented with a UITableViewCell.  Each row entry will display first and last name.  clicking the cell will present a view that display the entire first name, last name, and phone number of the selected row.  The user will be able to add new records by reusing the same view used to display the records, but with editing enabled on the UITextFields.

create the application

For the test application we are going to use a basic TableView application to view the records out of the database.  For this example, we are only creating, inserting into and reading a database.

adding database support to the app

If you read the previous blog entry “Building SQLite into your IOS applications”, there we setup an Xcode project that builds a static library of the latest SQLite Amalgamation source code.  Assuming you use the latest source code from Sqlite.org, then you will have the latest available.  This version will be much newer than anything that ships with the IOS SDK.  There are two methods to add the static library to the test app.  In a production environment, we would take the static library and add it through the Linked framework and Library settings under your project general settings.  this would require that you change the default output folder for the library which is something we have not done in the SQLite static library project yet.  That leaves us with the second method of including the SQLite project as a subproject of the test app.  This is also the easiest to set up but comes at a small cost of the extra build time to build the SQLite project each time you rebuild the test app.

SQLite classes

I use two classes to implement the SQLite database in your source code.  The first class manages the database connection while the other class manages the queries against the database.

The SQLite manager class is a simple class that creates the pointer to the SQLite database instance.   This class is also controls the initialization of the database on the first run of the application.  When the application is first deployed, we need to create the database, and for the purpose of this sample, fill it with data that will be displayed on the table view.

@interface SQLiteManager : NSObject

+(void) initializeDatabase;
+(NSString *) databasePath;
+(sqlite3 *) newConnection;
+(sqlite3 *) newConnectionFromFilename:(NSString *)databaseFilePath;
+(void) closeConnection:(sqlite3 *)database;
@end
  • +(void) initializeDatabase – This method initializes the database on the first run.  Inside this method we create the database file and populate the database with a table and add records to the table for this sample.
  • +(NSString *) databasePath – A method to return the absolute path to the database file on the IOS device.
  • +(sqlite3 *) newConnection – Open a new connection to the database.  If the database file has not been initialized, this will create it.  This will cause the initializeDatabase to fail if called before the database is initialized.
  • +(sqlite3 *) newConnectionFromFilename:(NSString *)databaseFilePath – open a database located at the absolute path in databasePath.
  • +(void) closeConnection:(sqlite3 *)database – close an open database connection.

The SQLite object class is a class that queries the database and tracks individual records retrieved from the database.  This class is intended to be used as a parent class to a derived class that represents a table in the database.

@interface SQLiteObject : NSObject {
    sqlite3 *_database;
    NSInteger _primaryKey;
    BOOL      _dirty;
}
@property (assign, nonatomic, readonly) NSInteger primaryKey;
@property (nonatomic, readonly) sqlite3 *database;
@property (nonatomic, assign) BOOL dirty;

-(id) init;
-(id) initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db;
-(BOOL) isConnected;

@end

SQLiteObject Properties

  • primaryKey – the primary key on this record item.
  • database – The database connection handle used to query this record.
  • dirty – Boolean flag to determine if the data on this object has changed.

SQLiteObject methods

  • -(id) init – initialize a blank SQLiteObject
  • -(id) initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db – initialize a new SQLiteObject with a primary key and database connection handle.
  • -(BOOL) isConnected – returns YES if the primary key and database connection handles are valid.

create and upgrading the schema

Creating the schema for the database is also an easy task once you get a new connection to the database.  When the application is first launched, the database does not exist.  Creating it is a three step process.

  1. Get the path to the database file and issue a call to sqlite3_open().  This initializes Sqlite in memory and creates a file in the  IOS apps documents folder.  When its first opened, it will have a zero length since there are no tables in this new database file.
  2. Using SQL, create the tables you need and for the purpose of this sample, we are inserting data as well using the SQL Insert and Create commands.
  3. close the database instance with a call to sqlite3_close().  This flushes the SQLite instance to the file, and there you go; one Sqlite database.

Upgrading the schema would follow the same procedures.  Open a connection to the database, then run the upgrade using SQL commands as you would on a normal SQL database.

retrieving data for viewing

Once the database has been populated with data, the application will attempt to load the data in the ViewControllers call to viewDidLoad.  To retrieve records from a SQLite database we need to use a sqlite3_stmt pointer.  To initialize a statement pointer, we issue a call to sqlite3_prepare().  The sqlite3_prepare() function takes 3 parameters that we care about.

  1. the database connection handle
  2. The SQL command string to issue against the database
  3. a statement object pointer to hold the retrieved query results.
- (void)viewDidLoad
{
    [super viewDidLoad];

    // setup a dummy table cell.  If we fail to load the database, you
    // will only see one record.
    _tableViewContents = [NSArray arrayWithObjects:@"item", nil];

    database = [SQLiteManager newConnection];

    if ( database )
    {
        // now we have opened the database, retrieve some records.
        sqlite3_stmt *statement = NULL;

        // execute the Sql statement
        int error = sqlite3_prepare_v2(database, "SELECT idx, firstname, lastname, phone FROM Contacts", -1, &statement, NULL);

        if (error == SQLITE_OK)
        {
            // got some results, now parse the records returned.
            NSMutableArray *results = [[NSMutableArray alloc] init];

            while (sqlite3_step(statement) == SQLITE_ROW) {
                // get the primary key
                int primaryKey = sqlite3_column_int(statement, 0);

                // get the data
                NSString *firstName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                NSString *lastName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
                NSString *phone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];

                // insert into an object
                Person *contact = [[Person alloc] init];
                if (contact) {
                    contact.primaryKey = [NSNumber numberWithInt:primaryKey];
                    contact.firstName = firstName;
                    contact.lastName = lastName;
                    contact.phone = phone;

                    [results addObject:contact];
                }

            }
            // store the results for viewing in the tableview.
            _tableViewContents = results;
        }
        // clean up the SQL statement when your done.
        sqlite3_finalize(statement);
        statement = NULL;

        [SQLiteManager closeConnection:database];
    }
}

When extracting data from the results we use a sqlite3_step() function to loop through all the records returned. This function will continue to loop through all the records upon each subsequent call until you reach the end. Each record returned will have column data that is ordered in the same order as you would expect with the SQL Query that you issued against the database. Accessing each item in the record can be done with a calls to SQLite result set interface. The results are ordered by columns and there are multiple types of column calls, one method for each datatype supported by SQLite.
After you have finished retrieving all the records, you must remember to clean up the SQLite pointers or you will leak memory. To free the statement, issue a call to sqlite3_finalize() and this clears the statement instance and frees any allocated memory. Once that is done, you can close the database instance pointer if you need to.

source code & conclusion

Implementing Sqlite in your application is not that hard if you understand basic SQL database operations.  Using Sqlite within your applications is not hard at all, and this powerful library provides an easy way for developers to add database capabilities to their applications without a large library to support.  I have found SQLite to be very robust and works on a wide variety of platforms with very consistent results.

Source code for this article is available off GitHub here:  SQLiteExample.git

You can get more information on SQLite from their website http://www.sqlite.org

About these ads

3 thoughts on “Using Sqlite3 in your IOS application part 1

  1. Pingback: Using SQLite in your IOS application part II | conedogers

  2. Pingback: Building SQLite into your IOS applications | conedogers

  3. Pingback: Using SQLite in your IOS applications part III | conedogers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s