MPT1YJmd

1import SQLite3
2import Foundation
3
4class DatabaseHandler  {
5    
6    static var db: OpaquePointer?
7    var brainList = [BrainDumpAdapter]()
8  //  var viewController = ViewController()
9    
10    
11    init() {
12        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
13            .appendingPathComponent("DB.sqlite")
14        
15        if sqlite3_open(fileURL.path, &DatabaseHandler.db) != SQLITE_OK {
16            print("error opening database")
17        }
18        
19        if sqlite3_exec(DatabaseHandler.db, "CREATE TABLE IF NOT EXISTS BrainDump (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, prio INTEGER)", nil, nil,nil) != SQLITE_OK {
20            let errmsg = String(cString: sqlite3_errmsg(DatabaseHandler.db)!)
21            print("error creating table: \(errmsg)")
22        }
23        else {
24            print("Database Created")
25        }
26        //readValues()
27    }
28    
29    func readValues() -> [BrainDumpAdapter]{
30        brainList.removeAll()
31        
32        let queryString = "SELECT * FROM BrainDump ORDER BY prio ASC"
33        
34        var stmt:OpaquePointer?
35        if sqlite3_prepare(DatabaseHandler.db, queryString, -1, &stmt, nil) != SQLITE_OK{
36            let errmsg = String(cString: sqlite3_errmsg(DatabaseHandler.db)!)
37            print("error preparing insert: \(errmsg)")
38        }
39        
40        if(sqlite3_exec(DatabaseHandler.db, queryString, nil,nil, nil) != SQLITE_OK){
41            print("cant execute query")
42            
43        }
44        else {
45            print("String execute")
46        }
47        
48     //   sqlite3_exec(stmt, queryString, DatabaseHandler.db, OpaquePointer, nil)
49        
50        while(sqlite3_step(stmt) == SQLITE_ROW){
51            let id = sqlite3_column_int(stmt, 0)
52            let name = String(cString: sqlite3_column_text(stmt, 1))
53           // let energyLevel = String(cString: sqlite3_column_text(stmt, 2))
54            let prio = sqlite3_column_int(stmt, 2)
55            brainList.append(BrainDumpAdapter(id: Int(id), name: String(describing: name), prio: Int(prio)))
56        }
57       // viewController.tableViewHeroes.reloadData()
58        
59        return brainList;
60
61    }
62    
63    func deleteBrainDumpItem(itemId: Int){
64        let deleteStatementStirng = "DELETE FROM BrainDump WHERE id = ?;"
65        var deleteStatement: OpaquePointer? = nil
66        if sqlite3_prepare_v2(DatabaseHandler.db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
67            
68            sqlite3_bind_int(deleteStatement, 1, Int32(itemId))
69            
70            if sqlite3_step(deleteStatement) == SQLITE_DONE {
71                print("Successfully deleted row.")
72            } else {
73                print("Could not delete row.")
74            }
75        } else {
76            print("DELETE statement could not be prepared")
77        }
78        
79        sqlite3_finalize(deleteStatement)
80        print("delete")
81        
82    }
83    
84    func addBrainDump(name: String, prio: Int) {
85        let addStatementString = "INSERT INTO BrainDump (name, prio) VALUES (?,?);"
86        
87        //let addStatementString = "INSERT INTO BrainDump (name, energyLevel, prio) VALUES (?,?,?) ORDER BY (prio);"
88
89        var addStatement: OpaquePointer? = nil
90        if sqlite3_prepare_v2(DatabaseHandler.db, addStatementString, -1, &addStatement, nil) == SQLITE_OK {
91            
92            sqlite3_bind_text(addStatement, 1, name, -1, nil)
93            //sqlite3_bind_text(addStatement, 2, energyLevel, -1, nil)
94            sqlite3_bind_int(addStatement, 2, Int32(prio))
95            
96            if sqlite3_step(addStatement) == SQLITE_DONE {
97                print("Successfully added row.")
98            } else {
99                print("Could not add row.")
100            }
101        } else {
102            print("DELETE statement could not be prepared")
103        }
104        
105        sqlite3_finalize(addStatement)
106        print("add")
107        
108    }
109    
110    func updateBrainDump(name: String, itemId: Int) {
111        let updateStatementString = "UPDATE BrainDump SET name = ? WHERE id = ?;"
112        var updateStatement: OpaquePointer? = nil
113        
114        if sqlite3_prepare_v2(DatabaseHandler.db, updateStatementString, -1, &updateStatement, nil) == SQLITE_OK {
115            sqlite3_bind_text(updateStatement, 1, name, -1, nil)
116            sqlite3_bind_int(updateStatement, 2, Int32(itemId))
117
118            if sqlite3_step(updateStatement) == SQLITE_DONE {
119                print("Successfully updated row.")
120            } else {
121                print("Could not update row.")
122            }
123        } else {
124            print("UPDATE statement could not be prepared")
125        }
126        sqlite3_finalize(updateStatement)
127
128    }
129    
130   
131     
132    /*func addEnergyLevel(energyLevel: String) {
133        let addEnergyStatementString = "INSERT INTO BrainDump (energyLevel) VALUES (?);"
134        var addStatementEnergy: OpaquePointer? = nil
135        if sqlite3_prepare_v2(DatabaseHandler.db, addEnergyStatementString, -1, &addStatementEnergy, nil) == SQLITE_OK {
136            
137            sqlite3_bind_text(addStatementEnergy, 2, energyLevel, -1, nil)
138            
139            
140            if sqlite3_step(addStatementEnergy) == SQLITE_DONE {
141                print("Successfully added energy row.")
142            } else {
143                print("Could not add row.")
144            }
145        } else {
146            print("DELETE statement could not be prepared")
147        }
148        
149        sqlite3_finalize(addStatementEnergy)
150        print("add energy")
151        
152        
153    }*/
154}