<commit_before>from flask.ext.mysqldb import MySQL
from MySQLdb import Error as MySQLError
import json
import os # for mkdir
import random

from util import log, filename

class DbHandler:
    def __init__(self, app):
        # MySQL configurations
        app.config['MYSQL_HOST'] = 'localhost'
        app.config['MYSQL_USER'] = 'default'
        app.config['MYSQL_DB']   = 'recordings_master'
        app.config['MYSQL_USE_UNICODE'] = True
        app.config['MYSQL_CHARSET'] = 'utf8'

        self.mysql = MySQL(app)

        # needed to sanitize the dynamic sql creation in insertGeneralData
        # keep a list of allowed column names for insertions etc. depending on the table (device, isntructor, etc)
        self.allowedColumnNames = {
            'device': [
                'userAgent',
                'imei'
            ],
            'instructor': [
                'name',
                'email',
                'phone',
                'address'
            ],
            'speaker': [
                'name',
                'deviceImei'
            ],
            'speaker_info': [
                'speakerId',
                's_key',
                's_value'
            ]
        }

    # inserts data into appropriate table
    #
    # name is i.e. 'instructor' and is a representation of the data, for errors and general identification
    # data is a json object whose keys will be used as table column names and those values
    #   will be inserted into table
    # returns the id of the newly inserted row or errors in the format
    #   dict(msg=id or msg, statusCode=htmlStatusCode)
    #
    # Example:
    #    name='device'
    #    data = {'imei':245, 'userAgent':'Mozilla'}
    #    table = 'device'
    #
    #    In which case, this function will 
    #    insert into device (imei, userAgent) 
    #                values ('245','Mozilla')
    #    and return said rows newly generated id.
    #
    # WARNING: appends the keys of data straight into a python string using %
    #          so at least this should be sanitized. Sanitized by a whitelist of
    #          allowed keys in self.allowedColumnNames
    def insertGeneralData(self, name, data, table):
        keys = []
        vals = []
        dataId = None
        try:
            if isinstance(data, str):
                data = json.loads(data)

            for key, val in data.items(): # use data.iteritems() for python 2.7
                # allow only keys from the appropriate list in self.allowedColumnNames
                if key not in self.allowedColumnNames[name]:
                    raise KeyError('Unallowed column name used! Did someone hack the frontend? name: %s' % key)
                keys.append(key)
                vals.append(val)

            data = None # data is untrusted, should not be used unless it's filtered
        except (KeyError, TypeError, ValueError) as e:
            msg = '%s data not on correct format, aborting.' % name
            log(msg, e)
            return dict(msg=msg, statusCode=400)

        try: 
            # insert into table
            cur = self.mysql.connection.cursor()

            # make our query something like (with 4 key/value pairs)
            # 'INSERT INTO %s (%s, %s, %s, %s) \
            #              VALUES (%s, %s, %s, %s)',
            # depending on number of data keys/values 
            queryStr = 'INSERT INTO %s ('

            queryStrMid = '' # since we can reuse the (%s,%s,...)
            for i in range(len(keys)):
                queryStrMid += '%s'
                if (i != len(keys) - 1):
                    queryStrMid += ', '

            queryStr += queryStrMid
            queryStr += ') '

            # input the keys first, because we don't want the '' quotes that cur.execute
            #   automatically puts there
            queryStr = queryStr % tuple([table] + keys)

            queryStr += 'VALUES ('
            queryStr += queryStrMid
            queryStr += ')'

            # make the replacement tuple which is set in place of the %s's in the query
            queryTuple = tuple(vals)

            cur.execute(queryStr, queryTuple)

            # get the newly auto generated id

            # create our query something like
            # 'SELECT id FROM %s WHERE \
            #              %s=%s AND %s=%s AND %s=%s AND %s=%s'
            # but now the order is WHERE key=val AND key1=val1 and so
            # we have to interleave our lists instead of appending them 
            # to get the correct order 
            interleavedList = []
            for i in range(len(keys)):
                interleavedList.append(keys[i])
                # just a hack, because of the quote thing mentioned above
                #   will be replaces with vals in query
                interleavedList.append('%s') 
            
            queryStr = 'SELECT id FROM %s WHERE '
            for i in range(len(keys)):
                queryStr += '%s=%s'
                if (i != len(keys) - 1):
                    queryStr += ' AND '

            queryStr = queryStr % tuple([table] + interleavedList)

            cur.execute(queryStr, queryTuple)
            # return highest id in case of multiple results (should be the newest entry)
            dataIds = cur.fetchall()
            dataId = max([i[0] for i in dataIds]) # fetchall() returns a list of tuples

            # only commit if we had no exceptions until this point
            self.mysql.connection.commit()

        except MySQLError as e:
            msg = 'Database error.'
            log(msg, e)
            return dict(msg=msg, statusCode=500)

        if dataId is None:
            msg = 'Couldn\'t get %s id.' % name
            log(msg)
            return dict(msg=msg, statusCode=500)
        else:
            return dict(msg='{"%sId":' % name + str(dataId) + '}', statusCode=200)

    # inserts into both speaker and speaker_info
    # speakerData is the {'name':name[, 'deviceImei':deviceImei]}
    # speakerInfo are the extra info values to insert into
    #   speaker_info table, e.g. speakerInfo: {'height':'154', etc.}
    # updates values if key already exists
    # assumes speaker doesn't exist in database.
    def insertSpeakerData(self, speakerData, speakerInfo):
        speakerId = None
        res = self.insertGeneralData('speaker', speakerData, 'speaker')
        if 'speakerId' in res['msg']:
            speakerId = json.loads(res['msg'])['speakerId']
        else:
            return res
        for k, v in speakerInfo.items():
            try: 
                cur = self.mysql.connection.cursor()

                cur.execute('SELECT s_value FROM speaker_info WHERE speakerId=%s AND s_key=%s', 
                             (speakerId, k))
                value = cur.fetchone()
                if (value is None):
                    # no value with this key, insert it
                    self.insertGeneralData('speaker_info', {
                                                                'speakerId':speakerId,
                                                                's_key':k,
                                                                's_value':v
                                                            },
                                            'speaker_info')
                else:
                    # key already exists, update value
                    value = value[0] # fetchone returns tuple on success
                    cur.execute('UPDATE speaker_info \
                                 SET s_value=%s \
                                 WHERE speakerId=%s AND s_key=%s', 
                                (v, speakerId, k))
            except MySQLError as e:
                msg = 'Database error.'
                log(msg, e)
                return dict(msg=msg, statusCode=500)
        return res

    # instructorData = look at format in the client-server API
    def processInstructorData(self, instructorData):
        try:
            if isinstance(instructorData, str):
                instructorData = json.loads(instructorData)
        except (ValueError) as e:
            msg = '%s data not on correct format, aborting.' % name
            log(msg, e)
            return dict(msg=msg, statusCode=400)

        if 'id' in instructorData:
            # instructor was submitted as an id, see if he exists in database
            try: 
                cur = self.mysql.connection.cursor()

                cur.execute('SELECT id FROM instructor WHERE id=%s', (instructorData['id'],)) # have to pass in a tuple, with only one parameter
                instructorId = cur.fetchone()
                if (instructorId is None):
                    # no instructor
                    msg = 'No instructor with that id.'
                    log(msg)
                    return dict(msg=msg, statusCode=400)
                else:
                    # instructor already exists, return it
                    instructorId = instructorId[0] # fetchone returns tuple on success<commit_msg>Remove update feature, it is assumed speaker doesnt exist.<commit_after>from flask.ext.mysqldb import MySQL
