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.
