From 078490603dea0a356c79fd448c5d5a861b218992 Mon Sep 17 00:00:00 2001 From: Valerie R Young Date: Fri, 9 Sep 2016 15:12:15 -0400 Subject: reproducible debian: make python sql more sqlite/postgres agnostic This commit fixes all the python script sql queries that required minimal editing to be syntactically correct for both sqlite and postgres. Almost all fixes consist of the removal of double quotes from query strings. NOTE: the following scripts expect the schedule table's primary key to autoincrement. Running reproducible_db_maintence.py on a postgres database will not add the necessary autoincrementing functionality at this time. The following script WILL run on the sqlite database but will fail on postgres database constructed from reproducible_db_maintence.py until this is fixed: reproducible_remote_scheduler.py reproducible_schedule.py Signed-off-by: Mattia Rizzolo Signed-off-by: Holger Levsen --- bin/reproducible_common.py | 46 ++++++++++++++-------------- bin/reproducible_html_breakages.py | 24 +++++++-------- bin/reproducible_html_notes.py | 12 ++++---- bin/reproducible_html_packages.py | 16 +++++----- bin/reproducible_json.py | 8 ++--- bin/reproducible_notes.py | 8 ++--- bin/reproducible_remote_scheduler.py | 32 +++++++++---------- bin/reproducible_scheduler.py | 59 +++++++++++++++++++++--------------- 8 files changed, 107 insertions(+), 98 deletions(-) diff --git a/bin/reproducible_common.py b/bin/reproducible_common.py index 356b4afc..443ef9cf 100755 --- a/bin/reproducible_common.py +++ b/bin/reproducible_common.py @@ -202,10 +202,10 @@ filtered_issues = ( filter_query = '' for issue in filtered_issues: if filter_query == '': - filter_query = 'n.issues LIKE "%' + issue + '%"' + filter_query = "n.issues LIKE '%%" + issue + "%%'" filter_html = '' + issue + '' else: - filter_query += ' OR n.issues LIKE "%' + issue + '%"' + filter_query += " OR n.issues LIKE '%%" + issue + "%%'" filter_html += ' or ' + issue + '' @@ -470,10 +470,10 @@ def package_has_notes(package): def link_package(package, suite, arch, bugs={}, popcon=None, is_popular=None): url = RB_PKG_URI + '/' + suite + '/' + arch + '/' + package + '.html' - query = 'SELECT n.issues, n.bugs, n.comments ' + \ - 'FROM notes AS n JOIN sources AS s ON s.id=n.package_id ' + \ - 'WHERE s.name="{pkg}" AND s.suite="{suite}" ' + \ - 'AND s.architecture="{arch}"' + query = """SELECT n.issues, n.bugs, n.comments + FROM notes AS n JOIN sources AS s ON s.id=n.package_id + WHERE s.name='{pkg}' AND s.suite='{suite}' + AND s.architecture='{arch}'""" css_classes = [] if is_popular: css_classes += ["package-popular"] @@ -564,9 +564,9 @@ def pkg_has_buildinfo(package, version=False, suite=defaultsuite, arch=defaultar reproducible.db """ if not version: - query = 'SELECT r.version ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id=s.id ' + \ - 'WHERE s.name="{}" AND s.suite="{}" AND s.architecture="{}"' + query = """SELECT r.version + FROM results AS r JOIN sources AS s ON r.package_id=s.id + WHERE s.name='{}' AND s.suite='{}' AND s.architecture='{}'""" query = query.format(package, suite, arch) version = str(query_db(query)[0][0]) buildinfo = BUILDINFO_PATH + '/' + suite + '/' + arch + '/' + package + \ @@ -579,9 +579,9 @@ def pkg_has_buildinfo(package, version=False, suite=defaultsuite, arch=defaultar def pkg_has_rbuild(package, version=False, suite=defaultsuite, arch=defaultarch): if not version: - query = 'SELECT r.version ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id=s.id ' + \ - 'WHERE s.name="{}" AND s.suite="{}" AND s.architecture="{}"' + query = """SELECT r.version + FROM results AS r JOIN sources AS s ON r.package_id=s.id + WHERE s.name='{}' AND s.suite='{}' AND s.architecture='{}'""" query = query.format(package, suite, arch) version = str(query_db(query)[0][0]) rbuild = RBUILD_PATH + '/' + suite + '/' + arch + '/' + package + '_' + \ @@ -709,7 +709,7 @@ class Bug: class Issue: def __init__(self, name): self.name = name - query = 'SELECT url, description FROM issues WHERE name="{}"' + query = "SELECT url, description FROM issues WHERE name='{}'" result = query_db(query.format(self.name)) try: self.url = result[0][0] @@ -734,9 +734,9 @@ class NotedPkg: self.package = package self.suite = suite self.arch = arch - query = 'SELECT n.issues, n.bugs, n.comments ' + \ - 'FROM sources AS s JOIN notes AS n ON s.id=n.package_id ' + \ - 'WHERE s.name="{}" AND s.suite="{}" AND s.architecture="{}"' + query = """SELECT n.issues, n.bugs, n.comments + FROM sources AS s JOIN notes AS n ON s.id=n.package_id + WHERE s.name='{}' AND s.suite='{}' AND s.architecture='{}'""" result = query_db(query.format(self.package, self.suite, self.arch)) try: result = result[0] @@ -757,15 +757,15 @@ class Build: def _get_package_status(self): try: - query = 'SELECT r.status, r.version, r.build_date ' + \ - 'FROM results AS r JOIN sources AS s ' + \ - 'ON r.package_id=s.id WHERE s.name="{}" ' + \ - 'AND s.architecture="{}" AND s.suite="{}"' + query = """SELECT r.status, r.version, r.build_date + FROM results AS r JOIN sources AS s + ON r.package_id=s.id WHERE s.name='{}' + AND s.architecture='{}' AND s.suite='{}'""" query = query.format(self.package, self.arch, self.suite) result = query_db(query)[0] except IndexError: # not tested, look whether it actually exists - query = 'SELECT version FROM sources WHERE name="{}" ' + \ - 'AND suite="{}" AND architecture="{}"' + query = """SELECT version FROM sources WHERE name='{}' + AND suite='{}' AND architecture='{}'""" query = query.format(self.package, self.suite, self.arch) try: result = query_db(query)[0][0] @@ -795,7 +795,7 @@ class Package: self.status = self._status[defaultsuite][defaultarch].status except KeyError: self.status = False - query = 'SELECT notify_maintainer FROM sources WHERE name="{}"' + query = "SELECT notify_maintainer FROM sources WHERE name='{}'" try: result = int(query_db(query.format(self.name))[0][0]) except IndexError: diff --git a/bin/reproducible_html_breakages.py b/bin/reproducible_html_breakages.py index 8c3b37d9..36115fc2 100755 --- a/bin/reproducible_html_breakages.py +++ b/bin/reproducible_html_breakages.py @@ -19,7 +19,7 @@ def unrep_with_dbd_issues(): sources_without_dbd = [] query = '''SELECT s.name, r.version, s.suite, s.architecture FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status="unreproducible" + WHERE r.status='unreproducible' ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' results = query_db(query) for pkg, version, suite, arch in results: @@ -50,7 +50,7 @@ def not_unrep_with_dbd_file(): bad_pkgs = [] query = '''SELECT s.name, r.version, s.suite, s.architecture FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status != "unreproducible" + WHERE r.status != 'unreproducible' ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' results = query_db(query) for pkg, version, suite, arch in results: @@ -69,7 +69,7 @@ def lack_rbuild(): bad_pkgs = [] query = '''SELECT s.name, r.version, s.suite, s.architecture FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status NOT IN ("blacklisted", "") + WHERE r.status NOT IN ('blacklisted', '') ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' results = query_db(query) for pkg, version, suite, arch in results: @@ -86,7 +86,7 @@ def lack_buildinfo(): query = '''SELECT s.name, r.version, s.suite, s.architecture FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status NOT IN - ("blacklisted", "not for us", "FTBFS", "depwait", "404", "") + ('blacklisted', 'not for us', 'FTBFS', 'depwait', '404', '') ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' results = query_db(query) for pkg, version, suite, arch in results: @@ -108,7 +108,7 @@ def pbuilder_dep_fail(): # and specific versions also come and go query = '''SELECT s.name, r.version, s.suite, s.architecture FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status = "FTBFS" AND s.suite = "testing" + WHERE r.status = 'FTBFS' AND s.suite = 'testing' ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' results = query_db(query) for pkg, version, suite, arch in results: @@ -135,8 +135,8 @@ def alien_log(directory=None): log.info('running alien_log check over ' + directory + '...') query = '''SELECT r.version FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status != "" AND s.name="{pkg}" AND s.suite="{suite}" - AND s.architecture="{arch}" + WHERE r.status != '' AND s.name='{pkg}' AND s.suite='{suite}' + AND s.architecture='{arch}' ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' bad_files = [] for root, dirs, files in os.walk(directory): @@ -179,9 +179,9 @@ def alien_buildinfo(): log.info('running alien_log check...') query = '''SELECT r.version FROM sources AS s JOIN results AS r ON r.package_id=s.id - WHERE r.status != "" AND s.name="{pkg}" AND s.suite="{suite}" - AND s.architecture="{arch}" - AND r.status IN ("reproducible", "unreproducible") + WHERE r.status != '' AND s.name='{pkg}' AND s.suite='{suite}' + AND s.architecture='{arch}' + AND r.status IN ('reproducible', 'unreproducible') ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' bad_files = [] for root, dirs, files in os.walk(BUILDINFO_PATH): @@ -218,8 +218,8 @@ def alien_rbpkg(): log.info('running alien_rbpkg check...') query = '''SELECT s.name FROM sources AS s - WHERE s.name="{pkg}" AND s.suite="{suite}" - AND s.architecture="{arch}" + WHERE s.name='{pkg}' AND s.suite='{suite}' + AND s.architecture='{arch}' ORDER BY s.name ASC, s.suite DESC, s.architecture ASC''' bad_files = [] for root, dirs, files in os.walk(RB_PKG_PATH): diff --git a/bin/reproducible_html_notes.py b/bin/reproducible_html_notes.py index 5f5bd201..f588b149 100755 --- a/bin/reproducible_html_notes.py +++ b/bin/reproducible_html_notes.py @@ -162,9 +162,9 @@ def load_notes(): notes = copy.copy(possible_notes) for package in possible_notes: # check if every package listed on the notes try: # actually have been tested - query = 'SELECT s.name ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id=s.id ' + \ - 'WHERE s.name="{pkg}" AND r.status != ""' + query = "SELECT s.name " + \ + "FROM results AS r JOIN sources AS s ON r.package_id=s.id " + \ + "WHERE s.name='{pkg}' AND r.status != ''" query = query.format(pkg=package) result = query_db(query)[0] except IndexError: @@ -325,9 +325,9 @@ def purge_old_notes(notes): for pkg in removed_pages: for suite in SUITES: try: - query = 'SELECT s.name ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id=s.id ' + \ - 'WHERE s.name="{pkg}" AND r.status != "" AND s.suite="{suite}"' + query = "SELECT s.name " + \ + "FROM results AS r JOIN sources AS s ON r.package_id=s.id " + \ + "WHERE s.name='{pkg}' AND r.status != '' AND s.suite='{suite}'" query = query.format(pkg=pkg, suite=suite) to_rebuild.append(query_db(query)[0][0]) except IndexError: # the package is not tested. this can happen if diff --git a/bin/reproducible_html_packages.py b/bin/reproducible_html_packages.py index 250cec0c..2c2a742f 100755 --- a/bin/reproducible_html_packages.py +++ b/bin/reproducible_html_packages.py @@ -450,10 +450,10 @@ def purge_old_pages(): if page == 'diffoscope-results': continue pkg = page.rsplit('.', 1)[0] - query = 'SELECT s.name ' + \ - 'FROM sources AS s ' + \ - 'WHERE s.name="{name}" ' + \ - 'AND s.suite="{suite}" AND s.architecture="{arch}"' + query = "SELECT s.name " + \ + "FROM sources AS s " + \ + "WHERE s.name='{name}' " + \ + "AND s.suite='{suite}' AND s.architecture='{arch}'" query = query.format(name=pkg, suite=suite, arch=arch) result = query_db(query) if not result: # actually, the query produces no results @@ -476,10 +476,10 @@ def purge_old_pages(): log.debug('diffoscope page presents: ' + str(presents)) for page in presents: pkg = page.rsplit('.', 1)[0] - query = 'SELECT s.name ' + \ - 'FROM sources AS s ' + \ - 'WHERE s.name="{name}" ' + \ - 'AND s.suite="{suite}" AND s.architecture="{arch}"' + query = "SELECT s.name " + \ + "FROM sources AS s " + \ + "WHERE s.name='{name}' " + \ + "AND s.suite='{suite}' AND s.architecture='{arch}'" query = query.format(name=pkg, suite=suite, arch=arch) result = query_db(query) if not result: # actually, the query produces no results diff --git a/bin/reproducible_json.py b/bin/reproducible_json.py index 6d20a6b3..33e0fb3e 100755 --- a/bin/reproducible_json.py +++ b/bin/reproducible_json.py @@ -25,10 +25,10 @@ output4tracker = [] log.info('Creating json dump of current reproducible status') # filter_query is defined in reproducible_common.py and excludes some FTBFS issues -query = 'SELECT s.name, r.version, s.suite, s.architecture, r.status, r.build_date ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id = s.id '+ \ - 'WHERE status != "" AND status NOT IN ("not for us", "404", "blacklisted" ) AND (( status != "FTBFS" ) OR ' \ - ' ( status = "FTBFS" and r.package_id NOT IN (SELECT n.package_id FROM NOTES AS n WHERE ' + filter_query + ' )))' +query = "SELECT s.name, r.version, s.suite, s.architecture, r.status, r.build_date " + \ + "FROM results AS r JOIN sources AS s ON r.package_id = s.id "+ \ + "WHERE status != '' AND status NOT IN ('not for us', '404', 'blacklisted' ) AND (( status != 'FTBFS' ) OR " \ + " ( status = 'FTBFS' and r.package_id NOT IN (SELECT n.package_id FROM NOTES AS n WHERE " + filter_query + " )))" result = sorted(query_db(query)) log.info('\tprocessing ' + str(len(result))) diff --git a/bin/reproducible_notes.py b/bin/reproducible_notes.py index 4d8b8284..8b81781a 100755 --- a/bin/reproducible_notes.py +++ b/bin/reproducible_notes.py @@ -43,10 +43,10 @@ def load_notes(): except AssertionError: print_critical_message(pkg + ' does not include a version') irc_msg('The note for ' + pkg + ' does not include a version.') - query = 'SELECT s.id, s.version, s.suite ' + \ - 'FROM results AS r JOIN sources AS s ON r.package_id=s.id' + \ - ' WHERE s.name="{pkg}" AND r.status != ""' - #' AND s.architecture="amd64"' + query = """SELECT s.id, s.version, s.suite + FROM results AS r JOIN sources AS s ON r.package_id=s.id + WHERE s.name='{pkg}' AND r.status != ''""" + # AND s.architecture='amd64'""" query = query.format(pkg=pkg) result = query_db(query) if not result: diff --git a/bin/reproducible_remote_scheduler.py b/bin/reproducible_remote_scheduler.py index 6ba1f3a3..aec225eb 100755 --- a/bin/reproducible_remote_scheduler.py +++ b/bin/reproducible_remote_scheduler.py @@ -116,23 +116,23 @@ if arch not in ARCHS: if issue or status or built_after or built_before: formatter = dict(suite=suite, arch=arch, notes_table='') log.info('Querying packages with given issues/status...') - query = 'SELECT s.name ' + \ - 'FROM sources AS s, {notes_table} results AS r ' + \ - 'WHERE r.package_id=s.id ' + \ - 'AND s.architecture= "{arch}" ' + \ - 'AND s.suite = "{suite}" AND r.status != "blacklisted" ' + query = "SELECT s.name " + \ + "FROM sources AS s, {notes_table} results AS r " + \ + "WHERE r.package_id=s.id " + \ + "AND s.architecture= '{arch}' " + \ + "AND s.suite = '{suite}' AND r.status != 'blacklisted' " if issue: - query += 'AND n.package_id=s.id AND n.issues LIKE "%{issue}%" ' + query += "AND n.package_id=s.id AND n.issues LIKE '%{issue}%' " formatter['issue'] = issue - formatter['notes_table'] = 'notes AS n,' + formatter['notes_table'] = "notes AS n," if status: - query += 'AND r.status = "{status}"' + query += "AND r.status = '{status}'" formatter['status'] = status if built_after: - query += 'AND r.build_date > "{built_after}" ' + query += "AND r.build_date > '{built_after}' " formatter['built_after'] = built_after if built_before: - query += 'AND r.build_date < "{built_before}" ' + query += "AND r.build_date < '{built_before}' " formatter['built_before'] = built_before results = query_db(query.format_map(formatter)) results = [x for (x,) in results] @@ -158,11 +158,11 @@ if notify_on_start: ids = [] pkgs = [] -query1 = '''SELECT id FROM sources WHERE name="{pkg}" AND suite="{suite}" - AND architecture="{arch}"''' -query2 = '''SELECT p.date_build_started +query1 = """SELECT id FROM sources WHERE name='{pkg}' AND suite='{suite}' + AND architecture='{arch}'""" +query2 = """SELECT p.date_build_started FROM sources AS s JOIN schedule as p ON p.package_id=s.id - WHERE p.package_id="{id}"''' + WHERE p.package_id='{id}'""" for pkg in packages: # test whether the package actually exists result = query_db(query1.format(pkg=pkg, suite=suite, arch=arch)) @@ -214,8 +214,8 @@ log.debug('date_scheduled = ' + date + ' time_delta = ' + str(time_delta)) # a single person can't schedule more than 200 packages in the same day; this # is actually easy to bypass, but let's give some trust to the Debian people -query = '''SELECT count(*) FROM manual_scheduler - WHERE requester = "{}" AND date_request > "{}"''' +query = """SELECT count(*) FROM manual_scheduler + WHERE requester = '{}' AND date_request > '{}'""" try: amount = int(query_db(query.format(requester, int(time.time()-86400)))[0][0]) except IndexError: diff --git a/bin/reproducible_scheduler.py b/bin/reproducible_scheduler.py index a12c005d..737e1bb4 100755 --- a/bin/reproducible_scheduler.py +++ b/bin/reproducible_scheduler.py @@ -226,8 +226,8 @@ def update_sources_db(suite, arch, sources): pkg = (src['Package'], src['Version'], suite, arch) new_pkgs.append(pkg) # get the current packages in the database - query = 'SELECT name, version, suite, architecture FROM sources ' + \ - 'WHERE suite="{}" AND architecture="{}"'.format(suite, arch) + query = "SELECT name, version, suite, architecture FROM sources " + \ + "WHERE suite='{}' AND architecture='{}'".format(suite, arch) cur_pkgs = query_db(query) pkgs_to_add = [] updated_pkgs = [] @@ -236,8 +236,8 @@ def update_sources_db(suite, arch, sources): different_pkgs) for pkg in different_pkgs: # pkg: (name, version, suite, arch) - query = 'SELECT id, version, notify_maintainer FROM sources ' + \ - 'WHERE name="{}" AND suite="{}" AND architecture="{}"' + query = "SELECT id, version, notify_maintainer FROM sources " + \ + "WHERE name='{}' AND suite='{}' AND architecture='{}'" query = query.format(pkg[0], pkg[2], pkg[3]) try: result = query_db(query)[0] @@ -291,8 +291,8 @@ def update_sources_db(suite, arch, sources): rmed_pkgs) rmed_pkgs_id = [] pkgs_to_rm = [] - query = 'SELECT id FROM sources WHERE name="{}" AND suite="{}" ' + \ - 'AND architecture="{}"' + query = "SELECT id FROM sources WHERE name='{}' AND suite='{}' " + \ + "AND architecture='{}'" for pkg in rmed_pkgs: result = query_db(query.format(pkg, suite, arch)) rmed_pkgs_id.append({'deleteid': result[0][0]}) @@ -321,8 +321,8 @@ def update_sources_db(suite, arch, sources): transaction.commit() # finally check whether the db has the correct number of packages - query = 'SELECT count(*) FROM sources WHERE suite="{}" ' + \ - 'AND architecture="{}"' + query = "SELECT count(*) FROM sources WHERE suite='{}' " + \ + "AND architecture='{}'" pkgs_end = query_db(query.format(suite, arch)) count_new_pkgs = len(set([x[0] for x in new_pkgs])) if int(pkgs_end[0][0]) != count_new_pkgs: @@ -413,16 +413,18 @@ def query_new_versions(suite, arch, limit): 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 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}' AND r.status='FTBFS' AND ( n.bugs = '[]' OR n.bugs IS NULL ) - AND r.build_date < datetime('now', '-3 days') + AND r.build_date < {date} AND s.id NOT IN (SELECT schedule.package_id FROM schedule) ORDER BY r.build_date - LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit) + LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit, + date=date) packages = query_db(query) print_schedule_result(suite, arch, criteria, packages) return packages @@ -431,14 +433,16 @@ def query_old_ftbfs_versions(suite, arch, limit): 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 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 < datetime('now', '-2 days') + AND r.build_date < {date} AND s.id NOT IN (SELECT schedule.package_id FROM schedule) ORDER BY r.build_date - LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit) + LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit, + date=date) packages = query_db(query) print_schedule_result(suite, arch, criteria, packages) return packages @@ -447,14 +451,17 @@ def query_old_depwait_versions(suite, arch, limit): def query_old_versions(suite, arch, limit): criteria = """tested at least {minimum_age} days ago, no new version available, 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 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 < datetime('now', '-{minimum_age} day') + AND r.build_date < {date} AND s.id NOT IN (SELECT schedule.package_id FROM schedule) ORDER BY r.build_date - LIMIT {limit}""".format(suite=suite, arch=arch, minimum_age=MINIMUM_AGE[arch], limit=limit) + LIMIT {limit}""".format(suite=suite, arch=arch, + date=date, limit=limit) packages = query_db(query) print_schedule_result(suite, arch, criteria, packages) return packages @@ -462,14 +469,16 @@ def query_old_versions(suite, arch, limit): 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 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 < datetime('now', '-1 day') + AND r.build_date < {date} AND s.id NOT IN (SELECT schedule.package_id FROM schedule) ORDER BY r.build_date - LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit) + LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit, + date=date) packages = query_db(query) print_schedule_result(suite, arch, criteria, packages) return packages @@ -591,9 +600,9 @@ def schedule_404_versions(arch, total): def scheduler(arch): - query = 'SELECT count(*) ' + \ - 'FROM schedule AS p JOIN sources AS s ON p.package_id=s.id ' + \ - 'WHERE s.architecture="{arch}"' + query = "SELECT count(*) " + \ + "FROM schedule AS p JOIN sources AS s ON p.package_id=s.id " + \ + "WHERE s.architecture='{arch}'" total = int(query_db(query.format(arch=arch))[0][0]) log.info('==============================================================') log.info('Currently scheduled packages in all suites on ' + arch + ': ' + str(total)) @@ -627,9 +636,9 @@ def scheduler(arch): if suite not in priotized_suite_order: priotized_suite_order.append(suite) for suite in priotized_suite_order: - query = 'SELECT count(*) ' \ - 'FROM schedule AS p JOIN sources AS s ON p.package_id=s.id ' \ - 'WHERE s.suite="{suite}" AND s.architecture="{arch}"' + query = "SELECT count(*) " \ + "FROM schedule AS p JOIN sources AS s ON p.package_id=s.id " \ + "WHERE s.suite='{suite}' AND s.architecture='{arch}'" query = query.format(suite=suite, arch=arch) now_queued_here[suite] = int(query_db(query)[0][0]) + \ len(untested[suite]+new[suite]+old[suite]) @@ -676,9 +685,9 @@ if __name__ == '__main__': update_sources(suite) log.info('Sources for suite %s done at %s.', suite, datetime.now()) purge_old_pages() - query = 'SELECT count(*) ' + \ - 'FROM schedule AS p JOIN sources AS s ON s.id=p.package_id ' + \ - 'WHERE s.architecture="{}"' + query = "SELECT count(*) " + \ + "FROM schedule AS p JOIN sources AS s ON s.id=p.package_id " + \ + "WHERE s.architecture='{}'" message = '' for arch in ARCHS: log.info('Scheduling for %s...', arch) -- cgit v1.2.3-70-g09d2