Migrate repos in order of recency

[cybardev]

Currently it migrates in alphabetical order, which means repos starting with A are oldest and Z are newest, which is... fine, but not very useful. I would ofc want my most recently active repos to show up on the first page.

[swt83]

I used this shell script to patch the dates. It requires a Forgejo token with the user and repo scopes, read only. This is just so it can fetch the list of all your repos. Then it uses MySQL terminal commands to patch the created_unix and updated_unix timestamps in the respository and branch tables.

(The problem with this solution is it assumes you used MySQL as your database.)

#!/bin/bash

# --- Prompt for Forgejo API vars ---
read -rp "Forgejo URL (e.g. https://forgejo.example.com): " FORGEJO_URL
read -rp "API Token: " TOKEN
read -rp "Username: " USERNAME

# --- Prompt for MySQL/MariaDB vars ---
echo ""
echo "--- Database Configuration ---"
read -rp "DB Host (default: 127.0.0.1): " DB_HOST
DB_HOST="${DB_HOST:-127.0.0.1}"
read -rp "DB Port (default: 3306): " DB_PORT
DB_PORT="${DB_PORT:-3306}"
read -rp "DB Name (default: forgejo): " DB_NAME
DB_NAME="${DB_NAME:-forgejo}"
read -rp "DB User (default: forgejo): " DB_USER
DB_USER="${DB_USER:-forgejo}"
read -rsp "DB Password: " DB_PASS
echo ""

# Helper: run a mysql query
mysql_query() {
    mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -sNe "$1"
}

# Helper: convert ISO 8601 to unix timestamp (Linux + macOS)
to_unix() {
    local iso="$1"
    local ts
    ts=$(date -d "$iso" +%s 2>/dev/null)
    if [ -z "$ts" ]; then
        ts=$(date -j -f "%Y-%m-%dT%H:%M:%SZ" "$iso" +%s 2>/dev/null)
    fi
    echo "$ts"
}

# Helper: fetch oldest commit date for a branch (handles pagination)
get_oldest_commit_date() {
    local repo="$1"
    local branch="$2"

    local commits_response
    commits_response=$(curl -si -H "Authorization: token $TOKEN" \
        "$FORGEJO_URL/api/v1/repos/$USERNAME/$repo/commits?limit=50&page=1&sha=$branch")

    local total
    total=$(echo "$commits_response" | grep -i '^x-total-count:' | tr -d '[:space:]' | cut -d: -f2)

    if [ -n "$total" ] && [ "$total" -gt 50 ]; then
        local last_page=$(( (total + 49) / 50 ))
        curl -s -H "Authorization: token $TOKEN" \
            "$FORGEJO_URL/api/v1/repos/$USERNAME/$repo/commits?limit=50&page=$last_page&sha=$branch" | \
            jq -r '.[-1].commit.committer.date'
    else
        echo "$commits_response" | sed -n '/^\[/,$ p' | jq -r '.[-1].commit.committer.date'
    fi
}

# Helper: fetch all repos with pagination
get_all_repos() {
    local page=1
    local result
    local all=""
    while true; do
        result=$(curl -s -H "Authorization: token $TOKEN" \
            "$FORGEJO_URL/api/v1/user/repos?limit=50&page=$page" | jq -r '.[].name')
        [ -z "$result" ] && break
        all="$all"$'\n'"$result"
        page=$((page + 1))
    done
    echo "$all" | sed '/^$/d'
}

# --- Get all repositories ---
echo ""
echo "Fetching repository list..."
repos=$(get_all_repos)

if [ -z "$repos" ]; then
    echo "No repositories found or error fetching list. Check your URL/token."
    exit 1
fi

repo_count=$(echo "$repos" | wc -l)
echo "Found $repo_count repositories."

# --- Loop through each repository ---
for repo in $repos; do
    echo ""
    echo "Processing: $repo"

    # 1. Get all branches for this repo
    branches=$(curl -s -H "Authorization: token $TOKEN" \
        "$FORGEJO_URL/api/v1/repos/$USERNAME/$repo/branches?limit=50" | \
        jq -r '.[].name')

    if [ -z "$branches" ]; then
        echo "  -> No branches found. Skipping."
        continue
    fi

    # 2. Get repo ID from DB
    repo_id=$(mysql_query "
        SELECT r.id FROM repository r
        JOIN user u ON r.owner_id = u.id
        WHERE u.name = '$USERNAME' AND r.name = '$repo'
        LIMIT 1;
    ")

    if [ -z "$repo_id" ]; then
        echo "  -> Could not find repo in database. Skipping."
        continue
    fi

    echo "  -> DB repo ID: $repo_id"

    overall_first_ts=""
    overall_latest_ts=""

    # 3. Process each branch
    for branch in $branches; do
        echo "  -> Branch: $branch"

        latest_date=$(curl -s -H "Authorization: token $TOKEN" \
            "$FORGEJO_URL/api/v1/repos/$USERNAME/$repo/commits?limit=1&page=1&sha=$branch" | \
            jq -r '.[0].commit.committer.date')

        if [ "$latest_date" = "null" ] || [ -z "$latest_date" ]; then
            echo "     -> No commits found on branch '$branch'. Skipping."
            continue
        fi

        oldest_date=$(get_oldest_commit_date "$repo" "$branch")

        if [ "$oldest_date" = "null" ] || [ -z "$oldest_date" ]; then
            echo "     -> Could not determine oldest commit for '$branch'. Skipping."
            continue
        fi

        latest_ts=$(to_unix "$latest_date")
        oldest_ts=$(to_unix "$oldest_date")

        echo "     -> oldest=$oldest_date  latest=$latest_date"

        # Update branch table with all three timestamps
        mysql_query "
            UPDATE branch
            SET commit_time = $latest_ts,
                created_unix = $oldest_ts,
                updated_unix = $latest_ts
            WHERE repo_id = $repo_id AND name = '$branch';
        "
        echo "     -> branch updated (commit_time, created_unix, updated_unix)"

        # Track overall earliest/latest across all branches
        if [ -z "$overall_first_ts" ] || [ "$oldest_ts" -lt "$overall_first_ts" ]; then
            overall_first_ts=$oldest_ts
        fi
        if [ -z "$overall_latest_ts" ] || [ "$latest_ts" -gt "$overall_latest_ts" ]; then
            overall_latest_ts=$latest_ts
        fi
    done

    if [ -z "$overall_first_ts" ] || [ -z "$overall_latest_ts" ]; then
        echo "  -> Could not determine repo-level timestamps. Skipping."
        continue
    fi

    # 4. Update repository table
    mysql_query "
        UPDATE repository
        SET created_unix = $overall_first_ts,
            updated_unix = $overall_latest_ts
        WHERE id = $repo_id;
    "
    echo "  -> repository: created_unix=$overall_first_ts updated_unix=$overall_latest_ts"

    sleep 0.2
done

echo ""
echo "All done!"

[cybardev]

I used PGSQL, but thanks for the idea. I'll see what I can do. Shouldn't be too far off.

It's a little more "involved" for me tho, since I use containers via compose files...