WebSVN – Iphone Microlearning - Nuevo Interface – Diff – /twogetskills/Database/Database.swift

 //
 import Foundation
 import SQLite3
+class Database : ObservableObject {
-class Database {
-    private var conn : OpaquePointer?
+    @Published var db : OpaquePointer? = nil
-    private var path : String = Constants.DATABASE_NAME
     static let sharedInstance: Database = {
-           let instance = Database()
-           // setup code
-           return instance
-    }()
-    public func printError()
+    static let sharedInstance: Database = {
            let instance = Database()
-    }
            return instance
     }()
     public func createTables()
     public func createTables()
             self.createTableUserNotification()
+        }
+    }
-    public func open() -> OpaquePointer? {
+    public func open() -> OpaquePointer? {
-        if self.conn == nil {
-            let filePath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
+        if self.db == nil {
-                .appendingPathComponent(path)
+            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 {
-            print("Database FilePath : \(filePath.path)")
+                self.createTables();
-            if sqlite3_open(filePath.path, &self.conn) != SQLITE_OK {
                 print("No se pudo abrir la DB : \(path) ")
+                print("Se abrio la conexión con la db : \(filePath.absoluteString) ")
+                return self.db
                 self.printError()
-                return nil
+            } else {
-            } else {
+                print("No se pudo abrir la db : \(filePath.absoluteString) ")
-                return self.conn
+                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")
-        } else {
+            self.db = nil
-            return self.conn
+        } else {
-        }
+            print("No se pudo cerrar la conexión de la base de datos")
+        }
     }
-    func executeMultiQuery(queries: [String]) {
     private func executeMultiQuery(queries: [String]) -> Void {
         queries.forEach { query in
-            var statement : OpaquePointer? = nil
-            if sqlite3_prepare_v2(self.conn, query, -1, &statement, nil) == SQLITE_OK {
                 if sqlite3_step(statement) != SQLITE_DONE {
-                    print("Fallo la consulta: \(query) ")
         for query  in queries {
-                    self.printError()
+            var statement : OpaquePointer? = nil
-                }
-            } else {
-                print("Fallo la preparación: \(query) ")
-                self.printError()
+            if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
                 if sqlite3_step(statement) != SQLITE_DONE {
                     print("Fallo la consulta: \(query) ")
+                }
             } else {
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_app_id` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_ID + "` );",
+                    "CREATE INDEX IF NOT EXISTS `idx_app_id` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_ID + "` );",
-                    "CREATE INDEX `idx_app_user_uuid` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_USER_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS `idx_app_user_uuid` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_USER_UUID + "` );",
-                    "CREATE INDEX `idx_app_device_uuid` ON `" +  Constants.TABLE_APP + "` (`" +  Constants.TABLE_APP_FIELD_DEVICE_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)")
                 self.printError()
+            }
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_user_unotification_id` ON `" +  Constants.TABLE_USER_NOTIFICATION + "` (`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "` );",
+                    "CREATE INDEX IF NOT EXISTS `idx_user_unotification_id` ON `" +  Constants.TABLE_USER_NOTIFICATION + "` (`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_ID + "` );",
-                    "CREATE INDEX `idx_user_notification_user_uuid` ON `" +  Constants.TABLE_USER_NOTIFICATION + "` (`" +  Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + "` );"
+                    "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)
+                executeMultiQuery(queries: queries)
             } else {
                 print("No se pudo crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
                 self.printError()
+            }
         } else {
             print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
             self.printError()
+        }
     }
-    private func createTableUserExtended()
+    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 + Constants.TABLE_USER_EXTENDED_FIELD_VALUE + " TEXT"
         sql = sql + ");"
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_user_extended_company_uuid` ON `" +  Constants.TABLE_USER_EXTENDED + "` (`" +  Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + "` );"
+                    "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)")
-                self.printError()
+            }
         } else {
             print("Fallo la preparación para crear la tabla: \(Constants.TABLE_USER_EXTENDED)")
             self.printError()
         }
+    }
-    private func createTableSync()  {
+    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(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        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)")
                 self.printError()
+            }
         } else {
-            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SYNC)")
+            print("Fallo la preparación para crear la tabla: \(Constants.TABLE_SYNC)")
             self.printError()
