I am currently working on an iOS-App using SQLite via import sqlite3. I followed the SQLite With Swift Tutorial: Getting Started from Kodeco - but I have some issues because they're using playgrounds.

Where do I create the database file and the tables?

public class SQLiteDatabase {

    public static var currentAccount = Account(id: -1, email: "", name: "", lastname: "", gender: "", birthday: Date(), creationDate: Date())
    public static let pathExtension = "xPose.sqlite"
    public static var path: String!
    
    private let dbPointer: OpaquePointer?
    private init(dbPointer: OpaquePointer?) {
        self.dbPointer = dbPointer
    }
    
    deinit {
        sqlite3_close(dbPointer)
    }
    
    fileprivate var errorMessage: String {
        if let errorPointer = sqlite3_errmsg(dbPointer) {
            let errorMessage = String(cString: errorPointer)
            return errorMessage
        } else {
            return "No error message provided from sqlite."
        }
    }
    
    func enableForeignKeys() {
        if sqlite3_exec(dbPointer, "PRAGMA foreign_keys = ON", nil, nil, nil) != SQLITE_OK {
            let err = String(cString: sqlite3_errmsg(dbPointer))
                print("error attempting to enable foreign keys: \(err)")
            }
    }
    
    func createFile() {
        do {
            let url = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent(SQLiteDatabase.pathExtension)
            SQLiteDatabase.path = url.absoluteString
            print("File created")
        }
        catch {
            print("Error creating file")
        }
    }
    
    func createTables() {
        do {
            try createTable(table: Account.self)
            try createTable(table: Measurement.self)
            try createTable(table: Posture.self)
            try createTable(table: Vital.self)
            try createTable(table: Mood.self)
        } catch {
            print("Failed to create Tables")
        }
    }
    
    // Open the databse
    static func open(path: String) throws -> SQLiteDatabase {
        var db: OpaquePointer?
        
        if sqlite3_open(path, &db) == SQLITE_OK {
            return SQLiteDatabase(dbPointer: db)
        } else {
            defer {
                if db != nil {
                    sqlite3_close(db)
                }
            }
            if let errorPointer = sqlite3_errmsg(db) {
                let message = String(cString: errorPointer)
                throw SQLiteError.OpenDatabase(message: message)
            } else {
                throw SQLiteError.OpenDatabase(message: "No error message provided from sqlite.")
            }
        }
    }
    
    func prepareStatement(sql: String) throws -> OpaquePointer? {
        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(dbPointer, sql, -1, &statement, nil) == SQLITE_OK else {
            throw SQLiteError.Prepare(message: errorMessage)
        }
        return statement
    }
    
    func createTable(table: SQLTable.Type) throws {
        let createTableStatement = try prepareStatement(sql: table.createStatement)
        defer {
            sqlite3_finalize(createTableStatement)
        }
        
        guard sqlite3_step(createTableStatement) == SQLITE_DONE else {
            throw SQLiteError.Step(message: errorMessage)
        }
        print ("\(table) table created.")
    }

I tried putting it into the private init Method but it would try to create new files and tables every time I tried to open the database and there was no database-file created in the directory.

CodePudding user response：

You need to see if the database file at path exists or not before you call sqlite3_open. If it doesn't exist then you want to call createTables after opening the database file.

By doing this everything is setup the first time you try to open the database.

Something like this:

// Open the databse
static func open(path: String) throws -> SQLiteDatabase {
    var db: OpaquePointer?
    
    let exists = FileManager.default.fileExists(atPath: path)
    if sqlite3_open(path, &db) == SQLITE_OK {
        if !exists {
            createTables()
        }
        return SQLiteDatabase(dbPointer: db)
    } else {
        defer {
            if db != nil {
                sqlite3_close(db)
            }
        }
        if let errorPointer = sqlite3_errmsg(db) {
            let message = String(cString: errorPointer)
            throw SQLiteError.OpenDatabase(message: message)
        } else {
            throw SQLiteError.OpenDatabase(message: "No error message provided from sqlite.")
        }
    }
}

You only need to call open once each time your app starts up.