/// <summary> /// Fetches and returns project number, version etc listed in the remote cohort database for this cohort /// </summary> public IExternalCohortDefinitionData GetExternalData() { var db = ExternalCohortTable.Discover(); var syntax = db.Server.GetQuerySyntaxHelper(); string sql = $@"Select {syntax.EnsureWrapped("projectNumber")}, {syntax.EnsureWrapped("description")}, {syntax.EnsureWrapped("version")}, {syntax.EnsureWrapped("dtCreated")} from {ExternalCohortTable.DefinitionTableName} where {syntax.EnsureWrapped("id")} = {OriginID}"; using (var con = db.Server.GetConnection()) { con.Open(); using (var getDescription = db.Server.GetCommand(sql, con)) { using (var r = getDescription.ExecuteReader()) { if (!r.Read()) { throw new Exception("No records returned for Cohort OriginID " + OriginID); } return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name)); } } } }
/// <summary> /// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping). /// </summary> /// <param name="externalSource"></param> /// <param name="displayMemberName"></param> /// <param name="valueMemberName"></param> /// <param name="versionMemberName"></param> /// <param name="projectNumberMemberName"></param> /// <returns></returns> public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName) { var server = externalSource.Discover().Server; var syntax = server.GetQuerySyntaxHelper(); using (var con = server.GetConnection()) { con.Open(); string sql = $@"Select {syntax.EnsureWrapped("description")}, {syntax.EnsureWrapped("id")}, {syntax.EnsureWrapped("version")}, {syntax.EnsureWrapped("projectNumber")} from {externalSource.DefinitionTableName} where exists (Select 1 from {externalSource.TableName} WHERE {externalSource.DefinitionTableForeignKeyField}=id)"; using (var da = server.GetDataAdapter(sql, con)) { displayMemberName = "description"; valueMemberName = "id"; versionMemberName = "version"; projectNumberMemberName = "projectNumber"; DataTable toReturn = new DataTable(); da.Fill(toReturn); return(toReturn); } } }
/// <summary> /// Returns the remote DataTable row held in <paramref name="externalSource"/> that describes all cohorts held in it (that have at least one identifier mapping). /// </summary> /// <param name="externalSource"></param> /// <param name="displayMemberName"></param> /// <param name="valueMemberName"></param> /// <param name="versionMemberName"></param> /// <param name="projectNumberMemberName"></param> /// <returns></returns> public static DataTable GetImportableCohortDefinitionsTable(ExternalCohortTable externalSource, out string displayMemberName, out string valueMemberName, out string versionMemberName, out string projectNumberMemberName) { var server = externalSource.Discover().Server; using (var con = server.GetConnection()) { con.Open(); string sql = string.Format( "Select description,id,version,projectNumber from {0} where exists (Select 1 from {1} WHERE {2}=id)" , externalSource.DefinitionTableName, externalSource.TableName, externalSource.DefinitionTableForeignKeyField); var da = server.GetDataAdapter(sql, con); displayMemberName = "description"; valueMemberName = "id"; versionMemberName = "version"; projectNumberMemberName = "projectNumber"; DataTable toReturn = new DataTable(); da.Fill(toReturn); return(toReturn); } }
/// <inheritdoc/> public string GetReleaseIdentifierDataType() { DiscoveredTable table = ExternalCohortTable.Discover().ExpectTable(ExternalCohortTable.TableName); //discover the column return(table.DiscoverColumn(GetReleaseIdentifier(true)) .DataType.SQLType); //and return it's datatype }
/// <inheritdoc/> public IQuerySyntaxHelper GetQuerySyntaxHelper() { if (_cachedQuerySyntaxHelper == null) { _cachedQuerySyntaxHelper = ExternalCohortTable.GetQuerySyntaxHelper(); } return(_cachedQuerySyntaxHelper); }
/// <summary> /// Returns details of all cohorts held in <paramref name="externalSource"/> (that have at least one identifier mapping). /// </summary> /// <param name="externalSource"></param> /// <returns></returns> public static IEnumerable <CohortDefinition> GetImportableCohortDefinitions(ExternalCohortTable externalSource) { string displayMemberName, valueMemberName, versionMemberName, projectNumberMemberName; DataTable dt = GetImportableCohortDefinitionsTable(externalSource, out displayMemberName, out valueMemberName, out versionMemberName, out projectNumberMemberName); foreach (DataRow r in dt.Rows) { yield return (new CohortDefinition( Convert.ToInt32(r[valueMemberName]), r[displayMemberName].ToString(), Convert.ToInt32(r[versionMemberName]), Convert.ToInt32(r[projectNumberMemberName]) , externalSource)); } }
/// <summary> /// Creates a new cohort reference in the data export database. This must resolve (via <paramref name="originalId"/>) to /// a row in the external cohort database (<paramref name="externalSource"/>). /// </summary> /// <param name="repository"></param> /// <param name="externalSource"></param> /// <param name="originalId"></param> public ExtractableCohort(IDataExportRepository repository, ExternalCohortTable externalSource, int originalId) { Repository = repository; if (!externalSource.IDExistsInCohortTable(originalId)) { throw new Exception("ID " + originalId + " does not exist in Cohort Definitions (Referential Integrity Problem)"); } Repository.InsertAndHydrate(this, new Dictionary <string, object> { { "OriginID", originalId }, { "ExternalCohortTable_ID", externalSource.ID } }); ClearAllInjections(); }
/// <inheritdoc/> public IExternalCohortDefinitionData GetExternalData(int timeoutInSeconds = -1) { var db = ExternalCohortTable.Discover(); var syntax = db.Server.GetQuerySyntaxHelper(); string sql = $@"Select {syntax.EnsureWrapped("projectNumber")}, {syntax.EnsureWrapped("description")}, {syntax.EnsureWrapped("version")}, {syntax.EnsureWrapped("dtCreated")} from {ExternalCohortTable.DefinitionTableName} where {syntax.EnsureWrapped("id")} = {OriginID}"; if (timeoutInSeconds != -1) { db.Server.TestConnection(timeoutInSeconds * 1000); } using (var con = db.Server.GetConnection()) { con.Open(); using (var getDescription = db.Server.GetCommand(sql, con)) { if (timeoutInSeconds != -1) { getDescription.CommandTimeout = timeoutInSeconds; } using (var r = getDescription.ExecuteReader()) { if (!r.Read()) { return(ExternalCohortDefinitionData.Orphan); } return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name)); } } } }
/// <summary> /// Fetches and returns project number, version etc listed in the remote cohort database for this cohort /// </summary> public IExternalCohortDefinitionData GetExternalData() { string sql = @"select projectNumber, description,version,dtCreated from " + ExternalCohortTable.DefinitionTableName + " where id = " + OriginID; var db = ExternalCohortTable.Discover(); using (var con = db.Server.GetConnection()) { con.Open(); var getDescription = db.Server.GetCommand(sql, con); var r = getDescription.ExecuteReader(); if (!r.Read()) { throw new Exception("No records returned for Cohort OriginID " + OriginID); } return(new ExternalCohortDefinitionData(r, ExternalCohortTable.Name)); } }
/// <inheritdoc/> public void InjectKnown(ExternalCohortTable instance) { _knownExternalCohortTable = new Lazy <IExternalCohortTable>(() => instance); }
/// <inheritdoc/> public void ReverseAnonymiseDataTable(DataTable toProcess, IDataLoadEventListener listener, bool allowCaching) { int haveWarnedAboutTop1AlreadyCount = 10; var syntax = ExternalCohortTable.GetQuerySyntaxHelper(); string privateIdentifier = syntax.GetRuntimeName(GetPrivateIdentifier()); string releaseIdentifier = syntax.GetRuntimeName(GetReleaseIdentifier()); //if we don't want to support caching or there is no cached value yet if (!allowCaching || _releaseToPrivateKeyDictionary == null) { DataTable map = FetchEntireCohort(); Stopwatch sw = new Stopwatch(); sw.Start(); //dictionary of released values (for the cohort) back to private values _releaseToPrivateKeyDictionary = new Dictionary <string, string>(); foreach (DataRow r in map.Rows) { if (_releaseToPrivateKeyDictionary.Keys.Contains(r[releaseIdentifier])) { if (haveWarnedAboutTop1AlreadyCount > 0) { haveWarnedAboutTop1AlreadyCount--; listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Top 1-ing will occur for release identifier " + r[releaseIdentifier] + " because it maps to multiple private identifiers")); } else { if (haveWarnedAboutTop1AlreadyCount == 0) { haveWarnedAboutTop1AlreadyCount = -1; listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Top 1-ing error message disabled due to flood of messages")); } } } else { _releaseToPrivateKeyDictionary.Add(r[releaseIdentifier].ToString().Trim(), r[privateIdentifier].ToString().Trim()); } _reverseAnonymiseProgressFetchingMap++; if (_reverseAnonymiseProgressFetchingMap % 500 == 0) { listener.OnProgress(this, new ProgressEventArgs("Assembling Release Map Dictionary", new ProgressMeasurement(_reverseAnonymiseProgressFetchingMap, ProgressType.Records), sw.Elapsed)); } } listener.OnProgress(this, new ProgressEventArgs("Assembling Release Map Dictionary", new ProgressMeasurement(_reverseAnonymiseProgressFetchingMap, ProgressType.Records), sw.Elapsed)); } int nullsFound = 0; int substitutions = 0; Stopwatch sw2 = new Stopwatch(); sw2.Start(); //fix values foreach (DataRow row in toProcess.Rows) { try { object value = row[releaseIdentifier]; if (value == null || value == DBNull.Value) { nullsFound++; continue; } row[releaseIdentifier] = _releaseToPrivateKeyDictionary[value.ToString().Trim()].Trim();//swap release value for private value (reversing the anonymisation) substitutions++; _reverseAnonymiseProgressReversing++; if (_reverseAnonymiseProgressReversing % 500 == 0) { listener.OnProgress(this, new ProgressEventArgs("Substituting Release Identifiers For Private Identifiers", new ProgressMeasurement(_reverseAnonymiseProgressReversing, ProgressType.Records), sw2.Elapsed)); } } catch (KeyNotFoundException e) { throw new Exception("Could not find private identifier (" + privateIdentifier + ") for the release identifier (" + releaseIdentifier + ") with value '" + row[releaseIdentifier] + "' in cohort with cohortDefinitionID " + OriginID, e); } } //final value listener.OnProgress(this, new ProgressEventArgs("Substituting Release Identifiers For Private Identifiers", new ProgressMeasurement(_reverseAnonymiseProgressReversing, ProgressType.Records), sw2.Elapsed)); if (nullsFound > 0) { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found " + nullsFound + " null release identifiers amongst the " + toProcess.Rows.Count + " rows of the input data table (on which we were attempting to reverse annonymise)")); } listener.OnNotify(this, new NotifyEventArgs(substitutions > 0?ProgressEventType.Information : ProgressEventType.Error, "Substituted " + substitutions + " release identifiers for private identifiers in input data table (input data table contained " + toProcess.Rows.Count + " rows)")); toProcess.Columns[releaseIdentifier].ColumnName = privateIdentifier; }
/// <inheritdoc/> public DiscoveredDatabase GetDatabaseServer() { return(ExternalCohortTable.Discover()); }
/// <inheritdoc/> public string GetReleaseIdentifierDataType() { return(ExternalCohortTable.DiscoverReleaseIdentifier().DataType.SQLType); }