private void TableInfo_Click(TableInfo tableInfo)
{
TableInfoSynchronizer syncher = new TableInfoSynchronizer(tableInfo);
try
{
bool wasSynchedsuccessfully = syncher.Synchronize(new MakeChangePopup(new YesNoYesToAllDialog()));
if (wasSynchedsuccessfully)
{
MessageBox.Show("Synchronization complete, TableInfo is Synchronized with the live database");
}
else
{
MessageBox.Show("Synchronization failed");
}
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
Publish(tableInfo);
foreach (var c in syncher.ChangedCatalogues)
{
Publish(c);
}
}
public void Synchronization_ParameterDefinitionChanged()
{
string expectedMessage =
"Parameter @startNumber is declared as 'DECLARE @startNumber AS int;' but in the Catalogue it appears as 'DECLARE @startNumber AS datetime;'";
AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber"));
parameter.ParameterSQL = "DECLARE @startNumber AS datetime;";
parameter.SaveToDatabase();
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
var ex = Assert.Throws <Exception>(() => syncer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.IsTrue(ex.Message.Contains(expectedMessage));
//no changes should yet have taken place since we didn't accept it yet
Assert.IsTrue(parameter.HasLocalChanges().Evaluation == ChangeDescription.NoChanges);
//sync should have proposed to adjusting the datatype
Assert.IsTrue(syncer.Synchronize(new AcceptAllCheckNotifier()));
//now parameter should have the correct datatype
Assert.IsTrue(parameter.HasLocalChanges().Evaluation == ChangeDescription.DatabaseCopyDifferent);
var diff = parameter.HasLocalChanges().Differences.Single();
Assert.AreEqual("DECLARE @startNumber AS datetime;", diff.LocalValue);
Assert.AreEqual("DECLARE @startNumber AS int;", diff.DatabaseValue);
}
private void CheckTableInfoSynchronization(TableInfo tableInfo, ICheckNotifier notifier)
{
//live is the current data load's (possilby overridden server/database)
var tableInfoSynchronizer = new TableInfoSynchronizer(tableInfo);
string problemList = "";
//synchronize but refuse to apply all fixes, problems are instead added to problemList
if (tableInfoSynchronizer.Synchronize(notifier))
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + tableInfo + " passed Synchronization check", CheckResult.Success, null)); //passed synchronization
}
else
{
bool launchSyncFixer = notifier.OnCheckPerformed(new CheckEventArgs(
"TableInfo " + tableInfo + " failed Synchronization check with following problems:" + problemList, CheckResult.Fail,
null, "Launch Synchronization Fixing")); //failed syncronization
if (launchSyncFixer)
{
bool userFixed;
//if silent running accept all changes
userFixed = tableInfoSynchronizer.Synchronize(notifier);
if (!userFixed)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + tableInfo + " still failed Synchronization check", CheckResult.Fail, null)); //passed synchronization
}
}
}
}
public void Synchronization_ExtraParameter()
{
string expectedMessage =
"MyAwesomeFunction is a Table Valued Function, in the Catalogue it has a parameter called @fish but this parameter no longer appears in the underlying database";
var excessParameter = new AnyTableSqlParameter(CatalogueRepository, _function.TableInfoCreated, "DECLARE @fish as int");
var checker = new ToMemoryCheckNotifier();
_function.TableInfoCreated.Check(checker);
Assert.IsTrue(checker.Messages.Any(m => m.Result == CheckResult.Fail
&&
m.Message.Contains(expectedMessage)));
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
var ex = Assert.Throws <Exception>(() => syncer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.IsTrue(ex.Message.Contains(expectedMessage));
//no changes yet
Assert.IsTrue(excessParameter.HasLocalChanges().Evaluation == ChangeDescription.NoChanges);
//sync should have proposed to drop the excess parameter (see above), accept the change
Assert.IsTrue(syncer.Synchronize(new AcceptAllCheckNotifier()));
//now parameter shouldnt be there
Assert.IsTrue(excessParameter.HasLocalChanges().Evaluation == ChangeDescription.DatabaseCopyWasDeleted);
}
public void TestView(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
var syntax = db.Server.GetQuerySyntaxHelper();
DataTable dt = new DataTable();
dt.Columns.Add("FF");
var tbl = db.CreateTable("MyTable", dt);
Import(tbl, out TableInfo tblInfo, out _);
Assert.IsTrue(tblInfo.Discover(DataAccessContext.InternalDataProcessing).Exists());
Assert.AreEqual(TableType.Table, tblInfo.Discover(DataAccessContext.InternalDataProcessing).TableType);
var viewName = "MyView";
//oracle likes to create stuff under your user account not the database your actually using!
if (dbType == DatabaseType.Oracle)
{
viewName = syntax.EnsureFullyQualified(tbl.Database.GetRuntimeName(), null, "MyView");
}
//postgres hates upper case tables (unless they are wrapped)
if (dbType == DatabaseType.PostgreSql)
{
viewName = syntax.EnsureWrapped(viewName);
}
var sql = string.Format(@"CREATE VIEW {0} AS
SELECT {2}
FROM {1}",
viewName,
tbl.GetFullyQualifiedName(),
syntax.EnsureWrapped("FF"));
using (var con = tbl.Database.Server.GetConnection())
{
con.Open();
var cmd = tbl.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
var view = tbl.Database.ExpectTable("MyView", null, TableType.View);
Import(view, out TableInfo viewInfo, out _);
var sync = new TableInfoSynchronizer(viewInfo);
sync.Synchronize(new ThrowImmediatelyCheckNotifier());
Assert.IsTrue(viewInfo.Discover(DataAccessContext.InternalDataProcessing).Exists());
Assert.AreEqual(TableType.View, viewInfo.Discover(DataAccessContext.InternalDataProcessing).TableType);
view.Drop();
Assert.IsFalse(view.Exists());
}
public void SynchronizationTests_NoChanges()
{
Assert.AreEqual(TABLE_NAME, tableInfoCreated.GetRuntimeName());
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
Assert.AreEqual(true, synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
}
public override void Execute()
{
base.Execute();
TableInfoSynchronizer syncher = new TableInfoSynchronizer(_tableInfo);
var listener = _autoYes ? new AcceptAllCheckNotifier() :(ICheckNotifier) new FromActivateItemsToCheckNotifier(BasicActivator);
try
{
bool wasSynchedsuccessfully = syncher.Synchronize(listener);
if (wasSynchedsuccessfully)
{
BasicActivator.Show("Synchronization complete, TableInfo is Synchronized with the live database");
}
else
{
BasicActivator.Show("Synchronization failed");
}
}
catch (Exception exception)
{
BasicActivator.ShowException("Failed to sync", exception);
}
if (_alsoSyncAno)
{
var ANOSynchronizer = new ANOTableInfoSynchronizer(_tableInfo);
try
{
ANOSynchronizer.Synchronize(listener);
BasicActivator.Show("ANO synchronization successful");
}
catch (ANOConfigurationException e)
{
BasicActivator.ShowException("Anonymisation configuration error", e);
}
catch (Exception exception)
{
BasicActivator.ShowException("Fatal error while attempting to synchronize (" + exception.Message + ")", exception);
}
}
Publish(_tableInfo);
foreach (var c in syncher.ChangedCatalogues)
{
Publish(c);
}
}
private void btnSynchronize_Click(object sender, EventArgs e)
{
try
{
bool isSync = new TableInfoSynchronizer(_tableInfo).Synchronize(new MakeChangePopup(new YesNoYesToAllDialog()));
if (isSync)
{
MessageBox.Show("TableInfo is synchronized");
}
}
catch (Exception exception)
{
ExceptionViewer.Show(exception);
}
}
public void SynchronizationTests_ColumnAddedWithCatalogue(bool acceptChanges)
{
ForwardEngineerCatalogue cataEngineer = new ForwardEngineerCatalogue(tableInfoCreated, columnInfosCreated, true);
Catalogue cata;
CatalogueItem[] cataItems;
ExtractionInformation[] extractionInformations;
cataEngineer.ExecuteForwardEngineering(out cata, out cataItems, out extractionInformations);
try
{
Assert.AreEqual(TABLE_NAME, cata.Name);
Assert.AreEqual(2, cataItems.Length);
Assert.AreEqual(2, extractionInformations.Length);
using (var con = _server.GetConnection())
{
con.Open();
_server.GetCommand("ALTER TABLE " + TABLE_NAME + " ADD Birthday datetime not null", con).ExecuteNonQuery();
}
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
if (acceptChanges)
{
//accept changes should result in a synchronized table
Assert.AreEqual(true, synchronizer.Synchronize(new AcceptAllCheckNotifier()));
Assert.AreEqual(3, tableInfoCreated.ColumnInfos.Length); //should 3 now
Assert.AreEqual(3, cata.CatalogueItems.Length); //should 3 now
Assert.AreEqual(3, cata.GetAllExtractionInformation(ExtractionCategory.Any).Length); //should 3 now
Assert.AreEqual(1, cata.GetAllExtractionInformation(ExtractionCategory.Any).Count(e => e.SelectSQL.Contains("Birthday")));
Assert.AreEqual(1, cata.CatalogueItems.Count(ci => ci.Name.Contains("Birthday")));
}
else
{
var ex = Assert.Throws <Exception>(() => synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.AreEqual("The following columns are missing from the TableInfo:Birthday", ex.Message);
}
}
finally
{
cata.DeleteInDatabase();
}
}
private void SynchronizeANOConfiguration_Click(TableInfo tableInfo)
{
//let use check the TableInfo accurately reflects the underlying database first
if (_activator.YesNo("Check that TableInfo is synchronized with underlying database first?", "Check database first?"))
{
try
{
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfo);
bool isSynchronized = synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier());
if (!isSynchronized)
{
MessageBox.Show("Unable to synchronize with ANO database because TableInfo is not synchronized with underlying database.");
return;
}
}
catch (Exception exception)
{
MessageBox.Show(
"Unable to check synchronization of TableInfo with underlying database (this check must be performed before checking ANO synchronization):" +
exception.Message);
return;
}
}
var ANOSynchronizer = new ANOTableInfoSynchronizer(tableInfo);
try
{
ANOSynchronizer.Synchronize(new MakeChangePopup(new YesNoYesToAllDialog()));
MessageBox.Show("ANO synchronization successful");
}
catch (ANOConfigurationException e)
{
ExceptionViewer.Show(e);
}
catch (Exception exception)
{
ExceptionViewer.Show("Fatal error while attempting to synchronize (" + exception.Message + ")", exception);
}
Publish(tableInfo);
}
/// <summary>
/// Checks that the table referenced exists on the database server and that it's properties and <see cref="ColumnInfo"/> etc are synchronized with the live
/// table as it exists on the server.
/// </summary>
/// <param name="notifier"></param>
public void Check(ICheckNotifier notifier)
{
if (IsLookupTable())
{
if (IsPrimaryExtractionTable)
{
notifier.OnCheckPerformed(new CheckEventArgs("Table is both a Lookup table AND is marked IsPrimaryExtractionTable", CheckResult.Fail));
}
}
try
{
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(this);
synchronizer.Synchronize(notifier);
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Synchronization failed on TableInfo " + this, CheckResult.Fail, e));
}
}
public void Synchronization_ParameterRenamed()
{
AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber"));
parameter.ParameterSQL = "DECLARE @startNum AS int";
parameter.SaveToDatabase();
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
//shouldn't be any
Assert.IsFalse(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber")));
syncer.Synchronize(new AcceptAllCheckNotifier());
var after = _function.TableInfoCreated.GetAllParameters();
//now there should be recreated (actually it will suggest deleting the excess one and creating the underlying one as 2 separate suggestions one after the other)
Assert.IsTrue(after.Any(p => p.ParameterName.Equals("@startNumber")));
//still there should only be 3 parameters
Assert.AreEqual(3, after.Length);
}
public void SynchronizationTests_ColumnAdded(bool acceptChanges)
{
using (var con = _database.Server.GetConnection())
{
con.Open();
_server.GetCommand("ALTER TABLE " + TABLE_NAME + " ADD Birthday datetime not null", con).ExecuteNonQuery();
}
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
if (acceptChanges)
{
//accept changes should result in a synchronized table
Assert.AreEqual(true, synchronizer.Synchronize(new AcceptAllCheckNotifier()));
Assert.AreEqual(3, tableInfoCreated.ColumnInfos.Length);//should 3 now
}
else
{
var ex = Assert.Throws <Exception>(() => synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.AreEqual("The following columns are missing from the TableInfo:Birthday", ex.Message);
}
}
public void SynchronizationTests_ColumnDropped(bool acceptChanges)
{
Assert.AreEqual(TABLE_NAME, tableInfoCreated.GetRuntimeName());
var table = _database.ExpectTable(TABLE_NAME);
var colToDrop = table.DiscoverColumn("Address");
table.DropColumn(colToDrop);
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
if (acceptChanges)
{
//accept changes should result in a synchronized table
Assert.AreEqual(true, synchronizer.Synchronize(new AcceptAllCheckNotifier()));
Assert.AreEqual(1, tableInfoCreated.ColumnInfos.Length);//should only be 1 remaining
}
else
{
var ex = Assert.Throws <Exception>(() => synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.AreEqual("The ColumnInfo Address no longer appears in the live table.", ex.Message);
}
}
public void Synchronization_MissingParameter()
{
string expectedMessage = "MyAwesomeFunction is a Table Valued Function but it does not have a record of the parameter @startNumber which appears in the underlying database";
AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber"));
parameter.DeleteInDatabase();
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
var ex = Assert.Throws <Exception>(() => syncer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.IsTrue(ex.Message.Contains(expectedMessage));
//no parameter called @startNumber (because we deleted it right!)
Assert.IsFalse(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber")));
//sync should have proposed to create the missing parameter (see above), accept the change
Assert.IsTrue(syncer.Synchronize(new AcceptAllCheckNotifier()));
//now parameter should have reappeared due to accepthing change
Assert.IsTrue(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber")));
}
private void AddNewANOColumnInfo(Func <string, bool> shouldApplySql, DbConnection con, ICheckNotifier notifier)
{
string anoColumnNameWillBe = "ANO" + _colToNuke.GetRuntimeName(LoadStage.PostLoad);
string alterSql = "ALTER TABLE " + _tableInfo.Name + " ADD " + anoColumnNameWillBe + " " + _toConformTo.GetRuntimeDataType(LoadStage.PostLoad);
if (shouldApplySql(alterSql))
{
var cmd = DatabaseCommandHelper.GetCommand(alterSql, con);
cmd.ExecuteNonQuery();
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(_tableInfo);
synchronizer.Synchronize(notifier);
//now get the new ANO columninfo
_newANOColumnInfo = _tableInfo.ColumnInfos.Single(c => c.GetRuntimeName().Equals(anoColumnNameWillBe));
_newANOColumnInfo.ANOTable_ID = _toConformTo.ID;
_newANOColumnInfo.SaveToDatabase();
}
else
{
throw new Exception("User chose not to apply part of the operation");
}
}
public void Test_SynchronizeTable_BracketsInTableName()
{
var db = _database;
//FAnsi doesn't let you create tables with brackets in the names so we have to do it manually
using (var con = db.Server.GetConnection())
{
con.Open();
var cmd = db.Server.GetCommand("CREATE TABLE [BB (ff)] (A int not null)", con);
cmd.ExecuteNonQuery();
}
var tbl = db.CreateTable("FF",
new DatabaseColumnRequest[]
{
new DatabaseColumnRequest("F", new DatabaseTypeRequest(typeof(int)))
});
Import(tbl, out TableInfo ti, out _);
var s = new TableInfoSynchronizer(ti);
s.Synchronize(new ThrowImmediatelyCheckNotifier());
}
protected bool Synchronize()
{
var sync = new TableInfoSynchronizer(TableInfo);
return(sync.Synchronize(new AcceptAllCheckNotifier()));
}
public void TestCreateTableInSchemaAndImportAsTableInfo()
{
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using (var con = db.Server.GetConnection())
{
con.Open();
db.Server.GetCommand("CREATE SCHEMA Omg", con).ExecuteNonQuery();
var tbl = db.CreateTable("Fish", new [] { new DatabaseColumnRequest("MyCol", "int")
{
IsPrimaryKey = true
} }, schema: "Omg");
Assert.AreEqual("Fish", tbl.GetRuntimeName());
Assert.AreEqual("Omg", tbl.Schema);
Assert.IsTrue(tbl.GetFullyQualifiedName().EndsWith("[Omg].[Fish]"));
Assert.IsTrue(tbl.Exists());
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
Assert.AreEqual("Omg", ti.Schema);
var tbl2 = ti.Discover(DataAccessContext.InternalDataProcessing);
Assert.AreEqual("Omg", tbl2.Schema);
Assert.IsTrue(tbl2.Exists());
Assert.IsTrue(ti.Name.EndsWith("[Omg].[Fish]"));
Assert.IsTrue(ti.GetFullyQualifiedName().EndsWith("[Omg].[Fish]"));
var c = cols.Single();
Assert.AreEqual("MyCol", c.GetRuntimeName());
StringAssert.Contains("[Omg].[Fish]", c.GetFullyQualifiedName());
//should be primary key
Assert.IsTrue(c.IsPrimaryKey);
var triggerFactory = new TriggerImplementerFactory(DatabaseType.MicrosoftSQLServer);
var impl = triggerFactory.Create(tbl);
Assert.AreEqual(TriggerStatus.Missing, impl.GetTriggerStatus());
impl.CreateTrigger(new ThrowImmediatelyCheckNotifier());
Assert.AreEqual(TriggerStatus.Enabled, impl.GetTriggerStatus());
Assert.IsTrue(impl.CheckUpdateTriggerIsEnabledAndHasExpectedBody());
//should be synced
var sync = new TableInfoSynchronizer(ti);
sync.Synchronize(new AcceptAllCheckNotifier());
//Test importing the _Legacy table valued function that should be created in the Omg schema and test synching that too.
var tvf = ti.Discover(DataAccessContext.InternalDataProcessing).Database.ExpectTableValuedFunction("Fish_Legacy", "Omg");
Assert.IsTrue(tvf.Exists());
var importerTvf = new TableValuedFunctionImporter(CatalogueRepository, tvf);
TableInfo tvfTi;
ColumnInfo[] tvfCols;
importerTvf.DoImport(out tvfTi, out tvfCols);
Assert.AreEqual("Omg", tvfTi.Schema);
var syncTvf = new TableInfoSynchronizer(tvfTi);
syncTvf.Synchronize(new ThrowImmediatelyCheckNotifier());
StringAssert.EndsWith("[Omg].Fish_Legacy(@index) AS Fish_Legacy", tvfTi.Name);
}
}
