이번 노트에서는 앱 내에서 database를 사용해 보려한다. 여러 방법이 있지만 sqlite
를 사용하여 데이터 CRUD
하는 방법을 알아보려 한다.
import SQLite3
sqlite3
는 기본 내장 되어있어 import만 해주면 된다.
var database: OpaquePointer?
let tableName: String = "dbTable"
func createTable() {
let fileURL = try! FileManager.default.url(
for: .documentDirectory,
in: .userDomainMask,
appropriateFor: nil,
create: false).appendingPathComponent("DSDatabase.sqlite")
if sqlite3_open(fileURL.path, &self.database) != SQLITE_OK {
print("table not exsist")
}
let CREATE_QUERY_TEXT: String = "CREATE TABLE IF NOT EXISTS \(self.tableName) (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, birth TEXT, phone TEXT)"
if sqlite3_exec(self.database, CREATE_QUERY_TEXT, nil, nil, nil) != SQLITE_OK {
let errorMsg = String(cString: sqlite3_errmsg(self.database))
print("cannot create table: \(errorMsg)")
} else {
print("success create table!!")
}
}
func insert(name: String, birth: String, phone: String) {
var stmt: OpaquePointer?
let INSERT_QUERY_TEXT: String = "INSERT INTO \(self.tableName) (name, birth, phone) VALUES (?,?,?)"
if sqlite3_prepare(self.database, INSERT_QUERY_TEXT, -1, &stmt, nil) != SQLITE_OK {
let errorMsg = String(cString: sqlite3_errmsg(self.database)!)
print("error preparing insert:v1 \(errorMsg)")
return
}
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("failure binding name(name): \(errMsg)")
return
}
if sqlite3_bind_text(stmt, 2, birth, -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("failure binding name(birth): \(errMsg)")
return
}
if sqlite3_bind_text(stmt, 3, phone, -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("failure binding name(phone): \(errMsg)")
return
}
if sqlite3_step(stmt) != SQLITE_DONE {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("insert fail: \(errMsg)")
return
} else {
print("success insert data!")
}
}
func read() {
let SELECT_QUERY = "SELECT * FROM \(self.tableName)"
var stmt: OpaquePointer?
if sqlite3_prepare(self.database, SELECT_QUERY, -1, &stmt, nil) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("error preparing insert: v1\(errMsg)")
return
}
self.userData.removeAll()
while(sqlite3_step(stmt) == SQLITE_ROW) {
let id = sqlite3_column_int(stmt, 0)
let name = String(cString: sqlite3_column_text(stmt, 1))
let birth = String(cString: sqlite3_column_text(stmt, 2))
let phone = String(cString: sqlite3_column_text(stmt, 3))
self.userData.append(UserType(id: Int(id), name: name, birth: birth, phone: phone))
}
sqlite3_finalize(stmt)
self.tableView?.reloadData()
print("success read data!")
}
func delete(id: Int) {
let DELETE_QUERY = "DELETE FROM \(self.tableName) WHERE id = \(id)"
var stmt: OpaquePointer?
if sqlite3_prepare(self.database, DELETE_QUERY, -1, &stmt, nil) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("error preparing delete: v1\(errMsg)")
return
}
if sqlite3_step(stmt) != SQLITE_DONE {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("delete fail: \(errMsg)")
return
}
sqlite3_finalize(stmt)
let index = self.userData.enumerated().filter {
$1.id == id
}
if index.count > 0, self.userData.count > 0 {
self.userData.remove(at: index.first!.offset)
}
print("success delete id: \(id)")
}
func update(id: Int, name: String, birth: String, phone: String) {
let UPDATE_QUERY = "UPDATE \(self.tableName) SET name = '\(name)', birth = '\(birth)', phone = '\(phone)' WHERE id == \(id)"
var stmt: OpaquePointer?
if sqlite3_prepare(self.database, UPDATE_QUERY, -1, &stmt, nil) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("error preparing update: v1\(errMsg)")
return
}
if sqlite3_step(stmt) != SQLITE_DONE {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("update fail: \(errMsg)")
return
}
sqlite3_finalize(stmt)
print("success update id: \(id)")
}
func dropTable(tableName: String) {
let DROP_TABLE_QUERY = "DROP TABLE \(tableName)"
var stmt: OpaquePointer?
if sqlite3_prepare(self.database, DROP_TABLE_QUERY, -1, &stmt, nil) != SQLITE_OK {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("error preparing drop table: v1\(errMsg)")
return
}
if sqlite3_step(stmt) != SQLITE_DONE {
let errMsg = String(cString: sqlite3_errmsg(self.database)!)
print("drop table fail: \(errMsg)")
return
}
sqlite3_finalize(stmt)
self.userData.removeAll()
self.tableView.reloadData()
print("success dropped table named: \(tableName)")
}
이렇게 db table
을 생성(create)
하고 데이터 입력(write)
, 데이터 조회(read)
, 삭제(delete)
와 table을 삭제(drop)
하는 방법에 대해 알아봤다.