PHP and SQL scripts to convert a trac ticket database w/SVN linked entries to JIRA consumable import

tracsvn2JiraFisheyeLinkConverter.php

<?php
// tracsvn2JiraFisheyeLinkConverter.php

// 	(c) 2014 J Terwilliger

// free to reuse and modify

$_DEBUG = true;

class TracDB extends SQLite3 {
	function __construct() {
		$this->open('db/trac.db');
	}
}

function findMatch($row) {

	// $checks = array(	
	// 				'raw' => '/\[(\d+)\]/', // use caution
	//	This is the legacy format, not sure which version it switched up to the 
	// 				'repo1' => '/\[(\d+)\/Repo1.*\]/',
	// 				'repo2' => '/\[(\d+)\/Repo2.*\]/'
	// 			);
	$checks = array(	
	//				'raw' => '/\[(\d+)\]/',  // might be too risky for your install, you've been warned
	//	This is our current 0.12.2 changeset format for multiple repositories
					'repo1' => '/\[changeset:\"(\d+)\/Repo1.*\]/',
					'repo2' => '/\[changeset:\"(\d+)\/Repo2.*\]/'
				);				
	foreach ($checks as $checkTitle=>$check) {
		// adjust $row id when searching different table field.
		$res = preg_match($check, $row['newvalue'], $match);
		
		if ($res) {
			// these infiltrators came from cut and paste MySQL errors in ticket comments 
			// and descriptions, obviously use discretion or disable if not an issue
			// these are only useful if the 'raw' check is enabled
			// if (in_array($match[1],array("23000","42000",'0','4096'))) return false;
			echo "Found $checkTitle changeset: ".$match[1]."\n";
			$match[2] = $checkTitle;
			return $match;
		}
	}
	
	return false;
}

function updateChangesetLinks($row, $match) {
	global $_DEBUG, $db;
	
	// These are working repo links for our company, obviously switch with correct links to your
	// repos or customize as you see fit.
	if ($match[2] == 'repo2') {
		$newLinks = "[Fisheye {$match[1]}|https://fe.yourcompany.org/fisheye/changelog/repo2?cs={$match[1]}] [trac {$match[1]}|https://dev.yourcompany.org/projects/yourcompany.org/changeset/{$match[1]}/repo2]";
	}
	else {
		$newLinks = "[Fisheye {$match[1]}|https://fe.yourcompany.org/fisheye/changelog/repo1?cs={$match[1]}] [trac {$match[1]}|https://dev.yourcompany.org/projects/yourcompany.org/changeset/{$match[1]}/repo1]";
	}
	// adjust $row id when searching different table field.
	$newComment = str_replace($match[0], $newLinks, $row['newvalue']);
	// cleans these out from SVN autocommit messages, if found
	$newComment = preg_replace('/#\!CommitTicket.*on="\d+"/', '', $newComment);
	
	// prevents errors when inserint comments with double quotes
	// do this last or previous replaces may not work
	$newComment = str_replace('"', '""', $newComment);
	
	if ($_DEBUG)
		echo "NEW COMMENT:\n$newComment\n\n";
	
	// COMMENT FOLLOWING LINE TO MAKE UPDATES PERMANENT
	// RECOMMEND TO DRY RUN, SCAN OUTPUT AND FIX ALL WARNINGS SEVERAL TIMES BEFORE PROCEEDING!
	return $newComment;
	
	// the where clause is the unique key for this table, 
	// use care when porting other tables to include all unique key field where clauses
	$query = "UPDATE ticket_change SET newvalue = \"$newComment\" WHERE ticket = '{$row['ticket']}' AND time = '{$row['time']}' AND field = '{$row['field']}'";
	// alternate table query, remember to adjust ALL $row ids before switching to different tables
	// $query = "UPDATE ticket SET description = \"$newComment\" WHERE id = '{$row['id']}'";
	
	$r = $db->exec($query);
	if (!$r) {
		
		echo "Unable to update database with query:\n$query\n\n";
	}
	else {
		echo "VERIFY: ".verifyChangeset($row);
	}
	
	return $newComment;
}

function verifyChangeset($row) {
	global $db;
	
	// the where clause is the unique key for this table, 
	// use care when porting other tables to include all unique key field where clauses
	$query = "select * from ticket_change where field = 'comment' and time = '{$row['time']}' and ticket = '{$row['ticket']}'";

	$result = $db->query($query);
	if (!$result)
	   die("Error with query: $query");

	$verifiedRow = $result->fetchArray(SQLITE3_ASSOC);
	return var_export($verifiedRow,true);
}