+        }
+    }
-    private func createTableCompany()  {
+    private func createTableCompany() -> Void  {
         var sql = "CREATE TABLE IF NOT EXISTS " + Constants.TABLE_COMPANY + "("
         sql = sql + ");"
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_topics_company_uuid` ON `" +  Constants.TABLE_TOPIC + "` (`" +  Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + "` );"
+                    "CREATE INDEX IF NOT EXISTS  `idx_topics_company_uuid` ON `" +  Constants.TABLE_TOPIC + "` (`" +  Constants.TABLE_TOPIC_FIELD_COMPANY_UUID + "` );"
-                ]
+                ]
                 executeMultiQuery(queries: queries)
-            } else {
+            } else {
                 print("No se pudo crear la tabla: \(Constants.TABLE_TOPIC)")
-                self.printError()
+           }
         } else {
             print("Fallo la preparación para crear la tabla: \(Constants.TABLE_TOPIC)")
         sql = sql + ");"
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_capsules_topic_uuid` ON `" +  Constants.TABLE_CAPSULE + "` (`" +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + "` );"
+                    "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)")
                 self.printError()
+           }
         } else {
             print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_CAPSULE)")
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_slides_quiz_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS `idx_slides_quiz_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + "` );",
-                    "CREATE INDEX `idx_slides_capsule_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS  `idx_slides_capsule_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + "` );",
-                    "CREATE INDEX `idx_slides_topic_uuid` ON `" +  Constants.TABLE_SLIDE + "` (`" +  Constants.TABLE_SLIDE_FIELD_TOPIC_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)")
                 self.printError()
+           }
         print("sql : \(sql)")
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_progress_topic_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS `idx_progress_topic_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + "` );",
-                    "CREATE INDEX `idx_progress_capsule_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS  `idx_progress_capsule_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + "` );",
-                    "CREATE INDEX `idx_progress_slide_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + "` );",
+                    "CREATE INDEX IF NOT EXISTS  `idx_progress_slide_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + "` );",
-                    "CREATE INDEX `idx_progress_user_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "` );"
+                    "CREATE INDEX IF NOT EXISTS  `idx_progress_user_uuid` ON `" +  Constants.TABLE_PROGRESS + "` (`" +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "` );"
+                ]
-                executeMultiQuery(queries: queries)
+                executeMultiQuery(queries: queries)
             } else {
                 print("No se pudo crear la tabla: \(Constants.TABLE_PROGRESS)")
                 print(sql)
                 self.printError()
+           }
         } else {
             print("Fallo la preparación para la creación de la tabla: \(Constants.TABLE_PROGRESS)")
         sql = sql + ");"
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `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_user_uuid` ON `" +  Constants.TABLE_USER_LOG + "` (`" +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + "` );",
-                    "CREATE INDEX `idx_user_log_added_on` ON `" +  Constants.TABLE_USER_LOG + "` (`" +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON + "` );"
+                    "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)")
                 self.printError()
+           }
         } else {
             print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_USER_LOG)")
         sql = sql + Constants.TABLE_QUIZ_FIELD_UPDATED_ON + " TEXT);"
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_quizzes_company_uuid` ON `" +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + "` );"
+                    "CREATE INDEX IF NOT EXISTS  `idx_quizzes_company_uuid` ON `" +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_COMPANY_UUID + "` );"
-                ]
+                ]
                 executeMultiQuery(queries: queries)
-            } else {
+            } else {
                 print("No se pudo crear la tabla: \(Constants.TABLE_QUIZ)")
-                self.printError()
+           }
         } else {
             print("Fallo la preparación de la creación de la tabla: \(Constants.TABLE_QUIZ)")
             self.printError()
         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(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_questions_quiz_uuid` ON " +  Constants.TABLE_QUIZ + "` (`" +  Constants.TABLE_QUIZ_FIELD_UUID + "` );"
+                    "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)")
                 self.printError()
+           }
         } else {
             print("Fallo la preparación de la creación  de la tabla: \(Constants.TABLE_QUESTION)")
             self.printError()
+        }
+    }
         sql = sql + Constants.TABLE_ANSWER_FIELD_UPDATED_ON + " TEXT); "
         var statement : OpaquePointer? = nil
-        if sqlite3_prepare_v2(self.conn, sql, -1, &statement, nil) == SQLITE_OK {
+        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
             if sqlite3_step(statement) == SQLITE_DONE {
                 let queries = [
-                    "CREATE INDEX `idx_answers_question_uuid` ON `" +  Constants.TABLE_QUESTION + "` (`" +  Constants.TABLE_QUESTION_FIELD_UUID + "` );"
+                    "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)")
+                print("No se pudo crear la tabla: \(Constants.TABLE_ANSWER)")
                 self.printError()

Proyectos de Subversion Iphone Microlearning - Nuevo Interface

(root)/twogetskills/Database/Database.swift – Rev 32 → 61