[SAC] Trac postgres conversion

[Frank Warmerdam]

Astrid Emde wrote:
> Hello sac-people,
> 
> we have problems with our trac
> 
> The reports are not shown anymore:
> 
> http://trac.osgeo.org/mapbender/report/42
> 
> Error:
> Report execution failed: column "modified" does not exist
> Best regards
> 
> Here is the sql from the report
> 
> ------------
> SELECT p.value AS __color__,
>   t.milestone AS __group__,
>   (CASE status
>      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: 
> #ccc;'
>      ELSE
>        (CASE owner WHEN '$USER' THEN 'font-weight: bold' END)
>    END) AS __style__,
>   id AS ticket, summary, component, status,
>   resolution,version, t.type AS type, priority, owner,
>   changetime AS modified,   time AS _time,reporter AS _reporter
>  FROM ticket t
>  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
>  WHERE  milestone = '2.6 release'
>  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
>        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC
> 
> 
> ------------------
> What happened to the column  changetime  (changetime AS modified)?
> 
> Hope I did not miss any discussion on this. I could change all our 
> reports. But maybe you have a more simple solution.

Astrid,

I tried report 12 (http://trac.osgeo.org/mapbender/report/12) which complains
about ticket not being found in:

SELECT p.value AS __color__,
    (CASE status
       WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
       ELSE
         (CASE owner WHEN '$USER' THEN 'font-weight: bold' END)
     END) AS __style__,
    id AS ticket, summary, component, status,
    resolution,version, t.type AS type, priority, owner,
    changetime AS modified,
    time AS _time,reporter AS _reporter
   FROM ticket t
   LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
   WHERE ticket > 0 AND t.type = 'defect' AND version = '2.4.2'
   ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
         (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC

The issue seems to be that sqlite and postgres sql are subtly different.  In
Postgres the "id AS ticket" renaming does not seem to be applied at the point
where the WHERE clause is evaluated so I needed to change the WHERE to:

   WHERE id > 0 AND t.type = 'defect' AND version = '2.4.2'

It was not so easy for me to check report 42.

So it appears that you will need to update your report sql a bit.  This
appears to be due to the sophistication of your reports and I haven't
heard of anyone else running into this problem yet.  The routine standard
reports seem to work ok.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent