SharpCoreDbProcessor SQL generation bugs: UndefinedDefaultValue sentinel, duplicate PRIMARY KEY, and unsupported SQLite DDL statements

[YBazanPro]

Bug Summary

SharpCoreDbProcessor in SharpCoreDB.Extensions v1.7.1 has multiple SQL generation issues that cause runtime failures when using FluentMigrator migrations. Two are critical (crash on every migration), and several are high severity (generate SQL unsupported by the SQLite engine that SharpCoreDB is built on).

Affected Version

SharpCoreDB.Extensions 1.7.1 (latest)

Critical Issues

1. UndefinedDefaultValue sentinel emitted as SQL literal

Location: BuildColumnDefinition method

Current code:

if (column.DefaultValue is not null)
{
    builder.Append(" DEFAULT ");
    builder.Append(FormatValue(column.DefaultValue));
}

Problem: FluentMigrator's ColumnDefinition.DefaultValue is initialized to new ColumnDefinition.UndefinedDefaultValue() — a sentinel object that is not null but means "no default value was specified". The check is not null passes, and FormatValue falls through to the default case, producing:

"Version" INTEGER PRIMARY KEY NOT NULL DEFAULT 'FluentMigrator.Model.ColumnDefinition+UndefinedDefaultValue'

How FluentMigrator's own generators handle this (from ColumnBase.FormatDefaultValue in FluentMigrator.Runner.Core):

protected virtual string FormatDefaultValue(ColumnDefinition column)
{
    if (column.DefaultValue is ColumnDefinition.UndefinedDefaultValue)
    {
        return string.Empty;
    }
    // ... handle SystemMethods and normal values
}

Fix:

if (column.DefaultValue is not null && column.DefaultValue is not ColumnDefinition.UndefinedDefaultValue)
{
    builder.Append(" DEFAULT ");
    builder.Append(FormatValue(column.DefaultValue));
}

2. Duplicate PRIMARY KEY constraint in CREATE TABLE

Location: Process(CreateTableExpression) + BuildColumnDefinition

Current behavior:

• BuildColumnDefinition adds inline PRIMARY KEY when column.IsPrimaryKey is true (or PRIMARY KEY AUTOINCREMENT when column.IsIdentity)
• Process(CreateTableExpression) then also adds a table-level PRIMARY KEY (...) constraint

Result:

CREATE TABLE IF NOT EXISTS "__SharpMigrations" (
    "Version" INTEGER PRIMARY KEY NOT NULL DEFAULT '...', 
    PRIMARY KEY ("Version")
)

This is invalid SQL — duplicate PRIMARY KEY definition.

How FluentMigrator's own generators handle this (from ColumnBase.Generate(IEnumerable<ColumnDefinition>, string)):

var colDefs = columns.ToList();
var primaryKeyColumns = colDefs.Where(x => x.IsPrimaryKey).ToList();

if (ShouldPrimaryKeysBeAddedSeparately(primaryKeyColumns))
{
    primaryKeyString = AddPrimaryKeyConstraint(tableName, primaryKeyColumns);
    foreach (var column in colDefs) { column.IsPrimaryKey = false; }  // ← Clear inline PK
}

The official generators set IsPrimaryKey = false on all columns before generating individual column definitions, so the inline PK is never emitted when the table-level constraint is used.

Fix options (pick one):

Option A — Remove inline PK from BuildColumnDefinition, always use table-level:

// In BuildColumnDefinition, remove:
//   else if (column.IsPrimaryKey)
//       builder.Append(" PRIMARY KEY");

// Keep only the IsIdentity case (AUTOINCREMENT requires inline PRIMARY KEY in SQLite)
if (column.IsIdentity)
    builder.Append(" PRIMARY KEY AUTOINCREMENT");

Then in Process(CreateTableExpression), skip the table-level PK when there's an identity column (since AUTOINCREMENT already implies it).

Option B — Follow FluentMigrator's pattern: clear IsPrimaryKey before building column definitions:

