CODEWITHSUNDEEP
iosswiftsqlite
Rahul singh
Rahul singh

Reputation: 261

How to upgrade database version in sqlite and add new column in table in swift

I've already created sqlite tables for my app, but now I want to add a new column in table to the database. ALTER TABLE will help me in this problem but first i want to check the database version. i am using PRAGMA user_version to check the user version and update the user_version but it always returning user_version as 0.

var database: FMDatabase? = nil

    class func getInstance() -> ModelManager{
        if(sharedInstance.database == nil){
            sharedInstance.database = FMDatabase(path: Util.getPath("XXXX.sqlite"))
        }
        return sharedInstance
    }


    func userVersion(){
        sharedInstance.database!.open()
        var userVer = Int()
        let resultSet = sharedInstance.database?.executeQuery("pragma user_version", withArgumentsInArray: nil)
        userVer =  Int(resultSet!.intForColumn("user_version"))
        print("user version : ",userVer)
        sharedInstance.database!.close()
    }

    func updateUserVersion(){
        sharedInstance.database!.open()
        sharedInstance.database?.executeUpdate("PRAGMA user_version=1", withArgumentsInArray: nil)
        sharedInstance.database!.close()
    }

Upvotes: 3

Views: 2681

Answers (2)

vietlh
vietlh

Reputation: 39

The code below works fine with Swift 4

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")
        }
    }
}

Upvotes: 3

Gwendal Roué
Gwendal Roué

Reputation: 4044

You need to call the next() method on your resultSet so that it loads the first row, before you access intForColumn.

Besides, since you use FMDB, take a look at https://github.com/groue/GRDB.swift: it's a Swift wrapper for SQLite that will look familiar to FMDB users. But you'd simply write let userVer = Int.fetchOne(db, "pragma user_version") this time.

Upvotes: 0

Related Questions