OSSP CVS Repository

ossp - SQL For Report Format Number 3
Not logged in
[Honeypot]  [Browse]  [Home]  [Login]  [Reports
[Search]  [Ticket]  [Timeline
  [View

Title: Tickets associated with current user
Owner:
SQL:
SELECT
  CASE priority WHEN 1 THEN '#f2dcdc'
       WHEN 2 THEN '#e8e8bd'
       WHEN 3 THEN '#cfe8bd'
       WHEN 4 THEN '#cacae5'
       ELSE '#c8c8c8' END as 'bgcolor',
  tn AS '#',
  type AS 'Type',
  status AS 'Status',
  sdate(origtime) AS 'Created',
  owner AS 'By',
  subsystem AS 'Subsys',
  sdate(changetime) AS 'Changed',
  assignedto AS 'Assigned',
  severity AS 'Svr',
  priority AS 'Pri',
  title AS 'Title'
FROM ticket
WHERE owner=user() OR assignedto=user()
Priority:
1
2
3
4
5

TICKET Schema

CREATE TABLE ticket(
   tn integer primary key,  -- Unique tracking number for the ticket
   type text,               -- code, doc, todo, new, or event
   status text,             -- new, review, defer, active, fixed,
                            -- tested, or closed
   origtime int,            -- Time this ticket was first created
   changetime int,          -- Time of most recent change to this ticket
   derivedfrom int,         -- This ticket derived from another
   version text,            -- Version or build number
   assignedto text,         -- Whose job is it to deal with this ticket
   severity int,            -- How bad is the problem
   priority text,           -- When should the problem be fixed
   subsystem text,          -- What subsystem does this ticket refer to
   owner text,              -- Who originally wrote this ticket
   title text,              -- Title of this bug
   description text,        -- Description of the problem
   remarks text             -- How the problem was dealt with
);

Notes

Examples

In this example, the first column in the result set is named "bgcolor". The value of this column is not displayed. Instead, it selects the background color of each row based on the TICKET.STATUS field of the database. The color key at the right shows the various color codes.

new or active
review
fixed
tested
defer
closed
SELECT
  CASE WHEN status IN ('new','active') THEN '#f2dcdc'
       WHEN status='review' THEN '#e8e8bd'
       WHEN status='fixed' THEN '#cfe8bd'
       WHEN status='tested' THEN '#bde5d6'
       WHEN status='defer' THEN '#cacae5'
       ELSE '#c8c8c8' END as 'bgcolor',
  tn AS '#',
  type AS 'Type',
  status AS 'Status',
  sdate(origtime) AS 'Created',
  owner AS 'By',
  subsystem AS 'Subsys',
  sdate(changetime) AS 'Changed',
  assignedto AS 'Assigned',
  severity AS 'Svr',
  priority AS 'Pri',
  title AS 'Title'
FROM ticket

To base the background color on the TICKET.PRIORITY or TICKET.SEVERITY fields, substitute the following code for the first column of the query:

1
2
3
4
5
SELECT
  CASE priority WHEN 1 THEN '#f2dcdc'
       WHEN 2 THEN '#e8e8bd'
       WHEN 3 THEN '#cfe8bd'
       WHEN 4 THEN '#cacae5'
       ELSE '#c8c8c8' END as 'bgcolor',
...
FROM ticket

To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include them as the last two columns of the result set and given them names that begin with an underscore. Like this:

 SELECT
   tn AS '#',
   type AS 'Type',
   status AS 'Status',
   sdate(origtime) AS 'Created',
   owner AS 'By',
   subsystem AS 'Subsys',
   sdate(changetime) AS 'Changed',
   assignedto AS 'Assigned',
   severity AS 'Svr',
   priority AS 'Pri',
   title AS 'Title',
   description AS '_Description',   -- When the column name begins with '_'
   remarks AS '_Remarks'            -- the data is shown on a separate row.
 FROM ticket

Or, to see part of the description on the same row, use the wiki() function with some string manipulation. Using the tkt() function on the ticket number will also generate a linked field, but without the extra edit column:

 SELECT
   tkt(tn) AS '',
   title AS 'Title',
   wiki(substr(description,0,80)) AS 'Description'
 FROM ticket

CVSTrac 2.0.1