[SAC] Trac postgres conversion

[Jason Birch]

I believe that all of the canned report 6 "All Tickets By Milestone (Including closed)" are broken because of this statement:

ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC

The fixed version (referencing the underlying column for modified and casting the p.value) looks something like 

ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN changetime ELSE (-1 * CAST(p.value AS numeric)) END) DESC

I've fixed MapGuide's and FDO's, but it looks like others are broken, such as:

http://trac.osgeo.org/gdal/report/6

Other than that this seems to have gone well.  Great job folks!

Jason


-----Original Message-----
From: Frank Warmerdam
Sent: Friday, July 03, 2009 8:46 AM
To: System Administration Committee Discussion/OSGeo
Subject: Re: [SAC] Trac postgres conversion

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.