Support encoding, and collation for database, table, and column

[milkcocoa]

Hi! MySQL supports encoding, and collation for each database, table, and column. Is there any plan to support them?

For example, in Ruby on Rails, database.yml application-wide configuration sets the database encoding, and collation, and in the migration file, option parameter of the create_table method sets the table ones, and also each column has encoding, and collation option to set them.

I want the database configuration, and the option for each column at least, because I don't like to depend on the MySQL system configurations, and a column collation particularly depends on its use-case because MySQL collation has some issues on the emojis used or the Japanese environment.

You can see the detail of that issue at the following URL.
https://bugs.mysql.com/bug.php?id=76553

So, considering the Japanese environment, I'd like to use different collations between email, and texts.

[tanner0101]

Hi @milkcocoa, thanks for the feature request. If I understand correctly, there are two separate issues here:

1: Configuring character set for a given MySQL connection.

When connecting to a MySQL server, the client must send the default character set it wishes to use. This currently defaults to .utf8mb4. We could make this configurable in MySQLNIO and then expose it via the MySQLConfiguration struct.

2: Configuring character set for database, tables, and columns.

I'm assuming you are using Fluent here since MySQLKit itself (built on SQLKit) does not explicitly support migrations (although you could implement them).

Database

Fluent is not responsible for creation of the database. Unless I'm mistaken, choosing a database-wide character set should be handled when you create the database. If you use Docker for example, you could configure that like so: https://stackoverflow.com/questions/45729326/how-to-change-the-default-character-set-of-mysql-using-docker-compose

Table

Fluent is responsible for creating tables. There is currently no way to specify character set or collate using database.schema. As a workaround, you can use custom SQL.

For example:

import FluentSQL
import MySQLKit

let builder = database.schema("planets").id()
if database is MySQLDatabase {
    // The underlying database driver is MySQL.
    builder.constraint(.sql(raw: "CHARACTER SET charset_name"))
    builder.constraint(.sql(raw: "COLLATE collation_name"))
} else {
    // The underlying database driver is _not_ MySQL.
}
builder.create()

Column

There is currently no way to specify character set or collate using Fluent. As a workaround, you can use custom SQL here, too.

import FluentSQL
import MySQLKit

let builder = database.schema("planets").id()
if database is MySQLDatabase {
    // The underlying database driver is MySQL.
    builder.field(
        "name", 
        .string,
        .required, 
        .sql(raw: "CHARACTER SET charset_name"), 
        .sql(raw: "COLLATE collation_name")
    )
} else {
    // The underlying database driver is _not_ MySQL.
    builder.field("name", .string, .required)
}
builder.create()

---

Since the concept of CHARACTER SET / COLLATE is unique to MySQL, it probably doesn't make sense to add these options to Fluent itself. Unless the other databases Fluent supports have similar things that I'm not aware of.

However, some things we could do are:

• Add these examples to the documentation
• Add convenience APIs for MySQL in particular, for example:

if database is MySQLDatabase {
    // The underlying database driver is MySQL.
    builder.field("name", .string, .required, .mysql(characterSet: .utf8mb4))
}

Let me know if this makes sense and then we can split out these issues into their respective repos.

[milkcocoa]

Hi @tanner0101, thanks for replying to my request, and your understand is correct. There are two separate issues.

For the first issue, if MySQLConfiguration struct provides the configurations, that would be great!

For the second, I think Database issue is resolved by the first connection issue.

About Table, and Column, it makes sense that Fluent doesn't have the Fluent-wide resolution, and I understand the workarounds. I'm grad that the documentation has the examples. Moreover, if Fluent has convenience APIs, it is great useful.

[tanner0101]

@milkcocoa sounds good. Do you want to open the separate issues?

1: Configuring character set for a given MySQL connection (vapor/mysql-nio first).
2: Convenience APIs for configuring character set for database, tables, and columns (vapor/fluent-mysql-driver).

Also let me know if you want to provide PRs. Otherwise I will grab them eventually. Thanks!

[milkcocoa]

OK. I've split this into vapor/mysql-nio#55 and vapor/fluent-mysql-driver#194.

Also I'll take a look at the codes, and provide PRs if I can.