/// <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); } }