def getConsent(self, survey_id):
conn_handler = SQLConnectionHandler()
with conn_handler.get_postgres_cursor() as cur:
cur.execute("""SELECT agc.participant_name,
agc.participant_email,
agc.parent_1_name,
agc.parent_2_name,
agc.is_juvenile,
agc.deceased_parent,
agc.ag_login_id,
agc.date_signed,
agc.assent_obtainer,
agc.age_range,
agl.survey_id
FROM ag_consent agc JOIN
ag_login_surveys agl
USING (ag_login_id, participant_name)
WHERE agl.survey_id=%s""", [survey_id])
colnames = [x[0] for x in cur.description]
result = cur.fetchone()
if result:
result = {k: v for k, v in zip(colnames, result)}
if 'date_signed' in result:
result['date_signed'] = str(result['date_signed'])
return result
def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
# Remove user using old stype DB Schema
self.get_cursor().callproc("ag_delete_participant", [ag_login_id, participant_name])
self.connection.commit()
# Remove user from new schema
conn_handler = SQLConnectionHandler()
sql = "SELECT survey_id FROM ag_login_surveys WHERE ag_login_id = " "%s AND participant_name = %s"
survey_id = conn_handler.execute_fetchone(sql, (ag_login_id, participant_name))[0]
with conn_handler.get_postgres_cursor() as curr:
sql = "DELETE FROM survey_answers WHERE " "survey_id = %s"
curr.execute(sql, [survey_id])
sql = "DELETE FROM survey_answers_other WHERE " "survey_id = %s"
curr.execute(sql, [survey_id])
# Reset survey attached to barcode(s)
sql = "UPDATE ag_kit_barcodes SET survey_id = NULL WHERE " "survey_id = %s"
curr.execute(sql, [survey_id])
sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s"
curr.execute(sql, [survey_id])
# Delete last due to foreign keys
sql = "DELETE FROM ag_login_surveys WHERE " "survey_id = %s"
curr.execute(sql, [survey_id])
sql = "DELETE FROM ag_consent WHERE ag_login_id = " "%s AND participant_name = %s"
curr.execute(sql, [ag_login_id, participant_name])
def make_settings_table():
conn = SQLConnectionHandler()
settings = AMGUT_CONFIG.get_settings()
columns = [' '.join([setting[0], 'varchar']) for setting in settings]
column_names = [setting[0] for setting in settings]
num_values = len(settings)
sql = "INSERT INTO settings ({}) VALUES ({})".format(
', '.join(column_names), ', '.join(['%s'] * num_values))
args = [str(setting[1]) for setting in settings]
with conn.get_postgres_cursor() as cur:
create_sql = ("CREATE TABLE ag.settings ({}, current_patch varchar "
"NOT NULL DEFAULT 'unpatched')")
create_sql = create_sql.format(', '.join(columns))
cur.execute(create_sql)
cur.execute(sql, args)
def patch_db(patches_dir=PATCHES_DIR, verbose=False):
"""Patches the database schema based on the settings table
Pulls the current patch from the settings table and applies all subsequent
patches found in the patches directory.
"""
conn = SQLConnectionHandler()
current_patch = conn.execute_fetchone(
"SELECT current_patch FROM settings")[0]
current_patch_fp = join(patches_dir, current_patch)
sql_glob = join(patches_dir, '*.sql')
patch_files = natsorted(glob(sql_glob))
if current_patch == 'unpatched':
next_patch_index = 0
elif current_patch_fp not in patch_files:
raise RuntimeError("Cannot find patch file %s" % current_patch)
else:
next_patch_index = patch_files.index(current_patch_fp) + 1
patch_update_sql = "UPDATE settings SET current_patch = %s"
for patch_fp in patch_files[next_patch_index:]:
patch_filename = split(patch_fp)[-1]
with conn.get_postgres_cursor() as cur:
cur.execute('SET SEARCH_PATH TO ag, barcodes, public')
with open(patch_fp, 'U') as patch_file:
if verbose:
echo('\tApplying patch %s...' % patch_filename)
cur.execute(patch_file.read())
cur.execute(patch_update_sql, [patch_filename])
conn._connection.commit()