· 8 years ago · Jul 09, 2018, 01:40 PM
1import UIKit
2import SQLite3
3
4
5import UIKit
6
7let aStrDBName = "Users.db"
8
9
10class Database: NSObject {
11
12 //Properties
13 var db: OpaquePointer? = nil
14
15 var statement: OpaquePointer? = nil
16
17 //Shared Instance
18 class var sharedInstance: Database {
19 struct Static {
20 static var instance = Database()
21 }
22
23 return Static.instance
24 }
25
26 //MARK:- Create and Copy DB
27 /// create path for DB if does not exist
28 ///
29 /// - Parameter dbname: pass user Database file
30 /// - Returns: the path for db
31 private class func createPath(dbname: String) -> String {
32 let doumentDirectoryPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0] as NSString
33 let destinationPath = doumentDirectoryPath.appendingPathComponent(dbname)
34
35 print("destinationPath == \(destinationPath)")
36 return destinationPath
37 }
38
39
40
41 /// Create copy for database in filemanager
42 ///
43 /// - Parameter complitionFailure: incase of failure return description
44 func createEditableCopyOfDatabaseIfNeeded(failure complitionFailure: (()-> Void)) {
45 let fileManger = FileManager.default
46
47 let sourcePath = Bundle.main.path(forResource: "Users", ofType: "db")
48
49 let dbPath = Database.createPath(dbname: aStrDBName)
50
51 if fileManger.fileExists(atPath: dbPath) {
52 //Success
53 let successBackup = addSkipBackupAttributeToItem(at: URL(fileURLWithPath: dbPath))
54 print(successBackup)
55
56 }else{
57 //Failure
58
59 do {
60 try fileManger.copyItem(atPath: sourcePath!, toPath: dbPath)
61 }catch{
62 print(error.localizedDescription)
63
64 // Alert if file is not exist
65 if !error.localizedDescription.contains("already exists") {
66 print(error.localizedDescription)
67
68 complitionFailure()
69
70 }else{
71
72 print(error.localizedDescription)
73
74 }
75 }
76 }
77 }
78
79 private func sqliteOpenAndPrepare(query: String, success complition: (()-> Void), fail complitionFailure: ((String)-> Void)) {
80
81 let dbPath = Database.createPath(dbname: aStrDBName)
82
83 if sqlite3_open(URL(fileURLWithPath: dbPath).path, &db) == SQLITE_OK {
84
85 if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
86
87 if sqlite3_step(statement) == SQLITE_DONE {
88 complition()
89 }else{
90 let errmsg = String(cString: sqlite3_errmsg(db))
91 print("\(errmsg)")
92 complitionFailure(errmsg)
93 }
94
95 }else{
96 let errmsg = String(cString: sqlite3_errmsg(db))
97 print("error preparing insert: \(errmsg)")
98 complitionFailure(errmsg)
99
100 }
101 }else{
102 let errmsg = String(cString: sqlite3_errmsg(db))
103 complitionFailure(errmsg)
104 }
105
106 }
107
108 //MARK:- Create Table
109
110 /// - Parameters:
111 /// - query: "CREATE TABLE if not exists test (id integer primary key autoincrement, name text)"
112 /// - complition: called when successfully inserted on db
113 /// - complitionFailure: called when failure occurs
114
115
116 func createTable(query: String, success complition: (()-> Void), failure complitionFailure: ((String)-> Void)) {
117
118 let dbPath = Database.createPath(dbname: aStrDBName)
119
120 if sqlite3_open(URL(fileURLWithPath: dbPath).path, &db) == SQLITE_OK {
121
122 if sqlite3_exec(db, query, nil, nil, nil) == SQLITE_OK {
123
124 if sqlite3_step(statement) == SQLITE_DONE {
125 complition()
126 }
127
128 }else{
129 let errmsg = String(cString: sqlite3_errmsg(db))
130 complitionFailure(errmsg)
131
132 }
133 }else{
134 let errmsg = String(cString: sqlite3_errmsg(db))
135 complitionFailure(errmsg)
136 }
137
138
139 if sqlite3_finalize(statement) == SQLITE_OK {
140
141 if sqlite3_close(db) == SQLITE_OK {
142 }else{
143
144 }
145 }else{
146
147 }
148
149 }
150
151
152
153
154
155 //MARK:- Insert Data in DB
156
157 /// Insert Data in db
158 ///
159 /// - Parameters:
160 /// - query: pass value to be inserted as "INSERT INTO tableName (key1 , key2) VALUES ('value1' , ''value2)" forEx:- "INSERT INTO Employee (name, cell) VALUES ('abc', 123)"
161 /// - complition: called when successfully inserted on db
162 /// - complitionFailure: called when failure occurs
163
164 func insert(query: String, success complition: (()-> Void), failure complitionFailure:((String)-> Void)) {
165
166 self.sqliteOpenAndPrepare(query: query, success: {
167 // Succes
168
169 complition()
170
171 }, fail: {_ in
172 // Failure
173
174 let errmsg = String(cString: sqlite3_errmsg(db))
175 complitionFailure(errmsg)
176 })
177
178 if sqlite3_finalize(statement) == SQLITE_OK {
179
180 if sqlite3_close(db) == SQLITE_OK {
181 }else{
182 let errmsg = String(cString: sqlite3_errmsg(db))
183 complitionFailure(errmsg)
184
185 }
186 }else{
187 let errmsg = String(cString: sqlite3_errmsg(db))
188 complitionFailure(errmsg)
189
190 }
191
192 }
193
194
195 /// - Parameters:
196 /// - query: "INSERT INTO Tbl (name, cell) VALUES (?,?)"
197 /// - complition: called when successfully inserted on db
198 /// - complitionFailure: called when failure occurs
199
200 func insertDatinBulk(query: String, array : [[String:AnyObject]], fail complitionFailure: ((String)-> Void)) {
201
202 if (array.count > 0) {
203
204 var errMsg:UnsafeMutablePointer<Int8>? = nil
205
206 if sqlite3_exec(db, "BEGIN TRANSACTION", nil, nil, &errMsg) == SQLITE_OK {
207
208 print("Insert Data is \(array)")
209
210 let cSql = query.cString(using: String.Encoding.utf8)
211 var result:CInt=0
212 let dbPath = Database.createPath(dbname: aStrDBName)
213
214 let dbpathEncoded = dbPath.cString(using: String.Encoding.utf8)
215
216 if sqlite3_open(dbpathEncoded, &db) == SQLITE_OK {
217
218 if sqlite3_prepare_v2(db, cSql, -1, &statement, nil) == SQLITE_OK {
219
220 for item in array
221 {
222
223 let itemName = item["name"] as! NSString
224 sqlite3_bind_text(statement, 1, itemName.utf8String, -1, nil)
225 sqlite3_bind_int(statement, 2, Int32(123))
226
227 result = sqlite3_step(statement)
228
229 if(result != SQLITE_DONE)
230 {
231 print("failed to insert")
232 }
233 else
234 {
235 print("inserted")
236 break
237 }
238
239 sqlite3_clear_bindings(statement);
240 sqlite3_reset(statement);
241 }
242
243 }else{
244 let errmsg = String(cString: sqlite3_errmsg(db))
245 print("error preparing insert: \(errmsg)")
246
247 complitionFailure(errmsg)
248
249 }
250 }else{
251 let errmsg = String(cString: sqlite3_errmsg(db))
252
253 complitionFailure(errmsg)
254 }
255
256
257 sqlite3_exec(db, "COMMIT TRANSACTION", nil, nil, &errMsg)
258 sqlite3_exec(db, "END TRANSACTION", nil, nil, &errMsg)
259 sqlite3_finalize(statement)
260 sqlite3_close(statement)
261
262 }
263
264
265 }
266 }
267
268
269 //MARK:- Update Data in DB
270
271 /// update Data in db
272 ///
273 /// - Parameters:
274 /// - query: pass value to be updated as "UPDATE TblName SET name='Test' WHERE name='indianic'"
275 /// - complition: called when successfully inserted on db
276 /// - complitionFailure: called when failure occurs
277
278 func update(query: String, success complition: (()-> Void), failure complitionFailure: @escaping ((String)-> Void)) {
279
280
281 self.sqliteOpenAndPrepare(query: query, success: {
282 // Succes
283
284 complition()
285
286 }, fail: {_ in
287 // Failure
288 let errmsg = String(cString: sqlite3_errmsg(db))
289 complitionFailure(errmsg)
290 })
291
292
293 if sqlite3_finalize(statement) == SQLITE_OK {
294
295 if sqlite3_close(db) == SQLITE_OK {
296 }else{
297 sqlite3_errmsg(db)
298
299 }
300 }else{
301 sqlite3_errmsg(db)
302
303 }
304
305 }
306
307 //MARK:- Delete Data in DB
308
309 /// delete Data in db
310 ///
311 /// - Parameters:
312 /// - query: pass value to be delete as "DELETE FROM Employee WHERE name='abc'"
313 /// - complition: called when successfully inserted on db
314 /// - complitionFailure: called when failure occurs
315
316 func delete(query: String, success complition: (()-> Void), failure complitionFailure: @escaping ((String)-> Void)) {
317
318 self.sqliteOpenAndPrepare(query: query, success: {
319 // Succes
320
321 complition()
322
323 }, fail: {_ in
324 // Failure
325
326 let errmsg = String(cString: sqlite3_errmsg(db))
327 complitionFailure(errmsg)
328 })
329
330
331 if sqlite3_finalize(statement) == SQLITE_OK {
332
333 if sqlite3_close(db) == SQLITE_OK {
334 }else{
335 sqlite3_errmsg(db)
336
337 }
338 }else{
339 sqlite3_errmsg(db)
340
341 }
342
343 }
344
345 //MARK:- Get count for Table in DB
346
347 /// Get Data from db
348 ///
349 /// - Parameters:
350 /// - query: "SELECT COUNT(*) FROM TblName" // Can set column instead of *
351 /// - complition: called when successfully inserted on db
352 /// - complitionFailure: called when failure occurs
353
354 func getRecordCount(query: String, success complition: ((Int)-> Void), failure complitionFailure: ((String)-> Void)) {
355
356 var count: Int = 0
357
358 let dbPath = Database.createPath(dbname: aStrDBName)
359
360 if sqlite3_open(URL(fileURLWithPath: dbPath).path, &db) == SQLITE_OK {
361
362 if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
363
364 if sqlite3_step(statement) == SQLITE_ROW {
365 count = Int(sqlite3_column_int(statement, 0))
366 complition(count)
367
368 }
369
370 }else{
371 let errmsg = String(cString: sqlite3_errmsg(db))
372 complitionFailure(errmsg)
373
374 }
375 }else{
376 let errmsg = String(cString: sqlite3_errmsg(db))
377 complitionFailure(errmsg)
378 }
379
380
381 if sqlite3_finalize(statement) == SQLITE_OK {
382
383 if sqlite3_close(db) == SQLITE_OK {
384 }else{
385 sqlite3_errmsg(db)
386 }
387 }else{
388 sqlite3_errmsg(db)
389 }
390
391 }
392
393 /// Get Data from db
394 ///
395 /// - Parameters:
396 /// - query: "SELECT AVG(marks) AS MarksAverage FROM Tbl"
397 /// - complition: called when successfully inserted on db
398 /// - complitionFailure: called when failure occurs
399
400 func getRecordAvegare(query: String, success complition: ((Int)-> Void), failure complitionFailure: ((String)-> Void)) {
401
402 var avg: Int = 0
403
404 let dbPath = Database.createPath(dbname: aStrDBName)
405
406 if sqlite3_open(URL(fileURLWithPath: dbPath).path, &db) == SQLITE_OK {
407
408 if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
409
410 if sqlite3_step(statement) == SQLITE_ROW {
411 avg = Int(sqlite3_column_int(statement, 0))
412 complition(avg)
413
414 }
415
416 }else{
417 let errmsg = String(cString: sqlite3_errmsg(db))
418 complitionFailure(errmsg)
419
420 }
421 }else{
422 let errmsg = String(cString: sqlite3_errmsg(db))
423 complitionFailure(errmsg)
424 }
425
426
427 if sqlite3_finalize(statement) == SQLITE_OK {
428
429 if sqlite3_close(db) == SQLITE_OK {
430 }else{
431 sqlite3_errmsg(db)
432 }
433 }else{
434 sqlite3_errmsg(db)
435 }
436
437 }
438
439 /// - Parameters:
440 /// - query: "SELECT * FROM Tbl WHERE name='abc'"
441 /// - complition: called when successfully inserted on db
442 /// - complitionFailure: called when failure occurs
443
444 func getData(query: String, fail complitionFailure: ((String)-> Void)) -> [[String:AnyObject]] {
445
446 var result = [[String:AnyObject]]()
447
448 let dbPath = Database.createPath(dbname: aStrDBName)
449
450 if sqlite3_open(URL(fileURLWithPath: dbPath).path, &db) == SQLITE_OK {
451
452 if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
453
454
455 while(sqlite3_step(statement) == SQLITE_ROW) {
456
457 let count: Int32 = sqlite3_column_count(statement)
458 print(count)
459
460 var columnNAme: String = ""
461
462 var aDict = [AnyHashable: Any]()
463
464 for i in 0..<count {
465
466 let columnName = sqlite3_column_name(statement, i)
467 columnNAme = String(cString: columnName!)
468
469 if sqlite3_column_type(statement, i) == SQLITE_TEXT {
470
471 let name = sqlite3_column_text(statement, i)
472
473 aDict[columnNAme] = String(cString: name!)
474
475 }else if sqlite3_column_type(statement, i) == SQLITE_INTEGER {
476
477 let aintValue = sqlite3_column_int(statement, i)
478
479 aDict[columnNAme] = Int(aintValue)
480
481 }else if sqlite3_column_type(statement, i) == SQLITE_BLOB {
482
483 let len = sqlite3_column_bytes(statement, i)
484
485 let aBolbValue = sqlite3_column_blob(statement, i)
486
487 aDict[columnNAme] = NSData(bytes: aBolbValue, length: Int(len))
488
489 }else if sqlite3_column_type(statement, i) == SQLITE_FLOAT {
490
491 let aFloatValue = sqlite3_column_double(statement, i)
492
493 aDict[columnNAme] = Float(aFloatValue)
494
495 }else if sqlite3_column_type(statement, i) == SQLITE_NULL {
496
497
498 }
499 }
500 result.append(aDict as! [String : AnyObject])
501
502 }
503
504 }else{
505 let errmsg = String(cString: sqlite3_errmsg(db))
506 complitionFailure(errmsg)
507
508 }
509 }else{
510 let errmsg = String(cString: sqlite3_errmsg(db))
511 complitionFailure(errmsg)
512 }
513
514 return result
515
516 }
517
518
519
520 func addSkipBackupAttributeToItem(at URL: URL) -> Bool {
521 do {
522 if FileManager.default.fileExists(atPath: URL.path) {
523 let error: Error? = nil
524 let success = try NSURL(string: URL.absoluteString)?.setResourceValue(Int(truncating: true), forKey: .isExcludedFromBackupKey)
525 if success == nil {
526 print("Error excluding \(URL.lastPathComponent) from backup \(error)")
527 }
528 return (success != nil)
529 }
530 }
531 catch {
532 }
533 return false
534 }
535}