Swift - Pre-populated SQLite database not included on device

Question

I am trying to include a pre-populated SQLite database (with FMDB wrapper) in a build to my physical iPhone. However, the pre-populated database is not being included in the build to the physical device.

Note that it works fine in the IOS Simulator, but only for one simulator device.

I have included the database in Build Phases > Copy Bundled Resources and linked libsqlite3.dylib under Link Binary with Libraries.

What Swift code do I need to add to get the database included in the build to the physical device?

Code:

import UIKit

class ViewController: UIViewController {


    @IBOutlet weak var checkButton: UIButton!
    @IBOutlet weak var tests_scroller: UITextView!

    var databasePath = NSString()

    override func viewDidLoad() {
        super.viewDidLoad()

        checkButton.setTitle("\u{2610}", forState: .Normal)
        checkButton.setTitle("\u{2611}", forState: .Selected)


        let filemgr = NSFileManager.defaultManager()

        let dirPaths = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)

        let docsDir = dirPaths[0] as! String

        databasePath = docsDir.stringByAppendingPathComponent("vmd_db.db")

        let myDatabase = FMDatabase(path: databasePath as String)


        if myDatabase.open(){

            var arrayData:[String] = []

            let query_lab_test = "SELECT lab_test FROM lab_test ORDER BY lab_test ASC"

            let results_lab_test:FMResultSet? = myDatabase.executeQuery(query_lab_test, withArgumentsInArray: nil)

            while results_lab_test?.next() == true {

                if let resultString = results_lab_test?.stringForColumn("lab_test"){

                arrayData.append(resultString)

            }
        }
            var multiLineString = join("\n", arrayData)
            tests_scroller.text = multiLineString
            myDatabase.close()
    }
    }




    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }
}

UPDATE - Working Swift 2 code in XCode 7 - Pre-populated SQLite database using FMDB wrapper copied to the physical device OK:

import UIKit

class ViewController: UIViewController {

    @IBOutlet weak var tests_scroller: UITextView!

    var databasePath = NSString()

    override func viewDidLoad() {
        super.viewDidLoad()


        let sourcePath = NSBundle.mainBundle().pathForResource("vmd_db", ofType: "db")

        let documentDirectoryPath = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).first as String!

        let destinationPath = (documentDirectoryPath as NSString).stringByAppendingPathComponent("vmd_db.db")

        do {                                
            try NSFileManager().copyItemAtPath(sourcePath!, toPath: destinationPath)

        } catch _ {                
        }

        //read it
        let myDatabase = FMDatabase(path: destinationPath as String)

        if myDatabase.open(){

            var arrayData:[String] = []

            let query_lab_test = "SELECT lab_test FROM lab_test ORDER BY lab_test ASC"

            let results_lab_test:FMResultSet? = myDatabase.executeQuery(query_lab_test, withArgumentsInArray: nil)

            while results_lab_test?.next() == true {

                if let resultString = results_lab_test?.stringForColumn("lab_test"){

                    arrayData.append(resultString)

                }
            }

            let multiLineString = arrayData.joinWithSeparator("\n")

            tests_scroller.text = multiLineString
            myDatabase.close()
        }
    }


    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }
}

Legislatorial

Why was my question voted down???? – Legislatorial

down voter left no comment so I upvoted to counter vote. – Nev

Answer 1

from what I see it shouldn't work on any simulator / device since you access the file in the documents folder. that is not in your bundle.

what you need to do is copy the file from the bundle to the documents directory before trying to open it there:

//path in documents
let dirPaths = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)
let docsDir = dirPaths[0]
databasePath = docsDir.stringByAppendingPathComponent("vmd_db.db")

//if not there, copy from bundle
if !filemgr.fileExistsAtPath(databasePath) {
    let bundleDatabasePath = NSBundle.mainBundle().pathForResource("vm_db", ofType: "db")
    filemgr.copyItemAtPath(bundleDatabasePath, toPath: databasePath)
}

//read it
let myDatabase = FMDatabase(path: databasePath as String)

Nev

@Daij-Djan...thanks for the upvote! XCode wanted a change to the following to include the error bit: filemgr.copyItemAtPath(bundleDatabasePath, toPath: databasePath, error:nil) but now I'm getting "NSString is not implicitly convertible to 'String';did you mean 'as' to explicitly convert?" – Legislatorial

you switch between swift 1.2 and 2 -- your above code is 1.2 but the error is 2.0 -- databasePath should never be an NSString :) – Nev

Maybe XCode is the problem...I'm using v6.4. – Legislatorial

yip - don't have it anymore, xcode 7 has swift 2 so yes - you need to cast here and there – Nev

I decided to update XCode to v7 and use Swift 2. Now stringByAppendingPathComponent has been removed from Swift 2, and replaced with URLByAppendingPathComponent which is producing an error "value of type 'String' has no member 'URLByAppendingPathComponent'" Any ideas? – Legislatorial

Thanks, that fixed that issue. The other is filemgr.copyItemAtPath(bundleDatabasePath, toPath: databasePath) has an error and expects a try statement. This now produces a fatal error: do { try filemgr.copyItemAtPath(bundleDatabasePath!, toPath: databasePath) } catch _ { // } – Legislatorial

The fatal error was "fatal error: unexpectedly found nil while unwrapping an Optional value (lldb) " – Legislatorial

There is also the error on line try filemgr.copyItemAtPath(bundleDatabasePath!, toPath: databasePath) "Thread 1: EXC_BAD_INSTRUCTION (code=EXC_1386_invop,SUBCODE=0X0)" – Legislatorial

you never added it to the target or it is named differently or in a subfolder – Nev

Answer 2

I had a similar problem; specifically, after adding data to an app I'm working on in the iPhone simulator, I didn't want to have to add all of that data again to my physical iPhone device. After looking at the solutions offered by IlludimPu36 and Daij-Djan, I came up with the following method, which I call in the very first line from the App Delegate's (AppDelegate.swift) application(didFinishLaunchingWithOptions) method:

func copyBundledSQLiteDB() {
    let sourcePath = NSBundle.mainBundle().pathForResource("filename", ofType: "sqlite")

    let documentDirectoryPath = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).first as String!

    let destinationPath = (documentDirectoryPath as NSString).stringByAppendingPathComponent("filename.sqlite")

    // If file does not exist in the Documents directory already,
    // then copy it from the bundle.
    if !NSFileManager().fileExistsAtPath(destinationPath) {
        do {
            try NSFileManager().copyItemAtPath(sourcePath!, toPath: destinationPath)

        } catch _ {
        }
    }
}

It appears to work, and stepping through each line of the method in the debugger looks error-free for each case, both when the database is not already there (i.e., after a fresh install after deleting the app or resetting the simulator), and when the database does already exist. I get the same results on a physical device also.

Thanks @IlludiumPu36 and @Daij-Djan for providing tips/answers to this, and I hope this can help someone else out.

Thankyou