如何在swift sqlite3中编写具有自动递增索引键值的插入

Question

我有下面的db类，我希望列indexKey在其中自动递增。它也应该是主要的关键。但是，每次插入值时，在每次读取数据库查询时，自动递增的字段都是0。我错过什么了吗？有什么建议吗？

下面是我的DB类；

class DbClass
{
var indexKey:Int = 0
var name = ""    
var indexNo:Int = 0
init(indexKey:Int, name:String, indexNo:Int)
{
self.indexKey = indexKey
self.name = name        
self.indexNo = indexNo
}

}

下面是我创建、插入和读取的表

//创建表

func createTable(){
let createString = "CREATE TABLE IF NOT EXISTS dbclass(indexKey INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, indexNo INTEGER);"
var createTableStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, createString, -1, &createTableStatement, nil) == SQLITE_OK
{
if sqlite3_step(createTableStatement) == SQLITE_DONE
{
print("dbclass table created.")
} else {
print("dbclass table could not be created.")
}
} else {
print("CREATE TABLE statement could not be prepared.")
}
sqlite3_finalize(createTableStatement)
}

//插入表

func insert(cardNumber:String, name:String, indexNo:Int){
let dbclass = read()
for a in dbclass
{
if a.cardNumber == cardNumber
{
return
}
}
let insertStatementString = "INSERT INTO dbclass (cardNumber, name, indexNo) VALUES (?, ?, ?);"
var insertStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
sqlite3_bind_text(insertCreditTransStatement, 2, (name as NSString).utf8String, -1, nil)            
sqlite3_bind_int(insertCreditTransStatement, 3, Int32(indexNo))
if sqlite3_step(insertStatement) == SQLITE_DONE {
print("Successfully inserted row.")
} else {
print("Could not insert row.")
}
} else {
print("INSERT statement could not be prepared.")
}
sqlite3_finalize(insertStatement)
}

//读取表

func read() -> [DbClass] {
let queryStatementString = "SELECT * FROM dbclass;"
var queryStatement: OpaquePointer? = nil
var psns : [DbClass] = []
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
while sqlite3_step(queryStatement) == SQLITE_ROW {
let indexKey = sqlite3_column_int(queryStatement, 0)
let name = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
let indexNo = sqlite3_column_int(queryCreditTransStatement, 2)
psns.append(DbClass(indexKey: Int(indexKey), name: name, indexNo: Int(indexNo)))
print("Query Result:")
print("(indexKey) |(name) | (indexNo)")
}
} else {
print("SELECT statement could not be prepared")
}
sqlite3_finalize(queryStatement)
return psns
}

Inside View控制器；

var db:DBHelper = DBHelper()
var dbclass:[DbClass] = []

我在视图控制器中插入的代码如下所示；

self.db.insert(name: "abc", indexNo: 1)
self.dbclass = self.db.read()
print("DB Read")

Answer 1

创建表

if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS Heroes (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, powerrank INTEGER)", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error creating table: (errmsg)")
}

更多细节请点击链接https://www.simplifiedios.net/swift-sqlite-tutorial/