Swift 내부DB

SQLite3 - 기본 제공
FMDB - Pods 라이브러리

SQLite3

기본으로 제공되는 내부DB
sqlite3_open
- DB접근
sqlite3_prepare_v2
- 준비과정
- Query가 유효한지 확인
sqlite3_step
- Query 보냄
sqlite3_errmsg(db)
- 에러메시지를 찍어서 확인 할 수 있음
sqlite3_finalize
insert, select, update, 등 Query사용시 lock걸림을 풀어줘야함 ➡️ lock으로 디비접근불가 오류가능
- ⚠️자원 해제를 해주는 역할⚠️

Open

dbPath를 이용해서 파일 경로에 db등록
sqlite3_open 을 이용해서 db접근
OpaquePointer형의 변수를 활용하여 db이용

import SQLite3

class SQLiteServiceImp: SQLiteServicePorotol {
    var db: OpaquePointer?
    private let dbName = "imgMemo"
    private let tableName = "imgMemo"
    
    func openDatabase() -> Bool {
        guard let partDbPath = try? FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("\(dbName).sqlite") else {
            print("partDbPath is nil.")
            return false
        }
        
        if sqlite3_open(partDbPath.path, &db) == SQLITE_OK {
            print("Successfully opened connection to database at \(partDbPath)")
            return true
        } else {
            print("Unable to open database.")
            return false
        }
    }

Create

sqlite3_open을 이용해서 받아온 db를 참조하고있는 OpaquePointer형의 변수와

create쿼리 결과를 참조할 OpaquePointer형의 변수존재
sqlite3_prepare_v2(디비참조변수, 쿼리문, -1(문장끝까지의미), 쿼리결과참조변수, nil)
- 결과값으로 SQLITE_OK면 성공적 쿼리
- 준비과정, 맞는 쿼리인지 확인하는것 같다
sqlite3_step(쿼리결과참조변수)
- 쿼리를 보내는 함수같다
- 결과값으로 쿼리의 결과를 반환함

