Bugzilla:SQLCookBook

Bugzilla mysql queries

This is a set of mysql queries to get data out of the bugzilla database. This is really here for sql weenies, and probably not too useful to anyone else.

Please feel free to edit these for current practice.

List all the classifications

select id, name from classifications;

List the product_id's of all the products in a particular classification

assumes you know the classification_id

select products.id from products where products.classification_id = 5;

Get a list of all milestones which belong to a particular classifications

select * from milestones where product_id = any (select products.id from products where products.classification_id = 5);

---

The difference between the next two code bits is that the last join, against bugs, is a left join instead a regular join.

Get a list of all bugs with their named classification, product and component

select cmp.name, p.name, cl.name, bugs.bug_id
from classifications cl left join products p on p.classification_id = cl.id
                       left join components cmp on cmp.product_id = p.id
                            join bugs on bugs.component_id = cmp.id
order by bugs.bug_id

Get a list of all bugs, with named classification, product and components, including categories with nothing in them.

select cmp.name, p.name, cl.name, bugs.bug_id
from classifications cl left join products p on p.classification_id = cl.id
                       left join components cmp on cmp.product_id = p.id
                       left join bugs on bugs.component_id = cmp.id
order by bugs.bug_id

---

Get a list of the bug opening times from bugs, and combine it with the bugs_activity table to make a table which actually has all the transitions in it.

Note that I actually limit to the changes in field 9, which is the bug status.

select bug_id, creation_ts, 'OPENED' from bugs union all select bug_id, bug_when, added from bugs_activity where fieldid = 9 order by bug_id;

---

Insert a set of milestones into all the products of a classification, multiple times:

set @num := 0;
set @sk := 0;
set @num := @num + 1;
set @sk := @sk + 100;
select @num, @sk;
insert into milestones(product_id, value,sortkey) select id, concat("Sprint ",@num), @sk from products where classification_id = 5;
select product_id, value, sortkey from milestones, products where milestones.product_id = products.id and products.classification_id = 5;

---

Get a table that is all of bugs_activity, corrected to include bug opens

select bug_id, bug_when, added as state from bugs_activity
 where fieldid = 9
union all
 select bug_id, creation_ts, 'OPENED'
 from bugs order by bug_id;

---

"Edit" a comment= replacing it with a new version

update longdescs set thetext='(In reply to comment #2)\n\> Technical details such as this should pro
bably be included in a separate, more\n\> specific CVS training curriculum rather than a general (S)
CM class.\n\> Wasn't someone in the Software Competency going to put together a CVS class?\n\nSee bu
g 1555 (my request for a CVS training class)\n'   where comment_id=4520;