Database Abstraction Part 2: iOS

You’ve made it to my favorite part of this series. In the previous post, I covered the APP(ideas) database abstraction layer for PHP. Our primary goal has been to make it a simple system that is easy to use, fully capable and with little bloat. Some natural side-effects of such a system are that the code is easy understand, easy to change to suit any need, it is efficient, and it is easy to adapt to many database systems and programming languages. In short, it is a fantastic (in our opinion, the best) foundation to any database-centered app. You’ll see in this post that, if you are familiar with what has already been done in PHP, you won’t have to learn much of anything new to access your data, even though you have switched to a different programming language. Here it is: The APP(ideas) database abstraction layer for iOS.

Examples

I am going to depart from a more logical arrangement of information for this post and provide examples first. I think that most people reading this will want to see it in action before reading more deeply into the details. After seeing how it works through examples, continue reading for complete details about how to get and use the database abstraction layer for iOS.

If you’re looking for the code, you can get it here: https://github.com/appideasDOTcom/DatabaseAbstraction

The code for this post is in the “ios” directory.

If you’re not certain how to get this all setup, skip ahead to the “Installation and Setup” section below. Once you’ve got it setup, create an instance of the database abstraction class like this:

1
2
3

#import "AiDatabase.h"
 
AiDatabase *db = [[AiDatabase alloc] init];

And here is how to perform a query and iterate the results.

1
2
3
4
5
6
7
8
9
10

NSString *sql = @"SELECT id, str_field FROM test_table ORDER BY time_field DESC";
// result will hold an array of records returned by the query. Each result (record) is a dictionary of key/value pairs.
NSMutableArray *result = [db query:sql];
for( NSInteger i = 0; i < [result count]; i++ )
{
    // The variable "row" will represent the current record as a dictionary, so pull it out and grab its values by key
    NSMutableDictionary *row = [result objectAtIndex:i];
    NSInteger currentId = [[row objectForKey:@"id"] integerValue];
    NSString *currentString = [row objectForKey:@"str_filed"];
}

That’s pretty easy, right? And you’ve already got a fully-functional database system setup and raring to go!

But there’s a lot more to it than that. You can take a look at the included sample app’s TestTable class for a useable implementation. Here are some of the more useful examples.

To insert a new record and get its ID, use the insertBlankIntoTable method like this:

1

NSInteger idValue = [db insertBlankIntoTable:@"test_table" withIdField:_idField];

The variable “_idField” is a special constant defined in the project’s constants.h file. If your ID (surrogate key) field names differ from table-to-table, you can change this call to something like:

1

NSInteger idValue = [db insertBlankIntoTable:@"test_table" withIdField:@"test_table_id"];

And now that you have a new ID, you can update that record in the database with the values you want to save like this:

1
2
3
4
5
6
7
8
9

NSString *sql = [NSString stringWithFormat:@"\
    UPDATE test_table \
    SET    int_field = 2, \
           str_field = '%@', \
           bool_field = %li \
    WHERE  %@ = %li",
    [AiDatabase escapeString:myStringInput], (long)[AiDatabase fixDbBoolean:myBoolInput],
    _idField, (long)idValue];
[db noReturnQuery:sql];

That’s about as complicated as it gets. You’ll notice that we wrapped a couple of the input variables in AiDatabase helper methods. The first (escapeString) will escape your input string for SQL input (you will want to do this with all user input that you are saving), and the second (fixDbBoolean) will convert an Objective-C boolean (YES/NO) into something that can be handled by the database (1/0). There is a method called fixBoolean to convert the other way (database to Objective-C).

An alternative way to create a new record and get its ID, which allows you to skip the call to insertBlankIntoTable, is this:

1
2
3
4
5
6
7
8
9
10

NSString *sql = [NSString stringWithFormat:@"\
    INSERT \
    INTO   test_table \
           ( %@, int_field, str_field, bool_field )
    VALUES ( NULL, 2, '%@', %li )",
    _idField, [AiDatabase escapeString:myStringInput], (long)[AiDatabase fixDbBoolean:myBoolInput]];
[db beginTransaction];
[db noReturnQuery:sql];
NSInteger idValue = [db getLastIdFromTable:@"test_table" withIdField:_idField];
[db endTransaction];

