/// <inheritdoc />
protected override string BuildSql(LookupDbModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("set nocount on;");
if (UseTransaction)
{
sql.AppendLine("set xact_abort on; -- rollback on error");
sql.AppendLine("begin tran;");
}
EnsureSchema(sql);
CreateViews(sql, model.Lookups);
if (!DoNotGenerateConstraints)
{
AddCheckConstraints(sql, model.References, model.Lookups);
}
if (UseTransaction)
{
sql.AppendLine("commit;");
}
return(sql.ToString());
}
private LookupDbModel BuildModelFromContext(DbContext context)
{
// recurse through dbsets and references finding anything that uses an enum
var enumReferences = FindEnumReferences(context);
// for the list of enums generate and missing tables
var enums = enumReferences
.Select(r => r.EnumType)
.Distinct()
.OrderBy(r => r.Name)
.ToList();
var lookups = (
from enm in enums
select new LookupData
{
Name = enm.Name,
NumericType = enm.GetEnumUnderlyingType(),
Values = _enumParser.GetLookupValues(enm),
}).ToList();
var model = new LookupDbModel
{
Lookups = lookups,
References = enumReferences,
Schema = context.GetDefaultSchema()
};
return(model);
}
protected override string BuildSql(LookupDbModel model)
{
var sql = new StringBuilder();
sql.AppendLine("set nocount on;");
if (UseTransaction)
{
sql.AppendLine("set xact_abort on; -- rollback on error");
sql.AppendLine("begin tran;");
}
EnsureSchema(sql);
sql.AppendLine(CreateTables(model.Lookups));
sql.AppendLine(PopulateLookups(model.Lookups));
if (!DoNotGenerateConstraints)
{
sql.AppendLine(AddForeignKeys(model.References));
}
if (UseTransaction)
{
sql.AppendLine("commit;");
}
return(sql.ToString());
}
private string BuildSql(LookupDbModel model)
{
var sql = new StringBuilder();
sql.AppendLine("set nocount on;");
sql.AppendLine("set xact_abort on; -- rollback on error");
sql.AppendLine("begin tran;");
sql.AppendLine(CreateTables(model.Lookups));
sql.AppendLine(PopulateLookups(model.Lookups));
sql.AppendLine(AddForeignKeys(model.References));
sql.AppendLine("commit;");
return sql.ToString();
}
private string PopulateLookups(LookupDbModel model)
{
var sql = new StringBuilder();
sql.AppendLine(string.Format("CREATE TABLE #lookups (Id int, Name nvarchar({0}) COLLATE database_default);", NameFieldLength));
foreach (var lookup in model.Lookups)
{
sql.AppendLine(PopulateLookup(lookup, model.Schema));
}
sql.AppendLine("DROP TABLE #lookups;");
return(sql.ToString());
}
private string AddForeignKeys(LookupDbModel model)
{
var sql = new StringBuilder();
foreach (var enumReference in model.References)
{
var fkName = string.Format("FK_{0}_{1}", enumReference.ReferencingTable, enumReference.ReferencingField);
sql.AppendFormat(
" IF OBJECT_ID('{4}.{0}', 'F') IS NULL ALTER TABLE [{4}].[{1}] ADD CONSTRAINT {0} FOREIGN KEY ([{2}]) REFERENCES [{4}].[{3}] (Id);{5}",
fkName, enumReference.ReferencingTable, enumReference.ReferencingField, TableName(enumReference.EnumType.Name), model.Schema, Environment.NewLine);
}
return(sql.ToString());
}
private string BuildSql(LookupDbModel model)
{
var sql = new StringBuilder();
sql.AppendLine("set nocount on;");
sql.AppendLine("set xact_abort on; -- rollback on error");
sql.AppendLine("begin tran;");
sql.AppendLine(CreateTables(model));
sql.AppendLine(PopulateLookups(model));
sql.AppendLine(AddForeignKeys(model));
sql.AppendLine("commit;");
return(sql.ToString());
}
private string CreateTables(LookupDbModel model)
{
var sql = new StringBuilder();
foreach (var lookup in model.Lookups)
{
sql.AppendFormat(
@"IF OBJECT_ID('{3}.{0}', 'U') IS NULL
begin
CREATE TABLE [{3}].[{0}] (Id {2} CONSTRAINT PK_{0} PRIMARY KEY, Name nvarchar({1}));
exec sys.sp_addextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA', @level0name=N'{3}', @level1type=N'TABLE',
@level1name=N'{0}', @value=N'Automatically generated. Contents will be overwritten on app startup. Table & contents generated by https://github.com/timabell/ef-enum-to-lookup';
end{4}" ,
TableName(lookup.Name), NameFieldLength, NumericSqlType(lookup.NumericType), model.Schema, Environment.NewLine);
}
return(sql.ToString());
}
public string GenerateMigrationSql(LookupDbModel model)
{
return BuildSql(model);
}
public void Apply(LookupDbModel model, Action<string, IEnumerable<SqlParameter>> runSql)
{
var sql = BuildSql(model);
runSql(sql, null);
}
/// <summary>
/// Generates the migration SQL needed to update the database to match
/// the enums in the current model.
/// </summary>
/// <param name="model">Details of lookups and foreign keys to add/update</param>
/// <returns>The generated SQL script</returns>
public virtual string GenerateMigrationSql(LookupDbModel model)
{
return(BuildSql(model));
}
/// <summary>
/// Make the required changes to the database.
/// </summary>
/// <param name="model">Details of lookups and foreign keys to add/update</param>
/// <param name="runSql">A callback providing a means to execute sql against the
/// server. (Or possibly write it to a file for later use.</param>
public virtual void Apply(LookupDbModel model, Action <string, IEnumerable <SqlParameter> > runSql)
{
string sql = BuildSql(model);
runSql(sql, null);
}
/// <summary> /// Abstract method which creates SQL considering all the <see cref="IDbHandler"/> settings. /// </summary> /// <param name="model">Details of lookups and foreign keys to add/update</param> /// <returns>The generated SQL script</returns> protected abstract string BuildSql(LookupDbModel model);
private LookupDbModel BuildModelFromContext(DbContext context)
{
// recurse through dbsets and references finding anything that uses an enum
var enumReferences = FindEnumReferences(context);
// for the list of enums generate and missing tables
var enums = enumReferences
.Select(r => r.EnumType)
.Distinct()
.OrderBy(r => r.Name)
.ToList();
var lookups = (
from enm in enums
select new LookupData
{
Name = enm.Name,
NumericType = enm.GetEnumUnderlyingType(),
Values = _enumParser.GetLookupValues(enm),
}).ToList();
var model = new LookupDbModel
{
Lookups = lookups,
References = enumReferences,
};
return model;
}
