/// <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; }