This patch will make compose_query.cgi considerably faster. It also has the
beginnings of a library for parsing a simple language (for expressing
conditionals) into SQL. Right now, the language only understands &'s, |'s, and
most operators sql understands. In the future, it'll understand ()'s, !'s, and
maybe others...but, given that the original compose_query.cgi only knew &'s and
='s, I didn't want to gate this patch for such support. In addition, high on
my todo list is the ability to drill down into results by clicking on rows,
columns, and cells.
Signed-off-by: Jeremy Orlow <jorlow@google.com>
NB. mbligh changed the set calls to use the builtin class. If that doesn't
work, it's my fault ;-)
git-svn-id: http://test.kernel.org/svn/autotest/trunk@1100 592f7852-d20e-0410-864c-8624ca9c26a4
diff --git a/tko/compose_query.cgi b/tko/compose_query.cgi
index 86240fc..96f8489 100644
--- a/tko/compose_query.cgi
+++ b/tko/compose_query.cgi
@@ -15,6 +15,10 @@
sys.path.insert(0, tko)
import display, frontend, db, query_lib
+client_bin = os.path.abspath(os.path.join(tko, '../client/bin'))
+sys.path.insert(0, client_bin)
+import kernel_versions
+
html_header = """\
<form action="compose_query.cgi" method="get">
@@ -37,7 +41,7 @@
</SELECT>
</td>
<td>
- <input type="text" name="condition" size="30" maxlength="80" value="%s">
+ <input type="text" name="condition" size="30" maxlength="200" value="%s">
<input type="hidden" name="title" value="Report">
</td>
<td align="center"><input type="submit" value="Submit">
@@ -47,22 +51,65 @@
</form>
"""
-columns_default = 'kernel'
-rows_default = 'test'
+
+# dictionary used simply for fast lookups
+field_dict = {
+ 'kernel': 'kernel_printable',
+ 'hostname': 'machine_hostname',
+ 'test': 'test',
+ 'label': 'job_label',
+ 'machine_group': 'machine_group',
+ 'reason': 'reason',
+ 'tag': 'job_tag',
+ 'user': 'job_username',
+ 'status': 'status_word',
+}
+
+
+def parse_field(form, form_field, field_default):
+ if not form_field in form:
+ return field_default
+ field_input = form[form_field].value.lower()
+ if field_input and field_input in field_dict:
+ return field_input
+ return field_default
+
+
+def parse_condition(form, form_field, field_default):
+ if not form_field in form:
+ return field_default
+ return form[form_field].value
+
+
+form = cgi.FieldStorage()
+row_field = parse_field(form, 'rows', 'kernel')
+column_field = parse_field(form, 'columns', 'machine_group')
+condition_field = parse_condition(form, 'condition', '')
cgitb.enable()
db = db.db()
-def create_select_options(selected_val, default_val):
+
+def get_value(test, field):
+ if field == 'kernel':
+ return test.kernel_printable
+ if field == 'hostname':
+ return test.machine_hostname
+ if field == 'test':
+ return test.testname
+ if field == 'label':
+ return test.job_label
+ if field == 'machine_group':
+ return test.machine_group
+ if field == 'reason':
+ return test.reason
+ raise "Unknown field"
+
+
+def create_select_options(selected_val):
ret = ""
- option_list = ['kernel', 'hostname', 'test', 'label',
- 'machine_group', 'reason']
- if option_list.count(selected_val) == 0:
- selected_val = default_val
- assert(option_list.count(selected_val) > 0)
-
- for option in option_list:
+ for option in sorted(field_dict.keys()):
if selected_val == option:
selected = " SELECTED"
else:
@@ -75,107 +122,65 @@
return ret
-def main():
+def smart_sort(list, field):
+ if field == 'kernel':
+ def kernel_encode(kernel):
+ return kernel_versions.version_encode(kernel)
+ list.sort(key = kernel_encode, reverse = True)
+ else:
+ list.sort()
+
+
+def gen_matrix():
display.print_main_header()
- # parse the fields from the form.
- form = cgi.FieldStorage()
- columns = columns_default
- rows = rows_default
- condition = None
- for field in form:
- value = form[field].value
- if field == 'columns':
- columns = value
- elif field == 'rows':
- rows = value
- elif field == 'condition':
- condition = value
+ where = None
+ if condition_field.strip() != '':
+ where = query_lib.parse_scrub_and_gen_condition(
+ condition_field, field_dict)
+ print "<!-- where clause: %s -->" % (where,)
- # parse the conditions into sql query and value list.
- condition_sql = ""
- condition_value = []
- if condition:
- condition_list = query_lib.parse_condition(condition)
- condition_sql, condition_value = \
- query_lib.generate_sql_condition(condition_list)
+ ret = frontend.get_matrix_data(db, field_dict[column_field],
+ field_dict[row_field], where)
+ (data, column_list, row_list, stat_list) = ret
- # get all possible column values.
- column_groups = frontend.anygroup.selectunique(db, columns)
+ if not row_list:
+ msg = "There are no results for this query (yet?)."
+ return [[display.box(msg)]]
- # get all possible row values.
- row_groups = frontend.anygroup.selectunique(db,rows)
- # keep only those values in rows/columns that have a test
- # corresponding to it.
- row_groups = query_lib.prune_list(row_groups, condition_sql, \
- condition_value)
- column_groups = query_lib.prune_list(column_groups, condition_sql, \
- condition_value)
+ smart_sort(row_list, row_field)
+ smart_sort(column_list, column_field)
- # prepare the header for the table.
- headers = [g.name for g in column_groups]
-
- header_row = [display.box(x, header=True) for x in headers]
- header_row.insert(0, display.box("", header=True))
+ header_row = [display.box("", header=True)]
+ for column in column_list:
+ header_row.append(display.box(column, header=True))
matrix = [header_row]
+ for row in row_list:
+ cur_row = [display.box(row)]
+ for column in column_list:
+ try:
+ box_data = data[column][row]
+ except:
+ cur_row.append(display.box(None, None))
+ continue
+ cur_row.append(display.status_precounted_box(db,
+ box_data,
+ ""))
+ matrix.append(cur_row)
- # get all the tests that satify the given condition.
- tests = query_lib.get_tests(condition_sql, condition_value)
+ return matrix
- for r_group in row_groups:
- row = [display.box(r_group.name)]
- # build the row sql for this row.
- row_expr = [ " %s = %%s " % r_group.idx_name for val in r_group.idx_value]
- row_sql = " (%s) " % " or ".join(row_expr)
-
- # get individual unit values
- for c_group in column_groups:
- # get the list of tests that belong to this x,y in the matrix.
- xy_test = [test for test in tests
- if query_lib.get_value(test, r_group.idx_name) \
- in r_group.idx_value \
- and query_lib.get_value(test,c_group.idx_name) \
- in c_group.idx_value]
-
- # build the column sql
- column_expr = [ " %s = %%s " % c_group.idx_name for val in c_group.idx_value]
- column_sql = " (%s) " % " or ".join(column_expr)
-
- sql = "t where %s and %s " % (row_sql, column_sql)
-
- # add the corresponding values of the fields to
- # the value list.
-
- value = []
- value.extend(r_group.idx_value)
- value.extend(c_group.idx_value)
-
- # append the condition sql and the values to the
- # sql/list respectively.
- if condition_sql:
- sql += " and "
- sql += condition_sql
- value.extend(condition_value)
-
- value_str = [str(val) for val in value]
- link = 'test.cgi?sql=%s&values=%s' % \
- (sql, ','.join(value_str))
- row.append(display.status_count_box(db, xy_test, link))
- matrix.append(row)
-
+def main():
# create the actual page
- condition_str = condition
- if condition_str == None:
- condition_str = ""
print '<html><head><title>'
print 'Filtered Autotest Results'
print '</title></head><body>'
- print html_header % (create_select_options(columns, columns_default),
- create_select_options(rows, rows_default),
- condition_str)
- display.print_table(matrix)
+ print html_header % (create_select_options(column_field),
+ create_select_options(row_field),
+ condition_field)
+ display.print_table(gen_matrix())
print '</body></html>'
diff --git a/tko/db.py b/tko/db.py
index 0ccd753..1cce50f 100644
--- a/tko/db.py
+++ b/tko/db.py
@@ -62,7 +62,20 @@
def select(self, fields, table, where, wherein={}, distinct = False,
group_by = None):
"""\
- select fields from table where {dictionary}
+ This selects all the fields requested from a
+ specific table with a particular where clause.
+ The where clause can either be a dictionary of
+ field=value pairs, a string, or a tuple of (string,
+ a list of values). The last option is what you
+ should use when accepting user input as it'll
+ protect you against sql injection attacks (if
+ all user data is placed in the array rather than
+ the raw SQL).
+
+ For example:
+ where = ("a = %s AND b = %s", ['val', 'val'])
+ is better than
+ where = "a = 'val' AND b = 'val'"
"""
cmd = ['select']
if distinct:
@@ -71,13 +84,21 @@
values = []
if where and isinstance(where, types.DictionaryType):
+ # key/value pairs (which should be equal)
keys = [field + '=%s' for field in where.keys()]
values = [where[field] for field in where.keys()]
cmd.append(' where ' + ' and '.join(keys))
elif where and isinstance(where, types.StringTypes):
+ # the exact string
cmd.append(' where ' + where)
+ elif where and isinstance(where, types.TupleType):
+ # preformatted where clause + values
+ (sql, vals) = where
+ values = vals
+ cmd.append(' where (%s) ' % sql)
+ # TODO: this assumes there's a where clause...bad
if wherein and isinstance(wherein, types.DictionaryType):
keys_in = []
for field_in in wherein.keys():
diff --git a/tko/frontend.py b/tko/frontend.py
index 7b610a4..09f107b 100755
--- a/tko/frontend.py
+++ b/tko/frontend.py
@@ -72,13 +72,19 @@
rows = db.select(fields, 'test_view', where=where, group_by=group_by)
data = {}
+ x_set = set()
+ y_set = set()
+ status_set = set()
for (x, y, status, count) in rows:
if not data.has_key(x):
data[x] = {}
if not data[x].has_key(y):
data[x][y] = {}
data[x][y][status] = count
- return data
+ x_set.add(x)
+ y_set.add(y)
+ status_set.add(status)
+ return (data, list(x_set), list(y_set), list(status_set))
class anygroup:
diff --git a/tko/index.html b/tko/index.html
index 1d4d579..8afb843 100644
--- a/tko/index.html
+++ b/tko/index.html
@@ -10,12 +10,13 @@
<SELECT NAME="columns">
<OPTION VALUE="kernel">kernel
<OPTION VALUE="hostname">hostname
- <OPTION VALUE="user">user
<OPTION VALUE="test">test
<OPTION VALUE="label">label
<OPTION VALUE="machine_group">machine_group
- <OPTION VALUE="status">status
<OPTION VALUE="reason">reason
+ <OPTION VALUE="tag">tag
+ <OPTION VALUE="user">user
+ <OPTION VALUE="status">status
</SELECT>
</td>
</tr>
@@ -23,14 +24,15 @@
<td>Row: </td>
<td>
<SELECT NAME="rows">
- <OPTION VALUE="test">test
<OPTION VALUE="kernel">kernel
<OPTION VALUE="hostname">hostname
- <OPTION VALUE="user">user
+ <OPTION VALUE="test" SELECTED>test
<OPTION VALUE="label">label
<OPTION VALUE="machine_group">machine_group
- <OPTION VALUE="status">status
<OPTION VALUE="reason">reason
+ <OPTION VALUE="tag">tag
+ <OPTION VALUE="user">user
+ <OPTION VALUE="status">status
</SELECT>
</td>
</tr>
@@ -58,7 +60,7 @@
<ul>
<li>user='johnmacdonald' & test='burnin'</li>
<li>hostname='bdpk1' & user='yinghan'</li>
-<li>tag='134-lesliele%%' to search for job '134-lesliele'</li>
+<li>tag~'134-jorlow%' to search for job '134-jorlow'</li>
</ul>
</p>
</td>
diff --git a/tko/machine_kernel.cgi b/tko/machine_kernel.cgi
index 588e2c4..f9ffc7d 100755
--- a/tko/machine_kernel.cgi
+++ b/tko/machine_kernel.cgi
@@ -28,7 +28,8 @@
def main():
display.print_main_header()
- data = frontend.get_matrix_data(db, 'machine_group', 'kernel_printable')
+ ret = frontend.get_matrix_data(db, 'machine_group', 'kernel_printable')
+ (data, group_list, kernel_list, status_list) = ret
groups = frontend.group.select(db)
group_names = [display.group_name(g) for g in groups]
diff --git a/tko/query_lib.py b/tko/query_lib.py
index 402142b..e20a667 100644
--- a/tko/query_lib.py
+++ b/tko/query_lib.py
@@ -14,6 +14,85 @@
db = db.db()
+def dprint(str):
+ pass
+ #print "! %s<br>" % str
+
+def parse_scrub_and_gen_condition(condition, valid_field_dict):
+ me = parse_scrub_and_gen_condition # shorten the name
+ compare_ops = {'=':'=', '<>':'<>', '==':'=', '!=':'<>', '>':'>',
+ '<':'<', '>=':'>=', '<=':'<=', '~':'LIKE', '#':'REGEXP'}
+
+ # strip white space
+ condition = condition.strip()
+
+ # ()'s
+ #match = re.match(r'^[(](.+)[)]$', condition)
+ #if match:
+ # dprint("Matched () on %s" % condition)
+ # depth = 0
+ # for c in match.group(1):
+ # if c == '(': depth += 1
+ # if c == ')': depth -= 1
+ # if depth < 0: break
+ # dprint("Depth is %d" % depth)
+ # if depth == 0:
+ # dprint("Match...stripping ()'s")
+ # return me(match.group(1), valid_field_dict)
+
+ # OR
+ match = re.match(r'^(.+)[|](.+)$', condition)
+ if match:
+ dprint("Matched | on %s" % condition)
+ (a_sql, a_values) = me(match.group(1), valid_field_dict)
+ (b_sql, b_values) = me(match.group(2), valid_field_dict)
+ return (" (%s) OR (%s) " % (a_sql, b_sql),
+ a_values + b_values)
+
+ # AND
+ match = re.match(r'^(.+)[&](.+)$', condition)
+ if match:
+ dprint("Matched & on %s" % condition)
+ (a_sql, a_values) = me(match.group(1), valid_field_dict)
+ (b_sql, b_values) = me(match.group(2), valid_field_dict)
+ return (" (%s) AND (%s) " % (a_sql, b_sql),
+ a_values + b_values)
+
+ # NOT
+ #match = re.match(r'^[!](.+)$', condition)
+ #if match:
+ # dprint("Matched ! on %s" % condition)
+ # (sql, values) = me(match.group(1), valid_field_dict)
+ # return (" NOT (%s) " % (sql,), values)
+
+ # '<field> <op> <value>' where value can be quoted
+ # double quotes are escaped....i.e. '''' is the same as "'"
+ regex = r'^(%s)[ \t]*(%s)[ \t]*' + \
+ r'(\'((\'\'|[^\'])*)\'|"((""|[^"])*)"|([^\'"].*))$'
+ regex = regex % ('|'.join(valid_field_dict.keys()),
+ '|'.join(compare_ops.keys()))
+ match = re.match(regex, condition)
+ if match:
+ field = valid_field_dict[match.group(1)]
+ op = compare_ops[match.group(2)]
+ if match.group(5):
+ val = match.group(4).replace("''", "'")
+ elif match.group(7):
+ val = match.group(6).replace('""', '"')
+ elif match.group(8):
+ val = match.group(8)
+ else:
+ raise "Internal error"
+ return ("%s %s %%s" % (field, op), [val])
+
+
+ raise "Could not parse '%s' (%s)" % (condition, regex)
+
+
+###
+### Everything past here is depricated.
+###
+
def generate_sql_condition(condition_list):
""" generate the sql for the condition list."""
sql = ''