    func createTable() {
        let createTableString = """
        CREATE TABLE IF NOT EXISTS \(tableName) (
        id INT PRIMARY KEY,
        title CHAR(200),
        content TEXT,
        imgString TEXT);
        """
        
        var createTableStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) ==
            SQLITE_OK {
            
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("\nContact table created.")
            } else {
                print("\nContact table is not created.")
            }
        } else {
            print("\nCREATE TABLE statement is not prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }

Insert

타입에 유의
- Int ➡️ Int32
- String ➡️ NSString ➡️ utf8String
query 코드
- ‼️Column인덱스는 1부터‼️ 시작한다
- sqlite3_bind_int
- sqlite3_bind_text

var insertStatement: OpaquePointer?
let insertStatementString = "INSERT INTO \(tableName) (id, title, content, imgString) VALUES (?, ?, ?, ?)"

sqlite3_bind_int(insertStatement, 1, Int32(id))
sqlite3_bind_text(insertStatement, 2, title.utf8String, -1, nil)
sqlite3_bind_text(insertStatement, 3, content.utf8String, -1, nil)
sqlite3_bind_text(insertStatement, 4, concatString.utf8String, -1, nil)

이런식으로 insertStatement에 원하는 ? 위치를 고르고 바인드 시켜서 쿼리를 보냄

전체코드

    func insert(id: Int32, title: String, content: String, imgString: [String]) -> Bool {
        var insertStatement: OpaquePointer?
        let insertStatementString = "INSERT INTO \(tableName) (id, title, content, imgString) VALUES (?, ?, ?, ?)"
        
        if sqlite3_prepare(db, insertStatementString, -1, &insertStatement, nil) ==
            SQLITE_OK {
            
            let title: NSString = NSString(string: title)
            let content: NSString = NSString(string: content)
            var tempConcatString = ""
            for index in imgString.indices {
                if index == imgString.count - 1 {
                    tempConcatString += imgString[index]
                } else {
                    tempConcatString += "\(imgString[index]),"
                }
            }
            let concatString = NSString(string: tempConcatString)
            
            sqlite3_bind_int(insertStatement, 1, Int32(id))
            sqlite3_bind_text(insertStatement, 2, title.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, content.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, concatString.utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) != SQLITE_DONE {
                let errmsg = String(cString: sqlite3_errmsg(db)!)
                print("failure inserting: \(errmsg)")
                sqlite3_finalize(insertStatement)
                return false
            } else {
                print("\n success insert row.")
                sqlite3_finalize(insertStatement)
                return true
            }
        } else {
            print("\nINSERT statement is not prepared.")
            sqlite3_finalize(insertStatement)
            return false
        }
        
    }

Select

‼️컬럼인덱스는 0 부터시작‼️
sqlite3_column_int(쿼리변수, 가져올컬럼인덱스)
sqlite3_column_text

    func select() -> [Memo]? {
        var queryStatement: OpaquePointer?
        let queryStatementString = "SELECT * FROM \(tableName)"
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) ==
            SQLITE_OK {
            
            var memoList: [Memo] = []
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                
                let id = sqlite3_column_int(queryStatement, 0)
                
                guard let titleQuery = sqlite3_column_text(queryStatement, 1) else {
                    print("Query title is nil")
                    return nil
                }
                
                guard let contentQuery = sqlite3_column_text(queryStatement, 2) else {
                    print("Query content is nil")
                    return nil
                }
                guard let imgtQuery = sqlite3_column_text(queryStatement, 3) else {
                    print("Query content is nil")
                    return nil
                }
                
                let title = String(cString: titleQuery)
                let content = String(cString: contentQuery)
                let imgString = String(cString: imgtQuery)
                let memo = Memo(idx: Int(id), title: title, content: content, imgsString: imgString)
                
                memoList.append(memo)
                
            }
            sqlite3_finalize(queryStatement)
            return memoList
        } else {
            let errorMessage = String(cString: sqlite3_errmsg(db))
            print("\nQuery is not prepared \(errorMessage)")
            sqlite3_finalize(queryStatement)
            return nil
        }
    }

Update

    func update(id: Int, title: String, content: String, imgString: [String]) -> Bool {
        var updateStatement: OpaquePointer?
        var tempConcatString = ""
        for index in imgString.indices {
            if index == imgString.count - 1 {
                tempConcatString += imgString[index]
            } else {
                tempConcatString += "\(imgString[index]),"
            }
        }
        let concatString = NSString(string: tempConcatString)
        let updateStatementString = "UPDATE \(tableName) SET title = '\(title)', content = '\(content)', imgString = '\(concatString)' WHERE id = \(id)"
        
        if sqlite3_prepare_v2(db, updateStatementString, -1, &updateStatement, nil) ==
            SQLITE_OK {
            if sqlite3_step(updateStatement) == SQLITE_DONE {
                print("\nSuccessfully updated row.")
                sqlite3_finalize(updateStatement)
                return true
            } else {
                print("\nCould not update row.")
                sqlite3_finalize(updateStatement)
                return false
            }
        } else {
            print("\nUPDATE statement is not prepared")
            sqlite3_finalize(updateStatement)
            return false
        }
    }

Delete

    func delete(id: Int) {
        var deleteStatement: OpaquePointer?
        let deleteStatementString = "DELETE FROM \(tableName) WHERE Id = \(id)"
        
        if sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) ==
            SQLITE_OK {
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("\nSuccessfully deleted row.")
            } else {
                print("\nCould not delete row.")
            }
        } else {
            print("\nDELETE statement could not be prepared")
        }
        
        sqlite3_finalize(deleteStatement)
    }

Close

    func close() {
        sqlite3_close(db)
        print("close db")
    }

Drop

    func drop() {
        var dropStatement: OpaquePointer?
        let dropStatementString = "DROP TABLE \(tableName)"
        
        if sqlite3_prepare_v2(db, dropStatementString, -1, &dropStatement, nil) ==
            SQLITE_OK {
            
            if sqlite3_step(dropStatement) == SQLITE_DONE {
                print("\nSuccessfully drop table.")
            } else {
                print("\nCould not drop table.")
            }
        } else {
            print("\nDROP statement could not be prepared")
        }
        sqlite3_finalize(dropStatement)
    }
}

