Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

Rev 19 | Ir a la última revisión | | Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 1 
//
2 
//  QuestionDao.swift
3 
//  twogetskills
4 
//
5 
//  Created by Efrain Yanez Recanatini on 2/21/22.
6 
//
7 
 
8 
import UIKit
9 
import SQLite3
10 
 
11 
class QuestionDao {
12 
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
13 
    private var db : OpaquePointer?
14 
 
15 
    static let sharedInstance: QuestionDao = {
16 
           let instance = QuestionDao()
17 
 
18 
           // setup code
19 
           return instance
20 
    }()
21 
 
22 
    init() {
23 
        self.db = Database.sharedInstance.open()
24 
    }
25 
 
26 
    func selectAllByQuizUuid(quizUuid: String)-> [QuestionModel] {
27 
        var records = [QuestionModel]()
28 
 
29 
        var query = "SELECT "
30 
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " , "
31 
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " , "
32 
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + " , "
33 
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + " , "
34 
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " , "
35 
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " , "
36 
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
37 
        query = query + " FROM " + Constants.TABLE_QUESTION
38 
        query = query + " WHERE " + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " = '\(quizUuid)'  "
39 
        query = query + " ORDER BY " + Constants.TABLE_QUESTION_FIELD_POSITION + " ;"
40 
        var statement : OpaquePointer? = nil
41 
 
42 
 
43 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
44 
            while sqlite3_step(statement) == SQLITE_ROW {
45 
                var model = QuestionModel()
46 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
47 
                model.quizUuid = String(describing: String(cString : sqlite3_column_text(statement, 1)))
48 
                model.text = String(describing: String(cString: sqlite3_column_text(statement, 2)))
49 
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 3)))
50 
                model.points = Int(sqlite3_column_int(statement, 4))
51 
                model.position = Int(sqlite3_column_int(statement, 5))
52 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
53 
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
54 
 
55 
                records.append(model)
56 
 
57 
            }
58 
        }
59 
        sqlite3_finalize(statement)
60 
        return records
61 
    }
62 
 
63 
    func selectByUuid(uuid: String)-> QuestionModel  {
64 
        var model = QuestionModel()
65 
 
66 
        var query = "SELECT "
67 
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " , "
68 
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " , "
69 
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + " , "
70 
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + " , "
71 
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " , "
72 
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " , "
73 
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
74 
        query = query + " WHERE " + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(uuid)' LIMIT 1;"
75 
 
76 
        var statement : OpaquePointer? = nil
77 
 
78 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
79 
            if (sqlite3_step(statement) == SQLITE_ROW) {
80 
 
81 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
82 
                model.quizUuid = String(describing: String(cString : sqlite3_column_text(statement, 1)))
83 
                model.text = String(describing: String(cString: sqlite3_column_text(statement, 2)))
84 
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 3)))
85 
                model.points = Int(sqlite3_column_int(statement, 4))
86 
                model.position = Int(sqlite3_column_int(statement, 5))
87 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
88 
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
89 
            }
90 
        }
91 
        sqlite3_finalize(statement)
92 
        return model
93 
    }
94 
 
95 
    func insert(question: QuestionModel) {
96 
 
97 
        var query = "INSERT INTO " + Constants.TABLE_QUESTION + " ( "
98 
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + ", "
99 
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + ", "
100 
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + ", "
101 
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + ", "
102 
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + ", "
103 
        query = query + Constants.TABLE_QUESTION_FIELD_POSITION + ", "
104 
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + ", "
105 
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
106 
        query = query + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
107 
        var statement : OpaquePointer?
108 
 
109 
 
110 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
111 
 
112 
            sqlite3_bind_text(statement, 1, question.uuid, -1, SQLITE_TRANSIENT)
113 
            sqlite3_bind_text(statement, 2, question.quizUuid, -1, SQLITE_TRANSIENT)
114 
            sqlite3_bind_text(statement, 3, question.text , -1, SQLITE_TRANSIENT)
115 
            sqlite3_bind_text(statement, 4, question.type , -1, SQLITE_TRANSIENT)
116 
            sqlite3_bind_int(statement, 5, Int32(question.points))
117 
            sqlite3_bind_int(statement, 6, Int32(question.position))
118 
            sqlite3_bind_text(statement, 7, question.addedOn , -1, SQLITE_TRANSIENT)
119 
            sqlite3_bind_text(statement, 8, question.updatedOn , -1, SQLITE_TRANSIENT)
120 
 
121 
           if (sqlite3_step(statement) != SQLITE_DONE) {
122 
            print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_QUESTION) ")
123 
           }
124 
        } else {
125 
            print("Fallo la preparación de insertar un registro en una tabla: \(Constants.TABLE_QUESTION) ")
126 
        }
127 
 
128 
        sqlite3_finalize(statement)
129 
    }
130 
 
131 
    func update(question: QuestionModel) {
132 
        var query = "UPDATE " + Constants.TABLE_QUESTION
133 
        query = query + " SET " + Constants.TABLE_QUESTION_FIELD_TEXT + " = '\(question.text)', "
134 
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " = '\(question.points)', "
135 
        query = query + Constants.TABLE_QUESTION_FIELD_POSITION + " = '\(question.position)', "
136 
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " = '\(question.addedOn)', "
137 
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON + " = '\(question.updatedOn)' "
138 
        query = query + " WHERE "
139 
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(question.uuid)' ;"
140 
        var statement : OpaquePointer? = nil
141 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
142 
            if (sqlite3_step(statement) != SQLITE_DONE) {
143 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_QUESTION)")
144 
            }
145 
        } else {
146 
            print("No se pudo preparar el actualizar un registro en la tabla: \(Constants.TABLE_QUESTION)")
147 
        }
148 
        sqlite3_finalize(statement)
149 
    }
150 
 
151 
    func remove(uuid: String) {
152 
        let query = "DELETE FROM " + Constants.TABLE_QUESTION +
153 
            " WHERE " + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(uuid)' ;"
154 
        var statement : OpaquePointer? = nil
155 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
156 
            if sqlite3_step(statement) != SQLITE_DONE {
157 
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_QUESTION)")
158 
            }
159 
        } else {
160 
            print("No se pudo prepara el borrar de un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_QUESTION)" )
161 
        }
162 
        sqlite3_finalize(statement)
163 
    }
164 
 
165 
    func removeAll() {
166 
        let query = "DELETE FROM " + Constants.TABLE_QUESTION + ";"
167 
        var statement : OpaquePointer? = nil
168 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
169 
            if sqlite3_step(statement) != SQLITE_DONE {
170 
                print("No se pudo borrar todos los registros de la tabla: \(Constants.TABLE_QUESTION)")
171 
            }
172 
        } else {
173 
            print("No se pudo preparar el borrar todos los registros de la tabla: \(Constants.TABLE_QUESTION)")
174 
        }
175 
        sqlite3_finalize(statement)
176 
    }
177 
 
178 
}