Preface

wcdb has been in the pit for two months, which is still very good on the whole. For specific advantages, please refer to the documentation. Because the official specification does not support SQL, we have to write an extension to support it A kind of

statement
Welcome to reprint, but please keep the original source of the article:)
Blog Park: http://www.cnblogs.com
Uncle farmer: http://over140.cnblogs.com

text

I. function realization

fork a source code and add the following code to the source code (some classes limit access scope)

　　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
        }
    }

2. Use SQL to query or update data

2.1 query

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

It should be noted that if Codable data is returned, the order of the SELECT field must be the same as that in CodingKeys. Otherwise, the data will be filled disorderly. However, WINQ will not cause this problem.

2.2 update

                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])

End

At present, we haven't found any problems in a period of time. In addition to the previous attention to the order problem, WINQ just can't understand why the official doesn't directly support one SQL statement. Even if it can support all SQL statements, it's troublesome to change them, and the code volume is large.