Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

Rev 11 | Ir a la última revisión | Mostrar el archivo completo | | | Autoría | Ultima modificación | Ver Log |

Rev 11 Rev 19
Línea 8... Línea 8...
8
import UIKit
8
import UIKit
9
import SQLite3
9
import SQLite3
Línea 10... Línea 10...
10
 
10
 
11
class CapsuleDao {
11
class CapsuleDao {
12
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
12
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
Línea 13... Línea 13...
13
    private var db : OpaquePointer?
13
    private var database = Database.sharedInstance
14
    
14
    
Línea 15... Línea 15...
15
    static let sharedInstance: CapsuleDao = {
15
    static let sharedInstance: CapsuleDao = {
16
           let instance = CapsuleDao()
16
           let instance = CapsuleDao()
17
           
17
           
Línea 18... Línea -...
18
           // setup code
-
 
19
           return instance
-
 
20
    }()
-
 
Línea 21... Línea 18...
21
    
18
           // setup code
-
 
19
           return instance
22
    init() {
20
    }()
23
        self.db = Database.sharedInstance.open()
21
    
Línea 24... Línea 22...
24
    }
22
    
Línea 39... Línea 37...
39
        sqlite3_finalize(statement)
37
        sqlite3_finalize(statement)
40
        return count
38
        return count
41
    }
39
    }
Línea 42... Línea 40...
42
        
40
        
-
 
41
    func getCountByTopicUuid(topicUuid: String) -> Int {
43
    func getCountByTopicUuid(topicUuid: String) -> Int {
42
        let db = database.open()
44
        var count = 0
43
        var count = 0
45
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_CAPSULE +
44
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_CAPSULE +
Línea 46... Línea 45...
46
            " WHERE " +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " = \(topicUuid) LIMIT 1"
45
            " WHERE " +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " = \(topicUuid) LIMIT 1"
Línea 57... Línea 56...
57
        sqlite3_finalize(statement)
56
        sqlite3_finalize(statement)
58
        return count
57
        return count
59
    }
58
    }
Línea 60... Línea 59...
60
    
59
    
-
 
60
    func selectAll()-> [CapsuleModel] {
61
    func selectAll()-> [CapsuleModel] {
61
        let db = database.open()
62
        var records = [CapsuleModel]()
62
        var records = [CapsuleModel]()
63
        var query = "SELECT "
63
        var query = "SELECT "
64
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
64
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
65
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
65
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 105... Línea 105...
105
        sqlite3_finalize(statement)
105
        sqlite3_finalize(statement)
106
        return records
106
        return records
107
    }
107
    }
Línea 108... Línea 108...
108
        
108
        
-
 
109
    func selectAllByTopicUuid(topicUuid: String)-> [CapsuleModel] {
109
    func selectAllByTopicUuid(topicUuid: String)-> [CapsuleModel] {
110
        let db = database.open()
110
        var records = [CapsuleModel]()
111
        var records = [CapsuleModel]()
111
        var query = "SELECT "
112
        var query = "SELECT "
112
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
113
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
113
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
114
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 154... Línea 155...
154
        sqlite3_finalize(statement)
155
        sqlite3_finalize(statement)
155
        return records
156
        return records
156
    }
157
    }
Línea 157... Línea 158...
157
    
158
    
-
 
159
    func selectLastPendingByUserUuid(userUuid: String)-> [CapsuleModel] {
158
    func selectLastPendingByUserUuid(userUuid: String)-> [CapsuleModel] {
160
        let db = database.open()
159
        var records = [CapsuleModel]()
161
        var records = [CapsuleModel]()
160
        var query = "SELECT "
162
        var query = "SELECT "
161
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
163
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
162
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
164
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 211... Línea 213...
211
        sqlite3_finalize(statement)
213
        sqlite3_finalize(statement)
212
        return records
214
        return records
213
    }
215
    }
Línea 214... Línea 216...
214
    
216
    
-
 
217
    func selectAllPendingByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
215
    func selectAllPendingByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
218
        let db = database.open()
216
        var records = [CapsuleModel]()
219
        var records = [CapsuleModel]()
217
        var query = "SELECT "
220
        var query = "SELECT "
218
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
221
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
219
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
222
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 275... Línea 278...
275
        sqlite3_finalize(statement)
278
        sqlite3_finalize(statement)
276
        return records
279
        return records
277
    }
280
    }
Línea 278... Línea 281...
278
    
281
    
-
 
282
    func selectAllInProgressByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
279
    func selectAllInProgressByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
283
        let db = database.open()
280
        var records = [CapsuleModel]()
284
        var records = [CapsuleModel]()
281
        var query = "SELECT "
285
        var query = "SELECT "
282
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
286
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
283
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
287
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 340... Línea 344...
340
        sqlite3_finalize(statement)
344
        sqlite3_finalize(statement)
341
        return records
345
        return records
342
    }
346
    }
Línea 343... Línea 347...
343
    
347
    
-
 
348
    func selectAllCompletedByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
344
    func selectAllCompletedByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
349
        let db = database.open()
345
        var records = [CapsuleModel]()
350
        var records = [CapsuleModel]()
346
        var query = "SELECT "
351
        var query = "SELECT "
347
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
352
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
348
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
353
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
Línea 406... Línea 411...
406
        sqlite3_finalize(statement)
411
        sqlite3_finalize(statement)
407
        return records
412
        return records
408
    }
413
    }
Línea 409... Línea 414...
409
    
414
    
-
 
415
    func selectLastInProgress(userUuid: String)-> CapsuleModel {
410
    func selectLastInProgress(userUuid: String)-> CapsuleModel {
416
        let db = database.open()
411
        var model = CapsuleModel()
417
        var model = CapsuleModel()
412
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
418
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
413
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
419
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
414
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
420
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
Línea 468... Línea 474...
468
     WHERE uuid IN (SELECT capsule_uuid FROM tb_progress WHERE type = 'c' AND user_uuid = '4cf9fb4b-0190-4bb4-9369-15eb68e9cace' AND completed = 0)
474
     WHERE uuid IN (SELECT capsule_uuid FROM tb_progress WHERE type = 'c' AND user_uuid = '4cf9fb4b-0190-4bb4-9369-15eb68e9cace' AND completed = 0)
469
     ORDER BY name
475
     ORDER BY name
470
     */
476
     */
Línea 471... Línea 477...
471
 
477
 
-
 
478
    func selectByUuid(uuid: String)-> CapsuleModel {
472
    func selectByUuid(uuid: String)-> CapsuleModel {
479
        let db = database.open()
473
        var model = CapsuleModel()
480
        var model = CapsuleModel()
474
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
481
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
475
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
482
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
476
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
483
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
Línea 505... Línea 512...
505
                
512
                
506
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
513
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
Línea 507... Línea 514...
507
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
514
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
-
 
515
                
-
 
516
            }
508
                
517
        } else {
509
            }
518
            database.printError()
510
        }
519
        }
511
        sqlite3_finalize(statement)
520
        sqlite3_finalize(statement)
Línea 512... Línea 521...
512
        return model
521
        return model
-
 
522
    }
513
    }
523
        
514
        
