{"id":293,"date":"2008-06-07T23:43:55","date_gmt":"2008-06-07T21:43:55","guid":{"rendered":"http:\/\/www.lucas-nussbaum.net\/blog\/?p=293"},"modified":"2008-06-08T00:18:43","modified_gmt":"2008-06-07T22:18:43","slug":"dumping-launchpad-bugs-as-text","status":"publish","type":"post","link":"https:\/\/www.lucas-nussbaum.net\/blog\/?p=293","title":{"rendered":"Datamining Launchpad bugs"},"content":{"rendered":"

One think that really is annoying with Launchpad is its lack of interfaces with the outside world. No SOAP interface (well, I think that work is being done on this), no easy way to export all bugs. The only way to get all the bug data in a machine-parseable is to first fetch this URL<\/a>, and then, for each bug number listed there, to make another request for https:\/\/launchpad.net\/bugs\/$bug\/+text<\/a>. I filed a bug<\/a> a few weeks ago, asking for a simpler way to get all the data.<\/p>\n

A Launchpad dev suggested to do what I just described (fetch all the number, then fetch the data for each bug). I originally dismissed the idea because it just sounded too dirty\/aggressive\/whatever, but since I needed to practice python, I gave it a try. And actually, it works: I was able to get all the data in less than an hour (but that probably put some load on Launchpad ;-)).<\/p>\n

That allows to write cool SQL queries.<\/p>\n

Bugs with the most subscribers:<\/b><\/p>\n

select bugs.bug, title, count(*) as subscribers\r\nfrom bugs, subscribers\r\nwhere bugs.bug = subscribers.bug\r\ngroup by bugs.bug, title\r\norder by subscribers desc\r\nlimit 10;<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
bug<\/th>\nfirefox<\/th>\nsubscribers<\/th>\n<\/tr>\n
188540<\/td>\nfirefox-3.0 crashed with SIGSEGV in g_slice_alloc()<\/td>\n291<\/td>\n<\/tr>\n
154697<\/td>\npackage update-manager 1:0.81 failed to install\/upgrade: ErrorMessage: SystemError in cache.commit(): E:Sub-process \/tmp\/tmpjP6Bsx\/backports\/usr\/bin\/dpkg returned an error code (1), E:Sub-process \/tmp\/tmpjP6Bsx\/backports\/usr\/bin\/dpkg returned an error code (1), E:Sub-process \/tmp\/tmpjP6Bsx\/backports\/usr\/bin\/dpkg returned an error code (1), E:Sub-process \/tmp\/tmpjP6Bsx\/backports\/usr\/bin\/dpkg returned an error code (1)<\/td>\n278<\/td>\n<\/tr>\n
141613<\/td>\nnpviewer.bin crashed with SIGSEGV<\/td>\n262<\/td>\n<\/tr>\n
59695<\/td>\nHigh frequency of load\/unload cycles on some hard disks may shorten lifetime<\/td>\n182<\/td>\n<\/tr>\n
215005<\/td>\njockey-gtk crashed with AttributeError in enables_composite()<\/td>\n171<\/td>\n<\/tr>\n
216043<\/td>\ncompiz.real crashed with SIGSEGV<\/td>\n168<\/td>\n<\/tr>\n
121653<\/td>\n[gutsy] fglrx breaks over suspend\/resume<\/td>\n144<\/td>\n<\/tr>\n
1<\/td>\nMicrosoft has a majority market share<\/td>\n142<\/td>\n<\/tr>\n
145360<\/td>\ncompiz.real crashed with SIGSEGV<\/td>\n134<\/td>\n<\/tr>\n
23369<\/td>\nfirefox(-gnome-support) should get proxy from gconf<\/td>\n126<\/td>\n<\/tr>\n<\/table>\n
Bugs where someone is subscribed twice:<\/b><\/p>\n
select bug, subscriber_login as cnt\r\nfrom subscribers\r\ngroup by bug, subscriber_login\r\nhaving count(*) > 1;<\/pre>\n\n\n\n\n\n\n\n\n\n
bug<\/th>\nsubscriber<\/th>\n<\/tr>\n
33065<\/td>\nmvo<\/td>\n<\/tr>\n
48262<\/td>\nmvo<\/td>\n<\/tr>\n
144628<\/td>\nskyguy<\/td>\n<\/tr>\n
158126<\/td>\nbenekal<\/td>\n<\/tr>\n
213741<\/td>\nsandro-grundmann<\/td>\n<\/tr>\n
216043<\/td>\njotacayul<\/td>\n<\/tr>\n
221630<\/td>\nkami911<\/td>\n<\/tr>\n<\/table>\n
(Yes, that forced me to change a primary key)<\/p>\n
Packages with the most bugs:<\/b><\/p>\n
select package, count(distinct bug) as cnt\r\nfrom tasks\r\ngroup by package\r\norder by cnt desc\r\nlimit 10;<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
package<\/th>\nnumber<\/th>\n<\/tr>\n
ubuntu<\/td>\n5392<\/td>\n<\/tr>\n
linux<\/td>\n1464<\/td>\n<\/tr>\n
linux-source-2.6.20<\/td>\n1034<\/td>\n<\/tr>\n
update-manager<\/td>\n826<\/td>\n<\/tr>\n
linux-source-2.6.22<\/td>\n724<\/td>\n<\/tr>\n
firefox<\/td>\n684<\/td>\n<\/tr>\n
kdebase<\/td>\n673<\/td>\n<\/tr>\n
firefox-3.0<\/td>\n668<\/td>\n<\/tr>\n
ubiquity<\/td>\n590<\/td>\n<\/tr>\n
openoffice.org<\/td>\n566<\/td>\n<\/tr>\n<\/table>\n
Bugs with the shortest titles:<\/b><\/p>\n
select bug, title, length(title) as len\r\nfrom bugs\r\norder by len asc\r\nlimit 5;<\/pre>\n\n\n\n\n\n\n\n
bug<\/th>\ntitle<\/th>\nlength<\/th>\n<\/tr>\n
190560<\/td>\n–<\/td>\n1<\/td>\n<\/tr>\n
160381<\/td>\nuh<\/td>\n2<\/td>\n<\/tr>\n
224350<\/td>\ncss<\/td>\n3<\/td>\n<\/tr>\n
133621<\/td>\ngnus<\/td>\n4<\/td>\n<\/tr>\n
138052<\/td>\npbe5<\/td>\n4<\/td>\n<\/tr>\n<\/table>\n
If you want to play too, you can fetch the SQLite3 DB<\/a> (5.8M, lzma-compressed), the DB creation script<\/a>, and the script that fetches the bugs and import them into the DB<\/a>. Comments about my code would be very appreciated (stuff like “oh, there’s a better way to do that in python!”), as I’m not very confident about my pythonic skills. :-)<\/p>\n
Update:<\/b> apparently, I’m not really fetching all the bugs. I’m getting the same results as when you just press “Search” on https:\/\/launchpad.net\/ubuntu\/+bugs. But if you click on “Advanced search”, then select all the bug statuses, and click search, you get a lot more bugs (154066 vs 49031). If someone know which bugs are excluded with the default search, I’m interested!<\/p>\n
Update 2:<\/b> Got it. Apparently the default search doesn’t list bugs that have all their “tasks” marked “Won’t fix”, “Fix Released”, or “Invalid”.<\/p>\n","protected":false},"excerpt":{"rendered":"
One think that really is annoying with Launchpad is its lack of interfaces with the outside world. No SOAP interface (well, I think that work is being done on this), no easy way to export all bugs. The only way to get all the bug data in a machine-parseable is to first fetch this URL, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"0","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,12],"tags":[],"class_list":["post-293","post","type-post","status-publish","format-standard","hentry","category-planetdebian","category-planetubuntu"],"_links":{"self":[{"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=293"}],"version-history":[{"count":0,"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/293\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lucas-nussbaum.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}