Using a SQLite Database with Xuni iOS

February 11 2016
0 Comments

A common concern for many data driven apps is whether to store data locally on device or have data available via webservice. Both options can potentially be valid choices depending on your application. While we've covered fetching and parsing web data on iOS we haven't spent as much time on data stored locally, particularly in a database. Apple provides some libraries for interacting with SQLite databases on iOS, and in this article we'll explore using a SQLite database with several Xuni controls.

General App Roadmap

Before we get started, it's best to go through the overall design of our application. The app will be based around a simple sales database with both some sales data and a list of employees. The app will start with an initial screen that contains a list of all of the employees displayed in a FlexGrid. The user should then be able to select an employee row and either edit that data or display a chart of that employee's sales performance alongside the average sales performance.

Creating a SQLite Database

Apple pre-installs a version of SQLite on the Mac which we can use to get started. The command line version of SQLite can be run via the Terminal application, and we'll getting started by using the following command: sqlite3 sampleSales.sql This will both start the SQLite command line interface and create a new database called sampleSales. We can execute SQL statements inside this interface to work with the database which we just created. The DB schema will be relatively simple for the purposes of our application. We'll only have two tables for this sample, one to store some employee information and another to store some sales information. Creating the two tables is straightforward, and we can do so by inputting the following SQL commands into the command line: CREATE TABLE employee(employeeID integer primary key autoincrement, firstname text, lastname text, location text); CREATE TABLE sales(employeeID integer primary key autoincrement, q1 integer, q2 integer, q3 integer, q4 integer, FOREIGN KEY (employeeID) REFERENCES employee(employeeID)); Inserting records into the database is also easily accomplished with commands similar to the following: INSERT INTO employee(firstname, lastname, location) VALUES ('Bill', 'Williamson', 'United States'); INSERT INTO sales(q1, q2, q3, q4) VALUES ('7654', '8098', '2396', '7912'); Once we've finished adding records to the database (and tested any other SQL statements we may want to use) we can close our of the interface by using the command .exit or .quit.

Getting Started in Xcode

Moving over to Xcode, we can create a new project to get started. We'll import in the Xuni Frameworks we'll be using (XuniCore, XuniChartCore, FlexChart, and FlexGrid) in the Target-> General tab. We'll also need import in another library to handle working with SQLite called libsqlite3.tbd. The database file should now be copied into the Xcode project (you can drag and drop it into the project). A small dialog box should open where we need to make sure the database is copied and added to the target.

Creating a Database Manager Class

We'll create another Cocoa Touch class to handle connecting to and querying the database. The class will generically be called DatabaseManager. We'll add a few properties and public methods to the header as well as import sqlite3.h from the library which we added to the project earlier.


#import <Foundation/Foundation.h>  
#import "sqlite3.h"  

@interface DatabaseManager : NSObject  

@property (nonatomic, strong) NSMutableArray *columns;  

-(instancetype)initWithDatabaseFilename:(NSString *)dbFilename;  

-(NSArray *)loadDataFromDB:(NSString *)query;  

-(void)executeQuery:(NSString *)query;  
@end

Next, we can start working on the implementation file. We'll add some private properties and methods to the interface that out public methods will use internally. These properties and methods mostly relate to the database's name and location, mechanisms for querying it, and somewhere to store the results. Also note there is a runExecutableQuery and a runNonExecutableQuery method. An executable query is something that will change the database (insert, delete, add, etc.). A non-executable query would be something along the lines of a select statement where we are only returning a result.


#import "DatabaseManager.h"  

@interface DatabaseManager()  

@property (nonatomic, strong) NSString *documentsDirectory;  
@property (nonatomic, strong) NSString *databaseFilename;  
@property (nonatomic, strong) NSMutableArray *results;  

-(void)copyDatabaseIntoDocumentsDirectory;  
-(void)runExecutableQuery:(const char *)query;  
-(void)runNonExecutableQuery:(const char *)query;  

@end

The initWithDatabaseFilename allows us to use a string to specify which database we want to use. If the database does not exist in the documents directory than the copyDatabaseIntoDocumentsDirectory copies it from the main bundle to the documents.


