Importing Data

Introduction

The program takes the data in the session data (sd) tables in a source database (the 'session data' having been created by the most recent harvest operation), and compares it with the accumulated data for each source, stored in the accumulated data (ad) tables. New and revised data are then transferred to the ad tables. Unlike the preceding download and harvest steps, the import process is essentially the same for all data sources, because the sd and ad tables have the same structure in each database. The listing of tables present in each DB varies but not the table structures themselves.

N.B. The download code can be found at https://github.com/ecrin-github/DataImporter

Parameters

The system is a console app, (to more easily support being scheduled) and can take takes 2 parameters:
-s, followed by a comma separated list of integer ids, each representing a data source within the system: The program takes each of these ids in turn, and carries out the sd to ad import process for each of them.
-T: as a flag. If present, forces the recreation of a new set of accumulated data tables. This parameter should therefore only be used when creating the ad tables for the first time, or when the entire ad schema is recreated from harvested data that represents the totality of the data available from the source. In other words when carrying out a full rebuild of the source's data from a full harvest.
Thus, the parameter string
     -s "100116, 100124, 100132"
will cause data to be imported from the session data tables for each of the Australian, German and Dutch trial registries, in that order.
The parameter string
     -s "101900, 101901" -T
will cause, for the BioLinncc and Yoda repositories respectively, the current ad tables to be dropped and then rebuilt, before being filled with the most recent session data harvested from those repositories.
Note there is no parameter to allow using only part of the harvested data in the sd tables - all of the sd data is always used. The way in which different data is imported, e.g. data that has been revised or added after a certain date, is by controlling the data harvest processing that precedes the import step.

Overview

Identifying New or Changed Data

The initial stage of the import process is to recreate and fill a set of 4 'diff' tables, that summarise the differences between the sd data and the corresponding ad data. These tables are

• to_ad_study_recs - lists new, edited, unchanged and (possibly) deleted study records
• to_ad_study_atts - lists the details of edits carried out within study attributes
• to_ad_object_recs - lists new, edited, unchanged and (possibly) deleted data object records
• to_ad_object_atts - lists the details of edits carried out within data object attributes

All 4 tables are created in the sd schema, where they remain (to allow inspection) until the next harvest or import process.
The system fills the _recs tables by comparing the sd_sid (for studies) or sd_oid (for objects) fields. New study sd_sids are inserted into to_ad_study_recs with a status of 1, edited studies, identified by differences in the 'study_full_hash' fields of matching study records, are inserted with a status of 2, and unchanged studies with a status of 3. It is not possible to identify deleted studies, i.e. that exist in the ad table but not the sd data, unless the sd data represents 100% of the downloaded files, i.e. is a full harvest. The system checks if this is the case. If it is, it can insert deleted sd_sids - this time from the ad tables - into the to_ad_study_recs table with a status of 4. An exactly analogous process is used for the data object records, filling the to_ad_object_recs table. The code for the study records is shown below.

        public void IdentifyNewStudies()
        {
            string sql_string = @"INSERT INTO sd.to_ad_study_recs (sd_sid, status)
                    SELECT s.sd_sid, 1 from sd.studies s
                    LEFT JOIN ad.studies a
                    on s.sd_sid = a.sd_sid 
                    WHERE a.sd_sid is null;";
            ExecuteSQL(sql_string);
        }

        public void IdentifyEditedStudies()
        {
            string sql_string = @"INSERT INTO sd.to_ad_study_recs (sd_sid, status)
                SELECT s.sd_sid, 2 from sd.studies s
                INNER JOIN ad.studies a
                on s.sd_sid = a.sd_sid
                where s.study_full_hash <> a.study_full_hash;";
            ExecuteSQL(sql_string);
        }

        public void IdentifyIdenticalStudies()
        {
            string sql_string = @"INSERT INTO sd.to_ad_study_recs (sd_sid, status)
                SELECT s.sd_sid, 3 from sd.studies s
                INNER JOIN ad.studies a
                on s.sd_sid = a.sd_sid
                where s.study_full_hash = a.study_full_hash;";
            ExecuteSQL(sql_string);
        }

        public void IdentifyDeletedStudies()
        {
            string sql_string = @"INSERT INTO sd.to_ad_study_recs(sd_sid, status)
                SELECT a.sd_sid, 4 from ad.studies a
                LEFT JOIN sd.studies s
                on a.sd_sid = s.sd_sid
                WHERE s.sd_sid is null;";
            ExecuteSQL(sql_string);
        }

