linux嵌入式swift,swift简易操作sqlite3

sqlite轻便小巧，底层采用C操作，跨平台在Linux和Android以及iOS甚至是嵌入式都通用，今天我们简易进行一些swift操作sqlite……

开始之前我们先简易的看看一些sqlite操作

接下来我们参看怎么操作sqlite3

1 创建和打开db

open class SKSQLiteDatabase { public var handle : OpaquePointer? var dbFilePath: String var vFs: Int8 = 0 var flags:Int32 init(_ dbName: String, flags:Int32 = SQLITE_CREATE_TABLE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, extension ext: String = ".sqlite", vFs: Int8 = 0) { self.flags = flags self.dbFilePath = dbHome + "/" + dbName + ext self.vFs = vFs } open func openConnection()->Bool{ let ret = sqlite3_open_v2(self.dbFilePath, &handle, flags, &vFs) return ret == SQLITE_OK }}复制代码

2 查询column和对应的数据类型

open func dbName()-> String{ lock.lock() let db = sqlite3_column_database_name(stmt, 0) lock.unlock() return String(cString: db!) } open func columnCount()->Int32{ return sqlite3_column_count(stmt) } open func tableName()->String{ lock.lock() let table = sqlite3_column_table_name(stmt, 0) lock.unlock() return String(cString: table!) } open func sqliteDataType( `for` index:Int32)->Int32{ return sqlite3_column_type(stmt, index) }复制代码

3 进行查询(多列多行查询)

func execSql(_ query: String){ var tail:UnsafePointer? let ret = sqlite3_prepare_v2(handle, query, -1, &stmt, &tail) var next:Bool = true var rowSet = [SKSQLiteRow]() while next { let row = SKSQLiteRow() for index in 0 ..< columnCount() { let table = sqlite3_column_table_name(stmt, index) let column = sqlite3_column_name(stmt, index) let dataType = sqliteDataType(for: index) var value: Optional = NSNull() switch dataType { case SQLITE_INTEGER: value = sqlite3_column_int(stmt, index) case SQLITE_TEXT: let tValue : UnsafePointer = sqlite3_column_text(stmt, index) value = String(cString: tValue) case SQLITE_BLOB: let tValue = sqlite3_column_blob(stmt, index) let pointer = tValue?.assumingMemoryBound(to: Int8.self) value = String(cString: pointer!) default: "" } let data = SKSQLiteColumnData.init(table: toStr(p: table!) , columnName: toStr(p: column!), dataType: dataType, value: value) row.add(data) } rowSet.append(row) next = sqlite3_step(stmt) == SQLITE_OK } }复制代码

4 表中对应的每列数据信息

class SKSQLiteColumnData{ var table: String var columnName: String var dataType: Int32 var value: Optional init(table:String, columnName: String, dataType:Int32, value:Optional){ self.table = table self.columnName = columnName self.dataType = dataType self.value = value }}复制代码

5 对数据按行处理

class SKSQLiteRow : IteratorProtocol{ typealias Element = SKSQLiteColumnData var rows:[SKSQLiteColumnData] = [SKSQLiteColumnData]() init(_ rows:[SKSQLiteColumnData] = [SKSQLiteColumnData]()) { self.rows = rows } func add(_ data:SKSQLiteColumnData){ rows.append(data) } func next() -> SKSQLiteColumnData? { return rows.removeFirst() }}复制代码

6 对sqlite的数据类型进行统一整理

protocol SKSQLiteDataType {}extension Int32: SKSQLiteDataType{}extension String: SKSQLiteDataType{}extension NSNull: SKSQLiteDataType{}

复制代码