from MySQLdb import Error as MySQLError
import json
import os # for mkdir
import random

from util import log, filename

class DbHandler:
    def __init__(self, app):
        # MySQL configurations
        app.config['MYSQL_HOST'] = 'localhost'
        app.config['MYSQL_USER'] = 'default'
        app.config['MYSQL_DB']   = 'recordings_master'
        app.config['MYSQL_USE_UNICODE'] = True
        app.config['MYSQL_CHARSET'] = 'utf8'

        self.mysql = MySQL(app)

        # needed to sanitize the dynamic sql creation in insertGeneralData
        # keep a list of allowed column names for insertions etc. depending on the table (device, isntructor, etc)
        self.allowedColumnNames = {
            'device': [
                'userAgent',
                'imei'
            ],
            'instructor': [
                'name',
                'email',
                'phone',
                'address'
            ],
            'speaker': [
                'name',
                'deviceImei'
            ],
            'speaker_info': [
                'speakerId',
                's_key',
                's_value'
            ]
        }

    # inserts data into appropriate table
    #
    # name is i.e. 'instructor' and is a representation of the data, for errors and general identification
    # data is a json object whose keys will be used as table column names and those values
    #   will be inserted into table
    # returns the id of the newly inserted row or errors in the format
    #   dict(msg=id or msg, statusCode=htmlStatusCode)
    #
    # Example:
    #    name='device'
    #    data = {'imei':245, 'userAgent':'Mozilla'}
    #    table = 'device'
    #
    #    In which case, this function will 
    #    insert into device (imei, userAgent) 
    #                values ('245','Mozilla')
    #    and return said rows newly generated id.
    #
    # WARNING: appends the keys of data straight into a python string using %
    #          so at least this should be sanitized. Sanitized by a whitelist of
    #          allowed keys in self.allowedColumnNames
    def insertGeneralData(self, name, data, table):
        keys = []
        vals = []
        dataId = None
        try:
            if isinstance(data, str):
                data = json.loads(data)

            for key, val in data.items(): # use data.iteritems() for python 2.7
                # allow only keys from the appropriate list in self.allowedColumnNames
                if key not in self.allowedColumnNames[name]:
                    raise KeyError('Unallowed column name used! Did someone hack the frontend? name: %s' % key)
                keys.append(key)
                vals.append(val)

            data = None # data is untrusted, should not be used unless it's filtered
        except (KeyError, TypeError, ValueError) as e:
            msg = '%s data not on correct format, aborting.' % name
            log(msg, e)
            return dict(msg=msg, statusCode=400)

        try: 
            # insert into table
            cur = self.mysql.connection.cursor()

            # make our query something like (with 4 key/value pairs)
            # 'INSERT INTO %s (%s, %s, %s, %s) \
            #              VALUES (%s, %s, %s, %s)',
            # depending on number of data keys/values 
            queryStr = 'INSERT INTO %s ('

            queryStrMid = '' # since we can reuse the (%s,%s,...)
            for i in range(len(keys)):
                queryStrMid += '%s'
                if (i != len(keys) - 1):
                    queryStrMid += ', '

            queryStr += queryStrMid
            queryStr += ') '

            # input the keys first, because we don't want the '' quotes that cur.execute
            #   automatically puts there
            queryStr = queryStr % tuple([table] + keys)

            queryStr += 'VALUES ('
            queryStr += queryStrMid
            queryStr += ')'

            # make the replacement tuple which is set in place of the %s's in the query
            queryTuple = tuple(vals)

            cur.execute(queryStr, queryTuple)

            # get the newly auto generated id

            # create our query something like
            # 'SELECT id FROM %s WHERE \
            #              %s=%s AND %s=%s AND %s=%s AND %s=%s'
            # but now the order is WHERE key=val AND key1=val1 and so
            # we have to interleave our lists instead of appending them 
            # to get the correct order 
            interleavedList = []
            for i in range(len(keys)):
                interleavedList.append(keys[i])
                # just a hack, because of the quote thing mentioned above
                #   will be replaces with vals in query
                interleavedList.append('%s') 
            
            queryStr = 'SELECT id FROM %s WHERE '
            for i in range(len(keys)):
                queryStr += '%s=%s'
                if (i != len(keys) - 1):
                    queryStr += ' AND '

            queryStr = queryStr % tuple([table] + interleavedList)

            cur.execute(queryStr, queryTuple)
            # return highest id in case of multiple results (should be the newest entry)
            dataIds = cur.fetchall()
            dataId = max([i[0] for i in dataIds]) # fetchall() returns a list of tuples

            # only commit if we had no exceptions until this point
            self.mysql.connection.commit()

        except MySQLError as e:
            msg = 'Database error.'
            log(msg, e)
            return dict(msg=msg, statusCode=500)

        if dataId is None:
            msg = 'Couldn\'t get %s id.' % name
            log(msg)
            return dict(msg=msg, statusCode=500)
        else:
            return dict(msg='{"%sId":' % name + str(dataId) + '}', statusCode=200)

    # inserts into both speaker and speaker_info
    # speakerData is the {'name':name[, 'deviceImei':deviceImei]}
    # speakerInfo are the extra info values to insert into
    #   speaker_info table, e.g. speakerInfo: {'height':'154', etc.}
    # assumes speaker doesn't exist in database.
    def insertSpeakerData(self, speakerData, speakerInfo):
        speakerId = None
        res = self.insertGeneralData('speaker', speakerData, 'speaker')
        if 'speakerId' in res['msg']:
            speakerId = json.loads(res['msg'])['speakerId']
        else:
            return res
        for k, v in speakerInfo.items():
            self.insertGeneralData('speaker_info', {
                                                        'speakerId':speakerId,
                                                        's_key':k,
                                                        's_value':v
                                                    },
                                    'speaker_info')
        return res

    # instructorData = look at format in the client-server API
    def processInstructorData(self, instructorData):
        try:
            if isinstance(instructorData, str):
                instructorData = json.loads(instructorData)
        except (ValueError) as e:
            msg = '%s data not on correct format, aborting.' % name
            log(msg, e)
            return dict(msg=msg, statusCode=400)

        if 'id' in instructorData:
            # instructor was submitted as an id, see if he exists in database
            try: 
                cur = self.mysql.connection.cursor()

                cur.execute('SELECT id FROM instructor WHERE id=%s', (instructorData['id'],)) # have to pass in a tuple, with only one parameter
                instructorId = cur.fetchone()
                if (instructorId is None):
                    # no instructor
                    msg = 'No instructor with that id.'
                    log(msg)
                    return dict(msg=msg, statusCode=400)
                else:
                    # instructor already exists, return it
                    instructorId = instructorId[0] # fetchone returns tuple on success
{'fraction_non_alphanumeric': 0.08856045618526708, 'fraction_numerical': 0.0045385779122541605, 'mean_word_length': 1.5171353251318103, 'pattern_counts': {'":': 2, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1673105', 'n_tokens_mistral': 4550, 'n_tokens_neox': 4301, 'n_words': 1511}