1import pickle, datetime, itertools, operator
2from django.db import models as dbmodels
3from autotest_lib.client.common_lib import priorities
4from autotest_lib.frontend.afe import rpc_utils, model_logic
5from autotest_lib.frontend.afe import models as afe_models, readonly_connection
6from autotest_lib.frontend.tko import models, tko_rpc_utils, graphing_utils
7from autotest_lib.frontend.tko import preconfigs
8
9# table/spreadsheet view support
10
11def get_test_views(**filter_data):
12    return rpc_utils.prepare_for_serialization(
13        models.TestView.list_objects(filter_data))
14
15
16def get_num_test_views(**filter_data):
17    return models.TestView.query_count(filter_data)
18
19
20def get_group_counts(group_by, header_groups=None, fixed_headers=None,
21                     extra_select_fields=None, **filter_data):
22    """
23    Queries against TestView grouping by the specified fields and computings
24    counts for each group.
25    * group_by should be a list of field names.
26    * extra_select_fields can be used to specify additional fields to select
27      (usually for aggregate functions).
28    * header_groups can be used to get lists of unique combinations of group
29      fields.  It should be a list of tuples of fields from group_by.  It's
30      primarily for use by the spreadsheet view.
31    * fixed_headers can map header fields to lists of values.  the header will
32      guaranteed to return exactly those value.  this does not work together
33      with header_groups.
34
35    Returns a dictionary with two keys:
36    * header_values contains a list of lists, one for each header group in
37      header_groups.  Each list contains all the values for the corresponding
38      header group as tuples.
39    * groups contains a list of dicts, one for each row.  Each dict contains
40      keys for each of the group_by fields, plus a 'group_count' key for the
41      total count in the group, plus keys for each of the extra_select_fields.
42      The keys for the extra_select_fields are determined by the "AS" alias of
43      the field.
44    """
45    query = models.TestView.objects.get_query_set_with_joins(filter_data)
46    # don't apply presentation yet, since we have extra selects to apply
47    query = models.TestView.query_objects(filter_data, initial_query=query,
48                                          apply_presentation=False)
49    count_alias, count_sql = models.TestView.objects.get_count_sql(query)
50    query = query.extra(select={count_alias: count_sql})
51    if extra_select_fields:
52        query = query.extra(select=extra_select_fields)
53    query = models.TestView.apply_presentation(query, filter_data)
54
55    group_processor = tko_rpc_utils.GroupDataProcessor(query, group_by,
56                                                       header_groups or [],
57                                                       fixed_headers or {})
58    group_processor.process_group_dicts()
59    return rpc_utils.prepare_for_serialization(group_processor.get_info_dict())
60
61
62def get_num_groups(group_by, **filter_data):
63    """
64    Gets the count of unique groups with the given grouping fields.
65    """
66    query = models.TestView.objects.get_query_set_with_joins(filter_data)
67    query = models.TestView.query_objects(filter_data, initial_query=query)
68    return models.TestView.objects.get_num_groups(query, group_by)
69
70
71def get_status_counts(group_by, header_groups=[], fixed_headers={},
72                      **filter_data):
73    """
74    Like get_group_counts, but also computes counts of passed, complete (and
75    valid), and incomplete tests, stored in keys "pass_count', 'complete_count',
76    and 'incomplete_count', respectively.
77    """
78    return get_group_counts(group_by, header_groups=header_groups,
79                            fixed_headers=fixed_headers,
80                            extra_select_fields=tko_rpc_utils.STATUS_FIELDS,
81                            **filter_data)
82
83
84def get_latest_tests(group_by, header_groups=[], fixed_headers={},
85                     extra_info=[], **filter_data):
86    """
87    Similar to get_status_counts, but return only the latest test result per
88    group.  It still returns the same information (i.e. with pass count etc.)
89    for compatibility.  It includes an additional field "test_idx" with each
90    group.
91    @param extra_info a list containing the field names that should be returned
92                      with each cell. The fields are returned in the extra_info
93                      field of the return dictionary.
94    """
95    # find latest test per group
96    initial_query = models.TestView.objects.get_query_set_with_joins(
97            filter_data)
98    query = models.TestView.query_objects(filter_data,
99                                          initial_query=initial_query,
100                                          apply_presentation=False)
101    query = query.exclude(status__in=tko_rpc_utils._INVALID_STATUSES)
102    query = query.extra(
103            select={'latest_test_idx' : 'MAX(%s)' %
104                    models.TestView.objects.get_key_on_this_table('test_idx')})
105    query = models.TestView.apply_presentation(query, filter_data)
106
107    group_processor = tko_rpc_utils.GroupDataProcessor(query, group_by,
108                                                       header_groups,
109                                                       fixed_headers)
110    group_processor.process_group_dicts()
111    info = group_processor.get_info_dict()
112
113    # fetch full info for these tests so we can access their statuses
114    all_test_ids = [group['latest_test_idx'] for group in info['groups']]
115    test_views = initial_query.in_bulk(all_test_ids)
116
117    for group_dict in info['groups']:
118        test_idx = group_dict.pop('latest_test_idx')
119        group_dict['test_idx'] = test_idx
120        test_view = test_views[test_idx]
121
122        tko_rpc_utils.add_status_counts(group_dict, test_view.status)
123        group_dict['extra_info'] = []
124        for field in extra_info:
125            group_dict['extra_info'].append(getattr(test_view, field))
126
127    return rpc_utils.prepare_for_serialization(info)
128
129
130def get_job_ids(**filter_data):
131    """
132    Returns AFE job IDs for all tests matching the filters.
133    """
134    query = models.TestView.query_objects(filter_data)
135    job_ids = set()
136    for test_view in query.values('job_tag').distinct():
137        # extract job ID from tag
138        first_tag_component = test_view['job_tag'].split('-')[0]
139        try:
140            job_id = int(first_tag_component)
141            job_ids.add(job_id)
142        except ValueError:
143            # a nonstandard job tag, i.e. from contributed results
144            pass
145    return list(job_ids)
146
147
148# test detail view
149
150def _attributes_to_dict(attribute_list):
151    return dict((attribute.attribute, attribute.value)
152                for attribute in attribute_list)
153
154
155def _iteration_attributes_to_dict(attribute_list):
156    iter_keyfunc = operator.attrgetter('iteration')
157    attribute_list.sort(key=iter_keyfunc)
158    iterations = {}
159    for key, group in itertools.groupby(attribute_list, iter_keyfunc):
160        iterations[key] = _attributes_to_dict(group)
161    return iterations
162
163
164def _format_iteration_keyvals(test):
165    iteration_attr = _iteration_attributes_to_dict(test.iteration_attributes)
166    iteration_perf = _iteration_attributes_to_dict(test.iteration_results)
167
168    all_iterations = iteration_attr.keys() + iteration_perf.keys()
169    max_iterations = max(all_iterations + [0])
170
171    # merge the iterations into a single list of attr & perf dicts
172    return [{'attr': iteration_attr.get(index, {}),
173             'perf': iteration_perf.get(index, {})}
174            for index in xrange(1, max_iterations + 1)]
175
176
177def _job_keyvals_to_dict(keyvals):
178    return dict((keyval.key, keyval.value) for keyval in keyvals)
179
180
181def get_detailed_test_views(**filter_data):
182    test_views = models.TestView.list_objects(filter_data)
183
184    tests_by_id = models.Test.objects.in_bulk([test_view['test_idx']
185                                               for test_view in test_views])
186    tests = tests_by_id.values()
187    models.Test.objects.populate_relationships(tests, models.TestAttribute,
188                                               'attributes')
189    models.Test.objects.populate_relationships(tests, models.IterationAttribute,
190                                               'iteration_attributes')
191    models.Test.objects.populate_relationships(tests, models.IterationResult,
192                                               'iteration_results')
193    models.Test.objects.populate_relationships(tests, models.TestLabel,
194                                               'labels')
195
196    jobs_by_id = models.Job.objects.in_bulk([test_view['job_idx']
197                                             for test_view in test_views])
198    jobs = jobs_by_id.values()
199    models.Job.objects.populate_relationships(jobs, models.JobKeyval,
200                                              'keyvals')
201
202    for test_view in test_views:
203        test = tests_by_id[test_view['test_idx']]
204        test_view['attributes'] = _attributes_to_dict(test.attributes)
205        test_view['iterations'] = _format_iteration_keyvals(test)
206        test_view['labels'] = [label.name for label in test.labels]
207
208        job = jobs_by_id[test_view['job_idx']]
209        test_view['job_keyvals'] = _job_keyvals_to_dict(job.keyvals)
210
211    return rpc_utils.prepare_for_serialization(test_views)
212
213
214def get_tests_summary(job_names):
215    """
216    Gets the count summary of all passed and failed tests per suite.
217    @param job_names: Names of the suite jobs to get the summary from.
218    @returns: A summary of all the passed and failed tests per suite job.
219    """
220    # Take advantage of Django's literal escaping to prevent SQL injection
221    sql_list = ','.join(['%s'] * len(job_names))
222    query = ('''SELECT job_name, IF (status = 'GOOD', status, 'FAIL')
223                   AS test_status, COUNT(*) num
224                 FROM tko_test_view_2
225                 WHERE job_name IN (%s)
226                   AND test_name <> 'SERVER_JOB'
227                   AND test_name NOT LIKE 'CLIENT_JOB%%%%'
228                   AND status <> 'TEST_NA'
229                 GROUP BY job_name, IF (status = 'GOOD', status, 'FAIL')'''
230            % sql_list)
231
232    cursor = readonly_connection.cursor()
233    cursor.execute(query, job_names)
234    results = rpc_utils.fetchall_as_list_of_dicts(cursor)
235
236    summaries = {}
237    for result in results:
238        status = 'passed' if result['test_status'] == 'GOOD' else 'failed'
239        summary = summaries.setdefault(result['job_name'], {})
240        summary[status] = result['num']
241
242    return summaries
243
244
245def get_tests_summary_with_wildcards(job_names):
246    """
247    Like get_tests_summary(job_names) but allowing wildcards.
248    @param job_names: Names of the suite jobs to get the summary from.
249    @returns: A summary of all the passed and failed tests per suite job.
250    """
251    query = '''SELECT IF (status = 'GOOD', status, 'FAIL')
252                   AS test_status, COUNT(*) num
253                 FROM tko_test_view_2
254                 WHERE job_name LIKE %s
255                   AND test_name <> 'SERVER_JOB'
256                   AND test_name NOT LIKE 'CLIENT_JOB%%'
257                   AND status <> 'TEST_NA'
258                 GROUP BY IF (status = 'GOOD', status, 'FAIL')'''
259
260    summaries = {}
261    cursor = readonly_connection.cursor()
262    for job_name in job_names:
263        cursor.execute(query, job_name)
264        results = rpc_utils.fetchall_as_list_of_dicts(cursor)
265        summary = summaries.setdefault(job_name, {})
266        for result in results:
267            status = 'passed' if result['test_status'] == 'GOOD' else 'failed'
268            summary[status] = result['num']
269
270    return summaries
271
272
273# graphing view support
274
275def get_hosts_and_tests():
276    """\
277    Gets every host that has had a benchmark run on it. Additionally, also
278    gets a dictionary mapping the host names to the benchmarks.
279    """
280
281    host_info = {}
282    q = (dbmodels.Q(test_name__startswith='kernbench') |
283         dbmodels.Q(test_name__startswith='dbench') |
284         dbmodels.Q(test_name__startswith='tbench') |
285         dbmodels.Q(test_name__startswith='unixbench') |
286         dbmodels.Q(test_name__startswith='iozone'))
287    test_query = models.TestView.objects.filter(q).values(
288        'test_name', 'hostname', 'machine_idx').distinct()
289    for result_dict in test_query:
290        hostname = result_dict['hostname']
291        test = result_dict['test_name']
292        machine_idx = result_dict['machine_idx']
293        host_info.setdefault(hostname, {})
294        host_info[hostname].setdefault('tests', [])
295        host_info[hostname]['tests'].append(test)
296        host_info[hostname]['id'] = machine_idx
297    return rpc_utils.prepare_for_serialization(host_info)
298
299
300def create_metrics_plot(queries, plot, invert, drilldown_callback,
301                        normalize=None):
302    return graphing_utils.create_metrics_plot(
303        queries, plot, invert, normalize, drilldown_callback=drilldown_callback)
304
305
306def create_qual_histogram(query, filter_string, interval, drilldown_callback):
307    return graphing_utils.create_qual_histogram(
308        query, filter_string, interval, drilldown_callback=drilldown_callback)
309
310
311# TODO(showard) - this extremely generic RPC is used only by one place in the
312# client.  We should come up with a more opaque RPC for that place to call and
313# get rid of this.
314def execute_query_with_param(query, param):
315    cursor = readonly_connection.cursor()
316    cursor.execute(query, param)
317    return cursor.fetchall()
318
319
320def get_preconfig(name, type):
321    return preconfigs.manager.get_preconfig(name, type)
322
323
324def get_embedding_id(url_token, graph_type, params):
325    try:
326        model = models.EmbeddedGraphingQuery.objects.get(url_token=url_token)
327    except models.EmbeddedGraphingQuery.DoesNotExist:
328        params_str = pickle.dumps(params)
329        now = datetime.datetime.now()
330        model = models.EmbeddedGraphingQuery(url_token=url_token,
331                                             graph_type=graph_type,
332                                             params=params_str,
333                                             last_updated=now)
334        model.cached_png = graphing_utils.create_embedded_plot(model,
335                                                               now.ctime())
336        model.save()
337
338    return model.id
339
340
341def get_embedded_query_url_token(id):
342    model = models.EmbeddedGraphingQuery.objects.get(id=id)
343    return model.url_token
344
345
346# test label management
347
348def add_test_label(name, description=None):
349    return models.TestLabel.add_object(name=name, description=description).id
350
351
352def modify_test_label(label_id, **data):
353    models.TestLabel.smart_get(label_id).update_object(data)
354
355
356def delete_test_label(label_id):
357    models.TestLabel.smart_get(label_id).delete()
358
359
360def get_test_labels(**filter_data):
361    return rpc_utils.prepare_for_serialization(
362        models.TestLabel.list_objects(filter_data))
363
364
365def get_test_labels_for_tests(**test_filter_data):
366    label_ids = models.TestView.objects.query_test_label_ids(test_filter_data)
367    labels = models.TestLabel.list_objects({'id__in' : label_ids})
368    return rpc_utils.prepare_for_serialization(labels)
369
370
371def test_label_add_tests(label_id, **test_filter_data):
372    test_ids = models.TestView.objects.query_test_ids(test_filter_data)
373    models.TestLabel.smart_get(label_id).tests.add(*test_ids)
374
375
376def test_label_remove_tests(label_id, **test_filter_data):
377    label = models.TestLabel.smart_get(label_id)
378
379    # only include tests that actually have this label
380    extra_where = test_filter_data.get('extra_where', '')
381    if extra_where:
382        extra_where = '(' + extra_where + ') AND '
383    extra_where += 'tko_test_labels.id = %s' % label.id
384    test_filter_data['extra_where'] = extra_where
385    test_ids = models.TestView.objects.query_test_ids(test_filter_data)
386
387    label.tests.remove(*test_ids)
388
389
390# user-created test attributes
391
392def set_test_attribute(attribute, value, **test_filter_data):
393    """
394    * attribute - string name of attribute
395    * value - string, or None to delete an attribute
396    * test_filter_data - filter data to apply to TestView to choose tests to act
397      upon
398    """
399    assert test_filter_data # disallow accidental actions on all hosts
400    test_ids = models.TestView.objects.query_test_ids(test_filter_data)
401    tests = models.Test.objects.in_bulk(test_ids)
402
403    for test in tests.itervalues():
404        test.set_or_delete_attribute(attribute, value)
405
406
407# saved queries
408
409def get_saved_queries(**filter_data):
410    return rpc_utils.prepare_for_serialization(
411        models.SavedQuery.list_objects(filter_data))
412
413
414def add_saved_query(name, url_token):
415    name = name.strip()
416    owner = afe_models.User.current_user().login
417    existing_list = list(models.SavedQuery.objects.filter(owner=owner,
418                                                          name=name))
419    if existing_list:
420        query_object = existing_list[0]
421        query_object.url_token = url_token
422        query_object.save()
423        return query_object.id
424
425    return models.SavedQuery.add_object(owner=owner, name=name,
426                                        url_token=url_token).id
427
428
429def delete_saved_queries(id_list):
430    user = afe_models.User.current_user().login
431    query = models.SavedQuery.objects.filter(id__in=id_list, owner=user)
432    if query.count() == 0:
433        raise model_logic.ValidationError('No such queries found for this user')
434    query.delete()
435
436
437# other
438def get_motd():
439    return rpc_utils.get_motd()
440
441
442def get_static_data():
443    result = {}
444    group_fields = []
445    for field in models.TestView.group_fields:
446        if field in models.TestView.extra_fields:
447            name = models.TestView.extra_fields[field]
448        else:
449            name = models.TestView.get_field_dict()[field].verbose_name
450        group_fields.append((name.capitalize(), field))
451    model_fields = [(field.verbose_name.capitalize(), field.column)
452                    for field in models.TestView._meta.fields]
453    extra_fields = [(field_name.capitalize(), field_sql)
454                    for field_sql, field_name
455                    in models.TestView.extra_fields.iteritems()]
456
457    benchmark_key = {
458        'kernbench' : 'elapsed',
459        'dbench' : 'throughput',
460        'tbench' : 'throughput',
461        'unixbench' : 'score',
462        'iozone' : '32768-4096-fwrite'
463    }
464
465    tko_perf_view = [
466        ['Test Index', 'test_idx'],
467        ['Job Index', 'job_idx'],
468        ['Test Name', 'test_name'],
469        ['Subdirectory', 'subdir'],
470        ['Kernel Index', 'kernel_idx'],
471        ['Status Index', 'status_idx'],
472        ['Reason', 'reason'],
473        ['Host Index', 'machine_idx'],
474        ['Test Started Time', 'test_started_time'],
475        ['Test Finished Time', 'test_finished_time'],
476        ['Job Tag', 'job_tag'],
477        ['Job Name', 'job_name'],
478        ['Owner', 'job_owner'],
479        ['Job Queued Time', 'job_queued_time'],
480        ['Job Started Time', 'job_started_time'],
481        ['Job Finished Time', 'job_finished_time'],
482        ['Hostname', 'hostname'],
483        ['Platform', 'platform'],
484        ['Machine Owner', 'machine_owner'],
485        ['Kernel Hash', 'kernel_hash'],
486        ['Kernel Base', 'kernel_base'],
487        ['Kernel', 'kernel'],
488        ['Status', 'status'],
489        ['Iteration Number', 'iteration'],
490        ['Performance Keyval (Key)', 'iteration_key'],
491        ['Performance Keyval (Value)', 'iteration_value'],
492    ]
493
494    result['priorities'] = priorities.Priority.choices()
495    result['group_fields'] = sorted(group_fields)
496    result['all_fields'] = sorted(model_fields + extra_fields)
497    result['test_labels'] = get_test_labels(sort_by=['name'])
498    result['current_user'] = rpc_utils.prepare_for_serialization(
499            afe_models.User.current_user().get_object_dict())
500    result['benchmark_key'] = benchmark_key
501    result['tko_perf_view'] = tko_perf_view
502    result['tko_test_view'] = model_fields
503    result['preconfigs'] = preconfigs.manager.all_preconfigs()
504    result['motd'] = rpc_utils.get_motd()
505
506    return result
507