Sunday, April 1, 2012

trac: bulk update for ticket custom field

We wanted to update a custom field for group of tickets in trac, we have version 0.11.6, tried to look around, and found that there is no support for bulk change from the UI

I thought of doing this on the DB directly, I found that our trac  is using SQLite from the configuration file,

database = sqlite:db/trac.db

I downloaded SQLiteStudio, a tool for SQLite from http://sqlitestudio.one.pl/

opened the tool, created new Database from the file stated above in the configuration file, usually it will be in trac-projects folder, inside project folder\db

started building my query, it ended up like below

update ticket_custom
set value = 'Parked'
where name = 'internal_status' and ticket in (select t.id from ticket t
    INNER JOIN ticket_custom tc on tc.ticket = t.id
where version in ('Release1','Release2')
     and tc.name = 'internal_status'
     and tc.value <> 'Closed' and tc.value <> 'Parked')

executing the above query, I was able to bulk update for my custom field for a group of tickets