[Qa-jenkins-scm] [jenkins.debian.net] 01/03: reproducible Debian: partial conversion to sqlalchemy expression language
Holger Levsen
holger at layer-acht.org
Sun Nov 27 15:31:38 UTC 2016
This is an automated email from the git hooks/post-receive script.
holger pushed a commit to branch master
in repository jenkins.debian.net.
commit 68ff1feb17c9f206f6e1081b0ab27d317741c838
Author: Valerie R Young <spectranaut at riseup.net>
Date: Fri Sep 9 15:16:38 2016 -0400
reproducible Debian: partial conversion to sqlalchemy expression language
This commit partially converts two scripts to use the sqlachemy expression
language to be sqlite/postgres agnostic.
Signed-off-by: Mattia Rizzolo <mattia at debian.org>
Signed-off-by: Holger Levsen <holger at layer-acht.org>
---
bin/reproducible_html_indexes.py | 305 ++++++++++++++++++++++++++++++-----
bin/reproducible_html_live_status.py | 114 ++++++++++---
2 files changed, 358 insertions(+), 61 deletions(-)
diff --git a/bin/reproducible_html_indexes.py b/bin/reproducible_html_indexes.py
index 3163da5..ea58678 100755
--- a/bin/reproducible_html_indexes.py
+++ b/bin/reproducible_html_indexes.py
@@ -11,6 +11,7 @@
# Build quite all index_* pages
from reproducible_common import *
+from sqlalchemy import select, and_, or_, func, bindparam, desc
"""
Reference doc for the folowing lists:
@@ -51,35 +52,255 @@ Technically speaking, a page can be empty (we all love nonsense) but every
section must have at least a `query` defining what to file in.
"""
-# filter_query is defined in reproducible_common.py and excludes some FTBFS issues
+timespan_date_map = {}
+timespan_date_map[24] = (datetime.now()-timedelta(hours=24)).strftime('%Y-%m-%d %H:%M')
+timespan_date_map[48] = (datetime.now()-timedelta(hours=48)).strftime('%Y-%m-%d %H:%M')
+
+# sqlalchemy table definitions needed for queries
+results = db_table('results')
+sources = db_table('sources')
+notes = db_table('notes')
+
+# filtered_issues is defined in reproducible_common.py and
+# can be used to excludes some FTBFS issues
+filter_issues_list = []
+for issue in filtered_issues:
+ filter_issues_list.append(notes.c.issues.contains(issue))
+if not filtered_issues:
+ filter_issues_list = [None]
+
+count_results = select(
+ [func.count(results.c.id)]
+).select_from(
+ results.join(sources)
+).where(
+ and_(
+ sources.c.suite == bindparam('suite'),
+ sources.c.architecture == bindparam('arch')
+ )
+)
+
+select_sources = select(
+ [sources.c.name]
+).select_from(
+ results.join(sources)
+).where(
+ and_(
+ sources.c.suite == bindparam('suite'),
+ sources.c.architecture == bindparam('arch')
+ )
+)
+
queries = {
- 'count_total': 'SELECT COUNT(*) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}"',
- 'count_timespan': 'SELECT COUNT(*) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND build_date > datetime("now", "-{timespan} hours")',
- 'reproducible_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="reproducible" ORDER BY r.build_date DESC',
- 'reproducible_last24h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="reproducible" AND r.build_date > datetime("now", "-24 hours") ORDER BY r.build_date DESC',
- 'reproducible_last48h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="reproducible" AND r.build_date > datetime("now", "-48 hours") ORDER BY r.build_date DESC',
- 'reproducible_all_abc': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="reproducible" ORDER BY name',
- 'FTBR_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "unreproducible" ORDER BY build_date DESC',
- 'FTBR_last24h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "unreproducible" AND build_date > datetime("now", "-24 hours") ORDER BY build_date DESC',
- 'FTBR_last48h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "unreproducible" AND build_date > datetime("now", "-48 hours") ORDER BY build_date DESC',
- 'FTBR_all_abc': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "unreproducible" ORDER BY name',
- 'FTBFS_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "FTBFS" ORDER BY build_date DESC',
- 'FTBFS_last24h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "FTBFS" AND build_date > datetime("now", "-24 hours") ORDER BY build_date DESC',
- 'FTBFS_last48h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "FTBFS" AND build_date > datetime("now", "-48 hours") ORDER BY build_date DESC',
- 'FTBFS_all_abc': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "FTBFS" ORDER BY s.name',
- 'FTBFS_filtered': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status = "FTBFS" AND r.package_id NOT IN (SELECT n.package_id FROM NOTES AS n WHERE ' + filter_query + ' ) ORDER BY s.name',
- 'FTBFS_caused_by_us': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status = "FTBFS" AND r.package_id IN (SELECT n.package_id FROM NOTES AS n WHERE ' + filter_query + ' ) ORDER BY s.name',
- '404_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "404" ORDER BY build_date DESC',
- '404_all_abc': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "404" ORDER BY name',
- 'depwait_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "depwait" ORDER BY build_date DESC',
- 'depwait_all_abc': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "depwait" ORDER BY name',
- 'depwait_last24h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "depwait" AND build_date > datetime("now", "-24 hours") ORDER BY build_date DESC',
- 'depwait_last48h': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "depwait" AND build_date > datetime("now", "-48 hours") ORDER BY build_date DESC',
- 'not_for_us_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "not for us" ORDER BY name',
- 'blacklisted_all': 'SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND status = "blacklisted" ORDER BY name',
- 'notes': 'SELECT s.name FROM sources AS s JOIN notes AS n ON n.package_id=s.id JOIN results AS r ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="{status}" ORDER BY r.build_date DESC',
- 'no_notes': 'SELECT s.name FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="{status}" AND s.id NOT IN (SELECT package_id FROM notes) ORDER BY r.build_date DESC',
- 'notification': 'SELECT s.name FROM sources AS s JOIN results AS r ON s.id=r.package_id WHERE s.suite="{suite}" AND s.architecture="{arch}" AND r.status="{status}" AND s.notify_maintainer = 1',
+ "count_total": count_results,
+ "count_timespan":
+ count_results.where(
+ results.c.build_date > bindparam('timespan_date'),
+ ),
+ "reproducible_all":
+ select_sources.where(
+ results.c.status == 'reproducible',
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "reproducible_last24h":
+ select_sources.where(
+ results.c.build_date > timespan_date_map[24]
+ ),
+ "reproducible_last48h":
+ select_sources.where(
+ results.c.build_date > timespan_date_map[48],
+ ),
+ "reproducible_all_abc":
+ select_sources.where(
+ results.c.status == 'reproducible',
+ ).order_by(
+ sources.c.name
+ ),
+ "FTBR_all":
+ select_sources.where(
+ results.c.status == 'unreproducible',
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBR_last24h":
+ select_sources.where(
+ and_(
+ results.c.status == 'unreproducible',
+ results.c.build_date > timespan_date_map[24],
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBR_last48h":
+ select_sources.where(
+ and_(
+ results.c.status == 'unreproducible',
+ results.c.build_date > timespan_date_map[48],
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBR_all_abc":
+ select_sources.where(
+ results.c.status == 'unreproducible',
+ ).order_by(
+ sources.c.name
+ ),
+ "FTBFS_all":
+ select_sources.where(
+ results.c.status == 'FTBFS',
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBFS_last24h":
+ select_sources.where(
+ and_(
+ results.c.status == 'FTBFS',
+ results.c.build_date > timespan_date_map[24],
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBFS_last48h":
+ select_sources.where(
+ and_(
+ results.c.status == 'FTBFS',
+ results.c.build_date > timespan_date_map[48],
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBFS_all_abc":
+ select_sources.where(
+ results.c.status == 'FTBFS',
+ ).order_by(
+ sources.c.name
+ ),
+ "FTBFS_filtered":
+ select_sources.where(
+ and_(
+ results.c.status == 'FTBFS',
+ sources.c.id.notin_(
+ select(
+ [notes.c.package_id]
+ ).select_from(
+ notes
+ ).where(
+ or_(*filter_issues_list)
+ )
+ )
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "FTBFS_caused_by_us":
+ select_sources.where(
+ and_(
+ results.c.status == 'FTBFS',
+ sources.c.id.in_(
+ select(
+ [notes.c.package_id]
+ ).select_from(
+ notes
+ ).where(
+ or_(*filter_issues_list)
+ )
+ )
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "404_all":
+ select_sources.where(
+ results.c.status == '404',
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "404_all_abc":
+ select_sources.where(
+ results.c.status == '404',
+ ).order_by(
+ sources.c.name
+ ),
+ "depwait_all":
+ select_sources.where(
+ results.c.status == 'depwait',
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "depwait_all_abc":
+ select_sources.where(
+ results.c.status == 'depwait',
+ ).order_by(
+ sources.c.name
+ ),
+ "depwait_last24h":
+ select_sources.where(
+ and_(
+ results.c.status == 'depwait',
+ results.c.build_date > timespan_date_map[24],
+
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "depwait_last48h":
+ select_sources.where(
+ and_(
+ results.c.status == 'depwait',
+ results.c.build_date > timespan_date_map[48],
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "not_for_us_all":
+ select_sources.where(
+ and_(
+ results.c.status == 'not for us',
+
+ )
+ ).order_by(
+ sources.c.name
+ ),
+ "blacklisted_all":
+ select_sources.where(
+ results.c.status == 'blacklisted',
+ ).order_by(
+ sources.c.name
+ ),
+ "notes":
+ select(
+ [sources.c.name]
+ ).select_from(
+ sources.join(results).join(notes)
+ ).where(
+ and_(
+ results.c.status == bindparam('status'),
+ sources.c.suite == bindparam('suite'),
+ sources.c.architecture == bindparam('arch')
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "no_notes":
+ select_sources.where(
+ and_(
+ results.c.status == bindparam('status'),
+ sources.c.id.notin_(select([notes.c.package_id]).select_from(notes))
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
+ "notification":
+ select_sources.where(
+ and_(
+ results.c.status == bindparam('status'),
+ sources.c.notify_maintainer == 1
+ )
+ ).order_by(
+ desc(results.c.build_date)
+ ),
}
pages = {
@@ -292,7 +513,7 @@ pages = {
'notes': True,
'title': 'Packages with notes',
'header': '<p>There are {tot} packages with notes in {suite}/{arch}.</p>',
- 'header_query': 'SELECT count(*) FROM (SELECT * FROM sources AS s JOIN notes AS n ON n.package_id=s.id WHERE s.suite="{suite}" AND s.architecture="{arch}" GROUP BY s.name) AS tmp',
+ 'header_query': "SELECT count(*) FROM (SELECT s.name FROM sources AS s JOIN notes AS n ON n.package_id=s.id WHERE s.suite='{suite}' AND s.architecture='{arch}' GROUP BY s.name) AS tmp",
'body': [
{
'icon_status': 'FTBR',
@@ -348,7 +569,7 @@ pages = {
'notes_hint': True,
'title': 'Packages without notes',
'header': '<p>There are {tot} faulty packages without notes in {suite}/{arch}.{hint}</p>',
- 'header_query': 'SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status IN ("unreproducible", "FTBFS", "blacklisted") AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite="{suite}" AND s.architecture="{arch}")',
+ 'header_query': "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status IN ('unreproducible', 'FTBFS', 'blacklisted') AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='{suite}' AND s.architecture='{arch}') AS tmp",
'body': [
{
'icon_status': 'FTBR',
@@ -379,7 +600,7 @@ pages = {
'nosuite': True,
'title': 'Packages with notification enabled',
'header': '<p>The following {tot} packages have notifications enabled. (This page only shows packages in {suite}/{arch} though notifications are send for these packages in unstable and experimental in all tested architectures.) On status changes (e.g. reproducible → unreproducible) the system notifies the maintainer and relevant parties via an email to $srcpackage at packages.debian.org. Notifications are collected and send once a day to avoid flooding.<br />Please ask us to enable [...]
- 'header_query': 'SELECT COUNT(*) FROM sources WHERE suite="{suite}" AND architecture="{arch}" AND notify_maintainer = 1',
+ 'header_query': "SELECT COUNT(*) FROM sources WHERE suite='{suite}' AND architecture='{arch}' AND notify_maintainer = 1",
'body': [
{
'icon_status': 'FTBR',
@@ -413,7 +634,7 @@ pages = {
def build_leading_text_section(section, rows, suite, arch):
html = '<p>\n' + tab
total = len(rows)
- count_total = int(query_db(queries['count_total'].format(suite=suite, arch=arch))[0][0])
+ count_total = int(query_db(queries['count_total'].params({'suite': suite, 'arch': arch}))[0][0])
try:
percent = round(((total/count_total)*100), 1)
except ZeroDivisionError:
@@ -433,10 +654,13 @@ def build_leading_text_section(section, rows, suite, arch):
html += '</a>'
html += '\n' + tab
if section.get('text') and section.get('timespan'):
- count = len(query_db(queries[section['query2']].format(suite=suite, arch=arch)))
+ count = len(query_db(queries[section['query2']].params(
+ {'suite': suite, 'arch': arch})))
percent = round(((count/count_total)*100), 1)
timespan = section['timespan']
- timespan_count = int(query_db(queries['count_timespan'].format(suite=suite, arch=arch, timespan=timespan))[0][0])
+ timespan_date = timespan_date_map[timespan]
+ timespan_count = int(query_db(queries['count_timespan'].params(
+ {'suite': suite, 'arch': arch, 'timespan_date': timespan_date}))[0][0])
try:
timespan_percent = round(((total/timespan_count)*100), 1)
except ZeroDivisionError:
@@ -445,7 +669,8 @@ def build_leading_text_section(section, rows, suite, arch):
timespan_percent = 0
html += section['text'].substitute(tot=total, percent=percent,
- timespan_percent=timespan_percent, timespan_count=timespan_count,
+ timespan_percent=timespan_percent,
+ timespan_count=timespan_count,
count_total=count_total,
count=count, suite=suite, arch=arch)
elif section.get('text'):
@@ -463,18 +688,18 @@ def build_page_section(page, section, suite, arch):
suite = defaultsuite
arch = defaultarch
if pages[page].get('notes') and pages[page]['notes']:
- query = queries[section['query']].format(
- status=section['db_status'], suite=suite, arch=arch)
+ query = queries[section['query']].params({
+ 'status': section['db_status'], 'suite': suite, 'arch': arch})
else:
- query = queries[section['query']].format(suite=suite, arch=arch)
+ query = queries[section['query']].params({'suite': suite, 'arch': arch})
rows = query_db(query)
except:
- print_critical_message('A query failed: ' + query)
+ print_critical_message('A query failed: %s' % query)
raise
html = ''
footnote = True if rows else False
if not rows: # there are no package in this set
- log.debug('empty query: ' + query) # do not output anything.
+ log.debug('empty query: %s' % query) # do not output anything.
return (html, footnote)
html += build_leading_text_section(section, rows, suite, arch)
html += '<p>\n' + tab + '<code>\n'
diff --git a/bin/reproducible_html_live_status.py b/bin/reproducible_html_live_status.py
index 6db9aa7..5681fe4 100755
--- a/bin/reproducible_html_live_status.py
+++ b/bin/reproducible_html_live_status.py
@@ -10,10 +10,17 @@
from reproducible_common import *
from reproducible_html_indexes import build_leading_text_section
+from sqlalchemy import select, func, cast, Integer, and_, bindparam
import glob
bugs = get_bugs()
+# sqlalchemy table definitions needed for queries
+results = db_table('results')
+sources = db_table('sources')
+schedule = db_table('schedule')
+stats_build = db_table('stats_build')
+
def convert_into_status_html(status):
if status != 'None':
status, icon, spokenstatus = get_status_icon(status)
@@ -26,15 +33,43 @@ def generate_schedule(arch):
""" the schedule pages are very different than others index pages """
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, ' + \
- '(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 IS NULL 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...
+
+ # '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...
+ averagesql = select([
+ func.coalesce(func.avg(cast(stats_build.c.build_duration, Integer)), 0)
+ ]).where(
+ and_(
+ stats_build.c.status.in_(('reproducible', 'unreproducible')),
+ stats_build.c.name == sources.c.name,
+ stats_build.c.suite == sources.c.suite,
+ stats_build.c.architecture == sources.c.architecture,
+ )
+ ).as_scalar()
+
+ query = select([
+ schedule.c.date_scheduled,
+ sources.c.suite,
+ sources.c.architecture,
+ sources.c.name,
+ results.c.status,
+ results.c.build_duration,
+ averagesql
+ ]).select_from(
+ sources.join(schedule).join(results, isouter=True)
+ ).where(
+ and_(
+ schedule.c.date_build_started == None,
+ sources.c.architecture == bindparam('arch'),
+ )
+ ).order_by(
+ schedule.c.date_scheduled
+ )
+
text = Template('$tot packages are currently scheduled for testing on $arch:')
html = ''
- rows = query_db(query.format(arch=arch))
+ rows = query_db(query.params({'arch': arch}))
html += build_leading_text_section({'text': text}, rows, defaultsuite, arch)
html += generate_live_status_table(arch)
html += '<p><table class="scheduled">\n' + tab
@@ -61,15 +96,40 @@ 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, ' + \
- '(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.job ' + \
- '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 IS NOT NULL AND s.architecture="{arch}" ' + \
- 'ORDER BY p.date_build_started DESC'
+ averagesql = select([
+ func.coalesce(func.avg(cast(stats_build.c.build_duration, Integer)), 0)
+ ]).where(
+ and_(
+ stats_build.c.status.in_(('reproducible', 'unreproducible')),
+ stats_build.c.name == sources.c.name,
+ stats_build.c.suite == sources.c.suite,
+ stats_build.c.architecture == sources.c.architecture,
+ )
+ ).as_scalar()
+
+ query = select([
+ sources.c.id,
+ sources.c.suite,
+ sources.c.architecture,
+ sources.c.name,
+ sources.c.version,
+ schedule.c.date_build_started,
+ results.c.status,
+ results.c.build_duration,
+ averagesql,
+ schedule.c.job,
+ ]).select_from(
+ sources.join(schedule).join(results, isouter=True)
+ ).where(
+ and_(
+ schedule.c.date_build_started != None,
+ sources.c.architecture == bindparam('arch'),
+ )
+ ).order_by(
+ schedule.c.date_scheduled
+ )
html = ''
- rows = query_db(query.format(arch=arch))
+ rows = query_db(query.params({'arch': arch}))
html += '<p><table class="scheduled">\n' + tab
html += '<tr><th class="center">#</th><th class="center">src pkg id</th><th class="center">suite</th><th class="center">arch</th>'
html += '<th class=\"center\">source package</th><th class=\"center\">version</th></th>'
@@ -105,13 +165,25 @@ def generate_oldies(arch):
title = 'Oldest results on ' + arch
html = ''
for suite in SUITES:
- query = 'SELECT s.suite, s.architecture, s.name, r.status, r.build_date ' + \
- 'FROM results AS r JOIN sources AS s ON r.package_id=s.id ' + \
- 'WHERE s.suite="{suite}" AND s.architecture="{arch}" ' + \
- 'AND r.status != "blacklisted" ' + \
- 'ORDER BY r.build_date LIMIT 15'
+ query = select([
+ sources.c.suite,
+ sources.c.architecture,
+ sources.c.name,
+ results.c.status,
+ results.c.build_date
+ ]).select_from(
+ results.join(sources)
+ ).where(
+ and_(
+ sources.c.suite == bindparam('suite'),
+ sources.c.architecture == bindparam('arch'),
+ results.c.status != 'blacklisted'
+ )
+ ).order_by(
+ results.c.build_date
+ ).limit(15)
text = Template('Oldest results on $suite/$arch:')
- rows = query_db(query.format(arch=arch,suite=suite))
+ rows = query_db(query.params({'arch': arch, 'suite': suite}))
html += build_leading_text_section({'text': text}, rows, suite, arch)
html += '<p><table class="scheduled">\n' + tab
html += '<tr><th class="center">#</th><th class="center">suite</th><th class="center">arch</th>'
--
Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/qa/jenkins.debian.net.git
More information about the Qa-jenkins-scm
mailing list