I selected activeCollab with the hope that the 1.0 release would be sufficient to manage the development of a series of software projects. Part of the requirement was bug tracking: we used tasks and task lists to track bugs with the hope of migrating them to the 1.0 version of activeCollab. Key to enabling activeCollab for bug tracking was the ability to attach comments to tasks. It was not an ideal solution but believed 1.0 would be worth the wait. Unfortunately, the wait was too long and we decided to transition totrac.

I wasn’t able to find any utility to move the tasks to trac so I decided to write my own, mostly as a collection of SQL code and offer them for those in a similar predicament.
I am using a trac installation with SQLite and activeCollab running from MySQL. I also have added two custom fields: Vertical and Application. Ad I’m not bringing any attached files.

Also worthwhile to note is I used mysqldump to move the data from MySQL to SQL Server so I could do development there. Some changes to the code below may be necessary to get this to run on MySQL.

The migration consists of generating a bunch of insert statements to populate the SQLite database.

There are a few support routines I created to help the migration.

SQLite3_DateToJulian converts a datetime value into a Julian int suitable for a SQLite int column.

create function dbo.SQLite3_DateToJulian (
@Date datetime -- Date to convert to Julian
) returns int -- Julian date. # days since 1900-01-01
as begin
declare @val int
if (@Date is null)
set @val = 0
else
set @val = datediff(second, '1970-01-01', @Date)
return @val
end

The next function converts an activeCollab user id into a username for trac. We are using SSPI to map our network logins to a trac user automatically. Our user names are the first character of our first name followed by the last name. Yoy may have to update this as necessary.

create function dbo.acuser (@id int)
returns varchar(50)
as
begin
declare @val varchar(50)
select @val = upper(left(username, 2)) + lower(right(username, len(username)-2)) from ac_users where id = @id

return @val
end

The last function formats a string value into a string literal.

create function dbo.strout (@s varchar(max))
returns varchar(max)
as
begin
declare @res varchar(max)
if (@s is null) set @s = ''
set @s = replace(@s, '''', '''''')
set @s = replace(@s, '’', '''''')
set @s = replace(@s, '\r', ' ') -- may want to convert this CR/NL translation to something else
set @s = replace(@s, '\n', '')
return '''' + @s + ''''
end

There are four trac tables I populated to hold the tickets, comments and custom fields. I always start from scratch so it is necessary to delete the records in each table first.

select 'delete from milestone;'
select 'delete from ticket;'
select 'delete from ticket_custom;'
select 'delete from ticket_change;'

I translate the activeCollab milestones to trac milestones but use the trac milestone name as the Project Name followed by the Milestone name (as my milestones were not unique across projects).

select 'insert into milestone values(''' + p.name + ' ' + m.name + ''', ' + convert(varchar, dbo.SQLite3_DateToJulian(m.due_date)) + ', ' + convert(varchar, dbo.SQLite3_DateToJulian(m.completed_on)) + ', ''' + convert(varchar(max), m.description) + ''');'
from ac_project_milestones m
join ac_projects p on p.id = m.project_id

Tickets are tasks attached to task lists. For us, the task list describes a sub component and we pump this into the keywords field. We could add a third UDF but decided against it.

select
'insert into ticket values (' +
convert(varchar, t.id) + ', ' +
dbo.strout('defect') + ', ' + -- defects by default, could add more logic
convert(varchar, dbo.SQLite3_DateToJulian(t.created_on)) + ', ' +
convert(varchar, dbo.SQLite3_DateToJulian(t.updated_on)) + ', ' +
dbo.strout('Templates') + ', ' + -- Component can (and should) be genericized
dbo.strout('') + ', ' +
dbo.strout('major') + ', ' + -- major by default
dbo.strout(dbo.acuser(t.assigned_to_user_id)) + ', ' +
dbo.strout(dbo.acuser(t.created_by_id)) + ', ' +
dbo.strout('') + ', ' +
dbo.strout('0.9') + ', ' + -- the version
'''' + isnull(p.name, '') + isnull(' ' + m.name, '') + ''', ' + -- milestone names are project names followed by milestones
dbo.strout( -- set the status
case
when t.completed_on is not null then 'closed'
when t.assigned_to_user_id > 0 then 'assigned'
else 'new'
end) + ', ' +
dbo.strout(case when t.completed_on is not null then 'fixed' else '' end) +', ' +
dbo.strout(t.text) + ', ' + -- summary and description are one in the same
dbo.strout(t.text) + ', ' +
dbo.strout(l.name) + ');'
from ac_project_tasks t
join ac_project_task_lists l on l.id = t.task_list_id
left outer join ac_project_milestones m on m.id = l.milestone_id
left outer join ac_projects p on p.id = l.project_id

Adding a custom field is easy; I do a mapping from the project name to determine the vertical and application.
select
'insert into ticket_custom values (' +
convert(varchar, t.id) + ', ' +
dbo.strout('vertical') + ', ' +
dbo.strout(
case p.name
when 'Project 1' then 'Vertical 1'
when 'Project 2' then 'Vertical 2'
when 'Project 3' then 'Vertical 1'
end
) + ');',
'insert into ticket_custom values (' +
convert(varchar, t.id) + ', ' +
dbo.strout('application') + ', ' +
dbo.strout(
case p.name
when 'Project 1' then 'Application 1'
when 'Project 2' then 'Application 2'
when 'Project 3' then 'Application 1'
end
) + ');'
from ac_project_tasks t
join ac_project_task_lists l on l.id = t.task_list_id
left outer join ac_project_milestones m on m.id = l.milestone_id
left outer join ac_projects p on p.id = l.project_id

And finally moving the comments over is straightforward.
select
'insert into ticket_change values (' +
convert(varchar, c.rel_object_id) + ', ' +
convert(varchar, dbo.SQLite3_DateToJulian(c.created_on)) + ', ' +
dbo.strout(dbo.acuser(c.created_by_id)) + ', ' +
dbo.strout('comment') + ', ' +
dbo.strout('1') + ', ' + -- this should really sequence for each comment for each ticket
dbo.strout(c.text) + ');'
from ac_comments c
where rel_object_manager = 'ProjectTasks' -- not concerted about messages, only tasks
order by rel_object_id, created_on

There is a lot of cheating going on here but it is mostly for historical purposes. Just take the output of each section of routines and run them against the trac database.

Good luck!