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: http://test.kernel.org/svn/autotest/trunk@908 592f7852-d20e-0410-864c-8624ca9c26a4
diff --git a/tko/compose_query.cgi b/tko/compose_query.cgi
index b8ec758..460513b 100644
--- a/tko/compose_query.cgi
+++ b/tko/compose_query.cgi
@@ -14,71 +14,11 @@
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 @@
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 @@
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 @@
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 22e6be9..df1fa49 100755
--- a/tko/frontend.py
+++ b/tko/frontend.py
@@ -55,7 +55,18 @@
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 @@
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 0000000..402142b
--- /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 0000000..e57264e
--- /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)