public static ICreateTableColumnOptionOrWithColumnSyntax CreateEntityTable(
this FluentMigrator.Migration migration, string tableName)
{
return(migration.Create.Table(tableName)
.WithColumn("Id").AsInt64().PrimaryKey()
.WithColumn("[Version]").AsInt32().NotNullable()
.WithColumn("Created").AsDateTime().NotNullable());
}
public static void EnforceForeignKey(this FluentMigrator.Migration migration, string fromTable, string toTable)
{
var keyName = $"FK_{fromTable}_{toTable}";
migration.Create.ForeignKey(keyName)
.FromTable(fromTable)
.ForeignColumn($"{toTable}Id")
.ToTable(toTable)
.PrimaryColumn("Id");
}
public static string GetConfigurtedDatabaseType(this FluentMigrator.Migration migration)
{
var configuredDatabaseType = string.Empty;
migration.IfDatabase(s =>
{
configuredDatabaseType = s;
return(true);
});
return(configuredDatabaseType);
}
public static ICreateSequenceSyntax WithTableSequence(
this ICreateTableColumnOptionOrWithColumnSyntax tableSyntax, FluentMigrator.Migration migration)
{
var expression = ((CreateTableExpressionBuilder)tableSyntax).Expression;
var fromTable = expression.TableName;
var schemaName = expression.SchemaName;
var sequenceName = $"{fromTable}_seq";
return(migration.Create
.Sequence(sequenceName.ToLower())
.InSchema(schemaName)
.MaxValue(int.MaxValue).IncrementBy(1));
}
public static string GeneratePkName(this FluentMigrator.Migration migration, string primaryTableName, string idColumn)
{
const string prefix = "PK_";
const string postfix = "";
var pkName = $"{prefix}{primaryTableName}_{idColumn}{postfix}";
if (pkName.Length > 30)
{
pkName = $"{prefix}{GetShortNameOfTableName(primaryTableName)}_{GetShortNameOfTableName(idColumn)}{postfix}";
}
return(pkName);
}
public static string GenerateFkName(this FluentMigrator.Migration migrationModel, string primaryTableName, string fromTableName)
{
const string prefix = "FK_";
const string postfix = "";
var fkName = $"{prefix}{fromTableName}_{primaryTableName}{postfix}";
if (fkName.Length > 30)
{
fkName = $"{prefix}{GetShortNameOfTableName(fromTableName)}_{GetShortNameOfTableName(primaryTableName)}{postfix}";
}
return(fkName);
}
/// <summary>
/// return Primary Key name
/// </summary>
/// <param name="migration"></param>
/// <param name="primaryTableName"></param>
/// <param name="idColumn"></param>
/// <param name="schemaPrefix"></param>
/// <returns></returns>
public static string GeneratePkName(this FluentMigrator.Migration migration, string primaryTableName, string idColumn, string schemaPrefix = null)
{
const string prefix = "";
const string postfix = "_PK";
var pkName = $"{prefix}{primaryTableName.GetPrefixedName(schemaPrefix)}_{idColumn}{postfix}";
if (pkName.Length > 30)
{
var length = 13 - (schemaPrefix ?? "").Length / 2;
pkName = $"{prefix}{GetShortNameOfTableName(primaryTableName, length).GetPrefixedName(schemaPrefix)}_{GetShortNameOfTableName(idColumn, length)}{postfix}";
}
return(pkName);
}
/// <summary>
/// return Foreign Key name
/// </summary>
/// <param name="migrationModel"></param>
/// <param name="primaryTableName"></param>
/// <param name="fromTableName"></param>
/// <param name="schemaPrefix"></param>
/// <returns></returns>
public static string GenerateFkName(this FluentMigrator.Migration migrationModel, string primaryTableName, string fromTableName, string schemaPrefix = null)
{
const string prefix = "FK_";
const string postfix = "";
var fkName = $"{prefix}{fromTableName.GetPrefixedName(schemaPrefix)}_{primaryTableName}{postfix}";
if (fkName.Length > 30)
{
var length = 13 - (schemaPrefix ?? "").Length / 2;
fkName = $"{prefix}{GetShortNameOfTableName(fromTableName, length).GetPrefixedName(schemaPrefix)}_{GetShortNameOfTableName(primaryTableName, length)}{postfix}";
}
return(fkName);
}
/// <summary>
/// Create a foreign columnName to the table <paramref name="primaryTableName"/><br/>
/// ------------------------------------------------------------------ <br/>
/// If <paramref name="columnName"/> is specified, the new column-name will follow that value<br/>
/// If <paramref name="columnName"/> is unspecified, the new column-name will be computed<br/>
/// <br/>
/// If <paramref name="primaryColumnName"/> is specified, the primary-table-column will follow that value<br/>
/// If <paramref name="primaryColumnName"/> is unspecified, the primary-table-column will be computed<br/>
/// <br/>
/// i.e:<br/>
/// <paramref name="columnName"/> is unspecified and <paramref name="primaryTableName"/> is "Parent" => column-name will be computed to "ParentId"<br/>
/// <br/>
/// <paramref name="primaryColumnName"/> is unspecified => the primary-table-column will be "Id"<br/>
/// </summary>
/// <param name="syntax"></param>
/// <param name="primaryTableName"></param>
/// <param name="migration"></param>
/// <param name="schemaPrefix"></param>
/// <param name="columnName"></param>
/// <param name="primaryColumnName"></param>
/// <param name="errors"></param>
/// <returns></returns>
public static ICreateTableColumnAsTypeSyntax WithForeignKeyColumn(
this ICreateTableWithColumnSyntax syntax,
string primaryTableName,
FluentMigrator.Migration migration,
string schemaPrefix = null,
string columnName = null,
string primaryColumnName = null,
params int[] errors)
{
var expression = ((CreateTableExpressionBuilder)syntax).Expression;
var fromTable = expression.TableName.GetPrefixedName(schemaPrefix);
var fromSchemaName = expression.SchemaName;
var toSchemaName = fromSchemaName;
var split = primaryTableName.Split('.');
if (split.Length == 2)
{
primaryTableName = split[1];
toSchemaName = split[0];
}
var fkName = migration?.GenerateFkName(primaryTableName, fromTable.TrimPrefixName(schemaPrefix), schemaPrefix);
columnName = columnName ?? primaryTableName + ColumnName.Id;
primaryColumnName = primaryColumnName ?? ColumnName.Id;
var syntaxWithColumn = syntax.WithColumn(columnName);
var fkSyntax = migration?.Create
.ForeignKey(fkName)
.FromTable(fromTable).InSchema(fromSchemaName)
.ForeignColumn(columnName)
.ToTable(primaryTableName).InSchema(toSchemaName)
.PrimaryColumn(primaryColumnName)
.OnDelete(Rule.SetNull);
if (fkSyntax is CreateForeignKeyExpressionBuilder builder)
{
var id = $"{nameof(CreateForeignKeyExpression)}_{builder.Expression.ForeignKey.PrimaryTable}";
AddOrUpdateErrorFilter(id, errors);
var id2 = $"{nameof(CreateForeignKeyExpression)}_{builder.Expression.ForeignKey.ForeignTable}";
AddOrUpdateErrorFilter(id2, errors);
}
return(syntaxWithColumn);
}
public static ICreateTableColumnAsTypeSyntax WithForeignKeyColumn(
this ICreateTableWithColumnSyntax table, string primaryTableName, FluentMigrator.Migration migration)
{
var expression = ((CreateTableExpressionBuilder)table).Expression;
var fromTable = expression.TableName;
var schemaName = expression.SchemaName;
var fkName = migration?.GenerateFkName(primaryTableName, fromTable);
var foreignColumn = primaryTableName + ColumnName.Id;
var syntax = table.WithColumn(foreignColumn);
migration?.Create
.ForeignKey(fkName)
.FromTable(fromTable)
.InSchema(schemaName)
.ForeignColumn(foreignColumn)
.ToTable(primaryTableName)
.InSchema(schemaName)
.PrimaryColumn(ColumnName.Id)
.OnDelete(Rule.SetNull);
return(syntax);
}
public static ICreateTableColumnOptionOrWithColumnSyntax WithIdAsInt32Column(this ICreateTableWithColumnSyntax tableWithColumnSyntax, FluentMigrator.Migration migration)
{
var syntax = tableWithColumnSyntax.WithIdColumn(migration).AsInt32().NotNullable();
return(syntax);
}
public static ICreateTableColumnOptionOrWithColumnSyntax WithIdAsGuidColumn(this ICreateTableWithColumnSyntax tableWithColumnSyntax, FluentMigrator.Migration migration)
{
return(tableWithColumnSyntax.WithIdColumn(migration).AsGuid().NotNullable());
}
public static ICreateTableColumnAsTypeSyntax WithIdColumn(this ICreateTableWithColumnSyntax tableWithColumnSyntax, FluentMigrator.Migration migration)
{
var expression = ((CreateTableExpressionBuilder)tableWithColumnSyntax).Expression;
var syntax = tableWithColumnSyntax
.WithColumn(ColumnName.Id);
migration.Create
.PrimaryKey(migration?.GeneratePkName(expression.TableName, ColumnName.Id))
.OnTable(expression.TableName)
.WithSchema(expression.SchemaName)
.Column(ColumnName.Id);
return(syntax);
}
public static IAlterTableColumnOptionOrAddColumnOrAlterColumnSyntax AddForeignKey(this IAlterTableAddColumnOrAlterColumnSyntax table, string primaryTableName, FluentMigrator.Migration migration)
{
var expression = ((AlterTableExpressionBuilder)table).Expression;
var fromTableName = expression.TableName;
var fkName = migration.GenerateFkName(primaryTableName, fromTableName);
return(table
.AddColumn(primaryTableName + ColumnName.Id).AsGuid()
.ForeignKey(fkName, primaryTableName, ColumnName.Id));
}
/// <summary>
/// Execute Sql. If Sql contains line-breaks, sql is split into smaller sql statements and execute them separately
/// </summary>
/// <param name="migration"></param>
/// <param name="sql"></param>
public static void ExecuteSql(this FluentMigrator.Migration migration, string sql)
{
ExecuteSql(migration.Execute.Sql, sql);
}
public static void ParaquickTables(this FluentMigrator.Builders.Create.ICreateExpressionRoot Create, FluentMigrator.Migration migration)
{
Create.Table("paraquick_companies")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey().Identity()
.WithColumn("name").AsParaType(ParaTypes.Name)
.WithColumn("user_name").AsParaType(ParaTypes.Name).Unique()
.WithColumn("password").AsParaType(ParaTypes.Name)
.WithColumn("hcp_xml").AsParaType(ParaTypes.Max).Nullable()
.WithColumn("country").AsParaType(ParaTypes.Name).Nullable()
.WithColumn("major").AsParaType(ParaTypes.Int32).Nullable()
.WithColumn("minor").AsParaType(ParaTypes.Int32).Nullable();
Create.Table("paraquick_session_statuses")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey()
.WithColumn("name").AsParaType(ParaTypes.Name);
migration.Insert.IntoTable("paraquick_session_statuses").Row(new { id = 1, name = "New" });
migration.Insert.IntoTable("paraquick_session_statuses").Row(new { id = 2, name = "Open" });
migration.Insert.IntoTable("paraquick_session_statuses").Row(new { id = 3, name = "Incomplete" });
migration.Insert.IntoTable("paraquick_session_statuses").Row(new { id = 4, name = "Success" });
migration.Insert.IntoTable("paraquick_session_statuses").Row(new { id = 5, name = "Error" });
Create.Table("paraquick_sessions")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey().Identity()
.WithColumn("company_id").AsParaType(ParaTypes.Key).ForeignKey("paraquick_companies", "id")
.WithColumn("ticket").AsParaType(ParaTypes.GuidString).Unique()
.WithColumn("create_date").AsParaType(ParaTypes.DateTime)
.WithColumn("start_date").AsParaType(ParaTypes.DateTime).Nullable()
.WithColumn("end_date").AsParaType(ParaTypes.DateTime).Nullable()
.WithColumn("status_id").AsParaType(ParaTypes.Key).ForeignKey("paraquick_session_statuses", "id");
Create.Table("paraquick_session_errors")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey().Identity()
.WithColumn("session_id").AsParaType(ParaTypes.Key).ForeignKey("paraquick_sessions", "id")
.WithColumn("date").AsParaType(ParaTypes.DateTime)
.WithColumn("message").AsParaType(ParaTypes.LongText).Nullable();
Create.Table("paraquick_message_types")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey().Identity()
.WithColumn("name").AsParaType(ParaTypes.Name)
.WithColumn("request_type_name").AsParaType(ParaTypes.Name)
.WithColumn("response_type_name").AsParaType(ParaTypes.Name);
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "CustomerAdd", request_type_name = "CustomerAddRqType", response_type_name = "CustomerAddRsType" });
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "CustomerQuery", request_type_name = "CustomerQueryRqType", response_type_name = "CustomerQueryRsType" });
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "CustomerMod", request_type_name = "CustomerModRqType", response_type_name = "CustomerModRsType" });
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "EstimateAdd", request_type_name = "EstimateAddRqType", response_type_name = "EstimateAddRsType" });
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "EstimateQuery", request_type_name = "EstimateQueryRqType", response_type_name = "EstimateQueryRsType" });
migration.Insert.IntoTable("paraquick_message_types").Row(new { name = "EstimateMod", request_type_name = "EstimateModRqType", response_type_name = "EstimateModRsType" });
Create.Table("paraquick_messages")
.WithColumn("id").AsParaType(ParaTypes.Key).PrimaryKey().Identity()
.WithColumn("session_id").AsParaType(ParaTypes.Key).ForeignKey("paraquick_sessions", "id")
.WithColumn("message_set_sequence").AsParaType(ParaTypes.Int32)
.WithColumn("message_sequence").AsParaType(ParaTypes.Int32)
.WithColumn("message_type_id").AsParaType(ParaTypes.Key).ForeignKey("paraquick_message_types", "id")
.WithColumn("application_entity_id").AsParaType(ParaTypes.Key)
.WithColumn("request_id").AsParaType(ParaTypes.GuidString)
.WithColumn("request_xml").AsParaType(ParaTypes.Max)
.WithColumn("request_date").AsParaType(ParaTypes.DateTime)
.WithColumn("response_xml").AsParaType(ParaTypes.Max).Nullable()
.WithColumn("response_date").AsParaType(ParaTypes.DateTime).Nullable()
.WithColumn("status_code").AsParaType(ParaTypes.Description).Nullable()
.WithColumn("status_severity").AsParaType(ParaTypes.Note).Nullable()
.WithColumn("status_message").AsParaType(ParaTypes.LongText).Nullable();
Create.Index("uidx_paraquick_messages").OnTable("paraquick_messages")
.OnColumn("session_id").Unique()
.OnColumn("message_set_sequence").Unique()
.OnColumn("message_sequence").Unique()
.OnColumn("request_id").Unique();
}
