private void GetInsertData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
{
var memoryRepository = new MemoryCatalogueRepository();
var sytnaxHelper = server.GetQuerySyntaxHelper();
string tableName = _tableInfo.Name;
string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");
var whereStatement = "";
foreach (ColumnInfo pk in _pks)
{
whereStatement += string.Format("{0}.{1} = {2}.{1} AND ", tableName, pk.GetRuntimeName(), archiveTableName);
}
var qb = new QueryBuilder(null, null, new[] { _tableInfo });
qb.TopX = _batchSize;
qb.AddColumnRange(_tableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());
//where
var filter1 = new SpontaneouslyInventedFilter(memoryRepository, null, SpecialFieldNames.DataLoadRunID + " = " + _dataLoadRunID, "DataLoadRunID matches", null, null);
var filter2 =
new SpontaneouslyInventedFilter(memoryRepository, null,
string.Format(@" not exists (
select 1 from {0} where {1} {2} < {3}
)", archiveTableName, whereStatement, SpecialFieldNames.DataLoadRunID, _dataLoadRunID),
"Record doesn't exist in archive", null, null);
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { filter1, filter2 }, FilterContainerOperation.AND);
Inserts = new DataTable();
FillTableWithQueryIfUserConsents(Inserts, qb.SQL, checkNotifier, server);
}
/// <inheritdoc/>
public void DoImport(out TableInfo tableInfoCreated, out ColumnInfo[] columnInfosCreated)
{
string tableName;
string databaseName;
var querySyntaxHelper = _server.GetQuerySyntaxHelper();
tableName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);
if (_type == DatabaseType.MicrosoftSQLServer || _type == DatabaseType.PostgreSql)
{
tableName += "." + (_importFromSchema ?? querySyntaxHelper.GetDefaultSchemaIfAny()) + ".";
}
else if (_type == DatabaseType.MySql || _type == DatabaseType.Oracle)
{
tableName += ".";
}
else
{
throw new NotSupportedException("Unknown Type:" + _type);
}
tableName += querySyntaxHelper.EnsureWrapped(_importTableName);
databaseName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);
DiscoveredColumn[] discoveredColumns = _server.ExpectDatabase(_importDatabaseName)
.ExpectTable(_importTableName, _importFromSchema, _importTableType)
.DiscoverColumns();
TableInfo parent = new TableInfo(_repository, tableName)
{
DatabaseType = _type,
Database = databaseName,
Server = _importFromServer,
Schema = _importFromSchema,
IsView = _importTableType == TableType.View
};
parent.SaveToDatabase();
List <ColumnInfo> newCols = new List <ColumnInfo>();
foreach (DiscoveredColumn discoveredColumn in discoveredColumns)
{
newCols.Add(CreateNewColumnInfo(parent, discoveredColumn));
}
tableInfoCreated = parent;
columnInfosCreated = newCols.ToArray();
//if there is a username then we need to associate it with the TableInfo we just created
if (!string.IsNullOrWhiteSpace(_username))
{
DataAccessCredentialsFactory credentialsFactory = new DataAccessCredentialsFactory(_repository);
credentialsFactory.Create(tableInfoCreated, _username, _password, _usageContext);
}
}
/// <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 DiscoveredTable Discover()
{
var server = new DiscoveredServer(MappingConnectionString, MappingDatabaseType);
var idx = MappingTableName.LastIndexOf('.');
var tableNameUnqualified = MappingTableName.Substring(idx + 1);
idx = MappingTableName.IndexOf('.');
if (idx == -1)
{
throw new ArgumentException($"MappingTableName did not contain the database/user section:'{MappingTableName}'");
}
var databaseName = server.GetQuerySyntaxHelper().GetRuntimeName(MappingTableName.Substring(0, idx));
if (string.IsNullOrWhiteSpace(databaseName))
{
throw new ArgumentException($"Could not get database/username from MappingTableName {MappingTableName}");
}
return(server.ExpectDatabase(databaseName).ExpectTable(tableNameUnqualified));
}
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);
}
public override int UploadImpl(DataTable dt)
{
//don't run an insert if there are 0 rows
if (dt.Rows.Count == 0)
{
return(0);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var tt = syntaxHelper.TypeTranslater;
//if the column name is a reserved keyword e.g. "Comment" we need to give it a new name
Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName);
int affectedRows = 0;
var mapping = GetMapping(dt.Columns.Cast <DataColumn>());
var dateColumns = new HashSet <DataColumn>();
var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})",
string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')),
string.Join(",", mapping.Keys.Select(c => parameterNames[c]))
);
using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection))
{
//send all the data at once
cmd.ArrayBindCount = dt.Rows.Count;
foreach (var kvp in mapping)
{
var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value);
p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType);
if (p.DbType == DbType.DateTime)
{
dateColumns.Add(kvp.Key);
}
}
var values = new Dictionary <DataColumn, List <object> >();
foreach (DataColumn c in mapping.Keys)
{
values.Add(c, new List <object>());
}
foreach (DataRow dataRow in dt.Rows)
{
//populate parameters for current row
foreach (var col in mapping.Keys)
{
var val = dataRow[col];
if (val is string && string.IsNullOrWhiteSpace((string)val))
{
val = null;
}
else
if (val == null || val == DBNull.Value)
{
val = null;
}
else if (dateColumns.Contains(col))
{
if (val is string s)
{
val = (DateTime)DateTimeDecider.Parse(s);
}
else
{
val = Convert.ToDateTime(dataRow[col]);
}
}
values[col].Add(val);
}
}
foreach (DataColumn col in mapping.Keys)
{
var param = cmd.Parameters[parameterNames[col]];
param.Value = values[col].ToArray();
}
//send query
affectedRows += cmd.ExecuteNonQuery();
}
return(affectedRows);
}
private void GetUpdatetData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
{
var sytnaxHelper = server.GetQuerySyntaxHelper();
string tableName = _tableInfo.Name;
string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");
var whereStatement = string.Join(" AND ", _pks.Select(pk => string.Format("{0}.{1} = {2}.{1} ", tableName, pk.GetRuntimeName(), archiveTableName)));
//hold onto your hats ladies and gentlemen, we start by selecting every column twice with a cross apply:
//once from the main table e.g. Col1,Col2,Col3
//then once from the archive e.g. zzArchivezzCol1, zzArchivezzCol2, zzArchivezzCol3 -- notice this is a query alias not affecting anything underlying
//this lets us then fill 2 DataTables from the combo table we get back with absolute assurity of same row semantically by primary key
var sql = "";
switch (sytnaxHelper.DatabaseType)
{
case DatabaseType.MicrosoftSQLServer:
sql = @"
--Records which appear in the archive
SELECT top {0}
{6},
{7}
FROM {1}
CROSS APPLY
(
SELECT TOP 1 {2}.*
FROM {2}
WHERE
{3}
order by " + SpecialFieldNames.ValidFrom + @" desc
) Archive
where
{1}.{4} = {5}";
break;
case DatabaseType.Oracle:
case DatabaseType.MySql:
sql = @"
/*Records which appear in the archive*/
SELECT
{6},
{7}
FROM
{1}
Join
{2} Archive on " + whereStatement.Replace(archiveTableName, "Archive") + @"
AND
Archive.hic_validFrom = (select max(" + SpecialFieldNames.ValidFrom + @") from {2} s where " + whereStatement.Replace(archiveTableName, "Archive").Replace(tableName, "s") + @")
where
{1}.{4} = {5}
";
sql += sytnaxHelper.HowDoWeAchieveTopX(_batchSize).SQL;
break;
default:
throw new ArgumentOutOfRangeException();
}
sql = string.Format(sql,
_batchSize, //{0}
tableName, //{1}
archiveTableName, //{2}
whereStatement, //{3}
SpecialFieldNames.DataLoadRunID, //{4}
_dataLoadRunID, //{5}
GetSharedColumnsSQL(tableName), //{6}
GetSharedColumnsSQLWithColumnAliasPrefix("Archive", "zzArchivezz") //{7}
);
DataTable dtComboTable = new DataTable();
FillTableWithQueryIfUserConsents(dtComboTable, sql, checkNotifier, server);
Updates_New = new DataTable();
Updates_Replaced = new DataTable();
//add the columns from the combo table to both views
foreach (DataColumn col in dtComboTable.Columns)
{
if (!col.ColumnName.StartsWith("zzArchivezz", StringComparison.InvariantCultureIgnoreCase))
{
Updates_New.Columns.Add(col.ColumnName, col.DataType);
Updates_Replaced.Columns.Add(col.ColumnName, col.DataType);
}
}
foreach (DataRow fromRow in dtComboTable.Rows)
{
var newRow = Updates_New.Rows.Add();
var replacedRow = Updates_Replaced.Rows.Add();
foreach (DataColumn column in dtComboTable.Columns)
{
if (column.ColumnName.StartsWith("zzArchivezz", StringComparison.InvariantCultureIgnoreCase))
{
replacedRow[column.ColumnName.Substring("zzArchivezz".Length)] = fromRow[column];
}
else
{
newRow[column.ColumnName] = fromRow[column];
}
}
}
}