Proyectos de Subversion Iphone Microlearning - Inconcert

Rev

Rev 15 | | Comparar con el anterior | Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 1 
//
2 
//  Database.swift
3 
//  twogetskills
4 
//
5 
//  Created by Efrain Yanez Recanatini on 2/21/22.
6 
//
7 
 
8 
import Foundation
9 
import SQLite3
10 
 
17 efrain 11 
class Database : ObservableObject {
1 efrain 12 
 
15 efrain 13 
 
17 efrain 14 
 
15 
    @Published var db : OpaquePointer? = nil
1 efrain 16 
 
17 efrain 17 
 
1 efrain 18 
    static let sharedInstance: Database = {
19 
           let instance = Database()
20 
 
21 
           return instance
22 
    }()
23 
 
24 
 
25 
    public func createTables()
26 
    {
15 efrain 27 
        if(self.db != nil) {
1 efrain 28 
            self.createTableApp()
29 
            self.createTableSync()
30 
            self.createTableProgress()
31 
            self.createTableUserLog()
32 
            self.createTableCompany()
33 
            self.createTableTopic()
34 
            self.createTableCapsule()
35 
            self.createTableSlide()
36 
            self.createTableQuiz()
37 
            self.createTableQuestion()
38 
            self.createTableAnswer()
39 
            self.createTableUserExtended()
40 
            self.createTableUserNotification()
41 
        }
42 
    }
43 
 
44 
    public func open() -> OpaquePointer? {
15 efrain 45 
        if self.db == nil {
17 efrain 46 
 
1 efrain 47 
            let filePath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
17 efrain 48 
                .appendingPathComponent(Config.DATABASE_FILENAME)
1 efrain 49 
 
15 efrain 50 
            if sqlite3_open(filePath.path, &self.db) == SQLITE_OK {
51 
 
17 efrain 52 
                self.createTables();
53 
 
54 
                print("Se abrio la conexión con la db : \(filePath.absoluteString) ")
55 
                return self.db
56 
 
15 efrain 57 
            } else {
17 efrain 58 
                print("No se pudo abrir la db : \(filePath.absoluteString) ")
1 efrain 59 
                return nil
60 
            }
17 efrain 61 
        } else {
62 
            return self.db
1 efrain 63 
        }
15 efrain 64 
 
17 efrain 65 
 
1 efrain 66 
 
67 
    }
68 
 
15 efrain 69 
    public func close() -> Void
70 
    {
71 
        if self.db == nil {
72 
            print("No se pudo cerrar la conexión, no es un recurso valida")
73 
            return
74 
        }
75 
 
76 
 
77 
        if sqlite3_close(db) == SQLITE_OK {
78 
            print("Se cerro la conexión de la base de datos")
79 
            self.db = nil
80 
        } else {
81 
            print("No se pudo cerrar la conexión de la base de datos")
82 
        }
83 
    }
1 efrain 84 
 
85 
 
15 efrain 86 
 
17 efrain 87 
    private func executeMultiQuery(queries: [String]) -> Void {
88 
 
1 efrain 89 
 
17 efrain 90 
        for query  in queries {
1 efrain 91 
            var statement : OpaquePointer? = nil
92 
 
15 efrain 93 
            if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
17 efrain 94 
                if sqlite3_step(statement) != SQLITE_DONE {
1 efrain 95 
                    print("Fallo la consulta: \(query) ")
96 
                }
97 
            } else {
98 
                print("Fallo la preparación: \(query) ")
99 
            }
100 
        }
101 
    }
102 
 
103 
 
15 efrain 104 
    private func createTableApp() -> Void
