Importing Data
Contents
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_status 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);
}
Finally the _atts tables are filled. Amongst the edited records, if a study or object attribute record has changed in some way, the value of the 'composite hash' for that attribute type, for that study or object, will be different in the sd and ad data. The study or object ids, attribute types and composite hashes in such cases are all stored in the _atts tables with a status value of 2. If a study or object has attributes of a certain type when previously it had none of this type at all, there will be a new composite hash record, also stored in the _atts table with a status of 1. And finally, if a study or object used to have attributes of a certain type but now has none at all, the data (taken from the ad tables) is stored in the _atts table with a status of 4. The _atts tables therefore indicate which attribute types have been changed for any edited study or object. The code for the study data is shown below.
public void IdentifyChangedStudyAtts()
{
// Store as the sd_id and hash type of all changed composite hash values
// in edited records - indicates that one or more of the attributes has changed.
string sql_string = @"INSERT INTO sd.to_ad_study_atts
(sd_sid, hash_type_id, status, composite_hash)
select s.sd_sid, s.hash_type_id, 2, s.composite_hash
from
ad.temp_sd_study_hashes s
INNER JOIN
ad.temp_ad_study_hashes a
on s.sd_sid = a.sd_sid
and s.hash_type_id = a.hash_type_id
where s.composite_hash <> a.composite_hash;";
ExecuteSQL(sql_string);
}
public void IdentifyNewStudyAtts()
{
// Stores the sd_id and hash type of a new ad_sid / hash type combinations,
// indicates that one or more of new types of attributes have been added.
string sql_string = @"INSERT INTO sd.to_ad_study_atts
(sd_sid, hash_type_id, status, composite_hash)
select s.sd_sid, s.hash_type_id, 1, s.composite_hash
from
ad.temp_sd_study_hashes s
LEFT JOIN
ad.temp_ad_study_hashes a
on s.sd_sid = a.sd_sid
and s.hash_type_id = a.hash_type_id
where a.sd_sid is null;";
ExecuteSQL(sql_string);
}
public void IdentifyDeletedStudyAtts()
{
// Stores the sd_id and hash type of deleted ad_sid / hash type combinations,
// indicates that one or more types of attributes have disappeared.
string sql_string = @"INSERT INTO sd.to_ad_study_atts
(sd_sid, hash_type_id, status, composite_hash)
select a.sd_sid, a.hash_type_id, 4, a.composite_hash
from
ad.temp_sd_study_hashes s
RIGHT JOIN
ad.temp_ad_study_hashes a
on s.sd_sid = a.sd_sid
and s.hash_type_id = a.hash_type_id
where s.sd_sid is null;";
ExecuteSQL(sql_string);
}
At the end of this process the system has 4 tables that hold a complete record of the differences between the session data and accumulated data sets, which can then be used to direct the necessary changes inthe ad tables.
Actioning the changes
The system then works through the actions required, following a fairly simple set of rules.
1) Any new studies or objects are directly imported into the corresponding ad tables, along with all their attributes. Note that objects (in study based sources) always hold a reference to their 'parent' study sd_sid, which is a persistent identifier, so a new data object added to an existing study is still correctly matched.
--code
2) For unchanged studies the 'date of data fetch', a field in both study and data object records, 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. Note that the same update, of 'date of data fetch' is also made to edited records, and the data is contained automatically within new records.
--code
3) For edited studies, the nature of the edit is examined. If a change has occurred in the main (singleton) study record, that record is replaced with the new version from the sd data.
--code
If a change has occurred 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. The system knows, from the changed composite hash value, that an edit has happened somewhere in a set of attributes of a particular type. But because of the lack of persistent identifiers for the attribute records, and therefore the difficulty in trying to work out exactly what record(s) have changed, and / or have been added, and / or have been deleted, it is easier to replace the whole set of attributes. The system does this by first deleting the attributes and then inserting the new set. This strategy also means that if a completely new type of attribute appears for a study all the records of that attribute type are inserted, while if an attribute type completely disappears from a study all the corresponding attribute records are removed.
-- code
4) For deleted studies or objects, if it has been possible to identify these, the entire study and all attributes are removed from the ad tables.
-- code
