public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration, AggregateOperation operation)
{
var syntax = _database.Server.GetQuerySyntaxHelper();
var mgrTable = _database.ExpectTable(ResultsManagerTable);
using (var con = _server.GetConnection())
{
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand(
$@"Select {syntax.EnsureWrapped("TableName")} from {mgrTable.GetFullyQualifiedName()}
WHERE {syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID}
AND {syntax.EnsureWrapped("Operation")} = '{operation}'", con))
{
using (var r = cmd.ExecuteReader())
if (r.Read())
{
string tableName = r["TableName"].ToString();
return(_database.ExpectTable(tableName));
}
}
}
return(null);
}
private void RefreshCache()
{
var repo = (TableRepository)_repository;
using (var con = repo.GetConnection())
{
using (DbCommand cmd = DatabaseCommandHelper.GetCommand("SELECT * from [ConfigurationProperties]",
con.Connection, con.Transaction))
{
using (var reader = cmd.ExecuteReader())
{
_cacheDictionary.Clear();
//get cache of all answers
while (reader.Read())
{
_cacheDictionary.Add(reader["Property"].ToString(), reader["Value"] as string);
}
}
}
}
_cacheOutOfDate = false;
}
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");
}
}
/// <summary>
/// Creates a new private RSA encryption key certificate at the given location and sets the catalogue repository to use it for encrypting passwords.
/// This will make any existing serialized passwords iretrievable unless you restore and reset the original key file location.
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public FileInfo CreateNewKeyFile(string path)
{
string existingKey = GetKeyFileLocation();
if (existingKey != null)
{
throw new NotSupportedException("There is already a key file at location:" + existingKey);
}
RSACryptoServiceProvider provider = new RSACryptoServiceProvider(4096);
RSAParameters p = provider.ExportParameters(true);
using (var stream = File.Create(path))
{
XmlSerializer SerializeXml = new XmlSerializer(typeof(RSAParameters));
SerializeXml.Serialize(stream, p);
stream.Flush();
stream.Close();
}
var fileInfo = new FileInfo(path);
if (!fileInfo.Exists)
{
throw new Exception("Created file but somehow it didn't exist!?!");
}
using (var con = _catalogueRepository.GetConnection())
{
DbCommand cmd = DatabaseCommandHelper.GetCommand("INSERT INTO PasswordEncryptionKeyLocation(Path,Lock) VALUES (@Path,'X')", con.Connection, con.Transaction);
DatabaseCommandHelper.AddParameterWithValueToCommand("@Path", cmd, fileInfo.FullName);
cmd.ExecuteNonQuery();
}
return(fileInfo);
}
public void Check(ICheckNotifier checker)
{
if (OtherColumnInfo == null)
{
checker.OnCheckPerformed(
new CheckEventArgs("No ColumnInfo has been selected yet! unable to populate constraint HashSet",
CheckResult.Fail));
return;
}
// Attempt to get a single value from the table and then validate it
var tableInfo = OtherColumnInfo.TableInfo;
object itemToValidate;
using (var con = GetConnectionToOtherTable(tableInfo))
{
con.Open();
try
{
var cmd = DatabaseCommandHelper.GetCommand("SELECT TOP 1 " + OtherColumnInfo + " FROM " + tableInfo + " WHERE " + OtherColumnInfo + " IS NOT NULL", con);
cmd.CommandTimeout = 5;
itemToValidate = cmd.ExecuteScalar();
}
catch (Exception e)
{
if (e.HResult == -2) // timeout
{
checker.OnCheckPerformed(
new CheckEventArgs("Timeout when trying to access lookup table: " + tableInfo, CheckResult.Warning, e));
}
else
{
checker.OnCheckPerformed(
new CheckEventArgs("Failed to query validation lookup table: " + tableInfo, CheckResult.Fail, e));
}
return;
}
}
if (itemToValidate == null)
{
checker.OnCheckPerformed(new CheckEventArgs("No validation items were found in " + OtherColumnInfo, CheckResult.Fail));
return;
}
// Now attempt to validate the item just retrieved from the lookup table. By definition it should be valid.
ValidationFailure failure;
// If it is an exclusion list then pass a guid which shouldn't match any of the items in the lookup column
// Call to Validate may cause performance issues as it loads the entire column contents which for, e.g. CHI, can take a long time
if (InvertLogic)
{
failure = Validate(Guid.NewGuid().ToString(), null, null);
}
else
{
failure = Validate(itemToValidate, null, null);
}
checker.OnCheckPerformed(failure == null
? new CheckEventArgs("Test Read Code validation successful", CheckResult.Success)
: new CheckEventArgs(failure.Message, CheckResult.Fail));
}
/// <summary>
/// Checks that the Catalogue has a sensible Name (See <see cref="IsAcceptableName(string)"/>). Then checks that there are no missing ColumnInfos
/// </summary>
/// <param name="notifier"></param>
public void Check(ICheckNotifier notifier)
{
string reason;
if (!IsAcceptableName(Name, out reason))
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Catalogue name " + Name + " (ID=" + ID + ") does not follow naming conventions reason:" + reason,
CheckResult.Fail));
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Catalogue name " + Name + " follows naming conventions ", CheckResult.Success));
}
ITableInfo[] tables = GetTableInfoList(true);
foreach (TableInfo t in tables)
{
t.Check(notifier);
}
ExtractionInformation[] extractionInformations = this.GetAllExtractionInformation(ExtractionCategory.Core);
if (extractionInformations.Any())
{
bool missingColumnInfos = false;
foreach (ExtractionInformation missingColumnInfo in extractionInformations.Where(e => e.ColumnInfo == null))
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"ColumnInfo behind ExtractionInformation/CatalogueItem " +
missingColumnInfo.GetRuntimeName() + " is MISSING, it must have been deleted",
CheckResult.Fail));
missingColumnInfos = true;
}
if (missingColumnInfos)
{
return;
}
notifier.OnCheckPerformed(
new CheckEventArgs(
"Found " + extractionInformations.Length +
" ExtractionInformation(s), preparing to validate SQL with QueryBuilder", CheckResult.Success));
var accessContext = DataAccessContext.InternalDataProcessing;
try
{
var server = DataAccessPortal.GetInstance().ExpectDistinctServer(tables, accessContext, false);
using (var con = server.GetConnection())
{
con.Open();
string sql;
try
{
QueryBuilder qb = new QueryBuilder(null, null);
qb.TopX = 1;
qb.AddColumnRange(extractionInformations);
sql = qb.SQL;
notifier.OnCheckPerformed(new CheckEventArgs("Query Builder assembled the following SQL:" + Environment.NewLine + sql, CheckResult.Success));
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs("Could not generate extraction SQL for Catalogue " + this,
CheckResult.Fail, e));
return;
}
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con))
{
cmd.CommandTimeout = 10;
using (DbDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
notifier.OnCheckPerformed(new CheckEventArgs("successfully read a row of data from the extraction SQL of Catalogue " + this, CheckResult.Success));
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("The query produced an empty result set for Catalogue" + this, CheckResult.Warning));
}
}
}
con.Close();
}
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Extraction SQL Checking failed for Catalogue " + this +
" make sure that you can access the underlying server under DataAccessContext." +
accessContext +
" and that the SQL generated runs correctly (see internal exception for details)",
CheckResult.Fail, e));
}
}
//supporting documents
var f = new SupportingDocumentsFetcher(this);
f.Check(notifier);
}
private void AddColumnToDump(PreLoadDiscardedColumn column, DbConnection con)
{
using (var cmdAlter = DatabaseCommandHelper.GetCommand("Alter table " + _parent.GetRuntimeName() + " ADD " + column.RuntimeColumnName + " " + column.SqlDataType, con))
cmdAlter.ExecuteNonQuery();
}
public DataTable GetChunk(IDataLoadEventListener job, GracefulCancellationToken cancellationToken)
{
if (_reader == null)
{
_con = DatabaseCommandHelper.GetConnection(_builder);
_con.Open();
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Running SQL:" + Environment.NewLine + Sql));
cmd = DatabaseCommandHelper.GetCommand(Sql, _con);
cmd.CommandTimeout = _timeout;
CommandAdjuster?.Invoke(cmd);
_reader = cmd.ExecuteReaderAsync(cancellationToken.AbortToken).Result;
_numberOfColumns = _reader.FieldCount;
}
int readThisBatch = 0;
timer.Start();
try
{
DataTable chunk = GetChunkSchema(_reader);
while (_reader.Read())
{
AddRowToDataTable(chunk, _reader);
readThisBatch++;
//we reached batch limit
if (readThisBatch == BatchSize)
{
return(chunk);
}
}
//if data was read
if (readThisBatch > 0)
{
return(chunk);
}
//data is exhausted
//if data was exhausted on first read and we are allowing empty result sets
if (firstChunk && AllowEmptyResultSets)
{
return(chunk);//return the empty chunk
}
//data exhausted
return(null);
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Source read failed", e));
throw;
}
finally
{
firstChunk = false;
timer.Stop();
job.OnProgress(this, new ProgressEventArgs(_taskBeingPerformed, new ProgressMeasurement(TotalRowsRead, ProgressType.Records), timer.Elapsed));
}
}
public static Dictionary <DateTime, ArchivalPeriodicityCount> GetPeriodicityCountsForEvaluation(Evaluation evaluation, bool discardOutliers)
{
var toReturn = new Dictionary <DateTime, ArchivalPeriodicityCount>();
var calc = new DatasetTimespanCalculator();
var result = calc.GetMachineReadableTimespanIfKnownOf(evaluation, discardOutliers);
using (var con = evaluation.DQERepository.GetConnection())
{
var sql =
@"SELECT
[Year]
,[Month]
,RowEvaluation
,CountOfRecords
FROM [PeriodicityState]
where
Evaluation_ID = " + evaluation.ID + " and PivotCategory = 'ALL' ORDER BY [Year],[Month]";
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction))
{
using (var r = cmd.ExecuteReader())
{
while (r.Read())
{
var date = new DateTime((int)r["Year"], (int)r["Month"], 1);
//discard outliers before start
if (discardOutliers && result.Item1.HasValue && date < result.Item1.Value)
{
continue;
}
//discard outliers after end
if (discardOutliers && result.Item2.HasValue && date > result.Item2.Value)
{
continue;
}
if (!toReturn.ContainsKey(date))
{
toReturn.Add(date, new ArchivalPeriodicityCount());
}
var toIncrement = toReturn[date];
/*
*
* Correct
* InvalidatesRow
* Missing
* Wrong
* */
switch ((string)r["RowEvaluation"])
{
case "Correct":
toIncrement.CountGood += (int)r["CountOfRecords"];
toIncrement.Total += (int)r["CountOfRecords"];
break;
case "InvalidatesRow": toIncrement.Total += (int)r["CountOfRecords"];
break;
case "Missing": toIncrement.Total += (int)r["CountOfRecords"];
break;
case "Wrong": toIncrement.Total += (int)r["CountOfRecords"];
break;
default: throw new ArgumentOutOfRangeException("Unexpected RowEvaluation '" + r["RowEvaluation"] + "'");
}
}
}
}
}
return(toReturn);
}
private void databaseTypeUI1_DatabaseTypeChanged(object sender, EventArgs e)
{
_helper = DatabaseCommandHelper.For(DatabaseType);
UpdateDatabaseList();
}
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
using (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
{
using (DataTable dt = new DataTable())
{
//get the existing data
using (DbCommand cmdGetExistingData =
DatabaseCommandHelper.GetCommand(
string.Format("SELECT {0},{1} from {2}", from, to, tbl.GetFullyQualifiedName()), con))
{
using (DbDataAdapter da = DatabaseCommandHelper.GetDataAdapter(cmdGetExistingData))
{
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
using (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
using (DbCommand dropMappingTable = DatabaseCommandHelper.GetCommand("DROP TABLE TempANOMap", con))
dropMappingTable.ExecuteNonQuery();
}
}
public static Dictionary <DateTime, ArchivalPeriodicityCount> GetPeriodicityCountsForEvaluation(Evaluation evaluation)
{
var toReturn = new Dictionary <DateTime, ArchivalPeriodicityCount>();
using (var con = evaluation.DQERepository.GetConnection())
{
var sql =
@"SELECT
[Year]
,[Month]
,RowEvaluation
,CountOfRecords
FROM [PeriodicityState]
where
Evaluation_ID = " + evaluation.ID + " and PivotCategory = 'ALL' ORDER BY [Year],[Month]";
var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction);
var r = cmd.ExecuteReader();
while (r.Read())
{
var date = new DateTime((int)r["Year"], (int)r["Month"], 1);
if (!toReturn.ContainsKey(date))
{
toReturn.Add(date, new ArchivalPeriodicityCount());
}
var toIncrement = toReturn[date];
/*
*
* Correct
* InvalidatesRow
* Missing
* Wrong
* */
switch ((string)r["RowEvaluation"])
{
case "Correct":
toIncrement.CountGood += (int)r["CountOfRecords"];
toIncrement.Total += (int)r["CountOfRecords"];
break;
case "InvalidatesRow": toIncrement.Total += (int)r["CountOfRecords"];
break;
case "Missing": toIncrement.Total += (int)r["CountOfRecords"];
break;
case "Wrong": toIncrement.Total += (int)r["CountOfRecords"];
break;
default: throw new ArgumentOutOfRangeException("Unexpected RowEvaluation '" + r["RowEvaluation"] + "'");
}
}
}
return(toReturn);
}
private ExtractCommandState ExtractSupportingSql(SupportingSQLTable sql, IDataLoadEventListener listener, DataLoadInfo dataLoadInfo)
{
try
{
var tempDestination = new DataTableUploadDestination();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to download SQL for global SupportingSQL " + sql.Name));
using (var con = sql.GetServer().GetConnection())
{
con.Open();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Connection opened successfully, about to send SQL command " + sql.SQL));
var cmd = DatabaseCommandHelper.GetCommand(sql.SQL, con);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
var sw = new Stopwatch();
sw.Start();
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = GetTableName(_destinationDatabase.Server.GetQuerySyntaxHelper().GetSensibleTableNameFromString(sql.Name));
var tableLoadInfo = dataLoadInfo.CreateTableLoadInfo("", dt.TableName, new[] { new DataSource(sql.SQL, DateTime.Now) }, -1);
tableLoadInfo.Inserts = dt.Rows.Count;
listener.OnProgress(this, new ProgressEventArgs("Reading from SupportingSQL " + sql.Name, new ProgressMeasurement(dt.Rows.Count, ProgressType.Records), sw.Elapsed));
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Decided on the following destination table name for SupportingSQL: " + dt.TableName));
tempDestination.AllowResizingColumnsAtUploadTime = true;
tempDestination.PreInitialize(GetDestinationDatabase(listener), listener);
tempDestination.ProcessPipelineData(dt, listener, new GracefulCancellationToken());
tempDestination.Dispose(listener, null);
//end auditing it
tableLoadInfo.CloseAndArchive();
if (_request is ExtractDatasetCommand)
{
var result = (_request as ExtractDatasetCommand).CumulativeExtractionResults;
var supplementalResult = result.AddSupplementalExtractionResult(sql.SQL, sql);
supplementalResult.CompleteAudit(this.GetType(), TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName, dt.Rows.Count);
}
else
{
var extractGlobalsCommand = (_request as ExtractGlobalsCommand);
Debug.Assert(extractGlobalsCommand != null, "extractGlobalsCommand != null");
var result =
new SupplementalExtractionResults(extractGlobalsCommand.RepositoryLocator.DataExportRepository,
extractGlobalsCommand.Configuration,
sql.SQL,
sql);
result.CompleteAudit(this.GetType(), TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName, dt.Rows.Count);
extractGlobalsCommand.ExtractionResults.Add(result);
}
}
}
catch (Exception e)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Extraction of SupportingSQL " + sql + " failed ", e));
return(ExtractCommandState.Crashed);
}
return(ExtractCommandState.Completed);
}
