Esempio n. 1
0
def retrieve_data(x_param, y_param, filters, tasks, dbname = "results.db"):
    """
    Return a list of selected parameters and a data structure (list of list of tuples),

    - 1st index corresponds to the task,
    - 2nd index corresponds to the row,
    - 3rd index corresponds to the selected parameter.
    The key parameters that define a benchmark are always selected.
    """
    db = connect_db(dbname)
    data = []

    # exclude last word, which is the type; the name may have multiple words in it
    cols_to_select = [sql_escape(strip_last_word(x_param)), sql_escape(strip_last_word(y_param))]
    print("cols to select:",cols_to_select)

    # always pass shared primary key information (they define a distinct benchmark)
    primary_keys = []
    for t in range(len(tasks)):
        if t == 0:
            primary_keys = retrieve_primary_keys(tasks[t], db)
        else:
            primary_keys = intersection(primary_keys, retrieve_primary_keys(tasks[t], db))
    for key in primary_keys:
        if sql_escape(key) not in cols_to_select:
            cols_to_select.append(sql_escape(key))

    # also pass filter parameter value in
    for f in filters:
        if f.param not in cols_to_select:
            cols_to_select.append(f.param)

    sql_val_args = []
    filter_command = ""
    for t in range(len(tasks)):
        select_command = "SELECT DISTINCT {} FROM {} ".format(','.join(cols_to_select), task_name(tasks[t]))
        if filters:
            # first time, still need to populate sql_val_args and make filter_command
            if t == 0: 
                filter_command = "WHERE "
                for f in range(len(filters)):

                    filter_command += str(filters[f])
                    sql_val_args.extend(filters[f].args)

                    if f < len(filters) - 1:
                        filter_command += " AND "

        select_command += filter_command
        select_command += ';'

        print(select_command)
        cursor = db.cursor()
        cursor.execute(select_command, sql_val_args)
        data.append(tuple(tuple(row) for row in cursor.fetchall()));
        
    return [strip_quotes(col) for col in cols_to_select], data
Esempio n. 2
0
 def search_email(self, email):
     """Find a member based on their email, or None if email does not exist"""
     email = util.sql_escape(email)
     result = util.fetch_first_or_none(self.conn.cursor(),
                                       "select id, email, first_name, last_name " +
                                       "from am_am_member inner join aminno_member_email " + 
                                       "on am_am_member.id = aminno_member_email.pnum " + 
                                       "where aminno_member_email.email = '" + email + "';")
     return AmMember(self.conn, *result) if result is not None else None
Esempio n. 3
0
 def search_email(self, email):
     """Find a member based on their email, or None if email does not exist"""
     email = util.sql_escape(email)
     result = util.fetch_first_or_none(
         self.conn.cursor(), "select id, email, first_name, last_name " +
         "from am_am_member inner join aminno_member_email " +
         "on am_am_member.id = aminno_member_email.pnum " +
         "where aminno_member_email.email = '" + email + "';")
     return AmMember(self.conn, *result) if result is not None else None
Esempio n. 4
0
 def search_first_last(self, fname, lname):
     """Find a member based on their first and last name, or None if not exist"""
     fname, lname = tuple(map(lambda s: util.sql_escape(s), [fname, lname]))
     result = util.fetch_first_or_none(self.conn.cursor(),
                                       "select id, email, first_name, last_name " +
                                       "from am_am_member inner join aminno_member_email " + 
                                       "on am_am_member.id = aminno_member_email.pnum " + 
                                       "where am_am_member.first_name = '" + fname + "'" + 
                                       "and am_am_member.last_name = '" + lname + "';")
     return AmMember(self.conn, *result) if result is not None else None
Esempio n. 5
0
 def search_first_last(self, fname, lname):
     """Find a member based on their first and last name, or None if not exist"""
     fname, lname = tuple(map(lambda s: util.sql_escape(s), [fname, lname]))
     result = util.fetch_first_or_none(
         self.conn.cursor(), "select id, email, first_name, last_name " +
         "from am_am_member inner join aminno_member_email " +
         "on am_am_member.id = aminno_member_email.pnum " +
         "where am_am_member.first_name = '" + fname + "'" +
         "and am_am_member.last_name = '" + lname + "';")
     return AmMember(self.conn, *result) if result is not None else None
Esempio n. 6
0
def get_tickets_for_milestone(env, db, milestone, field='component'):
    custom = field not in Ticket.std_fields
    cursor = db.cursor()
    sql = 'SELECT ticket.id AS id, ticket.status AS status, '
    if custom:
        sql += 'ticket_custom.value AS %s ' \
               'FROM ticket LEFT OUTER JOIN ticket_custom ON id = ticket ' \
               'WHERE name = \'%s\' AND milestone = \'%s\'' % (
               sql_escape(field), sql_escape(field), sql_escape(milestone))
    else:
        sql += 'ticket.%s AS %s FROM ticket WHERE milestone = \'%s\'' % (
            sql_escape(field), sql_escape(field), sql_escape(milestone))

    cursor.execute(sql)
    tickets = []
    while 1:
        row = cursor.fetchone()
        if not row:
            break
        ticket = {
            'id': int(row['id']),
            'status': row['status'],
            field: row[field]
        }
        tickets.append(ticket)
    return tickets
