CodeLogic.MySQL2 4.6.81

This package has a SemVer 2.0.0 package version: 4.6.81+ed53802.

dotnet add package CodeLogic.MySQL2 --version 4.6.81

NuGet\Install-Package CodeLogic.MySQL2 -Version 4.6.81

This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.

<PackageReference Include="CodeLogic.MySQL2" Version="4.6.81" />

For projects that support PackageReference, copy this XML node into the project file to reference the package.

<PackageVersion Include="CodeLogic.MySQL2" Version="4.6.81" />
                    

                            Directory.Packages.props

<PackageReference Include="CodeLogic.MySQL2" />
                    

                            Project file

For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.

paket add CodeLogic.MySQL2 --version 4.6.81

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

#r "nuget: CodeLogic.MySQL2, 4.6.81"

#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.

#:package CodeLogic.MySQL2@4.6.81

#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.

#addin nuget:?package=CodeLogic.MySQL2&version=4.6.81
                    

                            Install as a Cake Addin

#tool nuget:?package=CodeLogic.MySQL2&version=4.6.81
                    

                            Install as a Cake Tool

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

CodeLogic.MySQL2

A typed MySQL data layer for CodeLogic 4: repositories, LINQ-shaped SQL, cursor paging, schema synchronization, migrations, caching, resilience, and operational diagnostics in one library.

CodeLogic.MySQL2 sits between a micro-ORM and a lightweight application data platform. Map ordinary C# classes with attributes, then use repositories or a fluent query builder while the library handles parameterized SQL, compiled row materialization, schema drift, cache invalidation, retries, health checks, and events.

It is built on MySqlConnector and supports MySQL, MariaDB, and Percona. Fallible operations return CodeLogic Result<T> values so expected failures can be handled without exception-driven control flow.

What the library covers

Area	Capabilities
Entity persistence	CRUD repositories, batch insert, upsert, batch upsert, insert-or-increment, atomic counters, soft and hard delete.
Querying	Typed filters, string and collection predicates, ordering, offset paging, cursor paging, subqueries, typed and raw joins, projections, grouping, aggregates, and set-based writes.
Schema management	Attribute-driven tables and columns, type inference, keys, foreign keys, indexes, covering indexes, column renames, schema diffing, and three synchronization modes.
Migrations	Versioned up/down migrations, discovery, pending plans, checksums, rollback preflight, migration tracking, and a cross-node schema lock.
Data lifecycle	Soft-delete filtering, retention-based background purging, schema backup, and schema restore.
Performance	Compiled materializers, projection pushdown, batched writes, result caching, single-flight misses, warm smart-cache pools, and time-quantized cache keys.
Reliability	Connection pooling, named databases, explicit transactions, deadlock/lock-timeout retry, command cancellation, and health checks.
Operations	Query timing, slow-query detection, optional `EXPLAIN FORMAT=JSON`, N+1 detection, cache statistics, pool statistics, and CodeLogic events.
Escape hatches	Parameterized raw queries, commands, scalar reads, direct connection access, and pluggable cache/coordinator interfaces.

Install

dotnet add package CodeLogic.MySQL2

Quick start

using CL.MySQL2;
using CL.MySQL2.Models;
using CL.MySQL2.Services;
using CodeLogic;
using CodeLogic.Core.Results;

[Table(Name = "users")]
public class User
{
    [Column(Name = "id", DataType = DataType.BigInt, Primary = true, AutoIncrement = true)]
    public long Id { get; set; }

    [Column(Name = "email", DataType = DataType.VarChar, Size = 160, NotNull = true, Unique = true, Index = true)]
    public string Email { get; set; } = "";

    [Column(Name = "created_utc", DataType = DataType.DateTime, NotNull = true)]
    public DateTime CreatedUtc { get; set; } = DateTime.UtcNow;
}

await Libraries.LoadAsync<MySQL2Library>();
await CodeLogic.ConfigureAsync();
await CodeLogic.StartAsync();

var mysql = Libraries.Get<MySQL2Library>()!;

// Reconcile the table with the mapped model.
Result<SyncResult> sync = await mysql.SyncTableAsync<User>();

// Repository persistence.
Repository<User> users = mysql.GetRepository<User>();
Result<User> inserted = await users.InsertAsync(
    new User { Email = "ada@example.com" });

// Typed query translated and executed in MySQL.
Result<List<User>> recent = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(u => u.CreatedUtc)
    .Take(20)
    .WithCache(TimeSpan.FromMinutes(1))
    .ToListAsync();

Configuration files are generated on first run. Add the connection details to config.mysql.json, restart, and the named connection is ready for repositories, queries, schema sync, and migrations.

Entity mapping and schema

The model is the schema source of truth. CL.MySQL2.Models provides attributes for the table shape and lifecycle:

[Table] controls the table name, engine, charset, collation, and comment.
[Column] controls the physical name, type, length, precision, scale, primary/auto-increment flags, nullability, uniqueness, indexing, defaults, unsigned values, charset, comments, and binary storage.
[ForeignKey], [Index], and [CompositeIndex] describe constraints and single, composite, unique, or covering indexes.
[Ignore] excludes a property from persistence.
[SoftDelete] marks a nullable timestamp used for automatic read filtering and repository soft deletes.
[RetainDays] enables scheduled batch deletion of expired rows.
PreviousName on [Column] performs an in-place column rename so existing data is preserved.

Properties without [Column] use CLR type inference and their property name as the column name. When [Column] is used for explicit mapping, its DataType selects from the MySQL integer, decimal, floating point, bit, character, text, binary/blob, date/time, enum/set, JSON, and geometry families. StorageType.Binary can store a Guid as BINARY(16); SequentialGuid.NewId() creates time-ordered UUIDv7 values suitable for indexed primary keys.