-(instancetype)initWithDatabaseFilename:(NSString *)databaseFilename{  
    self = [super init];  
    if (self) {  
        // Set the documents directory path to the documentsDirectory property and copy database to documents direcory if needed.  
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, true);  
        self.documentsDirectory = [paths objectAtIndex:0];  
        self.databaseFilename = databaseFilename;  
        [self copyDatabaseIntoDocumentsDirectory];  
    }  
    return self;  
}  
-(void)copyDatabaseIntoDocumentsDirectory{  
    // Check if the database already exists in the documents directory  
    NSString *destinationPath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];  
    if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {  
        // The database file does not exist and needs to be copied from bundle  
        NSString *source = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFilename];  
        NSError *error;  
        [[NSFileManager defaultManager] copyItemAtPath:source toPath:destinationPath error:&error];  
        if (error != nil) {  
            NSLog(@"%@", [error localizedDescription]);  
        }  
    }  
}

As mentioned earlier, the runNonExecutableQuery will work with a read only operation from the database which we'll later use when we supply select queries to return the result sets that we'll visualize in the app. Basically we'll be using some supplied SQLite objects and methods to open a connection to the database, feed the database our query, loop through the results, and then close the connection.


-(void)runNonExecutableQuery:(const char *)query{  
    sqlite3 *sqlite3Database;  
    NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];  

    if (self.results != nil) {  
        [self.results removeAllObjects];  
        self.results = nil;  
    }  
    self.results = [[NSMutableArray alloc] init];  

    if (self.columns != nil) {  
        [self.columns removeAllObjects];  
        self.columns = nil;  
    }  
    self.columns = [[NSMutableArray alloc] init];  

    // Open database  
    BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);  
    if(openDatabaseResult == SQLITE_OK) {  
        // sqlite3_stmt object stores the query after having been compiled into a SQLite statement  
        sqlite3_stmt *compiledStatement;  
        // Load all data from database to memory  
        BOOL prepareStatementResult = sqlite3\_prepare\_v2(sqlite3Database, query, -1, &compiledStatement, NULL);  
        if(prepareStatementResult == SQLITE_OK) {  
            // load from the database.  
            NSMutableArray *dataRow;  
            // Loop through the results adding row by row  
            while(sqlite3\_step(compiledStatement) == SQLITE\_ROW) {  
                dataRow = [[NSMutableArray alloc] init];  
                int totalColumns = sqlite3\_column\_count(compiledStatement);  
                // Go through all columns fetching data  
                for (int i=0; i<totalColumns; i++){  
                    // Convert the column data to chars  
                    char \*dbDataAsChars = (char \*)sqlite3\_column\_text(compiledStatement, i);  
                    if (dbDataAsChars != NULL) {  
                        // Convert the characters to string  
                        [dataRow addObject:[NSString  stringWithUTF8String:dbDataAsChars]];  
                    }  
                    // Keep the current column name  
                    if (self.columns.count != totalColumns) {  
                        dbDataAsChars = (char *)sqlite3\_column\_name(compiledStatement, i);  
                        [self.columns addObject:[NSString stringWithUTF8String:dbDataAsChars]];  
                    }  
                }  
                // Store each fetched data row in results if there is data  
                if (dataRow.count > 0) {  
                    [self.results addObject:dataRow];  
                }  
            }  
        }  
        else {  
            //case where database cannot be opened  
            NSLog(@"%s", sqlite3_errmsg(sqlite3Database));  
        }  
        //release compiled statement from memory  
        sqlite3_finalize(compiledStatement);  

    }  
    // Close db  
    sqlite3_close(sqlite3Database);  
}

The runExecutable method is similar, but we'll use it after editing the data to insert our changes back into the database. The code is actually a little more simple since we aren't returning any results, but instead noting success or failure.


