Report 90 not functioning

[trenb@…]

New install of trac 1.0.1 and the version of the budgeting plugin from svn. Report 90 does not function. It errors out with the following error:

Report execution failed:
OperationalError: (1060, "Duplicate column name 'status'")
SELECT COUNT(*) FROM (
SELECT t.id, t.summary, t.milestone AS __group__,
           '../milestone/' || t.milestone AS __grouplink__,
           t.owner, t.reporter, t.status, t.type, t.priority, t.component,
           COUNT(b.ticket) AS num,
           SUM(b.cost) AS cost,
           SUM(b.estimation) AS estimation,
           FLOOR(AVG(b.status)) || '%' AS status, 
           (CASE t.status 
               WHEN 'closed' THEN 'budgeting_report_closed'
               ELSE (CASE SUM(b.cost) > SUM(b.estimation)
                      WHEN true THEN 'budgeting_report_est_exceeded'
                    END)
           END) AS __class__
    FROM ticket t LEFT JOIN budgeting b ON b.ticket = t.id
    WHERE
        t.milestone LIKE (CASE %s WHEN '' THEN '%' ELSE %s END)
    AND
        (t.component LIKE (CASE %s WHEN '' THEN '%' ELSE %s END)
        OR t.component IS NULL)
    AND 
        (t.owner LIKE (CASE %s WHEN '' THEN %s ELSE %s END)
        OR t.owner IS NULL OR b.username LIKE (CASE %s 
                                                WHEN '' THEN %s ELSE %s
                                              END))
    GROUP BY
        t.id, t.type, t.priority, t.summary, t.owner, t.reporter,
        t.component, t.status, t.milestone
    HAVING COUNT(b.ticket) > 0
    ORDER BY t.milestone desc, t.status, t.id
    DESC
) AS tab

Any assistance would be appreciated, as I cannot actually make use of the module.

When I create a new ticket, I do not get the option to enter budgeting information.

Kind of at a loss. Do I need to downgrade my trac installation to make this work?

Thanks in advance.

[trenb@…]

1) I was wrong about the module not working. I figured out how to add budgeting info to a ticket. 2) How do I recreate the report 90 that wasn't working? I removed it, but it doesn't seem to come back after a re-install of the module.

Thanks in advance!

[Franz]

Well this our report 90 (copied from report view in Trac):

SELECT t.id, t.summary, t.milestone AS __group__, '../milestone/' || t.milestone AS __grouplink__, t.owner, t.reporter, t.status, t.type, t.priority, t.component,
    count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schätzung, floor(avg(b.status)) || '%' AS "Status", 
(CASE t.status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE sum(b.cost) > sum(b.estimation) WHEN true THEN 'font-weight: bold; background: orange;' END)
    END) AS __style__
    from ticket t
    left join budgeting b ON b.ticket = t.id left join milestone m on t.milestone = m.name
    where t.milestone like 
    (CASE $MILESTONE
              WHEN '' THEN '%' 
              ELSE $MILESTONE END) and not milestone in ('abgelehnt', 'tbd') and
    (t.component like (CASE $COMPONENT
              WHEN '' THEN '%' 
              ELSE $COMPONENT END)) and 
    (t.owner like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) or 
     b.username like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) )
    group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.milestone, t.status, case when m.due=0 then 1500000000000000+bit_length(m.name) else m.due end
order by case when m.due=0 then 1500000000000000+bit_length(m.name) else m.due end desc, t.id desc

Note that we use German words, so maybe you get errors when using "Schätzung" at an ANSI-DB. We use Postgres 8.4 for Trac; we haven't tested it with SQLite.

[trenb@…]

I tried using your report and it fails in the same way. I am not using SQLite, but MySQL 5.5.

Does this report only work on Postgres? If so, it should be marked as such.

Here is your report with the german words translated:

