N0jit35J

1
2
3import Foundation
4import SQLite3
5
6/*
7 *  Don't change the signatures
8 *  This class should be used in all places where access to the data is required
9 *  
10 */
11
12
13class DbManager{
14    
15    var db: OpaquePointer?
16    
17    init() {
18        //create and open the database
19        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
20            .appendingPathComponent("labtest.sqlite")
21        
22        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
23            print("error opening database")
24        }
25        
26        if sqlite3_exec(db, "DROP TABLE contacts;", nil, nil, nil) != SQLITE_OK {
27            let errmsg = String(cString: sqlite3_errmsg(db)!)
28            print("error creating table: \(errmsg)")
29        }
30        
31        if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS contacts ( contact_id integer PRIMARY KEY, name text NOT NULL, email text NOT NULL );", nil, nil, nil) != SQLITE_OK {
32            let errmsg = String(cString: sqlite3_errmsg(db)!)
33            print("error creating table: \(errmsg)")
34        }
35        
36        // insert some contacts
37        insertItem(name: "1n", email: "1e")
38        insertItem(name: "2n", email: "2e")
39        insertItem(name: "3n", email: "3e")
40        
41        
42    }
43    
44    deinit {
45        //close the database
46        
47        
48        
49    }
50    
51    var data:[Int:(id:Int, name:String, email:String)] = [:]
52    
53    func selectAll()->[Int:(id:Int, name:String, email:String)]{
54        
55        //this is our select query
56        let queryString = "SELECT * FROM contacts"
57        
58        //statement pointer
59        var stmt:OpaquePointer?
60        
61        //preparing the query
62        if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
63            let errmsg = String(cString: sqlite3_errmsg(db)!)
64            print("error preparing insert: \(errmsg)")
65        }
66        
67        var counter = 0
68        
69        //traversing through all the records
70        while(sqlite3_step(stmt) == SQLITE_ROW){
71            let id = sqlite3_column_int(stmt, 0)
72            let name = String(cString: sqlite3_column_text(stmt, 1))
73            let email = String(cString: sqlite3_column_text(stmt, 2))
74            
75            //adding values to list
76            data[counter] = (id: Int(id), name: name, email: email)
77            counter += 1;
78        }
79        
80        return data
81        
82    }
83    
84    func selectItem(id:Int)->(id:Int, name:String, email:String){
85        
86        //returning selected item based on ID
87        //replace below return with your code
88        //this is our select query
89        let queryString = "SELECT * FROM contacts where contact_id = ?"
90        
91        //statement pointer
92        var stmt:OpaquePointer?
93        
94        //preparing the query
95        if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
96            let errmsg = String(cString: sqlite3_errmsg(db)!)
97            print("error preparing insert: \(errmsg)")
98        }
99        
100        if sqlite3_bind_int(stmt, 1, Int32(id)) != SQLITE_OK{
101            let errmsg = String(cString: sqlite3_errmsg(db)!)
102            print("failure binding name: \(errmsg)")
103        }
104        
105        //traversing through all the records
106        while(sqlite3_step(stmt) == SQLITE_ROW){
107            let id = sqlite3_column_int(stmt, 0)
108            let name = String(cString: sqlite3_column_text(stmt, 1))
109            let email = String(cString: sqlite3_column_text(stmt, 2))
110            
111            //adding values to list
112            return (id: Int(id), name: name, email: email)
113        }
114        
115        return (id: 0, name: "", email: "")
116    }
117    
118    func updateItem(id:Int, name:String) {
119        //call update
120        
121        
122    }
123    
124    func insertItem(name:String, email:String){
125        let query1 = "INSERT INTO contacts (name, email) VALUES (?,?)"
126        
127        var stmt: OpaquePointer?
128        
129        if sqlite3_prepare(db, query1, -1, &stmt, nil) != SQLITE_OK{
130            let errmsg = String(cString: sqlite3_errmsg(db)!)
131            print("error preparing insert: \(errmsg)")
132            return
133        }
134        
135        if sqlite3_bind_text(stmt, 1, name, -1, nil) != SQLITE_OK{
136            let errmsg = String(cString: sqlite3_errmsg(db)!)
137            print("failure binding name: \(errmsg)")
138            return
139        }
140        
141        if sqlite3_bind_text(stmt, 2, email, -1, nil) != SQLITE_OK{
142            let errmsg = String(cString: sqlite3_errmsg(db)!)
143            print("failure binding name: \(errmsg)")
144            return
145        }
146        
147        if sqlite3_step(stmt) != SQLITE_DONE {
148            let errmsg = String(cString: sqlite3_errmsg(db)!)
149            print("failure inserting hero: \(errmsg)")
150            return
151        }
152        
153    }
154    
155}