Sort CVEs as versions

2 files changed, 7 insertions, 14 deletions

diff --git a/bin/tracker_service.py b/bin/tracker_service.py
index 2686bc4c24..3065715a04 100755
--- a/bin/tracker_service.py
+++ b/bin/tracker_service.py
@@ -831,7 +831,7 @@ to improve our documentation and procedures, so feedback is welcome.""")])])
                 FROM source_package_status AS st, source_packages AS sp
                 WHERE st.vulnerable AND sp.rowid = st.package
                 AND sp.release = ?  AND sp.subrelease = ''
-                ORDER BY sp.name, st.bug_name""", (rel,)):
+                ORDER BY sp.name, st.bug_name COLLATE version""", (rel,)):
                 if bf.urgencyFiltered(urgency, vulnerable):
                     continue
                 if bf.remoteFiltered(remote):
@@ -992,7 +992,7 @@ checker to find out why they have not entered testing yet."""),
                     WHERE st.vulnerable == 2 AND sp.rowid = st.package
                     AND sp.release IN (""" + ",".join("?" * len(releases)) + """)
                     AND sp.subrelease = '' AND st.bug_name == bugs.name
-                    ORDER BY sp.name, st.bug_name""", releases):
+                    ORDER BY sp.name, st.bug_name COLLATE version""", releases):
 
                 if old_bug == '':
                     old_bug = bug_name
@@ -1037,7 +1037,7 @@ checker to find out why they have not entered testing yet."""),
                     AND sp.release IN (""" + ",".join("?" * len(releases)) +  """)
                     AND st.urgency == 'unimportant'
                     AND sp.subrelease = '' AND st.bug_name == bugs.name
-                    ORDER BY sp.name, st.bug_name""", releases):
+                    ORDER BY sp.name, st.bug_name COLLATE version""", releases):
 
                 if old_bug == '':
                     old_bug = bug_name
@@ -1118,7 +1118,7 @@ package which is no longer in the archive."""),
                 AND n.bug_origin = ''
                 AND sp.name = n.package
                 AND sp.version LIKE '%:%'
-                ORDER BY bug_name, package"""):
+                ORDER BY bug_name COLLATE version, package"""):
                 if bug == old_bug:
                     bug = ''
                 else:
diff --git a/lib/python/security_db.py b/lib/python/security_db.py
index 26d1d61aca..396df949c6 100644
--- a/lib/python/security_db.py
+++ b/lib/python/security_db.py
@@ -138,14 +138,7 @@ BugsForSourcePackage_query = \
   JOIN source_packages sp ON (st.package = sp.rowid)
   WHERE sp.name = ?
   AND (bugs.name LIKE 'CVE-%' OR bugs.name LIKE 'TEMP-%')
-  ORDER BY
-    -- 'COLLATE natorder' emulation, using 0-padding (MR#76)
-    -- e.g. CVE-2016-1000393 -> CVE-2016-0001000393
-    CASE substr(bugs.name,1,3)
-      WHEN 'CVE' THEN
-        substr(bugs.name,1,9) || substr("0000000000"||substr(bugs.name, 10, 10), -10)
-      ELSE bugs.name
-    END DESC, sp.release"""
+  ORDER BY bugs.name COLLATE version DESC, sp.release"""
 # Sort order is important for the groupby operation below.
 
 def getBugsForSourcePackage(cursor, pkg):
@@ -1943,14 +1936,14 @@ class DB:
                 WHERE bugs_notes.typ = 'TODO'
                 AND bugs_notes.comment <> 'check'
                 AND bugs.name = bugs_notes.bug_name
-                ORDER BY name """)
+                ORDER BY name COLLATE version""")
         else:
             return cursor.execute(
                 """SELECT DISTINCT bugs.name, bugs.description, bugs_notes.comment
                 FROM bugs_notes, bugs
                 WHERE bugs_notes.typ = 'TODO'
                 AND bugs.name = bugs_notes.bug_name
-                ORDER BY name """)
+                ORDER BY name COLLATE version""")
 
     def getBugXrefs(self, cursor, bug):
         """Returns a generator for a list of bug names.  The listed