FMDB

SQLite는 Objective-C언어로 작성되어있음
Swift에 맞게 Wrapper해서 나온 라이브러리가 FMDB
SQLite보다 사용하기 용이
FMDatabase()
- DB 반환해줌
open()
- DB 접근
executeUpdate(쿼리) - Create, Insert, Update, Delete
- 쿼리 보낼때 사용
executeQuery(쿼리)- Select
- 쿼리 보낼때 사용
close()
- 자원 해제

Open

func setDB() {
    fileURL = try! FileManager.default
        .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        .appendingPathComponent("user.sqlite")
    database = FMDatabase(url: fileURL)
}

Create

try database.executeUpdate("create table if not exists history(id integer, item TEXT)", values: nil)

Insert

try database.executeUpdate("insert into history (id, item) values (?, ?)", values: ["0", item])

?위치에 맞게 배열을 생성해서 넣어주기

전체코드

    func insertQuery(item: String) {
        guard database.open() else {
            print("Unable to open database")
            return
        }
        do {
            try database.executeUpdate("create table if not exists history(id integer, item TEXT)", values: nil)
            if UserDefaults.standard.string(forKey: "dbId") == nil {
                UserDefaults.standard.set("0", forKey: "dbId")
                try database.executeUpdate("insert into history (id, item) values (?, ?)", values: ["0", item])
            }else {
                let dbId = UserDefaults.standard.integer(forKey: "dbId")
                UserDefaults.standard.set("\(dbId+1)", forKey: "dbId")
                try database.executeUpdate("insert into history (id, item) values (?, ?)", values: [dbId, item])
            }
            
        } catch {
            print("failed: \(error.localizedDescription)")
        }
        
        database.close()
    }

Select

rs.next()
- 쿼리에 맞는 row하나씩 반환
rs.string(forColumn: "item")
- 해당 컬럼Name과 자료형에 맞게 반환
- int형 반환 버그있음

    func selectQuery() {
        guard database.open() else {
            print("Unable to open database")
            return
        }
        do {
            let rs = try database.executeQuery("select id, item from history", values: nil)
            self.historyList.removeAll()
            while rs.next() {
                if let item = rs.string(forColumn: "item"), let id = rs.string(forColumn: "id") {
                    let data = SearchData(id: id, item: item)
                    self.historyList.append(data)
                }
            }
        } catch {
            print("failed: \(error.localizedDescription)")
        }
        database.close()
        self.historyList.reverse()
        self.searchHistoryTableView.reloadData()
    }

Update

 try database.executeUpdate("update history set item = ? where id = ?", values: [changeItem, id])

Close

database.close()

Drop

    func deleteAllQuery() {
        guard database.open() else {
            print("Unable to open database")
            return
        }
        do {
            try database.executeUpdate("drop table history", values: nil)
            self.historyList.removeAll()
        } catch {
            print("failed: \(error.localizedDescription)")
        }
        database.close()
    }

Delete

    func deleteQuery(id: String) {
        guard database.open() else {
            print("Unable to open database")
            return
        }
        do {
            try database.executeUpdate("delete from history where id = \(id)", values: nil)
        } catch {
            print("failed: \(error.localizedDescription)")
        }
        database.close()
    }

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

05차-발표자료.md

05차-발표자료.md

Swift 내부DB

SQLite3

Open

Create

Insert

Select

Update

Delete

Close

Drop

FMDB

Open

Create

Insert

Select

Update

Close

Drop

Delete

Files

05차-발표자료.md

Latest commit

History

05차-발표자료.md

File metadata and controls

Swift 내부DB

SQLite3

Open

Create

Insert

Select

Update

Delete

Close

Drop

FMDB

Open

Create

Insert

Select

Update

Close

Drop

Delete