I have updated this implementation on pull request #93.

Date type in Swift is recommended way to treat typical date and time type TIMESTAMP, DATETIME in MySQL commonly used for creation date, updates date.

Date only supports TIMESTAMP, DATETIME MySQL types.

DateComponents type in Swift is just container for representing SELECT ed string from databases into it. YEAR, TIME, DATE, TIMESTAMP, DATETIME column can be parsed into DateComponents. And used as query parameters for these types in MySQL as well.

DateComponents doesn't respect or treat for timeZone and calendar on itself by this library. But Date does respect time zone on ConnectionOption.timeZone.

Check more details on its tests and another one.

This is original proposal:

We could implement this:

• treat MySQL TIMESTAMP and DATETIME as SQLDate(and NSDate)
• DATE as SQLDate(NSDate) with YYYY-MM-DD 00:00:00
• TIME as String
• YEAR as Int

TIME type has range of '-838:59:59.000000' to '838:59:59.000000’, we couldn’t treat as NSDate. So we support it with String type for now.

(https://dev.mysql.com/doc/refman/5.6/en/datetime.html)

Related #24.

MySQL has 0000-00-00 date value for representing dummy date or empty date, or sometimes null. We could support such case in this library by following JDBC zeroDateTimeBehavior. (#24)

Hi, I'm currently evaluating Swift Mysql integration and was wondering if Swift 4 Codable protocol would be possible to integrate?

struct User: Codable {
  let id: Int
  let userName: String
  let age: Int?

  // Generated automatically by the compiler if not specified
  private enum CodingKeys: String, CodingKey {
    case id
    case userName = "user_name"
    case age
  }
  
  // Generated automatically by the compiler if not specified
  func encode(to encoder: Encoder) throws {
    var container = encoder.container(keyedBy: CodingKeys.self)
    try container.encode(id, forKey: . id)
    try container.encode(userName, forKey: . userName)
    try container.encode(age, forKey: . age)
  }

 // Generated automatically by the compiler if not specified
  init(from decoder: Decoder) throws {
    let container = try decoder.container(keyedBy: CodingKeys.self)
    id = try container.decode(Int.self, forKey: .id)
    userName = try container.decode(String.self, forKey: .userName)
    age = try container.decode(Int.self, forKey: .age)
  }
}

CodingKeys encode and decode are generated automatically. Reference https://www.mikeash.com/pyblog/friday-qa-2017-07-14-swiftcodable.html

An mysql ORM could now implement the necessary encode and decode container for arrays of objects. It looks similar to your current implementation. Would such an integration be possible?

Regards, Patrick

I'm planning following changes on next minor(actually major) version 0.9.0.

• [x] Change row decoder to Swift.Decoder(Decodable) #74 #72
• [x] Implement query parameter encoder for Swift.Encodable #72
• [x] JSON column support #85
• [x] Decimal type support #86

Current row decoder QueryRowResultType and parameter type QueryParameterDictionaryType will be deprecated or support dropped.

And will maintain 0.8.x for a period of time on 0.8-branch.

Related #58 #68.

環境

OS : Ubuntu 14.04 DB : 10.0.27-MariaDB-1~trusty コンパイラ : swift 3.0 release mysql-swift : 0.5.0

サンプル

struct Article: QueryRowResultType{
    var id: Int
    var title: String
    var content: String
    var isPublished: Bool
    var createdAt: SQLDate

    static func decodeRow(r: QueryRowResult) throws -> Article {
        return try Article(
            id: r <| "id",
            title: r <| "title",
            content: r <| "content",
            isPublished: r <| "is_published",
            createdAt: r <| "created_at"
        )
    }
}

class ArticleAccessor{
    class func loadPage(page: Int) -> [Article]{
        do{
            let articles: [Article] = try DB.connectionPool().execute { conn in
                try conn.query("SELECT * FROM articles LIMIT ?, ?", [10 * page, 10])
            }

            return articles
        }catch{
            return []
        }
    }
}

class DB{
    struct DataBaseOption: ConnectionOption{
        let host: String
        let port: Int
        let user: String
        let password: String
        let database: String
    }

    private static let options = DataBaseOption(host: "localhost", port: 3306, user: "root", password: "root", database: "hoge")

    class func connectionPool() -> ConnectionPool{
        return ConnectionPool(options: options)
    }
}

DB構成

+--------------+----------------+------+-----+-------------------+----------------+
| Field        | Type           | Null | Key | Default           | Extra          |
+--------------+----------------+------+-----+-------------------+----------------+
| id           | int(11)        | NO   | PRI | NULL              | auto_increment |
| title        | varchar(100)   | YES  |     | NULL              |                |
| content      | varchar(10000) | YES  |     | NULL              |                |
| is_published | tinyint(1)     | NO   |     | NULL              |                |
| created_at   | timestamp      | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+----------------+------+-----+-------------------+----------------+

上記のようなコードをコンパイルし実行すると「segmentation fault」でアプリが落ちてしまいます。 試しにSQLDateプロパティを除いてコンパイル＆実行すると正常に動作します。また、OSX(10.11.5)のMySQL 5.7.12で同じ構成をとるとSQLDateプロパティが含まれている状態でも正常に動作するようです。

mysql-swift 0.2.7を使用していた時は問題なかったようなのですが、こちら何か原因がわかりますでしょうか。

I have imported all the files inside "Sources", into my Xcode project.
Xcode's giving me multiple errors:

private var mysql_: UnsafeMutablePointer<MYSQL>? // Use of undeclared type 'MYSQL'

Same for mysql_init, MYSQL_OPT_CONNECT_TIMEOUT, my_bool, mysql_real_connect, MYSQL_OPT_RECONNECT, etc. ...

Am I missing something? Thanks!

It would be nice to use enums, OptionSets and other RawRepresentable types directly as query parameters.

This could make use of conditional conformance introduced in Swift 4.1. The following code will not compile and complain that an extension for a protocol cannot have an inheritance clause: Extension of protocol 'RawRepresentable' cannot have an inheritance clause. However, it should explain my idea. I guess mysql-swift needs some special internal handling of RawRepresentables to make this work.

extension RawRepresentable: QueryParameter where RawValue: QueryParameterType {
    public func queryParameter(option: QueryParameterOption) -> QueryParameterType {
        return self.rawValue
    }
}

struct User: Decodable {
  let id: Int
  let user_name: String
  let age: Int?
}
let rows: [User] =  try conn.query("SELECT id, user_name FROM users WHERE id = 123")

See https://github.com/novi/mysql-swift/issues/58. QueryRowResultType still supported.

The string value from MySQL was converted to Double. In some cases this can produce strange Decimal values, e.g. 1.5489999999999995904 instead of 1.549.

In node-mysql I can do something like this:

let users: [User] = [user1, user2, user3]
try connection.query("INSERT INTO User (id, name, age) VALUES ?", [users])

In mysql-swift I have to manually build the query string depending on the number of rows, e.g. "INSERT INTO User (id, name, age) VALUES (?,?,?), (?,?,?), (?,?,?)".

I could also be missing something. Is there another way to achieve this in mysql-swift?

Hello,

the package initialization should be

.Package(url: "https://github.com/novi/mysql-swift.git", majorVersion: 0, minor: 5)

or swift build will run into already solved issues.

Regards, Sebastian Mecklenburg

I have fixed some warnings in XCode 8. Would you like me to make a PR? It's not much, just some @noescape and unused results.

Another warning is for the deprecated operator declaration.

warning: operator should no longer be declared with body; use a precedence group instead
infix operator <| { associativity left precedence 150 }
                  ^
warning: operator should no longer be declared with body; use a precedence group instead
infix operator <|? { associativity left precedence 150 }
                   ^

As discussed in swift-evolution a possible fix would be

// Before
infix operator <|  {  associativity left precedence 150 }

// After
precedencegroup ComparisonPrecedence {
  associativity: left
  higherThan: LogicalConjunctionPrecedence
}
infix operator <| : ComparisonPrecedence

I don't know what you had in mind when you put 150, but I'm thinking that it should have the precedence around AdditivePrecedence.

Andrei

The age-old floating point issues bit me once again...

Note: This most definitely also applies to Float, but I have just tested with Double.

When Swift formats a Double as a String it rounds the number, e.g. an internal representation of 7.7087009966199993 has a String value of 7.70870099662. When you create a Double from that String, you get back 7.7087009966200002. This is closer to 7.70870099662 than 7.7087009966199993, so it is the right thing to do here.

mysql-swift uses the String initializer to convert numbers to strings and hands it off to MySQL client. I believe the server then decodes that string in much the same way Swift does: The string 7.70870099662 is converted to the double value 7.7087009966200002.

JSONEncoder/JSONDecoder does not observe these issues and formats the double value as 7.7087009966199993. We should probably look into the Codable implementation of Double and figure out how they encode floating point values.

I have created a sample implementation to reproduce the issue:

CREATE TABLE `DoubleTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `doubleField` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

import Foundation
import MySQL

struct MySQLOptions: MySQL.ConnectionOption {
    init() { }
    let host = "127.0.0.1"
    let port = 3306
    let user = "doubleUser"
    let password = "doubleUser"
    let database = "DoubleDatabase"
    let timeZone = TimeZone(identifier: "UTC")!
    let encoding = Connection.Encoding.UTF8MB4
    let timeout = 10
    let reconnect = true
    let omitDetailsOnError = false
}

struct DoubleStruct: Codable {
    let id: UInt32
    let doubleField: Double
}

let pool = ConnectionPool(options: MySQLOptions())

try! pool.execute { connection in
    // Write a double value to the database
    print("Writing double value to database")
    let doubleStruct1 = DoubleStruct(id: 0, doubleField: 7.7087009966199993)
    var query = "INSERT INTO DoubleTable (doubleField) VALUES (?)"
    let result = try connection.query(query, [doubleStruct1.doubleField])
    print("Created row with id \(result.insertedID) and double field \(doubleStruct1.doubleField). Memory dump follows below.")
    dump(doubleStruct1.doubleField)
    
    // Read the same double value from the database
    query = "SELECT id, doubleField FROM DoubleTable WHERE id = ?"
    let doubleStructs: [DoubleStruct] = try connection.query(query, [UInt32(result.insertedID)])
    let doubleStruct2 = doubleStructs.first!
    print("Read row with id \(doubleStruct2.id) and double field \(doubleStruct2.doubleField). Their internal values are\(doubleStruct1.doubleField == doubleStruct2.doubleField ? "" : " not") equal. Dump of the read value follows.")
    dump(doubleStruct2.doubleField)
    
    // Encode and decode as JSON
    print("\nNow encoding as JSON")
    let jsonEncoder = JSONEncoder()
    let jsonDecoder = JSONDecoder()
    let json1 = try jsonEncoder.encode(doubleStruct1)
    let json2 = try jsonEncoder.encode(doubleStruct2)
    print(String(decoding: json1, as: UTF8.self))
    print(String(decoding: json2, as: UTF8.self))
    let doubleStruct1a = try jsonDecoder.decode(DoubleStruct.self, from: json1)
    let doubleStruct2a = try jsonDecoder.decode(DoubleStruct.self, from: json2)
    print("JSON double values for struct 1 are\(doubleStruct1.doubleField == doubleStruct1a.doubleField ? "" : " not") equal. Dump follows.")
    dump(doubleStruct1.doubleField)
    dump(doubleStruct1a.doubleField)
    print("JSON double values for struct 2 are\(doubleStruct2.doubleField == doubleStruct2a.doubleField ? "" : " not") equal. Dump follows.")
    dump(doubleStruct2.doubleField)
    dump(doubleStruct2a.doubleField)
    
    // Convert to string and back
    print("\nNow encoding as string")
    let string1 = String(doubleStruct1.doubleField)
    let doubleFromString = Double(string1)!
    print("Encoding and decoding from string. Values are\(doubleStruct1.doubleField == doubleFromString ? "" : " not") equal. Dump follows")
    dump(doubleStruct1.doubleField)
    dump(doubleFromString)
}