Lesson 2: Adding a database to the application

The Data Access class will be the only class which interacts with the database. This way, if a schema change or other database-related change is required, there is only one place where updates are required.

To set up the Data Model Class

Control-click the Classes folder in the Project window. In the File menu, select New File from the Add menu.
Choose iPhone OS Cocoa Touch Class in the left-hand selection.
Select a new Objective-C class.
Make sure the Subclass of selection is NSObject.
Click Next.
Name the file DataAccess.mm. The .mm extension is important as it signals to Xcode that the file contains both Objective-C and C++.
Make sure the Also create DataAccess.h box is checked.
For Location, choose the Names/Classes subfolder.
Click Finish.

When the application launches, it needs to initialize the UltraLite database manager and connect to the local database. To do this, the class creates a singleton instance of this data access object which is used by the RootViewController to display and manage the list of names.

In the DataAccess.mm file, add the following code in the implementation:

static DataAccess *        sharedInstance = nil;

+ (DataAccess *)sharedInstance {
    // Create a new instance if none was created yet
    if (sharedInstance == nil) {
        sharedInstance = [[super alloc] init];
        [sharedInstance openConnection];
    }
    
    // Otherwise, just return the existing instance
    return sharedInstance;
}

Before being able to use any UltraLite classes and methods, you have to import the ulcpp header file. Add the following to the existing imports in DataAccess.h:

#import "ulcpp.h"

Before the connection can be opened, the database managers Init method is used to initialize the UltraLite runtime. Once initialized, an attempt to connect to the database will indicate whether the database exists. Add the following instance variable to the DataAccess header:

ULConnection *     connection;

In the DataAccess.mm file, add the following code in the implementation:

- (void)openConnection {
    NSLog(@"Connect to database.");
    if (ULDatabaseManager::Init()) {
        NSArray *	paths = NSSearchPathForDirectoriesInDomains(
                                                                 NSDocumentDirectory,
                                                                 NSUserDomainMask,
                                                                 YES);
        NSString *	documentsDirectory = [paths objectAtIndex:0];
        NSString *	writableDBPath = [documentsDirectory 
                                        stringByAppendingPathComponent:
                                        @"Names.udb"];
        ULConnection *    conn = nil;
        const char *    connectionParms;
        ULError            error;

        connectionParms = [[NSString stringWithFormat:@"DBF=%@",
                            writableDBPath]
                            UTF8String];
        
        // Attempt connection to the database
        conn = ULDatabaseManager::OpenConnection(
                                                 connectionParms,
                                                 &error);
        // If database file not found, create it and create the schema
        if (error.GetSQLCode() == SQLE_ULTRALITE_DATABASE_NOT_FOUND) {
            conn = [self createDatabase:connectionParms];
        }
        connection = conn;
    } else {
        NSLog(@"UL Database Manager initialization failed.");
        connection = nil;
    }
}

The database schema of the application is composed of a single table with two columns. The Names table has an ID column which uses UUIDs and a name column which stores the names as VARCHARs. The ID column uses UUIDs to easily support row insertions from remote databases using MobiLink. This is discussed in Lesson 6: Adding synchronization.

Displaying data in a table view requires that each row be accessible using a 1-based index. To do this, the database uses an ascending index on the name column. Each row's index will be equal to its position in the alphabetical list of names.

In the DataAccess.mm file, add the following code in the implementation:

- (ULConnection *)createDatabase:(const char *)connectionParms {
    const char *    CREATE_TABLE = 
    "CREATE TABLE Names ("
    "id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,"
    "name VARCHAR(254) NOT NULL)";
    const char *    CREATE_INDEX = 
    "CREATE UNIQUE INDEX namesIndex ON Names(name ASC)";
    const char *    createParms = 
    "page_size=4k;utf8_encoding=true;collation=UTF8BIN";
    ULError            error;
    ULConnection *    conn;

    conn = ULDatabaseManager::CreateDatabase(
                                             connectionParms,
                                             createParms,
                                             &error);
    if (!conn) {
        NSLog(@"Error code creating the database: %ld",
              error.GetSQLCode());
    } else {
        NSLog(@"Creating Schema.");
        conn->ExecuteStatement(CREATE_TABLE);
        conn->ExecuteStatement(CREATE_INDEX);
    }
    return conn;
}

Note

openConnection uses createDatabase, so either createDatabase must come before openConnection or its method signature must be added to the header file.

Add a dealloc method to finalize the UltraLite runtime,

- (void)dealloc {
    NSLog(@"Finalizing DB Manager.");
    connection->Close();
    ULDatabaseManager::Fini();
    
    [super dealloc];
}

And a fini method to release the instance,

+ (void)fini {
    [sharedInstance release];
}

Add the method signatures to the header file after the interface curly brace block:

// Release objects.
- (void)dealloc;

// Singleton instance of the DataAccess class. 
+ (DataAccess*)sharedInstance;

// Finalize the Database Manager when done with the DB.
+ (void)fini;

Call the fini method from the NamesAppDelegate's applicationWillTerminate method:

- (void)applicationWillTerminate:(UIApplication *)application {
    // Save data if appropriate
    [DataAccess fini];

Also, since the application delegate is calling fini, it must:

#import "DataAccess.h"