SQL skript pro získání dat z Redmine a jejich vložení do databáze Gitlabu.

redmine-to-gitlab_conversion-script.sql

-- attachments
SELECT
	container_id AS issue_id,
	filename,
	content_type,
	author_id,
	created_on,
	description
FROM attachments
WHERE container_type = 'Issue'
	ORDER BY container_id;

-- issues
SET SESSION group_concat_max_len = 1000000000;

SELECT
	id, -- issue_id
	subject AS title,
	CASE WHEN assigned_to_id = 6 THEN 1 WHEN assigned_to_id = 8 THEN 3 WHEN assigned_to_id = 16 THEN 7 WHEN assigned_to_id = 25 THEN 6 WHEN assigned_to_id = 35 THEN 4 ELSE NULL END AS assignee_id,
	CASE WHEN author_id = 6 THEN 1 WHEN assigned_to_id = 8 THEN 3 WHEN assigned_to_id = 16 THEN 7 WHEN author_id = 25 THEN 6 WHEN author_id = 35 THEN 4 ELSE 3 END AS author_id,
	1 AS project_id,
	created_on created_at,
	updated_on updated_at,
	0 AS position,
	NULL AS branch_name,
	CONCAT(description, '\n\n', COALESCE(r.content, '')),
	NULL AS milestone_id,
	CASE WHEN status_id IN (3, 6) THEN 'closed' ELSE 'opened' END AS state,
	id AS iid
FROM issues i
LEFT JOIN (
	SELECT issue_id, GROUP_CONCAT(content SEPARATOR '\n\n') AS content
	FROM (
		SELECT
			j.journalized_id AS issue_id,
			CONCAT('**', u.firstname, ' ', u.lastname, ' (', u.login, ') dne ', DATE_FORMAT(j.created_on, '%e. %c. %Y v %H.%i'), ' řekl(a):**\n\n', j.notes) AS content
		FROM journals AS j
		JOIN users AS u ON u.id = j.user_id
		WHERE j.journalized_type = 'Issue'
			AND j.notes IS NOT NULL
			AND TRIM(j.notes) != ''
			ORDER BY j.created_on
	) d
	GROUP BY issue_id
) r ON r.issue_id = i.id
WHERE project_id = 1
ORDER BY id;

-- issue_categories
SELECT
	id + 14 AS id,
	CASE WHEN category_id = 9 THEN 8
	WHEN category_id = 10 THEN 9
	WHEN category_id = 13 THEN 10
	WHEN category_id = 14 THEN 11
	WHEN category_id = 17 THEN 12
	WHEN category_id = 18 THEN 13
	WHEN category_id = 19 THEN 14
	ELSE category_id
	END AS tag_id,
	id AS taggable_id,
	'Issue' AS taggable_type,
	NULL AS tagger_id,
	NULL AS tagger_type,
	'labels' AS context,
	created_on AS created_at
FROM issues
WHERE project_id = 1
	AND category_id IS NOT NULL
ORDER BY id;


-- trackers
SELECT
	id + 577 AS id,
	CASE WHEN tracker_id = 1 THEN 33
	WHEN tracker_id = 2 THEN 40
	ELSE NULL
	END AS tag_id,
	id AS taggable_id,
	'Issue' AS taggable_type,
	NULL AS tagger_id,
	NULL AS tagger_type,
	'labels' AS context,
	created_on AS created_at
FROM issues
WHERE project_id = 1
ORDER BY id;


-- users (pouze pro navázání vazeb s konkrétními uživateli)

-- RedmineId - nick - GitlabId
-- 6 - user1 - 1
-- 8 - user2 - 3
-- 25 - user3 - 6
-- 35 - user4 - 4
-- 16 - user5 - 7