chart queries break Postgresql support

[sven.reimers@…]

Since bitten is using the db backend from trac, it would be nice if the queries used inside bitten would not break that support.

There seems to be some problem with Group By and sum() for loc/cov.

I'm currently investigating this further.

Any ideas?

[cmlenz]

The problem might be that PostgreSQL needs explicit casting to integers. Another possible cause would be ambigious names of the joined tables.

[sreimers <sven.reimers@…>]

Ok. I will check for the explicit cast - will that be compatible with sqlite? I will also look further into the grouping problem along the lines you supposed. If I get a patch I will attach it here.

[cmlenz]

Actually, I think the issue is just casting to integer. If you could provide the actual error message that'd be helpful.

[sreimers]

for part of the problem (and solution) see #71

the other thing is a cast problem (verified)

[florian.vogler@…]

Here is an sql statement that works for postgresql, but not for sqlite. Try to find a better solution.

SELECT build.rev, SUM(CAST(item_lines.value AS int8)) AS loc,
       SUM(CAST(item_lines.value AS int8) * CAST(item_percentage.value AS int8) / 100) AS cov
FROM bitten_build AS build
 LEFT OUTER JOIN bitten_report AS report ON (report.build=build.id)
 LEFT OUTER JOIN bitten_report_item AS item_lines
  ON (item_lines.report=report.id AND item_lines.name='lines')
 LEFT OUTER JOIN bitten_report_item AS item_percentage
  ON (item_percentage.report=report.id AND item_percentage.name='percentage' AND
      item_percentage.item=item_lines.item)
WHERE build.config=%s AND report.category='coverage'
GROUP BY build.rev_time, build.rev, build.platform
ORDER BY build.rev_time

[florian.vogler@…]

Sorry, i forgot.
The sql statement is for the Code Coverage Chart Generator? in charts.py

[cmlenz]

See the ​cast() function provided by the DB connection, and ​this code as an example for how it is applied.

[cmlenz]

[319] should fix this issue, but I haven't tested it with PostgreSQL.

If you have a chance, please test the change, and let me know whether it works. I'll then port the fix over to the 0.5.x branch if it does. Thanks.

[cmlenz]

I've ported the change in [338]. Please reopen if this doesn't fix the issue.