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 ('ftpmaster@ftp-master.debian.org','noreply@salsa.debian.org','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;

                       done_email                       | count
--------------------------------------------------------+-------
 gothicx@sapo.pt                                        |  5221
 bartm@quantz.debian.org                                |  2665
 doko@cs.tu-berlin.de                                   |  2555
 kitame@northeye.org                                    |  2371
 md@Linux.IT                                            |  2056
 herbert@gondor.apana.org.au                            |  1900
 damog@merkel.debian.org                                |  1788
 daniel.baumann@progress-technologies.net               |  1393
 max@stro.at                                            |  1327
 bunk@fs.tum.de                                         |  1278
 debian-bts@adam-barratt.org.uk                         |  1155
 ccheney@cheney.cx                                      |  1031
 sramacher@respighi.debian.org                          |   992
...
 zweistein12@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.

Am Sun, Jan 07, 2024 at 01:38:35PM +0100 schrieb Andreas Tille:

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 ('ftpmaster@ftp-master.debian.org','noreply@salsa.debian.org','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;

SELECT DISTINCT done_name, done_email, cn.id FROM
  (SELECT BTRIM(done_name, '"') AS done_name, done_email FROM archived_bugs) ab
  LEFT JOIN carnivore_names cn ON cn.name = ab.done_name
  WHERE done_email in (SELECT done_email FROM missing_in_carnivore_emails WHERE count > 10)
    AND done_name IS NOT NULL AND done_name != ''
    AND id IS NOT null
;

           done_name            |                   done_email                    |  id
---------------------------------+-------------------------------------------------+------
 Camm Maguire                    | camm@enhanced.com                               | 6158
 Ross Vandegrift                 | ross@kallisti.us                                |  734
 Michael Ablassmeier             | abi@grinser.de                                  | 2751
 Neil McGovern                   | maulkin@halon.org.uk                            | 3708
 Torsten Landschoff              | torsten@pclab.ifg.uni-kiel.de                   | 6320
 Agney Lopes Roth Ferraz         | agney@users.sourceforge.net                     | 4000
 Galen Hazelwood                 | galenh@micron.net                               | 1241
 Anand Kumria                    | wildfire@progsoc.org                            | 4175
 Adam Rogoyski                   | rogoyski@cs.utexas.edu                          | 1102
 Christophe Barbe                | christophe.barbe@ufies.org                      | 2054
 Yann Dirson                     | ydirson@fr.alcove.com                           | 5804
 Arjan Oosting                   | arjanoosting@home.nl                            | 5366
 Julian Gilbey                   | J.D.Gilbey@qmw.ac.uk                            | 3875
 Norman Jordan                   | njordan@shaw.ca                                 | 3513
 Michael Piefel                  | piefel@informatik.hu-berlin.de                  | 1111
 Frederic Lepied                 | Lepied@debian.org                               | 2460
...
 Neil Williams                   | linux@codehelp.co.uk                            | 1552
 Christopher Martin              | chrsmrtn@freeshell.org                          | 2754
 Andrew Lenharth                 | adl@cs.washington.edu                           | 3085
(922 rows)


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.

Kind regards
     Andreas.

Hi,

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)
(in quantz:/org/qa.debian.org/carnivore)

Lucas

Am Sun, Jan 07, 2024 at 05:58:36PM +0100 schrieb Lucas Nussbaum:

Found this meanwhile.

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?

Kind regards
   Andreas.