Reporting subtickets for Trac

The plugin SubticketsPlugin contains stored functions that can aid in the reporting of subticket hierarchies in Trac. Using such functions is necessary due to TracReports rewriting of SQL queries.

The functions are found in the reporting ​subdirectory of the plugin code.

Currently only PostgreSQL is supported. The query technique used is supported by at least MySql and SQLServer, but the DDLs differ slightly. Patches are welcome.

Simple Example

The tracsubticket_tree() function returns a depth-first search of all tickets and subtickets. A simple report using that function is:

SELECT
    r.id, path, parent, summary, component,
    CASE WHEN parent IS NULL 
         THEN 'border-bottom:solid 3px #DDD;border-top: solid 3px #DDD;background-color:#DDD'    
         ELSE 'text-indent: ' ||10*level||'px' 
         END AS __style__
FROM 
    tracsubticket_tree() r 
JOIN 
    ticket t on r.id=t.id 
ORDER BY path

The result of this report query looks as follows:

Thanks to ​schneimi for the idea.