Connecting SQLite to an iOS mobile app via FMDB on Xcode using Swift

Faced with the task of connecting SQLLite to my iOS mobile application via FMDB, I did not find a single current guide in Russian. And even more so for Swift. In this article I will try to fix it.

In this guide, objective-c files will be used, so do not wait for the FMDB port on Swift.

Download FMDB here .
')
In FMDB, there are three main classes:

FMDatabase - represents SQLite data. Used to execute SQL statements.
FMResultSet - represents the results of the query by FMDatabase.
FMDatabaseQueue - if you want to run queries and updates on multiple threads, you can use this class. Example in paragraph 8.

Before you can interact with the database, it must be open. Opening fails if there are not enough resources or permission to open and / or create a database.

if (![db open]) { [db release]; return; } 

Steps:

1) Add the 'libsqlite3' standard library in the project settings and copy the FMDB files into your project. (yes, they are on objective-c).

2) Create a new file that will be called “FMDB-Bridging-Header.h”. Inside “Bridging-Header.h” write the following: #import “FMDB.h”.

3) Go to Build Settings -> Swift Compiler - Code Generation and add to the 'Objective-C Bridging Header': FMDB-Bridging-Header.h.

If the file is in the folder of your project, then so: NAME_PAPKI / FMDB-Bridging-Header.h

4) Copy the SQLite database into your project. In this guide, I will use the name 'tempdb.sqlite' with just one table inside:

CREATE TABLE test_tb (test_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, keywordtext TEXT)

5) In your AppDelegate.swift's class AppDelegate, add the following variables: var dbFilePath: NSString = NSString ()

Example:

 import UIKit @UIApplicationMain class AppDelegate: UIResponder, UIApplicationDelegate { var window: UIWindow? var navi: UINavigationController? var dbFilePath: NSString = NSString() func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: NSDictionary?) -> Bool { .... 

6) Add this method in AppDelegate.swift's class AppDelegate:

 // MARK: - FMDB let DATABASE_RESOURCE_NAME = "tempdb" let DATABASE_RESOURCE_TYPE = "sqlite" let DATABASE_FILE_NAME = "tempdb.sqlite" func initializeDb() -> Bool { let documentFolderPath = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as String let dbfile = "/" + DATABASE_FILE_NAME; self.dbFilePath = documentFolderPath.stringByAppendingString(dbfile) let filemanager = NSFileManager.defaultManager() if (!filemanager.fileExistsAtPath(dbFilePath) ) { let backupDbPath = NSBundle.mainBundle().pathForResource(DATABASE_RESOURCE_NAME, ofType: DATABASE_RESOURCE_TYPE) if (backupDbPath == nil) { return false } else { var error: NSError? let copySuccessful = filemanager.copyItemAtPath(backupDbPath, toPath:dbFilePath, error: &error) if !copySuccessful { println("copy failed: \(error?.localizedDescription)") return false } } } return true } 

