OSSP CVS Repository
SELECT status, count(case when type='code' then 'x' end), count(case when type='doc' then 'x' end), count(case when type='new' then 'x' end), count(case when type NOT IN ('code','doc','new') then 'x' end), count(*) FROM ticket GROUP BY status UNION SELECT 'TOTAL' AS 'Status', count(case when type='code' then 'x' end) as 'Code Bugs', count(case when type='doc' then 'x' end) as 'Doc Bugs', count(case when type='new' then 'x' end) as 'Enhancements', count(case when type NOT IN ('code','doc','new') then 'x' end) as 'Other', count(*) AS 'All Types' FROM ticket ORDER BY [All Types]
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 );
The SQL must consist of a single SELECT statement
If a column of the result set is named "#" then that column is assumed to hold a ticket number. A hyperlink will be created from that column to a detailed view of the ticket.
If a column of the result set is named "bgcolor" then the content of that column determines the background color of the row.
Times in the TICKET table are expressed as seconds since 1970. Convert these values to human-friendly date formats using the sdate() and ldate() SQL functions.
The now() SQL function returns the current time and date in seconds since 1970. The user() SQL function returns a string which is the login of the current user.
The first column whose name begins with underscore ("_") and all subsequent columns are shown on their own rows in the table. This is useful for displaying the TICKET.DESCRIPTION and TICKET.REMARKS fields.
The aux() SQL function takes a parameter name as an argument and returns the value that the user enters in the resulting HTML form. A second optional parameter provides a default value for the field.
The option() SQL function takes a parameter name and a quoted SELECT statement as parameters. The query results are presented as an HTML dropdown menu and the function returns the currently selected value. Results may be a single value column or two value,description columns. The first row is the default.
The cgi() SQL function takes a parameter name as an argument and returns the value of a corresponding CGI query value. If the CGI parameter doesn't exist, an optional second argument will be returned instead.
If a column is wrapped by the wiki() SQL function, it will be rendered as wiki formatted content.
If a column is wrapped by the tkt() SQL function, it will be shown as a ticket number with a link to the appropriate page
If a column is wrapped by the chng() SQL function, it will be shown as a checkin number with a link to the appropriate page.
The path() SQL function can be used to extract complete filename from FILE table. For example:
SELECT path(isdir, dir, base) AS 'filename' FROM file
The dirname() SQL function takes filename as only argument and extracts parent directory name from it.
The basename() SQL function takes filename as only argument and extracts basename from it.
The search() SQL function takes a keyword pattern and a search text. The function returns an integer score which is higher depending on how well the search went.
The query can join other tables in the database besides TICKET.
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|
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:
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