public void NowTestDataInsertion(DatabaseType dbType)
{
AlterTest_InvalidThenRecreateItAndItsValidAgain(dbType);
_table.Insert(new Dictionary <string, object>
{
{ "name", "Franky" },
{ "bubbles", 3 },
{ "hic_validFrom", new DateTime(2001, 1, 2) },
{ "hic_dataLoadRunID", 7 }
});
RunSQL("UPDATE {0} set bubbles =99", _table.GetFullyQualifiedName());
//new value is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _table.GetFullyQualifiedName()));
//archived value is 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _archiveTable.GetFullyQualifiedName()));
//Legacy table valued function only works for MicrosoftSQLServer
if (dbType == DatabaseType.MicrosoftSQLServer)
{
//legacy in 2001-01-01 it didn't exist
Assert.IsNull(ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-01') where name = 'Franky'"));
//legacy in 2001-01-03 it did exist and was 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-03') where name = 'Franky'"));
//legacy boundary case?
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-02') where name = 'Franky'"));
//legacy today it is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy(GETDATE()) where name = 'Franky'"));
}
}
public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
{
SqlCommand cmd;
switch (tableToDrop.TableType)
{
case TableType.View:
if (connection.Database != tableToDrop.Database.GetRuntimeName())
{
connection.ChangeDatabase(tableToDrop.GetRuntimeName());
}
if (!connection.Database.ToLower().Equals(tableToDrop.Database.GetRuntimeName().ToLower()))
{
throw new NotSupportedException("Cannot drop view " + tableToDrop + " because it exists in database " + tableToDrop.Database.GetRuntimeName() + " while the current current database connection is pointed at database:" + connection.Database + " (use .ChangeDatabase on the connection first) - SQL Server does not support cross database view dropping");
}
cmd = new SqlCommand("DROP VIEW " + tableToDrop.GetRuntimeName(), (SqlConnection)connection);
break;
case TableType.Table:
cmd = new SqlCommand("DROP TABLE " + tableToDrop.GetFullyQualifiedName(), (SqlConnection)connection);
break;
case TableType.TableValuedFunction:
DropFunction(connection, (DiscoveredTableValuedFunction)tableToDrop);
return;
default:
throw new ArgumentOutOfRangeException();
}
using (cmd)
cmd.ExecuteNonQuery();
}
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
{
var syntax = new PostgreSqlSyntaxHelper();
return(@"ALTER TABLE " + discoveredTable.GetFullyQualifiedName() + @"
RENAME TO " + syntax.EnsureWrapped(newName));
}
private string[] ListPrimaryKeys(IManagedConnection con, DiscoveredTable table)
{
string query = $@"SELECT
pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute
WHERE
pg_class.oid = '{table.GetFullyQualifiedName()}'::regclass AND
indrelid = pg_class.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary";
List <string> toReturn = new List <string>();
using (DbCommand cmd = table.GetCommand(query, con.Connection))
{
cmd.Transaction = con.Transaction;
using (DbDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
toReturn.Add((string)r["attname"]);
}
r.Close();
}
}
return(toReturn.ToArray());
}
protected override string BuildUpdateImpl(DiscoveredTable table1, DiscoveredTable table2, List <CustomLine> lines)
{
// This implementation is based on:
// https://stackoverflow.com/a/32748797/4824531
/*MERGE INTO table1 t1
* USING
* (
* -- For more complicated queries you can use WITH clause here
* SELECT * FROM table2
* )t2
* ON(t1.id = t2.id)
* WHEN MATCHED THEN UPDATE SET
* t1.name = t2.name,
* t1.desc = t2.desc;*/
return(string.Format(
@"MERGE INTO {1} t1
USING
(
SELECT * FROM {2}
)t2
on ({3})
WHEN MATCHED THEN UPDATE SET
{0}
WHERE
{4}",
string.Join(", " + Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SET).Select(c => c.Text)),
table1.GetFullyQualifiedName(),
table2.GetFullyQualifiedName(),
string.Join(" AND ", lines.Where(l => l.LocationToInsert == QueryComponent.JoinInfoJoin).Select(c => c.Text)),
string.Join(" AND ", lines.Where(l => l.LocationToInsert == QueryComponent.WHERE).Select(c => c.Text))));
}
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();
}
}
protected override string BuildUpdateImpl(DiscoveredTable table1, DiscoveredTable table2, List <CustomLine> lines)
{
//https://stackoverflow.com/a/7869611
string joinSql = string.Join(" AND ",
lines.Where(l => l.LocationToInsert == QueryComponent.JoinInfoJoin).Select(c => c.Text));
string whereSql = string.Join(" AND ",
lines.Where(l => l.LocationToInsert == QueryComponent.WHERE).Select(c => c.Text));
return(string.Format(
@"UPDATE {1} AS t1
SET
{0}
FROM
{2} AS t2
WHERE
{3}
{4}
{5}
",
string.Join(", " + Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SET)
.Select(c =>
//seems like you cant specify the table alias in the SET section of the query
c.Text.Replace("t1.", ""))),
table1.GetFullyQualifiedName(),
table2.GetFullyQualifiedName(),
joinSql,
!string.IsNullOrWhiteSpace(whereSql) ? "AND" :"",
!string.IsNullOrWhiteSpace(whereSql) ? "(" + whereSql + ")":""
));
}
/// <summary>
/// Returns SQL to update the <paramref name="topXRows"/> with the provided SET string
/// </summary>
/// <param name="tbl">Table to update</param>
/// <param name="topXRows">Number of rows to change</param>
/// <param name="setSql">Set SQL e.g. "Set Col1='fish'"</param>
/// <returns></returns>
private string GetUpdateTopXSql(DiscoveredTable tbl, int topXRows, string setSql)
{
switch (tbl.Database.Server.DatabaseType)
{
case DatabaseType.MicrosoftSQLServer:
return
($"UPDATE TOP ({topXRows}) {tbl.GetFullyQualifiedName()} {setSql}");
case DatabaseType.MySql:
return
($"UPDATE {tbl.GetFullyQualifiedName()} {setSql} LIMIT {topXRows}");
default:
throw new ArgumentOutOfRangeException();
}
}
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);
}
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 NowTestDataInsertion(DatabaseType dbType)
{
AlterTest_InvalidThenRecreateItAndItsValidAgain(dbType);
_table.Insert(new Dictionary <string, object>
{
{ "name", "Franky" },
{ "bubbles", 3 },
{ "hic_validFrom", new DateTime(2001, 1, 2) },
{ "hic_dataLoadRunID", 7 }
});
var liveOldRow = _table.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 3);
Assert.AreEqual(new DateTime(2001, 1, 2), ((DateTime)liveOldRow[SpecialFieldNames.ValidFrom]));
RunSQL("UPDATE {0} set bubbles =99", _table.GetFullyQualifiedName());
//new value is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _table.GetFullyQualifiedName()));
//archived value is 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _archiveTable.GetFullyQualifiedName()));
//Legacy table valued function only works for MicrosoftSQLServer
if (dbType == DatabaseType.MicrosoftSQLServer)
{
//legacy in 2001-01-01 it didn't exist
Assert.IsNull(ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-01') where name = 'Franky'"));
//legacy in 2001-01-03 it did exist and was 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-03') where name = 'Franky'"));
//legacy boundary case?
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-02') where name = 'Franky'"));
//legacy today it is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy(GETDATE()) where name = 'Franky'"));
}
// Live row should now reflect that it is validFrom today
var liveNewRow = _table.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 99);
Assert.AreEqual(DateTime.Now.Date, ((DateTime)liveNewRow[SpecialFieldNames.ValidFrom]).Date);
// Archived row should not have had it's validFrom field broken
var archivedRow = _archiveTable.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 3);
Assert.AreEqual(new DateTime(2001, 1, 2), ((DateTime)archivedRow[SpecialFieldNames.ValidFrom]));
}
public MicrosoftSQLBulkCopy(DiscoveredTable targetTable, IManagedConnection connection, CultureInfo culture) : base(targetTable, connection, culture)
{
_bulkcopy = new SqlBulkCopy((SqlConnection)connection.Connection, SqlBulkCopyOptions.KeepIdentity, (SqlTransaction)connection.Transaction)
{
BulkCopyTimeout = 50000,
DestinationTableName = targetTable.GetFullyQualifiedName()
};
}
/// <summary>
/// Creates a mock implementation of <see cref="ITableInfo"/> that points to the live database table <paramref name="table"/>
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static ITableInfo Mock_TableInfo(DiscoveredTable table)
{
return(Mock.Of <ITableInfo>(p =>
p.Name == table.GetFullyQualifiedName() &&
p.Database == table.Database.GetRuntimeName() &&
p.DatabaseType == table.Database.Server.DatabaseType &&
p.IsTableValuedFunction == (table.TableType == TableType.TableValuedFunction) &&
p.Discover(It.IsAny <DataAccessContext>()) == table));
}
public string GetSql()
{
var response = _table.GetQuerySyntaxHelper().HowDoWeAchieveTopX(100);
switch (response.Location)
{
case QueryComponent.SELECT:
return("Select " + response.SQL + " * from " + _table.GetFullyQualifiedName());
case QueryComponent.WHERE:
return("Select * from " + _table.GetFullyQualifiedName() + " WHERE " + response.SQL);
case QueryComponent.Postfix:
return("Select * from " + _table.GetFullyQualifiedName() + " " + response.SQL);
default:
throw new ArgumentOutOfRangeException();
}
}
public override void Setup(IMappingTableOptions options)
{
_options = options;
_swapTable = options.Discover();
_server = _swapTable.Database.Server;
if (!_swapTable.Exists())
{
throw new ArgumentException($"Swap table '{_swapTable.GetFullyQualifiedName()}' did not exist on server '{_server}'");
}
}
/// <summary>
/// Returns SQL to update a single <paramref name="word"/> in the <paramref name="table"/> row referenced by the primary key value
/// in <paramref name="failure"/>
/// </summary>
/// <param name="table"></param>
/// <param name="primaryKeys"></param>
/// <param name="syntax"></param>
/// <param name="failure"></param>
/// <param name="word">The word or collection of words that should be redacted</param>
/// <returns></returns>
protected string GetUpdateWordSql(DiscoveredTable table,
Dictionary <DiscoveredTable, DiscoveredColumn> primaryKeys, IQuerySyntaxHelper syntax, Failure failure, string word)
{
if (string.IsNullOrEmpty(failure.ResourcePrimaryKey))
{
throw new ArgumentException("Failure record's primary key is blank, cannot update database");
}
return($@"update {table.GetFullyQualifiedName()}
SET {syntax.EnsureWrapped(failure.ProblemField)} =
REPLACE({syntax.EnsureWrapped(failure.ProblemField)},'{syntax.Escape(word)}', 'SMI_REDACTED')
WHERE {primaryKeys[table].GetFullyQualifiedName()} = '{syntax.Escape(failure.ResourcePrimaryKey)}'");
}
/// <summary>
/// Sets up a CHI/ECHI mapping table with fallback guid and populates each table with a single record.
/// 0101010101 is a known CHI and 0202020202 is an known one (which was assigned a temporary guid mapping).
/// Also prepares the main map table for DLE loading (<see cref="TriggerImplementer"/>)
/// </summary>
/// <param name="dbType"></param>
/// <param name="map"></param>
/// <param name="guidTable"></param>
/// <param name="mapperOptions"></param>
/// <param name="guids">true to create a <see cref="TableLookupWithGuidFallbackSwapper"/> otherwise creates a <see cref="TableLookupSwapper"/></param>
private void SetupMappers(DatabaseType dbType, out DiscoveredTable map, out DiscoveredTable guidTable, out IdentifierMapperOptions mapperOptions, bool guids = true)
{
var db = GetCleanedServer(dbType);
using (var dt = new DataTable())
{
dt.Columns.Add("CHI");
dt.Columns.Add("ECHI");
dt.PrimaryKey = new [] { dt.Columns["CHI"] };
dt.Rows.Add("0101010101", "0A0A0A0A0A");
map = db.CreateTable("Map", dt);
}
mapperOptions = new IdentifierMapperOptions()
{
MappingTableName = map.GetFullyQualifiedName(),
MappingConnectionString = db.Server.Builder.ConnectionString,
SwapColumnName = "CHI",
ReplacementColumnName = "ECHI",
MappingDatabaseType = db.Server.DatabaseType,
SwapperType = (guids ? typeof(TableLookupWithGuidFallbackSwapper):typeof(TableLookupSwapper)).FullName
};
if (guids)
{
var swapper = new TableLookupWithGuidFallbackSwapper();
swapper.Setup(mapperOptions);
guidTable = swapper.GetGuidTableIfAny(mapperOptions);
Assert.AreEqual(0, guidTable.GetRowCount(), "No temporary guids should exist yet");
Assert.AreEqual(1, map.GetRowCount(), "We should have a mapping table with 1 entry");
//lookup an as yet unknown value
swapper.GetSubstitutionFor("0202020202", out _);
Assert.AreEqual(1, map.GetRowCount(), "We should have a mapping table with 1 entry");
Assert.AreEqual(1, guidTable.GetRowCount(), "We should have a temporary guid for 0202020202");
}
else
{
guidTable = null;
}
// make a fake data load into this table (create trigger and insert/update)
var triggerImplementer = new TriggerImplementerFactory(dbType).Create(map);
triggerImplementer.CreateTrigger(new ThrowImmediatelyCheckNotifier());
}
public override string GetSubstitutionFor(string toSwap, out string reason)
{
reason = null;
// If the cached key matches, return the last value
if (string.Equals(toSwap, _lastKey) && _lastVal != null)
{
_logger.Debug("Using cached swap value");
CacheHit++;
Success++;
return(_lastVal);
}
CacheMiss++;
// Else fall through to the database lookup
using (new TimeTracker(DatabaseStopwatch))
using (DbConnection con = _server.GetConnection())
{
con.Open();
string sql = string.Format("SELECT {0} FROM {1} WHERE {2}=@val",
_options.ReplacementColumnName,
_swapTable.GetFullyQualifiedName(),
_options.SwapColumnName);
DbCommand cmd = _server.GetCommand(sql, con);
_server.AddParameterWithValueToCommand("@val", cmd, toSwap);
object result = cmd.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
reason = "No match found for '" + toSwap + "'";
Fail++;
return(null);
}
_lastKey = toSwap;
_lastVal = result.ToString();
++Success;
return(_lastVal);
}
}
protected override string BuildUpdateImpl(DiscoveredTable table1, DiscoveredTable table2, List <CustomLine> lines)
{
return(string.Format(
@"UPDATE {1} t1
join {2} t2
on
{3}
SET
{0}
WHERE
{4}",
string.Join(", " + Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SET).Select(c => c.Text)),
table1.GetFullyQualifiedName(),
table2.GetFullyQualifiedName(),
string.Join(" AND ", lines.Where(l => l.LocationToInsert == QueryComponent.JoinInfoJoin).Select(c => c.Text)),
string.Join(" AND ", lines.Where(l => l.LocationToInsert == QueryComponent.WHERE).Select(c => c.Text))));
}
private string WorkOutArchiveTableCreationSQL()
{
//script original table
string createTableSQL = _table.ScriptTableCreation(true, true, true);
string toReplaceTableName = "CREATE TABLE " + _table.GetFullyQualifiedName();
if (!createTableSQL.Contains(toReplaceTableName))
{
throw new Exception("Expected to find occurrence of " + toReplaceTableName + " in the SQL " + createTableSQL);
}
//rename table
createTableSQL = createTableSQL.Replace(toReplaceTableName, "CREATE TABLE " + _archiveTable.GetFullyQualifiedName());
string toRemoveIdentities = "IDENTITY\\(\\d+,\\d+\\)";
//drop identity bit
createTableSQL = Regex.Replace(createTableSQL, toRemoveIdentities, "");
return(createTableSQL);
}
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);
}
}
}
/// <summary>
/// Returns a query for fetching the latest entry in the archive that matches a given private identifier (query contains parameter @currentSwapColValue)
/// </summary>
/// <param name="archiveTable"></param>
/// <param name="swapCol">The private identifier column name e.g. CHI</param>
/// <param name="forCol">The public release identifier column name e.g. ECHI</param>
/// <returns>SQL for fetching the latest release identifier value (e.g. ECHI value) from the archive</returns>
private string GetArchiveFetchSql(DiscoveredTable archiveTable, string swapCol, string forCol)
{
// Work out how to get the latest entry in the _Archive table that corresponds to a given private identifier (e.g. CHI)
var syntax = archiveTable.Database.Server.GetQuerySyntaxHelper();
var topX = syntax.HowDoWeAchieveTopX(1);
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
if (topX.Location == QueryComponent.SELECT)
{
sb.AppendLine(topX.SQL);
}
sb.AppendLine(forCol);
sb.AppendLine("FROM " + archiveTable.GetFullyQualifiedName());
sb.AppendLine("WHERE");
sb.AppendLine($"{swapCol} = @currentSwapColValue");
if (topX.Location == QueryComponent.WHERE)
{
sb.AppendLine("AND");
sb.AppendLine(topX.SQL);
}
sb.AppendLine("ORDER BY");
sb.AppendLine(SpecialFieldNames.ValidFrom + " desc");
if (topX.Location == QueryComponent.Postfix)
{
sb.AppendLine(topX.SQL);
}
return(sb.ToString());
}
private void AddColumnToTable(DiscoveredTable table, string desiredColumnName, string desiredColumnType, IDataLoadEventListener listener)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, string.Format("Adding column '{0}' with datatype '{1}' to table '{2}'", desiredColumnName, desiredColumnType, table.GetFullyQualifiedName())));
table.AddColumn(desiredColumnName, desiredColumnType, true, 500);
}
private void DropColumnFromTable(DiscoveredTable table, string columnName, IDataLoadEventListener listener)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, string.Format("Dropping column '{0}' from table '{1}'", columnName, table.GetFullyQualifiedName())));
var col = table.DiscoverColumn(columnName);
table.DropColumn(col);
}
/// <summary>
/// Generates and runs an SQL command on <paramref name="t"/>
/// </summary>
/// <param name="t"></param>
/// <param name="message"></param>
protected virtual int UpdateTable(DiscoveredTable t, UpdateValuesMessage message)
{
var audit = _audits.GetOrAdd(t, (k) => new UpdateTableAudit(k));
StringBuilder builder = new StringBuilder();
builder.AppendLine("UPDATE ");
builder.AppendLine(t.GetFullyQualifiedName());
builder.AppendLine(" SET ");
for (int i = 0; i < message.WriteIntoFields.Length; i++)
{
var col = t.DiscoverColumn(message.WriteIntoFields[i]);
builder.Append(GetFieldEqualsValueExpression(col, message.Values[i], "="));
//if there are more SET fields to come
if (i < message.WriteIntoFields.Length - 1)
{
builder.AppendLine(",");
}
}
builder.AppendLine(" WHERE ");
for (int i = 0; i < message.WhereFields.Length; i++)
{
var col = t.DiscoverColumn(message.WhereFields[i]);
builder.Append(GetFieldEqualsValueExpression(col, message.HaveValues[i], message?.Operators?[i]));
//if there are more WHERE fields to come
if (i < message.WhereFields.Length - 1)
{
builder.AppendLine(" AND ");
}
}
var sql = builder.ToString();
int affectedRows = 0;
audit.StartOne();
try
{
using (var con = t.Database.Server.GetConnection())
{
con.Open();
var cmd = t.Database.Server.GetCommand(sql, con);
cmd.CommandTimeout = UpdateTimeout;
try
{
return(affectedRows = cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new Exception($"Failed to excute query {sql} ", ex);
}
}
}
finally
{
audit.EndOne(affectedRows < 0 ? 0 : affectedRows);
}
}
private void MigrateExistingData(Func <string, bool> shouldApplySql, DbConnection con, ICheckNotifier notifier, DiscoveredTable tbl)
{
string from = _colToNuke.GetRuntimeName(LoadStage.PostLoad);
string to = _newANOColumnInfo.GetRuntimeName(LoadStage.PostLoad);
//create an empty table for the anonymised data
DbCommand cmdCreateTempMap = DatabaseCommandHelper.GetCommand(string.Format("SELECT top 0 {0},{1} into TempANOMap from {2}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdCreateTempMap.CommandText))
{
throw new Exception("User decided not to create the TempANOMap table");
}
cmdCreateTempMap.ExecuteNonQuery();
try
{
//get the existing data
DbCommand cmdGetExistingData = DatabaseCommandHelper.GetCommand(string.Format("SELECT {0},{1} from {2}", from, to, tbl.GetFullyQualifiedName()), con);
DbDataAdapter da = DatabaseCommandHelper.GetDataAdapter(cmdGetExistingData);
DataTable dt = new DataTable();
da.Fill(dt);//into memory
//transform it in memory
ANOTransformer transformer = new ANOTransformer(_toConformTo, new FromCheckNotifierToDataLoadEventListener(notifier));
transformer.Transform(dt, dt.Columns[0], dt.Columns[1]);
var tempAnoMapTbl = tbl.Database.ExpectTable("TempANOMap");
using (var insert = tempAnoMapTbl.BeginBulkInsert())
{
insert.Upload(dt);
}
//create an empty table for the anonymised data
DbCommand cmdUpdateMainTable = DatabaseCommandHelper.GetCommand(string.Format("UPDATE source set source.{1} = map.{1} from {2} source join TempANOMap map on source.{0}=map.{0}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdUpdateMainTable.CommandText))
{
throw new Exception("User decided not to perform update on table");
}
cmdUpdateMainTable.ExecuteNonQuery();
}
finally
{
//always drop the temp anomap
DbCommand dropMappingTable = DatabaseCommandHelper.GetCommand("DROP TABLE TempANOMap", con);
dropMappingTable.ExecuteNonQuery();
}
}
private void LoadFile(DiscoveredTable tableToLoad, FileInfo fileToLoad, DiscoveredDatabase dbInfo, Stopwatch timer, IDataLoadJob job, GracefulCancellationToken token)
{
using (var con = dbInfo.Server.GetConnection())
{
DataTable dt = tableToLoad.GetDataTable(0);
using (var insert = tableToLoad.BeginBulkInsert(Culture))
{
// setup bulk insert it into destination
insert.Timeout = 500000;
//if user wants to use a specific explicit format for datetimes
if (ExplicitDateTimeFormat != null)
{
insert.DateTimeDecider.Settings.ExplicitDateFormats = new string[] { ExplicitDateTimeFormat }
}
;
//bulk insert ito destination
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to open file " + fileToLoad.FullName));
OpenFile(fileToLoad, job, token);
//confirm the validity of the headers
ConfirmFlatFileHeadersAgainstDataTable(dt, job);
con.Open();
//now we will read data out of the file in batches
int batchNumber = 1;
int maxBatchSize = 10000;
int recordsCreatedSoFar = 0;
try
{
//while there is data to be loaded into table
while (IterativelyBatchLoadDataIntoDataTable(dt, maxBatchSize, token) != 0)
{
DropEmptyColumns(dt);
ConfirmFitToDestination(dt, tableToLoad, job);
try
{
recordsCreatedSoFar += insert.Upload(dt);
dt.Rows.Clear(); //very important otherwise we add more to the end of the table but still insert last batches records resulting in exponentially multiplying upload sizes of duplicate records!
job.OnProgress(this,
new ProgressEventArgs(tableToLoad.GetFullyQualifiedName(),
new ProgressMeasurement(recordsCreatedSoFar, ProgressType.Records), timer.Elapsed));
}
catch (Exception e)
{
throw new Exception("Error processing batch number " + batchNumber + " (of batch size " + maxBatchSize + ")", e);
}
}
}
catch (Exception e)
{
throw new FlatFileLoadException("Error processing file " + fileToLoad, e);
}
finally
{
CloseFile();
}
}
}
}
private void ConfirmTableDeletion(DiscoveredTable expectTable)
{
if (expectTable.Exists())
{
var confirm = MessageBox.Show(String.Format("A table named {0} has been created as part of this import. Do you want to keep it?", expectTable.GetFullyQualifiedName()),
"Confirm", MessageBoxButtons.YesNo);
if (confirm == DialogResult.No)
{
expectTable.Drop();
}
}
}
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);
}
protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
{
return(string.Format(@"alter table {0} rename to {1}", discoveredTable.GetFullyQualifiedName(), newName));
}
