2

I recently started an iOS project and need to work with a standalone database that'll be downloaded from the internet when there are updates. This is my first iOS project, so am trying to get functionality working before designing the download mechanism.

I've selected the SQLite wrapper SWLite.swift (https://github.com/stephencelis/SQLite.swift) and have my queries and table views ready to roll. But I can't connect to the DB. I've tried just about every other question/answer on here with absolutely no success.

For example, I dropped the SQLite database into the Assets.xcassets and tried let path = Bundle.main.path(forResource: "myDb", ofType: "db")!, and the app crashes (as I got nil back -- presumably because the path couldn't be located). LikewiseI tried what others suggested, including creating a folder on my Mac, dropping the file into it, appending .bundle to the folder name, and dropping that into the Assets... again, nil.

Could anybody advise? I've looked at the Apple Docs and couldn't really find what I was looking for -- again, this is my first time, so maybe I'm doing it wrong.

Dark Innocence
  • 1,389
  • 9
  • 17
The48Percent
  • 21
  • 1
  • 3

3 Answers3

2

1.: Get a valid path to your database file

Looks like your actual main problem was getting a valid path to your database file. I couldn't find any way to make it work, if the database file is located in the Assets, either. So don't save the database file in your Assets folder, but add it to your Copy Bundle Resources list in the Build Phases setting of your current target. If your file is called myDb.db you can then get a valid path like this:

let dbUrl = Bundle.main.url(forResource: "myDb", withExtension: "db")!
let dbPath = dbUrl.path

2.: Access your database (without copying it)

It is possible to access your database now without having to (manually?) copy it. Just use the already mentioned SQLite.swift library: 

db = try! Connection(dbPath)

Notes: As the question is a little bit older aleady, maybe it wasn't possible to access the database file like this at the time of asking. However, it does work by now. The only thing I haven't checked yet explicitly is writing to the database. At least a read-only access does work like a charm though.

Johnson_145
  • 1,994
  • 1
  • 17
  • 26
1

You cannot directly read from bundle sqlite files on your phone. You have to first copy it into Documents folder and start reading from there. This only needs to be done once. To move it,

let path = Bundle.main.path(forResource: "DBName", ofType: "sqlite")
do{
    try fileManger.copyItem(atPath: path!, toPath: dbPath)
}catch let error as NSError {
    print("error occurred, here are the details:\n \(error)")
}

Then to connect, you can use

let doumentDirectoryPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0] as NSString
let dbPath = doumentDirectoryPath.appendingPathComponent("DBName.sqlite")
Fangming
  • 24,551
  • 6
  • 100
  • 90
  • @The48Percent dbPath is string format path of your SQLite file under Document folder. You can use your SQLite library to read the file from there. – Fangming Jul 02 '17 at 13:02
  • Could you give an example of what `dbPath` should be? App crashes at the try statement with `fatal error: unexpectedly found nil while unwrapping an Optional value`. Code was a mix of what you suggested: `try fileManager.copyItem(atPath: path!, toPath: "myDb.sqlite")` – The48Percent Jul 02 '17 at 13:05
  • @The48Percent Use the `dbPath` in my second example. – Fangming Jul 02 '17 at 13:20
  • The problem in your code is that you need to copy the path to document folder, so you are missing all the previous level of folders. The idea here is that you need to get the actual path of the file in main bundle, then the full path of the file in Document folder. Copy the file over to the document folder and read from there in future. – Fangming Jul 02 '17 at 13:37
  • It must be the path location in assets, as it can't locate the file. I have opened the file located in Assets using Finder, and it seems to have placed the file in `myDB.dataset` directory with the following contents: `Contents.json` and `myDB.sqlite`. Which name and extension am I using for the `forResource` param? – The48Percent Jul 02 '17 at 13:58
  • @The48Percent I mean, either way, you have to copy it to Document folder to use it, no exceptions. So you can either find the path of the file in `assets` folder, or simply put it in main bundle. Don't forget to add it in `Copy Bundle Resources` from build setting if you use main bundle. – Fangming Jul 03 '17 at 11:23
-1

First off, you never want to have your db anywhere else except in your Documents Folder.

Here's the process to do with SQLite.Swift:- 

 // this is the path where your DB should be

 let path = NSSearchPathForDirectoriesInDomains(
.documentDirectory, .userDomainMask, true
).first! 


// this is how you create a DB with a table:- 

    do {
                let db_name = "my_db"
                let db = try Connection("\(path)" + "/" + db_name! + ".sqlite3")
                try db.key("12345") // if db is encrypted with SQLCipher

                do {
                    try db.run(mastertableName.create(ifNotExists: true) { t in

                        t.column(database_name)
                        t.column(email, unique: true)
                        t.column(date_created)
                        t.column(device_info)
                    })
                }
                catch {

                    print("Creating Master Table Failed!")
                }

Just by doing a connection, it will create a DB for you, if it doesn't exists!!

Reading the DB:-

do {
        let db_name = "my_db"
        let db = try Connection("\(path)" + "/" + db_name! + ".sqlite3")
        try db.key("12345")

    // db is now open... do your stuff..
}

catch {
print("opening the db failed!")
}
Dark Innocence
  • 1,389
  • 9
  • 17