1. Fix duplicate entries showing up in the table.

2. Modify the code to make only one sql query and then process the info than mak
e one sql query per xy in a table.
3. Add reporting clii.
4. Also move the common libraries used by the cli and web interface into query_l
ib.py.

From: Radha Ramachandran <radha@google.com>
Signed-off-by: Martin Bligh <mbligh@google.com>



git-svn-id: svn://test.kernel.org/autotest/trunk@908 592f7852-d20e-0410-864c-8624ca9c26a4

4 files changed, 416 insertions, 73 deletions

diff --git a/tko/compose_query.cgi b/tko/compose_query.cgi
index b8ec758f..460513b3 100644
--- a/tko/compose_query.cgi
+++ b/tko/compose_query.cgi
@@ -14,71 +14,11 @@ import sys, os
 tko = os.path.dirname(os.path.realpath(os.path.abspath(sys.argv[0])))
 sys.path.insert(0, tko)
 
-import display, frontend, db
+import display, frontend, db, query_lib
 
 cgitb.enable()
 db = db.db()
 
-def generate_sql_condition(condition_list):
-	""" generate the sql for the condition list."""
-	sql = ""
-	value = []
-	for field, operator, values in condition_list:
-		if len(values) == 1:
-			sql += " and %s%s%%s" % (field, operator)
-			value.append(values[0][0])
-		elif len(values) > 1:
-			sql += " and "
-			expression = [" %s %s %%s" % (field, operator) for val in values]
-			for val in values:
-				value.append(val[0])
-			sql += "(%s)" % " or ".join(expression)
-	return sql, value
-
-
-def prune_list(thelist, condition_sql, condition_value):
-	""" keep track of which columns do not have any elements."""
-	pruned_list = []
-	for g in thelist:
-		sql = "t where %s=%%s " % g.idx_name
-		value = [g.idx_value]
-		sql += condition_sql
-		value.extend(condition_value)
-		tests = frontend.test.select_sql(db, sql, value)
-		if len(tests) > 0:
-			pruned_list.append(g)
-	return pruned_list
-
-
-def ParseCondition(condition):
-	""" parse the condition into independent clauses."""
-	condition_list = []
-	if not condition:
-		return condition_list
-	attribute_re = r"(\w+)"
-	op_re = r"(=|!=)"
-	value_re = r"('[^']*')"
-	# condition is clause & clause & ..
-	clause_re = r"%s\s*%s\s*%s" % (attribute_re, op_re, value_re)
-	condition_re = re.compile(r"^\s*%s(\s*&\s*%s)*\s*$" % (clause_re, clause_re))
-	if not condition_re.match(condition):
-		print "Condition not in the correct format: %s" % condition
-		sys.exit(0)
-	triples = []
-	for clause in [c.strip() for c in condition.split('&')]:
-		attribute, op, value = re.match(clause_re, clause).groups()
-		triples.append((attribute, op, value))
-	for (field_name, operator, value) in triples:
-		match, field = frontend.select(db, field_name, value)
-		if len(match) > 0:
-			condition_list.append((field, operator, match))
-		else:
-			print "No matching records found for condition %s." % \
-			      condition
-			sys.exit(0)
-	return condition_list
-
-
 def main():
 
 	# parse the fields from the form.
@@ -99,8 +39,9 @@ def main():
 	condition_sql = ""
 	condition_value = []
 	if condition:
-		condition_list = ParseCondition(condition)
-		condition_sql, condition_value = generate_sql_condition(condition_list)
+		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)
@@ -109,8 +50,10 @@ def main():
 	row_groups = frontend.anygroup.selectunique(db,rows)
 	# keep only those values in rows/columns that have a test
 	# corresponding to it.
-	row_groups = prune_list(row_groups, condition_sql, condition_value)
-	column_groups = prune_list(column_groups, condition_sql, condition_value)
+	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]
@@ -120,20 +63,49 @@ def main():
 
 	matrix = [header_row]
 
+	# get all the tests that satify the given condition.
+	tests = query_lib.get_tests(condition_sql, condition_value)
+
 	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:
-			sql = "t where %s=%%s and %s=%%s" % (r_group.idx_name,
-							     c_group.idx_name)
-			value = [r_group.idx_value, c_group.idx_value]
-			sql += condition_sql
-			value.extend(condition_value)
-			tests = frontend.test.select_sql(db, sql, value)
+			# 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, tests, link))
+			row.append(display.status_count_box(db, xy_test, link))
 		matrix.append(row)
 	display.print_table(matrix)
 
diff --git a/tko/frontend.py b/tko/frontend.py
index 22e6be9e..df1fa49c 100755
--- a/tko/frontend.py
+++ b/tko/frontend.py
@@ -55,7 +55,18 @@ class anygroup:
 		 the table."""
 		rows, field_name_in_main_table = select(db, field, value=None, distinct=True)
 		groupnames = sorted([row for row in rows])
-		return [klass(db, field_name_in_main_table, groupname) for groupname in groupnames]
+
+		# collapse duplicates where records have the same name but
+		# multiple index values
+		headers = {}
+		for field_name, idx_value in groupnames:
+			if headers.has_key(field_name):
+				headers[field_name].append(idx_value)
+			else:
+				headers[field_name] = [idx_value]
+		headers = headers.items()
+		headers.sort()
+		return [klass(db, field_name_in_main_table, groupname) for groupname in headers]
 
 
 	def __init__(self, db, idx_name, name):
@@ -213,6 +224,7 @@ class job:
 		if not rows:
 			return None
 		(self.tag, self.machine_idx) = rows[0]
+		self.job_idx = job_idx
 
  
 class iteration:
diff --git a/tko/query_lib.py b/tko/query_lib.py
new file mode 100644
index 00000000..402142b2
--- /dev/null
+++ b/tko/query_lib.py
@@ -0,0 +1,110 @@
+#!/usr/bin/python
+"""
+This library provides a bunch of miscellaneous parameter parsing,
+sql generating and list cleanup library functions that are used
+by both the reporting cli and web interface.
+"""
+
+import sys, os, re
+
+tko = os.path.dirname(os.path.realpath(os.path.abspath(sys.argv[0])))
+sys.path.insert(0, tko)
+
+import display, frontend, db
+
+db = db.db()
+
+def generate_sql_condition(condition_list):
+	""" generate the sql for the condition list."""
+	sql = ''
+	value = []
+	for field, operator, values in condition_list:
+		if len(values) == 1:
+			if sql != '':
+				sql += " and "
+			sql += " %s%s%%s" % (field, operator)
+			value.append(values[0][0])
+		elif len(values) > 1:
+			expression = [" %s %s %%s" % (field, operator) for val in values]
+			for val in values:
+				value.append(val[0])
+			if sql != '':
+				sql += " and "
+			sql += "(%s)" % " or ".join(expression)
+	return sql, value
+
+
+def prune_list(thelist, condition_sql, condition_value):
+	""" keep track of which columns do not have any elements."""
+	pruned_list = []
+	for g in thelist:
+		# check for multiple index values in the db.
+		sql = "t where "
+		expr = [" %s = %%s" % (g.idx_name) for val in g.idx_value]
+		sql += " (%s) " % " or ".join(expr)
+		value = []
+		value.extend(g.idx_value)
+		if condition_sql:
+			sql += " and "
+			sql += condition_sql
+			value.extend(condition_value)
+		tests = frontend.test.select_sql(db, sql, value)
+		if len(tests) > 0:
+			pruned_list.append(g)
+	return pruned_list
+
+
+def parse_condition(condition):
+	""" parse the condition into independent clauses."""
+	condition_list = []
+	if not condition:
+		return condition_list
+	attribute_re = r"(\w+)"
+	op_re = r"(=|!=)"
+	value_re = r"('[^']*')"
+	# condition is clause & clause & ..
+	clause_re = r"%s\s*%s\s*%s" % (attribute_re, op_re, value_re)
+	condition_re = re.compile(r"^\s*%s(\s*&\s*%s)*\s*$" % (clause_re, clause_re))
+	if not condition_re.match(condition):
+		print "Condition not in the correct format: %s" % condition
+		sys.exit(0)
+	triples = []
+	for clause in [c.strip() for c in condition.split('&')]:
+		attribute, op, value = re.match(clause_re, clause).groups()
+		triples.append((attribute, op, value))
+	for (field_name, operator, value) in triples:
+		match, field = frontend.select(db, field_name, value, distinct=True)
+		if len(match) > 0:
+			condition_list.append((field, operator, match))
+		else:
+			print "No matching results found for condition %s." % \
+			      condition
+			sys.exit(0)
+	return condition_list
+
+
+def get_value(test, field):
+	""" get specific field values from the given test object."""
+	if field == 'test':
+		return test.testname
+	elif field == 'kernel_idx':
+		return test.kernel_idx
+	elif field == 'machine_idx':
+		return test.machine_idx
+	elif field == 'status':
+		return test.status_num
+
+
+def get_tests(condition_sql, condition_value):
+	# get all the tests that satify the given condition.
+	if condition_sql:
+		sql = "t where "
+		sql += condition_sql
+		value = [str(val) for val in condition_value]
+		#print sql , value
+		tests = frontend.test.select_sql(db, sql, value)
+	else:
+		sql = None
+		value = None
+		tests = frontend.test.select_sql(db, " t ", None)
+	return tests
diff --git a/tko/report.py b/tko/report.py
new file mode 100644
index 00000000..e57264ea
--- /dev/null
+++ b/tko/report.py
@@ -0,0 +1,249 @@
+#!/usr/bin/python
+
+"""
+CLI support to enable user to query the database.
+"""
+
+import sys, os, getopt
+
+tko = os.path.dirname(os.path.realpath(os.path.abspath(sys.argv[0])))
+sys.path.insert(0, tko)
+
+import query_lib, db, frontend
+
+db = db.db()
+
+help_msg_header = """
+NAME
+report.py - Print the results matching a given condition in the specified format.
+
+SYNOPSIS
+report.py [options]
+
+OPTIONS
+"""
+
+help_msg_trailer = """
+EXAMPLES
+To see every job that has ever been run:
+  report.py
+
+To see all the jobs started by johnmacdonald:
+  report.py --condition="user='johnmacdonald'"
+
+To see all the jobs started by johnmandonald and on hostname arh22:
+  report.py --condition="user='johnmacdonald' & hostname='arh22'"
+
+To see only the test, hostname and user for the reports:
+  report.py --columns="test, hostname, user"
+
+You can use both the columns and condition options to generate the kind of report you want.
+"""
+
+condition_desc = """Condition to filter the results with.
+		  Supported fields are: test, hostname, user, label, machine_group, status, reason, kernel.
+		  Supported operators are =, != and string values must be quoted within single quotes.
+"""
+
+columns_desc = """Specific columns to display in the results.
+		  Supported fields are: test, hostname, user, label, machine_group, status, reason, kernel.
+"""
+
+help_desc = """Print command help.
+"""
+
+
+class CliError(Exception):
+	pass
+
+
+class InvalidArgsError(CliError):
+	def __init__(self, error):
+		CliError.__init__(self, 'Unknown arguments: %r\nTry report.py --help' % error)
+
+
+class InvalidColumnValue(CliError):
+	def __init__(self, error):
+		CliError.__init__(self, 'Unrecognized column value: %r\nTry report.py --help' % error)
+
+
+class cli:
+	def __init__(self):
+		self.__options = {}
+
+
+	def add_option(self, name=None, short_name=None, type=None,
+		      description=None, value=None):
+		""" Adds the options to the cli.
+		"""
+		if not name and not short_name:
+			raise Error("No name provided for the option.")
+
+		short = False
+
+		if not name and short_name:
+			short = True
+			name = short_name
+
+		self.__options[name] = dict(name=name, type=type,
+				            description=description,
+					    value=value, short=short)
+
+
+	def list_options(self):
+		""" Return the options for this cli.
+		"""
+		return self.__options
+
+
+	def parse_options(self, args):
+		""" Parse the options and the values the cli is invoked with.
+		"""
+		short_opts = ""
+		long_opts = []
+		for name,val in self.__options.items():
+			if val['short']:
+				short_opts += val['name']
+				if val['type'] != 'bool':
+					short_opts += ':'
+			else:
+				opt = val['name']
+				if val['type'] != 'bool':
+					opt += '='
+				long_opts.append(opt)
+
+		opts, args = getopt.getopt(args[1:], short_opts, long_opts)
+		return opts, args
+
+
+	def usage(self):
+		""" Help for the cli.
+		"""
+		msg = help_msg_header
+		for opt,value in self.__options.items():
+			if value['short']:
+				msg += '-'
+			else:
+				msg += '--'
+			msg += '%s \t: %s\n' % (value['name'], value['description'])
+
+		msg += help_msg_trailer
+		return msg
+
+
+def pretty_print(header, results):
+	""" pretty prints the result with all the proper space indentations.
+	"""
+	# number of columns in the results table.
+	size = len(header)
+
+	# list containing the max width of each column.
+	column_width = [len(col_name) for col_name in header]
+
+	# update the column width based on the values in the table.
+	for record in results:
+		for i in xrange(size):
+			column_width[i] = max(column_width[i], len(record[i]))
+
+	# Generates the header.
+	lines = []
+	lines.append('  '.join([header[i].capitalize().ljust(column_width[i])
+						     for i in xrange(size)]))
+	lines.append('  '.join(['-' * c_size for c_size in column_width]))
+
+	# Generates the table with the appropriate space indent.
+	for record in results:
+		lines.append('  '.join([record[i].ljust(column_width[i])
+                            	for i in xrange(size)]))
+
+	return '\n'.join(lines)
+
+
+def main(args):
+	cli_obj = cli()
+
+	# Add all the known and acceptable options.
+	cli_obj.add_option(name='condition', type='string',
+			description=condition_desc)
+	cli_obj.add_option(name='columns', type='string',
+			description=columns_desc)
+	cli_obj.add_option(name='help', type='bool',
+			  description=help_desc)
+
+	# Parse the options.
+	opts,args = cli_obj.parse_options(args)
+
+	# unexpected argument.
+	if args:
+		raise InvalidArgsError(args)
+
+	sql = None
+	value = None
+
+	# by default display these columns
+	requested_columns = ['test', 'hostname', 'status', 'reason']
+
+	for option, value in opts:
+		if option == '--help':
+			print cli_obj.usage()
+			return
+		elif option == '--condition':
+			condition_list = query_lib.parse_condition(value.strip('"'))
+			sql, value = query_lib.generate_sql_condition(condition_list)
+		elif option == '--columns':
+			supported_columns = ['test', 'hostname', 'user', 'label',
+					     'machine_group', 'status', 'reason', 'kernel']
+			requested_columns = [x.strip() for x in value.split(',')]
+			for col in requested_columns:
+				if col not in supported_columns:
+					raise InvalidColumnValue, 'Unknown field %s specified in the columns option' % col
+
+	# get the values corresponding to the index fields.
+	col_values = {}
+	for col in requested_columns:
+		if col != 'test' and col != 'status' and col != 'reason':
+			# the rest of the columns need the index values.
+			col_group = frontend.anygroup.selectunique(db, col)
+			col_value, field_name = frontend.select(db, col)
+			col_values[col] = list(col_value)
+
+	# get all the tests that satisfy the given conditions.
+	tests = query_lib.get_tests(sql, value)
+
+	# accumulate the fields that are of interest to the user.
+	result = []
+
+	for test in tests:
+		record = []
+
+		test_values = {}
+		test_values['hostname'] = test.machine_idx
+		test_values['user'] = test.job.job_idx
+		test_values['label'] = test.job.job_idx
+		test_values['machine_group'] = test.machine_idx
+		test_values['kernel'] = test.kernel_idx
+
+		for col in requested_columns:
+			if col == 'test':
+				record.append(test.testname)
+			elif col == 'status':
+				record.append(test.status_word)
+			elif col == 'reason':
+				record.append(test.reason.strip())
+			else:
+				column = col_values[col]
+				found = False
+				for idx_name, idx_value in column:
+					if idx_value == test_values[col]:
+						record.append(idx_name)
+						found = True
+						break
+				if not found:
+					record.append('')
+		result.append(record)
+
+	# generate the pretty table.
+	print pretty_print(requested_columns, result)
+
+
+main(sys.argv)