[SAC] Trac postgres conversion

Folks,

This evening I completed converted the remaining Trac instances
to use postgres instead of sqlite and put in place backup procedures.
I'll try and document it more completely tomorrow.

Please let me know of any problems experienced.

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

Frank Warmerdam schrieb:

Folks,

This evening I completed converted the remaining Trac instances
to use postgres instead of sqlite and put in place backup procedures.
I'll try and document it more completely tomorrow.

Please let me know of any problems experienced.

Best regards,
  
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

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@pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush | Geospatial Programmer for Rent

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.

Jason Birch wrote:

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,

Thanks for the clear analysis. I will try and fixup the other trac's today.

Best regards,

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

Frank Warmerdam schrieb:

Jason Birch wrote:

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,

Thanks for the clear analysis. I will try and fixup the other trac's today.

Best regards,

Hello Frank, hello Jason,

thanks for all your help. Christoph already fixed the reports for mapbender today.

Best regards astrid

--

Mit freundlichen Grüßen

Astrid Emde
----------------------------------

AGIT Konferenz 2009
8.-10. Juli 2009 in Salzburg
http://www.mapbender.org/Mapbender_on_Agit_2009

----------------------------------

Astrid Emde
WhereGroup GmbH & Co.KG
Siemensstraße 8
53121 Bonn
Germany

Fon: +49(0)228 90 90 38 - 19
Fax: +49(0)228 90 90 38 - 11

astrid.emde@wheregroup.com
www.wheregroup.com

Amtsgericht Bonn, HRA 6788
-------------------------------
Komplementärin:
WhereGroup Verwaltungs GmbH
vertreten durch:
Arnulf Christl, Olaf Knopp, Peter Stamm
---------------------------------------