I’m trying to figure out a complete Sugar.Data Swift (Silver) working snippet.
I’m covering iOS only here:
- Compile Sugar.Data through Sugar.sln project (thanks to fix here I was able to compile on MacOS X - Compile Sugar.sln on Fire/MacOSX ) to build binaries
libSugar.Data.a (static library), ARM64.
Sugar.Data.h (public header)
This one starts with
// Header generated by RemObjects Elements for Cocoa
#include <stdint.h>
#include <stdbool.h>
#include <stddef.h>
#import <objc/NSObject.h>
#import <Foundation/Foundation.h>
@class SQLiteConnection;
@class SQLiteQueryResult;
@class SQLiteException;
@interface SQLiteConnection: NSObject
...
- In Xcode setup Obj-C Bridging-Header that references Sugar.Data public header:
#import "Sugar.Data.h"
- Code Snippet
First I need a Swift function that detects database sql, and return its path or nil (this is not pretty much straightforward so I prefer to append this func as well) Also this function will copy a .sql init file from app bundle if any:
func checkDatabaseURL() -> NSURL? {
let fileManager = NSFileManager.defaultManager()
let urls = fileManager.URLsForDirectory(.DocumentDirectory, inDomains: .UserDomainMask)
// If array of path is empty the document folder not found
guard urls.count == 0 else {
let finalDatabaseURL = urls.first!.URLByAppendingPathComponent("testdb.sql")
// Check if file reachable, and if reacheble just return path
guard finalDatabaseURL.checkResourceIsReachableAndReturnError(nil) else {
// Check if file is exists in bundle folder
if let bundleURL = NSBundle.mainBundle().URLForResource("testdb", withExtension: "sql") {
// if exist we will copy it
do {
try fileManager.copyItemAtURL(bundleURL, toURL: finalDatabaseURL)
} catch _ {
print("File copy failed!")
}
} else {
print("Our file not exist in bundle folder")
return finalDatabaseURL
}
return finalDatabaseURL
}
return finalDatabaseURL
}
return nil
}
-
At this point I can safely create or load a existing sql database:
let fname = checkDatabaseURL()?.absoluteString; print(fname); if let fpath = fname { // unwrap optional let dbConn:SQLiteConnection = SQLiteConnection.init(fpath, false, true); // name, readonly, createifneeded print(dbConn); } catch let error as SQLiteException { print("sql error"); print(error.description) } catch let error as NSError { print("undefined error"); print(error.description) } }
Now, that I have a SQLiteConnection instance, I can populate the database (if I did not before) in the unwrap block of the if...then
let dbConn:SQLiteConnection = SQLiteConnection.init(fname, false, true); // name, readonly, createifneeded
let SQL = "CREATE TABLE IF NOT EXISTS CACHE (ID INTEGER PRIMARY KEY AUTOINCREMENT, CACHE_KEY TEXT UNIQUE, CACHE_VALUE TEXT, TIMESTAMP TEXT);";
print(SQL);
//var VALUES: AutoreleasingUnsafeMutablePointer<NSObject?> = nil;
let INSERT = "INSERT OR REPLACE INTO CACHE (cache_key, cache_value, timestamp) VALUES (\"USER\",\"LORETO\",\"20160111\");";
dbConn.Execute(INSERT,nil);
Please note here, that native sqlite.a static class can crash without anyway to catch the runtime exception:
<SQLiteConnection: 0x13464a160>
2016-01-12 11:55:30.042 CrossTest[1757:591182] *** Terminating app due to uncaught exception 'SQLite', reason: 'Not in an transaction'
*** First throw call stack:
Said that (so double check your SQL),
First problem here, I was not able to pass to the statement the values in the form of a prepared statement like:
let INSERT = "INSERT OR REPLACE INTO CACHE (cache_key, cache_value, timestamp) VALUES (?,?,?);";
According to the public header this is a NSObject * *:
- (int64_t)Execute:(/* mapped */ NSString *)aSQL :(NSObject * * /* dynamicarray */)aArgValues;
-
Now I can query the ResultSet like:
do { let RES=try databaseExecuteQuery(dbConn, query: SQL); print(RES); if let result:SQLiteQueryResult = RES { // unwrap optional if ( !result.IsNull ) { // first row while ( result.MoveNext() ) { // next cursor row } } } } catch let error as SQLiteException { print("sql error"); print(error.description) } catch let error as NSError { print("undefined error"); print(error.description) }
Where I have used a throws func to handle the exceptions like:
func databaseExecuteQuery(dbConn:SQLiteConnection, query:String) throws -> SQLiteQueryResult? {
let RES:SQLiteQueryResult = dbConn.ExecuteQuery(query , nil);
return RES;
}
Here, I maybe wrong, since the NSException should be converted to a NSError (through ObjC class and bridging headers) according to minimal knowledge of Swift Error Handling (this is suggested here: http://stackoverflow.com/questions/32758811/catching-nsexception-in-swift ).
Anyways, now that I have an instance of ResultSet SQLiteQueryResult I can go through it:
if ( !result.IsNull ) {
print( result.GetString( 0 ), result.GetString( 1 ), result.GetString( 2 ) );
while ( result.MoveNext() ) {
print( result.GetString( 0 ), result.GetString( 1 ), result.GetString( 2 ) );
}
}
Second problem here I cannot see any row in the ResultSet, so the INSERT OR REPLACE failed, without errors, so this failed:
let INSERT = "INSERT OR REPLACE INTO CACHE (cache_key, cache_value, timestamp) VALUES (\"USER\",\"LORETO\",\"20160111\");";
dbConn.Execute(INSERT,nil);
So I suppose that this will work only as a prepared statement, but I was not able so far to figure out which is the AutoreleasingUnsafeMutablePointer to pass as
dbConn.Execute(INSERT,VALUES);