Schema synchronization modes

Mode	Intended environment	Behavior
`Production`	Normal production operation	Additive reconciliation; never drops. Destructive drift is reported through `DriftPending`.
`Developer`	Local and disposable environments	Fully reconciles the model, including removed columns, indexes, and foreign keys.
`Migration`	Deliberate production maintenance	Performs a backed-up, one-shot destructive reconciliation, then becomes a no-op once current.

Result<Dictionary<string, SyncResult>> schema = await mysql.SyncSchemaAsync(
    typeof(User), typeof(Order), typeof(Customer));

mysql.SetSyncMode(SyncMode.Production, connectionId: "Default");

Every desired schema is hashed into __schema_state. An unchanged model takes the CRC fast path and skips information_schema inspection and DDL entirely. SyncResult reports operations, errors, duration, CRC, whether work was skipped, and whether destructive drift remains pending.

Repository API

mysql.GetRepository<T>(connectionId) provides the conventional persistence surface:

Operation	Purpose
`InsertAsync` / `InsertManyAsync`	Insert one row with its generated key populated, or insert chunked batches and return the affected count.
`UpsertAsync` / `UpsertManyAsync`	Insert or update on duplicate key.
`UpsertWithIncrementsAsync`	Insert a seed or atomically accumulate selected numeric columns.
`GetByIdAsync` / `GetByColumnAsync` / `GetAllAsync` / `FindAsync`	Typed entity retrieval.
`GetPagedAsync`	Page-number/offset paging with totals.
`CountAsync`	Count table rows.
`UpdateAsync`	Update an entity by its mapped primary key.
`IncrementAsync` / `DecrementAsync` / `AdjustAsync`	Atomic server-side counter changes.
`DeleteAsync`	Soft delete when `[SoftDelete]` is present; otherwise physically delete.
`HardDeleteAsync`	Always physically delete.

Query builder

mysql.Query<T>() translates supported expression trees into parameterized SQL. Filtering and materialization remain server-side; the library does not load rows and apply LINQ in memory.

string[] countries = ["DK", "SE", "NO"];

Result<List<Order>> orders = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .Where(o => o.Total >= 100 && countries.Contains(o.Country))
    .Where(o => o.Reference.StartsWith("WEB-"))
    .OrderByDescending(o => o.CreatedUtc)
    .ToListAsync();

Supported filters include comparisons, boolean composition, negation, null checks, captured values, string Contains/StartsWith/EndsWith, and collection Contains translated to IN (...).

Subqueries

var shipped = await mysql.Query<Order>()
    .WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent")
    .ToListAsync();

var vipOrders = await mysql.Query<Order>()
    .WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip)
    .ToListAsync();

WhereExists, WhereNotExists, WhereIn, and WhereNotIn generate SQL subqueries and compose with normal filters.

Ordering and pagination

Offset paging returns totals and page-number metadata:

Result<PagedResult<Order>> page = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .ToPagedListAsync(page: 1, pageSize: 25);

Cursor paging performs a keyset seek without OFFSET or COUNT(*), making it the better fit for deep or frequently changing result sets:

Result<CursorPagedResult<Order>> first = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .ToCursorPagedListAsync(pageSize: 25);

Result<CursorPagedResult<Order>> next = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .After(first.Value!.NextCursor)
    .ToCursorPagedListAsync(pageSize: 25);

Cursor ordering supports multiple ASC/DESC and nullable columns. A mapped primary key is appended automatically as a stable tie-breaker. Continuation tokens are versioned Base64URL JSON bound to the entity/table and exact ordering; they are opaque paging state, but they are not signed or encrypted. Encoded tokens longer than 4,096 characters are rejected before decoding.

Joins, projections, grouping, and aggregates

Typed joins support inner, left, and right equi-joins, composite keys, two-entity filters and ordering, and compiled projection into a DTO:

Result<List<OrderView>> views = await mysql.Query<Order>()
    .Where(o => o.Total > 100)
    .Join<Customer, long, OrderView>(
        o => o.CustomerId,
        c => c.Id,
        (o, c) => new OrderView { OrderId = o.Id, Customer = c.Name, Total = o.Total },
        JoinType.Left)
    .Where((o, c) => c.IsVip)
    .OrderByDescending((o, c) => o.Total)
    .ToListAsync();

Projection pushdown selects only referenced columns, while grouped projections translate aggregate operations to SQL:

Result<List<DailyTotal>> totals = await mysql.Query<Order>()
    .Where(o => o.CreatedUtc >= DateTime.UtcNow.AddDays(-30))
    .GroupBy(o => o.Day)
    .Select(g => new DailyTotal
    {
        Day = g.Key,
        Count = g.Count(),
        Revenue = g.Sum(o => o.Total),
        Average = g.Average(o => o.Total)
    })
    .ToListAsync();

Single-value terminals include CountAsync, MinAsync, MaxAsync, SumAsync, and AverageAsync. Select(...) also supports anonymous or DTO projections without first materializing the entity.

Set-based updates and deletes

Result<int> updated = await mysql.Query<Order>()
    .Where(o => o.Status == "draft")
    .UpdateAsync(o => new Order
    {
        Status = "open",
        UpdatedUtc = DateTime.UtcNow
    });

Result<int> deleted = await mysql.Query<Order>()
    .Where(o => o.CreatedUtc < DateTime.UtcNow.AddYears(-3))
    .DeleteAsync();

These operations issue one server-side statement and do not materialize matching rows.

Raw SQL, transactions, and multiple databases

