Modify

Opened 7 years ago

Closed 6 years ago

#1541 closed enhancement (wontfix)

Here is some SQL that might make the burndown chart easier.

Reported by: jeremy@… Owned by: daan
Priority: normal Component: ScrumBurndownPlugin
Severity: normal Keywords:
Cc: Trac Release: 0.10

Description

Here is some SQL that would eliminate the need to have a cron job or a ticket hook. It just gets the historical times from the ticket_change table.

Feel free to use it or not.

Output

startofdaynice_timetotalhoursestimatedhoursremaininghours
117858240005/08/20070319319
117866880005/09/20073320317
117875520005/10/200713319306
117884160005/11/200726317291
117910080005/14/200741316275
117918720005/15/200751306255

SQL

drop view change_dates;
create view change_dates as select distinct time / 86400 * 86400 as startofday
from ticket_change where field = 'totalhours' or field = 'estimatedhours'
union select cast(strftime('%s', 'now') / 86400 as int) * 86400 as startofday;

drop view totalhours_log;
create view totalhours_log as
select tc.ticket, tc.time as changetime, tc.oldvalue as value
from ticket_change tc
where tc.field = 'totalhours'
union
select t.id as ticket, 9999999999 as changetime, th.value as value
from ticket t LEFT JOIN ticket_custom th on t.id = th.ticket and name = 'totalhours';

drop view estimatedhours_log;
create view estimatedhours_log as
select tc.ticket, tc.time as changetime, tc.oldvalue as value
from ticket_change tc
where tc.field = 'estimatedhours'
union
select t.id as ticket, 9999999999 as changetime, th.value as value
from ticket t LEFT JOIN ticket_custom th on t.id = th.ticket and name = 'estimatedhours';

select a.startofday, a.nice_time, a.totalhours, b.estimatedhours, b.estimatedhours - a.totalhours as remaininghours
from (select x.startofday as startofday, strftime('%m/%d/%Y', x.startofday, 'unixepoch') as nice_time, sum(th.value) as totalhours from totalhours_log th JOIN (
select t.id as ticket, cd.startofday as startofday, min(tl.changetime) as changetime from ticket t JOIN change_dates cd JOIN totalhours_log tl on (t.id = tl.ticket and cd.startofday < tl.changetime)
where t.milestone = 'v0.1.0'
group by t.id, cd.startofday) x on th.changetime = x.changetime and x.ticket = th.ticket
group by x.startofday) a JOIN (select x.startofday as startofday, strftime('%m/%d/%Y', x.startofday, 'unixepoch') as nice_time, sum(eh.value) as estimatedhours from estimatedhours_log eh JOIN (
select t.id as ticket, cd.startofday as startofday, min(el.changetime) as changetime from ticket t JOIN change_dates cd JOIN estimatedhours_log el on (t.id = el.ticket and cd.startofday < el.changetime)
where t.milestone = 'v0.1.0'
group by t.id, cd.startofday) x on eh.changetime = x.changetime and x.ticket = eh.ticket
group by x.startofday) b on a.startofday = b.startofday;

Attachments (0)

Change History (2)

comment:1 Changed 6 years ago by daan

  • Owner changed from sambloomquist to daan

comment:2 Changed 6 years ago by daan

  • Resolution set to wontfix
  • Status changed from new to closed

Nice idea! But, this seems difficult to maintain for multiple database backends. For larger projects, I also see some performance issues. Plus, when the database schema of Trac changes, all history could be lost.

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.