Report execution failed:
OperationalError: (1060, "Duplicate column name 'Status'")
SELECT COUNT(*) FROM (
SELECT t.id, t.summary, t.milestone AS __group__, '../milestone/' ||
t.milestone AS __grouplink__, t.owner, t.reporter, t.status, t.type,
t.priority, t.component,
     count(b.ticket) AS Num, sum(b.cost) AS Cost, sum(b.estimation) AS
Estimation, floor(avg(b.status)) || '%' AS "Status",
(CASE t.status
       WHEN 'closed' THEN 'color: #777; background: #ddd; border-color:
#ccc;'
       ELSE
         (CASE sum(b.cost) > sum(b.estimation) WHEN true THEN 'font-weight:
bold; background: orange;' END)
     END) AS __style__
     from ticket t
     left join budgeting b ON b.ticket = t.id left join milestone m on
t.milestone = m.name
     where t.milestone like
     (CASE %s
               WHEN '' THEN '%'
               ELSE %s END) and not milestone in ('rejected',
'tbd') and
     (t.component like (CASE %s
               WHEN '' THEN '%'
               ELSE %s END)) and
     (t.owner like (CASE %s
               WHEN '' THEN %s
               ELSE %s END) or
      b.username like (CASE %s
               WHEN '' THEN %s
               ELSE %s END) )
     group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter,
t.component, t.milestone, t.status, case when m.due=0 then
1500000000000000+bit_length(m.name) else m.due end
order by case when m.due=0 then 1500000000000000+bit_length(m.name) else
m.due end desc, t.id desc
) AS tab

[Franz]

Replying to trenb@…: When I replace (CASE %s ... ) (from where t.milestone like until group by t.id... with that it works:

(CASE $MILESTONE
              WHEN '' THEN '%' 
              ELSE $MILESTONE END) and not milestone in ('abgelehnt', 'tbd') and
    (t.component like (CASE $COMPONENT
              WHEN '' THEN '%' 
              ELSE $COMPONENT END)) and 
    (t.owner like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) or 
     b.username like (CASE $OWNER
              WHEN '' THEN $USER 
              ELSE $OWNER END) )

Note that you only get an count with your SQL.

[Kirk]

apply this changeset to the source and it will populate a ticket query with SQL that works with sqlite and not only with postgres, and the filter box works as expected

Index: ticketbudgeting/ticketbudgeting.py
===================================================================
--- ticketbudgeting/ticketbudgeting.py	(revision 16144)
+++ ticketbudgeting/ticketbudgeting.py	(working copy)
@@ -250,30 +250,22 @@
 
 
     BUDGET_REPORTS = [(BUDGET_REPORT_ALL_ID, 'report_title_90', 'report_description_90',
-    u"""SELECT t.id, t.summary, t.milestone AS __group__, ''../milestone/'' || t.milestone AS __grouplink__,
+    u"""SELECT t.id, t.summary, t.milestone AS __group__, ''../milestone/'' || t.milestone AS __grouplink__, 
     t.owner, t.reporter, t.status, t.type, t.priority, t.component,
-    count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schaetzung,
-    floor(avg(b.status)) || ''%'' AS "Status",
-    (CASE t.status
-      WHEN ''closed'' THEN ''color: #777; background: #ddd; border-color: #ccc;''
-      ELSE
-        (CASE sum(b.cost) > sum(b.estimation) WHEN true THEN ''font-weight: bold; background: orange;'' END)
-    END) AS __style__
+    count(b.ticket) AS Count, sum(b.cost) AS Cost, sum(b.estimation) AS Effort,
+    cast(avg(b.status) as int) || ''%'' AS "Status"
     from ticket t
     left join budgeting b ON b.ticket = t.id
-    where t.milestone like
+    where t.milestone like 
     (CASE $MILESTONE
-              WHEN '''' THEN ''%''
+              WHEN '''' THEN ''%'' 
               ELSE $MILESTONE END) and
     (t.component like (CASE $COMPONENT
-              WHEN '''' THEN ''%''
-              ELSE $COMPONENT END) or t.component is null) and
-    (t.owner like (CASE $OWNER
-              WHEN '''' THEN $USER
-              ELSE $OWNER END) or t.owner is null or
-     b.username like (CASE $OWNER
-              WHEN '''' THEN $USER
-              ELSE $OWNER END) )
+              WHEN '''' THEN ''%'' 
+              ELSE $COMPONENT END) or t.component is null) and 
+    b.username like (CASE $OWNER
+              WHEN '''' THEN ''%''
+              ELSE $OWNER END)
     group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.status, t.milestone
     having count(b.ticket) > 0
     order by t.milestone desc, t.status, t.id desc""")

[Ryan J Ollos]

In 16149:

0.6.6dev: Fix SQL errors in report 90 with SQLite

Patch by Jun Omae. Refs #12831, Fixes #11211.