public UniqueTableIndex AddUniqueIndex(ITable table, IColumn[] columns)
{
var index = new UniqueTableIndex(table, columns);
AddIndex(index);
return(index);
}
public UniqueTableIndex AddUniqueIndex(ITable table, Field[] fields)
{
var index = new UniqueTableIndex(table, TableIndex.GetColumnsFromFields(fields));
AddIndex(index);
return(index);
}
public static int DuplicateCount(UniqueTableIndex uniqueIndex, Replacements rep)
{
var primaryKey = uniqueIndex.Table.Columns.Values.Where(a => a.PrimaryKey).Only();
if (primaryKey == null)
{
throw new InvalidOperationException("No primary key found");
}
;
var oldTableName = rep.Apply(Replacements.KeyTablesInverse, uniqueIndex.Table.Name.ToString());
var columnReplacement = rep.TryGetC(Replacements.KeyColumnsForTable(uniqueIndex.Table.Name.ToString()))?.Inverse() ?? new Dictionary <string, string>();
var oldColumns = uniqueIndex.Columns.ToString(c => (columnReplacement.TryGetC(c.Name) ?? c.Name).SqlEscape(), ", ");
var oldPrimaryKey = columnReplacement.TryGetC(primaryKey.Name) ?? primaryKey.Name;
return((int)Executor.ExecuteScalar(
$@"SELECT Count(*) FROM {oldTableName}
WHERE {oldPrimaryKey} NOT IN
(
SELECT MIN({oldPrimaryKey})
FROM {oldTableName}
{(!uniqueIndex.Where.HasText() ? "" : "WHERE " + uniqueIndex.Where.Replace(columnReplacement))}
GROUP BY {oldColumns}
){(!uniqueIndex.Where.HasText() ? "" : "AND " + uniqueIndex.Where.Replace(columnReplacement))}") !);
}
private static SqlPreCommand RemoveDuplicates(UniqueTableIndex uniqueIndex, IColumn primaryKey, string columns, bool commentedOut)
{
return(new SqlPreCommandSimple($@"DELETE {uniqueIndex.Table.Name}
WHERE {primaryKey.Name} NOT IN
(
SELECT MIN({primaryKey.Name})
FROM {uniqueIndex.Table.Name}
{(string.IsNullOrWhiteSpace(uniqueIndex.Where) ? "" : "WHERE " + uniqueIndex.Where)}
GROUP BY {columns}
){(string.IsNullOrWhiteSpace(uniqueIndex.Where) ? "" : " AND " + uniqueIndex.Where)}".Let(txt => commentedOut ? txt.Indent(2, '-') : txt)));
}
public static SqlPreCommand?RemoveDuplicatesIfNecessary(UniqueTableIndex uniqueIndex, Replacements rep)
{
try
{
var primaryKey = uniqueIndex.Table.Columns.Values.Where(a => a.PrimaryKey).Only();
if (primaryKey == null)
{
return(null);
}
int count = DuplicateCount(uniqueIndex, rep);
if (count == 0)
{
return(null);
}
var columns = uniqueIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (rep.Interactive)
{
if (SafeConsole.Ask($"There are {count} rows in {uniqueIndex.Table.Name} with the same {columns}. Generate DELETE duplicates script?"))
{
return(RemoveDuplicates(uniqueIndex, primaryKey, columns, commentedOut: false));
}
return(null);
}
else
{
return(RemoveDuplicates(uniqueIndex, primaryKey, columns, commentedOut: true));
}
}
catch (Exception)
{
return(new SqlPreCommandSimple($"-- Impossible to determine duplicates in new index {uniqueIndex.IndexName}"));
}
}
