[iOS] SQLite 사용하기

이번 노트에서는 앱 내에서 database를 사용해 보려한다. 여러 방법이 있지만 sqlite를 사용하여 데이터 CRUD하는 방법을 알아보려 한다.

import

import SQLite3

sqlite3는 기본 내장 되어있어 import만 해주면 된다.

create

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

write

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

read

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

delete

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

update

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

drop table

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)하는 방법에 대해 알아봤다.