Esempio n. 7
0
def describe_param(param, mode, tasks, dbname="results.db"):
    """
    Give back metainformation about a parameter to allow for easier filtering.
    
    Param would be an element of the list returned by describe_tasks - space separated name and type
    Returns a 2-tuple describing the parameter type and values for some tasks of a database.
    - 1st value is either 'range' or 'categorical'
    - 2nd value is either a 2-tuple for range types, or a n-tuple for categorical
    """
    db = connect_db(dbname)
    cursor = db.cursor()

    param_pair = param.rsplit(' ', 1)
    param_name = sql_escape(param_pair[0])
    param_type = param_pair[1]
    if param_type == "TEXT":
        mode = 'categorical'
    elif mode not in {'categorical', 'range'}:
        raise ValueError

    subquery = ""
    min_param = "min_p"
    max_param = "max_p"
    if not isinstance(tasks, list):
        subquery = task_name(tasks)
        min_param = max_param = param_name
    else:
        subquery += '('
        for t in range(len(tasks)):
            if mode == "categorical":
                subquery += "SELECT DISTINCT {} FROM {}".format(
                    param_name, task_name(tasks[t]))
            else:
                subquery += "SELECT MIN({0}) as min_p, MAX({0}) as max_p FROM {1}".format(
                    param_name, task_name(tasks[t]))

            if t < len(tasks) - 1:
                subquery += " UNION ALL "
        subquery += ')'

    print(subquery)

    # categorical data, return a list of all distinct values
    if mode == 'categorical':
        cursor.execute("SELECT DISTINCT {} FROM {};".format(
            param_name, subquery))
        return (mode, tuple(row[0] for row in cursor.fetchall()))
    # ranged data, return (min, max)
    else:
        cursor.execute("SELECT MIN({}), MAX({}) FROM {};".format(
            min_param, max_param, subquery))
        return (mode, tuple(cursor.fetchone()))
Esempio n. 8
0
def describe_param(param, mode, tasks, dbname = "results.db"):
    """
    Give back metainformation about a parameter to allow for easier filtering.
    
    Param would be an element of the list returned by describe_tasks - space separated name and type
    Returns a 2-tuple describing the parameter type and values for some tasks of a database.
    - 1st value is either 'range' or 'categorical'
    - 2nd value is either a 2-tuple for range types, or a n-tuple for categorical
    """
    db = connect_db(dbname)
    cursor = db.cursor()

    param_pair = param.rsplit(' ', 1)
    param_name = sql_escape(param_pair[0])
    param_type = param_pair[1]
    if param_type == "TEXT":
        mode = 'categorical'
    elif mode not in {'categorical', 'range'}:
        raise ValueError

    subquery = ""
    min_param = "min_p"
    max_param = "max_p"
    if not isinstance(tasks,list):
        subquery = task_name(tasks)
        min_param = max_param = param_name
    else:
        subquery += '('
        for t in range(len(tasks)):
            if mode == "categorical":
                subquery += "SELECT DISTINCT {} FROM {}".format(param_name, task_name(tasks[t]))
            else:
                subquery += "SELECT MIN({0}) as min_p, MAX({0}) as max_p FROM {1}".format(param_name, task_name(tasks[t]))
            
            if t < len(tasks) - 1:
                subquery += " UNION ALL "
        subquery += ')'

    print(subquery)

    # categorical data, return a list of all distinct values
    if mode == 'categorical':
        cursor.execute("SELECT DISTINCT {} FROM {};".format(param_name, subquery))
        return (mode,tuple(row[0] for row in cursor.fetchall()))
    # ranged data, return (min, max)
    else:
        cursor.execute("SELECT MIN({}), MAX({}) FROM {};".format(min_param, max_param, subquery))
        return (mode,tuple(cursor.fetchone()))
Esempio n. 9
0
def parse_filters(verbose=False):
    """
    Parse filter from current request query string and return the filtered parameters and filters in a list

    verbose mode returns filters without splitting out the type
    """
    filter_param = None
    filter_method = None
    filters = []
    filter_args = []
    filter_params = []
    for arg in urlparse.parse_qsl(request.query_string):
        if arg[0][0] != 'f':
            continue
        # new filter parameter
        if arg[0] == 'fp':
            # previous filter ready to be built
            if filter_param and filter_method and filter_args:
                filters.append(d.Task_filter(filter_param, filter_method, filter_args))
                filter_args = []    # clear arguments; important!
                print("{}: {}".format(filters[-1], filters[-1].args))
                filter_params.append(filter_param)
            # split out the optional type following parameter name
            if verbose:
                filter_param = arg[1]
            else:
                filter_param = sql_escape(strip_last_word(arg[1]))

        if arg[0] == 'fm':
            filter_method = arg[1]
        if arg[0] == 'fa':
            filter_args.append(arg[1])
    # last filter to be added
    if (not filters or filter_param != filters[-1].param) and filter_args:
        filters.append(d.Task_filter(filter_param, filter_method, filter_args))
        print("{}: {}".format(filters[-1], filters[-1].args))
        filter_params.append(filter_param)

    return filter_params,filters