524
    func insert(capsule: CapsuleModel) {
515
    func insert(capsule: CapsuleModel) {
525
        let db = database.open()
516
        var query = "INSERT INTO " + Constants.TABLE_CAPSULE + " ( "
526
        var query = "INSERT INTO " + Constants.TABLE_CAPSULE + " ( "
517
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
527
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
Línea 544... Línea 554...
544
            sqlite3_bind_double(statement, 10,Double("\(capsule.totalRating)") ?? 0)
554
            sqlite3_bind_double(statement, 10,Double("\(capsule.totalRating)") ?? 0)
545
            sqlite3_bind_text(statement, 11, capsule.addedOn , -1, SQLITE_TRANSIENT)
555
            sqlite3_bind_text(statement, 11, capsule.addedOn , -1, SQLITE_TRANSIENT)
546
            sqlite3_bind_text(statement, 12, capsule.updatedOn , -1, SQLITE_TRANSIENT)
556
            sqlite3_bind_text(statement, 12, capsule.updatedOn , -1, SQLITE_TRANSIENT)
Línea 547... Línea 557...
547
 
557
 
548
           if (sqlite3_step(statement) != SQLITE_DONE) {
558
           if (sqlite3_step(statement) != SQLITE_DONE) {
-
 
559
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
-
 
560
            
549
            print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
561
                database.printError()
550
           }
562
           }
551
        } else {
563
        } else {
-
 
564
            print("Fallo la prepración de insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
-
 
565
            
552
            print("Fallo la prepración de insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
566
            database.printError()
Línea 553... Línea 567...
553
        }
567
        }
554
       
568
       
Línea 555... Línea 569...
555
        sqlite3_finalize(statement)
569
        sqlite3_finalize(statement)
-
 
570
    }
556
    }
571
 
557
 
572
    func update(capsule : CapsuleModel) {
558
    func update(capsule : CapsuleModel) {
573
        let db = database.open()
559
        var query = "UPDATE " + Constants.TABLE_CAPSULE
574
        var query = "UPDATE " + Constants.TABLE_CAPSULE
560
        query = query + " SET " + Constants.TABLE_CAPSULE_FIELD_NAME + " = '\(capsule.name)', "
575
        query = query + " SET " + Constants.TABLE_CAPSULE_FIELD_NAME + " = '\(capsule.name)', "
Línea 570... Línea 585...
570
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = '\(capsule.uuid)' ;"
585
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = '\(capsule.uuid)' ;"
571
        var statement : OpaquePointer? = nil
586
        var statement : OpaquePointer? = nil
572
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
587
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
573
            if (sqlite3_step(statement) != SQLITE_DONE) {
588
            if (sqlite3_step(statement) != SQLITE_DONE) {
574
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
589
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
-
 
590
           
-
 
591
                database.printError()
575
            }
592
            }
576
        } else {
593
        } else {
577
            print("Fallo la preparación para actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
594
            print("Fallo la preparación para actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
-
 
595
            
-
 
596
            database.printError()
578
        }
597
        }
579
        sqlite3_finalize(statement)
598
        sqlite3_finalize(statement)
580
    }
599
    }
Línea 581... Línea 600...
581
    
600
    
-
 
601
 
582
 
602
 
583
 
603
    func remove(uuid: String) {
584
    func remove(uuid: String) {
604
        let db = database.open()
585
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE +
605
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE +
586
            " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = " + "\(uuid) ;"
606
            " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = " + "\(uuid) ;"
587
        var statement : OpaquePointer? = nil
607
        var statement : OpaquePointer? = nil
-
 
608
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
-
 
609
            if (sqlite3_step(statement) != SQLITE_DONE) {
588
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
610
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
589
            if (sqlite3_step(statement) != SQLITE_DONE) {
611
                
590
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
612
                database.printError()
-
 
613
            }
-
 
614
        } else {
591
            }
615
            print("Fallo la preparación de borrar un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
592
        } else {
616
            
593
            print("Fallo la preparación de borrar un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
617
            database.printError()
Línea 594... Línea 618...
594
        }
618
        }
-
 
619
        sqlite3_finalize(statement)
595
        sqlite3_finalize(statement)
620
    }
596
    }
621
 
597
 
622
    func removeAll() {
598
    func removeAll() {
623
        let db = database.open()
599
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE + ";"
624
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE + ";"
-
 
625
        var statement : OpaquePointer? = nil
-
 
626
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
600
        var statement : OpaquePointer? = nil
627
            if (sqlite3_step(statement) != SQLITE_DONE) {
601
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
628
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
602
            if (sqlite3_step(statement) != SQLITE_DONE) {
629
                
-
 
630
                database.printError()
-
 
631
            }
603
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
632
        } else {
604
            }
633
            print("No se pudo preparar el borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
605
        } else {
634
            
Línea 606... Línea 635...
606
            print("No se pudo preparar el borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
635
            database.printError()