launchpad-dev team mailing list archive

[Jonathan Lange <jml@xxxxxxxxxxxxx>]

Hello Launchpadders & UDDers,

Since we want people to be able to easily build packages out of the
trunk of their upstreams, we want to have working Bazaar branches for
the trunks of the upstreams of all our source packages, starting with
the ones in 'main'.

To me, this is a quantifiable goal, so I think we should have a graph
that shows the following:
 - Number of packages (maybe in an interesting subset)
 - Number of these packages with upstream links
 - Number of packages that have upstream links that have trunk branches
 - Number of packages that have upstream links that have trunk
branches that work

When all of the lines converge, we win, and get to eat cake.

I've written some queries that measure these things, which I shall ask
to be put onto a graph by-and-by. However, I'd like you to have a look
at these queries and review them for correctness.

For those who are interested, the numbers for Lucid are:
 - Packages in main: 16143
 - Packages in main w/ upstream links: 16
 - Packages in main w/ upstream links w/ branches: 9
 - Packages in main w/ upstream links w/ products that have trunk branches: 9
 - Packages that have upstream links that have trunk branches that work: 9

The numbers for karmic:
 - Packages in main: 16217
 - Packages in main w/ upstream links: 340
 - Packages in main w/ upstream links w/ branches: 217
 - Packages in main w/ upstream links w/ products that have trunk branches: 230
 - Packages that have upstream links that have trunk branches that work: 223

The poor quality of the data in the Packaging table makes me think
that we need to:
  a) rethink the details of this particular goal (look under the
streetlight, so to speak)
  b) rethink the way we're gathering data
  c) make package / product linking way better.

Anyway, here are the queries.

jml

-- Number of packages in main
SELECT COUNT(*)
FROM Archive, Distribution, DistroSeries,
  SourcePackagePublishingHistory as SPPH
WHERE SPPH.archive = Archive.id
AND Archive.purpose = 1
AND Archive.distribution = Distribution.id
AND Distribution.name = 'ubuntu'
AND DistroSeries.distribution = Distribution.id
AND DistroSeries.name = 'lucid'
AND SPPH.distroseries = DistroSeries.id
AND SPPH.pocket in (0, 20)
AND SPPH.status = 2;


-- Number of these packages with upstream links
SELECT COUNT(*)
FROM Archive, Distribution, DistroSeries,
  SourcePackagePublishingHistory as SPPH, SourcePackageRelease, Packaging
WHERE SPPH.archive = Archive.id
AND Archive.purpose = 1
AND Archive.distribution = Distribution.id
AND Distribution.name = 'ubuntu'
AND DistroSeries.distribution = Distribution.id
AND DistroSeries.name = 'lucid'
AND SPPH.distroseries = DistroSeries.id
AND SPPH.pocket in (0, 20)
AND SPPH.status = 2
AND SPPH.sourcepackagerelease = SourcePackageRelease.id
AND SourcePackageRelease.sourcepackagename = Packaging.sourcepackagename
AND DistroSeries.id = Packaging.distroseries;


-- Number of packages that have upstream links that have branches
SELECT COUNT(*)
FROM Archive, Distribution, DistroSeries,
  SourcePackagePublishingHistory as SPPH, SourcePackageRelease, Packaging,
  ProductSeries
WHERE SPPH.archive = Archive.id
AND Archive.purpose = 1
AND Archive.distribution = Distribution.id
AND Distribution.name = 'ubuntu'
AND DistroSeries.distribution = Distribution.id
AND DistroSeries.name = 'lucid'
AND SPPH.distroseries = DistroSeries.id
AND SPPH.pocket in (0, 20)
AND SPPH.status = 2
AND SPPH.sourcepackagerelease = SourcePackageRelease.id
AND SourcePackageRelease.sourcepackagename = Packaging.sourcepackagename
AND DistroSeries.id = Packaging.distroseries
AND Packaging.productseries = ProductSeries.id
AND ProductSeries.branch IS NOT NULL;


-- Number of packages that have upstream links that have products that
have trunk branches
SELECT COUNT(*)
FROM Archive, Distribution, DistroSeries,
SourcePackagePublishingHistory as SPPH,
  SourcePackageRelease, Packaging, ProductSeries, Product,
ProductSeries AS DevFocus
WHERE SPPH.archive = Archive.id
AND Archive.purpose = 1
AND Archive.distribution = Distribution.id
AND Distribution.name = 'ubuntu'
AND DistroSeries.distribution = Distribution.id
AND DistroSeries.name = 'lucid'
AND SPPH.distroseries = DistroSeries.id
AND SPPH.pocket in (0, 20)
AND SPPH.status = 2
AND SPPH.sourcepackagerelease = SourcePackageRelease.id
AND SourcePackageRelease.sourcepackagename = Packaging.sourcepackagename
AND DistroSeries.id = Packaging.distroseries
AND Packaging.productseries = ProductSeries.id
AND ProductSeries.product = Product.id
AND Product.development_focus = DevFocus.id
AND DevFocus.branch IS NOT NULL;


-- Number of packages that have upstream links that have trunk
branches that work
SELECT COUNT(*)
FROM Archive, Distribution, DistroSeries,
SourcePackagePublishingHistory as SPPH,
  SourcePackageRelease, Packaging, ProductSeries, Product,
ProductSeries AS DevFocus,
  Branch
WHERE SPPH.archive = Archive.id
AND Archive.purpose = 1
AND Archive.distribution = Distribution.id
AND Distribution.name = 'ubuntu'
AND DistroSeries.distribution = Distribution.id
AND DistroSeries.name = 'lucid'
AND SPPH.distroseries = DistroSeries.id
AND SPPH.pocket in (0, 20)
AND SPPH.status = 2
AND SPPH.sourcepackagerelease = SourcePackageRelease.id
AND SourcePackageRelease.sourcepackagename = Packaging.sourcepackagename
AND DistroSeries.id = Packaging.distroseries
AND Packaging.productseries = ProductSeries.id
AND ProductSeries.product = Product.id
AND Product.development_focus = DevFocus.id
AND DevFocus.branch = Branch.id
AND Branch.mirror_failures = 0
AND Branch.last_scanned_id IS NOT NULL;