1 efrain 105 
    {
106 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_APP + " ("
107 
        sql = sql + Constants.TABLE_APP_FIELD_ID + " INTEGER NOT NULL, "
108 
        sql = sql + Constants.TABLE_APP_FIELD_DEVICE_UUID + " TEXT, "
109 
        sql = sql + Constants.TABLE_APP_FIELD_DEVICE_AES + " TEXT, "
110 
        sql = sql + Constants.TABLE_APP_FIELD_DEVICE_PASSWORD + " TEXT, "
111 
        sql = sql + Constants.TABLE_APP_FIELD_USER_UUID + " TEXT, "
112 
        sql = sql + Constants.TABLE_APP_FIELD_USER_FIRSTNAME + " TEXT, "
113 
        sql = sql + Constants.TABLE_APP_FIELD_USER_LASTNAME + " TEXT, "
114 
        sql = sql + Constants.TABLE_APP_FIELD_USER_EMAIL + " TEXT, "
115 
        sql = sql + Constants.TABLE_APP_FIELD_USER_IMAGE + " TEXT, "
116 
        sql = sql + Constants.TABLE_APP_FIELD_FCM_TOKEN + " TEXT, "
117 
        sql = sql + Constants.TABLE_APP_FIELD_TOPIC_UUID_ACTIVE + " TEXT, "
118 
        sql = sql + Constants.TABLE_APP_FIELD_CAPSULE_UUID_ACTIVE + " TEXT, "
119 
        sql = sql + Constants.TABLE_APP_FIELD_CAPSULE_UUID_OLD + " TEXT, "
120 
        sql = sql + Constants.TABLE_APP_FIELD_SLIDE_UUID_ACTIVE + " TEXT, "
121 
        sql = sql + Constants.TABLE_APP_FIELD_COMPANY_UUID_ACTIVE + " TEXT, "
122 
        sql = sql + Constants.TABLE_APP_FIELD_MAX_DATE_CHANGES + " TEXT, "
123 
        sql = sql + Constants.TABLE_APP_FIELD_LAST_CHECK_CHANGES + " TEXT, "
124 
        sql = sql + Constants.TABLE_APP_FIELD_URL_EXTERNAL_DOWNLOADED + " TEXT, "
125 
        sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_MESSAGE + " INTEGER, "
126 
        sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_MESSAGE_SHOW_PENDING + " INTEGER, "
127 
        sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_ACTION_REQUIRED + " INTEGER, "
128 
        sql = sql + Constants.TABLE_APP_FIELD_SIGNOUT_ACTION_REQUIRED + " INTEGER, "
129 
        sql = sql + " PRIMARY KEY(`" +  Constants.TABLE_APP_FIELD_ID + "`) "
130 
        sql = sql + ");"
131 
 
132 
 
133 
 
134 
 
135 
        var statement : OpaquePointer? = nil
136 
 
15 efrain 137 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 138 
            if sqlite3_step(statement) == SQLITE_DONE {
139 
 
140 
                let queries = [
14 efrain 141 
                    "CREATE INDEX IF NOT EXISTS `idx_app_id` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_ID + "` );",
1 efrain 142 
 
14 efrain 143 
                    "CREATE INDEX IF NOT EXISTS `idx_app_user_uuid` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_USER_UUID + "` );",
1 efrain 144 
 
14 efrain 145 
                    "CREATE INDEX IF NOT EXISTS `idx_app_device_uuid` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_DEVICE_UUID + "` );"
1 efrain 146 
                ]
147 
 
148 
                executeMultiQuery(queries: queries)
149 
 
150 
 
151 
            } else {
152 
                print("No se pudo crear la tabla: \(Constants.TABLE_APP)")
153 
 
15 efrain 154 
 
1 efrain 155 
            }
156 
        } else {
157 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_APP)")
158 
 
159 
 
15 efrain 160 
 
1 efrain 161 
        }
162 
    }
163 
 
15 efrain 164 
    private func createTableUserNotification() -> Void
1 efrain 165 
    {
166 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_NOTIFICATION + "("
167 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " INTEGER NOT NULL, "
168 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " TEXT,"
169 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + " TEXT,"
170 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + " TEXT,"
171 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " TEXT, "
172 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " INTEGER, "
173 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " TEXT, "
174 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " TEXT, "
175 
        sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON + " TEXT, "
176 
        sql = sql + " PRIMARY KEY(`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "`) "
177 
        sql = sql + ");"
178 
 
179 
 
180 
 
181 
        var statement : OpaquePointer? = nil
182 
 
15 efrain 183 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 184 
            if sqlite3_step(statement) == SQLITE_DONE {
185 
 
186 
                let queries = [
14 efrain 187 
                    "CREATE INDEX IF NOT EXISTS `idx_user_unotification_id` ON `" +  Constants.TABLE_USER_NOTIFICATION + "` (`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "` );",
1 efrain 188 
 
14 efrain 189 
                    "CREATE INDEX IF NOT EXISTS `idx_user_notification_user_uuid` ON `" +  Constants.TABLE_USER_NOTIFICATION + "` (`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + "` );"
1 efrain 190 
                ]
191 
 
192 
                executeMultiQuery(queries: queries)
193 
 
194 
            } else {
195 
                print("No se pudo crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
196 
 
15 efrain 197 
 
1 efrain 198 
            }
199 
        } else {
200 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
201 
 
15 efrain 202 
 
1 efrain 203 
        }
204 
    }
205 
 
15 efrain 206 
    private func createTableUserExtended() -> Void
1 efrain 207 
    {
208 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_EXTENDED + "("
209 
        sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_UUID + " TEXT UNIQUE, "
210 
        sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " TEXT,"
211 
        sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " TEXT,"
212 
        sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_VALUE + " TEXT"
213 
        sql = sql + ");"
214 
 
215 
        var statement : OpaquePointer? = nil
216 
 
15 efrain 217 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 218 
            if sqlite3_step(statement) == SQLITE_DONE {
219 
 
220 
                let queries = [
14 efrain 221 
                    "CREATE INDEX IF NOT EXISTS  `idx_user_extended_company_uuid` ON `" +  Constants.TABLE_USER_EXTENDED + "` (`" +  Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + "` );"
1 efrain 222 
                ]
223 
 
224 
                executeMultiQuery(queries: queries)
225 
 
226 
            } else {
227 
                print("No se pudo crear la tabla: \(Constants.TABLE_USER_EXTENDED)")
228 
 
15 efrain 229 
 
1 efrain 230 
            }
231 
        } else {
232 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_EXTENDED)")
233 
 
234 
 
15 efrain 235 
 
1 efrain 236 
        }
