Ejemplo n.º 1
0
 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
Ejemplo n.º 2
0
    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])
Ejemplo n.º 3
0
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)
Ejemplo n.º 4
0
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()