query for bubbled up finish date - for use in Time Reports

[jc]

• i have a number of tasks related by "blocked by" and "blocking".
• tracjsgantt make a great job of displaying them, linked with a red arrow line.
• most of the tasks contain no date - the first has a "Start Date"
• all these tasks are 100% complete - shown as a greyish bar inside the task bar.

All the above is great and as expected. The tasks are shown from 13 Sept. 2013 onwards. When I come to do time reporting, these tasks are shown in March 2014 - because that's when I got round to setting the status to closed/fixed. (Developer Work Summary). That's because the report query uses:

AND ticket_change.time >= $STARTDATE AND ticket_change.time < $ENDDATE

I looked in ticket_custom and other tables in sqlite etc. to find the date tracjsgantt uses, but couldn't find anything (probably calculated on the fly) (?).

What db field or query can I use to have the tasks appear in September 2013 in the "Developer Work Summary" Time Report?

One idea - a query to set the ticket_change.time to the time tracjsgantt uses/calculates, which I can run now and then before generating Time Reports.

[Chris Nelson]

The background ticket rescheduler computes a schedule based on any explicit due dates, estimated hours, and dependencies (including the implicit dependency resulting from resource contention). You can then query the schedule like:

SELECT 
   p.value AS __color__, 
   t1.ticket, 
   summary, 
   priority, 
   description as _description,
   est.value as est, 
   act.value as act, 
   due, 
   status
FROM (
   (SELECT
       (CASE WHEN t.owner ISNULL 
             THEN ' * Unassigned * ' 
             ELSE t.owner END) AS __group__,
       t.id AS ticket, 
       t.type,
       t.summary, 
       t.priority, 
       (SELECT to_date(to_timestamp((sched.finish/1000000))::text,
                                    'YYYY-MM-DD')) AS due,
       status,
       t.description
    FROM ticket AS t
    INNER JOIN schedule AS sched ON (t.id=sched.ticket)
    WHERE t.owner = '$USER' AND t.milestone != 'SupportIssues')
   UNION
   (SELECT 
       (CASE WHEN t.owner ISNULL 
             THEN ' * Unassigned * ' 
             ELSE t.owner END) AS __group__,
       t.id AS ticket, 
       t.type,
       t.summary, 
       t.priority, 
       NULL AS due,
       status,
       t.description
   FROM ticket AS t
   WHERE t.owner = '$USER' AND t.milestone = 'SupportIssues')
   ) AS t1
INNER JOIN enum AS p ON (p.name=t1.priority AND p.type = 'priority')
LEFT OUTER JOIN ticket_custom AS est ON
     (t1.ticket=est.ticket AND est.name='estimatedhours')
LEFT OUTER JOIN ticket_custom AS act ON
     (t1.ticket=act.ticket AND act.name='totalhours')
WHERE status <> 'closed' AND t1.type <> 'inchpebble' AND t1.type <> 'group'
ORDER BY due NULLS FIRST, p.value

[jc]

Thanks for your reply, I'm getting "no such function" on "to_timestamp" (sqlite3). Would you recommend changing the db to mysql or postgres?

[Chris Nelson]

Replying to jamescook:

Thanks for your reply, I'm getting "no such function" on "to_timestamp" (sqlite3). Would you recommend changing the db to mysql or postgres?

Always. ;-) Well, we found SQLite lacking and now use PostgreSQL. But for your purposes, you just need the SQLite equivalent of to_timestamp(); fixing your report should be a lot easier than switching DBMSs.

​http://www.sqlite.org/lang_datefunc.html suggests that you might just change

       (SELECT to_date(to_timestamp((sched.finish/1000000))::text,
                                    'YYYY-MM-DD')) AS due,

to something like

(SELECT date(sched.finish/1000000))