237 
    }
238 
 
15 efrain 239 
    private func createTableSync() -> Void {
1 efrain 240 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_SYNC + "("
241 
        sql = sql + Constants.TABLE_SYNC_FIELD_ID +  " INTEGER PRIMARY KEY, "
242 
        sql = sql + Constants.TABLE_SYNC_FIELD_TYPE + " INTEGER,"
243 
        sql = sql + Constants.TABLE_SYNC_FIELD_DATA + " TEXT"
244 
        sql = sql + ");"
245 
 
246 
        var statement : OpaquePointer? = nil
247 
 
15 efrain 248 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 249 
            if sqlite3_step(statement) != SQLITE_DONE {
250 
                print("No se pudo crear la tabla: \(Constants.TABLE_SYNC)")
251 
 
15 efrain 252 
 
1 efrain 253 
            }
254 
        } else {
255 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SYNC)")
256 
 
257 
 
15 efrain 258 
 
1 efrain 259 
        }
260 
    }
261 
 
15 efrain 262 
    private func createTableCompany() -> Void  {
1 efrain 263 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_COMPANY + "("
264 
        sql = sql + Constants.TABLE_COMPANY_FIELD_UUID + " TEXT UNIQUE, "
265 
        sql = sql + Constants.TABLE_COMPANY_FIELD_NAME + " TEXT, "
266 
        sql = sql + Constants.TABLE_COMPANY_FIELD_IMAGE + " TEXT "
267 
        sql = sql + ");"
268 
        var statement : OpaquePointer? = nil
269 
 
15 efrain 270 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 271 
            if sqlite3_step(statement) == SQLITE_DONE {
272 
 
273 
                let queries = [
14 efrain 274 
                    "CREATE INDEX IF NOT EXISTS `idx_company_company_uuid` ON `" +  Constants.TABLE_COMPANY + "` (`" +  Constants.TABLE_COMPANY_FIELD_UUID + "` );"
1 efrain 275 
                ]
276 
 
277 
                executeMultiQuery(queries: queries)
278 
 
279 
            } else {
280 
                print("No se pudo crear la tabla: \(Constants.TABLE_COMPANY)");
281 
 
15 efrain 282 
 
1 efrain 283 
            }
284 
        } else {
285 
            print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_COMPANY)")
