Proyectos de Subversion Iphone Microlearning

//
//  Database.swift
//  twogetskills
//
//  Created by Efrain Yanez Recanatini on 2/21/22.
//

import Foundation
import SQLite3

class Database {

    private var db : OpaquePointer?
    private var path : String = Constants.DATABASE_NAME
    
    public func createTables()
    {
        self.db = open()
        if(self.db != nil) {
            self.createTableSync()
            self.createTableProgress()
            self.createTableUserLog()
            self.createTableCompany()
            self.createTableTopic()
            self.createTableCapsule()
            self.createTableSlide()
            self.createTableQuiz()
            self.createTableQuestion()
            self.createTableAnswer()
            self.createTableUserExtended()
        }
    }

    func open() -> OpaquePointer? {
        
        if db == nil {
        
            let filePath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
                .appendingPathComponent(path)
            var db: OpaquePointer? = nil
            
            print("Database FilePath : \(filePath.path)")
            
            if sqlite3_open(filePath.path, &db) != SQLITE_OK {
                print("No se pudo abrir la DB : \(path) ")
                return nil
            } else {
                return db
            }
        } else {
            return db
        }
        
    }
    
    func deleteTables() {
        let queries = [
            "DELETE FROM " + Constants.TABLE_PROGRESS + ";",
            "DELETE FROM " + Constants.TABLE_USER_LOG + ";"
        ]
        
        queries.forEach { query in
            var statement : OpaquePointer? = nil
            
            if sqlite3_prepare_v2(self.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 createTableUserExtended()
    {
        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 + ");"
        let sql1 = "CREATE INDEX `idx_user_extended_company_uuid` ON `" +  Constants.TABLE_USER_EXTENDED + "` (`" +  Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + "` );"
        
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, sql, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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(self.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()  {
        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(self.db, sql, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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 + ");"
        let sql1 = "CREATE INDEX `idx_topics_company_uuid` ON `" +  Constants.TABLE_TOPIC + "` (`" +  Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + "` );"
        let query = sql + sql1
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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 + ");"
        let sql1 = "CREATE INDEX `idx_capsules_topic_uuid` ON `" +  Constants.TABLE_CAPSULE + "` (`" +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + "` );"
        let query = sql + sql1
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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 );"
        
        let sql1 = "CREATE INDEX `idx_slides_quiz_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + "` );"
        let sql2 = "CREATE INDEX `idx_slides_capsule_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + "` );"
        let sql3 = "CREATE INDEX `idx_slides_topic_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + "` );"
        let query = sql + sql1 + sql2 + sql3
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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);"
        let sql1 = "CREATE INDEX `idx_progress_topic_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + "` );"
        let sql2 = "CREATE INDEX `idx_progress_capsule_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + "` );"
        let sql3 = "CREATE INDEX `idx_progress_slide_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + "` );"
        let sql4 = "CREATE INDEX `idx_progress_user_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "` );"
        let query = sql + sql1 + sql2 + sql3 + sql4
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                print("No se pudo crear la tabla: \(Constants.TABLE_PROGRESS)")
           }
        } else {
            print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_PROGRESS)")
        }
    }
    
    private func createTableUserLog()  {
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_USER_LOG + "("
        sql = sql + Constants.TABLE_USER_LOG_FIELD_ID + " IINTEGER 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 + ");"
        
        let sql1 = "CREATE INDEX `idx_user_log_added_on` ON `" +  Constants.TABLE_USER_LOG + "` (`" +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON + "` );"
        let query = sql + sql1
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_QUIZ + "("
        sql = sql + Constants.TABLE_QUIZ_FIELD_UUID + " INTEGER UNIQUE, "
        sql = sql + Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + " INTEGER,"
        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);"
        
        let sql1 = "CREATE INDEX `idx_quizzes_company_uuid` ON `" +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + "` );"
        let query = sql + sql1
        
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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 );"
        
        let sql1 = "CREATE INDEX `idx_questions_quiz_uuid` ON " +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_UUID + "` );"
        let query = sql + sql1
        
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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()  {
        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); "
        
        let sql1 = "CREATE INDEX `idx_answers_question_uuid` ON `" +  Constants.TABLE_QUESTION + "` (`" +  Constants.TABLE_QUESTION_FIELD_UUID + "` );"
        let query = sql + sql1
        var statement : OpaquePointer? = nil
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                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)")
        }
    }
    
    
    
    
}