-(void)runExecutableQuery:(const char *)query{  
    sqlite3 *sqlite3Database;  
    NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];  
    if (self.results != nil) {  
        [self.results removeAllObjects];  
        self.results = nil;  
    }  
    self.results = [[NSMutableArray alloc] init];  
    if (self.columns != nil) {  
        [self.columns removeAllObjects];  
        self.columns = nil;  
    }  
    self.columns = [[NSMutableArray alloc] init];  
    // Open database  
    BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);  
    if(openDatabaseResult == SQLITE_OK) {  
        // sqlite3_stmt object stores the query after having been compiled into a SQLite statement  
        sqlite3_stmt *compiledStatement;  
        // Load all data from database to memory  
        BOOL prepareStatementResult = sqlite3\_prepare\_v2(sqlite3Database, query, -1, &compiledStatement, NULL);  
        if(prepareStatementResult == SQLITE_OK) {  
            BOOL executeQueryResults = sqlite3_step(compiledStatement);  
            if (executeQueryResults) {  
                NSLog(@"Success");  
            }  
            else {  
                NSLog(@"DB Error: %s", sqlite3_errmsg(sqlite3Database));  
            }  
        }  
        else {  
            //case where database cannot be opened  
            NSLog(@"%s", sqlite3_errmsg(sqlite3Database));  
        }  
        //release compiled statement from memory  
        sqlite3_finalize(compiledStatement);  
    }  
    // Close db  
    sqlite3_close(sqlite3Database);  
}

Finally, we need to add to our public methods which use the private runQuery methods that we just created. The loadDataFromDB method will later be used to get the results that we'll visualize with our Xuni controls, and the executeQuery method which we'll use when editing the data.


-(NSArray *)loadDataFromDB:(NSString *)query{  
    //query string is converted to a char* object  
    [self runNonExcutableQuery:[query UTF8String]];  
    return (NSArray *)self.results;  
}  
-(void)executeQuery:(NSString *)query{  
    // Run the executable query  
    [self runExecutableQuery:[query UTF8String]];  
}

Creating Data Models for the Controls

Since the database just returns an array of the results, we'll need to create models to package this data for the controls. Our GridModel is going to have properties for each of the columns we'll attach to the FlexGrid. The generateGridArray method takes the results array that the database has returned and iterates through it to create GridModel objects. The objects are added to a NSMutableArray which will eventually be returned to connect to the itemsSource of our FlexGrid. The header:


@interface GridModel : NSObject  

@property NSNumber *employeeID;  
@property NSString *firstName;  
@property NSString *lastName;  
@property NSString *location;  
@property NSNumber *total;  
+(NSMutableArray *)generateGridArray:(NSArray *)array;  

@end

And the implementatin:


#import "GridModel.h"  

@implementation GridModel  

+(NSMutableArray *)generateGridArray:(NSArray *)array{  
    NSMutableArray *gridArray = [[NSMutableArray alloc] init];  
    GridModel *gridObject;  
    NSArray *subArray;  
    for (int i = 0; i < array.count; i ++){  
        gridObject = [[GridModel alloc] init];  
        subArray = [array objectAtIndex:i];  
        gridObject.employeeID = [subArray objectAtIndex:0];  
        gridObject.firstName = [subArray objectAtIndex:1];  
        gridObject.lastName = [subArray objectAtIndex:2];  
        gridObject.location = [subArray objectAtIndex:3];  
        gridObject.total = [subArray objectAtIndex:4];  
        [gridArray addObject:gridObject];  
    }  
    return gridArray;  
}  
@end

The ChartModel is similar, but we're concerned with less data here. Basically, we only need to worry about the quarter we're in, the employee's sales for that quarter, and the average sales for the quarter. The generateChartArray method just packages the database results into this form. Eventually a NSMutableArray will be returned which we can connect to the itemsSource of our FlexChart later on. The header:


@interface ChartModel : NSObject  

@property NSString *quarter;  
@property NSNumber *average;  
@property NSNumber *employeeSales;  

+(NSMutableArray *)generateChartArray:(NSArray *)array;  

@end

And the implementation:


#import "ChartModel.h"  
@implementation ChartModel  
+(NSMutableArray *)generateChartArray:(NSArray *)array{  
    NSMutableArray *chartArray = [[NSMutableArray alloc] init];  
    ChartModel *chartObject;  
    for (int i = 0; i < 4; i++){  
        chartObject = [[ChartModel alloc] init];  
        chartObject.average = [[array objectAtIndex:0] objectAtIndex:i];  
        chartObject.employeeSales = [[array objectAtIndex:1] objectAtIndex:i];  
        chartObject.quarter = [NSString stringWithFormat:@"Q%d", i+1];  
        [chartArray addObject:chartObject];  
    }  
    return chartArray;  
}  
@end

