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
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
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
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
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
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
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()))
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()))
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
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
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')
