Example #1
0
        /// <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));
                    }
                }
            }
        }
Example #2
0
        /// <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);
                }
            }
        }
Example #3
0
        /// <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);
            }
        }
Example #4
0
        /// <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
        }
Example #5
0
        /// <inheritdoc/>
        public IQuerySyntaxHelper GetQuerySyntaxHelper()
        {
            if (_cachedQuerySyntaxHelper == null)
            {
                _cachedQuerySyntaxHelper = ExternalCohortTable.GetQuerySyntaxHelper();
            }

            return(_cachedQuerySyntaxHelper);
        }
Example #6
0
        /// <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));
            }
        }
Example #7
0
        /// <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();
        }
Example #8
0
        /// <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));
                    }
                }
            }
        }
Example #9
0
        /// <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));
            }
        }
Example #10
0
 /// <inheritdoc/>
 public void InjectKnown(ExternalCohortTable instance)
 {
     _knownExternalCohortTable = new Lazy <IExternalCohortTable>(() => instance);
 }
Example #11
0
        /// <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;
        }
Example #12
0
 /// <inheritdoc/>
 public DiscoveredDatabase GetDatabaseServer()
 {
     return(ExternalCohortTable.Discover());
 }
Example #13
0
 /// <inheritdoc/>
 public string GetReleaseIdentifierDataType()
 {
     return(ExternalCohortTable.DiscoverReleaseIdentifier().DataType.SQLType);
 }