Using FMDB to communicate with SQLite in Objective C

About an year ago, I posted a article titled “Introduction of SQLlite a modern local database engine for windows Store app & Windows Phone App” on my blog and recently, I moved it to  a new blog I just created for Windows Phone and Windows app.

In That article, I mentioned SQLite support all major mobile app platforms, like windows 8 and windows Phone 8, from Microsoft, Android from Google and iOS from apple.  But in that article, I focused on Microsoft platform. In this article I am going to focus on how to work with SQLite in iOS application development.

If you know a bit about iOS app development, you will know that in iOS, there is only one IDE, Xocde; there is only one language Objective C. so I titled this article as working with SQLite in Objective C.

First of all, before you start working on SQLite in Objective C, you need to know the fact that SQLite is written in C language, not C++ , not objective C, so it does not provide object oriented API,  but you are working with Objective C, which is object oriented language built on top the BSD C in  Unix, and most than likely you want to write your code in the object oriented way. 

Similar to the case when you with SQLite in Microsoft platform, you go and get a client library for SQLite, which provides OOP support, in iOS, you also can find such client libraries so that you deal with SQLite in the object oriented style. 

One of highly regarded such libraries is FM framework.  This link is a good resource to get it started.

The good news is 1) it is free; 2) it is open source. In this article I am going to show you how simple CRUD operation can be implemented.

In this library, there are 2 classes you will interacted with often, they are FMDatabase and FMResultSet

Before doing anything with the database , you need to open the database, and when you first open a new database,  SQLite creates the database for you.. you can use the command line utility, sqlite3, to interact with the database as you like,

In FM framework you send  databaseWithPath  message to  FMDatabase class.  The following is an example showing you how to open / create SQLite database with Objective C:

+ (FMDatabase *)Open:(NSString *)databaseName

{

    FMDatabase * database;

    NSArray * docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString * documentsDir = [ docPaths objectAtIndex:0];

    NSString * dbPath = [documentsDir stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.sqlite", databaseName]];

    NSLog(@"the DB path is %@", dbPath);

    database = [FMDatabase databaseWithPath:dbPath];

   

  if ( database.open)

      return database;

      else

          return  nil;

}

There is an important point to be noted here is in  iOS, if you  want your app able to write back to SQLite DB,  you have you store your SQLite database  in User Document Directory, if you include your DB file in you app bundle ,  the database will be read only for you. In the code above,  it shows you how to create  SQLite database in user document directory.

After you are done with the database, you need to properly close the database.  To close the database, you send close message to FMDatabase object. In my case, I put the code opening the database in  the default constrictor and put the code closing database in the dealloc method of the class, so in the rest of the class, I do not need to worry how to open and when to close the database.  This is how the code looks like:

- (id)init

{

    self = [super init];

    if (self) {

//     self.database =   [PLsqliteService Open:@"SpellingChampion"];

       

        [self Open:@"SpellingChampion"];

    }

    return self;

}

-(void)dealloc

{

    if (self.database)

    {

        [self.database close];

    }

   // [super dealloc]; ARC forbits explicit calling of dealloc

  }

just a small point to take note here, if your app does not enable ARC, you need to uncomment the commented line. Otherwise, you re good to go as ARC taking care of calling dealloc of the super class.

With opening, closing and creating database are out of way, let’s  look at how database operations are implemented:

1.     Create  / Drop tables

Let’s say you have  NSString with the follow SQL statements:

NSString * createModule = [NSString stringWithFormat:@"%@ %@ %@ %@ %@ %@ %@",

                         @"CREATE TABLE IF NOT EXISTS Module (\r",

                         @"Id INTEGER PRIMARY KEY AUTOINCREMENT,\r",

                         @"Title TEXT NOT NULL,\r",

                         @"Description TEXT NOT NULL,\r",

                         @"IsFavorite INTEGER NOT NULL CHECK (IsFavorite = 0 or IsFavorite = 1),\r",

                         @"ProductId  TEXT NULL,\r",

                         @"OfferId  INTEGER NULL );\r" ];

NSString * dropModule = @"DROP TABLE IF EXISTS Module;";

All it take is alling exectueStatments method of FMDatabase class

[self.database executeStatements:createModule];

[self.database executeStatements:dropModule];

There is a useful query for you to check if the database is empty of table or not.

SELECT COUNT(*) FROM sqlite_master where type = 'table' and name <> 'sqlite_sequence'

If it return zero, then the database does not have any tables.  Here 'sqlite_sequence' is a system table that take note of  AUTOINCREMENT columns.

2.     CRUD operation

[self.database executeUpdate: InsertModuleStatmentTemplate,

[NSString stringWithFormat:@"%@",entity.title],

[NSString stringWithFormat:@"%@",entity.elaboration],

[NSNumber numberWithBool:entity.IsFavorite],

[NSString stringWithFormat:@"%@",entity.ProductId],

[NSNumber numberWithInt:entity.OffierId]];

where

NSString * const InsertModuleStatmentTemplate = @"INSERT INTO Module (Title, Description,  IsFavorite, ProductId, OfferId )  VALUES ( ?, ?, ?, ?, ? )";

this line will get a row inserted to the table. However, an important point to take note there is the usage of NSString functions are necessary. If you omitted the function and simplify the code to something similar to the below, you will get runtime exception:

[self.database executeUpdate: InsertModuleStatmentTemplate,

entity.title,

entity.elaboration,

entity.IsFavorite,

entity.ProductId,

OffierId];

Similarly, the following code shows  you how update is done:

     [self.database executeUpdate:UpsateModuleStatmentTemplete,

     [NSString stringWithFormat:@"%@",entity.title],

     [NSString stringWithFormat:@"%@",entity.elaboration],

     [NSNumber numberWithBool:entity.IsFavorite],

     [NSString stringWithFormat:@"%@",entity.ProductId],

     [NSNumber numberWithInt:entity.OffierId],

     [NSNumber numberWithInt:entity.uniqueId]

     ];

Where the SQL statement template is defined as following:

NSString * const UpsateModuleStatmentTemplete = @"UPDATE Module SET Title = ?, Description = ?,  IsFavorite=?, ProductId = ?, OfferId = ? WHERE Id = ?;";

[self.database executeUpdate:DeleteModuleStatmentTemplete,

     [NSNumber numberWithInt:entityId]];

where  the sql statement template is defined as following:

NSString * const DeleteModuleStatmentTemplete= @"DELETE FROM Module WHERE Id = ?;";

For select operation, you  use executeQuery instesd of executeUpdate o or executeStatments , if you need to pass in parameters,  you do it in the same way you do in the case of executeUpdate ot executeStatment message.

FMResultSet *resultSet =  [self.database executeQuery: @"SELECT * FROM Lesson WHERE ParentId = ?;,

                              [NSNumber numberWithInt:moduleId]];

will get you all rows under specified module.

After you get the data in FMResultSet object, how do you navigate the result set to get the data out? The following is an example:

-(NSMutableArray *)BuildLessonList:(FMResultSet *)resultSet

{

    NSMutableArray * result = [[NSMutableArray alloc] init];

    SCLesson * entity;

   

    while ([resultSet next])

    {

        entity = [[SCLesson alloc] init];

        entity.uniqueId = [resultSet intForColumn:@"id"];

        entity.parentId = [resultSet intForColumn:@"ParentId"];

        entity.title= [resultSet stringForColumn:@"Title"];

        entity.elaboration = [resultSet stringForColumn:@"Description"];

        entity.IsFavorite = [resultSet boolForColumn:@"IsFavorite"];

        [result addObject:entity];

    }

    return result;

}

Last but not least, you might want to know how to handle transaction. Well that is not very difficult either.

-(void)DeleteLesson:(int)lessonId

{

    [self.database beginTransaction];

   

    @try

    {

        [self.database executeUpdate: DeleteLessonWordStatmentTemplate, [NSNumber numberWithInt:lessonId]];

        [self.database executeUpdate: DeleteLessonStatmentTemplate, [NSNumber numberWithInt:lessonId]];

        [self.database commit];

    }

    @catch (NSException *exception)

    {

        [self.database rollback];

        NSLog(@"an exception occured, %@|%@", exception.description, exception.debugDescription);

    }

    @finally

    {

        //  clean up action goes here

    }

}

Yu might wandering why I do 2 delete statements here instead of letting the database engine do the cascading deleting with foreign key.  Well, there is a performance reason behind it. Based on performance test with about handful of database engines including SQLite, on cascading deleting, SQLite is the only exception that it perform much slower than if you do it in your code, all other database engines include SQL/Server, Oracle, all perform better with cascading deletion than if you do it in the code like the one I shown here.

Please take note that all I am showing here is just a tip of the iceberg. SQLite is a very powerful local single file database engine. the officially description read as "SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine." 

And FM Framework is a open source library advisable GitHub.  You can fine the completed documentation of the library  and other information on FM framework at https://github.com/ccgus/fmdb .

Enjoy.