public static string DbObjectName(this Type type, bool squareBraces = false)
{
var obj = DbObject.FromType(type);
obj.SquareBraces = squareBraces;
return(obj.ToString());
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
DbObject obj = DbObject.FromType(_propertyInfo.DeclaringType);
obj.SquareBraces = false;
yield return($"EXEC sp_rename '{obj}.{_attr.OldName}', '{_propertyInfo.SqlColumnName()}', 'COLUMN'");
ForeignKeyAttribute fkAttr = _propertyInfo.GetAttribute <ForeignKeyAttribute>();
if (fkAttr != null)
{
yield return($"ALTER TABLE [{obj.Schema}].[{obj.Name}] DROP CONSTRAINT [FK_{obj.ConstraintName()}_{_attr.OldName}]");
if (fkAttr.CreateIndex)
{
yield return($"DROP INDEX [IX_{DbObject.ConstraintName(_propertyInfo.DeclaringType)}_{_attr.OldName}] ON [{obj.Schema}].[{obj.Name}]");
}
CreateForeignKey fk = new CreateForeignKey(_propertyInfo);
foreach (var cmd in fk.SqlCommands(connection))
{
yield return(cmd);
}
}
}
public override IEnumerable <ChangeHistory <TKey> > QueryChangeHistory(IDbConnection connection, TKey id, int timeZoneOffset = 0)
{
DbObject obj = DbObject.FromType(typeof(TRecord));
string tableName = $"{obj.Schema}_{obj.Name}";
var results = connection.Query <ChangeHistoryRecord <TKey> >(
$@"SELECT * FROM [{_changesSchema}].[{tableName}] WHERE [RecordId]=@id ORDER BY [DateTime] DESC", new { id = id });
return(results.GroupBy(item => new
{
RecordId = item.RecordId,
Version = item.Version
}).Select(ch =>
{
return new ChangeHistory <TKey>()
{
RecordId = ch.Key.RecordId,
DateTime = ch.First().DateTime.AddHours(timeZoneOffset),
Version = ch.Key.Version,
Properties = ch.Select(chr => new PropertyChange()
{
PropertyName = chr.ColumnName,
OldValue = chr.OldValue,
NewValue = chr.NewValue
})
};
}));
}
private static string RenameInfo(PropertyInfo propertyInfo)
{
RenameFromAttribute attr = propertyInfo.GetAttribute <RenameFromAttribute>();
DbObject obj = DbObject.FromType(propertyInfo.DeclaringType);
obj.SquareBraces = false;
return($"{obj}.{attr.OldName} -> {propertyInfo.SqlColumnName()}");
}
public static string RenameDescription(Type modelType)
{
RenameFromAttribute attr = modelType.GetAttribute <RenameFromAttribute>();
DbObject obj = DbObject.FromType(modelType);
obj.SquareBraces = false;
return($"{attr.OldName} -> {obj}");
}
public string TableName(Type tableType = null)
{
Type t = (tableType == null) ? typeof(TRecord) : tableType;
string result = DbObject.FromType(t).ToString();
//if (_squareBraces) result = string.Join(".", result.Split('.').Select(s => $"[{s}]"));
return(result);
}
public override IEnumerable <string> ValidationErrors(IDbConnection connection)
{
var tbl = DbObject.FromType(_propertyInfo.DeclaringType);
tbl.SquareBraces = false;
if (!connection.ColumnExists(tbl.Schema, tbl.Name, _attr.OldName))
{
yield return($"Can't rename from {tbl}.{_attr.OldName} -- column doesn't exist.");
}
if (_propertyInfo.SqlColumnName().Equals(_attr.OldName))
{
yield return($"Can't rename column to the same name.");
}
}
protected override object OnGetChangesPropertyValue(PropertyInfo propertyInfo, object record, IDbConnection connection)
{
object result = base.OnGetChangesPropertyValue(propertyInfo, record, connection);
ForeignKeyAttribute fk;
DereferenceExpression dr;
if (result != null && propertyInfo.HasAttribute(out fk) && fk.PrimaryTableType.HasAttribute(out dr))
{
DbObject obj = DbObject.FromType(fk.PrimaryTableType);
result = connection.QueryFirst <string>(
$@"SELECT {dr.Expression} FROM [{obj.Schema}].[{obj.Name}]
WHERE [{fk.PrimaryTableType.IdentityColumnName()}]=@id" , new { id = result });
}
return(result);
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
DbObject newTable = DbObject.FromType(_modelType);
CreateTable ct = new CreateTable(_modelType);
if (!connection.TableExists(newTable.Schema, newTable.Name))
{
foreach (var cmd in ct.SqlCommands(connection))
{
yield return(cmd);
}
}
DbObject oldTable = DbObject.Parse(_attr.OldName);
if (connection.TableExists(oldTable.Schema, oldTable.Name))
{
if (!connection.IsTableEmpty(oldTable.Schema, oldTable.Name))
{
yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] ON");
string columnNames = string.Join(", ", ct.ColumnProperties().Select(pi => $"[{pi.SqlColumnName()}]").Concat(new string[] { $"[{SqlDb<int>.IdentityColumnName}]" }));
yield return($"INSERT INTO [{newTable.Schema}].[{newTable.Name}] ({columnNames}) SELECT {columnNames} FROM [{oldTable.Schema}].[{oldTable.Name}]");
yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] OFF");
}
DbObject.SetObjectId(connection, oldTable);
foreach (var cmd in connection.GetFKDropStatements(oldTable.ObjectId))
{
yield return(cmd);
}
yield return($"DROP TABLE [{oldTable.Schema}].[{oldTable.Name}]");
}
}
public override IEnumerable <string> ValidationErrors(IDbConnection connection)
{
var oldTable = DbObject.Parse(_attr.OldName);
oldTable.SquareBraces = false;
if (!connection.TableExists(oldTable.Schema, oldTable.Name))
{
yield return($"Can't rename from {oldTable} -- table doesn't exist.");
}
var newTable = DbObject.FromType(_modelType);
if (oldTable.Equals(newTable))
{
yield return($"Can't rename table to the same name.");
}
if (_modelType.GetProperties().Any(pi => pi.HasAttribute <RenameFromAttribute>()))
{
yield return($"Can't rename columns while containing table is being renamed. Please do these changes one after the other.");
}
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
yield return($"ALTER TABLE {DbObject.SqlServerName(_pi.DeclaringType)} DROP CONSTRAINT [{_pi.ForeignKeyName()}]");
ForeignKeyAttribute fk = _pi.GetForeignKeyAttribute();
var obj = DbObject.FromType(_pi.DeclaringType);
if (!fk.CreateIndex)
{
yield return($"DROP INDEX [{_pi.IndexName()}] ON {obj}");
}
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
}
public override IEnumerable <string> SqlCommands(IDbConnection connection)
{
foreach (var cmd in base.SqlCommands(connection))
{
yield return(cmd);
}
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);
if (fk.CreateIndex && !connection.Exists("[sys].[indexes] WHERE [name]=@name", new { name = _pi.IndexName() }))
{
var obj = DbObject.FromType(_pi.DeclaringType);
yield return($"CREATE INDEX [{_pi.IndexName()}] ON {obj} ([{_pi.SqlColumnName()}])");
}
}
public AddColumn(PropertyInfo propertyInfo) : base(MergeObjectType.Column, MergeActionType.Create, $"Add column {propertyInfo.DeclaringType.Name}.{propertyInfo.Name}", nameof(AddColumn))
{
_propertyInfo = propertyInfo;
_object = DbObject.FromType(_propertyInfo.ReflectedType);
}
protected override void OnCaptureChanges(IDbConnection connection, TKey id, IEnumerable <PropertyChange> changes, string userName = null)
{
SqlConnection cn = connection as SqlConnection;
if (!cn.Exists("[sys].[schemas] WHERE [name]=@name", new { name = _changesSchema }))
{
cn.Execute($"CREATE SCHEMA [{_changesSchema}]");
}
var typeMap = new Dictionary <Type, string>()
{
{ typeof(int), "int" },
{ typeof(long), "bigint" },
{ typeof(Guid), "uniqueidentifier" }
};
DbObject obj = DbObject.FromType(typeof(TRecord));
string tableName = $"{obj.Schema}_{obj.Name}";
if (!cn.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = _changesSchema, name = $"{tableName}_Versions" }))
{
cn.Execute($@"CREATE TABLE [{_changesSchema}].[{tableName}_Versions] (
[RecordId] {typeMap[typeof(TKey)]} NOT NULL,
[NextVersion] int NOT NULL DEFAULT (1),
CONSTRAINT [PK_{_changesSchema}_{tableName}_Versions] PRIMARY KEY ([RecordId])
)" );
}
if (!cn.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = _changesSchema, name = tableName }))
{
cn.Execute($@"CREATE TABLE [{_changesSchema}].[{tableName}] (
[RecordId] {typeMap[typeof(TKey)]} NOT NULL,
[Version] int NOT NULL,
[UserName] nvarchar(256) NOT NULL,
[ColumnName] nvarchar(100) NOT NULL,
[OldValue] nvarchar(max) NULL,
[NewValue] nvarchar(max) NULL,
[DateTime] datetime NOT NULL DEFAULT (getutcdate()),
CONSTRAINT [PK_{_changesSchema}_{obj.Name}] PRIMARY KEY ([RecordId], [Version], [ColumnName])
)" );
}
int version = 0;
while (version == 0)
{
version = cn.QueryFirstOrDefault <int>($"SELECT [NextVersion] FROM [{_changesSchema}].[{tableName}_Versions] WHERE [RecordId]=@id", new { id = id });
if (version == 0)
{
cn.Execute($"INSERT INTO [{_changesSchema}].[{tableName}_Versions] ([RecordId]) VALUES (@id)", new { id = id });
}
}
cn.Execute($"UPDATE [{_changesSchema}].[{tableName}_Versions] SET [NextVersion]=[NextVersion]+1 WHERE [RecordId]=@id", new { id = id });
foreach (var change in changes)
{
cn.Execute(
$@"INSERT INTO [{_changesSchema}].[{tableName}] ([RecordId], [Version], [UserName], [ColumnName], [OldValue], [NewValue])
VALUES (@id, @version, @userName, @columnName, @oldValue, @newValue)" ,
new
{
id = id, version = version,
columnName = change.PropertyName,
userName = userName ?? "<unknown>",
oldValue = CleanMinDate(change.OldValue) ?? "<null>",
newValue = CleanMinDate(change.NewValue) ?? "<null>"
});
}
}