7) Call in the AppDelegate.swift's func application:

 func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: NSDictionary?) -> Bool { if self.initializeDb() { NSLog("Successful db copy") } 

8) In this example, we are working with data from UITableViewController) using FMDB:

 import UIKit class SecondViewController: UIViewController { // MARK: - .H @IBOutlet var dataTable: UITableView? var dataArray:[MultiField] = [] // MARK: - .M required init(coder: NSCoder) { fatalError("NSCoding not supported") } override init(nibName nibNameOrNil: String?, bundle nibBundleOrNil: NSBundle?) { super.init(nibName: nibNameOrNil, bundle: nibBundleOrNil) // Custom initialization } override func viewDidLoad() { super.viewDidLoad() // Do any additional setup after loading the view. self.title = "FMDB Using Swift" let mainDelegate: AppDelegate = UIApplication.sharedApplication().delegate as AppDelegate // initialize FMDB let db: FMDatabase = FMDatabase(path:mainDelegate.dbFilePath) if (db.open() == nil) { NSLog("error opening db") } //   let addQuery = "INSERT INTO test_tb (name, keywordtext) VALUES ('excalibur', 'hot')" let addSuccessful = db.executeUpdate(addQuery, withArgumentsInArray: nil) if !addSuccessful { println("insert failed: \(db.lastErrorMessage())") } //   -  // update  let updateQuery = "UPDATE test_tb SET keywordtext = 'cool' WHERE name = 'excalibur' " let updateSuccessful = db.executeUpdate(updateQuery, withArgumentsInArray: nil) if !updateSuccessful { println("update failed: \(db.lastErrorMessage())") } // update  -  //           UITableView let mainQuery = "SELECT name, keywordtext FROM test_tb" let rsMain: FMResultSet? = db.executeQuery(mainQuery, withArgumentsInArray: []) while (rsMain!.next() == true) { let productName = rsMain?.stringForColumn("name") let keywords = rsMain?.stringForColumn("keywordtext") let multiField = MultiField(aField1: productName!, aField2: keywords!) self.dataArray.append(multiField) } //   -  //   let delQuery = "DELETE FROM test_tb WHERE name = 'excalibur' " let deleteSuccessful = db.executeUpdate(delQuery, withArgumentsInArray: nil) if !deleteSuccessful { println("delete failed: \(db.lastErrorMessage())") } //   -  // :    let rsTemp: FMResultSet? = db.executeQuery("SELECT count(*) AS numrows FROM test_tb", withArgumentsInArray: []) rsTemp!.next() let numrows = rsTemp?.intForColumn("numrows") NSLog("numrows: \(numrows)") //:    -  db.close() } override func didReceiveMemoryWarning() { super.didReceiveMemoryWarning() // Dispose of any resources that can be recreated. } // MARK: - TableView DataSource func numberOfSectionsInTableView(tableView: UITableView!) -> Int { return 1 } func tableView(tableView: UITableView!, numberOfRowsInSection section: Int) -> Int { return self.dataArray.count } func tableView(tableView: UITableView!, cellForRowAtIndexPath indexPath: NSIndexPath!) -> UITableViewCell! { let cell: UITableViewCell = UITableViewCell(style: UITableViewCellStyle.Subtitle, reuseIdentifier: "FMDBTest") let multiField: MultiField = self.dataArray[indexPath.row] let num = indexPath.row + 1 cell.textLabel.text = "\(num). \(multiField.field1!)" cell.detailTextLabel.text = multiField.field2 return cell } // MARK: - UITableViewDelegate func tableView(tableView: UITableView!, didSelectRowAtIndexPath indexPath: NSIndexPath!) { tableView.deselectRowAtIndexPath(indexPath, animated: true) } } 

9) Some different chips, use FMDB multi-stream through FMDatabaseQueue.

 var queue: FMDatabaseQueue? func testDatabaseQueue() { let documentsFolder = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0] as String let databasePath = documentsFolder.stringByAppendingPathComponent("test.sqlite") queue = FMDatabaseQueue(path: databasePath) //   <source lang="objectivec"> queue?.inDatabase() { db in var success = db.executeUpdate("create table test (id integer primary key autoincrement, a text)", withArgumentsInArray:nil) if !success { println("table create failure: \(db.lastErrorMessage())") return } } 

// insert five lines

  queue?.inTransaction() { db, rollback in for i in 0 ..< 5 { if !db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"]) { println("insert \(i) failure: \(db.lastErrorMessage())") rollback.initialize(true) return } } } 

// let's try inserting lines, but deliberately mistaken and see what it rolls back correctly

  queue?.inTransaction() { db, rollback in for i in 5 ..< 10 { let success = db.executeUpdate("insert into test (a) values (?)", withArgumentsInArray: ["Row \(i)"]) if !success { println("insert \(i) failure: \(db.lastErrorMessage())") rollback.initialize(true) return } if (i == 7) { rollback.initialize(true) } } } 

// check that only the first five lines are there

  queue?.inDatabase() { db in if let rs = db.executeQuery("select * from test", withArgumentsInArray:nil) { while rs.next() { println(rs.resultDictionary()) } } else { println("select failure: \(db.lastErrorMessage())") } } 

// delete the table

  queue?.inDatabase() { db in let success = db.executeUpdate("drop table test", withArgumentsInArray:nil) if !success { println("table drop failure: \(db.lastErrorMessage())") return } } } 

10) Standard for a snack. Using the executeUpdate (values ​​:) class in Swift2:

 do { let identifier = 42 let name = "Liam O'Flaherty (\"the famous Irish author\")" let date = NSDate() let comment: String? = nil try db.executeUpdate("INSERT INTO authors (identifier, name, date, comment) VALUES (?, ?, ?, ?)", values: [identifier, name, date, comment ?? NSNull()]) } catch { print("error = \(error)") } 

Use queue:

 queue.inTransaction { db, rollback in do { try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [1]) try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [2]) try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [3]) if whoopsSomethingWrongHappened { rollback.memory = true return } try db.executeUpdate("INSERT INTO myTable VALUES (?)", values: [4]) } catch { rollback.memory = true print(error) } } 

An example from the standard description:

 let documents = try! NSFileManager.defaultManager().URLForDirectory(.DocumentDirectory, inDomain: .UserDomainMask, appropriateForURL: nil, create: false) let fileURL = documents.URLByAppendingPathComponent("test.sqlite") let database = FMDatabase(path: fileURL.path) if !database.open() { print("Unable to open database") return } do { try database.executeUpdate("create table test(x text, y text, z text)", values: nil) try database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", values: ["a", "b", "c"]) try database.executeUpdate("insert into test (x, y, z) values (?, ?, ?)", values: ["e", "f", "g"]) let rs = try database.executeQuery("select x, y, z from test", values: nil) while rs.next() { let x = rs.stringForColumn("x") let y = rs.stringForColumn("y") let z = rs.stringForColumn("z") print("x = \(x); y = \(y); z = \(z)") } } catch let error as NSError { print("failed: \(error.localizedDescription)") } database.close() 

If something does not work, write, I will try to help.