/// <summary>
/// Deletes the location of the RSA private key file from the platform database (does not delete the physical
/// file).
/// </summary>
public void DeleteKey()
{
ClearAllInjections();
string existingKey = GetKeyFileLocation();
if (existingKey == null)
{
throw new NotSupportedException("Cannot delete key because there is no key file configured");
}
using (var con = _catalogueRepository.GetConnection())
{
//Table can only ever have 1 record
using (DbCommand cmd = DatabaseCommandHelper.GetCommand("DELETE FROM PasswordEncryptionKeyLocation",
con.Connection, con.Transaction))
{
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows != 1)
{
throw new Exception("Delete from PasswordEncryptionKeyLocation resulted in " + affectedRows + ", expected 1");
}
}
}
ClearAllInjections();
}
public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration, AggregateOperation operation, out string sql)
{
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")},
{syntax.EnsureWrapped("SqlExecuted")} 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();
sql = r["SqlExecuted"] as string;
return(_database.ExpectTable(tableName));
}
}
}
sql = null;
return(null);
}
public void Commit(Evaluation evaluation, string pivotCategory)
{
using (var con = evaluation.DQERepository.GetConnection())
{
if (IsCommitted)
{
throw new NotSupportedException("PeriodicityState was already committed");
}
string sql =
string.Format(
"INSERT INTO [dbo].[PeriodicityState]([Evaluation_ID],[Year],[Month],[CountOfRecords],[RowEvaluation],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5})"
, evaluation.ID
, Year
, Month
, CountOfRecords
, "@RowEvaluation",
"@PivotCategory");
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@RowEvaluation", cmd, RowEvaluation);
DatabaseCommandHelper.AddParameterWithValueToCommand("@PivotCategory", cmd, pivotCategory);
cmd.ExecuteNonQuery();
}
IsCommitted = true;
}
}
public IMapsDirectlyToDatabaseTable GetObjectByID(Type type, int id)
{
if (id == 0)
{
return(null);
}
string typename = type.Name;
using (var connection = GetConnection())
using (DbCommand selectCommand = DatabaseCommandHelper.GetCommand("SELECT * FROM " + typename + " WHERE ID=" + id, connection.Connection, connection.Transaction))
{
using (DbDataReader r = selectCommand.ExecuteReader())
{
if (!r.HasRows)
{
throw new KeyNotFoundException("Could not find " + typename + " with ID " + id);
}
r.Read();
var result = ConstructEntity(type, r);
return(result);
}
}
}
public IEnumerable <T> SelectAll <T>(string selectQuery, string columnWithObjectID = null) where T : IMapsDirectlyToDatabaseTable
{
if (columnWithObjectID == null)
{
columnWithObjectID = typeof(T).Name + "_ID";
}
using (var opener = GetConnection())
{
var idsToReturn = new List <int>();
using (var cmd = DatabaseCommandHelper.GetCommand(selectQuery, opener.Connection, opener.Transaction))
{
using (var r = cmd.ExecuteReader())
{
while (r.Read())
{
idsToReturn.Add(Convert.ToInt32(r[columnWithObjectID]));
}
}
}
if (!idsToReturn.Any())
{
return(Enumerable.Empty <T>());
}
return(GetAllObjects <T>("WHERE ID in (" + String.Join(",", idsToReturn) + ")"));
}
}
/// <inheritdoc/>
public IEnumerable <ICacheFetchFailure> FetchPage(int start, int batchSize)
{
List <int> toReturnIds = new List <int>();
using (var conn = ((CatalogueRepository)Repository).GetConnection())
{
using (var cmd =
DatabaseCommandHelper.GetCommand(@"SELECT ID FROM CacheFetchFailure
WHERE CacheProgress_ID = @CacheProgressID AND ResolvedOn IS NULL
ORDER BY FetchRequestStart
OFFSET " + start + @" ROWS
FETCH NEXT " + batchSize + @" ROWS ONLY", conn.Connection, conn.Transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@CacheProgressID", cmd, ID);
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
toReturnIds.Add(Convert.ToInt32(reader["ID"]));
}
}
}
return(Repository.GetAllObjectsInIDList <CacheFetchFailure>(toReturnIds));
}
/// <inheritdoc/>
public void DeleteFromDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject)
{
//do not log information about access credentials
if (!(oTableWrapperObject is IDataAccessCredentials))
{
_logger.Debug("Deleted," + oTableWrapperObject.GetType().Name + "," + oTableWrapperObject.ID + "," + oTableWrapperObject);
}
lock (_oLockUpdateCommands)
{
//if the repository has obscure dependencies
if (ObscureDependencyFinder != null)
{
ObscureDependencyFinder.ThrowIfDeleteDisallowed(oTableWrapperObject);//confirm that deleting the object is allowed by the dependencies
}
using (var con = GetConnection())
{
using (DbCommand cmd = DatabaseCommandHelper.GetCommand(
"DELETE FROM " + oTableWrapperObject.GetType().Name + " WHERE ID =@ID", con.Connection,
con.Transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@ID", cmd, oTableWrapperObject.ID);
cmd.ExecuteNonQuery();
}
//likewise if there are obscure depenedency handlers let them handle cascading this delete into the mists of their obscure functionality (e.g. deleting a Catalogue in CatalogueRepository would delete all Evaluations of that Catalogue in the DQE repository because they would then be orphans)
if (ObscureDependencyFinder != null)
{
ObscureDependencyFinder.HandleCascadeDeletesForDeletedObject(oTableWrapperObject);
}
}
}
}
public DataTableViewerUI(IDataAccessPoint source, string sql, string caption)
{
InitializeComponent();
try
{
using (DbConnection con = DataAccessPortal.GetInstance().ExpectServer(source, DataAccessContext.DataExport).GetConnection())
{
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con))
using (var da = DatabaseCommandHelper.GetDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
catch (Exception e)
{
ExceptionViewer.Show("Failed to connect to source " + source + " and execute SQL: " + Environment.NewLine + sql, e);
}
this.Text = caption;
}
public void Commit(Evaluation evaluation, string pivotCategory, DbConnection con, DbTransaction transaction)
{
if (IsCommitted)
{
throw new NotSupportedException("ColumnState was already committed");
}
var sql = string.Format(
"INSERT INTO [dbo].[ColumnState]([TargetProperty],[DataLoadRunID],[Evaluation_ID],[CountCorrect],[CountDBNull],[ItemValidatorXML],[CountMissing],[CountWrong],[CountInvalidatesRow],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5},{6},{7},{8},{9})",
"@TargetProperty",
DataLoadRunID
, evaluation.ID
, CountCorrect
, CountDBNull
, "@ItemValidatorXML"
, CountMissing
, CountWrong
, CountInvalidatesRow
, "@PivotCategory"
);
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@ItemValidatorXML", cmd, ItemValidatorXML);
DatabaseCommandHelper.AddParameterWithValueToCommand("@TargetProperty", cmd, TargetProperty);
DatabaseCommandHelper.AddParameterWithValueToCommand("@PivotCategory", cmd, pivotCategory);
cmd.ExecuteNonQuery();
}
IsCommitted = true;
}
public IHasFullyQualifiedNameToo GetLatestResultsTable(AggregateConfiguration configuration, AggregateOperation operation, string currentSql)
{
using (var con = _server.GetConnection())
{
con.Open();
var cmd = DatabaseCommandHelper.GetCommand("Select TableName,SqlExecuted from CachedAggregateConfigurationResults WHERE AggregateConfiguration_ID = " + configuration.ID + " AND Operation = '" + operation + "'", con);
var r = cmd.ExecuteReader();
if (r.Read())
{
if (IsMatchOnSqlExecuted(r, currentSql))
{
string tableName = r["TableName"].ToString();
return(_database.ExpectTable(tableName));
}
else
{
return(null); //this means that there was outdated SQL, we could show this to user at some point
}
}
}
return(null);
}
public RowState(Evaluation evaluation, int dataLoadRunID, int correct, int missing, int wrong, int invalid, string validatorXml, string pivotCategory, DbConnection con, DbTransaction transaction)
{
var sql = string.Format(
"INSERT INTO [dbo].[RowState]([Evaluation_ID],[Correct],[Missing],[Wrong],[Invalid],[DataLoadRunID],[ValidatorXML],[PivotCategory])VALUES({0},{1},{2},{3},{4},{5},@validatorXML,{6})",
evaluation.ID,
correct,
missing,
wrong,
invalid,
dataLoadRunID,
"@pivotCategory"
);
var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction);
DatabaseCommandHelper.AddParameterWithValueToCommand("@validatorXML", cmd, validatorXml);
DatabaseCommandHelper.AddParameterWithValueToCommand("@pivotCategory", cmd, pivotCategory);
cmd.ExecuteNonQuery();
Correct = correct;
Missing = missing;
Wrong = wrong;
Invalid = invalid;
ValidatorXML = validatorXml;
DataLoadRunID = dataLoadRunID;
}
/// <inheritdoc/>
public DataAccessCredentials GetCredentialsIfExistsFor(TableInfo tableInfo, DataAccessContext context)
{
int toReturn = -1;
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("SELECT DataAccessCredentials_ID,Context FROM DataAccessCredentials_TableInfo WHERE TableInfo_ID = @tid and (Context =@context OR Context=" + ((int)DataAccessContext.Any) + ") ", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@tid", cmd));
cmd.Parameters["@tid"].Value = tableInfo.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@context", cmd));
cmd.Parameters["@context"].Value = context;
var r = cmd.ExecuteReader();
//gets the first liscenced usage
if (r.Read())
{
//there is one
//get it by it's id
toReturn = Convert.ToInt32(r["DataAccessCredentials_ID"]);
//if the first record is liscenced for Any
if (Convert.ToInt32(r["Context"]) == ((int)DataAccessContext.Any))
{
//see if there is a more specific second record (e.g. DataLoad)
if (r.Read())
{
toReturn = Convert.ToInt32(r["DataAccessCredentials_ID"]);
}
}
}
}
return(toReturn != -1 ?_repository.GetObjectByID <DataAccessCredentials>(toReturn):null);
}
public static DataTable GetPeriodicityForDataTableForEvaluation(Evaluation evaluation, string pivotCategoryValue, bool pivot)
{
using (var con = evaluation.DQERepository.GetConnection())
{
string sql = "";
if (pivot)
{
sql = string.Format(PeriodicityPivotSql, evaluation.ID, pivotCategoryValue);
}
else
{
sql = @"Select [Evaluation_ID]
,CAST([Year] as varchar(4)) + '-' + datename(month,dateadd(month, [Month] - 1, 0)) as YearMonth
,[CountOfRecords]
,[RowEvaluation] from PeriodicityState where Evaluation_ID=" + evaluation.ID + " AND PivotCategory = '" + pivotCategoryValue + "'";
}
var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
}
/// <inheritdoc/>
public IExternalDatabaseServer GetDefaultFor(PermissableDefaults field)
{
if (field == PermissableDefaults.None)
{
return(null);
}
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("SELECT ExternalDatabaseServer_ID FROM ServerDefaults WHERE DefaultType = @type", con.Connection, con.Transaction);
var p = cmd.CreateParameter();
p.ParameterName = "@type";
p.Value = StringExpansionDictionary[field];
cmd.Parameters.Add(p);
var executeScalar = cmd.ExecuteScalar();
if (executeScalar == DBNull.Value)
{
return(null);
}
return(_repository.GetObjectByID <ExternalDatabaseServer>(Convert.ToInt32(executeScalar)));
}
}
/// <summary>
/// Changes the location of the RSA private key file to a physical location on disk (which must exist)
/// </summary>
/// <param name="newLocation"></param>
public void ChangeLocation(string newLocation)
{
ClearAllInjections();
if (!File.Exists(newLocation))
{
throw new FileNotFoundException("Could not find key file at:" + newLocation);
}
//confirms that it is accessible and deserializable
DeserializeFromLocation(newLocation);
using (var con = _catalogueRepository.GetConnection())
{
//Table can only ever have 1 record
using (DbCommand cmd = DatabaseCommandHelper.GetCommand(
@"if exists (select 1 from PasswordEncryptionKeyLocation)
UPDATE PasswordEncryptionKeyLocation SET Path = @Path
else
INSERT INTO PasswordEncryptionKeyLocation(Path,Lock) VALUES (@Path,'X')
", con.Connection, con.Transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@Path", cmd, newLocation);
cmd.ExecuteNonQuery();
}
}
ClearAllInjections();
}
/// <inheritdoc/>
public Dictionary <DataAccessContext, List <TableInfo> > GetAllTablesUsingCredentials(DataAccessCredentials credentials)
{
Dictionary <DataAccessContext, List <int> > toReturn = new Dictionary <DataAccessContext, List <int> >();
toReturn.Add(DataAccessContext.Any, new List <int>());
toReturn.Add(DataAccessContext.DataExport, new List <int>());
toReturn.Add(DataAccessContext.DataLoad, new List <int>());
toReturn.Add(DataAccessContext.InternalDataProcessing, new List <int>());
toReturn.Add(DataAccessContext.Logging, new List <int>());
using (var con = _repository.GetConnection())
{
using (var cmd = DatabaseCommandHelper.GetCommand(
"SELECT TableInfo_ID,Context FROM DataAccessCredentials_TableInfo WHERE DataAccessCredentials_ID = @cid",
con.Connection, con.Transaction))
{
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
using (var r = cmd.ExecuteReader())
while (r.Read())
{
//get the context
DataAccessContext context = GetContext(r);
//add the TableInfo under that context
toReturn[context].Add((int)r["TableInfo_ID"]);
}
}
}
return(toReturn.ToDictionary(k => k.Key, v => _repository.GetAllObjectsInIDList <TableInfo>(v.Value).ToList()));
}
public void SetUp(DiscoveredServer server)
{
_server = server;
var databaseToLoadName = "HICPipelineTests";
// Create the databases
server.ExpectDatabase(databaseToLoadName).Create(true);
server.ChangeDatabase(databaseToLoadName);
// Create the dataset table
DatabaseToLoad = server.ExpectDatabase(databaseToLoadName);
using (var con = DatabaseToLoad.Server.GetConnection())
{
con.Open();
const string createDatasetTableQuery =
"CREATE TABLE TestData ([Col1] [int], [hic_dataLoadRunID] [int] NULL, [hic_validFrom] [datetime] NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([Col1] ASC))";
const string addValidFromDefault =
"ALTER TABLE TestData ADD CONSTRAINT [DF_TestData__hic_validFrom] DEFAULT (getdate()) FOR [hic_validFrom]";
using (var cmd = DatabaseCommandHelper.GetCommand(createDatasetTableQuery, con))
cmd.ExecuteNonQuery();
using (var cmd = DatabaseCommandHelper.GetCommand(addValidFromDefault, con))
cmd.ExecuteNonQuery();
}
// Ensure the dataset table has been created
var datasetTable = DatabaseToLoad.ExpectTable("TestData");
Assert.IsTrue(datasetTable.Exists());
}
public void BasicCase()
{
var ex = Assert.Throws <ArgumentException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", new DataTable(), 30)));
Assert.IsTrue(ex.Message.StartsWith("The DataTable that you claimed was an ExtractableAggregateResults had zero columns and therefore cannot be cached"));
DataTable dt = new DataTable();
dt.Columns.Add("Col1");
dt.Rows.Add("fishy!");
var ex2 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex2.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests is not marked as IsExtractable therefore cannot be cached"));
_config.IsExtractable = true;
_config.SaveToDatabase();
//make the underlying column an is extraction identifier
_extractionInformation.IsExtractionIdentifier = true;
_extractionInformation.SaveToDatabase();
AggregateDimension dim = new AggregateDimension(CatalogueRepository, _extractionInformation, _config);
_config.ClearAllInjections();
var ex3 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex3.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests contains dimensions marked as IsExtractionIdentifier or HashOnDataRelease (Col1)"));
_extractionInformation.IsExtractionIdentifier = false;
_extractionInformation.SaveToDatabase();
_config.ClearAllInjections();
Assert.DoesNotThrow(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
dim.DeleteInDatabase();
using (var con = DataAccessPortal.GetInstance().ExpectServer(QueryCachingDatabaseServer, DataAccessContext.InternalDataProcessing).GetConnection())
{
IHasFullyQualifiedNameToo table = _manager.GetLatestResultsTableUnsafe(_config, AggregateOperation.ExtractableAggregateResults);
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand("Select * from " + table.GetFullyQualifiedName(), con))
using (var r = cmd.ExecuteReader())
{
Assert.IsTrue(r.Read());
Assert.AreEqual("fishy!", r["Col1"]);
}
}
}
/// <inheritdoc/>
public void DeleteFromDatabase(IMapsDirectlyToDatabaseTable oTableWrapperObject)
{
_logger.Debug("Deleted," + oTableWrapperObject.GetType().Name + "," + oTableWrapperObject.ID + "," + oTableWrapperObject);
lock (_oLockUpdateCommands)
{
//if the repository has obscure dependencies
if (ObscureDependencyFinder != null)
{
ObscureDependencyFinder.ThrowIfDeleteDisallowed(oTableWrapperObject);//confirm that deleting the object is allowed by the dependencies
}
using (var con = GetConnection())
{
DbCommand cmd = DatabaseCommandHelper.GetCommand("DELETE FROM " + oTableWrapperObject.GetType().Name + " WHERE ID =@ID", con.Connection, con.Transaction);
DatabaseCommandHelper.AddParameterWithValueToCommand("@ID", cmd, oTableWrapperObject.ID);
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows != 1)
{
throw new Exception("Attempted to delete object of type " + oTableWrapperObject.GetType().Name + " from table " + oTableWrapperObject.GetType().Name + " with ID " + oTableWrapperObject.ID +
" but the DELETE command resulted in " + affectedRows + " affected rows");
}
//likewise if there are obscure depenedency handlers let them handle cascading this delete into the mists of their obscure functionality (e.g. deleting a Catalogue in CatalogueRepository would delete all Evaluations of that Catalogue in the DQE repository because they would then be orphans)
if (ObscureDependencyFinder != null)
{
ObscureDependencyFinder.HandleCascadeDeletesForDeletedObject(oTableWrapperObject);
}
}
}
}
/// <summary>
/// Returns the name of the query cache results table for <paramref name="configuration"/> if the <paramref name="currentSql"/> matches
/// the SQL run when the cache result was generated. Returns null if no cache result is found or there are changes in the <paramref name="currentSql"/>
/// since the cache result was generated.
/// </summary>
/// <param name="configuration"></param>
/// <param name="operation"></param>
/// <param name="currentSql"></param>
/// <returns></returns>
public IHasFullyQualifiedNameToo GetLatestResultsTable(AggregateConfiguration configuration, AggregateOperation operation, string currentSql)
{
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")},
{syntax.EnsureWrapped("SqlExecuted")}
from {mgrTable.GetFullyQualifiedName()}
WHERE
{syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID} AND
{syntax.EnsureWrapped("Operation")} = '{operation}'", con))
{
using (var r = cmd.ExecuteReader())
if (r.Read())
{
if (IsMatchOnSqlExecuted(r, currentSql))
{
string tableName = r["TableName"].ToString();
return(_database.ExpectTable(tableName));
}
return(null); //this means that there was outdated SQL, we could show this to user at some point
}
}
}
return(null);
}
protected override void TryExtractSupportingSQLTableImpl(SupportingSQLTable sqlTable, DirectoryInfo directory, IExtractionConfiguration configuration, IDataLoadEventListener listener, out int linesWritten,
out string destinationDescription)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to download SQL for global SupportingSQL " + sqlTable.SQL));
using (var con = sqlTable.GetServer().GetConnection())
{
con.Open();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Connection opened successfully, about to send SQL command " + sqlTable.SQL));
var cmd = DatabaseCommandHelper.GetCommand(sqlTable.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().GetSensibleEntityNameFromString(sqlTable.Name));
linesWritten = dt.Rows.Count;
var destinationDb = GetDestinationDatabase(listener);
var tbl = destinationDb.ExpectTable(dt.TableName);
if (tbl.Exists())
{
tbl.Drop();
}
destinationDb.CreateTable(dt.TableName, dt);
destinationDescription = TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName;
}
}
/// <summary>
/// Deletes any cache entries for <paramref name="configuration"/> in its role as <paramref name="operation"/>
/// </summary>
/// <param name="configuration"></param>
/// <param name="operation"></param>
/// <returns>True if a cache entry was found and deleted otherwise false</returns>
/// <exception cref="Exception"></exception>
public bool DeleteCacheEntryIfAny(AggregateConfiguration configuration, AggregateOperation operation)
{
var table = GetLatestResultsTableUnsafe(configuration, operation);
var mgrTable = _database.ExpectTable(ResultsManagerTable);
if (table != null)
{
using (var con = _server.GetConnection())
{
con.Open();
//drop the data
_database.ExpectTable(table.GetRuntimeName()).Drop();
//delete the record!
using (var cmd = DatabaseCommandHelper.GetCommand(
$"DELETE FROM {mgrTable.GetFullyQualifiedName()} WHERE AggregateConfiguration_ID = " +
configuration.ID + " AND Operation = '" + operation + "'", con))
{
int deletedRows = cmd.ExecuteNonQuery();
if (deletedRows != 1)
{
throw new Exception("Expected exactly 1 record in CachedAggregateConfigurationResults to be deleted when erasing its record of operation " + operation + " but there were " + deletedRows + " affected records");
}
}
return(true);
}
}
return(false);
}
public T[] GetAllObjects <T>(string whereSQL) where T : IMapsDirectlyToDatabaseTable
{
string typename = typeof(T).Name;
//if there is whereSQL make sure it is a legit SQL where
if (!string.IsNullOrWhiteSpace(whereSQL))
{
if (!whereSQL.Trim().ToUpper().StartsWith("WHERE"))
{
throw new ArgumentException("whereSQL did not start with the word 'WHERE', it was:" + whereSQL);
}
}
List <T> toReturn = new List <T>();
using (var opener = GetConnection())
{
DbCommand selectCommand = DatabaseCommandHelper.GetCommand("SELECT * FROM " + typename, opener.Connection, opener.Transaction);
if (whereSQL != null)
{
selectCommand.CommandText += " " + whereSQL;
}
using (DbDataReader r = selectCommand.ExecuteReader())
while (r.Read())
{
toReturn.Add(ConstructEntity <T>(r));
}
}
var result = toReturn.ToArray();
return(result);
}
public void MigrationOfOldPasswordsTest()
{
//cleanup
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(c => c.Name.Equals("frankieFran")))
{
c.DeleteInDatabase();
}
//create a new credentials
DataAccessCredentials creds = new DataAccessCredentials(CatalogueRepository, "frankieFran");
try
{
//update the database to an unencrypted password (like would be the case before software patch)
using (var con = CatalogueRepository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("UPDATE DataAccessCredentials set Password = '******' where Name='frankieFran'", con.Connection, con.Transaction);
Assert.AreEqual(1, cmd.ExecuteNonQuery());
}
DataAccessCredentials newCopy = CatalogueRepository.GetObjectByID <DataAccessCredentials>(creds.ID);
Assert.AreEqual("fish", newCopy.GetDecryptedPassword());
Assert.AreNotEqual("fish", newCopy.Password);
}
finally
{
creds.DeleteInDatabase();
}
}
/// <inheritdoc/>
public void CreateLinkBetween(AggregateConfiguration configuration, TableInfo tableInfo)
{
using (var con = _repository.GetConnection())
DatabaseCommandHelper.GetCommand(
string.Format(
"INSERT INTO AggregateForcedJoin (AggregateConfiguration_ID,TableInfo_ID) VALUES ({0},{1})",
configuration.ID, tableInfo.ID), con.Connection, con.Transaction).ExecuteNonQuery();
}
public void CommitResults(CacheCommitArguments arguments)
{
var configuration = arguments.Configuration;
var operation = arguments.Operation;
DeleteCacheEntryIfAny(configuration, operation);
//Do not change Types of source columns unless there is an explicit override
arguments.Results.SetDoNotReType(true);
using (var con = _server.GetConnection())
{
con.Open();
string nameWeWillGiveTableInCache = operation + "_AggregateConfiguration" + configuration.ID;
//either it has no name or it already has name we want so its ok
arguments.Results.TableName = nameWeWillGiveTableInCache;
//add explicit types
var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);
if (tbl.Exists())
{
tbl.Drop();
}
tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);
if (!tbl.Exists())
{
throw new Exception("Cache table did not exist even after CreateTable completed without error!");
}
var cmdCreateNew =
DatabaseCommandHelper.GetCommand(
"INSERT INTO CachedAggregateConfigurationResults (Committer,AggregateConfiguration_ID,SqlExecuted,Operation,TableName) Values (@Committer,@AggregateConfiguration_ID,@SqlExecuted,@Operation,@TableName)", con);
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Committer", cmdCreateNew));
cmdCreateNew.Parameters["@Committer"].Value = Environment.UserName;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@AggregateConfiguration_ID", cmdCreateNew));
cmdCreateNew.Parameters["@AggregateConfiguration_ID"].Value = configuration.ID;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@SqlExecuted", cmdCreateNew));
cmdCreateNew.Parameters["@SqlExecuted"].Value = arguments.SQL.Trim();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Operation", cmdCreateNew));
cmdCreateNew.Parameters["@Operation"].Value = operation.ToString();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@TableName", cmdCreateNew));
cmdCreateNew.Parameters["@TableName"].Value = tbl.GetRuntimeName();
cmdCreateNew.ExecuteNonQuery();
arguments.CommitTableDataCompleted(tbl);
}
}
public bool HasEvaluations(Catalogue catalogue)
{
using (var con = GetConnection())
{
//get all the row level data 1 to 1 join with evaluation
using (var cmdGetEvaluations = DatabaseCommandHelper.GetCommand("select count(*) from Evaluation where CatalogueID = " + catalogue.ID, con.Connection, con.Transaction))
return(Convert.ToInt32(cmdGetEvaluations.ExecuteScalar()) > 0);
}
}
private string GetKeyFileLocationImpl()
{
using (var con = _catalogueRepository.DiscoveredServer.GetConnection())
{
con.Open();
//Table can only ever have 1 record
using (DbCommand cmd = DatabaseCommandHelper.GetCommand("SELECT Path from PasswordEncryptionKeyLocation", con))
return(cmd.ExecuteScalar() as string);
}
}
public IEnumerable <Evaluation> GetEvaluationsWhere(string whereSQL)
{
List <Evaluation> toReturn = new List <Evaluation>();
using (var con = GetConnection())
{
//get all the row level data 1 to 1 join with evaluation
using (var cmdGetEvaluations = DatabaseCommandHelper.GetCommand("select * from Evaluation " + whereSQL, con.Connection, con.Transaction))
using (DbDataReader r = cmdGetEvaluations.ExecuteReader())
{
while (r.Read())
{
Evaluation toAdd = new Evaluation(this, r);
toReturn.Add(toAdd);
}
}
//use a separate command to read the children to prevent multiple active results sets problems
foreach (Evaluation evaluation in toReturn)
{
List <RowState> states = new List <RowState>();
//get all the row level data
using (var cmdGetRowStates = DatabaseCommandHelper.GetCommand(
"select * from RowState WHERE Evaluation_ID =" + evaluation.ID, con.Connection,
con.Transaction))
{
using (var r2 = cmdGetRowStates.ExecuteReader())
while (r2.Read())
{
states.Add(new RowState(r2));
}
}
evaluation.RowStates = states.ToArray();
//get all the column level data
using (var cmdGetColumnStates = DatabaseCommandHelper.GetCommand("select * from ColumnState WHERE ColumnState.Evaluation_ID =" + evaluation.ID, con.Connection, con.Transaction))
using (var r2 = cmdGetColumnStates.ExecuteReader())
{
List <ColumnState> columnStates = new List <ColumnState>();
while (r2.Read())
{
columnStates.Add(new ColumnState(r2));
}
evaluation.ColumnStates = columnStates.ToArray();
r2.Close();
}
}
}
return(toReturn);
}
public DbCommand PrepareCommand(string sql, Dictionary <string, object> parameters, DbConnection con, DbTransaction transaction = null)
{
var cmd = DatabaseCommandHelper.GetCommand(sql, con, transaction);
if (parameters == null)
{
return(cmd);
}
return(PrepareCommand(cmd, parameters));
}