Lesson 2: Adding a database to the application

The Data Access class is the only class that 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.

Set up the Data Model Class

Control-click the Classes folder in the Project window. Click Add » New File.
Click iPhone OS Cocoa Touch Class in the left selection.
Click 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 Also create DataAccess.h is selected.
For Location, click 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 you can use any UltraLite classes and methods, you must import the ulcpp header file. Add the following line to the existing imports in DataAccess.h:

#import "ulcpp.h"

Before the connection can be opened, the database manager's Init method is used to initialize the UltraLite runtime. Once it is initialized, an attempt to connect to the database indicates whether the database exists. Add the following instance variable to the DataAccess.h header inside the interface block:

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 that uses UUIDs and a name column that stores the names as VARCHARs. The ID column uses UUIDs to easily support row insertions from remote databases using MobiLink.

Note

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

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 is 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;
}

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 the DataAccess header file. Add the following to namesAppDelegate.h:

#import "DataAccess.h"

At this point you should build the application to test that it builds without errors. From the Build menu, click Build.