def get_linked_cause_or_rei(
modelable_entity_id: int,
session: orm.Session
) -> Tuple[Optional[int], str]:
"""
Returns a tuple of the id of interest (cause_id or rei_id) and
a description string ('cause', 'rei') saying what kind of id it is.
Id of interest is None is ME is a covariate.
Note:
MEs can only be linked to one of a rei, cause, or covariate.
Returns:
tuple, linked id (cause, rei or None if covariate) and a
description string
"""
linked_cause_id = query_tools.exec_query(
queries.GET_LINKED_CAUSE,
session,
parameters={'modelable_entity_id': modelable_entity_id}
).scalar()
if linked_cause_id:
return linked_cause_id, modelable_entities.CAUSE
linked_rei_id = query_tools.exec_query(
queries.GET_LINKED_REI,
session,
parameters={'modelable_entity_id': modelable_entity_id}
).scalar()
if linked_rei_id:
return linked_rei_id, modelable_entities.REI
# If neither a cause nor a REI is linked, then this is a covariate.
return None, modelable_entities.COVARIATE
def test_insert_defaults(covariate_db, cov_conn_def):
"""Insert a new row into the covariate.model_version without a best_start,
best_end, best_user, best_description column to check the default value."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
insert_q = """
INSERT INTO covariate.model_version (model_version_id, covariate_id,
description, code_version, status, is_best, gbd_round_id)
VALUES ('1', '1', 'testing defaults', 'version 1', '1', '0', '5');
"""
exec_query(insert_q, session=sesh)
sesh.commit()
select_q = """
SELECT * FROM covariate.model_version WHERE model_version_id = 1;
"""
res = query_2_df(select_q, session=sesh)
assert not res.empty
assert not res.at[0, 'best_user']
assert not res.at[0, 'best_description']
assert not res.at[0, 'best_start']
assert not res.at[0, 'best_end']
# we need to close the session or the next test will hang
sesh.close()
def run_parent_adjust(cause_id, children, mvt, vers, detail, sex):
##########################################
#Add oldCorrect model and its children
#into the cod.model_version_relation table
#for codCorrect
##########################################
session = get_session('cod')
parent = pull_mvid(cause_id, mvt, sex=sex)
assert parent, "No parent saved"
parent = parent[0]
if mvt == 9:
desc = 'oldC v%s hybrid of %s (%s)' % (vers, children, detail)
elif mvt == 10:
desc = 'oldC v%s target (%s)' % (vers, detail)
try:
for child in children:
query = """INSERT INTO cod.model_version_relation
(parent_id, child_id, model_version_relation_note)
VALUES ({parent}, {child}, "{desc}")
""".format(parent=parent, child=child, desc=desc)
exec_query(query, session=session)
session.commit()
except:
session.rollback()
session.close()
raise
def test_insert_new_row(covariate_db, cov_conn_def):
"""Tests the addition of a single row to the covariate.model table."""
initialize_and_fill_covariate_db(covariate_db, cov_conn_def)
sesh = get_session(cov_conn_def)
insert_q = """
INSERT INTO covariate.model (model_version_id, year_id, location_id,
sex_id, age_group_id, mean_value, upper_value, lower_value)
VALUES ('1', '2017', '1', '2', '22', '10.4', '11.2', '9.6'),
('1', '2016', '1', '2', '22', '10.5', '11.4', '9.4'),
('1', '2015', '1', '2', '22', '10.6', '11.6', '9.2'),
('1', '2014', '1', '2', '22', '10.7', '11.8', '9'),
('1', '2013', '1', '2', '22', '10.8', '12', '8.8');
"""
exec_query(insert_q, session=sesh)
sesh.commit()
select_q = """
SELECT * FROM covariate.model WHERE model_version_id = 1;
"""
res = query_2_df(select_q, session=sesh)
assert not res.empty
# We added five entries associated with model_version_id 1
assert len(res) == 5
# We have to close the session or the next test will hang
sesh.close()
def add_process_version_to_compare_version_output(process_version_id,
compare_version_id,
conn_def):
sesh = get_session(conn_def)
add_process_vers_query = """
CALL gbd.add_process_version_to_compare_version_output ({}, {})
""".format(compare_version_id, process_version_id)
exec_query(add_process_vers_query, session=sesh, close=True)
def activate_new_process_version(process_version_id, conn_def):
sesh = get_session(conn_def)
q = """
UPDATE gbd_process_version
SET gbd_process_version_status_id = 2
WHERE gbd_process_version_id = {process_version_id}
""".format(process_version_id=process_version_id)
exec_query(q, session=sesh, close=True)
def mark_gbd_best(output_version_id, process_version_id, gbd_conn_def):
gbd_sesh = get_session(gbd_conn_def)
update_gbd_cmd = """UPDATE
gbd.gbd_process_version
SET gbd_process_version_status_id = 1
WHERE gbd_process_version_id = {v}
""".format(v=process_version_id)
exec_query(update_gbd_cmd, session=gbd_sesh, close=True)
def add_kit_vers(self):
sesh = get_session(self.conn_def)
query = ('INSERT INTO gbd.kit_version '
'(kit_id, kit_version_note, '
'gbd_round_id, kit_version_status_id) '
'VALUES '
'(2, "MMR run for codcorrect version {}", {}, -1)'
.format(self.codcorrect_vers, self.gbd_round_id))
exec_query(query, session=sesh, close=True)
def create_output_partition(codcorrect_version_id) -> None:
exec_query(CoDCorrectVersion.CREATE_OUTPUT_PARTITION,
session=get_session(ConnectionDefinitions.COD),
close=True,
parameters={
'schema': COD.DataBase.SCHEMA,
'table': COD.DataBase.TABLE,
Columns.OUTPUT_VERSION_ID: codcorrect_version_id
})
def wipe_diagnostics():
# Create a connection
sesh = get_session("codcorrect")
unmark_best = ('UPDATE codcorrect.diagnostic_version SET is_best = 0 '
'WHERE is_best = 2;')
exec_query(unmark_best, session=sesh, close=True)
unmark_best = ('UPDATE codcorrect.diagnostic_version SET is_best = 2 '
'WHERE is_best = 1;')
exec_query(unmark_best, session=sesh, close=True)
def upload_cod_summaries(directories, conn_def):
sesh = get_session(conn_def)
exec_query("set unique_checks= 0", sesh)
inf = Infiles(table='output', schema='cod', session=sesh)
for directory in directories:
inf.indir(path=directory,
with_replace=True,
partial_commit=True,
commit=True)
def activate_new_compare_version(compare_version_id, conn_def):
sesh = get_session(conn_def)
q = """
UPDATE compare_version
SET compare_version_status_id = 2
WHERE compare_version_id = {}
AND compare_version_status_id = -1
""".format(compare_version_id)
exec_query(q, session=sesh, close=True)
def update_status(output_version_id, status, conn_def):
# Update status
sesh = get_session(conn_def)
update_cmd = """UPDATE
cod.output_version
SET
status = {s}
WHERE
output_version_id = {v}""".format(s=status,
v=output_version_id)
exec_query(update_cmd, session=sesh, close=True)
def add_metadata_to_process_version(self, process_version_id):
sesh = get_session(self.conn_def)
query = ('INSERT INTO gbd.gbd_process_version_metadata ('
'gbd_process_version_id, '
'metadata_type_id, '
'val) '
'VALUES '
'({pv_id}, '
'1, '
'"{ov_id}")').format(pv_id=process_version_id,
ov_id=self.codcorrect_vers)
exec_query(query, session=sesh, close=True)
def mark_cod_best(output_version_id, cod_conn_def):
now = datetime.datetime.now()
cod_sesh = get_session(cod_conn_def)
update_cod_cmd = """UPDATE
cod.output_version
SET
is_best = 1,
best_start = '{now}'
WHERE
output_version_id = {v}""".format(now=now,
v=output_version_id)
exec_query(update_cod_cmd, session=cod_sesh, close=True)
def unmark_cod_best(output_version_id, cod_conn_def):
now = datetime.datetime.now()
cod_sesh = get_session(cod_conn_def)
update_cod_cmd = """UPDATE cod.output_version SET
is_best = 0,
best_end = '{now}'
WHERE
output_version_id != {v}
AND best_start IS NOT NULL
AND best_end IS NULL""".format(now=now,
v=output_version_id)
exec_query(update_cod_cmd, session=cod_sesh, close=True)
def unmark_gbd_best(process_version_id, gbd_round_id, gbd_conn_def):
gbd_sesh = get_session(gbd_conn_def)
update_gbd_cmd = """
UPDATE
gbd.gbd_process_version
SET
gbd_process_version_status_id = 2
WHERE
gbd_process_version_id != {v}
AND gbd_round_id = {gbd_round_id}
AND gbd_process_version_status_id = 1
AND gbd_process_id = 3
""".format(v=process_version_id, gbd_round_id=gbd_round_id)
exec_query(update_gbd_cmd, session=gbd_sesh, close=True)
def create_new_version_row(codcorrect_version_id: int, description: str,
decomp_step_id: int, code_version: str,
env_version: int) -> None:
exec_query(CoDCorrectVersion.INSERT_NEW_VERSION_ROW,
session=get_session(ConnectionDefinitions.COD),
close=True,
parameters={
Columns.OUTPUT_VERSION_ID: codcorrect_version_id,
Columns.TOOL_TYPE_ID: COD.DataBase.TOOL_TYPE_ID,
Columns.DECOMP_STEP_ID: decomp_step_id,
Columns.DESCRIPTION: description,
Columns.CODE_VERSION: code_version,
Columns.ENV_VERSION: env_version,
Columns.STATUS: Status.SUBMITTED,
Columns.IS_BEST: Best.NOT_BEST
})
def add_metadata_to_process_version(process_version_id, output_version_id,
lifetable_version_id, envelope_version_id,
pop_version_id, conn_def):
sesh = get_session(conn_def)
query = ('INSERT INTO gbd.gbd_process_version_metadata ('
'gbd_process_version_id, metadata_type_id, val) '
'VALUES '
'({pv_id}, 1, "{ov_id}"), '
'({pv_id}, 3, "{ltv_id}"), '
'({pv_id}, 6, "{env_id}"), '
'({pv_id}, 7, "{popv_id}")').format(pv_id=process_version_id,
ov_id=output_version_id,
ltv_id=lifetable_version_id,
env_id=envelope_version_id,
popv_id=pop_version_id)
exec_query(query, session=sesh, close=True)
def exec_query(call, conn_def):
"""
Executes a raw SQL statement with db_tools
"""
session = ezfuncs.get_session(conn_def)
call = clean_string(call)
return query_tools.exec_query(call, session=session, close=True)
def test_fk_gbd_round_id_error(covariate_db, cov_conn_def):
"""Tries to insert a bad gbd_round_id into the model_version table."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
invalid_gbd_round_id = -1
insert_q = """
INSERT INTO covariate.model_version (covariate_id, description,
code_version, status, is_best, gbd_round_id)
VALUES ('1', 'trigger bad FK', 'vers 1', '1', '0', '{gbd_round_id}');
""".format(gbd_round_id=invalid_gbd_round_id)
with pytest.raises(IntegrityError):
exec_query(insert_q, session=sesh)
# we need to close the session or the next test will hang
sesh.close()
def initialize_and_fill_covariate_db(covariate_db, cov_conn_def):
"""Builds the covariate db and fills the covariate.model_version table with
a few rows needed for testing the covariate.model table."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
insert_q = """
INSERT INTO covariate.model_version (model_version_id, covariate_id,
gbd_round_id, description, code_version, status, is_best)
VALUES ('1', '100', '5', 'pigs per capita', 'version 1', '1', '1'),
('2', '728', '4', 'gun violence', 'version 2', '1', '0'),
('3', '881', '5', 'sdi', 'version 3', '1', '1');
"""
exec_query(insert_q, session=sesh)
sesh.commit()
# We must close the current session or the next one will hang
sesh.close()
def add_metadata_to_process_version(process_version_id, output_version_id,
envelope_version_id, pop_version_id,
conn_def):
sesh = get_session(conn_def)
query = """
INSERT INTO
gbd.gbd_process_version_metadata
(gbd_process_version_id, metadata_type_id, val)
VALUES
({pv_id}, 1, "{ov_id}"),
({pv_id}, 6, "{env_id}"),
({pv_id}, 7, "{popv_id}")
""".format(pv_id=process_version_id,
ov_id=output_version_id,
env_id=envelope_version_id,
popv_id=pop_version_id)
exec_query(query, session=sesh, close=True)
def create_schema(sesh, root_dir):
"""Runs the make_schema.sql script on our covariate database. This needs to
be run before tables can be created on test or prod.
Arguments:
sesh (sqlalchemy.orm.session.Session): our connection to the covariate
database.
root_dir (str, path): regardless of database server, our make_schema
script is the same. It has a fixed path from root_dir so let's
supply that and construct the absolute path to the script.
"""
sql_script = os.path.join(root_dir, 'db_covariate/make_schema.sql')
sql_lines = parse_sql_script(sql_script)
for line in sql_lines:
exec_query(line, session=sesh)
sesh.commit()
def test_fk_year_id_error(covariate_db, cov_conn_def):
"""Test the foreign key constraint to shared.year by providing an invalid
year_id to an insert statement."""
initialize_and_fill_covariate_db(covariate_db, cov_conn_def)
sesh = get_session(cov_conn_def)
invalid_year_id = -1988
insert_q = """
INSERT INTO covariate.model (model_version_id, year_id, location_id,
sex_id, age_group_id, mean_value, upper_value, lower_value)
VALUES ('2', '{year_id}', '1', '2', '22', '324', '515', '214');
""".format(year_id=invalid_year_id)
with pytest.raises(IntegrityError):
exec_query(insert_q, session=sesh)
# We have to close the session or the next test will hang
sesh.close()
def test_pk_integrity_error(covariate_db, cov_conn_def):
"""Test the IntegrityError of our PK by trying to add two rows with the
same model_version_id, location_id, year_id, sex_id, and age_group_id."""
initialize_and_fill_covariate_db(covariate_db, cov_conn_def)
sesh = get_session(cov_conn_def)
insert_q = """
INSERT INTO covariate.model (model_version_id, year_id, location_id,
sex_id, age_group_id, mean_value, upper_value, lower_value)
VALUES ('2', '2016', '1', '2', '22', '324', '515', '214');
"""
exec_query(insert_q, session=sesh)
sesh.commit()
with pytest.raises(IntegrityError):
exec_query(insert_q, session=sesh)
# We have to close the session or the next test will hang
sesh.close()
def test_pk_integrity_error(covariate_db, cov_conn_def):
"""Inserts a new row and then attempts to trigger an IntegrityError by
inserting the same data a second time."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
dummy_mvid = 1
insert_q = """
INSERT INTO covariate.model_version (model_version_id, covariate_id,
description, code_version, status, is_best, gbd_round_id)
VALUES ('{mvid}', '1', 'testing PK error', 'vers 1', '1', '0', '5');
""".format(mvid=dummy_mvid)
exec_query(insert_q, session=sesh)
sesh.commit()
with pytest.raises(IntegrityError):
exec_query(insert_q, session=sesh)
# we need to close the session or the next test will hang
sesh.close()
def test_unique_covariate_round_best_constraint(covariate_db, cov_conn_def):
"""Tries to insert two models marked best for the same covariate for a
single gbd_round_id."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
insert_q = """
INSERT INTO covariate.model_version (covariate_id, description,
code_version, status, is_best, gbd_round_id)
VALUES ('881', 'best SDI marked best', 'version 1', '1', '1', '5');
"""
exec_query(insert_q, session=sesh)
sesh.commit()
# Insert the same composite of covariate_id, gbd_round_id, and is_best to
# trigger failure
with pytest.raises(IntegrityError):
exec_query(insert_q, session=sesh)
# we need to close the session or the next test will hang
sesh.close()
def wipe_diagnostics():
# Create a connection
sesh = get_session('codcorrect')
unmark_best = """
UPDATE
codcorrect.diagnostic_version
SET
is_best = 0
WHERE
is_best = 2;
"""
exec_query(unmark_best, session=sesh, close=True)
unmark_best = """
UPDATE
codcorrect.diagnostic_version
SET
is_best = 2
WHERE
is_best = 1;
"""
exec_query(unmark_best, session=sesh, close=True)
def test_insert_new_row(covariate_db, cov_conn_def):
"""Inserts a new row into the model_version table and queries it to make
sure that the model version ids match."""
covariate_db.add_conn_def(cov_conn_def)
sesh = get_session(cov_conn_def)
dummy_mvid = 1
insert_q = """
INSERT INTO covariate.model_version (model_version_id, covariate_id,
description, code_version, status, is_best, gbd_round_id)
VALUES ('{mvid}', '1', 'best dummy round 5', 'vers 1', '1', '0', '5');
""".format(mvid=dummy_mvid)
exec_query(insert_q, session=sesh)
sesh.commit()
select_q = """
SELECT * FROM covariate.model_version WHERE model_version_id = {mvid};
""".format(mvid=dummy_mvid)
res = query_2_df(select_q, session=sesh)
assert not res.empty
assert res.at[0, 'model_version_id'] == dummy_mvid
# we need to close the session or the next test will hang
sesh.close()