286 
 
287 
 
15 efrain 288 
 
1 efrain 289 
        }
290 
    }
291 
 
15 efrain 292 
    private func createTableTopic() -> Void  {
1 efrain 293 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_TOPIC + "("
294 
        sql = sql + Constants.TABLE_TOPIC_FIELD_UUID + " TEXT UNIQUE, "
295 
        sql = sql + Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + " TEXT,"
296 
        sql = sql + Constants.TABLE_TOPIC_FIELD_NAME + " TEXT,"
297 
        sql = sql + Constants.TABLE_TOPIC_FIELD_DESCRIPTION + " TEXT,"
298 
        sql = sql + Constants.TABLE_TOPIC_FIELD_IMAGE + " TEXT,"
299 
        sql = sql + Constants.TABLE_TOPIC_FIELD_POSITION + " INTEGER, "
300 
        sql = sql + Constants.TABLE_TOPIC_FIELD_ADDED_ON + " TEXT,"
301 
        sql = sql + Constants.TABLE_TOPIC_FIELD_UPDATED_ON + " TEXT "
302 
        sql = sql + ");"
303 
 
304 
 
305 
        var statement : OpaquePointer? = nil
306 
 
15 efrain 307 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 308 
            if sqlite3_step(statement) == SQLITE_DONE {
309 
 
310 
                let queries = [
14 efrain 311 
                    "CREATE INDEX IF NOT EXISTS  `idx_topics_company_uuid` ON `" +  Constants.TABLE_TOPIC + "` (`" +  Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + "` );"
1 efrain 312 
                ]
313 
 
314 
                executeMultiQuery(queries: queries)
315 
 
316 
            } else {
317 
                print("No se pudo crear la tabla: \(Constants.TABLE_TOPIC)")
318 
 
15 efrain 319 
 
1 efrain 320 
           }
321 
        } else {
322 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_TOPIC)")
323 
 
324 
 
15 efrain 325 
 
1 efrain 326 
        }
327 
    }
328 
 
329 
 
15 efrain 330 
    private func createTableCapsule() -> Void  {
1 efrain 331 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_CAPSULE + "("
332 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_UUID + " TEXT UNIQUE, "
333 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " TEXT,"
334 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_NAME + " TEXT,"
335 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " TEXT,"
336 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_IMAGE + " TEXT,"
337 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_POSITION + " INTEGER, "
338 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " TEXT, "
339 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " TEXT, "
340 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " INTEGER, "
341 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " INTEGER, "
342 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " TEXT, "
343 
        sql = sql + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON  + " TEXT "
344 
        sql = sql + ");"
345 
 
346 
 
347 
        var statement : OpaquePointer? = nil
348 
 
15 efrain 349 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 350 
            if sqlite3_step(statement) == SQLITE_DONE {
351 
 
352 
                let queries = [
14 efrain 353 
                    "CREATE INDEX IF NOT EXISTS `idx_capsules_topic_uuid` ON `" +  Constants.TABLE_CAPSULE + "` (`" +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + "` );"
1 efrain 354 
 
355 
                ]
356 
 
357 
                executeMultiQuery(queries: queries)
358 
 
359 
            } else {
360 
                print("No se pudo crear la tabla: \(Constants.TABLE_CAPSULE)")
361 
 
15 efrain 362 
 
1 efrain 363 
           }
364 
        } else {
365 
            print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_CAPSULE)")
