memberPlumbing/sqlExport.py

#!/usr/bin/env python3

from datetime import datetime
import MySQLdb
import yaml

from common import membershipworks
from passwords import MEMBERSHIPWORKS_DB

def resolveSource(key, value):
    if type(value) == str:
        return value
    elif type(value) == dict and 'source' in value:
        return value['source']
    else:
        return key

def formatRows(tableMap, data):
    for d in data:
        yield [d.get(resolveSource(k, v)) for k, v in tableMap.items()]

def insertFromTableMap(table, data, tableMap):
    # TODO: this could probably be done better as a single statement?
    c.executemany(
        f"""INSERT INTO {table} ({','.join(f'`{k}`' for k in tableMap.keys())})
        VALUES ({','.join(len(tableMap) * ['%s'])})
        ON DUPLICATE KEY UPDATE
        {', '.join(f'`{k}`=VALUES(`{k}`)' for k in tableMap.keys())};""",
        list(formatRows(tableMap, data)))

def insertDistinctFromTableMap(table, data, tableMap):
    # TODO: this could probably be done better as a single statement?
    c.executemany(
       f"""INSERT INTO {table} ({','.join(f'`{k}`' for k in tableMap.keys())})
        SELECT {','.join(len(tableMap) * ['%s'])} FROM DUAL
        WHERE NOT EXISTS (
            SELECT 1 from {table}
            WHERE {' AND '.join(f'`{k}`<=>%s' for k in tableMap.keys())}
        );""" ,
        list([r * 2 for r in formatRows(tableMap, data)]))


# TODO: delete non-valid labels
def insertLabels(members):
    for member in members:
        for label, label_id in membershipworks._parse_flags()['labels'].items():
            if member[label]:
                c.execute("""
                INSERT INTO member_labels (uid, label_id) VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE
                  uid=VALUES(uid), label_id=VALUES(label_id);""",
                          (member['Account ID'], label_id))
            else:
                c.execute('DELETE FROM member_labels WHERE uid=%s && label_id=%s;',
                          (member['Account ID'], label_id))


with open('tableMapping.yaml') as f:
    tableMapping = yaml.load(f, yaml.SafeLoader)


conn =  MySQLdb.connect(**MEMBERSHIPWORKS_DB)
conn.set_character_set('utf8')
c = conn.cursor()

def createDefinitionsFromTableMap(tableMap):
    def resolveColType(value):
        if type(value) == dict and 'type' in value:
            return value['type']
        else:
            return 'TEXT'

    return ', '.join([f'`{k}` ' + resolveColType(v)
                      for k, v in tableMap.items()])

try:
    print("Creating tables...")
    c.execute('CREATE TABLE IF NOT EXISTS members (' +
              createDefinitionsFromTableMap(tableMapping['members']) +
              ') WITH SYSTEM VERSIONING;')

    c.execute("CREATE TABLE IF NOT EXISTS transactions (" +
              createDefinitionsFromTableMap(tableMapping['transactions']) +
              ", CONSTRAINT `fk_member_uid` FOREIGN KEY (uid) REFERENCES members(uid));")
              #-- FOREIGN KEY event_id REFERENCES event eid

    c.execute("""CREATE TABLE IF NOT EXISTS labels (
    label_id CHAR(24) PRIMARY KEY, label TEXT
    ) WITH SYSTEM VERSIONING;""")

    c.execute("""CREATE TABLE IF NOT EXISTS member_labels (
    uid CHAR(24), label_id CHAR(24),
    PRIMARY KEY(uid, label_id),
    FOREIGN KEY(uid) REFERENCES members(uid),
    FOREIGN KEY(label_id) REFERENCES labels(label_id)
    ) WITH SYSTEM VERSIONING;""")

    print("Updating labels")
    c.executemany("""INSERT INTO labels (label, label_id) VALUES (%s, %s)
    ON DUPLICATE KEY UPDATE
    label=VALUES(label), label_id=VALUES(label_id);""",
                  membershipworks._parse_flags()['labels'].items())

    print("Getting/Updating members...")
    members = membershipworks.get_all_members()
    for m in members:
        # replace flags by booleans
        for flag in [dek['lbl'] for dek in membershipworks.org_info['dek']]:
            if flag in m:
                m[flag] = m[flag] == flag

        for field_id, field in membershipworks._all_fields().items():
            # convert checkboxes to real booleans
            if field.get('typ') == 8 and field['lbl'] in m: # check box
                m[field['lbl']] = True if m[field['lbl']] == 'Y' else False

    insertFromTableMap('members', members, tableMapping['members'])

    insertLabels(members)

    print("Getting/Updating transactions...")
    now = datetime.now()
    transactions_csv = membershipworks.get_transactions(datetime(2010, 1, 1), now)
    transactions_json = membershipworks.get_transactions(
        datetime(2010, 1, 1), now, json=True)
    # this is terrible, but as long as the dates are the same, should be fiiiine
    transactions = [{**j, **v}
                    for j, v in zip(transactions_csv, transactions_json)]
    assert all([t['Account ID'] == t.get('uid', '')
                and t['Payment ID'] == t.get('sid', '')
                for t in transactions])
    insertDistinctFromTableMap(
        'transactions', transactions, tableMapping['transactions'])

    print("Committing changes...")
    conn.commit()

except Exception as e:
    print("Transaction failed, rolling back")
    conn.rollback();
    raise e

finally:
    conn.close()

# TODO: folders, levels, addons