Trac to iCal

One project I work on uses Trac and has a custom “due date” field (it doesn’t really have milestones – updates are more granular).  While this is useful, one problem is that I don’t check this Trac instance every day, and so sometimes I’ve missed deadlines because I haven’t noticed that they are due.  However, I do check my calendar every day (multiple times a day).  It seems like exporting this “due date” value into my calendar will help with this.

This little script creates a calendar that can be subscribed to.  I run it once a day (due dates don’t change very often), and have iCal set to update once a day, so it should work fine.  If it’s of use to you, use it (no restrictions).  Let me know if there are things that can be improved!  It requires the Python iCalendar module, which I already use for parsing public holiday data.

#! /usr/bin/env python

import sqlite3
import datetime

import icalendar

cal = icalendar.Calendar()
cal.add("prodid", "-//PROJECT NAME Trac Deadlines//trac.tonyandrewmeyer.com//")
cal.add("version", "2.0")
cal.add("method", "publish")
cal.add("x-wr-calname", "PROJECT NAME Ticket Due Dates")
cal.add("x-wr-caldesc", "Due dates for PROJECT NAME tickets")
db = sqlite3.connect("/trac_location/db/trac.db")
c = db.cursor()
c.execute("select t.id, t.owner, t.summary, c.value from ticket t, "
           "ticket_custom c where t.id=c.ticket and t.status!='closed'")
for ticket_id, owner, summary, due_date in c.fetchall():
    if not due_date:
        continue
    due_date = datetime.datetime.strptime(due_date, '%d/%m/%Y')
    due_date = datetime.date(due_date.year, due_date.month, due_date.day)
    event = icalendar.Event()
    event.add("summary", "PROJECT NAME #%s (%s): %s" % (ticket_id, owner, summary))
    event.add("dtstart", due_date)
    event.add("dtend", due_date)
    event.add("dtstamp", datetime.datetime.now())
    event.add("url", "https://trac.tonyandrewmeyer.com/ticket/%s" % ticket_id)
    event["sequence"] = datetime.datetime.now().strftime("%Y%m%d%H%M")
    event["uid"] = "ticket_due_%s@trac.tonyandrewmeyer.com" % ticket_id
    cal.add_component(event)
c.close()
db.close()
open("/var/www/subfolder/project-name-trac-due.ics", "wb").write(cal.as_string())
Advertisements

One response to this post.

  1. I had to make a few changes to get this to work in Google calendar as well as iCal. The revised version is:

    #! /usr/bin/env python
    
    import sqlite3
    import datetime
    
    import icalendar
    
    cal = icalendar.Calendar()
    cal.add("prodid", "-//Project Name Trac//trac.example.com//")
    cal.add("version", "2.0")
    cal.add("method", "PUBLISH")
    cal.add("x-wr-calname", "Project Name Ticket Due Dates")
    cal.add("x-wr-caldesc", "Due dates for Project Name tickets")
    db = sqlite3.connect("/project_path/db/trac.db")
    c = db.cursor()
    c.execute("select t.id, t.owner, t.summary, c.value from ticket t, "
              "ticket_custom c where t.id=c.ticket and t.status!='closed'")
    for ticket_id, owner, summary, due_date in c.fetchall():
        if not due_date:
            continue
        due_date = datetime.datetime.strptime(due_date, '%d/%m/%Y')
        due_date = datetime.date(due_date.year, due_date.month, due_date.day)
        event = icalendar.Event()
        event.add("summary", "Project Name #%s (%s): %s" % (ticket_id, owner, summary))
        event.add("dtstart;value=date", due_date.strftime("%Y%m%d"))
        event.add("dtstamp", datetime.datetime.now())
        event.add("url", "https://trac.example.com/ticket/%s" % ticket_id)
        event["sequence"] = (datetime.date.today() -
                             datetime.date(2011, 1, 14)).days
        event["uid"] = "ticket_due_%s@trac.example.com" % ticket_id
        event["transp"] = "TRANSPARENT"
        cal.add_component(event)
    c.close()
    db.close()
    open("/var/www/site/project-name-due.ics", "wb").write(cal.as_string())
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: