private void ResolvePrimaryKeyConflicts(IDataLoadEventListener job)
{
using (var con = (SqlConnection)_dbInfo.Server.GetConnection())
{
con.Open();
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(TargetTable);
SqlCommand cmdAreTherePrimaryKeyCollisions = new SqlCommand(resolver.GenerateCollisionDetectionSQL(), con);
cmdAreTherePrimaryKeyCollisions.CommandTimeout = 5000;
//if there are no primary key collisions
if (cmdAreTherePrimaryKeyCollisions.ExecuteScalar().ToString().Equals("0"))
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "No primary key collisions detected"));
return;
}
//there are primary key collisions so resolve them
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Primary key collisions detected"));
SqlCommand cmdResolve = new SqlCommand(resolver.GenerateSQL(), con);
cmdResolve.CommandTimeout = 5000;
int affectedRows = cmdResolve.ExecuteNonQuery();
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Primary key collisions resolved by deleting " + affectedRows + " rows"));
con.Close();
}
}
public void GetNullSubstituteTests()
{
var ti = Mock.Of <ITableInfo>(t => t.GetQuerySyntaxHelper() == new MicrosoftQuerySyntaxHelper());
var pk = new PrimaryKeyCollisionResolver(ti);
Assert.AreEqual("-999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(3)", true));
Assert.AreEqual("-9999999999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(10)", true));
Assert.AreEqual("-99.9", pk.GetNullSubstituteForComparisonsWithDataType("decimal(3,1)", true));
Assert.AreEqual("-.9999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(4,4)", true));
Assert.AreEqual("999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(3)", false));
Assert.AreEqual("9999999999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(10)", false));
Assert.AreEqual("99.9", pk.GetNullSubstituteForComparisonsWithDataType("decimal(3,1)", false));
Assert.AreEqual(".9999", pk.GetNullSubstituteForComparisonsWithDataType("decimal(4,4)", false));
}
public void Check(ICheckNotifier notifier)
{
if (TargetTable == null)
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Target table is null, a table must be specified upon which to resolve primary key duplication (that TableInfo must have a primary key collision resolution order)",
CheckResult.Fail, null));
}
try
{
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(TargetTable);
string sql = resolver.GenerateSQL();
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Failed to check PrimaryKeyCollisionResolver on " + TargetTable, CheckResult.Fail, e));
}
}
public void NoPrimaryKeys_ThrowsException()
{
TableInfo t;
ColumnInfo c1;
ColumnInfo c2;
ColumnInfo c3;
SetupTableInfos(out t, out c1, out c2, out c3);
try
{
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(t);
var ex = Assert.Throws <Exception>(() => Console.WriteLine(resolver.GenerateSQL()));
StringAssert.Contains("does not have any primary keys defined so cannot resolve primary key collisions", ex.Message);
}
finally
{
t.DeleteInDatabase();
}
}
public void GenerateSQL_OrderCorrect()
{
TableInfo t;
ColumnInfo c1;
ColumnInfo c2;
ColumnInfo c3;
SetupTableInfos(out t, out c1, out c2, out c3);
try
{
c1.IsPrimaryKey = true;
c1.SaveToDatabase();
c2.DuplicateRecordResolutionOrder = 1;
c2.DuplicateRecordResolutionIsAscending = true;
c2.SaveToDatabase();
c3.DuplicateRecordResolutionOrder = 2;
c3.DuplicateRecordResolutionIsAscending = false;
c3.SaveToDatabase();
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(t);
string sql = resolver.GenerateSQL();
Console.WriteLine(sql);
Assert.IsTrue(sql.Contains(c2.Name));
Assert.IsTrue(sql.Contains(c3.Name));
//column 2 has the following null substitute, is Ascending order and is the first of two
Assert.IsTrue(sql.Contains("ISNULL([col2],-9223372036854775808) ASC,"));
//column 3 has the following null substitute and is descending and is not followed by another column
Assert.IsTrue(sql.Contains("ISNULL([col3],-2147483648) DESC"));
}
finally
{
t.DeleteInDatabase();
}
}
private void btnSelectToClipboard_Click(object sender, EventArgs e)
{
try
{
//this is used only to generate the SQL preview of how to resolve primary key collisions so no username/password is required - hence the null,null
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(_table);
if (sender == btnCopyPreview)
{
System.Windows.Forms.Clipboard.SetText(resolver.GeneratePreviewSQL());
}
if (sender == btnCopyDetection)
{
System.Windows.Forms.Clipboard.SetText(resolver.GenerateCollisionDetectionSQL());
}
}
catch (Exception exception)
{
ExceptionViewer.Show(exception);
}
}
public void NoColumnOrdersConfigured_ThrowsException()
{
TableInfo t;
ColumnInfo c1;
ColumnInfo c2;
ColumnInfo c3;
SetupTableInfos(out t, out c1, out c2, out c3);
try
{
c1.IsPrimaryKey = true;
c1.SaveToDatabase();
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(t);
var ex = Assert.Throws <Exception>(() => Console.WriteLine(resolver.GenerateSQL()));
StringAssert.Contains("The ColumnInfos of TableInfo PrimaryKeyCollisionResolverTests do not have primary key resolution orders configured (do not know which order to use non primary key column values in to resolve collisions). Fix this by right clicking a TableInfo in CatalogueManager and selecting 'Configure Primary Key Collision Resolution'.", ex.Message);
}
finally
{
t.DeleteInDatabase();
}
}
private void RefreshUIFromDatabase()
{
lbPrimaryKeys.Items.Clear();
lbConflictResolutionColumns.Items.Clear();
foreach (var pkCol in _table.ColumnInfos.Where(col => col.IsPrimaryKey))
{
//primary keys are not used to resolve duplication of primary key values (obviously!)
if (pkCol.DuplicateRecordResolutionOrder != null)
{
//unset any that have accidentally gained an order e.g. if user set an order then made a new column a PK
pkCol.DuplicateRecordResolutionOrder = null;
pkCol.SaveToDatabase();
}
lbPrimaryKeys.Items.Add(pkCol);
}
List <IResolveDuplication> resolvers = new List <IResolveDuplication>();
resolvers.AddRange(_table.ColumnInfos.Where(col => !col.IsPrimaryKey));
resolvers.AddRange(_table.PreLoadDiscardedColumns);
//if there is no order yet
if (resolvers.All(r => r.DuplicateRecordResolutionOrder == null))
{
for (int i = 0; i < resolvers.Count; i++)
{
//set one up
resolvers[i].DuplicateRecordResolutionOrder = i;
resolvers[i].SaveToDatabase();
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(o => o.DuplicateRecordResolutionOrder).ToArray())
{
//if it starts with hic_
if (SpecialFieldNames.IsHicPrefixed(resolver))
{
//do not use it for duplication resolution
resolver.DuplicateRecordResolutionOrder = null;
resolver.DuplicateRecordResolutionIsAscending = false; //default to descending
resolver.SaveToDatabase();
resolvers.Remove(resolver);
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(c => c.DuplicateRecordResolutionOrder))
{
lbConflictResolutionColumns.Items.Add(resolver);
}
QueryEditor.ReadOnly = false;
try
{
//this is used only to generate the SQL preview of how to resolve primary key collisions so no username/password is required - hence the null,null
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(_table);
QueryEditor.Text = resolver.GenerateSQL();
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception e)
{
CommonFunctionality.ScintillaGoRed(QueryEditor, e);
}
QueryEditor.ReadOnly = true;
}