Esempio n. 10
0
def retrieve_data(x_param, y_param, filters, tasks, dbname="results.db"):
    """
    Return a list of selected parameters and a data structure (list of list of tuples),

    - 1st index corresponds to the task,
    - 2nd index corresponds to the row,
    - 3rd index corresponds to the selected parameter.
    The key parameters that define a benchmark are always selected.
    """
    db = connect_db(dbname)
    data = []

    # exclude last word, which is the type; the name may have multiple words in it
    cols_to_select = [
        sql_escape(strip_last_word(x_param)),
        sql_escape(strip_last_word(y_param))
    ]
    print("cols to select:", cols_to_select)

    # always pass shared primary key information (they define a distinct benchmark)
    primary_keys = []
    for t in range(len(tasks)):
        if t == 0:
            primary_keys = retrieve_primary_keys(tasks[t], db)
        else:
            primary_keys = intersection(primary_keys,
                                        retrieve_primary_keys(tasks[t], db))
    for key in primary_keys:
        if sql_escape(key) not in cols_to_select:
            cols_to_select.append(sql_escape(key))

    # also pass filter parameter value in
    for f in filters:
        if f.param not in cols_to_select:
            cols_to_select.append(f.param)

    sql_val_args = []
    filter_command = ""
    for t in range(len(tasks)):
        select_command = "SELECT DISTINCT {} FROM {} ".format(
            ','.join(cols_to_select), task_name(tasks[t]))
        if filters:
            # first time, still need to populate sql_val_args and make filter_command
            if t == 0:
                filter_command = "WHERE "
                for f in range(len(filters)):

                    filter_command += str(filters[f])
                    sql_val_args.extend(filters[f].args)

                    if f < len(filters) - 1:
                        filter_command += " AND "

        select_command += filter_command
        select_command += ';'

        print(select_command)
        cursor = db.cursor()
        cursor.execute(select_command, sql_val_args)
        data.append(tuple(tuple(row) for row in cursor.fetchall()))

    return [strip_quotes(col) for col in cols_to_select], data
Esempio n. 11
0
    def _render_results(self, constraints, order, desc):
        self.req.hdf.setValue('title', 'Custom Query')
        self.req.hdf.setValue(
            'query.edit_href',
            self.env.href.query(constraints, order, desc, action='edit'))

        # FIXME: the user should be able to configure which columns should
        # be displayed
        headers = ['id', 'summary', 'status', 'component', 'owner']
        cols = headers
        if not 'priority' in cols:
            cols.append('priority')

        if order != 'id' and not order in Ticket.std_fields:
            # order by priority by default
            order = 'priority'
        for i in range(len(headers)):
            self.req.hdf.setValue('query.headers.%d.name' % i, headers[i])
            if headers[i] == order:
                self.req.hdf.setValue(
                    'query.headers.%d.href' % i,
                    self.env.href.query(constraints, order, not desc))
                self.req.hdf.setValue('query.headers.%d.order' % i,
                                      desc and 'desc' or 'asc')
            else:
                self.req.hdf.setValue(
                    'query.headers.%d.href' % i,
                    self.env.href.query(constraints, headers[i]))

        sql = []
        sql.append("SELECT " + ", ".join(headers))
        custom_fields = [f['name'] for f in get_custom_fields(self.env)]
        for k in [k for k in constraints.keys() if k in custom_fields]:
            sql.append(", %s.value AS %s" % (k, k))
        sql.append(" FROM ticket")
        for k in [k for k in constraints.keys() if k in custom_fields]:
            sql.append(" LEFT OUTER JOIN ticket_custom AS %s ON " \
                       "(id=%s.ticket AND %s.name='%s')"
                       % (k, k, k, k))

        for col in [
                c for c in ['status', 'resolution', 'priority', 'severity']
                if c in cols
        ]:
            sql.append(" INNER JOIN (SELECT name AS %s_name, value AS %s_value " \
                                   "FROM enum WHERE type='%s')" \
                       " ON %s_name=%s" % (col, col, col, col, col))

        clauses = []
        for k, v in constraints.items():
            if len(v) > 1:
                inlist = ["'" + util.sql_escape(item) + "'" for item in v]
                clauses.append("%s IN (%s)" % (k, ",".join(inlist)))
            elif k in ['keywords', 'cc']:
                clauses.append("%s LIKE '%%%s%%'" % (k, util.sql_escape(v[0])))
            else:
                clauses.append("%s='%s'" % (k, util.sql_escape(v[0])))
        if clauses:
            sql.append(" WHERE " + " AND ".join(clauses))

        if order in ['status', 'resolution', 'priority', 'severity']:
            sql.append(" ORDER BY %s_value" % order)
        else:
            sql.append(" ORDER BY " + order)
        if desc:
            sql.append(" DESC")

        sql = "".join(sql)
        self.log.debug("SQL Query: %s" % sql)
        results = self.get_results(sql)
        util.add_to_hdf(results, self.req.hdf, 'query.results')