security_db: speed up rendering the web tracker

I checked real usage of the ELTS security tracker for locations taking long. By far the longest one is /source-package/linux. Some per-CVE views can also take time. I went on and profiled them using austin in a local installation of the Debian security tracker. With these profiles in hand I looked for bottlenecks and this is what came about. The /source-package/linux view (being by far the slowest of all source packages) should be sped up twice. The CVE views should also be sped up noticeably, but they don't take nearly as long.

Note that I did not consider the schema changes as a reason to increase the schema version. An old security.db continues to work with the old schema, but recreating the database will extend indices and avoid full table scans. I hope this approach is ok as it avoids complexity.

Some of the changes (such as removing the version collation from SQL) are kinda backwards as the original implementation was arguably cleaner, but this provided the largest speedup.

Merge request reports

Loading