[Qa-jenkins-scm] [jenkins.debian.net] 01/02: reproducible debian: make more sql sqlite3/postgres agnostic
Holger Levsen
holger at layer-acht.org
Mon Oct 24 22:18:47 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 ef373331a692ac8d0d81cd65b68b396504097aaa
Author: Valerie R Young <spectranaut at riseup.net>
Date: Mon Oct 24 16:59:42 2016 -0400
reproducible debian: make more sql sqlite3/postgres agnostic
This commit fixes to things in reproducible_scheduler.py:
1. Do not delete entries in sources table until all foreign
references to that source.id are deleted.
2. Cannot "SELECT DISTINCT" and then "ORDER BY" a column
that is not in the select.
Signed-off-by: Holger Levsen <holger at layer-acht.org>
---
bin/reproducible_scheduler.py | 35 ++++++++++++++++++++++-------------
1 file changed, 22 insertions(+), 13 deletions(-)
diff --git a/bin/reproducible_scheduler.py b/bin/reproducible_scheduler.py
index b266af5..c69e290 100755
--- a/bin/reproducible_scheduler.py
+++ b/bin/reproducible_scheduler.py
@@ -305,18 +305,22 @@ def update_sources_db(suite, arch, sources):
transaction = conn_db.begin()
results_table = db_table('results')
schedule_table = db_table('schedule')
+ notes_table = db_table('notes')
removed_packages_table = db_table('removed_packages')
- delete_sources_query = sources_table.delete().\
- where(sources_table.c.id == sql.bindparam('deleteid'))
delete_results_query = results_table.delete().\
where(results_table.c.package_id == sql.bindparam('deleteid'))
delete_schedule_query = schedule_table.delete().\
where(schedule_table.c.package_id == sql.bindparam('deleteid'))
+ delete_notes_query = notes_table.delete().\
+ where(notes_table.c.package_id == sql.bindparam('deleteid'))
+ delete_sources_query = sources_table.delete().\
+ where(sources_table.c.id == sql.bindparam('deleteid'))
- conn_db.execute(delete_sources_query, rmed_pkgs_id)
conn_db.execute(delete_results_query, rmed_pkgs_id)
conn_db.execute(delete_schedule_query, rmed_pkgs_id)
+ conn_db.execute(delete_notes_query, rmed_pkgs_id)
+ conn_db.execute(delete_sources_query, rmed_pkgs_id)
conn_db.execute(removed_packages_table.insert(), pkgs_to_rm)
transaction.commit()
@@ -391,14 +395,15 @@ def query_untested_packages(suite, arch, limit):
def query_new_versions(suite, arch, limit):
criteria = 'tested before, new version available, sorted by last build date'
- query = """SELECT DISTINCT s.id, s.name, s.version, r.version
+ query = """SELECT s.id, s.name, s.version, r.version, max(r.build_date) max_date
FROM sources AS s JOIN results AS r ON s.id = r.package_id
WHERE s.suite='{suite}' AND s.architecture='{arch}'
AND s.version != r.version
AND r.status != 'blacklisted'
AND s.id IN (SELECT package_id FROM results)
AND s.id NOT IN (SELECT schedule.package_id FROM schedule)
- ORDER BY r.build_date
+ GROUP BY s.id, s.name, s.version, r.version
+ ORDER BY max_date
LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit)
pkgs = query_db(query)
# the next line avoids constant rescheduling of packages:
@@ -414,7 +419,7 @@ def query_old_ftbfs_versions(suite, arch, limit):
criteria = 'status ftbfs, no bug filed, tested at least 3 days ago, ' + \
'no new version available, sorted by last build date'
date = (datetime.now()-timedelta(days=3)).strftime('%Y-%m-%d %H:%M')
- query = """SELECT DISTINCT s.id, s.name
+ query = """SELECT s.id, s.name, max(r.build_date) max_date
FROM sources AS s JOIN results AS r ON s.id = r.package_id
JOIN notes AS n ON n.package_id=s.id
WHERE s.suite='{suite}' AND s.architecture='{arch}'
@@ -422,7 +427,8 @@ def query_old_ftbfs_versions(suite, arch, limit):
AND ( n.bugs = '[]' OR n.bugs IS NULL )
AND r.build_date < '{date}'
AND s.id NOT IN (SELECT schedule.package_id FROM schedule)
- ORDER BY r.build_date
+ GROUP BY s.id, s.name
+ ORDER BY max_date
LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,
date=date)
packages = query_db(query)
@@ -434,13 +440,14 @@ def query_old_depwait_versions(suite, arch, limit):
criteria = 'status depwait, no bug filed, tested at least 2 days ago, ' + \
'no new version available, sorted by last build date'
date = (datetime.now()-timedelta(days=2)).strftime('%Y-%m-%d %H:%M')
- query = """SELECT DISTINCT s.id, s.name
+ query = """SELECT s.id, s.name, max(r.build_date) max_date
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='depwait'
AND r.build_date < '{date}'
AND s.id NOT IN (SELECT schedule.package_id FROM schedule)
- ORDER BY r.build_date
+ GROUP BY s.id, s.name
+ ORDER BY max_date
LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,
date=date)
packages = query_db(query)
@@ -453,13 +460,14 @@ def query_old_versions(suite, arch, limit):
sorted by last build date""".format(minimum_age=MINIMUM_AGE[arch])
date = (datetime.now()-timedelta(days=MINIMUM_AGE[arch]))\
.strftime('%Y-%m-%d %H:%M')
- query = """SELECT DISTINCT s.id, s.name
+ query = """SELECT s.id, s.name, max(r.build_date) max_date
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 != 'blacklisted'
AND r.build_date < '{date}'
AND s.id NOT IN (SELECT schedule.package_id FROM schedule)
- ORDER BY r.build_date
+ GROUP BY s.id, s.name
+ ORDER BY max_date
LIMIT {limit}""".format(suite=suite, arch=arch,
date=date, limit=limit)
packages = query_db(query)
@@ -470,13 +478,14 @@ def query_404_versions(suite, arch, limit):
criteria = """tested at least a day ago, status 404,
sorted by last build date"""
date = (datetime.now()-timedelta(days=1)).strftime('%Y-%m-%d %H:%M')
- query = """SELECT DISTINCT s.id, s.name
+ query = """SELECT s.id, s.name, max(r.build_date) max_date
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 = '404'
AND r.build_date < '{date}'
AND s.id NOT IN (SELECT schedule.package_id FROM schedule)
- ORDER BY r.build_date
+ GROUP BY s.id, s.name
+ ORDER BY max_date
LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,
date=date)
packages = query_db(query)
--
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