Use the raw SQL APIs when a query is outside the typed builder's scope. Values remain parameterized and executions still flow through retries and observability:

Result<List<User>> rows = await mysql.SqlQueryAsync<User>(
    "SELECT * FROM users WHERE email LIKE @pattern",
    new Dictionary<string, object?> { ["@pattern"] = "%@example.com" });

Result<int> affected = await mysql.ExecuteSqlAsync(
    "UPDATE users SET verified = 1 WHERE id = @id",
    new Dictionary<string, object?> { ["@id"] = 42L });

Result<long?> count = await mysql.SqlScalarAsync<long>("SELECT COUNT(*) FROM users");

BeginTransactionAsync returns an async-disposable transaction that rolls back automatically unless committed. Bind repositories or query builders to it through their transaction-aware constructors:

await using TransactionScope tx = await mysql.BeginTransactionAsync();
var accounts = new Repository<Account>(
    mysql.ConnectionManager, logger: null, transactionScope: tx);

await accounts.AdjustAsync(1L, a => a.Balance, -100m);
await accounts.AdjustAsync(2L, a => a.Balance, 100m);
await tx.CommitAsync();

Configure multiple named database connections and select one through GetRepository<T>(connectionId), Query<T>(connectionId), the raw SQL connectionId argument, or .WithConnection(connectionId).

Migrations, backups, and data lifecycle

Declarative sync handles structural model drift. Imperative IMigration implementations handle seeds, backfills, data transforms, and semantic changes that a schema diff cannot infer.

public sealed class SeedRoles() : Migration("1.4.0", 1, "Seed default roles")
{
    public override Task UpAsync(IMigrationContext context, CancellationToken ct) =>
        context.ExecuteAsync(
            "INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);

    public override Task DownAsync(IMigrationContext context, CancellationToken ct) =>
        context.ExecuteAsync(
            "DELETE FROM roles WHERE name IN ('admin', 'user')", ct: ct);
}

mysql.RegisterMigration(new SeedRoles())
     .RegisterMigrationsFrom(typeof(Program).Assembly);

IReadOnlyList<MigrationPlanItem> pending = await mysql.GetPendingMigrationsAsync();
Result<MigrationRunResult> applied = await mysql.MigrateAsync();

Migrations run in version/order sequence, are tracked in __migrations, verify checksums, and execute under the same cross-node lock as schema sync. RollbackAsync(target) preflights the complete range before running DownAsync newest-first.

Before destructive schema reconciliation, the backup manager writes DDL snapshots. RestoreSchemaAsync can replay the latest or a named snapshot and then clears the CRC state so the next sync performs a full comparison. These are schema backups only; they do not preserve table rows.

For row lifecycle management, [SoftDelete] changes repository deletion into a timestamp update and filters ordinary reads by default. .IncludeDeleted() opts a query back into those rows. [RetainDays] registers an entity for background batch purging based on its timestamp column.

Caching and performance

Cache-aside queries

Result<List<User>> cached = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-30))
    .WithCache(TimeSpan.FromMinutes(5))
    .ToListAsync();

Cache keys include the connection, SQL, and parameters.
Table version stamps invalidate cached results after repository or query-builder mutations.
Concurrent misses for one key collapse into a single database execution.
Near-current DateTime parameters can be quantized so rolling-window queries reuse cache entries.
ICacheStore and ICacheCoordinator provide seams for shared stores, cross-node invalidation, and refresh leases.

Smart cache pools

SmartCachePool dashboard = mysql.RegisterCachePool(
    name: "dashboard",
    refreshEvery: TimeSpan.FromSeconds(30),
    maxIdleFires: 10);

Result<List<User>> warm = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-1))
    .SmartCache("dashboard")
    .ToListAsync();

await mysql.RefreshCachePoolAsync("dashboard");
QueryCacheStats cacheStats = mysql.GetCacheStats();
IReadOnlyList<SmartCachePoolStats> poolStats = mysql.GetCachePoolStats();

Smart pools refresh registered queries in the background, retire idle entries, and optionally coordinate a single refresh owner across nodes. Compiled materializers, projection pushdown, chunked insert/upsert operations, and connection pooling apply independently of result caching.

Reliability and observability

Deadlocks (1213) and lock-wait timeouts (1205) on individual non-transactional statements are retried with exponential backoff and jitter.
TestConnectionAsync and the CodeLogic library health check expose connection health.
SlowQueryEvent can include EXPLAIN FORMAT=JSON when the configured threshold is exceeded.
QueryExecutedEvent reports SQL, duration, row count, connection, and cache-hit status.
CacheHitEvent, CacheMissEvent, N1QueryDetectedEvent, DatabaseConnectedEvent, DatabaseDisconnectedEvent, TableSyncedEvent, and HealthChangedEvent integrate with the CodeLogic event bus.
GetCacheStats() and GetCachePoolStats() expose cache entries, versions, refreshes, failures, and activity.

Important behavior boundaries

Cursor pagination is forward-only and applies to plain entity queries, not joined, projected, or grouped result shapes.
Continuation tokens are encoded and validated but are not signed, encrypted, or bound to filter values.
Typed joins and subquery-filtered queries are not result-cacheable because their dependencies span tables.
Explicit transactions disable result caching, smart caching, and per-statement transient retry; retry the complete transaction at the application boundary.
Repository.DeleteAsync honors [SoftDelete]; query-builder DeleteAsync is always a hard, set-based delete.
Query-builder bulk updates/deletes intentionally bypass the soft-delete read filter so deleted rows can be restored or purged.
Schema backups contain DDL only. Use a database backup strategy for row-level recovery.