366 
 
15 efrain 367 
 
1 efrain 368 
        }
369 
    }
370 
 
15 efrain 371 
    private func createTableSlide() -> Void {
1 efrain 372 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_SLIDE + " ("
373 
        sql = sql + Constants.TABLE_SLIDE_FIELD_UUID + " TEXT UNIQUE, "
374 
        sql = sql + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + " TEXT,"
375 
        sql = sql + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + " TEXT,"
376 
        sql = sql + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + " TEXT,"
377 
        sql = sql + Constants.TABLE_SLIDE_FIELD_NAME + " TEXT,"
378 
        sql = sql + Constants.TABLE_SLIDE_FIELD_DESCRIPTION + " TEXT,"
379 
        sql = sql + Constants.TABLE_SLIDE_FIELD_TYPE + " TEXT,"
380 
        sql = sql + Constants.TABLE_SLIDE_FIELD_FILE + " TEXT,"
381 
        sql = sql + Constants.TABLE_SLIDE_FIELD_BACKGROUND + " TEXT,"
382 
        sql = sql + Constants.TABLE_SLIDE_FIELD_POSITION + " INTEGER, "
383 
        sql = sql + Constants.TABLE_SLIDE_FIELD_ADDED_ON + " TEXT,"
384 
        sql = sql + Constants.TABLE_SLIDE_FIELD_UPDATED_ON + " TEXT);"
385 
 
386 
 
387 
 
388 
        var statement : OpaquePointer? = nil
389 
 
15 efrain 390 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 391 
            if sqlite3_step(statement) == SQLITE_DONE {
392 
 
393 
                let queries = [
14 efrain 394 
                    "CREATE INDEX IF NOT EXISTS `idx_slides_quiz_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + "` );",
1 efrain 395 
 
14 efrain 396 
                    "CREATE INDEX IF NOT EXISTS  `idx_slides_capsule_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + "` );",
1 efrain 397 
 
14 efrain 398 
                    "CREATE INDEX IF NOT EXISTS  `idx_slides_topic_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + "` );"
1 efrain 399 
 
400 
                ]
401 
 
402 
                executeMultiQuery(queries: queries)
403 
 
404 
            } else {
405 
                print("No se pudo crear la tabla: \(Constants.TABLE_SLIDE)")
406 
 
15 efrain 407 
 
1 efrain 408 
           }
409 
        } else {
410 
            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SLIDE)")
411 
 
15 efrain 412 
 
1 efrain 413 
        }
414 
    }
415 
 
416 
 
15 efrain 417 
    private func createTableProgress() -> Void  {
1 efrain 418 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_PROGRESS + " ("
419 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_ID + " INTEGER PRIMARY KEY, "
420 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " TEXT,"
421 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " TEXT,"
422 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " TEXT,"
423 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " TEXT,"
424 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " TEXT,"
425 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_PROGRESS + " REAL,"
426 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " INTEGER,"
427 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " INTEGER,"
428 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_TYPE + " TEXT,"
429 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_RETURNING  + " INTEGER,"
430 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED  + " INTEGER,"
431 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_COMPLETED  + " INTEGER,"
432 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_ADDED_ON  + " TEXT,"
433 
        sql = sql + Constants.TABLE_PROGRESS_FIELD_UPDATED_ON  + " TEXT);"
434 
 
435 
 
436 
        print("sql : \(sql)")
437 
 
438 
 
439 
        var statement : OpaquePointer? = nil
440 
 
15 efrain 441 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 442 
            if sqlite3_step(statement) == SQLITE_DONE {
443 
 
444 
                let queries = [
14 efrain 445 
                    "CREATE INDEX IF NOT EXISTS `idx_progress_topic_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + "` );",
1 efrain 446 
 
14 efrain 447 
                    "CREATE INDEX IF NOT EXISTS  `idx_progress_capsule_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + "` );",
1 efrain 448 
 
14 efrain 449 
                    "CREATE INDEX IF NOT EXISTS  `idx_progress_slide_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + "` );",
1 efrain 450 
 
14 efrain 451 
                    "CREATE INDEX IF NOT EXISTS  `idx_progress_user_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "` );"
1 efrain 452 
                ]
453 
 
454 
                executeMultiQuery(queries: queries)
455 
 
456 
            } else {
457 
                print("No se pudo crear la tabla: \(Constants.TABLE_PROGRESS)")
458 
                print(sql)
15 efrain 459 
 
1 efrain 460 
           }
461 
        } else {
462 
            print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_PROGRESS)")
463 
 
15 efrain 464 
 
1 efrain 465 
        }
466 
    }
467 
 
15 efrain 468 
    private func createTableUserLog() -> Void  {
1 efrain 469 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_LOG + "("
470 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_ID + " INTEGER PRIMARY KEY, "
471 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " TEXT,"
472 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " TEXT,"
473 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " TEXT,"
474 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " TEXT,"
475 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " TEXT,"
476 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " TEXT,"
477 
        sql = sql + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " TEXT"
478 
        sql = sql + ");"
479 
 
480 
 
481 
        var statement : OpaquePointer? = nil
482 
 
15 efrain 483 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 484 
            if sqlite3_step(statement) == SQLITE_DONE {
485 
 
486 
                let queries = [
14 efrain 487 
                    "CREATE INDEX IF NOT EXISTS `idx_user_log_user_uuid` ON `" +  Constants.TABLE_USER_LOG + "` (`" +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + "` );",
1 efrain 488 
 
14 efrain 489 
                    "CREATE INDEX IF NOT EXISTS `idx_user_log_added_on` ON `" +  Constants.TABLE_USER_LOG + "` (`" +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON + "` );"
1 efrain 490 
                ]
491 
 
492 
                executeMultiQuery(queries: queries)
493 
 
494 
            } else {
495 
                print("No se pudo crear la tabla: \(Constants.TABLE_USER_LOG)")
496 
 
15 efrain 497 
 
1 efrain 498 
           }
499 
        } else {
500 
            print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_USER_LOG)")
501 
 
15 efrain 502 
 
1 efrain 503 
        }
504 
    }
505 
 
506 
 
507 
 
15 efrain 508 
    private func createTableQuiz() -> Void  {
1 efrain 509 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_QUIZ + "("
510 
        sql = sql + Constants.TABLE_QUIZ_FIELD_UUID + " TEXT UNIQUE, "
511 
        sql = sql + Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + " TEXT,"
512 
        sql = sql + Constants.TABLE_QUIZ_FIELD_NAME + " TEXT,"
513 
        sql = sql + Constants.TABLE_QUIZ_FIELD_TEXT + " TEXT,"
514 
        sql = sql + Constants.TABLE_QUIZ_FIELD_FAILED + " TEXT,"
515 
        sql = sql + Constants.TABLE_QUIZ_FIELD_POINTS + " INTEGER,"
516 
        sql = sql + Constants.TABLE_QUIZ_FIELD_MINIMUM_POINTS_REQUIRED + " INTEGER, "
517 
        sql = sql + Constants.TABLE_QUIZ_FIELD_MAX_TIME + " INTEGER, "
518 
        sql = sql + Constants.TABLE_QUIZ_FIELD_ADDED_ON + " TEXT,"
519 
        sql = sql + Constants.TABLE_QUIZ_FIELD_UPDATED_ON + " TEXT);"
520 
 
521 
 
522 
        var statement : OpaquePointer? = nil
523 
 
15 efrain 524 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 525 
            if sqlite3_step(statement) == SQLITE_DONE {
526 
 
527 
                let queries = [
14 efrain 528 
                    "CREATE INDEX IF NOT EXISTS  `idx_quizzes_company_uuid` ON `" +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + "` );"
1 efrain 529 
                ]
530 
 
531 
                executeMultiQuery(queries: queries)
532 
 
533 
            } else {
534 
                print("No se pudo crear la tabla: \(Constants.TABLE_QUIZ)")
535 
 
15 efrain 536 
 
1 efrain 537 
           }
538 
        } else {
539 
            print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_QUIZ)")
540 
 
15 efrain 541 
 
1 efrain 542 
        }
543 
 
544 
    }
545 
 
15 efrain 546 
    private func createTableQuestion() -> Void  {
1 efrain 547 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_QUESTION + "("
548 
        sql = sql + Constants.TABLE_QUESTION_FIELD_UUID + " TEXT UNIQUE, "
549 
        sql = sql + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " TEXT,"
550 
        sql = sql + Constants.TABLE_QUESTION_FIELD_TEXT + " TEXT,"
551 
        sql = sql + Constants.TABLE_QUESTION_FIELD_TYPE + " TEXT,"
552 
        sql = sql + Constants.TABLE_QUESTION_FIELD_POINTS + " INTEGER,"
553 
        sql = sql + Constants.TABLE_QUESTION_FIELD_POSITION + " INTEGER, "
554 
        sql = sql + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " TEXT,"
555 
        sql = sql + Constants.TABLE_QUESTION_FIELD_UPDATED_ON + " TEXT);"
556 
 
557 
        var statement : OpaquePointer? = nil
558 
 
15 efrain 559 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 560 
            if sqlite3_step(statement) == SQLITE_DONE {
561 
 
562 
                let queries = [
14 efrain 563 
                    "CREATE INDEX IF NOT EXISTS `idx_questions_quiz_uuid` ON " +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_UUID + "` );"
1 efrain 564 
                ]
565 
 
566 
                executeMultiQuery(queries: queries)
567 
            } else {
568 
                print("No se pudo crear la tabla: \(Constants.TABLE_QUESTION)")
569 
 
15 efrain 570 
 
1 efrain 571 
           }