This also makes use of SQLite transactions, so if anything bad happens in the middle, the query can be rolled back.

One minor point to make is related to something you may have noticed in the queries above. In the first example, I performed a query like this:

1

NSMutableArray *result = [db query:sql];

In the second and third examples, the queries were a little different:

1

[db noReturnQuery:sql];

The noReturnQuery method is designed for times when your SQL query will not return results, such as UPDATE, INSERT or DELETE statements. The query method is designed for times that you expect a result set to be returned. If you don’t want to think about the differences, you can always use query instead of noReturnQuery. These two lines are functionally the same:

1
2

[db noReturnQuery:@"UPDATE test_table SET int_field = 2 WHERE id = 9"];
[db query:@"UPDATE test_table SET int_field = 2 WHERE id = 9"];

The practical difference is that the query method reserves a tiny amount of extra RAM on the device to store a result set.

Another handy trick is being able to tell whether or not your query returned any results before trying to iterate the result set. You can do that like this:

1
2
3
4
5
6

NSString *sql = @"SELECT id FROM test_table";
NSMutableArray *result = [db query:sql];
if( [db numRows] < 1 )
{
    NSLog( @"There were no records!" );
}

This is a simple and stable system to use. There are a few other cool things that this system can do to make your life as a programmer easier. Be sure to check out the documentation in the “docs” directory and the source code of the included sample app (including the test case code) to see it all working.

Now that I’ve shown how it works, I’d like to talk about it a bit.

An Introduction to the Database Abstraction Layer for iOS

There are several reasons that I have a particular fondness for database abstraction in iOS, and it’s not some emotion that I have telling me that iOS is better than Android. They both have their good points and their bad. I have spent the vast majority of my professional life over the past decade coding data-centered applications. The first and foremost reason that I have a particular affection for this post is that I love programming in Objecitve-C. I know that this is somewhat of a philosophical position, and programmer’s tend to pride themselves on scientific approach, but let’s not kid ourselves. Many of our decisions—our coding style, our file structure, our database schema, the programming language we choose—are as much a matter of religion as they are science. The decisions we make that guide our projects and make us do our job the way that we do has to do with the following of principles and techniques that often times have as many arguments against them as they have in favor. Many times, there are alternative methods that have equally valid arguments in favor of them, but we choose one way over another because the criterion upon which we base our decisions “seems” right for reasons that are not strictly scientific or found in a text book.

How does this apply to Objective-C? Over the past 13 years, I have been the lead on many projects for massive international corporations. Several of those projects were actively built, maintained, tweaked, added onto and continued to grow over a span that lasted more than 5 years. I have learned quite a few things about code maintainability for gigantic projects that last many years. One of the most important things is that code that is easier to understand is easier to maintain. Every programmer I have ever employed or managed that “didn’t work out” failed to grasp this concept. Let me give you a simplistic example, first using a PHP signature:

public function arrangePriority( $table, $id, $field, $value )

What does this mean? Assuming that the programmer wasn’t too lazy and at least gave the function a meaningful name, you can probably guess that this will arrange priority flags in a database table (although, I have had programmers working for me who made the mistake of submitting functions like this with a name like ‘ap’ because they didn’t want to type all those letters). The meaning of ‘$table’ is pretty obvious, but is $id the ID value or the ID field? If it is the value, then is $field the field that holds the priority flag, or is it the field that holds the ID value? Does this return the new priority, or anything at all? You would have no way of knowing this without reading the code (or at least the documentation, assuming that this function was documented well).

Now in Objective-C:

-(void) arrangePriorityInTable:(NSString *)table forId:(NSInteger)id withPriorityField:(NSInteger)field andPriorityValue:(NSInteger)value

This is a little chatty (and admittedly quite a lot to type), but there is no ambiguity about what it is supposed to do or what each argument provides. You can read the method signature—almost in English—and know precisely what it does and how to use it. You may think that it is a waste of time to have to type all those characters, but I can guarantee you that long-lasting code that is that easy to read and understand will pay huge dividends in the form of time saved. Also, assuming that you spend an appropriate amount of time writing documentation for your code, you will immediately discover that writing descriptive code allows you to spend less time on making comments. In the end, your code is easier to manage and you spend less time writing it.