ViewControllers

I'm not going to spend as much time on the view controllers since they're mostly by the numbers (you can look at the included sample if you want to see the full code), but there are a few things that will be unique with regards to interacting with the database. We'll need to import the DatabaseManager class into each of our ViewControllers, and declare a databaseManager property. The usage for the DatabaseManager follows this general pattern:


 self.databaseManager = [[DatabaseManager alloc] initWithDatabaseFilename:@"sampleSales.sql"];  
 [self loadData];  
 NSMutableArray *gridData = [GridModel generateGridArray:self.gridInfo];  
 grid.itemsSource = gridData;

You'll notice I'm calling the loadData method in the above code. There will be an implementation of this method in each ViewController, and it will vary between each since this is where we'll specify the query that returns the data we interact with. In the GridViewController, this is a relatively straightforward select statement:


-(void)loadData{  
    // Query DB  
    NSString *query = @"select employee.employeeID, employee.firstname, employee.lastname, employee.location, (sales.q1 + sales.q2 + sales.q3 + sales.q4) as 'Total' from employee, sales where employee.employeeID = sales.employeeID;";  
    //clear old data  
    if (self.gridInfo != nil) {  
        self.gridInfo = nil;  
    }  
    //load results into array  
    self.gridInfo = [[NSArray alloc] initWithArray:[self.databaseManager loadDataFromDB:query]];  

}

The loadData method of the ChartViewController is a bit more complicated as we're dealing with the union of a specific result for an employee as well as a result that's the average of all records. It's also important that we explicitly preserve a specific order here so the average is always the first result returned and the specific employee sales is always the second.


-(void)loadData{  
    // Form the query  
    NSString *query = [NSString stringWithFormat:@"select q1, q2, q3, q4 from (select AVG(q1) as q1, AVG(q2) as q2, AVG(q3) as q3, AVG(q4) as q4, 1 as n from sales union select q1, q2, q3, q4, 2 as n from sales where employeeID = %d) order by n;", recordIDForChart];  
    if (self.chartInfo != nil) {  
        self.chartInfo = nil;  
    }  
    self.chartInfo = [[NSArray alloc] initWithArray:[self.databaseManager loadDataFromDB:query]];  

}

Finally, the loadData for the EditViewController is probably the most straightforward implementation as it is a simple join statement that returns all of the individual fields for editing.


-(void)loadData{  
    // Form the query.  
    NSString *query = [ NSString stringWithFormat:@"select sales.employeeID, firstname, lastname, location, q1, q2, q3, q4 from employee, sales where employee.employeeID = sales.employeeID and employee.employeeID = %d;", recordIDForEdit];  

    // Get the results.  
    if (self.rowInfo != nil) {  
        self.rowInfo = nil;  
    }  
    self.rowInfo = [[NSArray alloc] initWithArray:[self.databaseManager loadDataFromDB:query]];  

}

Clicking the commit button on the EditViewController's screen is the single instance in our app where we'll be using an executable query. Here, we'll update both tables with the modifications that we've made.


-(void)commitButtonClicked{  
    NSString *query = [ NSString stringWithFormat:@"update employee set firstname = '%@', lastname = '%@', location = '%@' where employeeID = %d;", firstNameField.text, lastNameField.text, locationField.text,recordIDForEdit];  
    [self.databaseManager executeQuery:query];  
    query = [ NSString stringWithFormat:@"update sales set q1 = '%@', q2 = '%@', q3 = '%@' , q3 = '%@' where employeeID = %d;", q1Field.text, q2Field.text, q3Field.text, q4Field.text, recordIDForEdit];  
    [self.databaseManager executeQuery:query];  
}

When the full app is complete it allows you to navigate easily between several different visualizations of the data.

Conclusion

There are many possibilities for adding on to this structure, but this example should provide a basic idea of how a project using a SQLite database could work. SQLite is relatively easy to work with, and all of the tools you need are included.

Download the XuniSalesDB sample >>

MESCIUS inc.