Tables

The Table class in Weasel.MySql.Tables provides a fluent API for defining MySQL tables with columns, primary keys, foreign keys, indexes, and partitioning.

Defining a Table

var table = new Table("mydb.users");

table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
table.AddColumn<string>("name").NotNull();
table.AddColumn<string>("email").NotNull().AddIndex(idx => idx.IsUnique = true);
table.AddColumn<DateTime>("created_at");

^{snippet source | anchor}

The part before the dot is the MySQL database. A name without one is placed in the provider's default schema, public -- a database of that name, not the connection's database -- so name the database the table belongs in. See Schemas are databases.

Table Options

MySQL tables support engine, character set, and collation configuration:

table.Engine = "InnoDB";       // default
table.Charset = "utf8mb4";
table.Collation = "utf8mb4_unicode_ci";

^{snippet source | anchor}

Column Configuration

The AddColumn method returns a ColumnExpression with these options:

• AsPrimaryKey() -- marks the column as part of the primary key
• AutoNumber() -- adds AUTO_INCREMENT to the column
• NotNull() / AllowNulls() -- controls nullability
• DefaultValue(value) -- sets a default value
• DefaultValueByExpression(expr) -- sets a default using a SQL expression
• AddIndex(configure?) -- adds a standard index
• AddFulltextIndex(configure?) -- adds a FULLTEXT index
• AddSpatialIndex(configure?) -- adds a SPATIAL index
• ForeignKeyTo(table, column) -- adds a foreign key constraint

Index Direction

IndexDefinition.SortOrder = SortOrder.Desc makes every key column descending. For an index that mixes directions, name the descending columns instead:

var index = new IndexDefinition("idx_triggers_acquire")
{
    Columns = ["sched_name", "trigger_state", "next_fire_time", "priority", "misfire_instr"]
};
index.DescendingColumns.Add("priority");
// CREATE INDEX `idx_triggers_acquire` ON ... (`sched_name`, `trigger_state`, `next_fire_time`, `priority` DESC, `misfire_instr`);

Direction is read back per column from information_schema.STATISTICS.COLLATION and compared, so an index whose direction differs from the model is rebuilt. A name in DescendingColumns that is not a key column throws. MySQL 5.7 parses DESC and ignores it, so declare a direction only against 8.0 or later.

Partitioning

MySQL tables support Range, Hash, List, and Key partitioning:

table.PartitionByRange("created_at");
table.PartitionByHash("id");
table.PartitionByList("region");
table.PartitionByKey("id");
table.PartitionCount = 4;  // for Hash or Key strategies

^{snippet source | anchor}

Delta Detection

await using var conn = new MySqlConnection(connectionString);
await conn.OpenAsync();

var delta = await table.FindDeltaAsync(conn);

^{snippet source | anchor}

A column type is compared as information_schema.COLUMNS.COLUMN_TYPE reports it, so a synonym in the model is not drift:

Declared	Compared as
BOOL, BOOLEAN	tinyint(1)
INTEGER, INT1–INT8, MIDDLEINT	int, tinyint, smallint, mediumint, bigint
NUMERIC, DEC, FIXED	decimal
DOUBLE PRECISION, REAL, FLOAT8, FLOAT(25..53)	double
CHARACTER, NCHAR, NATIONAL CHAR	char
CHARACTER VARYING, VARCHARACTER, NVARCHAR, NCHAR VARYING, NATIONAL VARCHAR	varchar
LONG, LONG VARCHAR / LONG VARBINARY	mediumtext / mediumblob

UNSIGNED, ZEROFILL and a character length are compared. A display width (int(11)), a decimal's precision and scale, a character set and a collation are not. REAL is double unless the server runs with the REAL_AS_FLOAT SQL mode.

Generating DDL

var migrator = new MySqlMigrator();
var writer = new StringWriter();
table.WriteCreateStatement(migrator, writer);

^{snippet source | anchor}

The output uses CREATE TABLE IF NOT EXISTS with backtick-quoted identifiers.