public static DbObject FromType(Type modelType, IDbConnection connection)
{
DbObject obj = FromType(modelType);
obj.ObjectID = connection.QueryFirstOrDefault <int>("SELECT [object_id] FROM [sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = obj.Schema, name = obj.Name });
return(obj);
}
public override IEnumerable <string> SqlCommands()
{
var obj = DbObject.FromType(_modelType, _connection);
var temp = obj.GetTemp();
string sourceTableName = obj.ToString();
string tempTableName = temp.ToString();
yield return(SelectInto(obj, tempTableName));
DropTable drop = new DropTable(obj, _connection);
foreach (var cmd in drop.SqlCommands())
{
yield return(cmd);
}
CreateTable create = new CreateTable(_modelType);
foreach (var cmd in create.SqlCommands())
{
yield return(cmd);
}
RestoreTempTable rt = new RestoreTempTable(temp, _modelType, _connection,
_columns.Where(cr => !cr.PropertyInfo.HasAttribute <CalculatedAttribute>()).ToDictionary(
item => item.PropertyInfo.SqlColumnName(),
item => item.PropertyInfo.SqlDefaultExpression(forCreateTable: false)));
foreach (var cmd in rt.SqlCommands())
{
yield return(cmd);
}
}
private IEnumerable <string> CreateTableUniqueConstraints()
{
List <string> results = new List <string>();
if (PrimaryKeyColumns(markedOnly: true).Any())
{
results.Add($"CONSTRAINT [U_{DbObject.ConstraintName(_modelType)}_Id] UNIQUE ([Id])");
}
results.AddRange(_modelType.GetProperties().Where(pi =>
{
var unique = pi.GetCustomAttribute <UniqueKeyAttribute>();
return(unique != null);
}).Select(pi =>
{
return($"CONSTRAINT [U_{DbObject.ConstraintName(_modelType)}_{pi.SqlColumnName()}] UNIQUE ([{pi.SqlColumnName()}])");
}));
results.AddRange(_modelType.GetCustomAttributes <UniqueKeyAttribute>().Select((u, i) =>
{
string constrainName = (string.IsNullOrEmpty(u.ConstraintName)) ? $"U_{DbObject.ConstraintName(_modelType)}_{i}" : u.ConstraintName;
return($"CONSTRAINT [{constrainName}] UNIQUE ({string.Join(", ", u.ColumnNames.Select(col => $"[{col}]"))})");
}));
return(results);
}
public override IEnumerable <string> SqlCommands()
{
yield return
($"CREATE TABLE {DbObject.SqlServerName(_modelType)} (\r\n\t" +
string.Join(",\r\n\t", CreateTableMembers(false)) +
"\r\n)");
}
public static string SqlServerName(Type modelType)
{
DbObject obj = FromType(modelType);
obj.SquareBraces = true;
return(obj.ToString());
}
private IEnumerable <string> CreateTableForeignKeys()
{
List <string> results = new List <string>();
results.AddRange(_modelType.GetProperties().Where(pi => pi.HasAttribute <Attributes.ForeignKeyAttribute>())
.Select(pi =>
{
var fk = pi.GetCustomAttribute <Attributes.ForeignKeyAttribute>();
return
($@"CONSTRAINT [{pi.ForeignKeyName()}] FOREIGN KEY (
[{pi.SqlColumnName()}]
) REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (
[{nameof(DataRecord<int>.Id)}]
)" );
}));
results.AddRange(_modelType.GetCustomAttributes <Attributes.ForeignKeyAttribute>()
.Where(attr => HasColumnName(_modelType, attr.ColumnName))
.Select(fk =>
{
return
($@"CONSTRAINT [FK_{DbObject.ConstraintName(_modelType)}_{fk.ColumnName}] FOREIGN KEY (
[{fk.ColumnName}]
) REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (
[{nameof(DataRecord<int>.Id)}]
)" );
}));
return(results);
}
public ColumnRef(PropertyInfo pi)
{
PropertyInfo = pi;
DbObject obj = DbObject.FromType(pi.ReflectedType);
Schema = obj.Schema;
TableName = obj.Name;
ColumnName = pi.SqlColumnName();
}
public RestoreTempTable(DbObject tempTable, Type modelType, IDbConnection connection, Dictionary <string, string> addColumns = null) : base(MergeObjectType.Table, MergeActionType.Load, $"Restore {tempTable.QualifiedName()}")
{
_tempTable = tempTable;
_modelTable = DbObject.FromTempName(tempTable);
_modelType = modelType;
_addColumns = addColumns;
if (addColumns == null)
{
_addColumns = NullMissingTempColumns(connection);
}
}
public static string ConstraintName(Type modelType)
{
DbObject obj = FromType(modelType);
string result = obj.Name;
if (!obj.Schema.Equals("dbo"))
{
result = obj.Schema.Substring(0, 1).ToUpper() + obj.Schema.Substring(1).ToLower() + result;
}
return(result);
}
public override IEnumerable <string> SqlCommands()
{
ForeignKeyAttribute fk = _pi.GetForeignKeyAttribute();
string cascadeDelete = (fk.CascadeDelete) ? " ON DELETE CASCADE" : string.Empty;
yield return
($"ALTER TABLE {DbObject.SqlServerName(_pi.DeclaringType)} ADD CONSTRAINT [{_pi.ForeignKeyName()}] FOREIGN KEY (\r\n" +
$"\t[{_pi.SqlColumnName()}]\r\n" +
$") REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (\r\n" +
$"\t[{fk.PrimaryTableType.IdentityColumnName()}]\r\n" +
")" + cascadeDelete);
}
internal static IEnumerable <ForeignKeyRef> GetReferencingForeignKeys(Type modelType, IEnumerable <Type> allTypes)
{
return(allTypes.SelectMany(t => GetForeignKeys(t).Where(pi =>
{
ForeignKeyAttribute fk = pi.GetForeignKeyAttribute();
return (fk.PrimaryTableType.Equals(modelType));
}).Select(pi =>
new ForeignKeyRef()
{
ConstraintName = pi.ForeignKeyName(), ReferencingTable = DbObject.FromType(pi.DeclaringType)
}
)));
}
public override IEnumerable <string> SqlCommands()
{
Type modelType = _modelColumn.ReflectedType;
DbObject obj = DbObject.FromType(modelType);
if (_modelColumn.HasAttribute <PrimaryKeyAttribute>())
{
// issue #7, table must be dropped and rebuilt
}
yield return($"ALTER TABLE [{obj.Schema}].[{obj.Name}] ALTER COLUMN [{_modelColumn.SqlColumnName()}] {_modelColumn.SqlColumnType()}");
// restore PK
}
private static IEnumerable <PrimaryKeyRef> GetModelPrimaryKeys(IEnumerable <Type> types, IDbConnection connection)
{
return(types.Select(t =>
{
DbObject obj = DbObject.FromType(t, connection);
return new PrimaryKeyRef()
{
Schema = obj.Schema,
TableName = obj.Name,
ColumnList = string.Join(",", CreateTable.PrimaryKeyColumns(t)),
ObjectId = obj.ObjectID,
ModelType = t
};
}));
}
public override bool Equals(object obj)
{
DbObject test = obj as DbObject;
if (test != null)
{
return(test.Schema.ToLower().Equals(this.Schema.ToLower()) && test.Name.ToLower().Equals(this.Name.ToLower()));
}
Type testType = obj as Type;
if (testType != null)
{
return(Equals(FromType(testType)));
}
return(false);
}
private IEnumerable <Action> GetNewTables(IEnumerable <Type> modelTypes, IDbConnection connection)
{
List <Action> actions = new List <Action>();
foreach (var type in modelTypes)
{
DbObject obj = DbObject.FromType(type);
if (!connection.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = obj.Schema, name = obj.Name }))
{
if (!_createdTables.Contains(obj))
{
_createdTables.Add(obj);
actions.Add(new CreateTable(type));
}
}
}
return(actions);
}
public static DbObject FromTempName(DbObject obj)
{
return(new DbObject(obj.Schema, obj.Name.Substring(0, obj.Name.IndexOf(_tempSuffix))));
}
public CreateTable(Type modelType) : base(MergeObjectType.Table, MergeActionType.Create, DbObject.FromType(modelType).QualifiedName())
{
_modelType = modelType;
}
private string CreateTablePrimaryKey()
{
return($"CONSTRAINT [PK_{DbObject.ConstraintName(_modelType)}] PRIMARY KEY ({string.Join(", ", PrimaryKeyColumns().Select(col => $"[{col}]"))})");
}
public DropTable(DbObject @object, IDbConnection connection) : base(MergeObjectType.Table, MergeActionType.Delete, @object.QualifiedName())
{
_cn = connection;
_object = @object;
_foreignKeys = GetReferencingForeignKeys(connection, @object.ObjectID);
}
private string SelectInto(DbObject sourceObject, string intoTable)
{
return($"SELECT * INTO {intoTable} FROM {sourceObject.ToString()}");
}
private IEnumerable <Action> GetRestorableTempTables(IEnumerable <Type> modelTypes, IDbConnection connection)
{
List <Action> results = new List <Action>();
var tempTables = connection.Query <TempTableRef>(
@"SELECT
SCHEMA_NAME([schema_id]) AS [Schema], [name] AS [TempName],
SUBSTRING([name], 0, LEN([name]) - LEN('_temp') + 1) AS [ModelName],
[object_id] AS [TempObjectId]
FROM
[sys].[tables] WHERE [name] LIKE '%_temp'" );
var restoreTables = from tt in tempTables
join mt in modelTypes on new DbObject(tt.Schema, tt.ModelName) equals DbObject.FromType(mt)
select new { TempObject = new DbObject(tt.Schema, tt.TempName)
{
ObjectID = tt.TempObjectId
}, ModelType = mt };
results.AddRange(restoreTables.Select(rt => new RestoreTempTable(rt.TempObject, rt.ModelType, connection)));
return(results);
}
private IEnumerable <Action> GetNewColumns(IEnumerable <Type> modelTypes, IDbConnection connection)
{
List <Action> results = new List <Action>();
var schemaColumns = GetSchemaColumns(connection);
var dbObjects = schemaColumns.GroupBy(item => new DbObject(item.Schema, item.TableName)
{
ObjectID = item.ObjectID
});
var dcObjectIDs = dbObjects.ToDictionary(obj => obj.Key, obj => obj.Key.ObjectID);
Dictionary <DbObject, Type> dcModelTypes = new Dictionary <DbObject, Type>();
var modelColumns = modelTypes.SelectMany(mt => mt.GetProperties()
.Where(pi => CreateTable.IsSupportedType(pi.PropertyType) && !pi.HasAttribute <NotMappedAttribute>())
.Select(pi =>
{
DbObject obj = DbObject.FromType(mt);
if (!dcModelTypes.ContainsKey(obj))
{
dcModelTypes.Add(obj, mt);
}
return(new ColumnRef()
{
Schema = obj.Schema,
TableName = obj.Name,
ColumnName = pi.SqlColumnName(),
PropertyInfo = pi
});
}));
var newColumns = modelColumns.Where(mcol =>
!_createdTables.Contains(new DbObject(mcol.Schema, mcol.TableName)) &&
!schemaColumns.Any(scol => mcol.Equals(scol)));
foreach (var colGroup in newColumns.GroupBy(item => new DbObject(item.Schema, item.TableName)))
{
if (!_deletedTables.Contains(colGroup.Key))
{
_deletedTables.Add(colGroup.Key);
}
if (!_createdTables.Contains(colGroup.Key))
{
_createdTables.Add(colGroup.Key);
}
if (IsTableEmpty(connection, colGroup.Key.Schema, colGroup.Key.Name) || dcModelTypes[colGroup.Key].HasAttribute <AllowDropAttribute>())
{
colGroup.Key.ObjectID = dcObjectIDs[colGroup.Key];
results.Add(new DropTable(colGroup.Key, connection));
results.Add(new CreateTable(dcModelTypes[colGroup.Key]));
}
else
{
results.Add(new AddColumns(dcModelTypes[colGroup.Key], colGroup, connection));
}
}
return(results);
}