【Swift 4.0】扩展 WCDB 支持 SQL 语句

 

 

前言

　　入坑 wcdb 有两个月了，整体来说还是很不错的，具体优点可以参考文档说明，由于官方明确说明不支持 SQL 只好自己写一个扩展支持一下了 😂

 

声明
　　欢迎转载，但请保留文章原始出处:)
　　博客园：http://www.cnblogs.com
　　农民伯伯： http://over140.cnblogs.com

 

正文

　　一、功能实现

　　fork 一份源码然后把下面代码加入源码（有些类限制了访问作用域）

　　SelectSQL.swift

import Foundation

    extension Database {

        public func prepareSelectSQL(on propertyConvertibleList: [PropertyConvertible], sql: String, values: [ColumnEncodableBase] = []) throws -> SelectSQL {
            return try SelectSQL(with: self, on: propertyConvertibleList, sql: sql, values: values)
        }

    }

    public final class SelectSQL {

        private final var core: Core
        final var optionalRecyclableHandleStatement: RecyclableHandleStatement?
        final var statement: StatementSelectSQL

        private let keys: [CodingTableKeyBase]
        private let values: [ColumnEncodableBase]

        private lazy var decoder = TableDecoder(keys, on: optionalRecyclableHandleStatement!)

        init(with core: Core, on propertyConvertibleList: [PropertyConvertible], sql: String, values: [ColumnEncodableBase]) throws {
            //TODO: Use generic to check all coding table keys conform to same root type
            keys = propertyConvertibleList.asCodingTableKeys()
            self.statement = StatementSelectSQL(sql: sql)
            self.core = core
            self.values = values
        }

        private func bindValues() throws {
            guard values.count > 0 else {
                return
            }
            let handleStatement = try lazyHandleStatement()
            for idx in 0..<values.count {
                handleStatement.bind(values[idx].archivedFundamentalValue(), toIndex: idx + 1)
            }
        }

        deinit {
            try? finalize()
        }

        /// Get all selected objects according to the `CodingTableKey`.
        ///
        /// - Returns: Table decodable objects according to the `CodingTableKey`
        /// - Throws: `Error`
        public func allObjects() throws -> [Any] {
            let rootType = keys[0].rootType as? TableDecodableBase.Type
            assert(rootType != nil, "\(keys[0].rootType) must conform to TableDecodable protocol.")
            var objects: [Any] = []
            try bindValues()
            while try next() {
                objects.append(try rootType!.init(from: decoder))
            }
            return objects
        }

        /// Get all selected objects.
        ///
        /// - Parameter type: Type of table decodable object
        /// - Returns: Table decodable objects.
        /// - Throws: `Error`
        public func allObjects<Object: TableDecodable>(of type: Object.Type = Object.self) throws -> [Object] {
            assert(keys is [Object.CodingKeys], "Properties must belong to \(Object.self).CodingKeys.")
            var objects: [Object] = []
            try bindValues()
            while try next() {
                objects.append(try Object.init(from: decoder))
            }
            return objects
        }

        final func finalize() throws {
            if let recyclableHandleStatement = optionalRecyclableHandleStatement {
                try recyclableHandleStatement.raw.finalize()
                optionalRecyclableHandleStatement = nil
            }
        }

        final func lazyHandleStatement() throws -> HandleStatement {
            if optionalRecyclableHandleStatement == nil {
                optionalRecyclableHandleStatement = try core.prepare(statement)
            }
            return optionalRecyclableHandleStatement!.raw
        }

        //Since `next()` may throw errors, it can't conform to `Sequence` protocol to fit a `for in` loop.
        @discardableResult
        public final func next() throws -> Bool {
            do {
                return try lazyHandleStatement().step()
            } catch let error {
                try? finalize()
                throw error
            }
        }

    }

    extension SelectSQL: CoreRepresentable {
        /// The tag of the related database.
        public final var tag: Tag? {
            return core.tag
        }

        /// The path of the related database.
        public final var path: String {
            return core.path
        }
    }

 

　　StatementSelectSQL.swift

    import Foundation

    public final class StatementSelectSQL: Statement {

        public private(set) var description: String = ""
        public var statementType: StatementType {
            return .select
        }

        public init(sql: String) {
            self.description = sql
        }
    }

 

　　　　UpdateSQL.swift

    import Foundation

    extension Database {

        public func prepareUpdateSQL(sql: String) throws -> UpdateSQL {
            return try UpdateSQL(with: self, sql: sql)
        }

    }

    /// The chain call for updating
    public final class UpdateSQL {
        private var core: Core
        private let statement: StatementUpdateSQL

        /// The number of changed rows in the most recent call.
        /// It should be called after executing successfully
        public var changes: Int?

        init(with core: Core, sql: String) throws {
            self.core = core
            self.statement = StatementUpdateSQL(sql: sql)
        }

        /// Execute the update chain call with row.
        ///
        /// - Parameter row: Column encodable row
        /// - Throws: `Error`
        public func execute(with row: [ColumnEncodableBase?] = []) throws {
            let recyclableHandleStatement: RecyclableHandleStatement = try core.prepare(statement)
            let handleStatement = recyclableHandleStatement.raw
            for (index, value) in row.enumerated() {
                let bindingIndex = index + 1
                handleStatement.bind(value?.archivedFundamentalValue(), toIndex: bindingIndex)
            }
            try handleStatement.step()
            changes = handleStatement.changes
        }
    }

    extension UpdateSQL: CoreRepresentable {

        /// The tag of the related database.
        public var tag: Tag? {
            return core.tag
        }

        /// The path of the related database.
        public var path: String {
            return core.path
        }
    }

 

　　　　StatementUpdateSQL.swift　　

    import Foundation

    public final class StatementUpdateSQL: Statement {

        public private(set) var description: String = ""
        public var statementType: StatementType {
            return .update
        }

        public init(sql: String) {
            self.description = sql
        }
    }

 

　　二、使用 SQL 查询或更新数据

　　　　2.1　　查询

　　　　　　database.prepareSelectSQL(User.Properties.Id, "SELECT id FROM users where id = ?", values: ["1"])

　　　　　　需要特别注意的是如果返回 Codable 数据，SELECT 字段的顺序必须要和 CodingKeys 里的顺序一致，否则数据会填充乱，但是用 WINQ 不会有这个问题。

　　　　2.2　　更新

                let updateSQL = try database.prepareUpdateSQL(sql: "UPDATE conversations SET last_message_id = (select id from messages where conversation_id = ? order by created_at DESC limit 1) WHERE conversation_id = ?")
                try updateSQL.execute(with: [conversationId, conversationId])

　　

 结束

　　目前用了一段时间没有发现什么问题，除了前面那个注意顺序问题，WINQ 就是拼 SQL 语句搞不懂官方为啥不直接支持一个，就算能支持所有 SQL 改起来也很麻烦，而且代码量很多。