It is true that we could have made this slightly better in PHP with something like:

public function arrangePriority( $table, $idValue, $priorityField, $priorityValue )

However, the very structure of Objective-C encourages (and in many cases requires) you to write your method signatures in nearly-English constructs, and the prompting of the language to encourage the writing of readable code is a good thing.

Why do we want to specifically tackle a database abstraction layer in Objective-C? A big part of the reason is so that we have a data acquisition mechanism that closely mimics what we already use in PHP, Android and other programming languages. It means that there is one less thing to learn. It also provides the advantages that we outlined in the introduction to this series. But unlike PHP, at the time of this writing, you can’t find another database abstraction layer for Objective-C. If the data needs for your iOS app are simple, you can get by with Core Data, but Core Data does not work well if your data is relational. In Apple’s own words, “Core Data is not a relational database or a relational database management system (RDBMS)… Core Data provides an infrastructure for change management and for saving objects to and retrieving them from storage.” (Core Data Programming Guide) I’m not knocking Core Data. It’s fantastic for its purpose. But Core Data’s purpose is different, and if you have relational data, you need a relational database. There simply isn’t anything else like this available. The same can (nearly) be said for Android, although there are a few similar solutions that are, in my opinion, quite lousy for reasons that I will cover in the next post in this series.

Finally, when you use this database abstraction layer, most of the work is done for you. Just follow the few setup instructions we provide below, and get querying.

Installation and Setup

Click this link to get the code from Github. You are going to be interested in the contents of the “ios” directory.

If you want to start from our source code:

1. After downloading and unpacking the code, open AiDB.xcodeproj in Xcode.
2. If you want for the project to have your own name, use “File” and “Duplicate…” to give the project a new name.
3. Modify the appropriate values in your info.plist
4. If you want to call the database file something other than com.appideas.AiDB.sqlite, rename the file under the “Supporting Files” group and change the value of _databaseFileName in constants.h accordingly.

If you want to use this code in a new or existing project of your own creation:

1. Open your Xcode project.
2. Select your project name in the upper left-hand corner, go to Build Phases and click the “+” in the “Link Binary With Libraries” section. From there, add “libsqlite3.dylib” to your project.
3. Copy the com.appideas.AiDB.sqlite file into your project, preferably in the “Supporting Files” group. Rename that file prior to copying it if you want to use another database name.
4. Add a file called “constants.h” and add the key: _databaseFileName with a value of @”com.appideas.AiDB.sqlite” (include the @ symbol and quotes) or whatever name you chose for your database if you renamed that file.
5. Copy and paste all other definitions from constants.h into your project’s contants.h file.
6. Create a group called “classes” under your project’s main grouping folder, and a group inside of that called “base” and another called “util”
7. Copy the files from the AiDB project’s “classes” groups into the corresponding groups in your project. Make sure to check the option to copy these files into your project.
8. Modify your AppDelegate class in the following ways:
   1. Add this to the top of your AppDelegate.m: #import “AiDatabase.h”
   2. Add these lines into your application didFinishLaunchingWithOptions method:

1
2
3

[AiDatabase createDatabase];
AiDatabase *db = [[AiDatabase alloc] init];
[db doUpgrade];

Differences from the PHP Database Abstraction Layer

SQLite operates on a completely different principle than other database systems. The biggest difference is that it does not utilize a client/server access model. In SQLite, the database is self-contained in the data file, and best practices for that access model dictate that the data file be opened and closed on each query (or at the beginning and end of atomic operations). Otherwise, we risk leaving open file handles and corrupting the database if the app ever misbehaves in any way. As such, there are no handles/pointers to queries left open after performing those queries. Because of this, several of the methods that are used in the PHP database abstraction layer are not appropriate for SQLite. Specifically, the iOS (and Android) abstraction for SQLite does not have the following methods:

• fetchRow
• fetchArray
• fetchFieldObject
• fieldName
• fieldType
• simpleFieldType

As you will see, iterating the result set of an SQLite query does not need those methods anyway—the query methods do all of the work that those other methods perform for you in PHP. This ends up simplifying things quite a bit, but it is a departure from the way you may be used to iterating query results, particularly if you are used to preforming queries against a client/server database system.

There are a few additions to the SQLite database abstraction layer (for both iOS and Android). Those are:

1. noReturnQuery This was added to keep the compiler/analyzer happy when queries are performed that don’t need any results returned, and we didn’t want to add a blank result set into memory.
2. getLastIdFromTable Most database systems don’t have as reliable a method for grabbing a last inserted surrogate key as does SQLite, so we don’t include a method for doing so in PHP.

Most of the other differences are minor changes in the names of methods to be more appropriate for the language being used. For example, in PHP, you will see insertBlank( $table, $id ), but in Objective-C, you will find [db insertBlankIntoTable:@”table” withIdField:@”id”]. Take a little time with the documentation and example code, and it should be pretty easy to match up the corresponding methods between PHP and Objective-C. More importantly, it should be obvious how to use the methods.

Source Code + File Layout

After downloading the project from GitHub, the code and documentation for this post can be found in the “ios” directory. Inside of that directory, you will find the following files and directories:

• AiDB.xcodeproj: The Xcode project. On GitHub and in Windows, this will appear as a directory. For Mac OS users, it will look like a file, and it can be opened in Xcode. Doing so will take you to the source code, which includes a sample app, and you will also find the test cases we’ve created for the code.
• AiDB: The source code for the iOS project.
• AiDBTests: The source code for test cases that can be run against the project in Xcode.
• docs: Documentation that was generated from the source code is in this directory, inside of the “html” subdirectory.

Running the Sample APP

To run the sample app, open the included Xcode project in Xcode 5 or newer and click the Run button. It should run as-is in any current iPhone or iPad simulator. It will also run on any iOS device that works with Xcode 5, but you will need to setup the project with your provisioning profiles. If you have installed apps on your device before, this should be familiar to you.

The sample app makes use of the test_table that is installed by default with the project. It is a simple example of displaying, modifying and deleting records from a database.

I told you it was a simple sample.

Managing Schema Updates

One of the great features of this database abstraction layer is that it enables you to easily manage schema updates. The process of maintaining and distributing schema updates is usually a pain, but this system greatly simplifies the process for you. Schema updates are applied as a sequence of SQL files. To apply a schema update to your app’s database, follow these steps.

1. Right click the “updates” group under “Supporting Files”
2. Select “Other” then “Empty” and then “Next.”
3. Name the new file with a sequence number and the file extension “sql.” For example, if you are currently on schema version 2, to apply a version 3, your new file will be: 3.sql
4. Click “Next” and then “Create.”
5. The new file should contain SQL statements that implement your new schema changes. If you have multiple SQL statements for a schema update, put a caret (^) between each statement. Take a look at 1.sql as an example.

The sample app that we ship with the code for the iOS database abstraction layer includes two schema updates. The first creates two tables, and the second removes one of those tables. The remaining table is used to perform tests of the code. To remove the remaining test table, either:

• Add a file called 3.sql with this statement: DROP TABLE “test_table” or
• Remove 1.sql and 2.sql, delete the app from your device or simulator, then reinstall it, or
• Drop the test_table from the installed app’s database file by opening ~/Library/Application Support/iPhone Simulator/<iOS version>/Applications/<directory containing AiDB.app>/Library/Database/com.appideas.AiDB.sqlite in the SQLite client of your choice (we prefer Base).

Also, there is a constant defined in constants.h with the variable _idField. Querying your database will be a little bit easier if you use the value defined there as the field name for your table surrogate keys. The default value is “id” but it can be made into anything that works for you.

One final note about schema updates: The included test cases assume that the database schema has been updated to at least version 2, so if you decide to remove the test_table or start over with a fresh database that includes no version updates, you will need to adjust the test cases accordingly if you plan to use them.

Running Tests

The code that is available from GitHub contains a full set of regression tests using unit testing methodology as provided through the XCTest framework. If you care about your code or your customers, it is important to write, maintain and use regression tests. The importance of this point can never be overstated. You will release fewer bugs and spend less time tracking down bugs and maintaining your code if you are diligent in keeping a working and current test framework. Data in your app is important to your clients. Don’t let them down by being lazy with your tests!

When you open this project in Xcode, you will find a group called AiDBTests. Inside of that group are files that test the database abstraction layer as well as the core (functional) class files of the test app. To run the included tests, click “Product” and then “Test” (or Cmd-U on the keyboard). You can add on to these tests when you change the code to suit your needs, or you can add your own test cases. When you run the tests that we provide in the code, there are two things to consider:

1. There are destructive tests that will delete and reinstall the database. If you don’t want this to happen in your simulator or on your device, comment out the last two test methods in AiDatabaseTests. There is a comment immediately above where you should start the comment, just in case you’re not sure.
2. After each test, the contents of test_table are deleted. If you need to keep the contents of this table, comment this line: [db noReturnQuery:@”DELETE FROM test_table”]; that is in each of the test class’ tearDown methods. If you do this and don’t want test entries to linger in your table, you will need to target the test records for deletion. I’ll leave the exercise of that up to you, but as a hint, the TestTable class has a method called deleteWithPrimaryKey that can be of assistance.

To create a new test case, click “File” then “New” and then “File…” Select “Cocoa Touch” under “iOS” and then “Objective-C test case class” and then “Next.” Give your new test class a name, then click “Next” and “Create.”

You can follow our examples or check out documentation for XCTest—although, admittedly, Apple’s documentation for XCTest is pretty scant at this time.

There are several types of assertions defined by XCTest, but they behave differently than one would expect in many cases, so we chose to only use XCTAssertTrue and XCTAssertFalse for our tests, placing statements that we expect to return true or false as the arguments. For example, if we expect the number of rows returned by a query to be 2, we would write something like this:

1

XCTAssertTrue( ([db numRows] == 2), @"%@", self.failureMsg );

In this case, which may be obvious, we are asserting that it is true that [db numRows] is equal to two, and an error will be thrown if that assertion proves to be false. We have found that writing test cases for iOS in this manner (using only AssertTrue and AssertFalse) is simple, easy to understand and doesn’t ever produce unexpected results, so this is what we stick to.

One oddity of the XCTest assertions is that they don’t accept methods as input. In other words, what you see in the example above is perfectly valid, but this is not:

1

XCTAssertTrue( ([db numRows] == 2), [NSString stringWithFormat:@"Error: %@", someErrorMessage] );

However, this works just fine:

1

XCTAssertTrue( ([db numRows] == 2), @"Error: Something bad just happened." );

Just play around with it, and if you get stuck, search the Googles or hit us up on the forums.

API Documentation

Documentation for programmers is very important, and the best way we have found to make sure that it gets done is to maintain self-documenting code—that is, code with special commenting style that can run through a processor to generate documentation. The API documentation for this project is in the “html” subdirectory of the “docs” directory. Open the index.html file from that directory in any web browser to take a look.  The classes that are of most interest are AiDatabase and AiUtil. TestTable is the functional implementation class for the sample app, so that’s worth a look too.

If you make modifications to the code, you can generate your own API documentation using the Doxygen application, which can be found here: http://doxygen.org

To generate documentation, open the file called “Doxyfile” from the docs directory in Doxygen. Note that a bug in Doxygen prevents you from opening that file from Finder, so open Doxygen and then load the file using File > Open. Change the working directory, source code directory and destination directory as appropriate for your system, then click the “Run” button and “Run doxygen.” Generating documentation typically takes less than a second.

Wrapping Up

If you are writing data-driven apps, even if you don’t anticipate for your app to get very large or for it to be maintained over a long period of time (you’ll discover those assumptions to be disproved more often than not), getting a good, stable, consistent and light-weight foundation to your database access is paramount. As an added bonus, you’ll be able to write better code in less time. There’s nothing available for iOS that does a better job of that than what we have come up with here at APP(ideas).

This is already a powerful and important set of tools, but stay tuned for our upcoming series about Schema Adapters to see how your data-driven app app can become even easier to program and maintain with significantly less effort.

Be sure to stay up to date on our progress by subscribing to this blog, following us on social media or taking part in the discussions, either on individual posts or in the forums.

We’re also stoked when people buy our apps and leave positive reviews on the app store. That’s what enables us to continue doing what we love to do—making great apps, making fantastic code and sharing our hard-earned experience with whoever will listen.

Links to posts in this series:

1. Introduction
2. Part 1: PHP
3. Part 2: iOS (You’re there now)
4. Part 3: Android (Coming next)
5. Follow-up series: Schema Adapter (Coming soon-ish)