Launchpad itself

Merge ~wgrant/launchpad:optimise-gina into launchpad:master

  1. Git
  2. lp:~wgrant/launchpad
  3. optimise-gina
  4. Merge into master
Proposed by William Grant
Status: Merged
Approved by: William Grant
Approved revision: 40deb0968646913bc75b1d433850ef2fbfdf954a
Merge reported by: Otto Co-Pilot
Merged at revision: not available
Proposed branch: ~wgrant/launchpad:optimise-gina
Merge into: launchpad:master
Diff against target: 81 lines (+21/-5)
2 files modified
database/schema/patch-2210-01-3.sql (+7/-0)
lib/lp/soyuz/scripts/gina/handlers.py (+14/-5)
Reviewer Review Type Date Requested Status
Colin Watson (community) Approve
Review via email: mp+377080@code.launchpad.net

Commit message

Optimise gina source and binary DB lookups

Description of the change

The relatively modern SourcePackagePublishingHistory.sourcepackagename
and BinaryPackagePublishingHistory.binarypackagename denormalised fields
can improve gina's plans by orders of magnitude in some cases.

This also fixes gina's binary handlers (not used on production since
2006) to correctly deal with multiple archives per distribution (it's
been incorrect since 2007's ArchiveRework).

To post a comment you must log in.
Revision history for this message
Colin Watson (cjwatson) wrote :

I wouldn't be sad if you could contrive a test showing how the binary handlers were previously wrong, but otherwise LGTM.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2210-01-3.sql b/database/schema/patch-2210-01-3.sql
2new file mode 100644
3index 0000000..483ad39
4--- /dev/null
5+++ b/database/schema/patch-2210-01-3.sql
6@@ -0,0 +1,7 @@
7+-- Copyright 2019 Canonical Ltd. This software is licensed under the
8+-- GNU Affero General Public License version 3 (see the file LICENSE).
9+
10+CREATE INDEX binarypackagepublishinghistory__archive__bpr__idx
11+ ON binarypackagepublishinghistory (archive, binarypackagerelease);
12+
13+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 01, 3);
14diff --git a/lib/lp/soyuz/scripts/gina/handlers.py b/lib/lp/soyuz/scripts/gina/handlers.py
15index 7d167d5..66d2aeb 100644
16--- a/lib/lp/soyuz/scripts/gina/handlers.py
17+++ b/lib/lp/soyuz/scripts/gina/handlers.py
18@@ -560,9 +560,11 @@ class SourcePackageHandler:
19 SourcePackagePublishingHistory.distroseries =
20 DistroSeries.id AND
21 SourcePackagePublishingHistory.archive = %s AND
22+ SourcePackagePublishingHistory.sourcepackagename = %s AND
23 DistroSeries.distribution = %s
24 """ % sqlvalues(sourcepackagename, version,
25 distroseries.main_archive,
26+ sourcepackagename,
27 distroseries.distribution)
28 ret = SourcePackageRelease.select(query,
29 clauseTables=['SourcePackagePublishingHistory', 'DistroSeries'],
30@@ -740,7 +742,8 @@ class BinaryPackageHandler:
31 architecture = binarypackagedata.architecture
32
33 clauseTables = ["BinaryPackageRelease", "DistroSeries",
34- "BinaryPackageBuild", "DistroArchSeries"]
35+ "DistroArchSeries", "BinaryPackageBuild",
36+ "BinaryPackagePublishingHistory"]
37 distroseries = distroarchseries.distroseries
38
39 # When looking for binaries, we need to remember that they are
40@@ -748,13 +751,18 @@ class BinaryPackageHandler:
41 # distribution and the architecture tag of the distroarchseries
42 # they were built for
43 query = (
44+ "BinaryPackagePublishingHistory.archive = %s AND "
45+ "BinaryPackagePublishingHistory.binarypackagename = %s AND "
46+ "BinaryPackageRelease.id ="
47+ " BinaryPackagePublishingHistory.binarypackagerelease AND "
48 "BinaryPackageRelease.binarypackagename=%s AND "
49 "BinaryPackageRelease.version=%s AND "
50 "BinaryPackageRelease.build = BinaryPackageBuild.id AND "
51 "BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND "
52 "DistroArchSeries.distroseries = DistroSeries.id AND "
53- "DistroSeries.distribution = %d" %
54- (binaryname.id, quote(version), distroseries.distribution.id))
55+ "DistroSeries.distribution = %s" %
56+ sqlvalues(distroseries.main_archive, binaryname, binaryname,
57+ version, distroseries.distribution))
58
59 if architecture != "all":
60 query += ("AND DistroArchSeries.architecturetag = %s" %
61@@ -762,7 +770,7 @@ class BinaryPackageHandler:
62
63 try:
64 bpr = BinaryPackageRelease.selectOne(
65- query, clauseTables=clauseTables)
66+ query, clauseTables=clauseTables, distinct=True)
67 except SQLObjectMoreThanOneResultError:
68 # XXX kiko 2005-10-27: Untested
69 raise MultiplePackageReleaseError("Found more than one "
70@@ -845,9 +853,10 @@ class BinaryPackageHandler:
71 query = ("BinaryPackageBuild.source_package_release = %d AND "
72 "BinaryPackageBuild.distro_arch_series = "
73 " DistroArchSeries.id AND "
74+ "BinaryPackageBuild.archive = %d AND "
75 "DistroArchSeries.distroseries = DistroSeries.id AND "
76 "DistroSeries.distribution = %d"
77- % (srcpkg.id, distribution.id))
78+ % (srcpkg.id, distribution.main_archive.id, distribution.id))
79
80 if archtag != "all":
81 query += ("AND DistroArchSeries.architecturetag = %s"

Subscribers

People subscribed via source and target branches

to status/vote changes: