Using SQLite in your IOS application part II

In my previous article Using “SQLite in your IOS application Part I” introduced a new sample application that utilized an IOS static library containing the latest SQLite amalgamation source code.  In the sample application, a pair of classes were created for wrapping the basic SQLite functionality into Objective-C.  These classes were used to create and open a database.  Now that we have that database created and we have the code to open and close it, now we need to add the basic operations on a database.  In this article we will take the SQLiteManager and SQLiteObject classes and implement them in a better MVC architecture and add the capabilities to Add, Delete, and update records in a table.

SQLite classes

Just as a review, here are the classes that were introduced in the last article.  “SQLite in your IOS application Part I” introduced a pair of new classes that are designed to be used for SQLite database interaction.  That sample code really only covered the first class, the SQLiteManager which does not do that much.  In this sample code we are focusing on the second class, the SQLiteObject.  This class is really the main part of the application.

SQLite Manager

The SQLite manager class is a simple class that creates the pointer to the SQLite database instance.  The SQLiteManager class manages the database connection while the SQLiteObject class manages the queries against the database.  This class controls the initialization of the database on the first run of the application and maintains that connection for the lifetime of the application.  In the last example, we opened and closed the database connection on demand.  While this does work, leaving the connection open avoids hitting the file system.  It is possible to have multiple database connections open at one time, so tracking which database connection you queried your object against is important. The SQLiteObject is designed to track the open database connection because that object works directly with the data stored within the database.

SQLiteObject methods
  • +(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.  By itself, the SQLiteObject class does not contain any methods that directly query against the database connection held by its database property.  The real functionality resides within the class object that derives off this base class.  In this example, we have the Person Class which is derived from this SQLite class.  In the previous example the person object was not modified to run any queries against the database, we only used it to track the open connection and the primary key of the record.  Now we are going to add more methods to complete the missing database functionality.

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.

Person Class

The Person class contains the data retrieved from each record.  It is derived from the SQLiteObject class and it contains properties that define each record value retrieved from the database.  The Person object is a very small and light weight object.  It is designed to be small in size so it can easily and efficiently passed around to the different views that manipulate each object instance.

Person Properties
  • primaryKey – the primary key inherited from SQLiteObject
  • database – The database connection handle inherited from SQLiteObject.
  • dirty – Boolean flag inherited from SQLiteObject.
  • first – Contacts first name as a NSString
  • last – Contacts last name as a NSString
  • phone – Contacts phone as a NSString


Person Categories

In the last article “SQLite in your IOS application Part I“, we modified the Person class so it was derived from a SQLiteObject. All this did was to make the Person class SQLite aware, but no methods were implemented for the person class to access the database. At this point, the Person class tracks the primary index of the object in the SQL result set, and the opened database connection that was used in the last query.  The real functionality behind the person object is provided via categories.  Objective-C categories offer a good way to implement the database functionality we are looking for while keeping the base Person object lean and mean which helps keep our memory usage down.

Categories and SQLite operations

Categories are used to break down the  SQL operations into functional groups of selectors that are only defined within the scope of the source file in which the category was included.   For this example, we are going to use the following categories.  Most of the methods implemented in the categories are static, only the update method is implemented off the current instance.

To implement each of these categories in the IOS application we need to include the appropriate header in the source file where we need the functionality.  Before we talk about how the categories are implemented in the IOS sample application, we need to talk about the application itself first.

Application design

This sample is written using a storybook that manages a series of table views.  We’re not going to spend a whole lot of time on how to build the storybook here.  If you are curious, you can check out this good blog I found on the subject. “Storyboards tutorial in IOS7“.  That blog article does a good job at describing the tasks required to create the storyboard application we want using UINavigationController with UITableViewControllers.

  • The Application delegate implements the ReadPerson and WritePerson categories.
  • The Contacts View controller  implements the Read and Delete categories
  • The AddContactViewcontroller  implements the Add New Person category.
  • The Update view controller implements the Write Person category.


Screen Shot 2014-03-22 at 11.15.48 AM

The storybook layout for the sample application

Creating the storyboards is one of the easier tasks in this sample application.  Storyboards are nice because they let you get a running prototype up quickly.  Once you have the prototype all we need to do is plug in the calls to the SQLite classes and objects and we have an app!

Opening the database

In this sample, we put the main database connection into the application delegate so we can handle the main IOS events for launching, entering the background, and termination.  The application delegate contains two private variables, the database connection pointer and the list of contacts retrieved from the last query into the database.

Opening the database is easy, we first have to check to see if the database has been installed correctly, then we can initialize a new database connection.  To get the list of contacts from the database, we use the ReadPersons category from the Person object.

if for what ever reason you are not using ARC, then you need to be careful about handing the strings and other data that might have blocks of memory to move around. The string data returned by SQLite is managed by a different memory pool than the rest of your IOS environment.  Internally, the string data should be treated as SQLITE_TRANSIENT by default.  This means that we treat the strings as volatile, so we would need to retain the strings as they are extracted from the result set.  ARC takes care of this automatically.

Adding a new record

Adding a new record to the Contacts table is done through the Add Person View controller.  The AddContactViewController has 2 methods for handling the navigation bar button events for “Cancel” and “Done”.  The cancel action is self evident, all it does is close the view.  The done action performs the actual record insert before closing the view.

Inserting into the database with simple datatypes is easy enough. For this sample, we are just constructing a string that contains all the values within the string. This is impractical for large amounts of data in strings or blobs, but for now it will work.

Deleting a record

Deleting a record is done through deleting table cells on the contacts view.  The edit button enables or disables the UITableView editing feature.  Once we have deleted a cell the application is notified with a call to the following method.

Deleting a record is easy. When the application commits the changes to the UITableView, we update the list of contacts, and with the retrieved person object, we delete the record from the database.  We use the primary key to identify the record to delete, and your done.


Updating a record

Updating a record is a little more tricky. The SQLiteObject has a dirty flag that needs to be set before you can trigger an update. You can certainly hack the object and manually set the dirty flag when you want too. A better method is to overload the setter method when the property is synthesized.  Overloading the synthesized getter method lets us automatically set the dirty flag when we want to with little changes to the code.

Modify the person object

In the Person object we want to modify the source to include the overloads after creating synthesizer statements for each of the properties used in the person object.

That’s all the changes we need to support the updating of records in the Person object. Now anytime one of the properties changes in value, we set the dirty flag for that object instance. We do not set the dirty flag when the property is nil because that is the first assignment when the current object instance is first created. This only happens on the first application load, or when a new object is added to the database.

Updates are used to save the list of objects in the application delegate when we receive moving into background and termination events.  This should not be necessary with the approach this example takes.  Any time that data is changed we immediately write the data to the database to prevent data loss.  That said, for redundancy we add some extra calls to update the contacts in the list stored in the application delegate.

Updating all the objects is really easy with Objective-C and its fast enumeration. To flush the data out to the database, we call the updateDatabase selector on all the objects in the list. If any objects have been marked dirty, they are written out to the database.

That’s it! it is really easy to integrate SQLite into your IOS application. Included with this blog entry is a copy of the working project that you can take a look at. I would recommend that you look at the app, this blog was written in 3 languages. English, Objective-C, and C/C++. Everything is crystal clear in the computer languages, the english lost a bit in translation. so please take a look at the sample code

Source code is located on GitHub, SQLiteExamplePart2

Links & references

Programming With Objective-C – Customizing existing classes

Tutorial: Storyboards

Storyboard tutorial in IOS7


Using SQLite in your IOS applications Part I

Building SQLite in your IOS applications