iOS SQLite.swift, regarding upgrade of app?

Question

Regarding the magnificent and amazing SQLite.swift, I'm wondering

You have an app in the app store, v7. There's an upgrade to v8. User X does upgrade v7 to v8 using the app store.

Say in v8, we have slightly changed one of the sql tables, perhaps add a column or rename a column.

Should anything or must anything special be done in SQLite.swift in this case?

What's the SQLite.swift way to handle that?

(In for example, Android there's a handy onUpgrade concept in their helper class ... which comes with it's own set of complex issues.)

Answer 1

The implementation of SQLiteOpenHelper is quite simple:

        db = SQLiteDatabase.openDatabase(...);

        onConfigure(db);

        int version = db.getVersion();
        if (version != mNewVersion) {
            db.beginTransaction();
            try {
                if (version == 0) {
                    onCreate(db);
                } else {
                    if (version > mNewVersion) {
                        onDowngrade(db, version, mNewVersion);
                    } else {
                        onUpgrade(db, version, mNewVersion);
                    }
                }
                db.setVersion(mNewVersion);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }

Just do the same in Swift.

For how to implement onUpgrade(), see SQLiteOpenHelper onUpgrade() Confusion Android.

Answer 2

There are 2 cases where you want to upgrade database:

• There are no user data stored: database contains only predefined data and you want to alter tables' structure, add new rows, add new tables....

• There are user data stored: you want to alter data as well as keep user data persisted.

The example below explains how you can do to solve the first case. The second case would require adding some more logic based on the example.

import UIKit
import FMDB

class DataConnection: NSObject {
    static let databaseVersion = 2
    static var isDatabaseUpdated = false
    static var database: FMDatabase? = nil
    class func databaseSetup() {
        if database == nil {
            let docsDir = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0]
            let dpPath = docsDir.appendingPathComponent("database.sqlite")
            let file = FileManager.default
            if(!file.fileExists(atPath: dpPath.path)) {
                copyDatabase(file: file, dpPath: dpPath)
                database = FMDatabase(path: dpPath.path)
                do {
                    database!.open()
                    try database!.executeUpdate("PRAGMA user_version = \(databaseVersion)", values: nil)
                    database!.close()
                    isDatabaseUpdated = true
                }catch {
                    print("Error on updating user_version")
                }
            }else {
                database = FMDatabase(path: dpPath.path)
                if !isDatabaseUpdated {
                    var currentVersion = 0
                    do {
                        database!.open()
                        let resultSet: FMResultSet! = try database!.executeQuery("pragma user_version", values: nil)
                        while resultSet.next() {
                            currentVersion = Int(resultSet.int(forColumn: "user_version"))
                        }
                        database!.close()
                    }catch {
                        print("Error on getting user_version")
                    }

                    if databaseVersion > currentVersion {
                        do {
                            try file.removeItem(at: dpPath)
                        }catch {
                            print("Error on getting user_version")
                        }
                        copyDatabase(file: file, dpPath: dpPath)
                        database = FMDatabase(path: dpPath.path)
                        do {
                            database!.open()
                            try database!.executeUpdate("PRAGMA user_version = \(databaseVersion)", values: nil)
                            database!.close()
                            isDatabaseUpdated = true
                        }catch {
                            print("Error on updating user_version")
                        }
                    }else {
                        isDatabaseUpdated = true
                    }
                }
            }
        }
    }

    private class func copyDatabase(file: FileManager, dpPath: URL){
        let dpPathApp = Bundle.main.path(forResource: "database", ofType: "sqlite")
        print("resPath: "+String(describing: dpPathApp))
        do {
            try file.copyItem(atPath: dpPathApp!, toPath: dpPath.path)
            print("copyItemAtPath success")
        } catch {
            print("copyItemAtPath fail")
        }
    }
}

In this example, once you release a new app version and would like to upgrade database, you just need to increase the variable databaseVersion.