MySQL helper and base object definition

//
//  ENMySQL.swift
//
import OHMySQL        // https://github.com/oleghnidets/OHMySQL
import EVReflection   // https://github.com/evermeer/EVReflection

typealias sql = OHMySQLQueryRequestFactory

enum ENMySQLError: Int {
    case insertWithoutValues = -12001
}

class ENMySQL {

    private var coordinator: OHMySQLStoreCoordinator?
    private var context: OHMySQLQueryContext?

    private var isTransaction: Bool = false

    init() {

        //          // mysql with certificates
        //        let key = Bundle.main.path(forResource: "client-key", ofType: "pem")
        //        let cert = Bundle.main.path(forResource: "client-cert", ofType: "pem")
        //        let ca = Bundle.main.path(forResource: "ca-cert", ofType: "pem")
        //
        //        let config = OHSSLConfig(key: key!,
        //                                 certPath: cert!,
        //                                 certAuthPath: ca!,
        //                                 certAuthPEMPath: nil,
        //                                 cipher: nil)
        //
        //        let root = OHMySQLUser(userName: "username",
        //                               password: "password",
        //                               sslConfig: config!,
        //                               serverName: "localhost",
        //                               dbName: "db_name",
        //                               port: 3306,
        //                               socket: nil)

        let root = OHMySQLUser(userName: "username",
                               password: "passwords",
                               serverName: "localhost",
                               dbName: "db_name",
                               port: 3306,
                               socket: nil)

        coordinator = OHMySQLStoreCoordinator(user: root!)
        coordinator?.encoding = .UTF8MB4

        context = OHMySQLQueryContext()
        context?.storeCoordinator = coordinator!

        coordinator?.connect()
    }

    deinit {
        coordinator?.disconnect()
    }

    func disconnect() {
        coordinator?.disconnect()
    }

    func select(_ query: OHMySQLQueryRequest, _ completion: (_ elements: [[String: Any]]?, _ error: Error?) -> Void) {

        do {
            let elements = try context?.executeQueryRequestAndFetchResult(query)
            completion(elements, nil)
        } catch {
            completion(nil, error)
        }

    }

    func select<T:ENBaseObject>(id: AnyObject, completion: (_ object: T?, _ error: Error?) -> Void) {

        do {
            var object: T?

            let condition = "\(T().primaryKey()))=\(id)"
            let query = sql.selectFirst(T.table, condition: condition)
            if let elements = try context?.executeQueryRequestAndFetchResult(query), let dict = elements.first {
                object = T.init(dictionary: dict as NSDictionary)
            }

            completion(object, nil)
        } catch  {
            completion(nil, error)
        }

    }

    func select<T:ENBaseObject>(condition: String?, order: [String] = [], ascending: Bool = true, completion: (_ objects: [T]?, _ error: Error?) -> Void) {

        select(table: T.table, condition: condition, order: order, ascending: ascending) { (elements, error) in
            var objects:[T] = []

            elements?.forEach { (dict) in
                let obj = T.init(dictionary: dict as NSDictionary)
                objects.append(obj)
            }

            completion(objects, error)
        }
    }

    func select(table: String, condition: String?, order: [String] = [], ascending: Bool = true, completion: (_ result: [[String: Any]]?, _ error: Error?) -> Void) {
        do {
            var query = sql.select(table, condition: condition)
            if order.count > 0 {
                query = sql.select(table, condition: condition, orderBy: order, ascending: ascending)
            }
            let result = try context?.executeQueryRequestAndFetchResult(query)
            completion(result, nil)
        } catch {
            completion(nil, error)
        }
    }

    func select<T:ENBaseObject>(object: T, completion: (_ object: T?, _ error: Error?) -> Void) {

        do {
            let id = object.value(forKey: object.primaryKey())
            let condition = "\(object.primaryKey()))=\(id!)"
            let query = sql.selectFirst(object.mySQLTable(), condition: condition)
            if let elements = try context?.executeQueryRequestAndFetchResult(query), let dict = elements.first {
                let result = T.init(dictionary: dict as NSDictionary)
                completion(result, nil)
            }

        } catch  {
            completion(nil, error)
        }
    }

    func join(type: String,
              table: String,
              columns: [String],
              on: [String: String],
              completion: (_ result: [[String: Any]]?, _ error: Error?) -> Void) {
        do {
            let query = sql.joinType(type, fromTable: table, columnNames: columns, joinOn: on)
            let result = try context?.executeQueryRequestAndFetchResult(query)
            completion(result, nil)
        } catch {
            completion(nil, error)
        }
    }

    func insert<T:ENBaseObject>(_ object: T, _ completion: ((_ error: Error?) -> Void)? = nil) {

        do {
            context?.insertObject(object)
            if !isTransaction {
                try context?.save()
            }
            completion?(nil)
        } catch {
            completion?(error)
        }
    }