(but my SQLite is rusty and I don't have a system to try that on.)

[jc]

I changed the db using the tracmigrateplugin - worked fine once I figured out how to set it up. (easyinstall didn't seem to install anything ... so I used the zip). We have postgres installed anyway (for gitlab).

Now to_timestamp works. Running just

    SELECT 
	t.id,
        to_date(to_timestamp((sched.finish/1000000))::text,
                                    'YYYY-MM-DD') AS due,
        status
    FROM ticket AS t
    INNER JOIN schedule AS sched ON (t.id=sched.ticket)
    WHERE t.owner = 'XXX' AND t.milestone != 'SupportIssues'

I'm getting an empty set.

I checked "schedule" (and "schedule_change") I notice these tables are both empty (empty in sqlite too). I wonder if this is a consequence of trac-hacks.org/ticket/11634. I have

tracjsgantt.tracpm.ticketrescheduler = disabled

For the complete query - in the psql cli - I'm getting an empty set (t.owner is set correctly). Your original query from trac itself -> "no results found".

[jc]

FYI - after changing to Postgres I'm no longer getting https://trac-hacks.org/ticket/11761 - see https://trac-hacks.org/ticket/11761#comment:4

[jc]

Looking at (A)tracpm.py and (B)tracjsgantt.py (quick scan) - I can see: in (B) assembly in js:

AddTaskItem(new JSGantt.TaskItem(11, 'some text', start-date, end-date .... ca. line 488:

 # pStart, pEnd
 task += '"%s",' % self.pm.start(ticket).strftime(self.pyDateFormat)
 task += '"%s",' % self.pm.finish(ticket).strftime(self.pyDateFormat)

So, start and finish used in the diagram come from tracpm.py. Looking there (briefly) the code seems to traverse the parent and predecessor (blocked-by) until it gets to a start-date (or if none found, uses "now"). I suppose it then goes back along the chain and adds estimated hours for each task on the path to the current task (I haven't seen this code - assumption).

Reading the start of tracpm.py (ca. line 28):

     28 # TracPM.query() augments TicketQuery so that you can find all the
     29 # descendants of a ticket (root=id) or all the tickets required for a
     30 # specified ticket (goal=id).  After querying, TracPM post-processes
     31 # the query results to augment the tickets in memory with normalized
     32 # meta-data about their relationships.  After that processing, a
     33 # ticket may have the properties listed below.  (Which properties are
     34 # present depends on what is configured in the [TracPM] section of
     35 # trac.ini.)
     36 #
     37 #  parent - accessed with TracPM.parent(t)
     38 #  children - accessed with TracPM.children(t)
     39 #
     40 #  successors - accessed with TracPM.successors(t)
     41 #  predecessors - accessed with TracPM.predecessors(t)
     42 #
     43 #  start - accessed with TracPM.start(t)
     44 #  finish - accessed with TracPM.finish(t)

start and finish are the values used by tracjsgantt.

I need these values to be accessible during "Time Reporting" to get the task at the "correct" date in my reports. i.e. need to be able to do the same kind of augmentation to ticket query used there ...

(taking a break now) ...

[jc]

Looking at the code a little more: tracpm.py: rescheduleTickets:

   2913                             values.append(to_utimestamp(self.pm.finish(t)))
   2914                     cursor.execute('INSERT INTO schedule' + \
   2915                                        ' (ticket, start, finish)' + \
   2916                                        ' VALUES %s' % valuesClause,
   2917                                    values)

It seems this (and other) inserts are not carried out. I'll have to check why this is.

[jc]

There's no explicit db.commit in tracjsgannt py files.

(It could be in a superclass somewhere I suppose - I don't have a debug env. here at the moment ...)

[jc]

To recap: As mentioned in https://trac-hacks.org/ticket/11761#comment:4 the schedule and schedule_change tables are empty.

And they stay empty, even when a ticket is rescheduled (new Start Date etc.)

From "debugging" the code at a basic level (comments!) during a "Start Date" change I can see that INSERTs to schedule (and schedule_change) are "queued" but seem not to be committed. There are no errors in trac.log (or postgresql.log).

In trac.ini

[component] tracjsgantt.tracpm.ticketrescheduler = enabled

(but was disabled for a long time before (sqlite problem)

I am NOT using 0.11 but the setup as reported: ​http://trac-hacks.org/ticket/11634#comment:5

[jc]

I increased the log-level for the postgres log. This confirms the INSERT is queued, but ROLLedBACK immediately:

2014-05-25 16:17:57 CEST LOG:  statement: SELECT name, due, completed FROM milestone WHERE name IN ('dummymilestone','other tasks to end March 2014','future','spring feb 2013','before each release','facebook V1','mike future','start sept 2012','irenas aufgabe','owncloud','trac2014','End April 2014','End May 2014')
2014-05-25 16:17:57 CEST LOG:  statement: ROLLBACK
2014-05-25 16:17:58 CEST LOG:  statement: BEGIN
2014-05-25 16:17:58 CEST LOG:  statement: SELECT ticket, start, finish FROM schedule WHERE ticket IN (280,445,447,278,409,450,2,9,14,18,23,24,52,81,86,88,99,102,104,107,127,143,144,150,166,170,171,174,175,187,189,190,197,209,224,225,228,231,254,277,279,285,286,287,376,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,401,402,403,407,410,411,413,415,416,417,418,421,422,425,426,427,429,435,436,437,440,441,442,443,446,448,449,455,456,457,461,462,463,464,465,466,467,468,469,470,471,474,475,477,478,479,483,484,486,487,489,495,496,497,499,501,502,503,504,5,13,19,165,207,284,400,408,412,439)
2014-05-25 16:17:58 CEST LOG:  statement: INSERT INTO schedule (ticket, start, finish) VALUES (280,1393628400000000,1394514000000000),(445,1395979200000000,1395982800000000),(447,1401055200000000,1401058800000000),(278,1406673000000000,1407796200000000),(409,1401058800000000,1401073200000000),(450,1401073200000000,1401152400000000),(2,1401152400000000,1344744000000000),(9,1401152400000000,1344744000000000),(14,1401152400000000,1344744000000000),(18,1401152400000000,1401231600000000),(23,1401231600000000,1401246000000000),(24,1401246000000000,1344744000000000),(52,1401246000000000,1401325200000000),(81,1401325200000000,1344744000000000),(86,1401325200000000,1344744000000000),(88,1401325200000000,1344744000000000),(99,1401325200000000,1344744000000000),(102,1401325200000000,1344744000000000),(104,1401325200000000,1344744000000000),(107,1401325200000000,1344744000000000),(127,1401325200000000,1347595200000000),(143,1401325200000000,1347422400000000),(144,1401325200000000,1347422400000000),(150,1401325200000000,1401404400000000),(166,1401404400000000,1348113600000000),(170,1401404400000000,1348113600000000),(171,1401404400000000,1348113600000000),(174,1401404400000000,1348200000000000),(175,1401404400000000,1348200000000000),(187,1401404400000000,1353906000000000),(189,1401404400000000,1353733200000000),(190,1401404400000000,1353992400000000),(197,1401404400000000,1361941200000000),(209,1401404400000000,1358744400000000),(224,1401404400000000,1360213200000000),(225,1401404400000000,1361941200000000),(228,1401404400000000,1360213200000000),(231,1401404400000000,1360213200000000),(254,1401404400000000,1401418800000000),(277,1400796000000000,1400810400000000),(279,1401418800000000,1401922800000000),(285,1401055200000000,1401069600000000),(286,1401055200000000,1401156000000000),(287,1401922800000000,1401937200000000),(376,1401937200000000,1402012800000000),(383,1402012800000000,1402027200000000),(384,1402264800000000,1402279200000000),(385,1402279200000000,1402358400000000),(386,1402358400000000,1402372800000000),(387,1402437600000000,1402452000000000),(388,1402452000000000,1402531200000000),(389,1402531200000000,1402632000000000),(390,1402869600000000,1402884000000000),(391,1402884000000000,1402963200000000),(392,1402963200000000,1402977600000000),(393,1403042400000000,1403056800000000),(394,1403056800000000,1403136000000000),(395,1403136000000000,1403150400000000),(396,1403215200000000,1403229600000000),(397,1403229600000000,1403236800000000),(401,1403474400000000,1403488800000000),(402,1403488800000000,1403568000000000),(403,1403568000000000,1403582400000000),(407,1403647200000000,1403658000000000),(410,1403658000000000,1403665200000000),(411,1403665200000000,1403744400000000),(413,1403744400000000,1403823600000000),(415,1403823600000000,1403838000000000),(416,1403838000000000,1404093600000000),(417,1404093600000000,1404172800000000),(418,1404172800000000,1404187200000000),(421,1404252000000000,1404266400000000),(422,1404266400000000,1404345600000000),(425,1404345600000000,1404360000000000),(426,1404424800000000,1404439200000000),(427,1404439200000000,1404691200000000),(429,1406673000000000,1406687400000000),(435,1404691200000000,1404705600000000),(436,1404770400000000,1404784800000000),(437,1404784800000000,1404864000000000),(440,1404864000000000,1404878400000000),(441,1404943200000000,1404957600000000),(442,1404957600000000,1405036800000000),(443,1405036800000000,1405051200000000),(446,1405288800000000,1405303200000000),(448,1405303200000000,1405305000000000),(449,1405305000000000,1405384200000000),(455,1405384200000000,1405463400000000),(456,1405463400000000,1405477800000000),(457,1405477800000000,1405557000000000),(461,1401418800000000,1401670800000000),(462,1405557000000000,1405636200000000),(463,1405636200000000,1405650600000000),(464,1401670800000000,1401750000000000),(465,1401750000000000,1401764400000000),(466,1405650600000000,1405902600000000),(467,1405902600000000,1405981800000000),(468,1405981800000000,1405996200000000),(469,1405996200000000,1406075400000000),(470,1406075400000000,1406154600000000),(471,1406154600000000,1406169000000000),(474,1406169000000000,1397188800000000),(475,1406169000000000,1406248200000000),(477,1406248200000000,1406500200000000),(478,1406500200000000,1400558400000000),(479,1406500200000000,1400385600000000),(483,1406500200000000,1400212800000000),(484,1406500200000000,1406503800000000),(486,1406503800000000,1400126400000000),(487,1406503800000000,1400040000000000),(489,1406503800000000,1399089600000000),(495,1406503800000000,1406511000000000),(496,1406511000000000,1399435200000000),(497,1406511000000000,1399348800000000),(499,1406511000000000,1406590200000000),(501,1406590200000000,1400904000000000),(502,1406590200000000,1406673000000000),(503,1407796200000000,1407810600000000),(504,1407810600000000,1407889800000000),(5,1407889800000000,1344744000000000),(13,1407889800000000,1344744000000000),(19,1407889800000000,1344744000000000),(165,1407889800000000,1348113600000000),(207,1407889800000000,1358744400000000),(284,1408314600000000,1408660200000000),(400,1407889800000000,1407969000000000),(408,1407969000000000,1407983400000000),(412,1407983400000000,1408062600000000),(439,1408062600000000,1408314600000000)
2014-05-25 16:17:58 CEST LOG:  statement: INSERT INTO schedule_change (ticket, time, newstart, newfinish) VALUES (280,1401027477585149,1393628400000000,1394514000000000),(445,1401027477585149,1395979200000000,1395982800000000),(447,1401027477585149,1401055200000000,1401058800000000),(278,1401027477585149,1406673000000000,1407796200000000),(409,1401027477585149,1401058800000000,1401073200000000),(450,1401027477585149,1401073200000000,1401152400000000),(2,1401027477585149,1401152400000000,1344744000000000),(9,1401027477585149,1401152400000000,1344744000000000),(14,1401027477585149,1401152400000000,1344744000000000),(18,1401027477585149,1401152400000000,1401231600000000),(23,1401027477585149,1401231600000000,1401246000000000),(24,1401027477585149,1401246000000000,1344744000000000),(52,1401027477585149,1401246000000000,1401325200000000),(81,1401027477585149,1401325200000000,1344744000000000),(86,1401027477585149,1401325200000000,1344744000000000),(88,1401027477585149,1401325200000000,1344744000000000),(99,1401027477585149,1401325200000000,1344744000000000),(102,1401027477585149,1401325200000000,1344744000000000),(104,1401027477585149,1401325200000000,1344744000000000),(107,1401027477585149,1401325200000000,1344744000000000),(127,1401027477585149,1401325200000000,1347595200000000),(143,1401027477585149,1401325200000000,1347422400000000),(144,1401027477585149,1401325200000000,1347422400000000),(150,1401027477585149,1401325200000000,1401404400000000),(166,1401027477585149,1401404400000000,1348113600000000),(170,1401027477585149,1401404400000000,1348113600000000),(171,1401027477585149,1401404400000000,1348113600000000),(174,1401027477585149,1401404400000000,1348200000000000),(175,1401027477585149,1401404400000000,1348200000000000),(187,1401027477585149,1401404400000000,1353906000000000),(189,1401027477585149,1401404400000000,1353733200000000),(190,1401027477585149,1401404400000000,1353992400000000),(197,1401027477585149,1401404400000000,1361941200000000),(209,1401027477585149,1401404400000000,1358744400000000),(224,1401027477585149,1401404400000000,1360213200000000),(225,1401027477585149,1401404400000000,1361941200000000),(228,1401027477585149,1401404400000000,1360213200000000),(231,1401027477585149,1401404400000000,1360213200000000),(254,1401027477585149,1401404400000000,1401418800000000),(277,1401027477585149,1400796000000000,1400810400000000),(279,1401027477585149,1401418800000000,1401922800000000),(285,1401027477585149,1401055200000000,1401069600000000),(286,1401027477585149,1401055200000000,1401156000000000),(287,1401027477585149,1401922800000000,1401937200000000),(376,1401027477585149,1401937200000000,1402012800000000),(383,1401027477585149,1402012800000000,1402027200000000),(384,1401027477585149,1402264800000000,1402279200000000),(385,1401027477585149,1402279200000000,1402358400000000),(386,1401027477585149,1402358400000000,1402372800000000),(387,1401027477585149,1402437600000000,1402452000000000),(388,1401027477585149,1402452000000000,1402531200000000),(389,1401027477585149,1402531200000000,1402632000000000),(390,1401027477585149,1402869600000000,1402884000000000),(391,1401027477585149,1402884000000000,1402963200000000),(392,1401027477585149,1402963200000000,1402977600000000),(393,1401027477585149,1403042400000000,1403056800000000),(394,1401027477585149,1403056800000000,1403136000000000),(395,1401027477585149,1403136000000000,1403150400000000),(396,1401027477585149,1403215200000000,1403229600000000),(397,1401027477585149,1403229600000000,1403236800000000),(401,1401027477585149,1403474400000000,1403488800000000),(402,1401027477585149,1403488800000000,1403568000000000),(403,1401027477585149,1403568000000000,1403582400000000),(407,1401027477585149,1403647200000000,1403658000000000),(410,1401027477585149,1403658000000000,1403665200000000),(411,1401027477585149,1403665200000000,1403744400000000),(413,1401027477585149,1403744400000000,1403823600000000),(415,1401027477585149,1403823600000000,1403838000000000),(416,1401027477585149,1403838000000000,1404093600000000),(417,1401027477585149,1404093600000000,1404172800000000),(418,1401027477585149,1404172800000000,1404187200000000),(421,1401027477585149,1404252000000000,1404266400000000),(422,1401027477585149,1404266400000000,1404345600000000),(425,1401027477585149,1404345600000000,1404360000000000),(426,1401027477585149,1404424800000000,1404439200000000),(427,1401027477585149,1404439200000000,1404691200000000),(429,1401027477585149,1406673000000000,1406687400000000),(435,1401027477585149,1404691200000000,1404705600000000),(436,1401027477585149,1404770400000000,1404784800000000),(437,1401027477585149,1404784800000000,1404864000000000),(440,1401027477585149,1404864000000000,1404878400000000),(441,1401027477585149,1404943200000000,1404957600000000),(442,1401027477585149,1404957600000000,1405036800000000),(443,1401027477585149,1405036800000000,1405051200000000),(446,1401027477585149,1405288800000000,1405303200000000),(448,1401027477585149,1405303200000000,1405305000000000),(449,1401027477585149,1405305000000000,1405384200000000),(455,1401027477585149,1405384200000000,1405463400000000),(456,1401027477585149,1405463400000000,1405477800000000),(457,1401027477585149,1405477800000000,1405557000000000),(461,1401027477585149,1401418800000000,1401670800000000),(462,1401027477585149,1405557000000000,1405636200000000),(463,1401027477585149,1405636200000000,1405650600000000),(464,1401027477585149,1401670800000000,1401750000000000),(465,1401027477585149,1401750000000000,1401764400000000),(466,1401027477585149,1405650600000000,1405902600000000),(467,1401027477585149,1405902600000000,1405981800000000),(468,1401027477585149,1405981800000000,1405996200000000),(469,1401027477585149,1405996200000000,1406075400000000),(470,1401027477585149,1406075400000000,1406154600000000),(471,1401027477585149,1406154600000000,1406169000000000),(474,1401027477585149,1406169000000000,1397188800000000),(475,1401027477585149,1406169000000000,1406248200000000),(477,1401027477585149,1406248200000000,1406500200000000),(478,1401027477585149,1406500200000000,1400558400000000),(479,1401027477585149,1406500200000000,1400385600000000),(483,1401027477585149,1406500200000000,1400212800000000),(484,1401027477585149,1406500200000000,1406503800000000),(486,1401027477585149,1406503800000000,1400126400000000),(487,1401027477585149,1406503800000000,1400040000000000),(489,1401027477585149,1406503800000000,1399089600000000),(495,1401027477585149,1406503800000000,1406511000000000),(496,1401027477585149,1406511000000000,1399435200000000),(497,1401027477585149,1406511000000000,1399348800000000),(499,1401027477585149,1406511000000000,1406590200000000),(501,1401027477585149,1406590200000000,1400904000000000),(502,1401027477585149,1406590200000000,1406673000000000),(503,1401027477585149,1407796200000000,1407810600000000),(504,1401027477585149,1407810600000000,1407889800000000),(5,1401027477585149,1407889800000000,1344744000000000),(13,1401027477585149,1407889800000000,1344744000000000),(19,1401027477585149,1407889800000000,1344744000000000),(165,1401027477585149,1407889800000000,1348113600000000),(207,1401027477585149,1407889800000000,1358744400000000),(284,1401027477585149,1408314600000000,1408660200000000),(400,1401027477585149,1407889800000000,1407969000000000),(408,1401027477585149,1407969000000000,1407983400000000),(412,1401027477585149,1407983400000000,1408062600000000),(439,1401027477585149,1408062600000000,1408314600000000)
2014-05-25 16:17:58 CEST LOG:  statement: ROLLBACK
2014-05-25 16:17:58 CEST LOG:  statement: BEGIN

[jc]

Well, after researching and debugging even the db/api - i just ended up patching the code in tracpm.py in def rescheduleTickets(self, ticket, old_values) (at the very bottom):

        profile.append([ 'inserting', len(toInsert), end - start ])

+       db.commit()
        for step in profile:
            self.env.log.info('TracPM:%s %s tickets took %s' %
                              (step[0], step[1], step[2]))

This following guidance in ​http://trac.edgewall.org/wiki/TracDev/DatabaseApi, a db.commit() has to be explicitly added with cnx connections. They also state: The use of env.get_db_cnx() is now strongly discouraged.

Finally I have some entries in schedule and schedule change. I saved the db before doing this, and I'll check for side effects later on. Now at least I can check your original query idea.

[jc]

def upgrade_environment(self, db) would need the same patch - also contains INSERT/UPDATE.

[Jun Omae]

def upgrade_environment(self, db) would need the same patch - also contains INSERT/UPDATE.

I think upgrade_environment doesn't need to call db.commit(). The db.commit() is called from Environment.upgrade().

• ​trac:source:tags/trac-0.11.7/trac/env.py@:465#L442
• ​trac:source:tags/trac-0.12.5/trac/env.py@:527#L506
• ​trac:source:tags/trac-1.0.1/trac/env.py@:712#L688

[jc]

Thanks for that, I'm no plugins expert

[jc]

show effect of adding db.commit to rescheduling in tracpm.py

[jc]

The above attachment shows what happens when db.commit is added as described.

#502 has a start date (1.4.2014) and blocks #503 (no dates) and #504. This can be seen correctly in the first screenshot.

With db.commit() activated, I change the start-date of #502, this results in a schedule.finish date of 29.07.2014. Redraw of the milestone shown in 2nd screenshot. (Using option.schedule = asap)

If I drop the contents of schedule and schedule_change a redraw shows the expected results.

Very strange.

FYI:

[TracPM]
date_format = %Y-%m-%d
default_estimate = 4.0
estimate_pad = 0.0
fields.estimate = estimatedhours
fields.finish = userfinish
fields.parent = parents
fields.percent = complete
fields.pred = blockedby
fields.start = userstart

[ticket-custom]
billable = checkbox
billable.label = Billable?
billable.order = 3
billable.value = 1
blockedby = text
blockedby.label = Blocked By
blocking = text
blocking.label = Blocking
complete = select
complete.label = Completed [%]
complete.options = |0|5|10|15|20|25|30|35|40|45|50|55|60|65|70|75|80|85|90|95|100
complete.order = 3
estimatedhours = text
estimatedhours.label = Estimated Number of Hours
estimatedhours.order = 1
estimatedhours.value = 0
hours = text
hours.label = Add Hours to Ticket
hours.order = 2
hours.value = 0
internal = checkbox
internal.label = Internal?
internal.order = 5
internal.value = 0
parents = text
parents.label = Parent Tickets
totalhours = text
totalhours.label = Total Hours
totalhours.order = 4
totalhours.value = 0
userfinish = text
userfinish.date = true
userfinish.date_empty = true
userfinish.format = ymd
userfinish.label = Due Date
userfinish.order = 17
userfinish.separator = "-"
userstart = text
userstart.date = true
userstart.date_empty = true
userstart.label = Start Date
userstart.order = 2

[jc]

Installed latest version of tracjsgantt ...868 (using easy_install).

Tried just redrawing the DummyMilestone: result - basic chart now drawn differently. #502 shown AFTER #503,#504, and #504 now has a red line to #278.

See the attachment pdf below:

[jc]

with latest tracjsgantt original diagram drawn differently before any date changes

[jc]

Here are some table contents for the tickets concerned - as an attached pdf (below). There seems to be no clear reason why #504 is anchored to #278 (anchor to the ticket and in particular its date).

I have to say, DummyMilestone is for testing features only, and has grown over time. The data taken together is not very sensible.

[jc]

So, there are 2 issues here I've going to split into separate tickets.

1. schedule and schedule_change not being written to -> https://trac-hacks.org/ticket/11773#ticket
2. diagrams drawn differently (broken) between r13705 and r13868 -> https://trac-hacks.org/ticket/11774

I'll leave this ticket to its original purpose, finding a good query for the start/end dates of blocked by/blocking tasks to be used in "Time Reports"

[Chris Nelson]

Replying to jamescook:

I changed the db using the tracmigrateplugin - worked fine once I figured out how to set it up. (easyinstall didn't seem to install anything ... so I used the zip). We have postgres installed anyway (for gitlab).

Now to_timestamp works. Running just ... I'm getting an empty set.

I checked "schedule" (and "schedule_change") I notice these tables are both empty (empty in sqlite too). I wonder if this is a consequence of trac-hacks.org/ticket/11634. I have

tracjsgantt.tracpm.ticketrescheduler = disabled

For the complete query - in the psql cli - I'm getting an empty set (t.owner is set correctly). Your original query from trac itself -> "no results found".

Yes, the schedule and schedule_change tables are populated by the background ticket rescheduler. Some explanation is likely in order and my documentation likely hasn't kept up with my implementation:

• In the beginning there was jsGantt which to a bunch of task descriptions and presented the tasks in complex set of HTML <div> elements.
• I created tracJsGantt which took arguments much like a Trac query and then used jsGantt to present them.
• I found I wanted more robust (e.g., resource-leveled) scheduling *and* I wanted to save that schedule so I could query it, etc. Much as you seem to want to do.

It would be silly to schedule and save every time a page with a Gantt on it was invoked. What I did was create TracPM which handles all the funky scheduling (including being called from the Gantt) and, when the rescheduler is enabled, stores the schedule in a private table.

With the background rescheduler enabled, you can actually do something like [[TracJGSGanttChart(scheduled=1)]] to get data out of the database and by-pass the extra scheduling the chart would usually do for itself.

[Chris Nelson]

Replying to jamescook:

To recap: As mentioned in https://trac-hacks.org/ticket/11761#comment:4 the schedule and schedule_change tables are empty.

And they stay empty, even when a ticket is rescheduled (new Start Date etc.)

From "debugging" the code at a basic level (comments!) during a "Start Date" change I can see that INSERTs to schedule (and schedule_change) are "queued" but seem not to be committed. There are no errors in trac.log (or postgresql.log).

If you set

[TracPM]
logScheduling=1

you'll get a lot of detail from the background rescheduler. Grep for "sch>" in your log.

In trac.ini

[component] tracjsgantt.tracpm.ticketrescheduler = enabled

(but was disabled for a long time before (sqlite problem)

I am NOT using 0.11 but the setup as reported: ​http://trac-hacks.org/ticket/11634#comment:5

We are moving to 1.0.1 but have not yet tested extensively. If the semantics of ticket change listeners or the DB API have changed, perhaps the db.commit() you added is needed in 1.0.1.

[jc]

Chris, thanks for your comments. Great to have some background - I think if you added them in some way to the current doc. it'd be very helpful. Is there some general info on the listeners in trac somewhere? (preferably sequence diagrams, better than text for me). (btw - I forgot it was long WE in UK - good you made it laptop free!)

And you're right, I'm trying to query the dates tracjsganntt itself calculates to anchor tasks without explicit Start Dates/Due dates etc. In particular to make more accurate "Time Reports".

tracjsgantt is the main reason we still use trac! We tried redmine and the Gantt there - but it doesn't have the same dynamic functionality - in particular displaying "blocked by" tickets in a cascade behind the original blocking ticket - from a set Start Date or otherwise from 'Now'. In other words we use gantt not just to retrospectively document old tasks (as in redmine - all dates explicitly entered for each ticket) but to actively plan new and unfinished tasks too - to see e.g. the consequence of changing one ticket propagating down the line ... etc.

As you may have noticed from https://trac-hacks.org/ticket/11761#comment:18 I have split this ticket into 2 further tickets (! sorry about that).

1. schedule and schedule_change not being written to -> ​https://trac-hacks.org/ticket/11773#ticket. This in turn is about 2 things:

1: db.commit maybe missing, but 2: placing a db.commit into the code as I did results in the tasks being moved in the diagram(!)

2. diagrams drawn differently (broken) between r13705 and r13868 -> ​https://trac-hacks.org/ticket/11774 Here it seems the very dynamic propagation of "blocked by" tickets we rely upon has been removed - is this right?

I'd appreciate your feedback on particularly on question 2. -> ​https://trac-hacks.org/ticket/11774. (i.e. should we move back to r13705 if we need propagation of "blocked-by" tasks).

FYI We are now using postgres and tracjsganntt - r13868. Trac Version 1.0.1 py2.7

[Chris Nelson]

Replying to jamescook:

Chris, thanks for your comments. Great to have some background - I think if you added them in some way to the current doc. it'd be very helpful.

In my abundant spare time. ;-) You're the first person I've known to make use of the schedule table. But, yes, I need to catch up the documentation.

Is there some general info on the listeners in trac somewhere? (preferably sequence diagrams, better than text for me).

​ComponentArchitecture is the best I've found but is somewhat lacking.

(btw - I forgot it was long WE in UK - good you made it laptop free!)

:-)

And you're right, I'm trying to query the dates tracjsganntt itself calculates to anchor tasks without explicit Start Dates/Due dates etc. In particular to make more accurate "Time Reports".

You'll want something like [[TracJSGanttChart(scheduled=1,schedule=none)]] to just show what the background rescheduler created.

tracjsgantt is the main reason we still use trac!

What a great compliment! Thank you. :-)

... As you may have noticed from https://trac-hacks.org/ticket/11761#comment:18 I have split this ticket into 2 further tickets (! sorry about that).

No, that makes sense to me, if they really are distinct issues.

1. schedule and schedule_change not being written to -> ​https://trac-hacks.org/ticket/11773#ticket. This in turn is about 2 things:

1: db.commit maybe missing, but 2: placing a db.commit into the code as I did results in the tasks being moved in the diagram(!)

As I said, we're still experimenting with Trac 1.0 and I know there have been some DB API changes but from what I saw, the commit should be optional and I suspect the rollback is occurring due to an error.

2. diagrams drawn differently (broken) between r13705 and r13868 -> ​https://trac-hacks.org/ticket/11774

That's interesting. Some of the scheduling logic is so complex that it can be unpredictable and I've often made a change that had unintended consequences. Sometimes I've been lucky enough to find that while I had gotten used to the old output, the new result is more correct.

Here it seems the very dynamic propagation of "blocked by" tickets we rely upon has been removed - is this right?

No, that seems wrong.

I'd appreciate your feedback on particularly on question 2. -> ​https://trac-hacks.org/ticket/11774. (i.e. should we move back to r13705 if we need propagation of "blocked-by" tasks).

I'll take a look at #11774.

FYI We are now using postgres and tracjsganntt - r13868. Trac Version 1.0.1 py2.7

[Chris Nelson]

Replying to jamescook:

...

1: db.commit maybe missing, but

...

I wonder if this is related to my used of get_db_cnx() (see ​http://trac.edgewall.org/wiki/TracDev/DatabaseApi, it's deprecated now). I'm having trouble with that in another plugin. If I fix this there, I'll copy the change to TracPM.

[Chris Nelson]

Replying to ChrisNelson:

... Yes, the schedule and schedule_change tables are populated by the background ticket rescheduler. Some explanation is likely in order and my documentation likely hasn't kept up with my implementation: ...

I updated the documentation.