Migrating Trac to Lighthouse

We migrated our ticketing system from Trac to Lighthouse recently.

Trac uses SQLite database and exporting data was not an issue. Lighthouse does not have a bulk import capability but it does have a comprehensive API. To migrate data all we had to do is write a simple script.

# Migrate records from a Trac database to LighthouseApp
require 'rubygems'
require 'sqlite3'
require 'lib/lighthouse.rb' # download from https://github.com/Caged/lighthouse-api

# Connect to Lighthouse API
Lighthouse.account = 'YOUR LIGHTHOUSE ACCOUNT'
Lighthouse.token = 'YOUR LIGHTHOUSE API TOKEN'
project = Lighthouse::Project.find(:all).first
puts "MIGRATING TICKETS TO PROJECT: #{project.name}"

# Connect to database
db = SQLite3::Database.new("trac.db")

# Retrieve open trac ticket records
columns, *rows = db.execute2("select * from ticket t where t.status in ('new', 'assigned') order by t.id")

puts "TICKET TABLE COLUMNS"
  (0..columns.length-1).each do |i|
  puts "#{i}:#{columns[i]}"
end

# Process ticket records
rows.each do |row|
  puts row[0] # trac ticket ID
  # Create a new Lighthouse ticket
  ticket = Lighthouse::Ticket.new(:project_id => project.id)
  ticket.title = row[14] # summary column
  ticket.body = row[15] # description column
  # Additional data mapings go here

  # Get trac ticket changes and append to the Lighthouse ticket
  col, *updates = db.execute2("select newvalue from ticket_change where ticket=? and field = 'comment'", row[0])
  updates.each do |update|
    upd = update[0]
    ticket.body << "\n" + upd if !upd.empty?
  end

  # Display progress
  puts ticket.title
  puts ticket.body
  ticket.save
end

The source code is also available at https://gist.github.com/1523987.

software development

lighthouseapp, product development, programming, ruby on rails, software development, trac

December 27, 2011

Leave a comment