Database Manager Guide Document

Overview

The purpose of the document is to guide you on how to use Database layer and its classes to help in dealing with CRUD operations. This layer is built using iOS sqlite3 library. No CoreData framework methods or properties are used. SQLite isn’t the only way to persist data on iOS. Besides CoreData, there are many other alternatives for data persistence including Realm. Each of these has its own pros and cons, including SQLite itself. The base class for this layer is ORADBManager. All the database operations can be handled with the help of this class.

Creating the database

Creating the database is quite simple using ORADBManager class. For that, you need to initialize the ORADBManager and have its instance for further use of your DB operations. Below is the sample code:

ORADBManager *db = [[ORADBManager alloc] initWithDatabase:@"your_db_name.sqlite"];
let db = ORADBManager(database: "your_db_name.sqlite")

After successful creation of the DB, console will show the appropriate log with the database path. The same is true in case of failure. The failure will clearly detail the exact reason for database creation failure.

Below is the complete sample code to create a specific table:

ORADBManager *dbObject = [[ORADBManager alloc]initWithDatabase:@"EventManager.sqlite"];
NSString *sqlCreateTableQuery = @"CREATE TABLE IF NOT EXISTS 'events' (eventID TEXT PRIMARY KEY, eventName TEXT, extra BLOB, sessionID TEXT, timestamp INTEGER DEFAULT CURRENT_TIMESTAMP)";
BOOL create = [dbObject createTable:sqlCreateTableQuery];
if (create) {
    NSLog(@"Table created");
} else {
    NSLog(@"Table NOT created");
}
let dbObject = ORADBManager(database: "EventManager.sqlite")
let sqlCreateTableQuery = "CREATE TABLE IF NOT EXISTS 'events' (eventID TEXT PRIMARY KEY, eventName TEXT, extra BLOB, sessionID TEXT, timestamp INTEGER DEFAULT CURRENT_TIMESTAMP)"
let create = dbObject.createTable(sqlCreateTableQuery)
if create {
    print("Table created")
} else {
    print("Table NOT created")
}

Access the database / tables

In this section we will cover the read and write operations on the table.

Insert data

There are different types of methods available to deal with the read operation. However, to write into a table we only have the insertOrReplaceDataInto:contentValues: method. Below is the implementation using the above table:

NSString *blobString = @"This is the test data to store as BLOB";
NSData *data = [blobString dataUsingEncoding:NSUTF8StringEncoding];
NSDictionary *contentDict = @{@"eventID" : @"101",
                            @"eventName" : @"background",
                                @"extra" : data,
                            @"sessionID" : @"12345"};
[dbObject insertOrReplaceDataInto:@"events" withContentValues:contentDict];
let blobString = "This is the test data to store as BLOB"
let data = blobString.data(using: .utf8)
let contentDict: [String: Any] = ["eventID" : "101",
                                "eventName" : "background",
                                    "extra" : data!,
                                "sessionID" : "12345"]
dbObject.insertOrReplaceData(into: "events", withContentValues: contentDict)

Retrieve data

There are a wide variety of methods available to fetch the table data based on your preference. Below is the implementation of basic getData: with the data it is returning to the above inserted values:

NSArray *rows = [dbObject getData:@"events"];
NSLog(@"rows = %@",rows);

//Below is printed under console 
rows = (
        {
        eventID = 101;
        eventName = background;
        extra = {length = 26, bytes = 0x54686973 20697320 6d792074 65737420 ... 656e7420 64617461 };
        sessionID = 12345;
        timestamp = "2020-04-13 10:37:12";
    }
)

let data = dbObject.getData("events")
print("table data is \(data)")

//Below is printed under console 
data is [{
    eventID = 101;
    eventName = background;
    extra = {length = 38, bytes = 0x54686973 20697320 74686520 74657374 ... 20617320 424c4f42 };
    sessionID = 12345;
    timestamp = "2020-04-13 09:58:40";
}]

Below are the other getData: variants with their use and parameter reference:

- (NSArray *)getData:(NSString *)tableName count:(int)count;
//Used to get the rows for the given count

- (NSArray *)getData:(NSString *)tableName whereColumn:(NSString *)columnName columnValue:(NSString *)columnValue;
//Used to get the rows which satisfy the given column name and the column value

- (NSArray *)getData:(NSString *)tableName whereColumn:(NSString *)columnName valueRange:(NSArray *)range;
//Used to get the rows which satisfy the given column name and the column value range

Update data

To update the record, below is the method which accepts string query as a parameter and returns true if record updated successfully, else false:

- (BOOL)updateDataWithQuery:(NSString *)query

Delete data

Similar to the retrieve data above, there are different variants available to delete table data. Below is the detail:

- (BOOL)deleteData:(NSString *)tableName;
//Used to delete all the rows of the table. 

- (BOOL)deleteData:(NSString *)tableName columnName:(NSString *)columnName columnValue:(NSString *)columnValue;
//Used to delete the rows which satisfy the given column name and the column value

- (BOOL)deleteData:(NSString *)tableName columnName:(NSString *)columnName valueRange:(NSArray *)range;
- //Used to delete the rows which satisfy the given column name and the column value range

Drop table

To remove a table structure and its data for a given table name, dropTable: method will be used:

if ([dbObject dropTable:@"events"]) {
    NSLog(@"Table removed");
} else {
    //Handle failure
}
if dbObject.dropTable("events") {
    print("Table removed")
} else {
    //handle failure
}