vapor / fluent-mysql-driver Goto Github PK

View Code? Open in Web Editor NEW

76.0 26.0 52.0 406 KB

🖋🐬 Swift ORM (queries, models, relations, etc) built on MySQL.

License: MIT License

Swift 100.00%

fluent swift vapor mysql vapor-service fluent-driver server-side-swift swift-linux fluent-database

fluent-mysql-driver's Introduction

fluent-mysql-driver's People

Contributors

Stargazers

Watchers

Forkers

ts technikyle brettrtoomey tadija dmcyk sergdort cwoloszynski volkerbb mozharovsky rafiki270 foulkesjohn twof adammika sandordobi bre7 jseibert mateusfsilva hercip rb-de0 omochi pvels tholo thesparklabs mihaelisaev ml-archive skelpo mixio phamotcreations ozitrance kevintao weijentu foscomputerservices portellaa nobodynada easykardex iq3addli frets1700 cweinberger podul samalone latencyzero dec22gln dksuperman t-ae masterofinsanity chrigu1981 isabella232 liyongjiaoya finestructure safe-care bonpetiteapps zunda-pixel

fluent-mysql-driver's Issues

Schema generator crash on enum values V3

MySQLDatabase+Schema.swift doesn't handle KeyStringDecodable's raw values and crashes in public static func fieldType(for type: Any.Type) throws -> ColumnType

create() should only request a new ID when it's nil

If the model already had an ID, try to create a new model using said ID instead of requesting the last one that was inserted

Make support for "after column"

Missing feature

ALTER TABLE `listings` ADD `client_id` INT NULL DEFAULT NULL AFTER `object_no`;

A default database is required if no database ID is passed to User.query(_:on:)

A default database is required if no database ID is passed to User.query(_:on:) or if User is being looked up statically.

I have a custom id User model like:

extension User: Model,Timestampable {
    public static var createdAtKey: CreatedAtKey {return \.createdAt }
    public static var updatedAtKey: UpdatedAtKey { return \.updatedAt}

    typealias Database = MySQLDatabase
    public typealias ID = String
    public static var idKey: IDKey{ return \.username }
}

The error was sent by Xcode at run process immediately, in the controller I have

func usuarios(_ req: Request)  -> Future<[User]>{
        return req.withConnection(to: .eccdatabase , closure: { db -> Future<[User]> in
            return  db.query(User.self).all()
        })
    }

Support of emojis?

I'm getting this error when trying to store a string containing emojis:

MySQL error occured while saving: MySQL Error: Incorrect string value: '\xF0\x9F\x92\x93\xF0\x9F...' for column 'description' at row 1

Identifier: MySQL.MySQLError.1366 (truncatedWrongValueForField)

If I perform a raw query, it inserts a row just fine:

if let mysqlDriver = drop.database?.driver as? MySQLDriver.Driver {
    try! mysqlDriver.raw("INSERT INTO countries (uValue, createdAt, updatedAt) VALUES ('Test 💓💓', '2008-7-04', '2008-7-04')")
}

My database seems properly set for storing emojis:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8
character_set_system	utf8
collation_connection	utf8mb4_general_ci
collation_database	utf8_general_ci
collation_server	utf8_general_ci

How to fix the issue? MySQL Driver version is 2.0.3

I noticed that someone's PR failed on Mac but not on Linux. I was really confused as it's usually the opposite. I looked into it and it looks like Linux is only running one of the test cases, not all three. Is there a specific reason for this?

https://github.com/vapor/mysql-driver/blob/master/Tests/LinuxMain.swift#L7

Error when handling models with Date properties

I have a Fluent model that has a Date property, which gets mapped to DATETIME in my MySQL database. If I use all() to get an array of my model object I see the data come back, and the dates are represented as epoch time. However, if I use find() to look for a particular object, I get the following error:

MySQL.MySQLError.invalidTypeBound: Field of type int was bound, mismatching the expected type datetime (supports(expecting:):Binding.swift:418:14)“

I haven't tested this using the new beta yet; this error is from alpha 12.

Vapor 3.0.0 RC 1: Support Codable Models with more complex mapping

I am getting nested JSON, which I am decoding / encoding in the model using init(from decoder: Decoder) and encode(to encoder: Encoder). While this compiles properly, the underlying table that is constructed does not contain all of the model's properties (e.g. the ones that are populated by those init and encode methods; also see this post about flattening JSON):

Note though, that this isn't Support for JSON!

I am on Vapor 3.0.0 RC 1 and Swift 4.1:

$ cat Package.swift 
...
        .package(url: "https://github.com/vapor/vapor.git", from:"3.0.0-rc"), //.branch("beta")),
        .package(url: "https://github.com/vapor/fluent-mysql", from:"3.0.0-rc") // .branch("beta")),
...
$ swift --version
Apple Swift version 4.1 (swiftlang-902.0.41 clang-902.0.31)
Target: x86_64-apple-darwin17.4.0
$

Example JSON:

{
    "type": 0,
    "measurement": {
        "t": 4.05,
        "p": 77.81,
        "h": 45.23
    },
    "published_at": "2018-02-22T11:39:15.159Z"
}

Example Model:

enum MeasurementType: Int, Codable {
    case placeA = 0
    case placeB
    case placeC
}

final class Measurement: Codable {
    /// Model.ID
    var id: Int?
    var coreID: Core.ID?
    var type: MeasurementType
    var temperature: Double
    var pressure: Double
    var humidity: Double
    var publishedAt: Date?
    
