public void GenerateStudyProvenanceData()
{
string sql_string = "";
sql_string = @"CREATE table nk.temp_study_provenance
as
select s.study_id,
'Data retrieved from ' || string_agg(d.repo_name || ' at ' || to_char(s.datetime_of_data_fetch, 'HH24:MI, dd Mon yyyy'), ', ' ORDER BY s.datetime_of_data_fetch) as provenance
from nk.all_ids_studies s
inner join
(select t.id,
case
when p.uses_who_harvest = true then t.default_name || ' (via WHO ICTRP)'
else t.default_name
end as repo_name
from context_ctx.data_sources t
inner join mon_sf.source_parameters p
on t.id = p.id) d
on s.source_id = d.id
group by study_id ";
db.ExecuteSQL(sql_string);
sql_string = @"update core.studies s
set provenance_string = tt.provenance
from nk.temp_study_provenance tt
where s.id = tt.study_id ";
db.ExecuteProvenanceSQL(sql_string, "core.studies");
sql_string = @"drop table nk.temp_study_provenance;";
db.ExecuteSQL(sql_string);
}
public void CreateJSONStudyData(bool also_do_files, bool create_table = true, int offset = 0)
{
JSONStudyDataLayer repo = new JSONStudyDataLayer(logging_repo);
if (create_table)
{
string sql_string = @"DROP TABLE IF EXISTS core.studies_json;
CREATE TABLE core.studies_json(
id INT NOT NULL PRIMARY KEY
, json JSON NULL
);
CREATE INDEX studies_json_id ON core.studies_json(id);";
db.ExecuteSQL(sql_string);
}
int min_id = repo.FetchMinId();
int max_id = repo.FetchMaxId();
LoopThroughStudyRecords(repo, min_id, max_id, also_do_files, offset);
}
public void CheckStudyLinks()
{
using (var conn = new NpgsqlConnection(connString))
{
// Does any study id correspond to a study already all_ids_studies
// table, that is linked to it via study-study link table.
// Such a study will match the left hand side of the study-study
// link table (the one to be replacwed), and take on the study_id
// used for the 'preferred' right hand side. This should already exist
// because addition of studies is donme in the order 'more preferred first'.
string sql_string = @"UPDATE nk.temp_study_ids t
SET study_id = s.study_id, is_preferred = false
FROM nk.study_study_links k
INNER JOIN nk.all_ids_studies s
ON k.preferred_sd_sid = s.sd_sid
AND k.preferred_source_id = s.source_id
WHERE t.sd_sid = k.sd_sid
AND t.source_id = k.source_id;";
int res = db.ExecuteSQL(sql_string);
logging_repo.LogLine(res.ToString() + " existing studies found");
// Also create a small table that has just the study_ids and sd_sids for the
// already existing studies (used in the import of any additional data
// from these studies
sql_string = @"DROP TABLE IF EXISTS nk.existing_studies;
CREATE TABLE nk.existing_studies as
SELECT sd_sid, study_id
FROM nk.temp_study_ids
WHERE is_preferred = false";
db.ExecuteSQL(sql_string);
}
}
