SKNjKHgx

1import Foundation
2import SQLite3
3
4class DBHelper
5{
6    init()
7    {
8        db = openDatabase()
9        createTable()
10    }
11
12    let dbPath: String = "myDb.sqlite"
13    var db:OpaquePointer?
14
15    func openDatabase() -> OpaquePointer?
16    {
17        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
18            .appendingPathComponent(dbPath)
19        var db: OpaquePointer? = nil
20        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
21        {
22            print("error opening database")
23            return nil
24        }
25        else
26        {
27            print("Successfully opened connection to database at \(dbPath)")
28            return db
29        }
30    }
31    
32    func createTable() {
33        let createTableString = "CREATE TABLE IF NOT EXISTS person(Id INTEGER PRIMARY KEY,name TEXT,age INTEGER);"
34        var createTableStatement: OpaquePointer? = nil
35        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK
36        {
37            if sqlite3_step(createTableStatement) == SQLITE_DONE
38            {
39                print("person table created.")
40            } else {
41                print("person table could not be created.")
42            }
43        } else {
44            print("CREATE TABLE statement could not be prepared.")
45        }
46        sqlite3_finalize(createTableStatement)
47    }
48    
49    
50    func insert(id:Int, name:String, age:Int)
51    {
52        let persons = read()
53        for p in persons
54        {
55            if p.id == id
56            {
57                return
58            }
59        }
60        let insertStatementString = "INSERT INTO person (Id, name, age) VALUES (?, ?, ?);"
61        var insertStatement: OpaquePointer? = nil
62        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
63            sqlite3_bind_int(insertStatement, 1, Int32(id))
64            sqlite3_bind_text(insertStatement, 2, (name as NSString).utf8String, -1, nil)
65            sqlite3_bind_int(insertStatement, 3, Int32(age))
66            
67            if sqlite3_step(insertStatement) == SQLITE_DONE {
68                print("Successfully inserted row.")
69            } else {
70                print("Could not insert row.")
71            }
72        } else {
73            print("INSERT statement could not be prepared.")
74        }
75        sqlite3_finalize(insertStatement)
76    }
77    
78    func read() -> [Person] {
79        let queryStatementString = "SELECT * FROM person;"
80        var queryStatement: OpaquePointer? = nil
81        var psns : [Person] = []
82        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
83            while sqlite3_step(queryStatement) == SQLITE_ROW {
84                let id = sqlite3_column_int(queryStatement, 0)
85                let name = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
86                let year = sqlite3_column_int(queryStatement, 2)
87                psns.append(Person(id: Int(id), name: name, age: Int(year)))
88                print("Query Result:")
89                print("\(id) | \(name) | \(year)")
90            }
91        } else {
92            print("SELECT statement could not be prepared")
93        }
94        sqlite3_finalize(queryStatement)
95        return psns
96    }
97    
98    func deleteByID(id:Int) {
99        let deleteStatementStirng = "DELETE FROM person WHERE Id = ?;"
100        var deleteStatement: OpaquePointer? = nil
101        if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
102            sqlite3_bind_int(deleteStatement, 1, Int32(id))
103            if sqlite3_step(deleteStatement) == SQLITE_DONE {
104                print("Successfully deleted row.")
105            } else {
106                print("Could not delete row.")
107            }
108        } else {
109            print("DELETE statement could not be prepared")
110        }
111        sqlite3_finalize(deleteStatement)
112    }
113    
114}