Skip to content

Instantly share code, notes, and snippets.

@fubar-coder

fubar-coder/AddCustomFluentMigratorSqliteGenerator.cs

Last active August 23, 2021 01:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save fubar-coder/1e8ba2c34ad95ae1cea3939b4e73e647 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save fubar-coder/1e8ba2c34ad95ae1cea3939b4e73e647 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. fubar-coder revised this gist Aug 22, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion CustomSqliteTypeMap.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@

    namespace Your.CustomFluentMigratorClasses
    {
    internal class VentunoSqliteTypeMap : TypeMapBase
    internal class CustomSqliteTypeMap : TypeMapBase
    {
    public const int AnsiStringCapacity = 8000;
    public const int AnsiTextCapacity = 2147483647;
  2. fubar-coder created this gist Aug 22, 2021.
    14 changes: 14 additions & 0 deletions AddCustomFluentMigratorSqliteGenerator.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,14 @@
    using Your.CustomFluentMigratorClasses;

    namespace Microsoft.Extensions.DependencyInjection
    {
    public static class ServiceCollectionExtensions
    {
    public static IServiceCollection AddCustomFluentMigratorSqliteGenerator(
    this IServiceCollection services)
    {
    return services.AddScoped<CustomSqliteGenerator>()
    .AddScoped<IMigrationGenerator>(sp => sp.GetRequiredService<CustomSqliteGenerator>());
    }
    }
    }
    66 changes: 66 additions & 0 deletions CustomSqliteColumn.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,66 @@
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;

    using FluentMigrator.Model;
    using FluentMigrator.Runner.Generators.Base;
    using FluentMigrator.Runner.Generators.SQLite;

    namespace Your.CustomFluentMigratorClasses
    {
    /// <summary>
    /// An almost 1:1 copy from the FM project
    /// </summary>
    internal class CustomSqliteColumn : ColumnBase
    {
    public CustomSqliteColumn()
    : base(new CustomSqliteTypeMap(), new SQLiteQuoter())
    {
    }

    /// <inheritdoc />
    public override string Generate(IEnumerable<ColumnDefinition> columns, string tableName)
    {
    var colDefs = columns.ToList();
    var foreignKeyColumns = colDefs.Where(x => x.IsForeignKey && x.ForeignKey != null);
    var foreignKeyClauses = foreignKeyColumns
    .Select(x => ", " + FormatForeignKey(x.ForeignKey, GenerateForeignKeyName));

    // Append foreign key definitions after all column definitions and the primary key definition
    return base.Generate(colDefs, tableName) + string.Concat(foreignKeyClauses);
    }

    /// <inheritdoc />
    public override bool ShouldPrimaryKeysBeAddedSeparately(IEnumerable<ColumnDefinition> primaryKeyColumns)
    {
    // If there are no identity column then we can add as a separate constraint
    var pkColDefs = primaryKeyColumns.ToList();
    return !pkColDefs.Any(x => x.IsIdentity) && pkColDefs.Any(x => x.IsPrimaryKey);
    }

    /// <inheritdoc />
    protected override string FormatIdentity(ColumnDefinition column)
    {
    // SQLite only supports the concept of Identity in combination with a single primary key
    // see: http://www.sqlite.org/syntaxdiagrams.html#column-constraint syntax details
    if (column.IsIdentity && !column.IsPrimaryKey && column.Type != DbType.Int32)
    {
    throw new ArgumentException("SQLite only supports identity on single integer, primary key coulmns");
    }

    return string.Empty;
    }

    /// <inheritdoc />
    protected override string FormatPrimaryKey(ColumnDefinition column)
    {
    if (!column.IsPrimaryKey)
    {
    return string.Empty;
    }

    return column.IsIdentity ? "PRIMARY KEY AUTOINCREMENT" : string.Empty;
    }
    }
    }
    90 changes: 90 additions & 0 deletions CustomSqliteGenerator.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,90 @@
    using FluentMigrator.Expressions;
    using FluentMigrator.Runner.Generators;
    using FluentMigrator.Runner.Generators.Generic;
    using FluentMigrator.Runner.Generators.SQLite;

    using Microsoft.Extensions.Options;

    namespace Your.CustomFluentMigratorClasses
    {
    /// <summary>
    /// An almost 1:1 copy from the FM project
    /// </summary>
    public class CustomSqliteGenerator : GenericGenerator
    {
    public CustomSqliteGenerator()
    : this(new SQLiteQuoter())
    {
    }

    public CustomSqliteGenerator(
    SQLiteQuoter quoter)
    : this(quoter, new OptionsWrapper<GeneratorOptions>(new GeneratorOptions()))
    {
    }

    public CustomSqliteGenerator(
    SQLiteQuoter quoter,
    IOptions<GeneratorOptions> generatorOptions)
    : base(new CustomSqliteColumn(), quoter, new EmptyDescriptionGenerator(), generatorOptions)
    {
    }

    public override string RenameTable => "ALTER TABLE {0} RENAME TO {1}";

    public override string Generate(AlterColumnExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("SQLite does not support alter column");
    }

    public override string Generate(RenameColumnExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("SQLite does not support renaming of columns");
    }

    public override string Generate(DeleteColumnExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("SQLite does not support deleting of columns");
    }

    public override string Generate(AlterDefaultConstraintExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("SQLite does not support altering of default constraints");
    }

    public override string Generate(CreateForeignKeyExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Foreign keys are not supported in SQLite");
    }

    public override string Generate(DeleteForeignKeyExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Foreign keys are not supported in SQLite");
    }

    public override string Generate(CreateSequenceExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Sequences are not supported in SQLite");
    }

    public override string Generate(DeleteSequenceExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Sequences are not supported in SQLite");
    }

    public override string Generate(DeleteDefaultConstraintExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Default constraints are not supported");
    }

    public override string Generate(CreateConstraintExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Constraints are not supported");
    }

    public override string Generate(DeleteConstraintExpression expression)
    {
    return CompatibilityMode.HandleCompatibilty("Constraints are not supported");
    }
    }
    }
    54 changes: 54 additions & 0 deletions CustomSqliteTypeMap.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,54 @@
    using System.Data;

    using FluentMigrator.Runner.Generators.Base;

    namespace Your.CustomFluentMigratorClasses
    {
    internal class VentunoSqliteTypeMap : TypeMapBase
    {
    public const int AnsiStringCapacity = 8000;
    public const int AnsiTextCapacity = 2147483647;
    public const int UnicodeStringCapacity = 4000;
    public const int UnicodeTextCapacity = 1073741823;
    public const int ImageCapacity = 2147483647;
    public const int DecimalCapacity = 19;
    public const int XmlCapacity = 1073741823;

    public override string GetTypeMap(DbType type, int? size, int? precision)
    {
    return base.GetTypeMap(type, size: null, precision: null);
    }

    protected override void SetupTypeMaps()
    {
    SetTypeMap(DbType.Binary, "BLOB");
    SetTypeMap(DbType.Byte, "INTEGER");
    SetTypeMap(DbType.Int16, "INTEGER");
    SetTypeMap(DbType.Int32, "INTEGER");
    SetTypeMap(DbType.Int64, "INTEGER");
    SetTypeMap(DbType.SByte, "INTEGER");
    SetTypeMap(DbType.UInt16, "INTEGER");
    SetTypeMap(DbType.UInt32, "INTEGER");
    SetTypeMap(DbType.UInt64, "INTEGER");
    SetTypeMap(DbType.Currency, "NUMERIC");
    SetTypeMap(DbType.Decimal, "NUMERIC");
    SetTypeMap(DbType.Double, "NUMERIC");
    SetTypeMap(DbType.Single, "NUMERIC");
    SetTypeMap(DbType.VarNumeric, "NUMERIC");
    SetTypeMap(DbType.AnsiString, "TEXT");
    SetTypeMap(DbType.String, "TEXT");
    SetTypeMap(DbType.AnsiStringFixedLength, "TEXT");
    SetTypeMap(DbType.StringFixedLength, "TEXT");

    SetTypeMap(DbType.Date, "DATETIME");
    SetTypeMap(DbType.DateTime, "DATETIME");
    SetTypeMap(DbType.DateTime2, "DATETIME");
    SetTypeMap(DbType.DateTimeOffset, "DATETIME");
    SetTypeMap(DbType.Time, "DATETIME");

    // This is the relevant change to get "BOOLEAN" instead of "INTEGER"
    SetTypeMap(DbType.Boolean, "BOOLEAN");
    SetTypeMap(DbType.Guid, "UNIQUEIDENTIFIER");
    }
    }
    }