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: svn://test.kernel.org/autotest/trunk@1100 592f7852-d20e-0410-864c-8624ca9c26a4
author: mbligh <mbligh@592f7852-d20e-0410-864c-8624ca9c26a4> 2008-01-03 02:01:53 +0000
committer: mbligh <mbligh@592f7852-d20e-0410-864c-8624ca9c26a4> 2008-01-03 02:01:53 +0000
commit: 54f64fe79bd131e4582ad7126c7d07b84ee8ac61 (patch)
tree: f1c5b95e85fb8300a169d1d6b334954d8e585f54
parent: df93225e1d7ad7f1e89dbf9c751bccf86fd4e0ce (diff)
6 files changed, 221 insertions, 107 deletions
diff --git a/tko/compose_query.cgi b/tko/compose_query.cgi
index 86240fcb..96f8489b 100644
--- a/tko/compose_query.cgi
+++ b/tko/compose_query.cgi
@@ -15,6 +15,10 @@ tko = os.path.dirname(os.path.realpath(os.path.abspath(sys.argv[0])))
 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 @@ html_header = """\
   </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 @@ html_header = """\
 </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):
-	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)
+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"
 
-	for option in option_list:
+
+def create_select_options(selected_val):
+	ret = ""
+
+	for option in sorted(field_dict.keys()):
 		if selected_val == option:
 			selected = " SELECTED"
 		else:
@@ -75,107 +122,65 @@ def create_select_options(selected_val, default_val):
 	return ret
 
 
-def main():
-	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
-
-	# 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)
-
-	# get all possible column values.
-	column_groups = frontend.anygroup.selectunique(db, columns)
-
-	# 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)
-
-	# 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))
-
-	matrix = [header_row]
-
-	# get all the tests that satify the given condition.
-	tests = query_lib.get_tests(condition_sql, condition_value)
+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()
 
-	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)
+def gen_matrix():
+	display.print_main_header()
 
-		# 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]
+	where = None
+	if condition_field.strip() != '':
+		where = query_lib.parse_scrub_and_gen_condition(
+		            condition_field, field_dict)
+		print "<!-- where clause: %s -->" % (where,)
 
-			# 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)
+	ret = frontend.get_matrix_data(db, field_dict[column_field],
+	                               field_dict[row_field], where)
+	(data, column_list, row_list, stat_list) = ret
 
-			sql = "t where %s and %s " % (row_sql, column_sql)
+	if not row_list:
+		msg = "There are no results for this query (yet?)."
+		return [[display.box(msg)]]
 
-			# add the corresponding values of the fields to
-			# the value list.
+	smart_sort(row_list, row_field)
+	smart_sort(column_list, column_field)
 
-			value = []
-			value.extend(r_group.idx_value)
-			value.extend(c_group.idx_value)
+	header_row = [display.box("", header=True)]
+	for column in column_list:
+		header_row.append(display.box(column, header=True))
 
-			# append the condition sql and the values to the
-			# sql/list respectively.
-			if condition_sql:
-				sql += " and "
-				sql += condition_sql
-				value.extend(condition_value)
+	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)
+
+	return matrix
 
-			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 0ccd7533..1cce50f8 100644
--- a/tko/db.py
+++ b/tko/db.py
@@ -62,7 +62,20 @@ class db_sql:
 	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 @@ class db_sql:
 
 		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 7b610a45..09f107bb 100755
--- a/tko/frontend.py
+++ b/tko/frontend.py
@@ -72,13 +72,19 @@ def get_matrix_data(db, x_axis, y_axis, where = None):
 	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 1d4d5798..8afb8439 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 @@ Textual values must be quoted.
 <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 588e2c42..f9ffc7d4 100755
--- a/tko/machine_kernel.cgi
+++ b/tko/machine_kernel.cgi
@@ -28,7 +28,8 @@ def kernel_encode(kernel):
 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 402142b2..e20a6671 100644
--- a/tko/query_lib.py
+++ b/tko/query_lib.py
@@ -14,6 +14,85 @@ import display, frontend, db
 
 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 = ''
author	mbligh <mbligh@592f7852-d20e-0410-864c-8624ca9c26a4>	2008-01-03 02:01:53 +0000
committer	mbligh <mbligh@592f7852-d20e-0410-864c-8624ca9c26a4>	2008-01-03 02:01:53 +0000
commit	54f64fe79bd131e4582ad7126c7d07b84ee8ac61 (patch)
tree	f1c5b95e85fb8300a169d1d6b334954d8e585f54
parent	df93225e1d7ad7f1e89dbf9c751bccf86fd4e0ce (diff)