    func insert<T:ENBaseObject>(_ object: T, _ completion: ((_ id: NSNumber?, _ error: Error?) -> Void)) {

        if !isTransaction {
            do {
                context?.insertObject(object)
                try context?.save()
                let result = context?.lastInsertID()
                completion(result, nil)
            } catch {
                completion(nil, error)
            }
        }
    }

    func delete<T:ENBaseObject>(_ object: T, _ completion: ((_ error: Error?) -> Void)? = nil)
    {
        do {
            context?.deleteObject(object)
            if !isTransaction {
                try context?.save()
            }
            completion?(nil)
        } catch {
            completion?(error)
        }
    }

    func update<T:ENBaseObject>(_ object: T, _ completion: ((_ error: Error?) -> Void)? = nil)
    {
        do {
            context?.updateObject(object)
            if !isTransaction {
                try context?.save()
            }
            completion?(nil)
        } catch {
            completion?(error)
        }
    }

    func transaction(_ block: (_ context: OHMySQLQueryContext?) -> Void, _ completion: ((_ error: Error?) -> Void)? = nil) {

        do {
            isTransaction = true
            block(context)
            isTransaction = false

            try context?.save()

            completion?(nil)
        } catch {
            completion?(error)
        }
    }

}


//
//  ENBaseObject.swift
//
import OHMySQL        // https://github.com/oleghnidets/OHMySQL
import EVReflection   // https://github.com/evermeer/EVReflection

class ENBaseObject: EVObject, OHMappingProtocol {

    static var table: String {
        return "\(String(describing: self).dropFirst(2))"
    }

    static var primaryKey: String {
        return "id\(self.table)"
    }

    convenience init(id: Any) {
        self.init()

        self.setValue(id, forKey: primaryKey())
    }

    // MARK: - EVObject

    override func initValidation(_ _dict: NSDictionary) {
        // convert fields named "NNN.NNN" to "class-property-name.property-name"
        // ex. "BookChapter.VerseNumber" to "verseNumber" property of "bookChapter" class property
        // for a query type: SELECT VerseNumber AS BookChapter.VerseNumber FROM Chapters
        if let dict = _dict as? Dictionary<String, AnyObject> {
            // look for the keys that have a dot in the name
            let keys = dict.keys.filter({ $0.contains(".") })
            if keys.count > 0 {
                var counter: Int = 0
                keys.forEach { (key) in

                    // takes the first part of the name that identifies a property
                    let keypaths = key.split(separator: ".")
                    if let first = keypaths.first {
                        let propertyName = "\(first)".prefix(1).lowercased() + first.dropFirst()

                        // if the property has never been initialized, get the property class type
                        if self.value(forKey: propertyName) == nil,
                            let type = self.typeForKey(propertyName),
                            let className = "\(type)"
                                .split(separator: ".")
                                .last?
                                .replacingOccurrences(of: ">)", with: ""),
                            let classType = NSClassFromString(className) as? EVObject.Type {

                            // takes only the values for the property and normalizes the name of the keys
                            let filtered = dict.filter({ $0.key.hasPrefix("\(propertyName).") })
                            let partial = filtered.updateKeys({ (key) -> String in
                                return key.replacingOccurrences(of: "\(propertyName).", with: "")
                            })

                            // instance object
                            let object = classType.init(dictionary: partial as NSDictionary)
                            self.setValue(object, forKey: propertyName)

                            // if has processed all keys, ends
                            counter += partial.count
                            if counter >= keys.count {
                                return
                            }
                        }
                    }
                }
            }
        }
    }

    // MARK: - OHMappingProtocol

    func mappingDictionary() -> [AnyHashable : Any]! {
        return self.toDictionary() as? [AnyHashable: Any]
    }

    func mySQLTable() -> String! {
        return type(of: self).table
    }

    func primaryKey() -> String! {
        return type(of: self).primaryKey
    }

    // MARK: - Database

    func select(_ completion: @escaping (_ object: ENBaseObject? , _ error: Error?) -> Void) {

        let db = ENMySQL()
        db.select(object: self) { (object, error) in
            completion(object, error)
        }
    }

    func insert(_ completion: ((_ error: Error?) -> Void)? = nil) {

        let db = ENMySQL()
        db.insert(self) { (_, error) in
            completion?(error)
        }
    }

    func delete(_ completion: ((_ error: Error?) -> Void)? = nil) {

        let db = ENMySQL()
        db.delete(self, completion)
    }

    func update(_ completion: ((_ error: Error?) -> Void)? = nil) {

        let db = ENMySQL()
        db.update(self, completion)
    }
}

fileprivate extension Dictionary {
    func updateKeys(_ transform: (Key) -> Key) -> Dictionary {
        return Dictionary(uniqueKeysWithValues:
            self.map { (transform($0), $1) })
    }
}