reproducible: include average build duration in live_status

1 files changed, 15 insertions, 8 deletions

diff --git a/bin/reproducible_html_live_status.py b/bin/reproducible_html_live_status.py
index 1d55bb39..a1dea9e0 100755
--- a/bin/reproducible_html_live_status.py
+++ b/bin/reproducible_html_live_status.py
@@ -15,6 +15,7 @@ def convert_into_hms_string(duration):
     if not duration:
         duration = "None"
     else:
+        duration = int(duration)
         hours = int(duration/3600)
         minutes = int((duration-(hours*3600))/60)
         seconds = int(duration-(hours*3600)-(minutes*60))
@@ -32,9 +33,11 @@ def generate_schedule(arch):
     log.info('Building the schedule index page for ' + arch + '...')
     title = 'Packages currently scheduled on ' + arch + ' for testing for build reproducibility'
     query = 'SELECT sch.date_scheduled, s.suite, s.architecture, s.name, ' + \
-            'r.status, r.build_duration ' + \
+            'r.status, r.build_duration, ' + \
+            '(SELECT coalesce(AVG(h.build_duration), 0) FROM stats_build AS h WHERE h.status IN ("reproducible", "unreproducible") AND h.name=s.name AND h.suite=s.suite AND h.architecture=s.architecture) ' + \
             'FROM schedule AS sch JOIN sources AS s ON sch.package_id=s.id LEFT JOIN results AS r ON s.id=r.package_id ' + \
             'WHERE sch.date_build_started = "" AND s.architecture="{arch}" ORDER BY sch.date_scheduled'
+    # 'AND h.name=s.name AND h.suite=s.suite AND h.architecture=s.architecture' in this query and the query below is needed due to not using package_id in the stats_build table, which should be fixed...
     text = Template('$tot packages are currently scheduled for testing on $arch:')
     html = ''
     rows = query_db(query.format(arch=arch))
@@ -42,16 +45,17 @@ def generate_schedule(arch):
     html += generate_live_status_table(arch)
     html += '<p><table class="scheduled">\n' + tab
     html += '<tr><th>#</th><th>scheduled at</th><th>suite</th>'
-    html += '<th>arch</th><th>source package</th><th>previous build status</th><th>previous build duration</th></tr>\n'
+    html += '<th>arch</th><th>source package</th><th>previous build status</th><th>previous build duration</th><th>average build duration</th></tr>\n'
     bugs = get_bugs()
     for row in rows:
-        # 0: date_scheduled, 1: suite, 2: arch, 3: pkg name 4: previous status 5: previous build duration
+        # 0: date_scheduled, 1: suite, 2: arch, 3: pkg name 4: previous status 5: previous build duration 6. avg build duration
         pkg = row[3]
         duration = convert_into_hms_string(row[5])
+        avg_duration = convert_into_hms_string(row[6])
         html += tab + '<tr><td>&nbsp;</td><td>' + row[0] + '</td>'
         html += '<td>' + row[1] + '</td><td>' + row[2] + '</td><td><code>'
         html += link_package(pkg, row[1], row[2], bugs)
-        html += '</code></td><td>'+str(row[4])+'</td><td>'+duration+'</td></tr>\n'
+        html += '</code></td><td>'+str(row[4])+'</td><td>'+duration+'</td><td>' + avg_duration + '</td></tr>\n'
     html += '</table></p>\n'
     destfile = BASE + '/index_' + arch + '_scheduled.html'
     desturl = REPRODUCIBLE_URL + '/index_' + arch + '_scheduled.html'
@@ -61,7 +65,9 @@ def generate_schedule(arch):
 
 def generate_live_status_table(arch):
     query = 'SELECT s.id, s.suite, s.architecture, s.name, s.version, ' + \
-            'p.date_build_started, r.status, r.build_duration, p.builder ' + \
+            'p.date_build_started, r.status, r.build_duration, ' + \
+            '(SELECT coalesce(AVG(h.build_duration), 0) FROM stats_build AS h WHERE h.status IN ("reproducible", "unreproducible") AND h.name=s.name AND h.suite=s.suite AND h.architecture=s.architecture) ' + \
+            ', p.builder ' + \
             'FROM sources AS s JOIN schedule AS p ON p.package_id=s.id LEFT JOIN results AS r ON s.id=r.package_id ' + \
             'WHERE p.date_build_started != "" AND s.architecture="{arch}" ' + \
             'ORDER BY p.date_build_started DESC'
@@ -71,7 +77,7 @@ def generate_live_status_table(arch):
     html += '<tr><th>#</th><th>src pkg id</th><th>suite</th><th>arch</th>'
     html += '<th>source package</th><th>version</th></th>'
     html += '<th>build started</th><th>previous build status</th>'
-    html += '<th>previous build duration</th><th>builder job</th>'
+    html += '<th>previous build duration</th><th>average build duration</th><th>builder job</th>'
     html += '</tr>\n'
     counter = 0
     for row in rows:
@@ -83,12 +89,13 @@ def generate_live_status_table(arch):
         arch = row[2]
         pkg = row[3]
         duration = convert_into_hms_string(row[7])
+        avg_duration = convert_into_hms_string(row[8])
         html += tab + '<tr><td>&nbsp;</td><td>' + str(row[0]) + '</td>'
         html += '<td>' + suite + '</td><td>' + arch + '</td>'
         html += '<td><code>' + link_package(pkg, suite, arch) + '</code></td>'
         html += '<td>' + str(row[4]) + '</td><td>' + str(row[5]) + '</td>'
-        html += '<td>' + str(row[6]) + '</td><td>' + duration + '</td> '
-        html += '<td><a href="https://jenkins.debian.net/job/reproducible_builder_' + str(row[8]) + '/console">' + str(row[8]) + '</a></td>'
+        html += '<td>' + str(row[6]) + '</td><td>' + duration + '</td><td>' + avg_duration + '</td>'
+        html += '<td><a href="https://jenkins.debian.net/job/reproducible_builder_' + str(row[9]) + '/console">' + str(row[9]) + '</a></td>'
         html += '</tr>\n'
     html += '</table></p>\n'
     return html