/// <summary>
///
/// </summary>
/// <exception cref="SynchronizationFailedException">Could not figure out how to resolve a synchronization problem between the TableInfo and the underlying table structure</exception>
/// <param name="notifier">Called every time a fixable problem is detected, method must return true or false. True = apply fix, False = don't - but carry on checking</param>
public bool Synchronize(ICheckNotifier notifier)
{
bool IsSynched = true;
//server exists and is accessible?
try
{
_toSyncTo.TestConnection();
}
catch (Exception e)
{
throw new SynchronizationFailedException("Could not connect to " + _toSyncTo, e);
}
//database exists?
var expectedDatabase = _toSyncTo.ExpectDatabase(_tableToSync.GetDatabaseRuntimeName());
if (!expectedDatabase.Exists())
{
throw new SynchronizationFailedException("Server did not contain a database called " + _tableToSync.GetDatabaseRuntimeName());
}
//identify new columns
DiscoveredColumn[] liveColumns;
DiscoveredTable expectedTable;
if (_tableToSync.IsTableValuedFunction)
{
expectedTable = expectedDatabase.ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException("Database " + expectedDatabase + " did not contain a TABLE VALUED FUNCTION called " + _tableToSync.GetRuntimeName());
}
}
else
{
//table exists?
expectedTable = expectedDatabase.ExpectTable(_tableToSync.GetRuntimeName(), _tableToSync.Schema, _tableToSync.IsView ? TableType.View:TableType.Table);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException("Database " + expectedDatabase + " did not contain a table called " + _tableToSync.GetRuntimeName());
}
}
try
{
liveColumns = expectedTable.DiscoverColumns();
}
catch (SqlException e)
{
throw new Exception("Failed to enumerate columns in " +
_toSyncTo +
" (we were attempting to synchronize the TableInfo " + _tableToSync + " (ID=" + _tableToSync.ID + "). Check the inner exception for specifics", e);
}
ColumnInfo[] catalogueColumns = _tableToSync.ColumnInfos.ToArray();
IDataAccessCredentials credentialsIfExists = _tableToSync.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
string pwd = null;
string usr = null;
if (credentialsIfExists != null)
{
usr = credentialsIfExists.Username;
pwd = credentialsIfExists.GetDecryptedPassword();
}
ITableInfoImporter importer;
//for importing new stuff
if (_tableToSync.IsTableValuedFunction)
{
importer = new TableValuedFunctionImporter(_repository, (DiscoveredTableValuedFunction)expectedTable);
}
else
{
importer = new TableInfoImporter(_repository, _toSyncTo.Name, _toSyncTo.GetCurrentDatabase().GetRuntimeName(), _tableToSync.GetRuntimeName(), _tableToSync.DatabaseType, username: usr, password: pwd, importFromSchema: _tableToSync.Schema, importTableType: _tableToSync.IsView ? TableType.View:TableType.Table);
}
DiscoveredColumn[] newColumnsInLive =
liveColumns.Where(
live => !catalogueColumns.Any(columnInfo =>
columnInfo.GetRuntimeName()
.Equals(live.GetRuntimeName()))).ToArray();
//there are new columns in the live database that are not in the Catalogue
if (newColumnsInLive.Any())
{
//see if user wants to add missing columns
bool addMissingColumns = notifier.OnCheckPerformed(new CheckEventArgs("The following columns are missing from the TableInfo:" + string.Join(",", newColumnsInLive.Select(c => c.GetRuntimeName())), CheckResult.Fail, null, "The ColumnInfos will be created and added to the TableInfo"));
List <ColumnInfo> added = new List <ColumnInfo>();
if (addMissingColumns)
{
foreach (DiscoveredColumn missingColumn in newColumnsInLive)
{
added.Add(importer.CreateNewColumnInfo(_tableToSync, missingColumn));
}
ForwardEngineerExtractionInformationIfAppropriate(added, notifier);
}
else
{
IsSynched = false;
}
}
//See if we need to delete any ColumnInfos
ColumnInfo[] columnsInCatalogueButSinceDisapeared =
catalogueColumns
.Where(columnInfo => !liveColumns.Any( //there are not any
c => columnInfo.GetRuntimeName().Equals(c.GetRuntimeName())) //columns with the same name between discovery/columninfo
).ToArray();
if (columnsInCatalogueButSinceDisapeared.Any())
{
foreach (var columnInfo in columnsInCatalogueButSinceDisapeared)
{
bool deleteExtraColumnInfos = notifier.OnCheckPerformed(new CheckEventArgs("The ColumnInfo " + columnInfo.GetRuntimeName() + " no longer appears in the live table.", CheckResult.Fail, null, "Delete ColumnInfo " + columnInfo.GetRuntimeName()));
if (deleteExtraColumnInfos)
{
columnInfo.DeleteInDatabase();
}
else
{
IsSynched = false;
}
}
}
_tableToSync.ClearAllInjections();
if (IsSynched)
{
IsSynched = SynchronizeTypes(notifier, liveColumns);
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have primary keys!
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsPrimaryKey");
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have autonum
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsAutoIncrement");
}
if (IsSynched)
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "Collation");
}
if (IsSynched && _tableToSync.IsTableValuedFunction)
{
IsSynched = SynchronizeParameters((TableValuedFunctionImporter)importer, notifier);
}
_tableToSync.ClearAllInjections();
//get list of primary keys from underlying table
return(IsSynched);
}
private bool SynchronizeParameters(TableValuedFunctionImporter importer, ICheckNotifier notifier)
{
var discoveredParameters = _toSyncTo.GetCurrentDatabase().ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema).DiscoverParameters();
var currentParameters = _tableToSync.GetAllParameters();
//For each parameter in underlying database
foreach (DiscoveredParameter parameter in discoveredParameters)
{
ISqlParameter existingCatalogueReference = currentParameters.SingleOrDefault(p => p.ParameterName.Equals(parameter.ParameterName));
if (existingCatalogueReference == null)// that is not known about by the TableInfo
{
bool create = notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function but it does not have a record of the parameter " +
parameter.ParameterName + " which appears in the underlying database", CheckResult.Fail,
null, "Create the Parameter"));
if (!create)
{
return(false); //no longer synched
}
importer.CreateParameter(_tableToSync, parameter);
}
else
{
//it is known about by the Catalogue but has it mysteriously changed datatype since it was imported / last synced?
var dbDefinition = importer.GetParamaterDeclarationSQL(parameter);
//if there is a disagreement on type etc
if (existingCatalogueReference.ParameterSQL != dbDefinition)
{
bool modify =
notifier.OnCheckPerformed(
new CheckEventArgs(
"Parameter " + existingCatalogueReference + " is declared as '" + dbDefinition +
"' but in the Catalogue it appears as '" +
existingCatalogueReference.ParameterSQL + "'", CheckResult.Fail, null,
"Change the definition in the Catalogue to '" + dbDefinition + "'"));
if (!modify)
{
return(false);
}
existingCatalogueReference.ParameterSQL = dbDefinition;
existingCatalogueReference.SaveToDatabase();
}
}
}
//Find redundant parameters - parameters that the catalogue knows about but no longer appear in the table valued function signature in the database
foreach (ISqlParameter currentParameter in currentParameters)
{
if (!discoveredParameters.Any(p => p.ParameterName.Equals(currentParameter.ParameterName)))
{
bool delete =
notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function, in the Catalogue it has a parameter called " +
currentParameter.ParameterName +
" but this parameter no longer appears in the underlying database", CheckResult.Fail,
null, "Delete Parameter " + currentParameter.ParameterName));
if (!delete)
{
return(false);
}
((IDeleteable)currentParameter).DeleteInDatabase();
}
}
return(true);
}