Database Manager - iOS

The Core module provides a set of capabilities to handle the CRUD operations required by the mobile apps. These capabilities can be leveraged by using the Database manager in the Core module. The database layer is the wrapper created around the iOS SQLite3 database. All the database operations can be handled with the class ORADBManager.

Note: CoreData methods or properties have not been used in the database manager

Creating a database

You can create a database using the ORADBManager class. You need to initialize the ORADBManager and create an instance of the class for your database operations.

Swift 5.1

let db = ORADBManager(database: "your_db_name.sqlite")

Objective C

ORADBManager *db = [[ORADBManager alloc] initWithDatabase:@"your_db_name.sqlite"];

After the creation of the database, the console will display the appropriate log along with the database path. If the database creation fails, the console displays the log message with the failure reason.

Swift 5.1

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")
				}

Objective C

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");
				}

Accessing database / tables

We support the following methods to carry out read and write operations on the tables you have created.

Insert Records

We support the following methods to handle read and write operations to a table.

Swift 5.1

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)

Objective C

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];

Retrieve records

getData() method helps to fetch the records from a table.

Swift 5.1

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";
				}]

Objective C

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";
				}
				)

Retrieve the number of records from the table

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

Retrieve records with matching criteria

We need to pass array contains two values in the arguments like, n1 and n2. The query returns all the records where the column values lie between n1 and n2.

- (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

Retrieve records with multiple matching criteria

We need to pass based on clause (GROUP BY, ORDER BY, HAVING)

- (NSArray *)getData:(NSString *)tableName whereColumn:(NSArray<NSString *> * _Nullable)columns whereClause:(NSString * _Nullable)whereClause andWhereArgs:(NSArray<NSString*> * _Nullable)whereArgs groupBy:(NSString *_Nullable)groupBy having:(NSString *_Nullable)having andOrderBy:(NSString *_Nullable)orderBy;
				//Used to get the rows which satisfy the given column name and the column value

Swift 5.1

et whereClause = "sessionID>?"
				let args = ["7"]
				let results = dbObject.getData("events", whereColumn: ["eventName", "COUNT(*) TOTAL"], whereClause: whereClause, andWhereArgs: args, groupBy: "eventID", having: "COUNT(eventID) > 3", andOrderBy: "COUNT(eventID) DESC")

Objective C

NSString *whereClause = @"sessionID>?";
				NSArray *args = [[NSArray alloc]initWithObjects:@"7", nil];
				NSArray *results = [dbObject getData:@"events" whereColumn:@[@"eventName",@"COUNT(*) TOTAL"] whereClause:whereClause andWhereArgs:args groupBy:@"eventID" having:@"COUNT(eventID) > 3" andOrderBy:@"COUNT(eventID) DESC"];

tableName: The table name as string.
columns (optional): That is array of specific column name, if pass nil we will consider as all column(*).
whereClause (optional): The optional WHERE clause to apply when updating. Passing nil will update all rows.
whereArgs (optional): You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
groupBy (optional): You may GROUP BY clause string value without prefix GROUP BY.
having (optional): You may HAVING clause string value without prefix HAVING.
orderBy (optional): You may ORDER BY clause string value without prefix ORDER BY.

Retrieve records for a range of matching criteria

- (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

Retrieve records by SQLite Query

- (NSArray *)getDataByQuery:(NSString *)sqlQuery;

Update records

Update records matching a criteria

The following method updates all the records matching with where clause. To update all the records you need to pass nil for whereClause.

- (BOOL)update:(NSString *)tableName contentValues:(NSDictionary *)contentValues whereClause:(NSString *)whereClause andWhereArgs:(NSArray<NSString *> *)whereArgs

Swift 5.1

let contentValues = ["eventName":"Event"]
				let whereClause = "sessionID>?"
				let args = ["7"]
				let isTableUpdated = dbObject.update("events", contentValues:contentValues , whereClause: whereClause, andWhereArgs: args)

Objective C

NSDictionary *contentValues = [[NSDictionary alloc]initWithObjectsAndKeys:@"Event",@"eventName", nil];
				NSString *whereClause = @"sessionID>?";
				NSArray *args = [[NSArray alloc]initWithObjects:@"7", nil];
				BOOL isTableUpdated = [dbObject update:@"events" contentValues:contentValues whereClause:whereClause andWhereArgs:args];

whereClause (optional): The optional WHERE clause to apply when updating. Passing nil will update all rows.
whereArgs (optional): You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Update records by SQLite Query

The following method accepts a query to update the table. The method returns true if the record(s) is updated, and returns false otherwise.

- (BOOL)updateDataWithQuery:(NSString *)query

Delete records

Delete all records from a table

This method helps to delete all the records in the table.

- (BOOL)deleteData:(NSString *)tableName;

Delete records matching a criteria

- (BOOL)deleteData:(NSString *)tableName columnName:(NSString *)columnName columnValue:(NSString *)columnValue;

Delete records from matching multiple criteria

This method helps to delete records matching multiple values in a column.

- (BOOL)deleteData:(NSString *)tableName columnName:(NSString *)columnName valueRange:(NSArray *)range;

Delete records by query

This method helps delete records by providing an SQLite query

- (BOOL)deleteDataByQuery:(NSString *)sqlDeleteQuery;

Drop table

This method helps to drop a table from the database.

Swift 5.1

if dbObject.dropTable("events") {
				print("Table removed")
				} else {
				//handle failure
				}

Objective C

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