Rev 15 | Autoría | Comparar con el anterior | Ultima modificación | Ver Log |
//
// Database.swift
// twogetskills
//
// Created by Efrain Yanez Recanatini on 2/21/22.
//
import Foundation
import SQLite3
class Database : ObservableObject {
@Published var db : OpaquePointer? = nil
static let sharedInstance: Database = {
let instance = Database()
return instance
}()
public func createTables()
{
if(self.db != nil) {
self.createTableApp()
self.createTableSync()
self.createTableProgress()
self.createTableUserLog()
self.createTableCompany()
self.createTableTopic()
self.createTableCapsule()
self.createTableSlide()
self.createTableQuiz()
self.createTableQuestion()
self.createTableAnswer()
self.createTableUserExtended()
self.createTableUserNotification()
}
}
public func open() -> OpaquePointer? {
if self.db == nil {
let filePath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
.appendingPathComponent(Config.DATABASE_FILENAME)
if sqlite3_open(filePath.path, &self.db) == SQLITE_OK {
self.createTables();
print("Se abrio la conexión con la db : \(filePath.absoluteString) ")
return self.db
} else {
print("No se pudo abrir la db : \(filePath.absoluteString) ")
return nil
}
} else {
return self.db
}
}
public func close() -> Void
{
if self.db == nil {
print("No se pudo cerrar la conexión, no es un recurso valida")
return
}
if sqlite3_close(db) == SQLITE_OK {
print("Se cerro la conexión de la base de datos")
self.db = nil
} else {
print("No se pudo cerrar la conexión de la base de datos")
}
}
private func executeMultiQuery(queries: [String]) -> Void {
for query in queries {
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) != SQLITE_DONE {
print("Fallo la consulta: \(query) ")
}
} else {
print("Fallo la preparación: \(query) ")
}
}
}
private func createTableApp() -> Void
{
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_APP + " ("
sql = sql + Constants.TABLE_APP_FIELD_ID + " INTEGER NOT NULL, "
sql = sql + Constants.TABLE_APP_FIELD_DEVICE_UUID + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_DEVICE_AES + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_DEVICE_PASSWORD + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_USER_UUID + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_USER_FIRSTNAME + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_USER_LASTNAME + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_USER_EMAIL + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_USER_IMAGE + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_FCM_TOKEN + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_TOPIC_UUID_ACTIVE + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_CAPSULE_UUID_ACTIVE + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_CAPSULE_UUID_OLD + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_SLIDE_UUID_ACTIVE + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_COMPANY_UUID_ACTIVE + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_MAX_DATE_CHANGES + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_LAST_CHECK_CHANGES + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_URL_EXTERNAL_DOWNLOADED + " TEXT, "
sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_MESSAGE + " INTEGER, "
sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_MESSAGE_SHOW_PENDING + " INTEGER, "
sql = sql + Constants.TABLE_APP_FIELD_REFRESH_CONTENT_ACTION_REQUIRED + " INTEGER, "
sql = sql + Constants.TABLE_APP_FIELD_SIGNOUT_ACTION_REQUIRED + " INTEGER, "
sql = sql + " PRIMARY KEY(`" + Constants.TABLE_APP_FIELD_ID + "`) "
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_app_id` ON `" + Constants.TABLE_APP + "` (`" + Constants.TABLE_APP_FIELD_ID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_app_user_uuid` ON `" + Constants.TABLE_APP + "` (`" + Constants.TABLE_APP_FIELD_USER_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_app_device_uuid` ON `" + Constants.TABLE_APP + "` (`" + Constants.TABLE_APP_FIELD_DEVICE_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_APP)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_APP)")
}
}
private func createTableUserNotification() -> Void
{
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_NOTIFICATION + "("
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " INTEGER NOT NULL, "
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + " TEXT,"
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + " TEXT,"
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " TEXT, "
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " INTEGER, "
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " TEXT, "
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " TEXT, "
sql = sql + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON + " TEXT, "
sql = sql + " PRIMARY KEY(`" + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "`) "
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_user_unotification_id` ON `" + Constants.TABLE_USER_NOTIFICATION + "` (`" + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_user_notification_user_uuid` ON `" + Constants.TABLE_USER_NOTIFICATION + "` (`" + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
}
}
private func createTableUserExtended() -> Void
{
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_EXTENDED + "("
sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " TEXT,"
sql = sql + Constants.TABLE_USER_EXTENDED_FIELD_VALUE + " TEXT"
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_user_extended_company_uuid` ON `" + Constants.TABLE_USER_EXTENDED + "` (`" + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_USER_EXTENDED)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_EXTENDED)")
}
}
private func createTableSync() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_SYNC + "("
sql = sql + Constants.TABLE_SYNC_FIELD_ID + " INTEGER PRIMARY KEY, "
sql = sql + Constants.TABLE_SYNC_FIELD_TYPE + " INTEGER,"
sql = sql + Constants.TABLE_SYNC_FIELD_DATA + " TEXT"
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) != SQLITE_DONE {
print("No se pudo crear la tabla: \(Constants.TABLE_SYNC)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SYNC)")
}
}
private func createTableCompany() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_COMPANY + "("
sql = sql + Constants.TABLE_COMPANY_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_COMPANY_FIELD_NAME + " TEXT, "
sql = sql + Constants.TABLE_COMPANY_FIELD_IMAGE + " TEXT "
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_company_company_uuid` ON `" + Constants.TABLE_COMPANY + "` (`" + Constants.TABLE_COMPANY_FIELD_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_COMPANY)");
}
} else {
print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_COMPANY)")
}
}
private func createTableTopic() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_TOPIC + "("
sql = sql + Constants.TABLE_TOPIC_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + " TEXT,"
sql = sql + Constants.TABLE_TOPIC_FIELD_NAME + " TEXT,"
sql = sql + Constants.TABLE_TOPIC_FIELD_DESCRIPTION + " TEXT,"
sql = sql + Constants.TABLE_TOPIC_FIELD_IMAGE + " TEXT,"
sql = sql + Constants.TABLE_TOPIC_FIELD_POSITION + " INTEGER, "
sql = sql + Constants.TABLE_TOPIC_FIELD_ADDED_ON + " TEXT,"
sql = sql + Constants.TABLE_TOPIC_FIELD_UPDATED_ON + " TEXT "
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_topics_company_uuid` ON `" + Constants.TABLE_TOPIC + "` (`" + Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_TOPIC)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_TOPIC)")
}
}
private func createTableCapsule() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_CAPSULE + "("
sql = sql + Constants.TABLE_CAPSULE_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " TEXT,"
sql = sql + Constants.TABLE_CAPSULE_FIELD_NAME + " TEXT,"
sql = sql + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " TEXT,"
sql = sql + Constants.TABLE_CAPSULE_FIELD_IMAGE + " TEXT,"
sql = sql + Constants.TABLE_CAPSULE_FIELD_POSITION + " INTEGER, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " TEXT, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " TEXT, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " INTEGER, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " INTEGER, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " TEXT, "
sql = sql + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON + " TEXT "
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_capsules_topic_uuid` ON `" + Constants.TABLE_CAPSULE + "` (`" + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_CAPSULE)")
}
} else {
print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_CAPSULE)")
}
}
private func createTableSlide() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_SLIDE + " ("
sql = sql + Constants.TABLE_SLIDE_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_NAME + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_DESCRIPTION + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_TYPE + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_FILE + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_BACKGROUND + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_POSITION + " INTEGER, "
sql = sql + Constants.TABLE_SLIDE_FIELD_ADDED_ON + " TEXT,"
sql = sql + Constants.TABLE_SLIDE_FIELD_UPDATED_ON + " TEXT);"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_slides_quiz_uuid` ON `" + Constants.TABLE_SLIDE + "` (`" + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_slides_capsule_uuid` ON `" + Constants.TABLE_SLIDE + "` (`" + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_slides_topic_uuid` ON `" + Constants.TABLE_SLIDE + "` (`" + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_SLIDE)")
}
} else {
print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SLIDE)")
}
}
private func createTableProgress() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_PROGRESS + " ("
sql = sql + Constants.TABLE_PROGRESS_FIELD_ID + " INTEGER PRIMARY KEY, "
sql = sql + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_PROGRESS + " REAL,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " INTEGER,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " INTEGER,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_TYPE + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_RETURNING + " INTEGER,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " INTEGER,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_COMPLETED + " INTEGER,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " TEXT,"
sql = sql + Constants.TABLE_PROGRESS_FIELD_UPDATED_ON + " TEXT);"
print("sql : \(sql)")
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_progress_topic_uuid` ON `" + Constants.TABLE_PROGRESS + "` (`" + Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_progress_capsule_uuid` ON `" + Constants.TABLE_PROGRESS + "` (`" + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_progress_slide_uuid` ON `" + Constants.TABLE_PROGRESS + "` (`" + Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_progress_user_uuid` ON `" + Constants.TABLE_PROGRESS + "` (`" + Constants.TABLE_PROGRESS_FIELD_USER_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_PROGRESS)")
print(sql)
}
} else {
print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_PROGRESS)")
}
}
private func createTableUserLog() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_LOG + "("
sql = sql + Constants.TABLE_USER_LOG_FIELD_ID + " INTEGER PRIMARY KEY, "
sql = sql + Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " TEXT,"
sql = sql + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " TEXT"
sql = sql + ");"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_user_log_user_uuid` ON `" + Constants.TABLE_USER_LOG + "` (`" + Constants.TABLE_USER_LOG_FIELD_USER_UUID + "` );",
"CREATE INDEX IF NOT EXISTS `idx_user_log_added_on` ON `" + Constants.TABLE_USER_LOG + "` (`" + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_USER_LOG)")
}
} else {
print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_USER_LOG)")
}
}
private func createTableQuiz() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_QUIZ + "("
sql = sql + Constants.TABLE_QUIZ_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + " TEXT,"
sql = sql + Constants.TABLE_QUIZ_FIELD_NAME + " TEXT,"
sql = sql + Constants.TABLE_QUIZ_FIELD_TEXT + " TEXT,"
sql = sql + Constants.TABLE_QUIZ_FIELD_FAILED + " TEXT,"
sql = sql + Constants.TABLE_QUIZ_FIELD_POINTS + " INTEGER,"
sql = sql + Constants.TABLE_QUIZ_FIELD_MINIMUM_POINTS_REQUIRED + " INTEGER, "
sql = sql + Constants.TABLE_QUIZ_FIELD_MAX_TIME + " INTEGER, "
sql = sql + Constants.TABLE_QUIZ_FIELD_ADDED_ON + " TEXT,"
sql = sql + Constants.TABLE_QUIZ_FIELD_UPDATED_ON + " TEXT);"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_quizzes_company_uuid` ON `" + Constants.TABLE_QUIZ + "` (`" + Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_QUIZ)")
}
} else {
print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_QUIZ)")
}
}
private func createTableQuestion() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_QUESTION + "("
sql = sql + Constants.TABLE_QUESTION_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " TEXT,"
sql = sql + Constants.TABLE_QUESTION_FIELD_TEXT + " TEXT,"
sql = sql + Constants.TABLE_QUESTION_FIELD_TYPE + " TEXT,"
sql = sql + Constants.TABLE_QUESTION_FIELD_POINTS + " INTEGER,"
sql = sql + Constants.TABLE_QUESTION_FIELD_POSITION + " INTEGER, "
sql = sql + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " TEXT,"
sql = sql + Constants.TABLE_QUESTION_FIELD_UPDATED_ON + " TEXT);"
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_questions_quiz_uuid` ON " + Constants.TABLE_QUIZ + "` (`" + Constants.TABLE_QUIZ_FIELD_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_QUESTION)")
}
} else {
print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_QUESTION)")
}
}
private func createTableAnswer() -> Void {
var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_ANSWER + "("
sql = sql + Constants.TABLE_ANSWER_FIELD_UUID + " TEXT UNIQUE, "
sql = sql + Constants.TABLE_ANSWER_FIELD_QUESTION_UUID + " TEXT,"
sql = sql + Constants.TABLE_ANSWER_FIELD_TEXT + " TEXT,"
sql = sql + Constants.TABLE_ANSWER_FIELD_POINTS + " INTEGER,"
sql = sql + Constants.TABLE_ANSWER_FIELD_CORRECT + " TEXT, "
sql = sql + Constants.TABLE_ANSWER_FIELD_ADDED_ON + " TEXT, "
sql = sql + Constants.TABLE_ANSWER_FIELD_UPDATED_ON + " TEXT); "
var statement : OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
let queries = [
"CREATE INDEX IF NOT EXISTS `idx_answers_question_uuid` ON `" + Constants.TABLE_QUESTION + "` (`" + Constants.TABLE_QUESTION_FIELD_UUID + "` );"
]
executeMultiQuery(queries: queries)
} else {
print("No se pudo crear la tabla: \(Constants.TABLE_ANSWER)")
}
} else {
print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_ANSWER)")
}
}
}