当前位置: 首页 > 编程学习 > 其它语言 > Swift > 正文

Swift 4.0:扩展 WCDB 支持 SQL 语句

2018-04-22 来源:博客园/农民伯伯

前言

入坑 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 改起来也很麻烦,而且代码量很多。