CSV Export Queries to Migrate from Trac 0.12 on PostgreSQL to Pivotal Tracker

gistfile1.sql

Here are some helper queries I used to generate a CSV file from Trac (0.12, but probably applicable to any 0.10 or higher build) that could be imported into Pivotal Tracker:

-- Some of the logic is based on https://github.com/hinnerk/Trac2Pivotal which works with sqlite3 Trac instances.  
-- I don't know Python and ours is PostgreSQL based so I rolled these queries instead.

-- in order to make text formatting, headers and a few other key text features work in the Pivotal/Github Markdown, we 
-- create this function which is based on: https://gist.github.com/619537/94091aa59bdf6d6e5ad2fbb063465b2d160156ad
CREATE OR REPLACE FUNCTION public.wiki2markdown(wiki text) RETURNS text LANGUAGE sql AS $function$
SELECT 
	regexp_replace(
	regexp_replace(
	regexp_replace(
	regexp_replace(
	regexp_replace(
	regexp_replace(
	regexp_replace(
	regexp_replace( 
	regexp_replace(wiki
					, '\}\}\}', '```', 'g')
					, '\{\{\{', '```', 'g')
					, 'r([1-9][0-9]{1,3})', E'[r\\1](http://trac.pukkasoft.com/svn/pukka/msr/trunk?p=\\1)', 'g')
					, '====\s(.+?)\s====', E'### \\1', 'g')
					, '===\s(.+?)\s===', E'## \\1', 'g')
					, '==\s(.+?)\s==', E'# \\1', 'g')
					, '=\s(.+?)\s=', E'# \\1', 'g')
					, '''''''(.+)''''''', E'*\\1*', 'g')
					, '''''(.+)''''', E'_\\1_', 'g')

$function$ 
;

-- create a temp table with the pivotal-prepared data
DROP TABLE IF EXISTS t2p;
CREATE TEMP TABLE t2p AS (
WITH comments AS (
	SELECT * 
	FROM crosstab('select ticket.id, ''Comment'' AS type, wiki2markdown(substring(ticket_change.newvalue FROM 1 FOR 19998)) from ticket, ticket_change where ticket.id = ticket_change.ticket and ticket_change.field = ''comment'' order by id') 
	AS ct(id integer, Comment1 text
	,Comment2 text
	,Comment3 text
	,Comment4 text
	,Comment5 text
	,Comment6 text
	,Comment7 text
	,Comment8 text
	,Comment9 text
	,Comment10 text
	,Comment11 text
	,Comment12 text
	,Comment13 text
	,Comment14 text
	,Comment15 text
	,Comment16 text
	,Comment17 text
	,Comment18 text
	,Comment19 text
	,Comment20 text
	,Comment21 text
	,Comment22 text
	,Comment23 text
	,Comment24 text
	,Comment25 text
	,Comment26 text
	,Comment27 text
	,Comment28 text
	,Comment29 text
	,Comment30 text)
) SELECT t.id
	,summary AS Title
	-- if you want to massage your component names before importing as labels, here's where you would do it
	,CASE WHEN component = 'Server/Software' THEN 'infrastructure'
		WHEN component = 'REST API/Plugins' THEN 'api'
		WHEN component = 'website.com' THEN 'marketing'
		WHEN component = 'API' THEN 'backend'
		WHEN component = 'Admin' THEN 'superuser'
		ELSE lower(component)
	 END AS Labels
	,CASE WHEN type = 'enhancement' THEN 'feature'
		  WHEN type = 'defect' THEN 'bug'
		  WHEN type = 'task' THEN 'chore'
	 END AS Type
	-- first sets all 'fixed' to accepted, then looks at blank resolution values (because invalid, duplicate, worksforme, wontfix are all filtered out)
	-- At Bat and On Deck were two milestones in our Trac issue which we wanted to migrate effectively to "started" and "next up in the backlog".  Could have changed these by hand once imported too.
	,CASE WHEN resolution = 'fixed' THEN 'accepted'
		  WHEN milestone = 'At Bat' THEN 'started'
		  WHEN milestone = 'On Deck' THEN 'unstarted'
		  ELSE 'unscheduled'
	 END AS "Current State"
	-- set estimate based on accepted, but import seems to require for all states
	-- docs state and customer service told me bugs/chores can't have estimates but I got errors if I did not give them a placeholder value
	,CASE WHEN type = 'enhancement' THEN '1'
		  ELSE ''
	 END AS estimate
	,to_char(to_timestamp(time/1000000), 'MM/DD/YYYY') AS "Created At"
	,CASE WHEN resolution = 'fixed' THEN to_char(to_timestamp(changetime/1000000), 'MM/DD/YYYY') 
		ELSE ''
	 END AS "Accepted At"
	,''::varchar AS "Owned By"
	-- 20000 character limit according to docs
	,wiki2markdown(substring(description FROM 1 FOR 19998)) AS description
	-- give it history.  we use the [tracXXX] syntax to make searching for a specific trac ID in pivotal possible
	,'Source: http://link.trac.com/path/to/tickets/' || t.id || ' [trac' || t.id || ']
CC: ' || cc || '
Keywords: ' || keywords AS comment0
	,comment1, comment2, comment3,comment4, comment5, comment6, comment7, comment8, comment9, comment10, comment11, comment12, comment13, comment14, comment15, comment16, comment17, comment18, comment19, comment20, comment21, comment22, comment23, comment24, comment25, comment26, comment27, comment28, comment29, comment30
  FROM ticket t LEFT OUTER JOIN comments ON t.id = comments.id
  -- no equivalent in pivotal and electing not to capture
  WHERE (resolution NOT IN ('invalid', 'duplicate', 'worksforme', 'wontfix') OR resolution IS NULL)
  ORDER BY t.id
  -- export in 500-ticket batches if you have > 500 to migrate
  -- OFFSET 500
  LIMIT 500
);

-- now export to a nicely formatted CSV file
\copy (SELECT * FROM t2p) TO '/tmp/pivotal.csv' WITH CSV HEADER FORCE QUOTE *

-- last step before importing is to conver Comment0,Comment1,Comment2 to just Comment,Comment,Comment.  Cut/paste these headers:
-- id,title,labels,type,Current State,estimate,Created At,Accepted At,Owned By,description,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment,Comment

-- we also manually downloaded and uploaded attachments on tickets not yet done.  You can find them with:
SELECT  'http://link.trac.com/path/to/tickets/' || a.id, a.filename, a.description
FROM attachment a, ticket t 
WHERE a.id::integer = t.id 
AND a.type = 'ticket'
AND (t.resolution NOT IN ('fixed', 'invalid', 'duplicate', 'worksforme', 'wontfix') OR t.resolution IS NULL);