[Qa-jenkins-scm] [Git][qa/jenkins.debian.net][master] reproducible db: add a "distribution" field to the sources table (defaulting to "debian" for now)
Mattia Rizzolo
gitlab at salsa.debian.org
Wed Dec 12 14:26:34 GMT 2018
Mattia Rizzolo pushed to branch master at Debian QA / jenkins.debian.net
Commits:
5e429a95 by Mattia Rizzolo at 2018-12-12T14:25:37Z
reproducible db: add a "distribution" field to the sources table (defaulting to "debian" for now)
Thanks to Bernhard M. Wiedemann for helping while working on this!
Signed-off-by: Mattia Rizzolo <mattia at debian.org>
- - - - -
4 changed files:
- bin/reproducible_build.sh
- bin/reproducible_db_maintenance.py
- bin/reproducible_html_indexes.py
- bin/reproducible_html_packages.py
Changes:
=====================================
bin/reproducible_build.sh
=====================================
@@ -408,10 +408,12 @@ call_diffoscope_on_changes_files() {
}
choose_package() {
+ DISTROID=$(query_db "SELECT id FROM distributions WHERE name='debian'")
local RESULT=$(query_db "
SELECT s.suite, s.id, s.name, s.version, sch.save_artifacts, sch.notify, s.notify_maintainer, sch.date_scheduled
FROM schedule AS sch JOIN sources AS s ON sch.package_id=s.id
WHERE sch.date_build_started is NULL
+ AND s.distribution=$DISTROID
AND s.architecture='$ARCH'
ORDER BY date_scheduled LIMIT 5"|sort -R|head -1)
if [ -z "$RESULT" ] ; then
=====================================
bin/reproducible_db_maintenance.py
=====================================
@@ -655,7 +655,19 @@ schema_updates = {
37: [ # change the data type in the stats_build.build_date column
"ALTER TABLE stats_build ALTER COLUMN build_date SET DATA TYPE timestamp"
" USING build_date::timestamp"
- ]
+ ],
+ 38: [ # add a distribution field to the sources tables
+ """CREATE TABLE distributions (
+ id SERIAL PRIMARY KEY,
+ name VARCHAR)""",
+ "INSERT INTO distributions (name) VALUES ('debian')",
+ """ALTER TABLE sources
+ ADD COLUMN distribution INTEGER DEFAULT 1
+ REFERENCES distributions(id)""",
+ """ALTER TABLE stats_build
+ ADD COLUMN distribution INTEGER DEFAULT 1
+ REFERENCES distributions(id)""",
+ ],
}
=====================================
bin/reproducible_html_indexes.py
=====================================
@@ -21,7 +21,7 @@ from rblib.models import Status, Package
from rblib.utils import print_critical_message
from rblib.html import tab, create_main_navigation, write_html_page
from rblib.const import (
- DISTRO_BASE, DISTRO_URI, DISTRO_URL,
+ DISTRO, DISTRO_BASE, DISTRO_URI, DISTRO_URL,
SUITES, ARCHS,
defaultsuite, defaultarch,
filtered_issues, filter_html,
@@ -71,6 +71,7 @@ timespan_date_map[24] = (datetime.now()-timedelta(hours=24)).strftime('%Y-%m-%d
timespan_date_map[48] = (datetime.now()-timedelta(hours=48)).strftime('%Y-%m-%d %H:%M')
# sqlalchemy table definitions needed for queries
+distributions = db_table('distributions')
results = db_table('results')
sources = db_table('sources')
notes = db_table('notes')
@@ -83,12 +84,15 @@ for issue in filtered_issues:
if not filtered_issues:
filter_issues_list = [None]
+distro_id = query_db(select([distributions.c.id]).where(distributions.c.name == DISTRO))[0][0]
+
count_results = select(
[func.count(results.c.id)]
).select_from(
results.join(sources)
).where(
and_(
+ sources.c.distribution == distro_id,
sources.c.suite == bindparam('suite'),
sources.c.architecture == bindparam('arch')
)
@@ -100,6 +104,7 @@ select_sources = select(
results.join(sources)
).where(
and_(
+ sources.c.distribution == distro_id,
sources.c.suite == bindparam('suite'),
sources.c.architecture == bindparam('arch')
)
@@ -307,6 +312,7 @@ queries = {
sources.join(results).join(notes)
).where(
and_(
+ sources.c.distribution == distro_id,
results.c.status == bindparam('status'),
sources.c.suite == bindparam('suite'),
sources.c.architecture == bindparam('arch')
@@ -560,7 +566,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 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",
+ 'header_query': "SELECT count(*) FROM (SELECT s.name FROM sources AS s JOIN notes AS n ON n.package_id=s.id WHERE s.distribution={distro} AND s.suite='{suite}' AND s.architecture='{arch}' GROUP BY s.name) AS tmp",
'body': [
{
'status': Status.FTBR,
@@ -617,7 +623,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 ('FTBR', 'FTBFS', 'blacklisted') AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='{suite}' AND s.architecture='{arch}') AS tmp",
+ 'header_query': "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE s.distribution={distro} AND r.status IN ('FTBR', 'FTBFS', 'blacklisted') AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='{suite}' AND s.architecture='{arch}') AS tmp",
'body': [
{
'status': Status.FTBR,
@@ -645,7 +651,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 notifications for your package(s) in our IRC channel #debian-reproducible or via <a href="mailto:reproducible-builds at lists.alioth.debian.org">mail</a> - but ask your fellow team members first if they want to receive such notifications.</p>',
- 'header_query': "SELECT COUNT(*) FROM sources WHERE suite='{suite}' AND architecture='{arch}' AND notify_maintainer = 1",
+ 'header_query': "SELECT COUNT(*) FROM sources WHERE distribution={distro} AND suite='{suite}' AND architecture='{arch}' AND notify_maintainer = 1",
'body': [
{
'status': Status.FTBR,
@@ -787,8 +793,8 @@ def build_page(page, suite=None, arch=None):
else:
hint = ''
if pages[page].get('header_query'):
- html += pages[page]['header'].format(
- tot=query_db(pages[page]['header_query'].format(suite=suite, arch=arch))[0][0], suite=suite, arch=arch, hint=hint)
+ result = query_db(pages[page]['header_query'].format(distro=distro_id, suite=suite, arch=arch))
+ html += pages[page]['header'].format(tot=result[0][0], suite=suite, arch=arch, hint=hint)
else:
html += pages[page].get('header')
for section in page_sections:
=====================================
bin/reproducible_html_packages.py
=====================================
@@ -24,6 +24,7 @@ from rblib.models import Package, Status
from rblib.utils import strip_epoch, convert_into_hms_string
from rblib.html import gen_status_link_icon, write_html_page
from rblib.const import (
+ DISTRO,
TEMPLATE_PATH,
REPRODUCIBLE_URL,
DISTRO_URL,
@@ -449,8 +450,12 @@ def gen_packages_html(packages, no_clean=False):
def gen_all_rb_pkg_pages(no_clean=False):
- query = 'SELECT DISTINCT name FROM sources WHERE suite = ANY(:s)'
- rows = query_db(sqlalchemy.text(query), s=SUITES)
+ query = (
+ 'SELECT DISTINCT s.name '
+ 'FROM sources s JOIN distributions d ON d.id=s.distribution '
+ 'WHERE d.name=:d AND s.suite = ANY(:s)'
+ )
+ rows = query_db(sqlalchemy.text(query), d=DISTRO, s=SUITES)
pkgs = [Package(str(i[0]), no_notes=True) for i in rows]
log.info('Processing all %s package from all suites/architectures',
len(pkgs))
@@ -472,9 +477,16 @@ def purge_old_pages():
log.debug('page presents: ' + str(presents))
# get the existing packages
- query = "SELECT name, suite, architecture FROM sources " + \
- "WHERE suite='{}' AND architecture='{}'".format(suite, arch)
- cur_pkgs = set([(p.name, p.suite, p.architecture) for p in query_db(query)])
+ query = (
+ "SELECT s.name, s.suite, s.architecture "
+ "FROM sources s JOIN distributions d on d.id=s.distribution "
+ "WHERE s.suite=:suite AND s.architecture=:arch "
+ "AND d.name=:dist"
+ )
+ cur_pkgs = set([
+ (p.name, p.suite, p.architecture) for p in query_db(
+ sqlalchemy.text(query), suite=suite, arch=arch, dist=DISTRO)
+ ])
for page in presents:
# When diffoscope results exist for a package, we create a page
View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/commit/5e429a958763d1ba2fd90aafffeb64ab25e7e396
--
View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/commit/5e429a958763d1ba2fd90aafffeb64ab25e7e396
You're receiving this email because of your account on salsa.debian.org.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/qa-jenkins-scm/attachments/20181212/3f122147/attachment-0001.html>
More information about the Qa-jenkins-scm
mailing list