    /// Timestampable.createdAt
    var createdAt: Date?
    /// Timestampable.updatedAt
    var updatedAt: Date?
    
    /// Initializer
    init(id: ID? = nil, coreID: Core.ID?, type: MeasurementType, temperature: Double, pressure: Double, humidity: Double, publishedAt: Date) {
        self.id = id
        self.coreID = coreID
        self.type = type
        self.temperature = temperature
        self.pressure = pressure
        self.humidity = humidity
        self.publishedAt = publishedAt
    }
    
    enum RootKeys: String, CodingKey {
        case id
        case coreID
        case type
        case measurement
        case publishedAt = "published_at"
    }
    
    enum MeasurementKeys: String, CodingKey {
        case temperature = "t"
        case pressure = "p"
        case humidity = "h"
    }
    
    init(from decoder: Decoder) throws {
        let container = try decoder.container(keyedBy: RootKeys.self)
        id = try? container.decode(ID.self, forKey: .id)
        coreID = try? container.decode(UUID.self, forKey: .coreID)
        type = try! container.decode(MeasurementType.self, forKey: .type)
        publishedAt = try container.decode(Date.self, forKey: .publishedAt)
        
        let measurementContainer = try container.nestedContainer(keyedBy: MeasurementKeys.self, forKey: .measurement)
        temperature = try measurementContainer.decode(Double.self, forKey: .temperature)
        pressure = try measurementContainer.decode(Double.self, forKey: .pressure)
        humidity = try measurementContainer.decode(Double.self, forKey: .humidity)
    }
    
    func encode(to encoder: Encoder) throws {
        var container = encoder.container(keyedBy: RootKeys.self)
        try? container.encode(id, forKey: .id)
        try? container.encode(coreID, forKey: .coreID)
        try? container.encode(type, forKey: .type)
        try? container.encode(publishedAt, forKey: .publishedAt)
        
        var measurementContainer = container.nestedContainer(keyedBy: MeasurementKeys.self, forKey: .measurement)
        try measurementContainer.encode(temperature, forKey: .temperature)
        try measurementContainer.encode(pressure, forKey: .pressure)
        try measurementContainer.encode(humidity, forKey: .humidity)
    }
}

/// MARK: Model
extension Measurement: Model {
    /// See Model.Database
    typealias Database = MySQLDatabase
    
    /// See Model.ID
    typealias ID = Int
    
    /// See Model.idKey
    static var idKey: WritableKeyPath<Measurement, Int?> {
        return \Measurement.id
    }
}

/// MARK: Relations
extension Measurement {
    /// A relation to this pressure's core
    var core: Parent<Measurement, Core>? {
        return parent(\.coreID)
    }
}

/// Stored date stamps
extension Measurement: Timestampable {
    static var createdAtKey: WritableKeyPath<Measurement, Date?> {
        return \.createdAt
    }
    
    static var updatedAtKey: WritableKeyPath<Measurement, Date?> {
        return \.updatedAt
    }
}

/// Support dynamic migrations.
extension Measurement: Migration { }

/// Support encoded to and decoded from HTTP messages.
extension Measurement: Content { }

/// Support using as a dynamic parameter in route definitions.
extension Measurement: Parameter { }

@Joannis mentioned on Slack that this might be related to the MySQL schema? Right now I don't do anything schema related in Migration (create and delete do not seem to be available for MySQL 🤔) and let Vapor create the schema.

If I do try to add schema creation in the Model's Migration protocol conformance, it appears create and delete are not available:

code:

/// Support dynamic migrations.
extension Measurement: Migration {
    /// See Migration.prepare
    static func prepare(on connection: Database.Connection) -> Future<Void> {
        connection.create(self) { builder in
            try builder.field(for: \.id)
            try builder.field(for: \.coreID)
            try builder.field(for: \.type)
            try builder.field(for: \.temperature)
            try builder.field(for: \.pressure)
            try builder.field(for: \.humidity)
            try builder.field(for: \.publishedAt)
            try builder.field(for: \.createdAt)
            try builder.field(for: \.updatedAt)
        }
    }
    
    /// See Migration.revert
    static func revert(on connection: Database.Connection) -> Future<Void> {
        return connection.delete(self)
    }
}

error:

Compile Swift Module 'App' (20 sources)
/.../Sources/App/Models/Measurement.swift:134:9: error: value of type 'MySQLDatabase.Connection' (aka 'MySQLConnection') has no member 'create'
        connection.create(self) { builder in
        ^~~~~~~~~~ ~~~~~~
/.../Sources/App/Models/Measurement.swift:149:16: error: value of type 'MySQLDatabase.Connection' (aka 'MySQLConnection') has no member 'delete'
        return connection.delete(self)
               ^~~~~~~~~~ ~~~~~~

Support for JSON

Looking at the current Vapor 3 PostgreSQL driver, it supports the JSON field. MySQL does support JSON and it would be nice to support this field in a similar manner to the PostgreSQL version to keep things simple across the drivers.

What I'm referring to is this: https://github.com/twof/VaporNation/blob/master/week0-18-01-21.md#postgresql-support-for-jsonjsonb-and-arrays @twof has put together this example.

EnableLogging not works with MySQL

The query logging feature is not working/implemented in MySQL

The DatabaseConfig.enableLogging(on: .mysql) is defunct.

Regards

packet parse issue on < 5.7 mysql

Hi,

after speaking with tanner on the slack channel, i am opening this issue. I am getting strange errors with the Vapor Fluent 3 RC2, which did not happen on RC1. The error happens every time on all queries.

MySQL Version MAMP: 5.6.35

The database schema already exists, so i don't migrate. Here is the code:

configure

var databases = DatabaseConfig()
     let mysqlConfig = MySQLDatabaseConfig(hostname: "localhost",
                              port: 3306,
                              username: "root",
                              password: "",
                              database: "vapor_test")
    databases.add(database: MySQLDatabase(config: mysqlConfig), as: .mysql)
    databases.enableLogging(on: .mysql)
    services.register(databases)

ProjectStateController

func index( _ req: Request ) throws -> Future<[ProjectState]> {
        return try ProjectState.query(on: req).filter( \ProjectState.deleted == 0 ).all()
    }

ProjectState Model

final class ProjectState: MySQLModel {

    static let entity = "\( Database.tablePrefix )state"
    
    var id: Int?
    var state: String
    var sort: Int? = 0
    var deleted: Int? = 0
    
    init( id: Int? = nil, state: String, deleted: Int? = nil, sort: Int? ) {
        self.id = id
        self.state = state
        if let delete = deleted {
            self.deleted = delete
        }
        if let sortPos = sort {
            self.sort = sortPos
        }
    }
    
}

extension ProjectState: Content {}
extension ProjectState: Migration {}

routes

let projectStateController = ProjectStateController()
    router.get("projects_states", use: projectStateController.index )

Error:

[vapor_test] [2018-03-26 17:33:11 +0000] SELECT * FROM `intra_state` WHERE (`intra_state`.`deleted` = ?) ["integer8(0)"]
[Async] Warning: read triggered when input queue was empty, ignoring: columnDefinition41(MySQL.MySQLColumnDefinition41(catalog: "def", schema: "vapor_test", table: "intra_state", orgTable: "intra_state", name: "id", orgName: "id", characterSet: binary, columnLength: 11, columnType: MYSQL_TYPE_LONG, flags: MySQL.MySQLColumnFlags(raw: 16899), decimals: 0)).
[Async] Warning: read triggered when input queue was empty, ignoring: columnDefinition41(MySQL.MySQLColumnDefinition41(catalog: "def", schema: "vapor_test", table: "intra_state", orgTable: "intra_state", name: "state", orgName: "state", characterSet: utf8_general_ci, columnLength: 90, columnType: MYSQL_TYPE_VAR_STRING, flags: MySQL.MySQLColumnFlags(raw: 4097), decimals: 0)).
[Async] Warning: read triggered when input queue was empty, ignoring: columnDefinition41(MySQL.MySQLColumnDefinition41(catalog: "def", schema: "vapor_test", table: "intra_state", orgTable: "intra_state", name: "sort", orgName: "sort", characterSet: binary, columnLength: 6, columnType: MYSQL_TYPE_SHORT, flags: MySQL.MySQLColumnFlags(raw: 4097), decimals: 0)).
[Async] Warning: read triggered when input queue was empty, ignoring: columnDefinition41(MySQL.MySQLColumnDefinition41(catalog: "def", schema: "vapor_test", table: "intra_state", orgTable: "intra_state", name: "deleted", orgName: "deleted", characterSet: binary, columnLength: 1, columnType: MYSQL_TYPE_TINY, flags: MySQL.MySQLColumnFlags(raw: 1), decimals: 0)).
[Async] Warning: read triggered when input queue was empty, ignoring: eof(MySQL.MySQLEOFPacket(warningsCount: Optional(0), statusFlags: SERVER_STATUS_AUTOCOMMIT | SERVER_STATUS_NO_INDEX_USED)).
[Async] Warning: read triggered when input queue was empty, ignoring: ok(MySQL.MySQLOKPacket(affectedRows: 0, lastInsertID: Optional(4), statusFlags: , warningsCount: Optional(2816), info: "Akquisition\u{04}\0\0", sessionStateChanges: nil)).
[Async] Warning: read triggered when input queue was empty, ignoring: ok(MySQL.MySQLOKPacket(affectedRows: 0, lastInsertID: Optional(5), statusFlags: , warningsCount: Optional(1024), info: "test\u{05}\0\0", sessionStateChanges: nil)).
[Async] Warning: read triggered when input queue was empty, ignoring: ok(MySQL.MySQLOKPacket(affectedRows: 0, lastInsertID: Optional(6), statusFlags: , warningsCount: Optional(2560), info: "Beauftragt\u{06}\0\0", sessionStateChanges: nil)).
[Async] Warning: read triggered when input queue was empty, ignoring: ok(MySQL.MySQLOKPacket(affectedRows: 0, lastInsertID: Optional(7), statusFlags: , warningsCount: Optional(2304), info: "Storniert\u{07}\0\0", sessionStateChanges: nil)).
[Async] Warning: read triggered when input queue was empty, ignoring: eof(MySQL.MySQLEOFPacket(warningsCount: Optional(0), statusFlags: SERVER_STATUS_AUTOCOMMIT | SERVER_STATUS_NO_INDEX_USED)).

ColumnType VARCHAR BINARY

When "BINARY" is added as attribute to ColumnType name: "VARCHAR", target field in db is assigned base "VARCHAR" type instead of "BINARY".

MySQL database is provided by AWS - RDS.

I'm using:

builder.field(type: .varChar(length: 32, binary: true), for: \.id)

Unable to decode a UUID property stored as a string

I am having trouble with a model containing a UUID stored in MySQL as CHAR not BINARY.

`uuid` CHAR(36) NOT NULL

This implementation stores the property as a UUID, and uses custom encode/decode methods to
fetch and store the UUID as a string. Encoding works fine, the table contains the UUID correctly,
but decoding fails. Fetching the UUID as a string always returns the literal string "0".

final class FailModel: MySQLModel {
  var uuid: UUID

  enum CodingKeys: String, CodingKey {
    case uuid = "uuid"
  }

  init(from decoder: Decoder) throws {
    let container = try decoder.container(keyedBy: CodingKeys.self)
    let uuidString: String = try container.decode(String.self, forKey: .uuid)
    uuid = UUID(uuidString)! // fails here. Decoded `uuidString` is the literal string "0".
  }

  func encode(to encoder: Encoder) throws {
    var container = encoder.container(keyedBy: CodingKeys.self)
    try container.encode(uuid.uuidString, forKey: .uuid)
  }
}

This implementation stores the property as a string, and adds a function to forcibly convert
it to a UUID as needed. It works.

final class SuccessModel: MySQLModel {
  var uuidString: String

  func uuid() -> UUID {
    return UUID(uuidString)!
  }

  enum CodingKeys: String, CodingKey {
    case uuidString = "uuid"
  }

  init(from decoder: Decoder) throws {
    let container = try decoder.container(keyedBy: CodingKeys.self)
    uuidString = try container.decode(String.self, forKey: .uuidString)
  }

  func encode(to encoder: Encoder) throws {
    var container = encoder.container(keyedBy: CodingKeys.self)
    try container.encode(uuidString, forKey: .uuidString)
  }
}

I suspect the first version doesn't work because the CodingKey points to a
UUID property and even though I am telling the decoder to decode a String,
Fluent has already populated the decoder with binary UUID data. Is that true?

While the latter version works, it feels like a regression against Vapor 2 where
the model did not have to exactly reflect the database table structure, and custom
logic could be used in the load/save methods to transform between the two.

migration error

Moving vapor/vapor#1581 here

Remove ambiguous `date(_:optional:unique:default:)`

Environment

Vapor       : 2.0.0-beta.1
MySQLDriver : 2.0.0-beta.1
Swift       : 3.1
Xcode       : 8.3

Report

I got this compiler error:

Ambiguous use of 'date(_:optional:unique:default:)'
Found this candidate (Fluent.Builder)
Found this candidate (MySQLDriver.Builder)

when trying to compile this line:

builder.date("completed_on", optional: true)

utf8mb4 support

MySQLConnection+Authenticate.swift on line 54

change characterset hex from 0x21( utf8_general_ci)

to whatever hex code represents utf8mb4

Support saving String-backed, Codable-conforming enums to MySQL DB w/Fluent...

I am attempting to save a String-backed Codable-conforming enum, exposed as a property of a model struct, to a MySQL DB with Fluent.

It is failing with the error Cannot serialize Status to MySQLData.

The enum in question:

enum Status: String, Content, ReflectionDecodable, MySQLColumnDefinitionStaticRepresentable {
	static var mySQLColumnDefinition: MySQLColumnDefinition {
		return .varChar(length: 255)
	}

	case active = "active"
	case expired = "expired"
	case confirmed = "confirmed"

	static func reflectDecoded() throws -> (Status, Status) {
		return (.active, .expired)
	}
}

I've tried this both with and without conforming to MySQLColumnDefinitionStaticRepresentable.

I've checked a couple of times on Slack and nobody seems to have any idea about why this isn't working, so I'm assuming it's a bug.

Add support for .bit type

.bit type is currently unsupported

Column type fixes

Hey, first time using Vapor in a long time.

I've just submitted 2 PRs related to column types:

[MINOR] #76 which sets the length to 1 for Bool values when converted to TINYINT
[IMPORTANT] #79 which adds (is it a regression fix ?) attributes like UNSIGNED

multiple query start same time

for some reason one of the following snippets works and the other does not

does not work:

func acronymHandler(_ req: Request) throws -> Future<View> {
    return try req.parameter(Acronym.self).flatMap(to: View.self) { acronym in
        return try flatMap(to: View.self, acronym.creator.get(on: req), acronym.categories.query(on: req).all()) { creator, categories in
            let context = AcronymContext(title: acronym.long, acronym: acronym, creator: creator, categoires: categories)
            return try req.leaf().render("acronym", context)
        }
    }
}

works:

func acronymHandler(_ req: Request) throws -> Future<View> {
        return try req.parameter(Acronym.self).flatMap(to: View.self) { acronym in
            return acronym.creator.get(on: req).flatMap(to: View.self) { creator in
                return try acronym.categories.query(on: req).all().flatMap(to: View.self) { categories in
                    let context = AcronymContext(title: acronym.long, acronym: acronym, creator: creator, categoires: categories)
                    return try req.leaf().render("acronym", context)

                }
            }
        }
    }

thinking maybe this is caused by MySQL not supporting starting two queries at once?

Cannot convert value of type 'TCPSocketSource' to expected argument type 'SocketSource<_>'

I added FluentMySQL to my Vapor 3 beta project and now I can't build it because of error

Cannot convert value of type 'TCPSocketSource' to expected argument type 'SocketSource<_>'

Is there any way to fix it?

Revert does not delete fluent entry

Reverting a model does not delete the model from the fluent table which makes it impossible to prepare that table again because Fluent thinks it's already done

Specifying unsigned: true on a TINYINT causes a MySQL syntax error

Specifying unsigned: true on a TINY causes a MySQL syntax error

Code:

enum AccountType: Int, Codable, GraphQLAccessibleEnum {
    public static var mySQLColumnDefinition = MySQLColumnDefinition.tinyInt(unsigned: true)
    ....
}

Error:

Migrating mysql DB
⚠️ [MySQLError.server (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TINYINTUNSIGNED NOT NULL, `subtype` TINYINTUNSIGNED NOT NULL, `description` VARC' at line 1] [/Users/jseibert/api/.build/checkouts/mysql.git-1890032512239690518/Sources/MySQL/Connection/MySQLConnection.swift:42:69]
Program ended with exit code: 1

add MySQLEnum type

correct foreign key enable / disable

    /// See `SchemaSupporting`.
    public static func enableReferences(on conn: MySQLConnection) -> Future<Void> {
        return conn.raw("SET @@session.foreign_key_checks=1").run()
    }
    
    /// See `SchemaSupporting`.
    public static func disableReferences(on conn: MySQLConnection) -> Future<Void> {
        return conn.raw("SET @@session.foreign_key_checks=0").run()
    }

Prepare response has invalid status...

Migration fails while using this prepare function:

extension User: Migration {
    static func prepare(on connection: MySQLConnection) -> Future<Void> {
        return MySQLDatabase.create(self, on: connection) { builder in
            try builder.field(type: .varChar(length: 36), for: \.id, isOptional: false, isIdentifier: true)
            try builder.field(type: .varChar(length: 32), for: \.name)
            try builder.field(type: .varChar(length: 255), for: \.email)
...

Connection with the database is established and table fluent is created by default, but table users and others are not. The code was working as it should be in rc.1

model event + Int ID types

Support preset model ids and more integer types for autoincrment

Fatal error: Error raised at top level: ⚠️ MySQL Error: Unsupported auth plugin: caching_sha2_password

Hi,
I just change my database to MySQL and when I run my app Xcode stop and display this error:

My package.swit:

// swift-tools-version:4.0
import PackageDescription

let package = Package(
    name: "MyApp",
    dependencies: [
        .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0"),
        .package(url: "https://github.com/vapor/fluent-mysql.git", from: "3.0.0-rc"),
    ],
    targets: [
        .target(name: "App", dependencies: ["FluentMySQL", "Vapor"]),
        .target(name: "Run", dependencies: ["App"]),
        .testTarget(name: "AppTests", dependencies: ["App"])
    ]
)

MySQL launched with Docker:

docker run --name mysql -e MYSQL_USER=vapor  -e MYSQL_PASSWORD=password -e MYSQL_DATABASE=vapor  -p 3306:3306 -d mysql/mysql-server

fix last insert id when uuid/int id types mixed

Moving vapor/fluent#262 here.

Error due to Datetime(6) on migration MySQL v5

When a migration runs, it tries to create DATETIME fields with the format DATETIME(6). This creates an error in mySQL 5.5.11

Generates Error:
CREATE TABLE fluent (id BINARY(16) PRIMARY KEY, name VARCHAR(255) NOT NULL, batch BIGINT NOT NULL, createdAt DATETIME(6) , updatedAt DATETIME(6) )

Does not Generate Error:
CREATE TABLE fluent (id BINARY(16) PRIMARY KEY, name VARCHAR(255) NOT NULL, batch BIGINT NOT NULL, createdAt DATETIME , updatedAt DATETIME )

fluent when designing new table (while using UUID as ID) doesn’t make the primary column unique

custom mysql type protocols

Add support for MySQL...Type protocols like MySQLJSONType.

Related to #69 #71

Swift 4 manifest

The MySQL Driver package has no Package.swift/[email protected] manifest which causes warnings in Xcode saying Swift 4 conversion is available. It also looks like there are some redundant conformance constraints as well that should be fixed to remove warnings in Swift 4/3.2

redundant conformance constraint 'E': 'Entity'

"swift test" gives these four warnings:

Compile Swift Module 'MySQLDriver' (4 sources)
mysql-driver-master/Sources/MySQLDriver/MySQLConnection.swift:25:26: warning: redundant conformance constraint 'E': 'Entity'
public func query<E: Entity>(_ query: RawOr<Query>) throws -> Node {
^
mysql-driver-master/Sources/MySQLDriver/MySQLConnection.swift:25:43: note: conformance constraint 'E': 'Entity' inferred from type here
public func query<E: Entity>(_ query: RawOr<Query>) throws -> Node {
^
mysql-driver-master/Sources/MySQLDriver/MySQLConnection.swift:25:26: warning: redundant conformance constraint 'E': 'Entity'
public func query<E: Entity>(_ query: RawOr<Query>) throws -> Node {
^
mysql-driver-master/Sources/MySQLDriver/MySQLConnection.swift:25:43: note: conformance constraint 'E': 'Entity' inferred from type here
public func query<E: Entity>(_ query: RawOr<Query>) throws -> Node {

hash foreign key names

Moving vapor/fluent#318 here

additional string types

Mentioning vapor/fluent#439 here. FluentMySQL should offer some additional String-like types that have varying lengths.

`.max()` (and possibly other aggregation functions) throw an error on empty table for MySQL

This is mostly a re-posting of issue vapor/fluent#541, since it seems the issue is related to MySQL-specific parts of Fluent, not Fluent itself. Here’s a minimal repro case:

// Todo.swift
import FluentMySQL
import Vapor

final class Todo: MySQLModel {
    var id: Int?
    var name: String
    var order: Int

    init(_ name: String, _ order: Int) {
        self.name = name
        self.order = order
    }
}

extension Todo: Migration {}
extension Todo: Content {}
extension Todo: Parameter {}

// TodoController.swift
import Vapor

final class TodoController {
    func createHandler(
        _ req: Request,
        todoData: CreateTodoRequest
    ) throws -> Future<Todo> {
        return Todo.query(on: req)
            .max(\Todo.order)
            .catchMap { _ in 0 }
            .flatMap { maxOrder in
                let todo = Todo(todoData.name, maxOrder + 1)

                return todo.save(on: req)
        }
    }
}

struct CreateTodoRequest: Content {
    let name: String
}

// routes.swift
import Vapor

public func routes(_ router: Router) throws {
    let todoController = TodoController()
    router.post(CreateTodoRequest.self, at: "todos", use: todoController.createHandler)
}

This code attempts to find maximum value of the order field and set the value of newly created object to be one higher.

However, when sending a {"name": "Test"} request to localhost:8080/todos, everything goes well until code reaches the return todo.save(on: req) line; at that point, crash is emitted from the MySQL/Connection/MySQLConnection.swift:82 file, along with this error message:

Assertion failed: Attempting to call `send(...)` again before previous invocation has completed.

Package versions: Vapor 3.0.6, Fluent 3.0.0-rc.4.0.2, FluentMySQL 3.0.0-rc.4.0.3, MySQL 3.0.0-rc.4.3. MySQL itself is Docker image tagged as mysql:5.

Custom column representables support

extension MyCustomType: MySQLColumnTypeRepresentable { 
    static var mysqlColumnType: MySQL.ColumnType { return .varChar(64) } 
}

(Before it get's deleted: https://qutheory.slack.com/archives/C4QRYJ6DD/p1515520041000182 )

[RC3] Parallel requests no longer permitted?

With the latest RC3 changes, it looks like parallelizing operations is no longer permitted? What is the recommended way to act on an array of object and initiate N database requests?

cc @tanner0101

deleteForeignKey doesn't generate a valid SQL command

Per the conversation on vapor/fluent#297 regarding the deleteForeignKey(...) not working as expected.

After spending a bit of time trying to figure out why Fluent's deleteForeignKey(...) wouldn't delete foreign keys, I discovered that the actual SQL command isn't valid.

When using the database's modify command like so

try database.modify(Child.self) { builder in
    builder.deleteForeignKey("user_id, referencing: "id", on: User.self)
}

the SQL query generated returns

ALTER TABLE 'children' DROP 'user_id', DROP '_fluent_fk_children.user_id-users.id'

This isn't valid for deleting key constraints. The correct query should read

ALTER TABLE 'children' DROP 'user_id', DROP FOREIGN KEY '_fluent_fk_children.user_id-users.id'

MySQLError.tooManyParametersBound

MySQLError.tooManyParametersBound: More parameters were bound than specified in the query (PreparedStatement.swift:86)

//Package
.package(url: "https://github.com/vapor/vapor.git", from: "3.0.0-rc.1.1"), .package(url: "https://github.com/vapor/fluent-mysql", from: "3.0.0-rc.1.1"), .package(url: "https://github.com/vapor/leaf.git", from:"3.0.0-rc.1"), .package(url:"https://github.com/vapor/auth.git", from:"2.0.0-rc"),

//Model

 final class User:Codable, CustomStringConvertible {
    var description: String {
        return "\(String(describing: self.username)), \(self.password), \(String(describing: createdAt)) \(String(describing: updatedAt))"
    } 
    
    
   
    var username:String?
    var password:String
    var createdAt: Date?
    var updatedAt: Date?
    
init( username:String?=nil, password:String) {
        //self.id = id
        self.username = username
        self.password = password
    }

}

extension User: Model,Timestampable {
    public static var createdAtKey: CreatedAtKey {return \.createdAt }
    public static var updatedAtKey: UpdatedAtKey { return \.updatedAt}

    typealias Database = MySQLDatabase
    
    public typealias ID = String
    public static var idKey: IDKey{ return \.username }
    

}


extension User: Migration {
    public static func prepare(on connection: Database.Connection) -> Future<Void> {
        return Database.create(self, on: connection, closure: { (builder) in
            try builder.field(for: \User.username)
            try builder.field(for: \User.password)
            try builder.field(for: \User.createdAt)
            try builder.field(for: \User.updatedAt)
        })
    }
}

extension User: Content { }
extension User: Parameter { }

//Controller

final class UserController {
    func crear(_ req: Request) throws -> Future<User> {
     return try req.content.decode(User.self).flatMap(to: User.self) { user in
        
        return user.save(on: req)
    }
 }
}

//Route

public func routes(_ router: Router) throws {
    let userController = UserController()
    router.post("crear", use:userController.crear)
    
}

server is badly crashing when the create SQL for a migrated table exceeds a specific limit

I have a a following model.
When the server wants to migrate the table into empty database it is crashing deep down in async.
When exclude some fields from the prepare method it is creating without crash.
The crash or success depends on length of excluded fieldname if i exclude long field name or more fields then it will not crash, if I exclude only one short field it crashing.

import Foundation
import FluentMySQL
import Vapor


final class CrashTest : MySQLModel {
    
    static let entity = "crashtest"
    
    static let idKey  = \CrashTest.profile_id
    
        // MARK: Properties start
    
    var profile_id: UUID?
    var device_id: String
    var created: Date?
    var email: String?
    var terms_date: Date?
    var premium_from: Date?
    var premium_until: Date?
    var laty: Double?
    var lngx: Double?
    var location_date: Date?
    var last_seen: Date?
    var last_avatar: Date?
    var report_count: Int
    var report_count_overall: Int
    var report_by: String?
    var logged_in: Bool
    var gender: String?
    var search_gender: String?
    var likes_out: String?
    var likes_in: String?
    var match_list: String?
    var ignore_list: String?
    var blocked_users: String?
    var blocked_by: String?
    var premium_pushed: Bool
    var invite_used: Bool
    var poke_sent: Date?
    var whatsup_balance: Bool
    var gift_period_until: Date?
    var token: String?
    var time_zone: String?
    var badge: Int
    var incognito: Bool
    var incognito_text: String?
    var area_notifications: Bool
    var sales: Bool
    var source_token: String?
    var source_place: UUID?
    var source_partner: UUID?
    var source_identified: Bool
    

    
    // MARK: init
    init(profile_id: UUID? = nil,
        device_id: String,
        created: Date? = nil,
        email: String? = nil,
        terms_date: Date? = nil,
        premium_from: Date? = nil,
        premium_until: Date? = nil,
        laty: Double? = nil,
        lngx: Double? = nil,
        location_date: Date? = nil,
        last_seen: Date? = nil,
        last_avatar: Date? = nil,
        report_count: Int = 0,
        report_count_overall: Int = 0,
        report_by: String? = nil,
        logged_in: Bool = true,
        gender: String? = nil,
        search_gender: String? = nil,
        likes_out: String? = nil,
        likes_in: String?  = nil,
        match_list: String? = nil,
        ignore_list: String? = nil,
        blocked_users: String? = nil,
        blocked_by: String?  = nil,
        premium_pushed: Bool = false,
        invite_used: Bool  = false,
        poke_sent: Date? = nil,
        whatsup_balance: Bool = false,
        gift_period_until: Date? = nil,
        token: String? = nil,
        time_zone: String? = nil,
        badge: Int = 0,
        incognito: Bool = false,
        incognito_text: String? = nil,
        area_notifications: Bool = true,
        sales: Bool = false,
        source_token: String? = nil,
        source_place: UUID? = nil,
        source_partner: UUID? = nil,
        source_identified: Bool = false)
    {
        
        self.profile_id  = profile_id
        self.device_id = device_id
        self.created = created
        self.email = email
        self.terms_date = terms_date
        self.premium_from = premium_from
        self.premium_until = premium_until
        self.laty = laty
        self.lngx = lngx
        self.location_date = location_date
        self.last_seen = last_seen
        self.last_avatar = last_avatar
        self.report_count = report_count
        self.report_count_overall = report_count_overall
        self.report_by = report_by
        self.logged_in = logged_in
        self.gender = gender
        self.search_gender = search_gender
        self.likes_out = likes_out
        self.likes_in = likes_in
        self.match_list = match_list
        self.ignore_list = ignore_list
        self.blocked_users = blocked_users
        self.blocked_by = blocked_by
        self.premium_pushed = premium_pushed
        self.invite_used = invite_used
        self.poke_sent = poke_sent
        self.whatsup_balance = whatsup_balance
        self.gift_period_until = gift_period_until
        self.token = token
        self.time_zone  = time_zone
        self.badge = badge
        self.incognito = incognito
        self.incognito_text = incognito_text
        self.area_notifications = area_notifications
        self.sales = sales
        self.source_token = source_token
        self.source_place = source_place
        self.source_partner = source_partner
        self.source_identified = source_identified
    }

}

extension CrashTest: Migration {
    static func prepare(on connection: MySQLConnection) -> Future<Void> {
        return MySQLDatabase.create(self, on: connection) { builder in
            builder.field(type: .varChar(length: 64, binary: true),     for: \CrashTest.profile_id)
            builder.field(type: .varChar(length: 128),                  for: \CrashTest.device_id, isOptional: false)
            builder.field(type: .datetime(),                            for: \CrashTest.created ,isOptional: true)
            builder.field(type: .varChar(length: 100),                  for: \CrashTest.email, isOptional: true)
            builder.field(type: .datetime(),                            for: \CrashTest.terms_date, isOptional: true)
            builder.field(type: .datetime(),                            for: \CrashTest.premium_from, isOptional: true)
            builder.field(type: .datetime(),                            for: \CrashTest.premium_until, isOptional: true)
            builder.field(type: .double(),                              for: \CrashTest.laty, isOptional: true)
            builder.field(type: .double(),                              for: \CrashTest.lngx, isOptional:true)
            builder.field(type: .datetime(),                            for: \CrashTest.location_date, isOptional: true)
            builder.field(type: .datetime(),                            for: \CrashTest.last_seen, isOptional:true )
            builder.field(type: .datetime(),                            for: \CrashTest.last_avatar, isOptional: true)
            builder.field(type: .int64(),                               for: \CrashTest.report_count, isOptional: false)
            builder.field(type: .int64(),                               for: \CrashTest.report_count_overall, isOptional: false)
            builder.field(type: .text(),                                for: \CrashTest.report_by, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.logged_in, isOptional: false)
            builder.field(type: .varChar(length:1),                     for: \CrashTest.gender, isOptional: true)
            builder.field(type: .varChar(length:1),                     for: \CrashTest.search_gender, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.likes_out, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.likes_in, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.match_list, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.ignore_list, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.blocked_users, isOptional: true)
            builder.field(type: .text(),                                for: \CrashTest.blocked_by, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.premium_pushed, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.invite_used, isOptional: false)
            builder.field(type: .datetime(),                            for: \CrashTest.poke_sent, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.whatsup_balance, isOptional: false)
            builder.field(type: .datetime(),                            for: \CrashTest.gift_period_until, isOptional: true)
            builder.field(type: .varChar(length: 15),                   for: \CrashTest.token, isOptional: true)
            builder.field(type: .varChar(length: 8),                    for: \CrashTest.time_zone, isOptional: true)
            builder.field(type: .int64(),                               for: \CrashTest.badge, isOptional: false)
            builder.field(type: .int8(),                                for: \CrashTest.incognito, isOptional: false)
            builder.field(type: .text(),                                for: \CrashTest.incognito_text, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.area_notifications, isOptional: false)
            builder.field(type: .int8(),                                for: \CrashTest.sales, isOptional: false)
            builder.field(type: .varChar(length: 20),                   for: \CrashTest.source_token, isOptional: true)
            builder.field(type: .varChar(length: 64, binary: true),     for: \CrashTest.source_place, isOptional: true)
            builder.field(type: .varChar(length: 64, binary:true),      for: \CrashTest.source_partner, isOptional: true)
            builder.field(type: .int8(),                                for: \CrashTest.source_identified, isOptional: false)
        }
    }
}

extension CrashTest: Content {}

extension CrashTest: Parameter {}

.filter(.field == nil) not working anymore

When using something like:

.filter(\field == nil)

the MySQL Output becomes "field == null", which really needs to be "field IS NULL".

Custom ID not allow saving Model on Database table

// User Model

    var username:String?
    var password:String
    
    init(username:String? = nil, password:String){
        self.username = username
        self.password = password
    }
} 

extension User:Model{
    static var idKey: IDKey { return \User.username }
    typealias Database = MySQLDatabase
    typealias ID = String
}


extension User:Migration{
     static func prepare(on connection: Database.Connection) -> FutureVoid{
        return  Database.create(User.self, on: connection, closure: { builder in
            try builder.field(for: \User.username)
            try builder.field(for: \User.password)
            try builder.field(for: \User.creado)
            try builder.field(for: \User.actualizado)
        })
    }

}  ```


//At first Vapor run to Migrate model to Database, show some log issues, the tables are created 

``` [ INFO ] Migrating 'eccdatabase' database (FluentProvider.swift:28)
[ INFO ] Preparing migration 'User' (MigrationContainer.swift:50)
[Async] Warning: read triggered when input queue was empty, ignoring: ok(MySQL.MySQLOKPacket(affectedRows: 1, lastInsertID: Optional(0), statusFlags: SERVER_STATUS_AUTOCOMMIT, warningsCount: Optional(0), info: "", sessionStateChanges: nil)).
[ INFO ] Migrations complete (FluentProvider.swift:32)

// Controller

final class UserController{
    func crear(_ req:Request) throws -> Future<User> {
        return try req.content.decode(User.self).flatMap(to: User.self, { (usuario) -> EventLoopFuture<User> in
            return usuario.save(on: req)
        })
    }
}

//trying to Save User on table
when attempt to post {username:"[email protected]", password:"12345"} the model in not saved on table Users.

//If MySQLUUIDModel is implementes instead custom Model it works perfectly!

Generated index names are almost always too long

In MySQL, index names must be 64 characters or less. However, FluentMySQL does not enforce or prepare for this, causing fatal errors:

Migrating mysql DB
⚠️ [MySQLError.server (1059): Identifier name '_fluent_index_financial_accounts_financialProviderId_plaidAccountId' is too long] [/Users/jseibert/api/.build/checkouts/mysql.git-1890032512239690518/Sources/MySQL/Connection/MySQLConnection.swift:42:69]
Program ended with exit code: 1

SchemaIndex should support manually overriding the name, or it should change to a more intelligent name synthesis scheme.

`property ~~ []` SQL Syntax is Broken

When you try run a query that gets models based on the elements in an array and the array is empty, like this:

MyModel.query(on: request).filter(\.id ~~ [])

Instead of generating a WHERE (false) statement, it creates

WHERE (`mymodels`.`id` false)

Add support for .timestamp type

Dramatic decrease of performance on Ubuntu

Hi,
I have a very weird behavior .... my raw mysql calls take on mac just 0.02 seconds but on my ubuntu server it takes over 400 seconds ...

Log messages:

on my mac: 
[ DEBUG ] pluginHandler Get Plugin informations (WebAppController.swift:230)
[ DEBUG ] pluginHandler Finished within 0.0155929327011108 seconds (WebAppController.swift:242)
on my server (ubuntu): 
[ DEBUG ] pluginHandler Get Plugin informations (WebAppController.swift:230)
[ DEBUG ] pluginHandler Finished within 429.439689993858 seconds (WebAppController.swift:242)

It seems, that I just happened if I use raw sql queries like in this code:
https://gist.github.com/DanielsCode/ed2af6375acd8eb8ee932982e88f38a9

If I execute this sql statement via a sql console I will get a response in milliseconds.

Looking forward to get some feedback.

Cheers,
Daniel

use varBinary for UUID

This will allow uuid to work better with mysql builtin methods