How to switch your iOS app to Vertabelo Mobile ORM

Our application

We've got a very simple application for iOS called Contacts. In the app's main screen, there is, surprisingly, a list of our contacts.

Clicking a person's name will show us their number.

Obviously, we can add a new contact.

And delete existing ones.

Connection with database

Underneath the hood, we're using an SQLite database consisting of only one table.

To connect with the database we use a class called SQLiteManager. We've got loadDataFromDB:method there for SELECT queries, which returns us an array of object arrays representing each row, and executeQuery: for INSERT, UPDATE and DELETE queries.

@interface SQLiteManager : NSObject

- (id)initWithDatabaseFilename:(NSString *)dbFileName;
- (NSArray *)loadDataFromDB:(NSString *)query;
- (void)executeQuery:(NSString *)query

@end
                

That's an implementation of SQLiteManager.

@interface SQLiteManager ()

@property (nonatomic) NSString *documentsDirectory;
@property (nonatomic) NSString *dbFilename;
@property (nonatomic) NSMutableArray *results;
- (void)queryRun:(const char *)query isExecutable:(BOOL)queryExecutable;
- (void)copyDatabaseIntoAppSupportDirectory;
- (void)addQueryResult:(sqlite3_stmt *)queryResult;

@end

@implementation SQLiteManager

- (void)copyDatabaseIntoAppSupportDirectory {
    NSString *destinationPath = [self.documentsDirectory
                        stringByAppendingPathComponent:self.dbFilename];
    if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {
        NSString *sourcePath = [[[NSBundle mainBundle] resourcePath]
                        stringByAppendingPathComponent:self.dbFilename];
        NSError *error;
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath
                                        toPath:destinationPath error:&error];
        if (error != nil) {
            NSLog(@"%@", [error localizedDescription]);
        }
    }
}

- (id)initWithDatabaseFilename:(NSString *)dbFilename {
    self = [super init];
    if (self) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSApplicationSupportDirectory, NSUserDomainMask, YES);
        self.documentsDirectory = [paths objectAtIndex:0];
        self.dbFilename = dbFilename;
        [self copyDatabaseIntoAppSupportDirectory];
    }
    return self;
}

- (void)addQueryResult:(sqlite3_stmt *)queryResult {
    while(sqlite3_step(queryResult) == SQLITE_ROW) {
        NSMutableArray *rowData = [[NSMutableArray alloc] init];
        int totalColumns = sqlite3_column_count(queryResult);

        for (int i=0; i
            char *dataInChars = (char *)sqlite3_column_text(queryResult, i);
            if (dataInChars) {
                [rowData addObject:[NSString stringWithUTF8String: dataInChars]];
            }
        }

        if (rowData.count > 0) {
            [self.results addObject:rowData];
        }
    }
}

- (void)queryRun:(const char *)query isExecutable:(BOOL)queryExecutable{
    sqlite3 *db;
    NSString *databasePath = [self.documentsDirectory
                        stringByAppendingPathComponent:self.dbFilename];
    if (self.results != nil) {
        [self.results removeAllObjects];
        self.results = nil;
    }

    self.results = [[NSMutableArray alloc] init];
    if (sqlite3_open([databasePath UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        return;
    }

    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, query, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"%s", sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if (!queryExecutable){
        [self addQueryResult:statement];
    }
    else {
        if (!sqlite3_step(statement)) {
            NSLog(@"%s", sqlite3_errmsg(db));
        }
    }
    sqlite3_finalize(statement);
    sqlite3_close(db);
}

- (NSArray *)loadDataFromDB:(NSString *)query{
    [self queryRun:[query UTF8String] isExecutable:NO];
    return (NSArray *)self.results;
}

- (void)executeQuery:(NSString *)query {
    [self queryRun:[query UTF8String] isExecutable:YES];
}

@end
                

SQLiteManager operates on a database using SQL queries provided just as plain strings - from your experience as a developer, you probably know how vulnerable and error-prone this approach is. Fortunately, with Vertabelo Mobile ORM you have no longer to worry about syntax errors or concentrate to not make any typo in your string queries. What's more, you don't have to write such a complex implementations like the one above, since Vertabelo Mobile ORM handles the communication with database itself. Let's go back to the code and make working with our database reliable, secure and convenient - with Vertabelo Mobile ORM.

Contacts app requires its data sources to conform to the ContactsService protocol.

@protocol ContactsService 

- (void)addNewContact:(Contact *)toAdd;
- (NSUInteger)size;
- (Contact *)contactWithId:(NSUInteger)objectId;
- (NSArray *)contactsArray;
- (void)removeContactWithId:(NSUInteger)toRemove;

@end
                

Current implementation

That's how it is realized by class named DatabaseContactsServiceImpl, which uses SQLiteManager to get and set data for the app. At first, we have to initialize the object with SQLiteManager.

- (id)init {
    self = [super init];
    if (self) {
        self.db = [[SQLiteManager alloc] initWithDatabaseFilename:@"contacts.sqlite"];
    }
    return self;
}
                

This is the addNewContact: method's implementation. As you see, lots of hardcoded strings and even more places to make a mistake.

- (void)addNewContact:(Contact *)toAdd {
    NSString *query = [NSString stringWithFormat: @"INSERT INTO contact (first_name, last_name, phone_number) VALUES (\"%@\", \"%@\", \"%@\")", toAdd.columnFirstName, toAdd.columnLastName, toAdd.columnPhoneNumber];
    [self.db executeQuery:query];
}
                

To make it work, we also have to write a Contact class which will be representing our contact table as an object.

@interface Contact : NSObject

@property NSNumber *id;
@property NSString *firstName;
@property NSString *lastName;
@property NSString *phoneNumber;

- (NSComparisonResult)compare:(Contact *)contactToCompare;

@end
                

Methods size and removeContactWithId: seem to have the same weaknesses.

- (NSUInteger)size {
    NSString *query = @"SELECT COUNT(*) FROM contact";
    NSArray *queryResult = [self.db loadDataFromDB:query];
    return (NSUInteger) [queryResult objectAtIndex:0];
}

- (void)removeContactWithId:(NSUInteger)toRemove {
    NSString *query = [NSString stringWithFormat:@"DELETE FROM contact WHERE id=%tu", toRemove];
    [self.db executeQuery:query];
}
                

When it comes to getting the objects from a database, things get even more complicated.

- (Contact *)contactWithId:(NSUInteger)objectId {
    NSString *query = [NSString stringWithFormat: @"SELECT * FROM contact WHERE id=%tu", objectId];
    NSArray *row = [self.db loadDataFromDB:query];
    return [self rowToContact:[row objectAtIndex:0]];
}

- (NSArray *)contactsArray {
    NSString *query = @"SELECT * FROM contact";
    NSArray *dbToArray = [self.db loadDataFromDB:query];
    NSMutableArray *resultArray = [[NSMutableArray alloc] init];
    for (NSArray *row in dbToArray) {
        [resultArray addObject:[self rowToContact:row]];
    }
    return [resultArray sortedArrayUsingSelector:@selector(compare:)];
}
                

Now, we also have to switch from a database representation of the contact to the objective one. For that purpose, we had to create a rowToContact: method.

- (Contact *)rowToContact:(NSArray *)row {
    Contact *contactFromDb = [[Contact alloc] init];
    contactFromDb.if = [[row objectAtIndex:0] integerValue];
    contactFromDb.firstName = [row objectAtIndex:1];
    contactFromDb.lastName = [row objectAtIndex:2];
    contactFromDb.phoneNumber = [row objectAtIndex:3];
    return contactFromDb;
}
                

Huh, that was a little bit overwhelming amount of things to code, remember and worry about for a just one small table, wasn't it? Now, let us make your life easier with Vertabelo Mobile ORM!

Generating O/R mapping

We begin by generating the O/R mapping for our database model and we get these files:

  • Contact.h
  • Contact.m
  • ContactDAO.h
  • ContactDAO.m
  • ContactRowHandler.h
  • ContactRowHandler.m

Thanks to Contact class, you no longer have to bother about creating classes consisted with your model and overriding basic NSObject methods for every table in your database - that's another thing Vertabelo Mobile ORM is doing for you, just take a look.

@interface Contact : NSObject

// Property representing column id.
@property (nonatomic) NSNumber *columnId;

// Property representing column first_name.
@property (nonatomic) NSString *columnFirstName;

// Property representing column last_name.
@property (nonatomic) NSString *columnLastName;

// Property representing column phone_number.
@property (nonatomic) NSString *columnPhoneNumber;

// Object factory
+ (Contact *)contact;

// Designated initializer
- (id)initWithId:(NSNumber *)anId firstName:(NSString *)aFirstName lastName:(NSString *)aLastName phoneNumber:(NSString *)aPhoneNumber;

// Compares two instances of class Contact.
- (BOOL)isEqualToContact:(Contact *)contact;

@end
                
@implementation Contact

+ (Contact *)contact {
    return [[Contact alloc] init];
}

- (id)initWithId:(NSNumber *)anId firstName:(NSString *)aFirstName lastName:(NSString *)aLastName phoneNumber:(NSString *)aPhoneNumber {
    self = [super init];
    if (self) {
        self.columnId = anId;
        self.columnFirstName = aFirstName;
        self.columnLastName = aLastName;
        self.columnPhoneNumber = aPhoneNumber;
    }
    return self;
}

- (BOOL)isEqualToContact:(Contact *)contact {
    if (!contact) {
        return NO;
    }
    BOOL result = YES;
    result = result && ((!self.columnId && !contact.columnId) || [self.columnId isEqualToString:contact.columnId]);
    result = result && ((!self.columnFirstName && !contact.columnFirstName) || [self.columnFirstName isEqualToString:contact.columnFirstName]);
    result = result && ((!self.columnLastName && !contact.columnLastName) || [self.columnLastName isEqualToString:contact.columnLastName]);
    result = result && ((!self.columnPhoneNumber && !contact.columnPhoneNumber) || [self.columnPhoneNumber isEqualToString:contact.columnPhoneNumber]);

    return result;
}

#pragma mark - NSObject

// Non-argument initializer implementation using designated initializer.
- (id)init {
    return [self initWithId:nil firstName:nil lastName:nil phoneNumber:nil];
}

- (BOOL)isEqual:(id)object {
    if (self == object) {
        return YES;
    } else if (![object isKindOfClass:[Contact class]]) {
        return NO;
    } else {
        return [self isEqualToContact:(Contact *)object];
    }
}

- (NSUInteger)hash {
    NSUInteger prime = 31;
    NSUInteger result = 1;
    result = prime * result + [self.columnId hash];
    result = prime * result + [self.columnFirstName hash];
    result = prime * result + [self.columnLastName hash];
    result = prime * result + [self.columnPhoneNumber hash];
    return result;
}

- (NSString *)description {
    NSMutableString *result = [[NSMutableString alloc] init];
    [result appendString:@"Contact:\n"];
    [result appendFormat:@"\t%@: %@\n", @"Id", self.columnId];
    [result appendFormat:@"\t%@: %@\n", @"First name", self.columnFirstName];
    [result appendFormat:@"\t%@: %@\n", @"Last name", self.columnLastName];
    [result appendFormat:@"\t%@: %@\n", @"Phone number", self.columnPhoneNumber];
    return result;
}

@end
                

Note

Objects representing database table generated by Vertabelo Mobile ORM will problably differ from those you were using before, especially in the terms of naming conventions. To avoid problems with that we suggest you to look through the generated classes and switch to our names with the help of your IDE. The rule we use for naming is just simple CamelCase: my_table -> MyTable for tables and my_column -> columnMyColumn for columns (properties). ‘column' is a default property prefix, but it can be changed - you can even go without any prefix, but in that case it may happen that you will face problems connected with Objective-C reserved words.

However, the most important and the most powerful class we are given by Vertabelo Mobile ORM is ContactDAO.

@interface ContactDAO : NSObject

@property (readonly) MOTableExpression *TABLE_EXPRESSION
@property (readonly) MOColumn *ID;
@property (readonly) MOColumn *FIRST_NAME;
@property (readonly) MOColumn *LAST_NAME;
@property (readonly) MOColumn *PHONE_NUMBER;
@property (readonly) id  rowHandler;

+ (ContactDAO *)daoWithDataSource:(id )dataSource;
- (id)initWithDataSource:(id )dataSource;
- (NSArray *)select:(MOSelectQuery *)query;
- (NSArray *)select:(MOSelectQuery *)query withRowHandler:(id )rowHandler;
- (Contact *)selectOne:(MOSelectQuery *)query;
- (Contact *)getById:(NSNumber *)id;
- (NSArray *)getContactArray;
- (NSArray *)getContactArrayOrderAscByColumn:(MOAExp *)orderBy;
- (NSArray *)getContactArrayOrderDescByColumn:(MOAExp *)orderBy;
- (NSArray *)getContactArrayWhere:(MOLExp *)where;
- (NSArray *)getContactArrayWhere:(MOLExp *)where orderAscByColumn:(MOAExp *)orderBy;
- (NSArray *)getContactArrayWhere:(MOLExp *)where orderDescByColumn:(MOAExp *)orderBy;
- (NSUInteger)getCount;
- (NSUInteger)getCountWhere:(MOLExp *)where;
- (id)invokeFunction:(NSString *)name withArgument:(MOAExp *)arg;
- (id)invokeFunction:(NSString *)name withArgument:(MOAExp *)arg where:(MOLExp *)where;
- (id)invokeForAllFunction:(NSString *)name;
- (void)insert:(MOInsertStatement *)query;
- (void)insertContact:(Contact *)contact;
- (void)insertContactArray:(NSArray *)array;
- (void)update:(MOUpdateQuery *)query;
- (void)updateContact:(Contact *)contact;
- (void)updateContactArray:(NSArray *)array;
- (void)delete:(MODeleteQuery *)query;
- (void)deleteContact:(Contact *)contact;
- (void)deleteContactArray:(NSArray *)array;
- (void)deleteById:(NSNumber *)id;
- (void)deleteWhere:(MOLExp *)where;
- (BOOL)isEqualByPK:(Contact *)contact1 and:(Contact *)contact2;

@end
                

DAO classes allow us to do lots of SQL magic with just one invocation of a method. Now, let's use this magic for our app. We're going to rebuild DatabaseContactsServiceImpl, step by step, now using ContactDAO.

Using Vertabelo Mobile ORM

Firstly, we have to add a ContactDAO property.

@property ContactDAO *dao
                

To initialize any DAO we need a data source. MOSQLiteDataSource is a protocol for data sources to conform to.

@protocol MOSQLiteDataSource 
- (sqlite3 *)getConnection;
@end
                

You can write your own class for a data source or use MOSQLiteOpenHelper provided by us. To use MOSQLiteOpenHelper correctly, you have to create a class inheriting from MOSQLiteOpenHelper, let's name it, for example, MyHelper.

@interface MyHelper : MOSQLiteOpenHelper

@end
                

The only thing we have to do in that class is implementing configure method

@implementation MyHelper

- (void)configure {
    [self addCreateScript:@"create.sql"];
    [self setDbVersion:1];
}

@end
                

File create.sql should be a create script for your database. For the new users of your application, database will be created based on this script. However, you don't have to worry about users, who are already using the app and have any data in it - if you were storing a database in Library/Application Support directory, it will be detected automatically. If not, then you can just go to the MOSQLiteOpenHelper implementation and change default database directory from NSApplicationSupportDirectory to another one. Full flexibility - that's why we're giving you our source code. Providing database version is compulsory for the purpose of performing migrations.

Let's write init method using MyHelper. From now, we will show you the initial app's code above, just to let you notice you how much the code is simplifying.

- (id)init {
    self = [super init];
    if (self) {
        self.db = [[SQLiteManager alloc] initWithDatabaseFilename:@"contacts.sqlite"];
    }
    return self;
}
                
- (id)init {
    self = [super init];
    if (self) {
        id  *db = [[MyHelper alloc] initWithDbName:@"contacts.sqlite"];
        self.dao = [ContactsDAO daoWithDataSource:db];
    }
    return self;
}
                

Now, having DAO initialized, we're ready to see the *real* power of Vertabelo Mobile ORM.

- (void)addNewContact:(Contact *)toAdd {
    NSString *query = [NSString stringWithFormat: @"INSERT INTO contacts(first_name, last_name, phone_number) VALUES"(\"%@\", \"%@\", \"%@\")", toAdd.columnFirstName, toAdd.columnLastName, toAdd.columnPhoneNumber];
        [self.db executeQuery:query];
}
                
- (void)addNewContact:(Contact *)toAdd {
    [self.dao insertContact:toAdd];
}
                

Wow, wasn't it great? Let's go ahead.

- (NSUInteger)size {
    NSString *query = @"SELECT COUNT(*) FROM contacts";
    NSArray *queryResult = [self.db loadDataFromDB:query];
    return (NSUInteger) [queryResult objectAtIndex:0];
}
                
- (NSUInteger)size {
    return [self.dao getCount];
}
                

Now, it's time to improve the delete feature!

- (void)removeContactWithId:(NSUInteger)toRemove {
    NSString *query = [NSString stringWithFormat:@"DELETE FROM contacts WHERE id=%tu", toRemove];
    [self.db executeQuery:query];
}
                
- (void)removeContactWithId:(NSUInteger)toRemove {
    [self.dao deleteById:@(toRemove)];
}
                

Do you remember the problem with handling the rows given by the database? Now, you don't have to worry about it, since we've generated for you a ContactRowHandler class, which deals exactly with that. Of course, if you want to take only some columns from the table you can write your own row handler - all it has to do is to conform to the MORowHandler protocol. To make things even easier, we prepared some row handlers for the most common situations, so remember to checked them before you decide to write your own.

Let's go back to our app and change two remaining methods.

- (Contact *)contactWithId:(NSUInteger)objectId {
    NSString *query = [NSString stringWithFormat:@"SELECT * FROM contacts WHERE id=%tu", objectId];
    NSArray *row = [self.db loadDataFromDB:query];
    return [self rowToContact:[row objectAtIndex:0]];
}
                
- (Contact *)contactWithId:(NSUInteger)objectId {
     return [self.dao getById:@(objectId)];
}
                

Is the last method also going to be a one-liner? What do you think?

- (NSArray *)contactsArray {
    NSString *query = @"SELECT * FROM contacts";
    NSArray *dbToArray = [self.db loadDataFromDB:query];
    NSMutableArray *resultArray = [[NSMutableArray alloc] init];
    for (NSArray *row in dbToArray) {
        [resultArray addObject:[self rowToContact:row]];
    }
    return [resultArray sortedArrayUsingSelector:@selector(compare:)];

}

- (NSArray *)contactsArray {
    return [[self.dao getContactsArray] sortedArrayUsingSelector:@selector(compare:)];
}
                

Of course it is! But keep in mind: the compare: method is not generated by Vertabelo Mobile ORM, so you have to write it on your own. Alternatively, you can use SQL for sorting.

- (NSArray *)contactsArray {

return [[self.dao getContactsArrayOrderAscByColumn:self.dao.LAST_NAME];

}

And that's all! Now our Contacts app is running on Vertabelo Mobile ORM!

Summary

Let's go back to the SQLiteManager class, just to see how complex it *was*.

@interface SQLiteManager ()

@property (nonatomic) NSString *documentsDirectory;
@property (nonatomic) NSString *dbFilename;
@property (nonatomic) NSMutableArray *results;
- (void)queryRun:(const char *)query isExecutable:(BOOL)queryExecutable;
- (void)copyDatabaseIntoAppSupportDirectory;
- (void)addQueryResult:(sqlite3_stmt *)queryResult;

@end

@implementation SQLiteManager

- (void)copyDatabaseIntoAppSupportDirectory {
    NSString *destinationPath = [self.documentsDirectory
                        stringByAppendingPathComponent:self.dbFilename];
    if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {
        NSString *sourcePath = [[[NSBundle mainBundle] resourcePath]
                        stringByAppendingPathComponent:self.dbFilename];
        NSError *error;
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath
                                        toPath:destinationPath error:&error];
        if (error != nil) {
            NSLog(@"%@", [error localizedDescription]);
        }
    }
}

- (id)initWithDatabaseFilename:(NSString *)dbFilename {
    self = [super init];
    if (self) {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSApplicationSupportDirectory, NSUserDomainMask, YES);
        self.documentsDirectory = [paths objectAtIndex:0];
        self.dbFilename = dbFilename;
        [self copyDatabaseIntoAppSupportDirectory];
    }
    return self;
}

- (void)addQueryResult:(sqlite3_stmt *)queryResult {
    while(sqlite3_step(queryResult) == SQLITE_ROW) {
        NSMutableArray *rowData = [[NSMutableArray alloc] init];
        int totalColumns = sqlite3_column_count(queryResult);

        for (int i=0; i
            char *dataInChars = (char *)sqlite3_column_text(queryResult, i);
            if (dataInChars) {
                [rowData addObject:[NSString stringWithUTF8String: dataInChars]];
            }
        }

        if (rowData.count > 0) {
            [self.results addObject:rowData];
        }
    }
}

- (void)queryRun:(const char *)query isExecutable:(BOOL)queryExecutable{
    sqlite3 *db;
    NSString *databasePath = [self.documentsDirectory
                        stringByAppendingPathComponent:self.dbFilename];
    if (self.results != nil) {
        [self.results removeAllObjects];
        self.results = nil;
    }

    self.results = [[NSMutableArray alloc] init];
    if (sqlite3_open([databasePath UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        return;
    }

    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, query, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"%s", sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if (!queryExecutable){
        [self addQueryResult:statement];
    }
    else {
        if (!sqlite3_step(statement)) {
            NSLog(@"%s", sqlite3_errmsg(db));
        }
    }
    sqlite3_finalize(statement);
    sqlite3_close(db);
}

- (NSArray *)loadDataFromDB:(NSString *)query{
    [self queryRun:[query UTF8String] isExecutable:NO];
    return (NSArray *)self.results;
}

- (void)executeQuery:(NSString *)query {
    [self queryRun:[query UTF8String] isExecutable:YES];
}

@end
                

Do you remember that we had to write this class on our own? Well, let's take a look what are the responsibilities for this class:

  • opening and closing database connection
  • finalizing statements
  • using sqlite3 functions in C to connect with database
  • converting between C types and Objective-C objects
  • looking for potential errors and showing messages about them to the user
  • checking SQLite result codes

Writing your own classes to manage data in database, you have to worry about everything from the list above, just like we did in our SQLiteManager. Using Vertabelo Mobile ORM, you don't have to worry about anything from the list above - it takes care about all of these things and even more. So, happy coding with Vertabelo Mobile ORM!

Note

What we've done in that article is only a small fraction of the things, which Vertabelo Mobile ORM can do like accessing objects by alternate or foreign keys, auto-assigning ids for the objects, aggregate functions or dealing with BLOBs in an efficient way. Besides generated DAO classes, you've got also an advanced query builder with the majority of SQL functions and operations for creating more complicated queries. You don't have also to worry about the security of the queries - Vertabelo Mobile ORM is protecting you from any injection attack. To learn more about Vertabelo Mobile ORM check iOS Tutorial, DAO Guide and Query Language Guide.