public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
var syntax = discoveredTable.GetQuerySyntaxHelper();
string sql =
@"DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum
FROM {1}
) as f
where RowNum > 1";
string columnList = string.Join(",",
discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName())));
string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName());
var server = discoveredTable.Database.Server;
using (var con = args.GetManagedConnection(server))
{
using (var cmd = server.GetCommand(sqlToExecute, con))
args.ExecuteNonQuery(cmd);
}
}
public virtual void AddColumn(DatabaseOperationArgs args, DiscoveredTable table, string name, string dataType, bool allowNulls)
{
var syntax = table.GetQuerySyntaxHelper();
using (var con = args.GetManagedConnection(table))
{
using (var cmd = table.Database.Server.GetCommand("ALTER TABLE " + table.GetFullyQualifiedName() + " ADD " + syntax.EnsureWrapped(name) + " " + dataType + " " + (allowNulls ? "NULL" : "NOT NULL"), con))
args.ExecuteNonQuery(cmd);
}
}
public virtual void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
var server = discoveredTable.Database.Server;
//if it's got a primary key they it's distinct! job done
if (discoveredTable.DiscoverColumns().Any(c => c.IsPrimaryKey))
{
return;
}
var tableName = discoveredTable.GetFullyQualifiedName();
var tempTable = discoveredTable.Database.ExpectTable(discoveredTable.GetRuntimeName() + "_DistinctingTemp").GetFullyQualifiedName();
using (var con = args.TransactionIfAny == null
? server.BeginNewTransactedConnection()
: //start a new transaction
args.GetManagedConnection(server)) //or continue the ongoing transaction
{
using (var cmdDistinct =
server.GetCommand(
string.Format("CREATE TABLE {1} AS SELECT distinct * FROM {0}", tableName, tempTable), con))
args.ExecuteNonQuery(cmdDistinct);
//this is the point of no return so don't cancel after this point
using (var cmdTruncate = server.GetCommand(string.Format("DELETE FROM {0}", tableName), con))
{
cmdTruncate.CommandTimeout = args.TimeoutInSeconds;
cmdTruncate.ExecuteNonQuery();
}
using (var cmdBack = server.GetCommand(string.Format("INSERT INTO {0} (SELECT * FROM {1})", tableName, tempTable), con))
{
cmdBack.CommandTimeout = args.TimeoutInSeconds;
cmdBack.ExecuteNonQuery();
}
using (var cmdDropDistinctTable = server.GetCommand(string.Format("DROP TABLE {0}", tempTable), con))
{
cmdDropDistinctTable.CommandTimeout = args.TimeoutInSeconds;
cmdDropDistinctTable.ExecuteNonQuery();
}
//if we opened a new transaction we should commit it
if (args.TransactionIfAny == null)
{
con.ManagedTransaction?.CommitAndCloseConnection();
}
}
}
/// <inheritdoc/>
public virtual DiscoveredRelationship AddForeignKey(DatabaseOperationArgs args, Dictionary <DiscoveredColumn, DiscoveredColumn> foreignKeyPairs, bool cascadeDeletes, string constraintName = null)
{
var foreignTables = foreignKeyPairs.Select(c => c.Key.Table).Distinct().ToArray();
var primaryTables = foreignKeyPairs.Select(c => c.Value.Table).Distinct().ToArray();
if (primaryTables.Length != 1 || foreignTables.Length != 1)
{
throw new ArgumentException("Primary and foreign keys must all belong to the same table", nameof(foreignKeyPairs));
}
var primary = primaryTables[0];
var foreign = foreignTables[0];
if (constraintName == null)
{
constraintName = primary.Database.Helper.GetForeignKeyConstraintNameFor(foreign, primary);
}
string constraintBit = primary.Database.Helper.GetForeignKeyConstraintSql(foreign.GetRuntimeName(), primary.GetQuerySyntaxHelper(),
foreignKeyPairs
.ToDictionary(k => (IHasRuntimeName)k.Key, v => v.Value), cascadeDeletes, constraintName);
string sql = $@"ALTER TABLE {foreign.GetFullyQualifiedName()}
ADD " + constraintBit;
using (var con = args.GetManagedConnection(primary))
{
try
{
using (var cmd = primary.Database.Server.GetCommand(sql, con))
args.ExecuteNonQuery(cmd);
}
catch (Exception e)
{
throw new AlterFailedException("Failed to create relationship using SQL:" + sql, e);
}
}
return(primary.DiscoverRelationships(args.TransactionIfAny).Single(
r => r.Name.Equals(constraintName, StringComparison.CurrentCultureIgnoreCase)
));
}
public virtual void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
{
var syntax = table.GetQuerySyntaxHelper();
using (var connection = args.GetManagedConnection(table))
{
try{
string sql = string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})",
table.GetFullyQualifiedName(),
string.Join(",", discoverColumns.Select(c => syntax.EnsureWrapped(c.GetRuntimeName())))
);
using (DbCommand cmd = table.Database.Server.Helper.GetCommand(sql, connection.Connection, connection.Transaction))
args.ExecuteNonQuery(cmd);
}
catch (Exception e)
{
throw new AlterFailedException(string.Format(FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(c => c.GetRuntimeName()))), e);
}
}
}
public override void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
{
try
{
using (var connection = args.GetManagedConnection(table))
{
var columnHelper = GetColumnHelper();
foreach (var col in discoverColumns.Where(dc => dc.AllowNulls))
{
var alterSql = columnHelper.GetAlterColumnToSql(col, col.DataType.SQLType, false);
using (var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction))
args.ExecuteNonQuery(alterCmd);
}
}
}
catch (Exception e)
{
throw new AlterFailedException(string.Format(FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(c => c.GetRuntimeName()))), e);
}
base.CreatePrimaryKey(args, table, discoverColumns);
}