Modify

Opened 8 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 owner will remain daan.
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.