Edgewall Software
Modify

Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#12656 closed enhancement (worksforme)

Allow common table expression in reports' SQL queries

Reported by: thenor Owned by:
Priority: normal Milestone:
Component: report system Version:
Severity: trivial Keywords: postgresql
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

The trac reporting systems performs some rewriting of SQL queries in reports. The consequence is that the query must be a SELECT.

However, for instance th:SubticketsPlugin needs a way to perform recursive queries in order to report on ticket hierarchies - and that is not possible in a simple SELECT. This is currently solved by means of a manually installed stored procedure that in turn is called through a normal SELECT. It is described in more detail here: th:SubticketsPlugin/Reporting.

The technique used in the plugin is a Common Table Expression; it is described here: https://www.postgresql.org/docs/9.1/static/queries-with.html

I suspect that e.g. th:MasterTicketsPlugin and other plugins that deal with ticket relations could benefit from a similar improvement. Plus, use of WITH can simplify many "ordinary" queries.

If that turns out to be unrealistic, it would be helpful if there was a nice way to install stored procedures in a reasonably database-independent manner through plugins' initialisation code.

Attachments (0)

Change History (5)

comment:1 by Jun Omae, 7 years ago

I tried the following query on Trac 1.0.13 and 1.2. It seems to be worked.

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t

Also, I think you could wrap with SELECT * FROM (WITH ...) if it doesn't work.

I'd like to close as wontfix.

comment:2 by Jun Omae, 7 years ago

Keywords: postgresql added

comment:3 by Jun Omae, 7 years ago

According to this entry, CTE query would be available since MySQL 8.0.

comment:4 by theodor.norup@…, 7 years ago

Resolution: wontfix
Status: newclosed

Sorry, I feel a bit stupid, works for me as well. Wonder, what I've been mixing up…

Maybe it is relevant to mention this possibility explicitly in the report documentation?

in reply to:  4 comment:5 by Ryan J Ollos, 7 years ago

Resolution: wontfixworksforme

Replying to theodor.norup@…:

Maybe it is relevant to mention this possibility explicitly in the report documentation?

Sure, feel free to edit TracReports. A statement after the last sentence in TracReports#CreatingCustomReports might be appropriate.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.