User:Qubit/Sql musings

From The Document Foundation Wiki
Jump to: navigation, search

Notes on SQL, Bugzilla, etc.

Whiteboard Tag Usage

 SELECT *
 FROM bugs_activity
 WHERE fieldid = 9      -- The Whiteboard has fieldid = 9
 LIMIT 2;

1) Find unique set of bugs we care about

 SELECT bug_id, bug_status
 FROM bugs
 WHERE bug_status NOT IN ('RESOLVED','VERIFIED','CLOSED')
 LIMIT 30;

2) Find all whiteboard tags

 SELECT DISTINCT ba.bug_id AS "Bug",
        ba.bug_when AS "When",
        ba.added,
        ba.removed
 FROM bugs_activity ba
 WHERE ba.fieldid = 9      -- The Whiteboard has fieldid = 9
 LIMIT 2;


=> What if we want to select all current whiteboard tags for open bugs?

Let's try working more in general:

( this also restricts to Whiteboards w/ a comma in them, which is not proper syntax )

 SELECT ba.bug_id AS "Bug",
        ba.bug_when AS "When",
        ba.added,
        ba.removed
 FROM bugs_activity ba
 NATURAL JOIN
     ( SELECT bug_id,
              MAX(bug_when) AS bug_when
       FROM bugs_activity
       GROUP BY bug_id ) mostrecent
 WHERE fieldid = 9      -- The Whiteboard has fieldid = 9
       AND added<>''    -- We only care about tags, so dump any empty Whiteboard fields
       AND added='%,%'
 LIMIT 50;

Grab the output for further processing: (for some reason this format of the query needs to be written as a single line...)

 \copy ( SELECT ba.bug_id AS "Bug",        ba.bug_when AS "When",        ba.added,        ba.removed FROM bugs_activity ba NATURAL JOIN     ( SELECT bug_id,              MAX(bug_when) AS bug_when       FROM bugs_activity       GROUP BY bug_id ) mostrecent WHERE fieldid = 9       AND added<>'' ) To '/tmp/whiteboard-tags.csv' With CSV HEADER DELIMITER '        '

NOTE: The TAB char in the delimiter won't copy-over in a bash terminal, so either scp that command over to the server, or (easier) copy the query over, then dellete the space that the tab is turned into, and create the right char with Ctrl-v TAB.