cli/query_results

#!/usr/bin/python
"""
Selects all rows and columns that satisfy the condition specified
and prints the matrix.
"""
import sys, os, re, optparse
import common
from autotest_lib.cli import rpc
from autotest_lib.database import database_connection
from autotest_lib.tko import display, frontend, db, query_lib
from autotest_lib.client.common_lib import kernel_versions


# First do all the options parsing
parser = optparse.OptionParser()
parser.add_option('-C', '--columns', action='store', dest='columns',
            default='*', help="""\
By default or when using the -c flag: 
kernel hostname test label machine_group reason tag user status

OR

When using the -w flag:
test_idx test_name reason test_started_time test_finished_time job_tag job_name job_owner job_queued_time job_started_time job_finished_time hostname platform kernel status""")

parser.add_option('-c', '--condition', action='store', dest='old_condition',
            help=("The WHERE condition for the query written in the 'old style' "
                  "condition syntax for the original tko"))
parser.add_option('-w', '--where', action='store', dest='new_condition',
            help=("The WHERE condition for the query witten in the 'new style' "
                  "condition syntax for new tko (see "
                  "http://autotest.kernel.org/wiki/TkoHowTo  for more info)"))
parser.add_option('-s', '--separator', action='store', default = ' | ',
            dest='separator', help = 'output separator')
parser.add_option('-n', '--nocount', action='store_true', default=False,
                  help='Do not display line counts before each line')
parser.add_option('-l', '--logpath', action='store_true', default=False,
                  help='Reformats the the tag column into a URL \
                        like http://autotest/results/[tag]. \
                        This will append the tag column if it isn\'t provided.')
parser.add_option('--host-label', action='store', dest='host_label',
                  help=('Return results only for machines currently '
                        'in the specified label'))

(options, args) = parser.parse_args()

if options.old_condition and options.new_condition:
    msg = 'You cannot specify WHERE clauses in both the old and new style.'
    parser.error(msg)
elif options.old_condition:
    where = query_lib.parse_scrub_and_gen_condition(
                options.old_condition, frontend.test_view_field_dict)
    view = 'test_view'
    tag = 'tag'
elif options.new_condition:
    where = options.new_condition.replace('%', '%%')
    view = 'test_view_2'
    tag = 'job_tag'
else:
    parser.error('You must specify at least one condition.')

columns = options.columns.split(',')

url_prefix = rpc.get_autotest_server() + '/results/'
if options.logpath:
    if tag not in columns:
        columns.append(tag)
    tag_index=columns.index(tag)

if options.old_condition:
    columns = [frontend.test_view_field_dict.get(field, field)
               for field in columns]

if options.host_label:
    database = database_connection.DatabaseConnection("AUTOTEST_WEB")
    database.connect()
    sql = ("SELECT hostname FROM labels JOIN hosts_labels "
           "ON labels.id=hosts_labels.label_id JOIN hosts "
           "ON hosts_labels.host_id=hosts.id WHERE name=%s")
    results = database.execute(sql, options.host_label)
    hosts = [row[0] for row in results]
    where += " AND hostname IN ('" + "','".join(hosts) + "')"

# Grab the data
db = db.db()
count = 0
for row in db.select(','.join(columns), view, where):
    values = [str(x) for x in row]
    if options.logpath:
        values[tag_index] = url_prefix + values[tag_index]
    if not options.nocount:
        print '[%d] ' % count,
        count += 1
    print options.separator.join(values)