On 17/08/15 at 19:34 +0200, Martin Michlmayr wrote:
> Package: qa.debian.org
> Severity: minor
> User: qa.debian@packages.debian.org
> Usertags: udd
>
> I was looking for uploads sponsored by me recently. I searched in
> upload_history by fingerprint and noticed that it says 'N/A'. signed_by,
> signed_by_name and signed_by_email are N/A or empty, too. key_id has the
> correct information though. It would be great to fill in these values.
>
> One example to show the missing information is:
>
> SELECT * FROM upload_history WHERE source = 'icheck' and version =
> '0.9.7-6.2';
>
> This is not a recent problem, it seems:
>
> udd=> SELECT count(*) FROM upload_history WHERE signed_by = 'N/A';
> count
> ---
> 29861
> (1 row)
>
> udd=> SELECT count(*) FROM upload_history WHERE fingerprint = 'N/A';
> count
> ---
> 30378
> (1 row)
Unfortunately, the current email parsing code needs the key to be in one
known keyring to determine the key and fingerprint. So the older the
upload, the more unlikely it is that the key is still in the keyring:
udd=> select extract (year from date) as year,
count(*),
count(case when fingerprint = 'N/A' then 1 end),
count(case when signed_by = 'N/A' then 1 end)
from upload_history
group by 1
order by 1 desc;
year | count | count | count
--+---+---+---
2024 | 9835 | 3488 | 2687
2023 | 45189 | 728 | 728
2022 | 57792 | 1110 | 1088
2021 | 44760 | 333 | 333
2020 | 51791 | 1891 | 1891
2019 | 39710 | 3617 | 3616
2018 | 47934 | 4101 | 4101
2017 | 34879 | 2909 | 2909
2016 | 46990 | 3931 | 3931
2015 | 36589 | 2780 | 2780
2014 | 35260 | 8992 | 8992
2013 | 31344 | 14045 | 14045
2012 | 31469 | 15832 | 15832
2011 | 32882 | 18602 | 18601
2010 | 29099 | 22663 | 22622
2009 | 32542 | 31200 | 31171
2008 | 31996 | 31662 | 31662
2007 | 28315 | 28109 | 28102
2006 | 32650 | 32551 | 32550
2005 | 27461 | 27308 | 27303
2004 | 30057 | 29709 | 29654
2003 | 27440 | 27391 | 27389
2002 | 27411 | 27411 | 27411
2001 | 24983 | 24983 | 24983
2000 | 10860 | 10860 | 10860
1999 | 12977 | 12977 | 12977
1998 | 3102 | 3102 | 3102
(27 rows)
This would probably need to be re-thought.
There's also something strange in 2024 that needs to be investigated:
udd=> select changed_by_email, count(*) from upload_history where
fingerprint='N/A' and date > '2024-01-01' group by changed_by_email order by 2
desc limit 10;
changed_by_email | count
--+---
vor...@debian.org| 414
bdr...@debian.org| 240
ti...@debian.org | 130
jbi...@ubuntu.com| 104
z...@debian.org |97
werdah...@riseup.net |97
mwhud...@debian.org |84
sl...@debian.org |79
gin...@debian.org|54
d...@jones.dk |53
(10 rows)
Lucas