Configuration

The library generates config.mysql.json (mysql) and config.mysql.cache.json (mysql.cache). A minimal database entry looks like this:

{
  "Databases": {
    "Default": {
      "Enabled": true,
      "Host": "localhost",
      "Port": 3306,
      "Database": "myapp",
      "Username": "app",
      "Password": "",
      "SyncMode": "Production",
      "MaxPoolSize": 100,
      "SlowQueryThresholdMs": 1000,
      "TransientRetryCount": 3
    }
  }
}

Important database settings include endpoint and credentials, pooling, connection/command/query timeouts, SSL, charset/collation, sync mode, backup location, batch size, IN limits, retry policy, N+1 threshold, slow-query threshold, and slow-query EXPLAIN capture.

The cache configuration controls its global switch, entry limit, default TTL, DateTime quantization window, and cache hit/miss event publication. Each named database can override the global cache switch.

Main entry points

Member	Purpose
`GetRepository<T>(connectionId)`	Create a CRUD repository.
`Query<T>(connectionId)`	Start a typed entity query.
`SqlQueryAsync<T>` / `ExecuteSqlAsync` / `SqlScalarAsync<T>`	Execute parameterized raw SQL.
`BeginTransactionAsync`	Start an explicit transaction scope.
`SyncTableAsync<T>` / `SyncSchemaAsync`	Reconcile mapped schemas.
`RegisterMigration` / `MigrateAsync` / `RollbackAsync`	Manage imperative migrations.
`RestoreSchemaAsync`	Restore a DDL snapshot.
`RegisterCachePool` / `RefreshCachePoolAsync`	Manage warm query pools.
`GetCacheStats` / `GetCachePoolStats`	Inspect cache behavior.
`TestConnectionAsync`	Verify a named database connection.

Documentation

Requirements

.NET 10
CodeLogic 4
MySqlConnector 2.x
MySQL 5.7+, MariaDB 10.3+, or a compatible Percona release

License

MIT — see LICENSE.

Product	Compatible and additional computed target framework versions.
.NET	net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed.

Compatible target framework(s)

Included target framework(s) (in package)

Learn more about Target Frameworks and .NET Standard.

net10.0
- CodeLogic (>= 4.0.0 && < 5.0.0)
- MySqlConnector (>= 2.6.1)

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version	Downloads	Last Updated
4.6.81	4	8/7/2026
4.6.80	8	8/7/2026
4.6.72	184	6/20/2026
4.6.70-preview	40	6/20/2026
4.6.69-preview	42	6/20/2026
4.5.4	135	6/5/2026
4.5.4-preview.59	75	5/24/2026
4.5.3	109	6/5/2026
4.5.3-preview.58	55	5/24/2026
4.5.2	117	5/24/2026
4.5.2-preview.68	60	6/20/2026
4.5.2-preview.57	67	5/24/2026
4.5.1	179	5/24/2026
4.5.1-preview.60	62	5/24/2026
4.5.1-preview.56	69	5/24/2026
4.4.2-preview.53	55	5/24/2026
4.4.1	110	5/24/2026
4.4.1-preview.55	60	5/24/2026
4.4.1-preview.52	58	5/24/2026
4.3.0-preview.51	63	5/24/2026

# CL.MySQL2 — Changelog

All notable changes to **CodeLogic.MySQL2** are documented here. Versions follow
[Semantic Versioning](https://semver.org/). The version listed here matches the
NuGet package version of `CodeLogic.MySQL2`.

## 2026-08-07

### Added

- Forward-only keyset pagination on entity queries through `.After(cursor)` and
`ToCursorPagedListAsync(pageSize)`, returning `CursorPagedResult<T>`.
- Versioned Base64URL continuation tokens, stable primary-key tie-breaking,
compound ASC/DESC ordering, and MySQL-compatible nullable ordering.

### Fixed

- Reject cursor tokens longer than 4,096 encoded characters before Base64 decoding
or JSON deserialization, bounding work performed on untrusted paging input.

### Documentation

- Expanded the package README into a complete capability overview covering entity
mapping, repositories, querying and paging, schema sync, migrations, lifecycle,
transactions, caching, resilience, observability, configuration, and API boundaries.

## 2026-06-20

### Fixed

- Query-builder parameter re-keying could corrupt SQL when a single predicate
emitted 11 or more parameters: the rename used a substring replace, so `@p1`
also rewrote `@p10`/`@p11`, leaving placeholders with no bound value.
Parameters are now renamed longest-name-first in `QueryBuilder.Where` and
`JoinedQuery`, matching the existing subquery path. Covered by a new
integration test (12-parameter predicate).

### Documentation

- **Full README + multi-page docs rewrite to the unified house style.** The
README is now concise — title, NuGet + license badges, one-line tagline, a
short intro, `Install`, `Quick start`, `Features`, `Configuration` (table +
JSON), `Documentation`, `Requirements`, and `License` — and renders correctly
on both GitHub and NuGet (Markdown only, absolute `https://` links, no raw
HTML or relative paths). The full API now lives in the docs site rather than
the README.
- **Docs site pages rewritten** to match the house style across the four-page
structure: [`index`](https://media2a.github.io/CodeLogic.Libs/libs/mysql2/index.html)
(overview, load, repository basics, entry points, config, health, events),
[`queries`](https://media2a.github.io/CodeLogic.Libs/libs/mysql2/queries.html),
[`schema-migrations`](https://media2a.github.io/CodeLogic.Libs/libs/mysql2/schema-migrations.html),
and [`performance`](https://media2a.github.io/CodeLogic.Libs/libs/mysql2/performance.html).
Each sub-page now opens with a tagline and an overview breadcrumb and closes
with a consistent "See also" footer.
- **No API changes.** Documentation only — no behaviour, signatures, config
keys, or version numbers were altered.

## [4.5.3] — 2026-06-20

### Added

- **Three schema sync modes — `SyncMode`.** A new operator-facing knob on each
database (`config.mysql.json`) replaces the lower-level `SchemaSyncLevel` /
`AllowDestructiveSync` flags (which still work for back-compat — `SyncMode` takes
precedence and maps onto them via `EffectiveSyncLevel`).

| Mode | Behaviour |
|---|---|
| `Developer` | Aggressive rolling updates — drops removed columns/indexes/FKs on every boot (maps to `Full`). |
| `Production` *(default)* | Additive only — adds/modifies, **never drops**. A change that needs a drop is deferred and the table is flagged `DriftPending`. |
| `Migration` | Deliberate one-shot destructive reconcile (takes a schema backup first). Idempotent — once every model matches and no drift is pending it does nothing and logs a warning to switch back to `Production`. |

```json
{ "Databases": { "Default": { "SyncMode": "Production" } } }
```

- **CRC sentinel — `__schema_state`.** Each model's desired schema is hashed
(CRC) into a per-table row. Sync skips a table **entirely** — no
`information_schema` diffing, no DDL — when the stored CRC matches the model
*and* the table still exists. New `SyncResult` fields: `Skipped`, `SchemaCrc`,
`DriftPending`; new `SchemaSyncStatus` enum (`Synced` / `DriftPending`).
Exposed via `mysql.SchemaState` (a `SchemaStateStore`).

- **Cross-node schema-sync lock — `SchemaSyncLock`.** A schema/migration pass
serializes across application nodes with MySQL `GET_LOCK`. The winner runs the
DDL; peers wait, then find the schema already reconciled (matching CRCs) and do
nothing.

- **Batch schema sync + runtime mode override.** `mysql.SyncSchemaAsync(params
Type[])` reconciles a whole set of entities as one pass under a single lock,
honouring the configured `SyncMode` and the CRC fast-path — the recommended
startup entry point. `mysql.SetSyncMode(mode, connectionId)` overrides the mode
at runtime (e.g. to flip `Migration` back to `Production` once a pass completes).

- **Imperative migrations.** `IMigration` / `MigrationVersion` / the abstract
`Migration` base for data transforms, seeds, and semantic changes the
declarative sync can't express. `IMigrationContext` provides `ExecuteAsync`,
`QueryAsync<T>`, `ScalarAsync<T>`, and a `SyncTableAsync<T>()` bridge into
declarative sync. The `MigrationRunner` applies pending migrations in
`MigrationVersion` order over the `__migrations` table, each in its own
transaction, under the shared lock, gated by the app version
(`CodeLogicEnvironment.AppVersion`), and warns when an applied migration's
checksum has drifted. Library surface: `RegisterMigration`,
`RegisterMigrationsFrom(assembly)`, `MigrateAsync`, `GetPendingMigrationsAsync`.

```csharp
public sealed class SeedRoles() : Migration("1.4.0", 1, "Seed default roles")
{
     public override async Task UpAsync(IMigrationContext ctx, CancellationToken ct) =>
         await ctx.ExecuteAsync("INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);
}
```

> MySQL implicitly commits on DDL, so a migration that mixes `ALTER` with data
> changes is not atomic — keep `UpAsync` steps idempotent.

- **Rollback.** `mysql.RollbackAsync(MigrationVersion target)` runs `DownAsync`
newest-first for every applied migration above `target`, each in its own
transaction. It pre-flights the range and aborts cleanly **before any change**
if a migration in range has no `DownAsync` override. Declaratively,
`mysql.RestoreSchemaAsync(tableName)` replays a `BackupManager` schema snapshot
(DDL only — rows are lost) and clears the table's `__schema_state` row so the
next sync reconciles from scratch.

### Fixed

- **Upsert now portable to MariaDB.** `UpsertAsync`, `UpsertManyAsync`, and
`UpsertWithIncrementsAsync` emit the portable `... ON DUPLICATE KEY UPDATE col =
VALUES(col)` form, which works on **both** MySQL and MariaDB, instead of the
MySQL-8.0.19+-only `INSERT ... AS new ... ON DUPLICATE KEY UPDATE` row-alias
syntax that MariaDB rejected.

## [4.5.2] — 2026-06-13

### Added

- **Typed JOINs.** `Query<TLeft>().Join<TRight, TKey, TResult>(leftKey, rightKey,
resultSelector, type)` translates a strongly-typed equi-join to real SQL with
table aliases (left `t0`, right `t1`) and a compiled, reflection-free projection
into `TResult` — only the columns the selector references are transferred.

```csharp
var views = await mysql.Query<Order>()
     .Where(o => o.Total > 100)
     .Join<Customer, long, OrderView>(
         o => o.CustomerId,                 // left key
         c => c.Id,                         // right key
         (o, c) => new OrderView { OrderId = o.Id, Customer = c.Name })
     .OrderByDescending((o, c) => o.Total)
     .Take(20)
     .ToListAsync();
```

- **Join types:** `Inner` (default), `Left`, `Right`. `Cross` is rejected for a
   keyed join (keys imply an equi-join).
- **Composite keys:** `o => new { o.A, o.B }` matched positionally with
   `c => new { c.X, c.Y }`.
- **Carried filters:** `.Where(...)` calls made on the left builder *before*
   `.Join` are re-qualified to the left table and preserved.
- **Fluent surface on the join:** `.Where((l, r) => …)`, `.OrderBy` /
   `.OrderByDescending((l, r) => …)`, `.Take` / `.Skip`, and the
   `ToListAsync` / `FirstOrDefaultAsync` / `CountAsync` terminals.
- The single-table query path and the existing raw-string
   `Join(table, condition, type)` overload are unchanged.

- **Subquery filters — `EXISTS` / `IN`.** Four new WHERE-family methods on the
query builder translate to real SQL subqueries:

```csharp
// Correlated EXISTS — correlated + non-correlated conditions in one predicate
mysql.Query<Order>()
     .WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent");

// IN (subquery) with an optional uncorrelated inner filter
mysql.Query<Order>()
     .WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip);
```

- `WhereExists<TInner>` / `WhereNotExists<TInner>` →
   `[NOT] EXISTS (SELECT 1 FROM inner WHERE …)`, correlated via the predicate.
- `WhereIn<TInner, TKey>` / `WhereNotIn<TInner, TKey>` →
   `col [NOT] IN (SELECT innerCol FROM inner [WHERE innerFilter])`.
- Composes with ordinary `.Where(...)` and reuses the same multi-source
   translator as joins (each source qualified by its table name).

- **Column rename — `[Column(PreviousName = "old_col")]`.** Schema sync now emits
`CHANGE COLUMN old_col new_col …` to rename in place and **preserve the data**,
instead of the drop-old + add-new that silently lost it (orphan column at Safe;
data loss at Full). Works at `Safe` and above; remove `PreviousName` once every
environment has synced.

```csharp
[Column(Name = "email_address", PreviousName = "email")]
public string EmailAddress { get; set; } = "";
```

- **Multi-node cache coordination — `ICacheCoordinator`.** A pluggable coordination
seam (same model as `ICacheStore`: interface + in-process default, distributed
adapter supplied by the consumer) that closes the single-node limitation called
out in 4.1.2's notes. Install with `QueryCache.UseCoordinator(...)`.

- **Cross-node invalidation** — a local mutation now fans out via
   `PublishInvalidationAsync`; a peer's broadcast bumps this node's table-version
   counter and evicts matching entries (without re-broadcasting). Previously the
   version counter was per-process, so a mutation on one node never invalidated
   the others.
- **Single-flight pool refresh** — `SmartCachePool` ticks now acquire a refresh
   lease via `TryAcquireRefreshLeaseAsync`; only the lease holder hits the DB, so
   N nodes don't all refresh the same pool. Idle-entry retirement still runs on
   every node. Pair with a shared `ICacheStore` (e.g. Redis) so non-leaders read
   the entry the leader writes.
- The default `NullCacheCoordinator` is single-node: no fan-out, always grants
   the lease — behaviour is identical to before off-cluster.

- **Raw SQL escape hatch.** `mysql.SqlQueryAsync<T>(sql, parameters)` materializes
rows into `T` with the same compiled materializer as the query builder;
`ExecuteSqlAsync(sql, parameters)` runs a non-query and returns the affected count;
`SqlScalarAsync<T>(sql, parameters)` returns a single value. All use named
parameters, flow through observability, and inherit the transient-retry policy.

```csharp
var rows = await mysql.SqlQueryAsync<UserRecord>(
     "SELECT * FROM users WHERE country = @c", new() { ["@c"] = "DK" });
```

- **Transient-error auto-retry.** Single non-transactional statements that fail with
a deadlock (1213) or lock-wait timeout (1205) are retried with exponential backoff
+ jitter. Configurable per database via `TransientRetryCount` (default 3) and
`TransientRetryBaseDelayMs` (default 50); 0 disables. Statements inside an explicit
transaction scope are never auto-retried — the whole transaction is the caller's
to retry.

- **Cache stampede protection.** Concurrent cache misses on the same cold key now
collapse to a single factory execution (single-flight) instead of a thundering
herd of identical DB queries. Transparent — no API change.

- **Soft deletes — `[SoftDelete(nameof(DeletedUtc))]`.** Marks a nullable-`DateTime`
column as the delete marker. `Repository.DeleteAsync` then sets it to UtcNow
instead of issuing a physical `DELETE`, and reads via `mysql.Query<T>()` and the
repository getters automatically exclude rows where it is set. Opt back in with
`.IncludeDeleted()` on a query, or purge for real with `Repository.HardDeleteAsync`.

```csharp
[SoftDelete(nameof(DeletedUtc))]
public class Account { /* … */ public DateTime? DeletedUtc { get; set; } }
```

### Notes

- **No breaking changes.** Joins and subquery filters are new methods; the
multi-source WHERE translator is byte-identical to the single-table translator
when no alias map is supplied.
- **Subquery-filtered queries are not cacheable** and cannot be turned into a
typed `.Join` — same single-table-version-stamping limitation as joins. Both
are gated explicitly (cache silently bypassed; `.Join` throws).
- **`WhereExists` against the outer query's own table is rejected** — unqualified
inner columns would be ambiguous.
- **Soft-delete auto-filtering applies to single-table reads only** —
`mysql.Query<T>()` terminals and the repository getters. It does NOT apply to
joins, subqueries, or the query builder's bulk `UpdateAsync`/`DeleteAsync` (those
stay raw so you can target or restore deleted rows). `QueryBuilder.DeleteAsync`
is a hard delete regardless of `[SoftDelete]`.
- **Joins are not cacheable in this version.** The result cache stamps each entry
with a single table's version counter, so a join entry could not be invalidated
when the *other* joined table mutates. `.WithCache` / `.SmartCache` are
intentionally absent on `JoinedQuery` rather than risk serving stale joins;
multi-table invalidation is on the roadmap.
- **`TRight` must be specified explicitly** (e.g. `Join<Customer, long, OrderView>`)
— it cannot be inferred from a lambda parameter type.

## [4.5.0] — 2026-05-24

### Added

- **`StorageType` enum on `ColumnAttribute`.** Per-column physical storage
override that takes precedence over `DataType` for DDL generation.
Available values: `Binary`, `VarBinary`, `TinyBlob`, `Blob`, `MediumBlob`,
`LongBlob`. When set, the column is stored as the chosen binary type and
values are automatically converted to/from binary on read and write.

- **Guid-as-BINARY(16) support.** Set `StorageType = StorageType.Binary` on a
`Guid` property and CL.MySQL2 stores it as `BINARY(16)` using RFC 4122
big-endian byte layout for correct lexicographic sort order. Conversion is
automatic in all paths: insert, update, read, WHERE clauses, and IN queries.

```csharp
[Column(StorageType = StorageType.Binary, Primary = true, NotNull = true)]
public Guid Id { get; set; }
```

- **Automatic binary conversion for all CLR types.** Any property can be
stored as binary by setting `StorageType`. Supported types and their binary
sizes (auto-detected when `Size` is not explicit):

| CLR type | Binary size | Byte order |
|---|---|---|
| `Guid` | 16 | RFC 4122 big-endian |
| `long` / `ulong` | 8 | big-endian |
| `int` / `uint` | 4 | big-endian |
| `short` / `ushort` | 2 | big-endian |
| `double` | 8 | big-endian |
| `float` | 4 | big-endian |
| `decimal` | 16 | big-endian |
| `DateTime` / `DateTimeOffset` | 8 | ticks, big-endian |
| `bool` / `byte` / `sbyte` | 1 | — |
| `string` | explicit | UTF-8 |
| `byte[]` | passthrough | as-is |

- **LINQ WHERE support for binary-stored columns.** Expressions like
`repo.Where(x => x.Id == someGuid)` and `list.Contains(x.Id)` correctly
convert parameter values to binary when the column uses `StorageType`.

- **`SequentialGuid.NewId()` helper.** Generates time-ordered UUIDv7 values
optimized for `BINARY(16)` primary keys. Sequential inserts append to the
B-tree instead of causing random page splits — dramatically reducing index
fragmentation compared to random UUIDv4.

```csharp
[Column(StorageType = StorageType.Binary, Primary = true, NotNull = true)]
public Guid Id { get; set; } = SequentialGuid.NewId();
```

- **Unified versioning.** All CodeLogic.Libs now share a single version line
controlled by `version.txt`. AssemblyVersion is derived automatically.

### Notes

- **No breaking changes.** `StorageType` defaults to `StorageType.Default`
(the zero-value), so all existing entities and schemas are unaffected.
Guid inference still returns `Char(36)` unless you explicitly opt in.

## [4.2.3] — 2026-05-15

### Fixed

- **Cache orphan accumulation on mutations.** `QueryCache.Invalidate(tableName)`
previously only bumped the per-table version counter — old cache entries
(now unreachable via the read path because the cache key changed) lingered
in the underlying store until TTL or LRU swept them. On a busy app this
produced unbounded memory growth. Invalidate now also calls
`ICacheStore.EvictByTableAsync` to sweep matching entries in the same step.
- **SmartCachePool orphan tracking.** Each pool entry now remembers the
cache key it last wrote. If the next tick computes a different key
(because a mutation bumped the table version between ticks), the
previous key is evicted explicitly. Works on any `ICacheStore`
implementation including ones that can't enumerate (Redis without
SCAN, memcached).

### Added

- `ICacheStore.EvictByTableAsync(tableName)` — bulk eviction by table.
Default in-process implementation is an O(n) scan over current entries.
Distributed adapters can override (e.g. Redis tag-set or key prefix).
- `ICacheStore.CountByTable()` — entries grouped by tableName for diagnostics.
- `QueryCache.GetStats()` → `QueryCacheStats(TotalEntries, EntriesByTable,
TableVersions)`. Surfaced on the library API via `MySQL2Library.GetCacheStats()`
so admin tools can render "what's in the cache right now" without
dumping values.

## [4.2.2] — 2026-05-15

### Fixed

- **SmartCache pool no longer corrupts `ToListAsync` results.** The pool's
refresh factory stored the unwrapped `List<T>` instead of the
`Result<List<T>>` that the cache-aside read path expects. After the first
background refresh tick, every subsequent read failed the `(Result<List<T>>)`
cast inside `GetOrSetAsync`, the outer try/catch turned it into a Failure
Result, and callers saw an empty list (manifested as "No servers configured"
/ empty leaderboards roughly one refresh interval after warm-up). `FirstOrDefaultAsync`
and `CountAsync` already cached the full Result and were unaffected; only
`ToListAsync` was wrong.

## [4.2.1] — 2026-05-15

### Fixed

- **Failure Results no longer poison the cache.** Previously, a query that
failed (e.g. transient connection error during a cold warm-up) had its
`Result<T>.Failure` value cached just like a successful one — subsequent
reads served the failure until the entry's TTL expired or a pool refresh
overwrote it. Now `QueryCache.GetOrSetAsync` skips writing failure Results,
evicts any pre-existing failure entry on read, and `SetDirectAsync` (the
smart-cache pool's refresh path) refuses to write failures too. Empty
server lists / leaderboards on first request after a deploy are gone.

## [4.2.0] — 2026-05-15

### Added

- **Smart-cache pool warm-up on registration.** `RegisterCachePool` now
accepts an optional `warmUp: Func<Task>` callback that fires as a
fire-and-forget task right after the pool starts. The callback just
calls the queries that should be warm — they auto-register with the
pool via their normal `.SmartCache(name)` decoration — so the cache
is hot before the first user request hits it. Exceptions are caught
and logged; the pool stays lazy if warm-up fails.
- `SmartCachePool.WarmUp(Func<Task>)` — public method exposing the same
behaviour for callers that want to warm a pool independently of
registration.

## [4.1.2] — 2026-05-15

### Added

- **Smart cache pools** — named groups of cached queries kept warm by a
background timer (`mysql.RegisterCachePool("dashboard", refreshEvery: 30s)`,
opt in per-query with `.SmartCache("dashboard")`). Reads after the first
populate the cache never block on the DB — the pool's timer re-runs every
registered query in the background and overwrites the entry.
- `SmartCachePool.RefreshNowAsync()` — out-of-schedule refresh, useful right
after a deploy to prime the cache before the first user hits the page.
- `MySQL2Library.GetCachePoolStats()` — diagnostic snapshot per pool
(entry count, ticks fired, ticks failed, last tick UTC).
- `QueryCache.SetDirectAsync(...)` — internal cache write API used by pools.

### Notes

- Smart cache is mutually exclusive with `.WithCache(TimeSpan)` — if both are
set, the pool wins and the TTL comes from `refreshEvery * 2`.
- Unknown pool name on `.SmartCache(name)` logs a warning and falls back to
non-cached execution (no exception).
- Per-pool eviction policy: an entry that has not been read for
`MaxIdleFires` (default 3) consecutive ticks is dropped from the refresh
list. Bounds cardinality on parameterized queries.
- Smart cache is disabled inside a transaction scope (same as `.WithCache`).
- Single-node only in v4.2. Multi-node coordination is on the roadmap.

## [4.1.1] — 2026-04-17

### Fixed

- Qualify LHS columns in upsert SET clauses so `UpsertAsync` no longer
generates ambiguous column references when the table has columns whose
names clash with parameter placeholders.

## [4.1.0] — 2026-04-17

### Added

- **Typed upsert** — `UpsertAsync` + `UpsertWithIncrementsAsync` on the
repository. Compiles to `INSERT ... ON DUPLICATE KEY UPDATE ...` with
full LINQ-shaped value/increment expressions on the update side.

### Changed

- `LibraryManifest.Version` now reads from the assembly's `AssemblyVersion`
attribute at runtime instead of being a hard-coded string. Keeps the
manifest honest across rebuilds.

## [4.0.4] — 2026-04-16

### Changed

- README + manifest refresh across every CodeLogic library for the v4 baseline.
- No functional changes vs 4.0.3.

## [4.0.1] — 2026-04-09

### Fixed

- Drop the `Expression.Compile().DynamicInvoke()` fast-path inside the SQL
expression visitor — it broke on closures over generic types. The visitor
now always walks the tree.

## [4.0.0] — 2026-04-09

Major rewrite. Breaking.

### Added

- **Projection pushdown** — `.Select<TResult>(x => new { ... })` emits a
real `SELECT col1, col2, ...` column list instead of `SELECT *`. Combined
with compiled materializers this often cuts row-transfer bandwidth by 80%+.
- **SQL-side aggregation** — `.GroupBy(...).Select(g => new { g.Key,
g.Sum(...), g.Average(...), ... })` translates to real `GROUP BY` +
aggregate functions. No client-side row materialization.
- **`SqlFn` helpers** — server-side function markers (`SqlFn.DayOfWeek`,
`SqlFn.Hour`, `SqlFn.BucketUtc`, `SqlFn.Coalesce`, `SqlFn.Round`, etc.)
recognized by the translator — mirrors EF's `EF.Functions` pattern.
- **`[Index]` attribute** — declare named, unique, and covering indexes
(with `Include = new[] { ... }`) at the column level.
- **`[RetainDays]` attribute** — opt entities into a daily background purge
worker that runs batched `DELETE` until drained.
- **Working result cache** — `.WithCache(TimeSpan)` with two correctness
fixes from prior versions:
- DateTime closures near `UtcNow` are time-quantized to a configurable
   window (default 60s) so `.Where(x => x.At >= UtcNow.AddDays(-30))`
   stops producing a unique cache key per call.
- Mutations bump a per-table version that participates in the cache
   key — invalidation is free (old keys become un-hittable, no eviction
   loop).
- **`EntityMetadata<T>` + compiled `Materializer<T>`** — reflection runs
once per entity at first use; subsequent reads use a compiled
reader-to-entity function.
- **Observability events** — `QueryExecutedEvent`, `SlowQueryEvent`,
`CacheHitEvent`, `CacheMissEvent`, `N1QueryDetectedEvent`,
`TableSyncedEvent` publish to the CodeLogic event bus.
- **`MaxBatchInsertSize`, `MaxInClauseValues`, `PreparedStatementCacheSize`,
`N1DetectorThreshold`, `CaptureExplainOnSlowQuery`, `DefaultStringSize`,
`CacheEnabledOverride`** — per-database config knobs.
- **`CacheConfiguration`** — global cache settings (`Enabled`,
`MaxEntries`, `DefaultTtlSeconds`, `TimeQuantizeSeconds`,
`PublishEvents`).

### Changed

- Republished as v4.0.0 to reset the version line with the new package shape.
- All public APIs refreshed under the v4 baseline.

## Earlier releases

Pre-4.0 history is retained in the
[git log](https://github.com/Media2A/CodeLogic.Libs/commits/main/CL.MySQL2)
but is not documented in detail here — the library shape changed
significantly in the v4 rewrite.