/// <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>
/// 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)
{
ClearAllInjections();
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);
var fi = new FileInfo(path);
if (fi.Directory != null && !fi.Directory.Exists)
{
fi.Directory.Create();
}
using (var stream = fi.Create())
{
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())
{
using (DbCommand cmd = DatabaseCommandHelper.GetCommand(
"INSERT INTO PasswordEncryptionKeyLocation(Path,Lock) VALUES (@Path,'X')", con.Connection,
con.Transaction))
{
DatabaseCommandHelper.AddParameterWithValueToCommand("@Path", cmd, fileInfo.FullName);
cmd.ExecuteNonQuery();
}
}
ClearAllInjections();
return(fileInfo);
}
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();
}
}
public FilterManagerFromChildProvider(CatalogueRepository repository, ICoreChildProvider childProvider) : base(repository)
{
_containersToFilters =
childProvider.AllAggregateFilters.Where(f => f.FilterContainer_ID.HasValue)
.GroupBy(f => f.FilterContainer_ID.Value)
.ToDictionary(gdc => gdc.Key, gdc => gdc.ToList());
var server = repository.DiscoveredServer;
using (var con = repository.GetConnection())
{
var r = server.GetCommand("SELECT [AggregateFilterContainer_ParentID],[AggregateFilterContainer_ChildID] FROM [AggregateFilterSubContainer]", con).ExecuteReader();
while (r.Read())
{
var parentId = Convert.ToInt32(r["AggregateFilterContainer_ParentID"]);
var subcontainerId = Convert.ToInt32(r["AggregateFilterContainer_ChildID"]);
if (!_subcontainers.ContainsKey(parentId))
{
_subcontainers.Add(parentId, new List <AggregateFilterContainer>());
}
_subcontainers[parentId].Add(childProvider.AllAggregateContainersDictionary[subcontainerId]);
}
r.Close();
}
}
/// <inheritdoc/>
public void CreateLinkBetween(DataAccessCredentials credentials, TableInfo tableInfo, DataAccessContext context)
{
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("INSERT INTO DataAccessCredentials_TableInfo(DataAccessCredentials_ID,TableInfo_ID,Context) VALUES (@cid,@tid,@context)", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
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;
cmd.ExecuteNonQuery();
}
}
/// <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();
}
/// <summary>
/// Deletes all data tables except <see cref="ServerDefaults"/>, <see cref="ExternalDatabaseServer"/> and some other core tables which are required for access to
/// DQE, logging etc
/// </summary>
protected void BlitzMainDataTables()
{
using (var con = CatalogueRepository.GetConnection())
{
var catalogueDatabaseName = ((TableRepository)RepositoryLocator.CatalogueRepository).DiscoveredServer.GetCurrentDatabase().GetRuntimeName();
var dataExportDatabaseName = ((TableRepository)RepositoryLocator.DataExportRepository).DiscoveredServer.GetCurrentDatabase().GetRuntimeName();
UsefulStuff.ExecuteBatchNonQuery(string.Format(BlitzDatabasesLite, catalogueDatabaseName, dataExportDatabaseName), con.Connection);
}
}
public void FetchAllRelationships(ICoreChildProvider childProvider)
{
using (var con = CatalogueRepository.GetConnection())
{
//find all the cohort SET operation subcontainers e.g. UNION Ag1,Ag2,(Agg3 INTERSECT Agg4) would have 2 CohortAggregateContainers (the UNION and the INTERSECT) in which the INTERSECT was the child container of the UNION
var r = CatalogueRepository.DiscoveredServer.GetCommand("SELECT [CohortAggregateContainer_ParentID],[CohortAggregateContainer_ChildID] FROM [CohortAggregateSubContainer] ORDER BY CohortAggregateContainer_ParentID", con).ExecuteReader();
while (r.Read())
{
var currentParentId = Convert.ToInt32(r["CohortAggregateContainer_ParentID"]);
var currentChildId = Convert.ToInt32(r["CohortAggregateContainer_ChildID"]);
if (!_contents.ContainsKey(currentParentId))
{
_contents.Add(currentParentId, new List <IOrderable>());
}
_contents[currentParentId].Add(childProvider.AllCohortAggregateContainers.Single(c => c.ID == currentChildId));
}
r.Close();
//now find all the Agg configurations within the containers too, (in the above example we will find Agg1 in the UNION container at order 1 and Agg2 at order 2 and then we find Agg3 and Agg4 in the INTERSECT container)
r = CatalogueRepository.DiscoveredServer.GetCommand(@"SELECT [CohortAggregateContainer_ID], [AggregateConfiguration_ID],[Order] FROM [CohortAggregateContainer_AggregateConfiguration] ORDER BY CohortAggregateContainer_ID", con).ExecuteReader();
while (r.Read())
{
var currentParentId = Convert.ToInt32(r["CohortAggregateContainer_ID"]);
var currentChildId = Convert.ToInt32(r["AggregateConfiguration_ID"]);
var currentOrder = Convert.ToInt32(r["Order"]);
if (!_contents.ContainsKey(currentParentId))
{
_contents.Add(currentParentId, new List <IOrderable>());
}
AggregateConfiguration config;
try
{
config = childProvider.AllAggregateConfigurations.Single(a => a.ID == currentChildId);
}
catch (Exception)
{
throw new Exception("Error occured trying to find AggregateConfiguration with ID " + currentChildId + " which is allegedly a child of CohortAggregateContainer " + currentParentId);
}
config.SetKnownOrder(currentOrder);
_contents[currentParentId].Add(config);
}
}
}
public void DataAccessCredentialsEncryption()
{
//cleanup
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(c => c.Name.Equals("frankieFran")))
{
c.DeleteInDatabase();
}
DataAccessCredentials creds = new DataAccessCredentials(CatalogueRepository, "frankieFran");
try
{
//as soon as you set a password it should be encrypted by the credentials class in memory
creds.Password = "******";
Assert.AreNotEqual("fish", creds.Password);
Assert.AreEqual("fish", creds.GetDecryptedPassword());//but we should still be able to decrypt it
//save it
creds.SaveToDatabase();
using (var con = CatalogueRepository.GetConnection())
{
string value;
using (var cmd = DatabaseCommandHelper.GetCommand("Select Password from DataAccessCredentials where Name='frankieFran'", con.Connection, con.Transaction))
value = (string)cmd.ExecuteScalar();
//ensure password in database is encrypted
Assert.AreNotEqual("fish", value);
Assert.AreEqual(creds.Password, value);//does value in database match value in memory (encrypted)
}
//get a new copy out of the database
DataAccessCredentials newCopy = CatalogueRepository.GetObjectByID <DataAccessCredentials>(creds.ID);
Assert.AreEqual(creds.Password, newCopy.Password); //passwords should match
Assert.AreNotEqual("fish", creds.Password); //neither should be fish
Assert.AreNotEqual("fish", newCopy.Password);
//both should decrypt to the same value (fish
Assert.AreEqual("fish", creds.GetDecryptedPassword());
Assert.AreEqual("fish", newCopy.GetDecryptedPassword());
}
finally
{
creds.DeleteInDatabase();
}
}
public void GetBinaryText()
{
using (var con = CatalogueRepository.GetConnection())
{
DbCommand cmd = DatabaseCommandHelper.GetCommand(
"SELECT definition FROM sysdiagrams where name = 'Catalogue_Data_Diagram' ",
con.Connection, con.Transaction);
var reader = cmd.ExecuteReader();
//The system diagram exists
Assert.IsTrue(reader.Read());
var bytes = (byte[])reader[0];
var bytesAsString = ByteArrayToString(bytes);
Console.WriteLine(bytesAsString);
Assert.Greater(bytesAsString.Length, 100000);
}
}
public int?GetOrderIfExistsFor(AggregateConfiguration configuration)
{
if (configuration.Repository != this)
{
if (((CatalogueRepository)configuration.Repository).ConnectionString != CatalogueRepository.ConnectionString)
{
throw new NotSupportedException("AggregateConfiguration is from a different repository than this with a different connection string");
}
}
using (var con = CatalogueRepository.GetConnection())
{
DbCommand cmd = DatabaseCommandHelper.GetCommand("SELECT [Order] FROM CohortAggregateContainer_AggregateConfiguration WHERE AggregateConfiguration_ID = @AggregateConfiguration_ID", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@AggregateConfiguration_ID", cmd));
cmd.Parameters["@AggregateConfiguration_ID"].Value = configuration.ID;
return(CatalogueRepository.ObjectToNullableInt(cmd.ExecuteScalar()));
}
}
public void SimpleCaseSingleThreaded(bool useTransaction)
{
using (
var con = useTransaction
? CatalogueRepository.BeginNewTransactedConnection()
: CatalogueRepository.GetConnection())
{
Assert.AreEqual(ConnectionState.Open, con.Connection.State);
Thread.Sleep(1000);
if (useTransaction)
{
CatalogueRepository.EndTransactedConnection(false);
}
else
{
con.Connection.Close();
}
Assert.AreEqual(ConnectionState.Closed, con.Connection.State);
}
}
