Bugzilla:SQLCookBook
Contents
- 1 Bugzilla mysql queries
- 1.1 List all the classifications
- 1.2 List the product_id's of all the products in a particular classification
- 1.3 List of all products by classification
- 1.4 Get a list of all milestones which belong to a particular classifications
- 1.5 Get a list of all bugs with their named classification, product and component
- 1.6 Get a list of all bugs, with named classification, product and components, including categories with nothing in them.
- 1.7 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.
- 1.8 Insert a set of milestones into all the products of a classification, multiple times:
- 1.9 Get a table that is all of bugs_activity, corrected to include bug opens
- 1.10 "Edit" a comment, replacing it with a new version
- 1.11 Add everyone to a particular group
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. Here are more example queries: http://www.bugzilla.org/docs/queries.html
If you want to learn to write your own SQL query for a Bugzilla search, you can start by doing a search in Bugzilla, and then add &debug=1 to the URL of the query result page. Then it will output a bunch of stuff, including the SQL statement that it uses to search for the bugs. You can take that and edit it to suit your needs. --Newacct 19:58, 16 July 2010 (UTC)
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;
List of all products by classification
select p.id, p.name, c.id, c.name from classifications c, products p where p.classification_id = c.id order by c.name, p.name;
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;
Add everyone to a particular group
select id from groups where name = "MyProduct"; +----+ | id | +----+ | 45 | +----+ 1 row in set (0.00 sec)
We then get all the rows from profiles, add on the groups.id we just got, one '0' to say that this is just a regular user, not one who can grant this power to others, and that their inclusion is explicit. The insert ignore says "Yeah, I know we are trying to insert duplicate rows, but don't stop going, you're allowed to fail to insert the row, IGNORE that error.
insert ignore into user_group_map select userid, 45, 0, 0 from profiles;
Note that if you wanted to replace any duplicate rows with your version (removing a blessing, for instance), you could add on duplicate key update. See the MySQL manual for more info. Further note that IGNORE and ON DUPLICATE KEY UPDATE are not mutually exclusive. IGNORE really means to turn all errors into warnings.