SPPH.ancestor is set for new copies, but nobody has ever gone back to populate it for old ones. Worse, it briefly had a different meaning where each SPPH had it set to the previous version of the same package. Witness the chaos (where I assert that both these counts should be 0 if this transformation is going to be sensible):
launchpad_dogfood=# SELECT COUNT(*) FROM sourcepackagepublishinghistory spph, sourcepackagerelease spr WHERE spph.sourcepackagerelease = spr.id AND (spph.archive != spr.upload_archive OR spph.distroseries != spr.upload_distroseries) AND spph.ancestor IS NULL;
count
--------
809350
(1 row)
launchpad_dogfood=# SELECT COUNT(*) FROM sourcepackagepublishinghistory spph, sourcepackagerelease spr WHERE spph.sourcepackagerelease = spr.id AND spph.archive = spr.upload_archive AND spph.distroseries = spr.upload_distroseries AND spph.ancestor IS NOT NULL;
count
-------
4576
(1 row)
As a result I don't think ancestor can be used without some fairly substantial garbo job work to clean all this up. (I'm not volunteering.)
I'm handling the display of SPPH.creator as bug 1032857.
SPPH.ancestor is set for new copies, but nobody has ever gone back to populate it for old ones. Worse, it briefly had a different meaning where each SPPH had it set to the previous version of the same package. Witness the chaos (where I assert that both these counts should be 0 if this transformation is going to be sensible):
launchpad_dogfood=# SELECT COUNT(*) FROM sourcepackagepu blishinghistory spph, sourcepackagere lease spr WHERE spph.sourcepack agerelease = spr.id AND (spph.archive != spr.upload_archive OR spph.distroseries != spr.upload_ distroseries) AND spph.ancestor IS NULL;
count
--------
809350
(1 row)
launchpad_dogfood=# SELECT COUNT(*) FROM sourcepackagepu blishinghistory spph, sourcepackagere lease spr WHERE spph.sourcepack agerelease = spr.id AND spph.archive = spr.upload_archive AND spph.distroseries = spr.upload_ distroseries AND spph.ancestor IS NOT NULL;
count
-------
4576
(1 row)
As a result I don't think ancestor can be used without some fairly substantial garbo job work to clean all this up. (I'm not volunteering.)
I'm handling the display of SPPH.creator as bug 1032857.