public void Process(CreateTableExpression expression)
{
    var columns = expression.Columns.ToList();
    var primaryKeyColumns = columns.Where(c => c.IsPrimaryKey).Select(c => QuoteIdentifier(c.Name)).ToList();
    
    // Clear inline PK flags so BuildColumnDefinition doesn't add them
    foreach (var col in columns) { col.IsPrimaryKey = false; }
    
    var columnDefs = columns.Select(BuildColumnDefinition).ToList();
    
    if (primaryKeyColumns.Count > 0)
    {
        columnDefs.Add($"PRIMARY KEY ({string.Join(", ", primaryKeyColumns)})");
    }
    
    Execute($"CREATE TABLE IF NOT EXISTS {QuoteIdentifier(expression.TableName)} ({string.Join(", ", columnDefs)})");
    // ... index creation
}

Note: Option B has a subtle issue with IsIdentity columns — BuildColumnDefinition adds PRIMARY KEY AUTOINCREMENT for identity columns, but then IsPrimaryKey is cleared so the table-level PK won't include them. The identity column must be included in the table-level PK. Option A is cleaner for SQLite.

High Severity Issues (SQLite-incompatible DDL)

Since SharpCoreDB is built from SQLite inspiration, the following Process methods generate SQL that SQLite does not support, causing runtime errors:

3. Process(AlterColumnExpression) — ALTER TABLE ... ALTER COLUMN not supported by SQLite

Execute($"ALTER TABLE {QuoteIdentifier(expression.TableName)} ALTER COLUMN {columnSql}");

SQLite only supports ALTER TABLE ... RENAME COLUMN and ALTER TABLE ... ADD COLUMN / DROP COLUMN. Column type/constraint changes require table recreation.

4. Process(CreateSchemaExpression) — CREATE SCHEMA not supported by SQLite

Execute($"CREATE SCHEMA IF NOT EXISTS {QuoteIdentifier(expression.SchemaName)}");

5. Process(DeleteSchemaExpression) — DROP SCHEMA not supported by SQLite

Execute($"DROP SCHEMA IF EXISTS {QuoteIdentifier(expression.SchemaName)}");

6. Process(CreateForeignKeyExpression) — ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY not supported by SQLite

Execute($"ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES ...");

SQLite requires foreign keys to be defined inline in the CREATE TABLE statement.

7. Process(DeleteForeignKeyExpression) — ALTER TABLE ... DROP CONSTRAINT not supported by SQLite

8. Process(CreateSequenceExpression) — CREATE SEQUENCE not supported by SQLite

9. Process(DeleteSequenceExpression) — DROP SEQUENCE not supported by SQLite

10. Process(CreateConstraintExpression) — ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY/UNIQUE not supported by SQLite

11. Process(DeleteConstraintExpression) — ALTER TABLE ... DROP CONSTRAINT not supported by SQLite

12. Process(AlterDefaultConstraintExpression) — ALTER TABLE ... ALTER COLUMN ... SET DEFAULT not supported by SQLite

13. Process(DeleteDefaultConstraintExpression) — ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT not supported by SQLite

Suggested approach for issues 3–13: Follow the pattern used by FluentMigrator's official SQLite processor — either throw NotSupportedException with a clear message, or implement the table-recreation workaround (create new table, copy data, drop old table, rename). The official SQLite generator simply does not support these operations.

Medium Severity Issues

14. FormatValue — IFormattable case outputs enum names unquoted

When value is an enum, it matches the IFormattable case and outputs the enum name as a bare SQL token (e.g., Active instead of 'Active'). This could be misinterpreted as a SQL identifier.

15. Process(DeleteColumnExpression) — SQLite DROP COLUMN restrictions not handled

SQLite's ALTER TABLE ... DROP COLUMN has restrictions: the column cannot be part of a PRIMARY KEY, UNIQUE constraint, FOREIGN KEY, or index. The processor does not check for or handle these cases.

Reproduction

The simplest reproduction is running any FluentMigrator migration with AddSharpCoreDBFluentMigrator():

services.AddSharpCoreDB();
services.AddSingleton<DatabaseFactory>();
services.AddSingleton<IDatabase>();
services.AddSharpCoreDBFluentMigrator();
// ...
var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
runner.MigrateUp();  // ← Crashes with issues #1 and #2

The very first migration FluentMigrator runs creates the __SharpMigrations table, which triggers both the UndefinedDefaultValue and duplicate PRIMARY KEY bugs simultaneously.

Environment

• .NET 10
• SharpCoreDB.Extensions 1.7.1
• FluentMigrator 8.0.1
• Windows 11