public override int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd, IManagedTransaction transaction = null)
{
var autoIncrement = discoveredTable.DiscoverColumns(transaction).SingleOrDefault(c => c.IsAutoIncrement);
if (autoIncrement == null)
{
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
var p = discoveredTable.Database.Server.Helper.GetParameter("identityOut");
p.Direction = ParameterDirection.Output;
p.DbType = DbType.Int32;
cmd.Parameters.Add(p);
cmd.CommandText += " RETURNING " + autoIncrement + " INTO :identityOut;";
cmd.CommandText = "BEGIN " + Environment.NewLine + cmd.CommandText + Environment.NewLine + "COMMIT;" + Environment.NewLine + "END;";
cmd.ExecuteNonQuery();
return(Convert.ToInt32(p.Value));
}
public override void MakeDistinct(DiscoveredTable discoveredTable, int timeoutInSeconds)
{
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 => c.GetRuntimeName()));
string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName());
var server = discoveredTable.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand(sqlToExecute, con);
cmd.CommandTimeout = timeoutInSeconds;
cmd.ExecuteNonQuery();
}
}
public override int Run()
{
DiscoveredTable tbl = GetServer(_opts.DatabaseConnectionString, _opts.DatabaseType, _opts.TableName);
var server = tbl.Database.Server;
_factory = new DatabaseFailureFactory(tbl);
_columns = tbl.DiscoverColumns();
_columnsNames = _columns.Select(c => c.GetRuntimeName()).ToArray();
_stringColumns = _columns.Select(c => c.GetGuesser().Guess.CSharpType == typeof(string)).ToArray();
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand(
string.Format("SELECT {0} FROM {1}"
, string.Join("," + Environment.NewLine, _columns.Select(c => c.GetFullyQualifiedName()).ToArray())
, tbl.GetFullyQualifiedName()), con);
_logger.Info("About to send command:" + Environment.NewLine + cmd.CommandText);
var reader = cmd.ExecuteReader();
foreach (Failure failure in reader.Cast <DbDataRecord>().SelectMany(GetFailuresIfAny))
{
AddToReports(failure);
}
CloseReports();
}
return(0);
}
private void CheckColumnDefinitionsMatchArchive()
{
List <string> errors = new List <string>();
var archiveTableCols = _archiveTable.DiscoverColumns().ToArray();
foreach (DiscoveredColumn col in _columns)
{
var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName()));
if (colInArchive == null)
{
errors.Add("Column " + col.GetRuntimeName() + " appears in Table '" + _table + "' but not in archive table '" + _archiveTable + "'");
}
else
if (!AreCompatibleDatatypes(col.DataType, colInArchive.DataType))
{
errors.Add("Column " + col.GetRuntimeName() + " has data type '" + col.DataType + "' in '" + _table + "' but in Archive table '" + _archiveTable + "' it is defined as '" + colInArchive.DataType + "'");
}
}
if (errors.Any())
{
throw new IrreconcilableColumnDifferencesInArchiveException("The following column mismatch errors were seen:" + Environment.NewLine + string.Join(Environment.NewLine, errors));
}
}
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 void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dillutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
var cmd = destDatabaseInfo.Server.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dillution = dillutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dillution != null)
{
column.DataType.AlterTypeTo(dillution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table)
{
var server = table.Database.Server;
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to run Coalese on table " + table));
var allCols = table.DiscoverColumns();
var pkColumnInfos = tableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).Select(c => c.GetRuntimeName()).ToArray();
var nonPks = allCols.Where(c => !pkColumnInfos.Contains(c.GetRuntimeName())).ToArray();
var pks = allCols.Except(nonPks).ToArray();
if (!pkColumnInfos.Any())
{
throw new Exception("Table '" + tableInfo + "' has no IsPrimaryKey columns");
}
if (allCols.Length == pkColumnInfos.Length)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping Coalesce on table " + table + " because it has no non primary key columns"));
return;
}
//Get an update command for each non primary key column
Dictionary <string, Task <int> > sqlCommands = new Dictionary <string, Task <int> >();
foreach (DiscoveredColumn nonPk in nonPks)
{
sqlCommands.Add(GetCommand(table, pks, nonPk), null);
}
server.EnableAsync();
using (var con = table.Database.Server.GetConnection())
{
con.Open();
if (CreateIndex)
{
var idxCmd = server.GetCommand(string.Format(@"CREATE INDEX IX_PK_{0} ON {0}({1});", table.GetRuntimeName(), string.Join(",", pks.Select(p => p.GetRuntimeName()))), con);
idxCmd.CommandTimeout = Timeout;
idxCmd.ExecuteNonQuery();
}
foreach (var sql in sqlCommands.Keys.ToArray())
{
var cmd = server.GetCommand(sql, con);
cmd.CommandTimeout = Timeout;
sqlCommands[sql] = cmd.ExecuteNonQueryAsync();
}
Task.WaitAll(sqlCommands.Values.ToArray());
}
int affectedRows = sqlCommands.Values.Sum(t => t.Result);
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Coalesce on table '" + table + "' completed (" + affectedRows + " rows affected)"));
}
public TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true)
{
_server = table.Database.Server;
_table = table;
_archiveTable = _table.Database.ExpectTable(table.GetRuntimeName() + "_Archive", table.Schema);
_columns = table.DiscoverColumns();
_primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray();
_createDataLoadRunIdAlso = createDataLoadRunIDAlso;
}
/// <summary>
/// Update the database <paramref name="server"/> to redact the <paramref name="failure"/>.
/// </summary>
/// <param name="server">Where to connect to get the data, can be null if <see cref="RulesOnly"/> is true</param>
/// <param name="failure">The failure to redact/create a rule for</param>
/// <param name="usingRule">Pass null to create a new rule or give value to reuse an existing rule</param>
public void Update(DiscoveredServer server, Failure failure, IsIdentifiableRule usingRule)
{
//theres no rule yet so create one (and add to RedList.yaml)
if (usingRule == null)
{
usingRule = Add(failure, RuleAction.Report);
}
//if we are running in rules only mode we don't need to also update the database
if (RulesOnly)
{
return;
}
var syntax = server.GetQuerySyntaxHelper();
//the fully specified name e.g. [mydb]..[mytbl]
string tableName = failure.Resource;
var tokens = tableName.Split('.', StringSplitOptions.RemoveEmptyEntries);
var db = tokens.First();
tableName = tokens.Last();
if (string.IsNullOrWhiteSpace(db) || string.IsNullOrWhiteSpace(tableName) || string.Equals(db, tableName))
{
throw new NotSupportedException($"Could not understand table name {failure.Resource}, maybe it is not full specified with a valid database and table name?");
}
db = syntax.GetRuntimeName(db);
tableName = syntax.GetRuntimeName(tableName);
DiscoveredTable table = server.ExpectDatabase(db).ExpectTable(tableName);
//if we've never seen this table before
if (!_primaryKeys.ContainsKey(table))
{
var pk = table.DiscoverColumns().SingleOrDefault(k => k.IsPrimaryKey);
_primaryKeys.Add(table, pk);
}
using (var con = server.GetConnection())
{
con.Open();
foreach (var sql in UpdateStrategy.GetUpdateSql(table, _primaryKeys, failure, usingRule))
{
var cmd = server.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
}
}
public override void FillDataTableWithTopX(DiscoveredTable table, int topX, DataTable dt, DbConnection connection, DbTransaction transaction = null)
{
((OracleConnection)connection).PurgeStatementCache();
var cols = table.DiscoverColumns();
string sql = "SELECT " + string.Join(",", cols.Select(c => c.GetFullyQualifiedName()).ToArray()) + " FROM " + table.GetFullyQualifiedName() + " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY";
var da = table.Database.Server.GetDataAdapter(sql, connection);
da.Fill(dt);
}
public override void FillDataTableWithTopX(DatabaseOperationArgs args, DiscoveredTable table, int topX, DataTable dt)
{
using (var con = args.GetManagedConnection(table))
{
((OracleConnection)con.Connection).PurgeStatementCache();
var cols = table.DiscoverColumns();
//apparently * doesn't fly with Oracle DataAdapter
string sql = "SELECT " + string.Join(",", cols.Select(c => c.GetFullyQualifiedName()).ToArray()) + " FROM " + table.GetFullyQualifiedName() + " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY";
using (var cmd = table.Database.Server.GetCommand(sql, con))
using (var da = table.Database.Server.GetDataAdapter(cmd))
args.Fill(da, cmd, dt);
}
}
private void CheckCohortDatabaseHasCorrectTables(ICheckNotifier notifier)
{
try
{
var database = DataAccessPortal.GetInstance().ExpectDatabase(this, DataAccessContext.DataExport);
DiscoveredTable cohortTable = database.ExpectTable(TableName);
if (cohortTable.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Found table " + cohortTable + " in database " + Database, CheckResult.Success, null));
var columns = cohortTable.DiscoverColumns();
ComplainIfColumnMissing(TableName, columns, PrivateIdentifierField, notifier);
ComplainIfColumnMissing(TableName, columns, ReleaseIdentifierField, notifier);
ComplainIfColumnMissing(TableName, columns, DefinitionTableForeignKeyField, notifier);
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + TableName + " in database " + Database, CheckResult.Fail, null));
}
DiscoveredTable foundCohortDefinitionTable = database.ExpectTable(DefinitionTableName);
if (foundCohortDefinitionTable.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Found table " + DefinitionTableName + " in database " + Database, CheckResult.Success, null));
var cols = foundCohortDefinitionTable.DiscoverColumns();
foreach (string requiredField in ExternalCohortTable.CohortDefinitionTable_RequiredFields)
{
ComplainIfColumnMissing(DefinitionTableName, cols, requiredField, notifier);
}
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + DefinitionTableName + " in database " + Database, CheckResult.Fail, null));
}
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not check table intactness for ExternalCohortTable '" + Name + "'", CheckResult.Fail, e));
}
}
public MigrationColumnSet(DiscoveredTable from, DiscoveredTable to, IMigrationFieldProcessor migrationFieldProcessor)
{
var fromCols = from.DiscoverColumns();
var toCols = to.DiscoverColumns();
migrationFieldProcessor.ValidateFields(fromCols, toCols);
SourceTable = from;
DestinationTable = to;
PrimaryKeys = fromCols.Where(c => c.IsPrimaryKey).ToArray();
FieldsToDiff = new List <DiscoveredColumn>();
FieldsToUpdate = new List <DiscoveredColumn>();
foreach (DiscoveredColumn pk in PrimaryKeys)
{
if (!toCols.Any(f => f.GetRuntimeName().Equals(pk.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase)))
{
throw new MissingFieldException("Column " + pk + " is missing from either the destination table");
}
}
if (!PrimaryKeys.Any())
{
throw new Exception("There are no primary keys declared in table " + from);
}
//figure out things to migrate and whether they matter to diffing
foreach (DiscoveredColumn field in fromCols)
{
if (
field.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase) ||
field.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
if (!toCols.Any(c => c.GetRuntimeName().Equals(field.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase)))
{
throw new MissingFieldException("Field " + field + " is missing from destination table");
}
migrationFieldProcessor.AssignFieldsForProcessing(field, FieldsToDiff, FieldsToUpdate);
}
}
protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table)
{
foreach (var col in table.DiscoverColumns())
{
if (AllDataTypes || col.DataType.GetLengthIfString() >= 1)
{
try
{
col.DataType.AlterTypeTo(DestinationType);
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "converted column " + col + " to " + DestinationType));
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to convert column " + col + " of data type " + col.DataType + " to destination type " + DestinationType, e));
}
}
}
}
public override int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd,
IManagedTransaction transaction = null)
{
var autoIncrement = discoveredTable.DiscoverColumns(transaction).SingleOrDefault(c => c.IsAutoIncrement);
if (autoIncrement != null)
{
cmd.CommandText += $" RETURNING {autoIncrement.GetFullyQualifiedName()};";
}
var result = cmd.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
return(0);
}
return(Convert.ToInt32(result));
}
private void ValidateIsolationTableSchema(DiscoveredTable toValidate, TableInfo tableInfo, ICheckNotifier notifier)
{
var expected = tableInfo.GetColumnsAtStage(LoadStage.AdjustRaw).Select(c => c.GetRuntimeName(LoadStage.AdjustRaw)).Union(new [] { SpecialFieldNames.DataLoadRunID }).ToArray();
var found = toValidate.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
foreach (var missingFromIsolation in expected.Except(found, StringComparer.CurrentCultureIgnoreCase))
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Isolation table '" + toValidate + "' did not contain expected column'" + missingFromIsolation +
"'", CheckResult.Fail));
}
foreach (var unexpectedInIsolation in found.Except(expected, StringComparer.CurrentCultureIgnoreCase))
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Isolation table '" + toValidate + "' contained an unexpected column'" + unexpectedInIsolation +
"'", CheckResult.Fail));
}
}
private void ConfirmFitToDestination(DataTable dt, DiscoveredTable tableToLoad, IDataLoadJob job)
{
var columnsAtDestination = tableToLoad.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
//see if there is a shape problem between stuff that is on the server and stuff that is in the flat file
if (dt.Columns.Count != columnsAtDestination.Length)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "There was a mismatch between the number of columns in the flat file (" +
columnsAtDestination.Aggregate((s, n) => s + Environment.NewLine + n) +
") and the number of columns in the RAW database table (" + dt.Columns.Count + ")"));
}
foreach (DataColumn column in dt.Columns)
{
if (!columnsAtDestination.Contains(column.ColumnName, StringComparer.CurrentCultureIgnoreCase))
{
throw new FlatFileLoadException("Column in flat file called " + column.ColumnName +
" does not appear in the RAW database table (after fixing potentially silly names)");
}
}
}
public void DiscoverState()
{
_unplannedChildren.Clear();
//assume no children exist
foreach (var anticipatedChild in _anticipatedChildren)
{
anticipatedChild.State = LoadDiagramState.NotFound;
}
//we dont exist either!
if (!Table.Exists())
{
State = LoadDiagramState.NotFound;
return;
}
//we do exist
State = LoadDiagramState.Found;
//discover children and marry them up to planned/ new unplanned ones
foreach (var discoveredColumn in Table.DiscoverColumns())
{
var match = _anticipatedChildren.SingleOrDefault(c => c.ColumnName.Equals(discoveredColumn.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase));
if (match != null)
{
match.SetState(discoveredColumn);
}
else
{
_unplannedChildren.Add(discoveredColumn); //unplanned column
}
}
//any NotFound or Different etc cols or any unplanned children
if (_anticipatedChildren.Any(c => c.State > LoadDiagramState.Found) || _unplannedChildren.Any())
{
State = LoadDiagramState.Different;//elevate our state to Different
}
}
public void Add(DiscoveredTable discoveredTable)
{
if (items.Any(i => i.Tag.Equals(discoveredTable)))
{
return;
}
var snip = new SubstringAutocompleteItem(discoveredTable.GetRuntimeName());
snip.MenuText = discoveredTable.GetRuntimeName(); //name of table
snip.Text = discoveredTable.GetFullyQualifiedName(); //full SQL
snip.Tag = discoveredTable; //record object for future reference
snip.ImageIndex = GetIndexFor(discoveredTable, RDMPConcept.TableInfo.ToString());
AddUnlessDuplicate(snip);
DiscoveredColumn[] columns = null;
try
{
if (discoveredTable.Exists())
{
columns = discoveredTable.DiscoverColumns();
}
}
catch (Exception)
{
//couldn't load nevermind
}
if (columns != null)
{
foreach (var col in columns)
{
Add(col);
}
}
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dilutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Creating table with SQL:" + sql));
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
using (var cmd = destDatabaseInfo.Server.GetCommand(sql, con))
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
//drop hic_ columns
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//if the ColumnInfo is explicitly marked to be ignored
if (_tableInfo.ColumnInfos.Any(c => c.IgnoreInLoads && c.GetRuntimeName(_copyToBubble.ToLoadStage()).Equals(colName)))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is marked IgnoreInLoads"));
drop = true;
}
//also drop any columns we have specifically been told to ignore in the DLE configuration
if (_hicDatabaseConfiguration.IgnoreColumns != null && _hicDatabaseConfiguration.IgnoreColumns.IsMatch(colName))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is matches the gloabl ignores pattern ({_hicDatabaseConfiguration.IgnoreColumns})"));
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dilution = dilutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dilution != null)
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Altering diluted column {colName} to {dilution.Data_type}"));
column.DataType.AlterTypeTo(dilution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
public UnplannedTable(DiscoveredTable table)
{
Table = table;
Columns = table.DiscoverColumns();
}
/// <summary>
/// Checks the object representation (Type) is perfectly synched with the underlying database (table must exist with matching name and all parameters must be column names with no unmapped fields)
/// </summary>
/// <param name="notifier"></param>
/// <param name="type"></param>
/// <param name="tables"></param>
private void CheckEntities(ICheckNotifier notifier, Type type, DiscoveredTable[] tables)
{
if (type.IsInterface)
{
return;
}
if (type.IsAbstract)
{
return;
}
if (typeof(SpontaneousObject).IsAssignableFrom(type))
{
return;
}
if (type.Name.StartsWith("Spontaneous"))
{
return;
}
//make sure argument was IMaps..
if (!typeof(IMapsDirectlyToDatabaseTable).IsAssignableFrom(type))
{
throw new ArgumentException("Type " + type.Name + " passed into method was not an IMapsDirectlyToDatabaseTable");
}
//make sure table exists with exact same name as class
DiscoveredTable table = tables.SingleOrDefault(t => t.GetRuntimeName().Equals(type.Name));
if (table == null)
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not find Table called " + type.Name + " (which implements IMapsDirectlyToDatabaseTable)", CheckResult.Fail, null));
return;
}
notifier.OnCheckPerformed(new CheckEventArgs("Found Table " + type.Name, CheckResult.Success, null));
//get columns from underlying database table
DiscoveredColumn[] columns = table.DiscoverColumns();
//get the properties that are not explicitly set as not mapping to database
PropertyInfo[] properties = TableRepository.GetPropertyInfos(type);
//this is part of the interface and hence doesnt exist in the underlying data table
properties = properties.Where(p => !p.Name.Equals("UpdateCommand")).ToArray();
//find columns in database where there are no properties with the same name
IEnumerable <DiscoveredColumn> missingProperties = columns.Where(col => !properties.Any(p => p.Name.Equals(col.GetRuntimeName())));
IEnumerable <PropertyInfo> missingDatabaseFields = properties.Where(p => !columns.Any(col => col.GetRuntimeName().Equals(p.Name)));
bool problems = false;
foreach (DiscoveredColumn missingProperty in missingProperties)
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Missing property " + missingProperty + " on class definition " + type.FullName + ", the underlying table contains this field but the class does not", CheckResult.Fail,
null));
problems = true;
}
foreach (PropertyInfo missingDatabaseField in missingDatabaseFields)
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Missing field in database table " + table + " when compared to class definition " + type.FullName
+ " property was called " + missingDatabaseField.Name
+ " and was of type " + missingDatabaseField.PropertyType
+ ((typeof(Enum).IsAssignableFrom(missingDatabaseField.PropertyType)?"(An Enum)":""))
, CheckResult.Warning,
null));
problems = true;
}
//Check nullability
foreach (PropertyInfo nonNullableProperty in properties.Where(property => property.PropertyType.IsEnum || property.PropertyType.IsValueType))
{
//it is something like int? i.e. a nullable value type
if (Nullable.GetUnderlyingType(nonNullableProperty.PropertyType) != null)
{
continue;
}
try
{
var col = table.DiscoverColumn(nonNullableProperty.Name);
if (col.AllowNulls)
{
notifier.OnCheckPerformed(new CheckEventArgs("Column " + nonNullableProperty.Name + " in table " + table + " allows nulls but is mapped to a ValueType or Enum", CheckResult.Warning, null));
}
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not check nullability of column " + nonNullableProperty.Name + " in table " + table, CheckResult.Fail, e));
}
}
if (!problems)
{
notifier.OnCheckPerformed(new CheckEventArgs("All fields present and correct in Type/Table " + table, CheckResult.Success, null));
}
}
public DatabaseFailureFactory(DiscoveredTable table)
{
_table = table;
_tableName = _table.GetFullyQualifiedName();
PrimaryKeys = _table.DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray();
}
public void DumpAllIdentifiersInTable_UnexpectedColumnFoundInIdentifierDumpTable()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "surname");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(20)";
preDiscardedColumn1.SaveToDatabase();
var preDiscardedColumn2 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn2.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn2.SqlDataType = "varchar(50)";
preDiscardedColumn2.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
dumper.Check(new AcceptAllCheckNotifier());
DiscoveredTable tableInDump = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable);
Assert.IsTrue(tableInDump.Exists(), "ID table did not exist");
var columnsInDump = tableInDump.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
//works and creates table on server
Assert.Contains("hic_validFrom", columnsInDump);
Assert.Contains("forename", columnsInDump);
Assert.Contains("chi", columnsInDump);
Assert.Contains("surname", columnsInDump);
//now delete it!
preDiscardedColumn2.DeleteInDatabase();
//now create a new dumper and watch it go crazy
IdentifierDumper dumper2 = new IdentifierDumper(tableInfoCreated);
var thrower = new ThrowImmediatelyCheckNotifier();
thrower.ThrowOnWarning = true;
try
{
var ex = Assert.Throws <Exception>(() => dumper2.Check(thrower));
Assert.AreEqual("Column forename was found in the IdentifierDump table ID_BulkData but was not one of the primary keys or a PreLoadDiscardedColumn", ex.Message);
}
finally
{
//Drop all this stuff
var server = IdentifierDump_Database.Server;
using (var con = server.GetConnection())
{
con.Open();
//leave the identifier dump in the way we found it (empty)
var cmdDrop = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable, con);
cmdDrop.ExecuteNonQuery();
var cmdDropArchive = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable + "_Archive", con);
cmdDropArchive.ExecuteNonQuery();
}
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public void Dispose(IDataLoadEventListener listener, Exception pipelineFailureExceptionIfAny)
{
try
{
if (_managedConnection != null)
{
//if there was an error
if (pipelineFailureExceptionIfAny != null)
{
_managedConnection.ManagedTransaction.AbandonAndCloseConnection();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction rolled back sucessfully"));
if (_bulkcopy != null)
{
_bulkcopy.Dispose();
}
}
else
{
_managedConnection.ManagedTransaction.CommitAndCloseConnection();
if (_bulkcopy != null)
{
_bulkcopy.Dispose();
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction committed sucessfully"));
}
}
}
catch (Exception e)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Commit failed on transaction (probably there was a previous error?)", e));
}
//if we have a primary key to create
if (pipelineFailureExceptionIfAny == null && _primaryKey != null && _primaryKey.Any() && discoveredTable != null && discoveredTable.Exists())
{
//Find the columns in the destination
var allColumns = discoveredTable.DiscoverColumns();
//if there are not yet any primary keys
if (allColumns.All(c => !c.IsPrimaryKey))
{
//find the columns the user decorated in his DataTable
DiscoveredColumn[] pkColumnsToCreate = allColumns.Where(c => _primaryKey.Any(pk => pk.Equals(c.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase))).ToArray();
//make sure we found all of them
if (pkColumnsToCreate.Length != _primaryKey.Count)
{
throw new Exception("Could not find primary key column(s) " + string.Join(",", _primaryKey) + " in table " + discoveredTable);
}
//create the primary key to match user provided columns
discoveredTable.CreatePrimaryKey(AlterTimeout, pkColumnsToCreate);
}
}
EndAuditIfExists();
}
public virtual string CreateTrigger(ICheckNotifier notifier, int timeout = 30)
{
if (!_primaryKeys.Any())
{
throw new TriggerException("There must be at least 1 primary key");
}
//if _Archive exists skip creating it
bool skipCreatingArchive = _archiveTable.Exists();
//check _Archive does not already exist
foreach (string forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })
{
if (_columns.Any(c => c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))
{
throw new TriggerException("Table " + _table + " already contains a column called " + forbiddenColumnName + " this column is reserved for Archiving");
}
}
bool b_mustCreate_validFrom = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));
bool b_mustCreate_dataloadRunId = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) && _createDataLoadRunIdAlso;
//forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table
if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)
{
throw new TriggerException("Cannot create trigger because table contains " + SpecialFieldNames.ValidFrom + " but not " + SpecialFieldNames.DataLoadRunID + " (ID must be placed before valid from in column order)");
}
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_dataloadRunId)
{
_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true, timeout);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_validFrom)
{
_table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, timeout);
}
//if we created columns we need to update _column
if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom)
{
_columns = _table.DiscoverColumns();
}
string sql = WorkOutArchiveTableCreationSQL();
if (!skipCreatingArchive)
{
using (var con = _server.GetConnection())
{
con.Open();
var cmdCreateArchive = _server.GetCommand(sql, con);
cmdCreateArchive.ExecuteNonQuery();
_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true, timeout);
_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true, timeout);
_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true, timeout);
}
}
return(sql);
}
///<inheritdoc/>
public void Check(ICheckNotifier notifier)
{
if (_table.Exists() && _archiveTable.Exists())
{
string[] liveCols = _table.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
string[] archiveCols = _archiveTable.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
var passed = CheckColumnOrderInTablesAndArchiveMatch(liveCols, archiveCols, notifier);
if (!passed)
{
return;
}
}
var factory = new TriggerImplementerFactory(_server.DatabaseType);
var implementer = factory.Create(_table);
bool present;
var primaryKeys = _table.DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray();
//we don't know the primary keys
if (!primaryKeys.Any())
{
try
{
//see if it exists
present = implementer.GetTriggerStatus() == TriggerStatus.Enabled;
}
catch (TriggerMissingException)
{
//clearly it doesnt exist
present = false;
}
}
else
{
try
{
//we do know the primary keys
present = implementer.CheckUpdateTriggerIsEnabledAndHasExpectedBody();
}
catch (IrreconcilableColumnDifferencesInArchiveException e)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Archive table for table " + _table +
" is corrupt, see inner Exception for specific errors", CheckResult.Fail, e));
return;
}
catch (Exception e)
{
NotifyFail(e, notifier, implementer);
return;
}
}
if (present)
{
notifier.OnCheckPerformed(new CheckEventArgs("Trigger presence/intactness for table " + _table + " matched expected presence", CheckResult.Success, null));
}
else
{
NotifyFail(null, notifier, implementer); //try creating it
}
}
public string GetCode()
{
var columns = _table.DiscoverColumns();
if (!columns.Any(c => c.GetRuntimeName().Equals("ID")))
{
throw new CodeGenerationException("Table must have an ID automnum column to become an IMapsDirectlyToDatabaseTable class");
}
StringBuilder classStart = new StringBuilder();
classStart.Append("public class " + _table.GetRuntimeName() + ": DatabaseEntity");
bool isINamed = columns.Any(c => c.GetRuntimeName() == "Name");
if (isINamed)
{
classStart.Append(",INamed");
}
classStart.AppendLine();
classStart.AppendLine("{");
StringBuilder databaseFields = new StringBuilder();
databaseFields.AppendLine("\t#region Database Properties");
databaseFields.AppendLine();
StringBuilder databaseProperties = new StringBuilder();
StringBuilder constructors = new StringBuilder();
constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository/*, TODO Required Construction Properties For NEW*/)");
constructors.AppendLine(@" {
repository.InsertAndHydrate(this,new Dictionary<string, object>()
{
//TODO Any parameters here as key value pairs
});
if (ID == 0 || Repository != repository)
throw new ArgumentException(""Repository failed to properly hydrate this class"");
}");
constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository, DbDataReader r): base(repository, r)");
constructors.AppendLine("\t{");
foreach (var col in columns.Where(c => c.GetRuntimeName() != "ID"))
{
string setCode;
var type = GetCSharpTypeFor(col, out setCode);
var propertyName = col.GetRuntimeName();
var fieldString = col.GetRuntimeName();
//cammel case it
fieldString = "_" + fieldString.Substring(0, 1).ToLower() + fieldString.Substring(1);
databaseFields.AppendLine("\tprivate " + type + " " + fieldString + ";");
databaseProperties.AppendLine("\tpublic " + type + " " + propertyName);
databaseProperties.AppendLine("\t{");
databaseProperties.AppendLine("\t\tget { return " + fieldString + ";}");
databaseProperties.AppendLine("\t\tset { SetField(ref " + fieldString + ", value);}");
databaseProperties.AppendLine("\t}");
constructors.AppendLine("\t\t" + propertyName + " = " + setCode);
}
databaseFields.AppendLine("\t#endregion");
databaseFields.AppendLine();
constructors.AppendLine("\t}");
if (isINamed)
{
constructors.AppendLine("\t" + @"public override string ToString()
{
return Name;
}");
}
return(classStart.ToString() + databaseFields + databaseProperties + constructors + "}");
}
public DataTable ProcessPipelineData(DataTable toProcess, IDataLoadEventListener listener, GracefulCancellationToken cancellationToken)
{
if (toProcess == null)
{
return(null);
}
IDatabaseColumnRequestAdjuster adjuster = null;
if (Adjuster != null)
{
var constructor = new ObjectConstructor();
adjuster = (IDatabaseColumnRequestAdjuster)constructor.Construct(Adjuster);
}
//work out the table name for the table we are going to create
if (TargetTableName == null)
{
if (string.IsNullOrWhiteSpace(toProcess.TableName))
{
throw new Exception("Chunk did not have a TableName, did not know what to call the newly created table");
}
TargetTableName = QuerySyntaxHelper.MakeHeaderNameSane(toProcess.TableName);
}
ClearPrimaryKeyFromDataTableAndExplicitWriteTypes(toProcess);
StartAuditIfExists(TargetTableName);
if (_loggingDatabaseListener != null)
{
listener = new ForkDataLoadEventListener(listener, _loggingDatabaseListener);
}
EnsureTableHasDataInIt(toProcess);
bool createdTable = false;
if (_firstTime)
{
bool tableAlreadyExistsButEmpty = false;
if (!_database.Exists())
{
throw new Exception("Database " + _database + " does not exist");
}
discoveredTable = _database.ExpectTable(TargetTableName);
//table already exists
if (discoveredTable.Exists())
{
tableAlreadyExistsButEmpty = true;
if (!AllowLoadingPopulatedTables)
{
if (discoveredTable.IsEmpty())
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found table " + TargetTableName + " already, normally this would forbid you from loading it (data duplication / no primary key etc) but it is empty so we are happy to load it, it will not be created"));
}
else
{
throw new Exception("There is already a table called " + TargetTableName + " at the destination " + _database);
}
}
if (AllowResizingColumnsAtUploadTime)
{
_dataTypeDictionary = discoveredTable.DiscoverColumns().ToDictionary(k => k.GetRuntimeName(), v => v.GetDataTypeComputer(), StringComparer.CurrentCultureIgnoreCase);
}
}
else
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that the table name " + TargetTableName + " is unique at destination " + _database));
}
//create connection to destination
if (!tableAlreadyExistsButEmpty)
{
createdTable = true;
if (AllowResizingColumnsAtUploadTime)
{
_database.CreateTable(out _dataTypeDictionary, TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
else
{
_database.CreateTable(TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Created table " + TargetTableName + " successfully."));
}
_managedConnection = _server.BeginNewTransactedConnection();
_bulkcopy = discoveredTable.BeginBulkInsert(_managedConnection.ManagedTransaction);
if (Culture != null)
{
_bulkcopy.DateTimeDecider.Culture = Culture;
}
_firstTime = false;
}
try
{
if (AllowResizingColumnsAtUploadTime && !createdTable)
{
ResizeColumnsIfRequired(toProcess, listener);
}
//push the data
swTimeSpentWritting.Start();
_affectedRows += _bulkcopy.Upload(toProcess);
swTimeSpentWritting.Stop();
listener.OnProgress(this, new ProgressEventArgs("Uploading to " + TargetTableName, new ProgressMeasurement(_affectedRows, ProgressType.Records), swTimeSpentWritting.Elapsed));
}
catch (Exception e)
{
_managedConnection.ManagedTransaction.AbandonAndCloseConnection();
if (LoggingServer != null)
{
_dataLoadInfo.LogFatalError(GetType().Name, ExceptionHelper.ExceptionToListOfInnerMessages(e, true));
}
throw new Exception("Failed to write rows (in transaction) to table " + TargetTableName, e);
}
return(null);
}
