[Qa-jenkins-scm] [jenkins.debian.net] 01/02: reproducible debian: make python sql more sqlite/postgres agnostic

Holger Levsen holger at layer-acht.org
Mon Oct 17 09:23: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 078490603dea0a356c79fd448c5d5a861b218992
Author: Valerie R Young <spectranaut at riseup.net>
Date:   Fri Sep 9 15:12:15 2016 -0400

    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 <mattia at debian.org>
    Signed-off-by: Holger Levsen <holger at layer-acht.org>
---
 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 356b4af..443ef9c 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 = '<a href="' + REPRODUCIBLE_URL + ISSUES_URI + '/$suite/' + issue + '_issue.html">' + issue + '</a>'
     else:
-        filter_query += ' OR n.issues LIKE "%' + issue + '%"'
+        filter_query += " OR n.issues LIKE '%%" + issue + "%%'"
         filter_html += ' or <a href="' + REPRODUCIBLE_URL + ISSUES_URI + '/$suite/' + issue + '_issue.html">' + issue + '</a>'
 
 
@@ -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 8c3b37d..36115fc 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 5f5bd20..f588b14 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 250cec0..2c2a742 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 6d20a6b..33e0fb3 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 4d8b828..8b81781 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 6ba1f3a..aec225e 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 a12c005..737e1bb 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)

-- 
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