Bug#1060201: qa.debian.org: [udd] carnivore_emails is lacking lots of entries

Andreas Tille

unread,

Jan 7, 2024, 7:50:04 AM1/7/24

to

Package: qa.debian.org
Severity: normal

Hi,

I tried to analyse closed bugs using done_email via carnivore_emails but realised
that this table is lacking lots of entries where I could easily add several from
my own memory:

SELECT done_email, COUNT(*) FROM (
SELECT done_email FROM archived_bugs WHERE id IN (SELECT id FROM (SELECT ab.id, ce.id AS ce_id
FROM archived_bugs ab
LEFT JOIN carnivore_emails ce ON ce.email = ab.done_email
) noid WHERE ce_id IS NULL ) AND done_email NOT IN ('ftpm...@ftp-master.debian.org','nor...@salsa.debian.org','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;

done_email | count
--------------------------------------------------------+-------
got...@sapo.pt | 5221
ba...@quantz.debian.org | 2665
do...@cs.tu-berlin.de | 2555
kit...@northeye.org | 2371
m...@Linux.IT | 2056
her...@gondor.apana.org.au | 1900
da...@merkel.debian.org | 1788
daniel....@progress-technologies.net | 1393
m...@stro.at | 1327
bu...@fs.tum.de | 1278
debia...@adam-barratt.org.uk | 1155
cch...@cheney.cx | 1031
sram...@respighi.debian.org | 992
...
zweis...@gmx.de | 1
(9075 rows)

I wonder how the carnivore_* tables are filled and whether you want me
to draft some INSERT statements filling up the most relevant emails
where I would volunteer to sort the according IDs.

Kind regards
Andreas.

Andreas Tille

unread,

Jan 7, 2024, 8:30:04 AM1/7/24

to

Control: usertag -1 udd

BTW, its probably pretty easy to resolve >900 of these missing e-mails:

CREATE TEMPORARY TABLE missing_in_carnivore_emails AS

SELECT done_email, COUNT(*) FROM (
SELECT done_email FROM archived_bugs WHERE id IN (SELECT id FROM (SELECT ab.id, ce.id AS ce_id
FROM archived_bugs ab
LEFT JOIN carnivore_emails ce ON ce.email = ab.done_email
) noid WHERE ce_id IS NULL ) AND done_email NOT IN ('ftpm...@ftp-master.debian.org','nor...@salsa.debian.org','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;

Lucas Nussbaum

unread,

Jan 7, 2024, 12:10:03 PM1/7/24

to

Hi,

On 07/01/24 at 14:21 +0100, Andreas Tille wrote:
> > I tried to analyse closed bugs using done_email via carnivore_emails but realised
> > that this table is lacking lots of entries where I could easily add several from
> > my own memory:
>

> [...]

>
> > I wonder how the carnivore_* tables are filled and whether you want me
> > to draft some INSERT statements filling up the most relevant emails
> > where I would volunteer to sort the according IDs.

See https://salsa.debian.org/qa/udd/-/blob/master/udd/carnivore_gatherer.py.

carnivore is a service managed by the QA team (or the MIA team?). UDD
just imports what is being produced in quantz:/org/qa.debian.org/carnivore
(see quantz:/org/qa.debian.org/carnivore/report in particular)

> This statement could be easily turned into injects and would be a first approach to enhance
> the carnivore_emails table with more ids.
>
> If you give some green light I could create such a statement and maybe more enhancements
> by looking into more tables.

Please don't: the correct way to fix that is to improve the source data
(in quantz:/org/qa.debian.org/carnivore)

Lucas

Andreas Tille

unread,

Jan 7, 2024, 12:10:03 PM1/7/24

to

Am Sun, Jan 07, 2024 at 05:58:36PM +0100 schrieb Lucas Nussbaum:
>
> See https://salsa.debian.org/qa/udd/-/blob/master/udd/carnivore_gatherer.py.

Found this meanwhile.

> > This statement could be easily turned into injects and would be a first approach to enhance
> > the carnivore_emails table with more ids.
> >
> > If you give some green light I could create such a statement and maybe more enhancements
> > by looking into more tables.
>
> Please don't: the correct way to fix that is to improve the source data
> (in quantz:/org/qa.debian.org/carnivore)

I have not found this code in Salsa. Is it true that the Python2 code I
can find at

quantz:/org/qa.debian.org/carnivore

is the code source of carnivore?