For studies and objects that have been edited the nature of the edit is then clarified - in particular whether or not it involves changes to the main study or object record, and / or if it involves an addition, edit, or deletion of one or more attribute records. This is done by comparing the record_hash fields of the edited study and object fields. If a change is found - indicating the main study or object record itself has been edited - then a value of 2 is inserted into the _rec_stattus field in the _recs tables. For data objects, an additional check is made in the same way to see if any dataset data (if it exists) has changed, because there is a one-to-one relationship between data object and object dataset data. If it has a value of 4 is inserted into the object_dataset_status field. the relevant code is shown below.

        public void IdentifyChangedStudyRecs()
        {
            string sql_string = @"with t as (
                select s.sd_sid
                from sd.studies s
                INNER JOIN ad.studies a
                on s.sd_sid = a.sd_sid
                where s.record_hash <> a.record_hash)
                UPDATE sd.to_ad_study_recs c
                SET study_rec_status = 2
                from t
                WHERE t.sd_sid = c.sd_sid;";
            ExecuteSQL(sql_string);
        }

        public void IdentifyChangedObjectRecs()
        {
            string sql_string = @"with t as (
                select s.sd_oid
                from sd.data_objects s
                INNER JOIN ad.data_objects a
                on s.sd_oid = a.sd_oid
                where s.record_hash <> a.record_hash)
                UPDATE sd.to_ad_object_recs c
                SET object_rec_status = 2
                FROM t
                WHERE t.sd_oid = c.sd_oid;";
            ExecuteSQL(sql_string);
        }

        public void IdentifyChangedDatasetRecs()
        {
            string sql_string = @"with t as (
                select s.sd_oid
                from sd.object_datasets s
                INNER JOIN ad.object_datasets a
                on s.sd_oid = a.sd_oid
                where s.record_hash <> a.record_hash)
                UPDATE sd.to_ad_object_recs c
                SET object_dataset_status = 4
                FROM t
                WHERE t.sd_oid = c.sd_oid;";
            ExecuteSQL(sql_string);
        }

At the end of this process the system has - within the ad schema of the source database - 4 temporary tables that hold this information, in effect providing a complete record of the differences between the session data and accumulated data sets.

Actioning the changes

The system then works through the various categories of data that have been identified:

• Any new studies are directly imported into the ad tables along with all their attributes.
• For edited studies, the nature of the edit is examined. If a change has occured in the main (singleton) study record, that record is replaced with the new version from the sd data. If a change has occured in a study attribute, *all* the attribute records of that type are replaced by *all* the attribute records of that type in the sd data. There is no attempt to try and match individual attribute records to see which specifically have been changed / added / deleted - partly because of the lack of persistent identifiers for these records. Instead the whole set of attributes is replaced. If a completely new type of attribute appears for a study all the records of that attribvute type are added. If an attribute type completely disapears from a study all the corresponding attribute records are removed.
  
• For unchanged studies the 'date of data fetch' is updated to match that in the sd data but no other changes are applied. This indicates the last date the data was examined, even if the data was unchanged. The same update, of 'date of data fetch' is made to edited records and is contained automatically within new records.
• For deleted studies, if it has been possible to identify these, the entire study and all attributes are removed from the ad tables.
  

All 4 steps are then repeated for the data object data.