On 6/30/2016 3:10 AM, Stephen De Gabrielle wrote:
The default tickets with comments report doesn't seem to work.
(The default examples don't seem to match the default schema)
I thought I recalled some discussion of the documentation of tickets and
the sample reports around the time that the ticketchng table was
introduced, but apparently I'm not remembering correctly, or only part
of the ticket system got updated.
As it stands right now, the ticket.comment field is effectively unused.
It will be displayed for backward compatibility by /tktview if it has a
value, but /tktnew for some time now stores the original comment in
ticketchng.icomment instead.
I can confirm that the default report format provided by a brand new
nearly unconfigured repo using Fossil v1.35 refers to only fields in the
ticket table, and specifically favors ticket.comment and completely
ignores any related entries in the ticketchng table.
That is certainly an oversight that out to be called a bug.
In my quick review I noticed that the words "comment", "description",
and "remark" seem to get used inconsistently in the various bits of
documentation and UI. That should also get reviewed and made consistent.
I'd appreciate it if list members could let me know if this works for them:
--
select
CASE WHEN status IN ('new','active') THEN '#f2dcdc'
WHEN status='review' THEN '#e8e8bd'
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status='defer' THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
substr(t.tkt_uuid,1,10) AS '#',
datetime(t.tkt_mtime) AS 'mtime',
t.type,
t.subsystem,
group_concat((tc.icomment || '<br> - '|| tc.login || ' - ' ||
datetime(tc.tkt_mtime)),'<br><br>') as '_comments'
FROM ticket t, ticketchng tc
WHERE t.status<>"Closed" AND t.tkt_id IN (SELECT tc.tkt_id FROM ticketchng )
GROUP BY t.tkt_id
ORDER BY datetime(t.tkt_mtime) DESC
In that same fresh repo, I tried this report and it does some of what
you'd expect.
I'm not expert in SQL, but it looks to me like your WHERE clause is too
complex and FROM not complex enough. Wouldn't a JOIN of some form be
more appropriate?
Perhaps something like this:
SELECT
-- ...
FROM ticket t LEFT JOIN ticketchng tc ON t.tkt_id = tc.tkt_id
WHERE t.status<>"Closed"
GROUP BY t.tkt_id
ORDER BY datetime(t.tkt_mtime) DESC
which seems to do what I think you meant but without the nested SELECT.
It also ignores the t.comment field, which might have content in a repo
created and used with older (how old? I don't recall) versions of fossil.
Some edge cases to consider: a ticket with no comments at all, a ticket
with the old comments field only, a ticket with both the old comments
field and icomments records.
--
Ross Berteig [email protected]
Cheshire Engineering Corp. http://www.CheshireEng.com/
+1 626 303 1602
_______________________________________________
fossil-users mailing list
[email protected]
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
