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

Question

How to upgrade database version in SQLite.swift and add new column in table in swift am using https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md

//migration of db version


    extension Connection {
        public var userVersion: Int32 {
            get { return Int32(try! scalar("PRAGMA user_version") as! Int64)}
            set { try! run("PRAGMA user_version = \(newValue)") }
        }
    }
    //in viewdidLoad of viewcontroller
    if db.userVersion == 0 {
        // handle first migration
        db.userVersion = 1
    }
    if db.userVersion == 1 {
        // handle second migration
        db.userVersion = 2
    }
    //my table and want to upgrade with some new columns 
    do 
    {
    let offlineLocationTable = sqliteOfflineLocationTable.offlineLocationTable.create(ifNotExists: true) { (table) in
    table.column(sqliteOfflineLocationTable.id, primaryKey: true)
    table.column(sqliteOfflineLocationTable.status)
    table.column(sqliteOfflineLocationTable.loadid)
    table.column(sqliteOfflineLocationTable.jobid)
    table.column(sqliteOfflineLocationTable.lat)
    table.column(sqliteOfflineLocationTable.lng)
    // this is new columns which i want to add in new version of db
    table.column(sqliteOfflineLocationTable.t)
     print("offline location table created")
     }
      try self.db.run(offlineLocationTable)    
     } catch {
    print(error)
    }

 // this code run successfully but when i try to get the column value it carsh due to No such column "t" in columns [........]

Answer 1

I have not tried, but you may try this. In Swift you can create a new extension file for connection class.

extension Connection {
    public var userVersion: Int32 {
        get { return Int32(try! scalar("PRAGMA user_version") as! Int64)}
        set { try! run("PRAGMA user_version = \(newValue)") }
    }
}

caution: please check the syntax of the below code, as I can not judge your variables

Your table is created as per your code, if table does not exist.

//in viewdidLoad of viewcontroller
if db.userVersion == 0 {
    // handle first migration
    do 
    {
        let offlineLocationTable = sqliteOfflineLocationTable.offlineLocationTable.create(ifNotExists: true) { (table) in
            table.column(sqliteOfflineLocationTable.id, primaryKey: true)
            table.column(sqliteOfflineLocationTable.status)
            table.column(sqliteOfflineLocationTable.loadid)
            table.column(sqliteOfflineLocationTable.jobid)
            table.column(sqliteOfflineLocationTable.lat)
            table.column(sqliteOfflineLocationTable.lng)
            // any other table entries
        }
        try self.db.run(offlineLocationTable)    
        // on complete of first table creation change the version
        db.userVersion = 1
    } catch {
        print(error)
    }
}
if db.userVersion == 1 {
    // handle second migration
    // Here we add New Column in table 
    do 
    {
        try self.db.run(offlineLocationTable.addColumn(t, defaultValue: ""))
        // any other modifications
        db.userVersion = 2
    } catch {
        print(error)
    }
}

Now write your any other code.