Proyectos de Subversion Iphone Microlearning - Inconcert

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


import UIKit
import SQLite3

class SyncDao {
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    private var database = Database.sharedInstance
    
    static let sharedInstance: SyncDao = {
           let instance = SyncDao()
           
           // setup code
           return instance
    }()

    func insert(record : SyncModel) -> Int {
        let db = database.open()
        let query = "INSERT INTO " + Constants.TABLE_SYNC + " ( " + Constants.TABLE_SYNC_FIELD_TYPE + ", " + Constants.TABLE_SYNC_FIELD_DATA + " ) VALUES (?, ?);"
        var statement : OpaquePointer?
        var result = 0;
        
           
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
                
            sqlite3_bind_int(statement, 1, Int32(record.type))
            sqlite3_bind_text(statement, 2, record.data , -1, SQLITE_TRANSIENT)
           if (sqlite3_step(statement) == SQLITE_DONE) {
               result = Int(sqlite3_last_insert_rowid(db))
           } else {
                print("No se pudo insertar el registro ( type: \(record.type) data: \(record.data)   en la tabla: \(Constants.TABLE_SYNC)")
                database.printError()
            
           }
           
        } else {
            print("Fallo la preparación del insert en la tabla: \(Constants.TABLE_SYNC)")
            database.printError()
        }
        
        sqlite3_finalize(statement)
        return result
     }
    
    func selectOneByType(type: Int) -> SyncModel {
        let db = database.open()
        var record = SyncModel()
        
        let query = "SELECT "
            + Constants.TABLE_SYNC_FIELD_ID + "  , "
            + Constants.TABLE_SYNC_FIELD_TYPE + " , "
            + Constants.TABLE_SYNC_FIELD_DATA
            + "  FROM " + Constants.TABLE_SYNC + " WHERE type = '\(type)' LIMIT 1; "
        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                record.id = Int(sqlite3_column_int(statement, 0))
                record.type = Int(sqlite3_column_int(statement, 1))
                record.data = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                
            }
        } else {
            database.printError()
        }
        sqlite3_finalize(statement)
        return record
    }
    
    
    func countPendingRecords()-> Int {
        let db = database.open()
        var count = 0 
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_SYNC
        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
            }
        } else {
            database.printError()
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func selectBatch()-> [SyncModel] {
        let db = database.open()
        var records = [SyncModel]()
        
        var query = "SELECT "
        query = query + Constants.TABLE_SYNC_FIELD_ID + "  , "
        query = query + Constants.TABLE_SYNC_FIELD_TYPE + " , "
        query = query + Constants.TABLE_SYNC_FIELD_DATA
        query = query + " FROM " + Constants.TABLE_SYNC
        query = query + " ORDER BY " + Constants.TABLE_SYNC_FIELD_ID + " ASC "
        query = query + " LIMIT 10; "
        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                var model = SyncModel()
                model.id = Int(sqlite3_column_int(statement, 0))
                model.type = Int(sqlite3_column_int(statement, 1))
                model.data = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                
                records.append(model)
            }
        } else {
            database.printError()
        }
        sqlite3_finalize(statement)
        return records
    }
    
    func remove(id : Int) {
        let db = database.open()
        let query = "DELETE FROM " + Constants.TABLE_SYNC + " WHERE " + Constants.TABLE_SYNC_FIELD_ID + " = " + "\(id) ;"
        var statement : OpaquePointer? = nil
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if (sqlite3_step(statement) != SQLITE_DONE) {
                print("No se pudo borrar el ID: \(id) de la tabla: \(Constants.TABLE_SYNC)")
                database.printError()
            } 
        } else {
            database.printError()
        }
        sqlite3_finalize(statement)
    }
}