From c2912158ade4f5be3a26e1f327bc7860f7207ab0 Mon Sep 17 00:00:00 2001 From: mbligh Date: Mon, 5 Nov 2007 19:24:51 +0000 Subject: 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 Signed-off-by: Martin Bligh git-svn-id: svn://test.kernel.org/autotest/trunk@908 592f7852-d20e-0410-864c-8624ca9c26a4 --- tko/compose_query.cgi | 116 +++++++++-------------- tko/frontend.py | 14 ++- tko/query_lib.py | 110 ++++++++++++++++++++++ tko/report.py | 249 ++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 416 insertions(+), 73 deletions(-) create mode 100644 tko/query_lib.py create mode 100644 tko/report.py 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) -- cgit v1.2.3