// init main

$db = new TracDB();

// this is the table we are targetting for upgrade
// you can switch this out with other tables but must also align all other
// queries and $row key values accordingly.
$query = "select * from ticket_change where field = 'comment'";
// $query = "select * from ticket";
$result = $db->query($query);

if (!$result)
   die("Error with query: $query");

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
	
	$match = findMatch($row);
	
	if ($match) {
		$replaced = updateChangesetLinks($row, $match);
	}
}

echo "\nDONE\n";

$db->close();

/*
How I imported a 4 year old legacy trac with multiple subversion repositories and commit hooks to Atlassian JIRA.
	
	First, rolled up count(*) with group by queries on all schema tables with an sid, username, author, reporter, owner, cc. eg:

SELECT count(*), cc FROM ticket GROUP BY cc;

then used variations on sample queries below to fix name typos or account renames and delete items I knew were no longer needed.  You could also rename previous developers you no longer need to identify into a single 'devuser' account.  Another performance gain would be to clean out your ticket_custom entries you do not need to preserve going forward, this will save time on the issue import.

	Obviously use care in this cleanup, one mistake and you need to start over.  I compiled a list of rollup queries and update commands before hand, and ran them in succession until it looked right.  Each time I failed I restored the trac.db and started over.
	
	JIRA seems to only care about the ticket and ticket_change fields when importing users, but could also be looking at other fields.  ticket.cc field is the real tricky one since it is prone to typos and all kinds of dupes, email addresses, etc, which is why I just set them all to '' (see below.)
		
	After I was satisfied the data was aligned for users, I made a backup of the trac.db in case the SVN link conversion failed.

    I ran this PHP script to update trac subversion repo links to be fully portable to a JIRA install using Fisheye and also retain a remote trac link for archive access.  In order to pair down our users to < 11 for a starter JIRA account, we chose to start our Fisheye import at a later changeset so not all ported Fisheye links would work, hence the extra link to the legacy trac install.  Your mileage and needs may vary, so please read the PHP script thoroughly and customize for your liking and test run it until you are sure it will suffice and there are no warnings.  If you make a mistake you need to restart from a restored trac.db.  This script was written and tested on a standard PHP 5.4 install with the Sqlite3 plugin compiled.  Although it should work with PHP 5.2 or 5.3 with Sqlite3 plugin.  It only took a few seconds to update several thousand ticket_change records.

		After this, I cleaned all files out of the trac install folder (working from a backup folder, of course) which meant everything but db/trac.db, attachments/ticket/* and conf/trac.ini.  Then I made a zip from within the trac install folder.  With 553 attachment files and our trac DB, the final zip file was about 126M and took < 10 minutes to upload to the JIRA trac project importer.  Prior to import, I created an empty project on JIRA (Trac Archive) and downloaded the free trac workflow from Atlassian marketplace and made sure I switched the scheme successfully.  For some reason the workflow switch had a problem 'sticking' for me, so I kept this window open and opened a new window for the actual Project Import process incase selecting the workflow failed during the import steps (to prevent a reupload.)

		Aside from the workflow hiccup on import, most everything else went smoothly.  I did have to assign the status fields manually, but most everything else mapped correctly.

		The final import of our 4001 issues and 553 attachments took about another 10 minutes.  This took three tries before I got it right, so be prepared to delete your Trac Archive project and start over incase of a snafu; they can happen, it's an involved process.  

// sample queries to use to trim or align users and custom fields prior to import

update attachment set author = 'correct' where author = 'Wrong';
update component set owner = '';
update ticket set cc = '';
update ticket set reporter = 'correct' where reporter = 'Wrong';
update ticket set owner = 'correct' where owner = 'Wrong';
delete from session where sid = 'deleteduser';
delete from session_attribute where sid = 'deleteduser';
delete from permission where username = 'deleteduser';
update ticket_change set author = 'correct' where author = 'Wrong';
update report set author = 'correct' where author = 'Wrong';
update revision set author = 'correct' where author = 'Wrong';
update wiki set author = 'correct' where author = 'Wrong';
delete from ticket_custom where name = 'billable' or name = 'internal';

// Our trac schema for comparison: we had several plugins installed:
// advancedworkflow
// burndown
// datefield
// mastertickets
// timingandestimationplugin
// tracjsgantt
// tracopt
// tracrpc
// tracsubtickets

.schema
CREATE TABLE attachment (
    type text,
    id text,
    filename text,
    size integer,
    time integer,
    description text,
    author text,
    ipnr text,
    UNIQUE (type,id,filename)
);
CREATE TABLE auth_cookie (
    cookie text,
    name text,
    ipnr text,
    time integer,
    UNIQUE (cookie,ipnr,name)
);
CREATE TABLE bill_date (
            time integer,
            set_when integer,
            str_value text
            );
CREATE TABLE burndown (
    id integer PRIMARY KEY,
    component_name text,
    milestone_name text,
    date text,
    hours_remaining integer
);
CREATE TABLE cache (
    id text PRIMARY KEY,
    generation integer
);
CREATE TABLE component (
    name text PRIMARY KEY,
    owner text,
    description text
);
CREATE TABLE custom_report (id         INTEGER,uuid       VARCHAR(64),maingroup  VARCHAR(255),subgroup   VARCHAR(255),version    INTEGER,ordering   INTEGER);
CREATE TABLE enum (
    type text,
    name text,
    value text,
    UNIQUE (type,name)
);
CREATE TABLE mastertickets (
    source integer,
    dest integer,
    UNIQUE (source,dest)
);
CREATE TABLE milestone (
    name text PRIMARY KEY,
    due integer,
    completed integer,
    description text,
    started integer
);
CREATE TABLE node_change (
    repos integer,
    rev text,
    path text,
    node_type text,
    change_type text,
    base_path text,
    base_rev text,
    UNIQUE (repos,rev,path,change_type)
);
CREATE TABLE permission (
    username text,
    action text,
    UNIQUE (username,action)
);
CREATE TABLE report (
    id integer PRIMARY KEY,
    author text,
    title text,
    query text,
    description text
);
CREATE TABLE repository (
    id integer,
    name text,
    value text,
    UNIQUE (id,name)
);
CREATE TABLE revision (
    repos integer,
    rev text,
    time integer,
    author text,
    message text,
    UNIQUE (repos,rev)
);
CREATE TABLE schedule (
    ticket integer PRIMARY KEY,
    start integer,
    finish integer
);
CREATE TABLE schedule_change (
    ticket integer,
    time integer,
    oldstart integer,
    oldfinish integer,
    newstart integer,
    newfinish integer,
    UNIQUE (ticket,time)
);
CREATE TABLE session (
    sid text,
    authenticated integer,
    last_visit integer,
    UNIQUE (sid,authenticated)
);
CREATE TABLE session_attribute (
    sid text,
    authenticated integer,
    name text,
    value text,
    UNIQUE (sid,authenticated,name)
);
CREATE TABLE subtickets (
    parent integer,
    child integer,
    UNIQUE (parent,child)
);
CREATE TABLE system (
    name text PRIMARY KEY,
    value text
);
CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);
CREATE TABLE ticket_change (
    ticket integer,
    time integer,
    author text,
    field text,
    oldvalue text,
    newvalue text,
    UNIQUE (ticket,time,field)
);
CREATE TABLE ticket_custom (
    ticket integer,
    name text,
    value text,
    UNIQUE (ticket,name)
);
CREATE TABLE version (
    name text PRIMARY KEY,
    time integer,
    description text
);
CREATE TABLE wiki (
    name text,
    version integer,
    time integer,
    author text,
    ipnr text,
    text text,
    comment text,
    readonly integer,
    UNIQUE (name,version)
);
CREATE INDEX burndown_id_idx ON burndown (id);
CREATE INDEX node_change_repos_rev_idx ON node_change (repos,rev);
CREATE INDEX revision_repos_time_idx ON revision (repos,time);
CREATE INDEX schedule_change_ticket_idx ON schedule_change (ticket);
CREATE INDEX schedule_change_time_idx ON schedule_change (time);
CREATE INDEX schedule_ticket_idx ON schedule (ticket);
CREATE INDEX session_authenticated_idx ON session (authenticated);
CREATE INDEX session_last_visit_idx ON session (last_visit);
CREATE INDEX ticket_change_ticket_idx ON ticket_change (ticket);
CREATE INDEX ticket_change_time_idx ON ticket_change (time);
CREATE INDEX ticket_status_idx ON ticket (status);
CREATE INDEX ticket_time_idx ON ticket (time);
CREATE INDEX wiki_time_idx ON wiki (time);


*/