Kanboard to Gitea migration

Some time ago I decided to move all tasks from Kanboard closer to the projects in my Gitea self-hosted instance. Surprisingly, Gitea doesn't have any importers, so I ended up preparing the data and calling the Gitea API.

Database#

I started by downloading the SQLite database. Inside the application, there is a specific option to do this:

There are quite a lot of tables, but the structure is simple.

tags#

Zero project id means these tags are global.

project_has_categories#

tasks#

Of course, there are also modification and completion dates, colors, etc. Note that the category has a one-to-one relation, while the tags have many-to-many.

In Gitea, all of these tags and categories should be under Labels. First, let's create them:

Gitea API#

Now, let's go to the Gitea API to check what's needed to create an issue.

owner*  string   owner of the repo
repo *  string   name of the repo
body    object

CreateIssueOption {
  assignee   string deprecated
  assignees  [string]
  body       string
  closed     boolean
  due_date   string($date-time)
  labels     [integer($int64)]
  milestone  integer($int64)
  ref        string
  title*     string
}

Quite a simple structure. Moreover, I don't need the due_date, milestone, ref, and assignees is always only me.

Overall, the idea is:

1. Prepare a SQL query containing the title, body, closed and labels columns.
2. Convert the result to a valid JSON array of tasks.
3. For each task object in the array, make a request to the Gitea API using cURL.

Data retrieval#

Let's start by retrieving the title, body, category and tags. For the tags, grouping and aggregation are required:

SELECT
  t.title,
  (1 - t.is_active) as closed,
  t.description as body,
  c.name as category,
  group_concat(tg.name) as tags
FROM tasks t
LEFT JOIN project_has_categories c ON t.category_id = c.id
LEFT JOIN task_has_tags ttg ON ttg.task_id = t.id
LEFT JOIN tags tg ON tg.id = ttg.tag_id
GROUP BY t.id
ORDER BY t.date_completed, t.date_modification

Now, let's combine category and tags into one column labels. Also, why not just include the creation/modification date time at the end of the body column?

SELECT
   t.title,
   (1 - t.is_active) as closed,
   -- body
   trim(
     concat(
	    t.description,
		"\n\nCreated: ",
		strftime(
		   "%Y-%m-%d %H:%M:%S",
		   datetime(t.date_creation, "unixepoch")
		),
		"\nLast modified: ",
		strftime(
		   "%Y-%m-%d %H:%M:%S",
		   datetime(coalesce(t.date_completed, t.date_modification), "unixepoch")
		)
	 ),
	 "\n"
   ) as body,
   -- tags
   trim(
     concat(c.name, ",", group_concat(tg.name)),
     ","
   ) as labels
FROM tasks t
LEFT JOIN project_has_categories c ON t.category_id = c.id
LEFT JOIN task_has_tags ttg ON ttg.task_id = t.id
LEFT JOIN tags tg ON tg.id = ttg.tag_id
GROUP BY t.id
ORDER BY t.date_completed, t.date_modification

The body now concatenates the task description, the formatted creation time, and the last modification time. If there's no description, trim("\n\nCreated: ...", "\n") will remove leading line breaks.

Label IDs#

According to the Gitea API, labels should contain an array of label ids. We need to replace these "articles,hard,feature" with ids from Gitea:

[
  {
    "id": 48,
    "name": "articles",
    "exclusive": false,
    "is_archived": false,
    "color": "cf7113",
    "description": "",
    "url": "https://.../api/v1/repos/annimon/repo/labels/48"
  },
  {
    "id": 46,
    "name": "bug",
    "exclusive": false,
    "is_archived": false,
    "color": "f03e41",
    "description": "",
    "url": "https://.../api/v1/repos/annimon/repo/labels/46"
  },
  ...
]

The trick is to just rename the categories and tags:

and voilà

JSON transformation#

Finally, export the result as JSON. Many SQLite clients have an option for this.

All that's left to do is construct the correct JSON using https://jsoneditoronline.org/ and JSON Query.

map({
  "assignees": [],
  "body": .body,
  "title": .title,
  "closed": .closed,
  "labels": split(.tags, ",")
    | filter(get() != "")
    | mapValues(
        number(get())
      )
    | values()
})

Bash script#

The rest is a simple Bash script that uses jq to extract an object from a JSON array and sends a POST request to the Gitea API using cURL:

#!/usr/bin/env sh
cat "in.json" | jq -c '.[]' | while read -r payload; do
  echo "$payload"
  curl -s -X POST \
    -H "Content-Type: application/json" \
    -H "Authorization: token $GITEA_TOKEN" \
    -d "$payload" \
    "${GITEA_URL}/repos/annimon/repo/issues"
done

Unfortunately, the Gitea API doesn't have a Projects API yet, so it's impossible to assign a ticket to a project board.

Result#

Finally, here's the result: