Fix up the main display page to only use one SQL query per rendering.
We use the new data view, and use SQL counting to get results
Signed-off-by: Martin Bligh <mbligh@google.com>
git-svn-id: http://test.kernel.org/svn/autotest/trunk@1059 592f7852-d20e-0410-864c-8624ca9c26a4
diff --git a/tko/create_db b/tko/create_db
index 1527b73..d9090f6 100644
--- a/tko/create_db
+++ b/tko/create_db
@@ -83,15 +83,29 @@
-- test_view (to make life easier for people trying to mine data)
CREATE VIEW test_view AS
-SELECT tests.test_idx, tests.job_idx, tests.test, tests.subdir,
- tests.kernel_idx, tests.status, tests.reason, jobs.machine_idx,
- jobs.tag, jobs.label, jobs.username, machines.hostname,
- machines.machine_group, machines.owner, kernels.kernel_hash,
- kernels.base, kernels.printable
+SELECT tests.test_idx,
+ tests.job_idx,
+ tests.test,
+ tests.subdir,
+ tests.kernel_idx,
+ tests.status,
+ tests.reason,
+ tests.machine_idx,
+ jobs.tag AS job_tag,
+ jobs.label AS job_label,
+ jobs.username AS job_username,
+ machines.hostname AS machines_hostname,
+ machines.machine_group,
+ machines.owner AS machine_owner,
+ kernels.kernel_hash,
+ kernels.base AS kernel_base,
+ kernels.printable AS kernel_printable,
+ status.word AS status_word
FROM tests
INNER JOIN jobs ON jobs.job_idx = tests.job_idx
INNER JOIN machines ON machines.machine_idx = jobs.machine_idx
-INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx;
+INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx
+INNER JOIN status ON status.status_idx = tests.status;
INSERT INTO status (word)
diff --git a/tko/db.py b/tko/db.py
index 06a37e5..0ccd753 100644
--- a/tko/db.py
+++ b/tko/db.py
@@ -52,14 +52,15 @@
def dprint(self, value):
if self.debug:
- sys.stderr.write('SQL: ' + str(value) + '\n')
+ sys.stdout.write('SQL: ' + str(value) + '\n')
def commit(self):
self.con.commit()
- def select(self, fields, table, where, wherein={}, distinct = False):
+ def select(self, fields, table, where, wherein={}, distinct = False,
+ group_by = None):
"""\
select fields from table where {dictionary}
"""
@@ -83,7 +84,10 @@
keys_in += [field_in + ' in (' + ','.join(wherein[field_in])+') ']
cmd.append(' and '+' and '.join(keys_in))
- self.dprint('%s %s' % (' '.join(cmd),values))
+ if group_by:
+ cmd.append(' GROUP BY ' + group_by)
+
+ self.dprint('%s %s' % (' '.join(cmd), values))
self.cur.execute(' '.join(cmd), values)
return self.cur.fetchall()
diff --git a/tko/display.py b/tko/display.py
index d37b8ff..258278f 100755
--- a/tko/display.py
+++ b/tko/display.py
@@ -71,7 +71,9 @@
def status_count_box(db, tests, link = None):
"""
Display a table within a box, representing the status count of
- the group of tests (e.g. 10 GOOD, 2 WARN, 3 FAIL)
+ the group of tests (e.g. 10 GOOD, 2 WARN, 3 FAIL).
+
+ Starts from a list of test objects
"""
if not tests:
return box(None, None)
@@ -80,6 +82,17 @@
for test in tests:
count = status_count.get(test.status_num, 0)
status_count[test.status_num] = count + 1
+ return status_precounted_box(db, status_count, link)
+
+
+def status_precounted_box(db, status_count, link = None):
+ """
+ Display a table within a box, representing the status count of
+ the group of tests (e.g. 10 GOOD, 2 WARN, 3 FAIL)
+ """
+ if not status_count:
+ return box(None, None)
+
worst = sorted(status_count.keys())[0]
html = status_html(db, status_count)
if link:
diff --git a/tko/frontend.py b/tko/frontend.py
index 6b6784b..dc440bf 100755
--- a/tko/frontend.py
+++ b/tko/frontend.py
@@ -48,6 +48,32 @@
# returns the value and its field name
return match, field_name_in_main_table
+
+def get_axis_data(axis):
+ rows = db.select(axis , 'test_view', distinct = True)
+ # Need to do a magic sort here if axis == 'kernel_printable'
+ return sorted([row[0] for row in rows])
+
+
+def get_matrix_data(db, x_axis, y_axis, where = None):
+ # Return a 3-d hash of data - [x-value][y-value][status_word]
+ # Searches on the test_view table - x_axis and y_axis must both be
+ # column names in that table.
+ assert x_axis != y_axis
+ fields = '%s, %s, status, COUNT(status_word)' % (x_axis, y_axis)
+ group_by = '%s, %s, status' % (x_axis, y_axis)
+ rows = db.select(fields, 'test_view', where=where, group_by=group_by)
+
+ data = {}
+ for (x, y, status, count) in rows:
+ if not data.has_key(x):
+ data[x] = {}
+ if not data[x].has_key(y):
+ data[x][y] = {}
+ data[x][y][status] = count
+ return data
+
+
class anygroup:
@classmethod
def selectunique(klass, db, field):
@@ -84,7 +110,8 @@
'machine_group is not null')
groupnames = sorted([row[0] for row in rows])
return [klass(db, groupname) for groupname in groupnames]
-
+
+
def __init__(self, db, name):
self.name = name
self.db = db
@@ -132,10 +159,8 @@
@classmethod
def select(klass, db, where = {}):
fields = ['kernel_idx', 'kernel_hash', 'base', 'printable']
- kernels = []
- for row in db.select(','.join(fields), 'kernels', where):
- kernels.append(klass(db, *row))
- return kernels
+ rows = db.select(','.join(fields), 'kernels', where)
+ return [klass(db, *row) for row in rows]
def __init__(self, db, idx, hash, base, printable):
diff --git a/tko/machine_kernel.cgi b/tko/machine_kernel.cgi
index c972860..588e2c4 100755
--- a/tko/machine_kernel.cgi
+++ b/tko/machine_kernel.cgi
@@ -14,13 +14,11 @@
db = db.db()
-def kernel_group_box(kernel, group):
- tests = group.tests({ 'kernel_idx':kernel.idx })
-
+def kernel_group_box(kernel, group, box_data):
machine_idxs = ['%d' % machine.idx for machine in group.machines()]
link = 'machine_kernel_test.cgi?machine=%s&kernel=%s' % \
(','.join(machine_idxs), kernel.idx)
- return display.status_count_box(db, tests, link)
+ return display.status_precounted_box(db, box_data, link)
def kernel_encode(kernel):
@@ -30,8 +28,9 @@
def main():
display.print_main_header()
- groups = frontend.group.select(db)
+ data = frontend.get_matrix_data(db, 'machine_group', 'kernel_printable')
+ groups = frontend.group.select(db)
group_names = [display.group_name(g) for g in groups]
headers = ['Version'] + group_names
header_row = [ display.box(x, header=True) for x in headers ]
@@ -44,7 +43,12 @@
link = 'group_test.cgi?kernel=%s' % kernel.idx
row = [display.box(kernel.printable, link=link)]
for group in groups:
- row.append(kernel_group_box(kernel, group))
+ try:
+ box_data = data[group.name][kernel.printable]
+ except:
+ row.append(display.box(None, None))
+ continue
+ row.append(kernel_group_box(kernel, group, box_data))
matrix.append(row)
matrix.append(header_row)