3

I've created some TracReports but trying to do them with SQL, I don't get the result I was expecting. This was my SQL query:

SELECT      p.value AS __color__,
            reporter AS __group__,
            id AS ticket, summary, resolution , component, 
            t.type AS type, priority, severity, time AS created,
            changetime AS modified,
            changetime AS __changetime, description AS _description,
            reporter AS _reporter
FROM        ticket t
LEFT JOIN   enum p ON p.name = t.priority AND p.type = 'priority'
WHERE       t.status = 'closed'
ORDER BY    changetime DESC, CAST(p.value AS integer), milestone, t.type, time

Using this query, the group by reporter doesn't worked at all. I get several rows per reporter. So I tried to get the result using the "Custom Query" function. That produced my expected result with the following TracQuery:

query:?status=closed
&group=reporter
&col=id
&col=summary
&col=resolution
&col=component
&col=type
&col=priority
&col=severity
&col=time
&col=changetime
&order=changetime
&desc=1

My question is: Can I convert TracQuery into the SQL statement or can I see the SQL statements that is done in the backend? I am asking because I want to be able to cast some of the columns or use other SQL functions.

2ndkauboy
  • 9,302
  • 3
  • 31
  • 65

2 Answers2

4

Set your logging level to DEBUG, and set the configuration option [trac] debug_sql = true in your trac.ini. This will log all SQL statements made on the database, including the one corresponding to your query. The most difficult part is finding the right statement in the log.

Oh, and disable the option again when you are finished, because all that logging slows down Trac considerably.

Remy Blank
  • 4,236
  • 2
  • 23
  • 24
1

The Trac source code converts the query to SQL internally, so you should be able to edit the source to print the query string to the logfile before it submits it to the DB (I don't have the source in front of me so I can't tell you an exact code location offhand). You may not get the exact same result when you enter the SQL directly, I can't remember if Trac does any extra post-processing on the results of a SQL query over a TracQuery.

It may also help to adjust your Trac logging settings to 'DEBUG' and see if there is any useful output in the log when executing those two queries.

bta
  • 43,959
  • 6
  • 69
  • 99
  • Thanks for you tips. Unfortunately I am not a python developer so I would like not to change the code myself. But I will try the debugging mode and report if that get's me a result. – 2ndkauboy Jan 12 '11 at 16:58