572 
        } else {
573 
            print("Fallo la preparación de la creación  de la tabla: \(Constants.TABLE_QUESTION)")
574 
 
15 efrain 575 
 
1 efrain 576 
        }
577 
    }
578 
 
15 efrain 579 
    private func createTableAnswer() -> Void {
1 efrain 580 
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_ANSWER + "("
581 
        sql = sql + Constants.TABLE_ANSWER_FIELD_UUID + " TEXT UNIQUE, "
582 
        sql = sql + Constants.TABLE_ANSWER_FIELD_QUESTION_UUID + " TEXT,"
583 
        sql = sql + Constants.TABLE_ANSWER_FIELD_TEXT + " TEXT,"
584 
        sql = sql + Constants.TABLE_ANSWER_FIELD_POINTS + " INTEGER,"
585 
        sql = sql + Constants.TABLE_ANSWER_FIELD_CORRECT + " TEXT, "
586 
        sql = sql + Constants.TABLE_ANSWER_FIELD_ADDED_ON + " TEXT, "
587 
        sql = sql + Constants.TABLE_ANSWER_FIELD_UPDATED_ON + " TEXT); "
588 
 
589 
 
590 
        var statement : OpaquePointer? = nil
591 
 
15 efrain 592 
        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
1 efrain 593 
            if sqlite3_step(statement) == SQLITE_DONE {
594 
                let queries = [
14 efrain 595 
                    "CREATE INDEX IF NOT EXISTS `idx_answers_question_uuid` ON `" +  Constants.TABLE_QUESTION + "` (`" +  Constants.TABLE_QUESTION_FIELD_UUID + "` );"
1 efrain 596 
                ]
597 
 
598 
                executeMultiQuery(queries: queries)
599 
 
600 
            } else {
601 
                print("No se pudo crear la tabla: \(Constants.TABLE_ANSWER)")
602 
 
15 efrain 603 
 
1 efrain 604 
           }
605 
        } else {
606 
            print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_ANSWER)")
607 
 
15 efrain 608 
 
1 efrain 609 
        }
610 
    }
611 
 
612 
 
613 
 
614 
 
615 
}