swee / Forgejo 14.0.0 SQLite to PostgreSQL

version id, version forgejo_version id, version forgejo_sem_ver version forgejo_migration id, created_unix dbfs_meta id, full_path, block_size, file_size, create_timestamp, modify_timestamp dbfs_data id, revision, meta_id, blob_offset, blob_size, blob_data email_hash hash, email access_token id, uid, name, token_hash, token_salt, token_last_eight, scope, created_unix, updated_unix oauth2_application id, uid, name, client_id, client_secret, *confidential_client, redirect_uris, created_unix, updated_unix oauth2_authorization_code id, grant_id, code, code_challenge, code_challenge_method, redirect_uri, valid_until oauth2_grant id, user_id, application_id, counter, scope, nonce, created_unix, updated_unix session key, data, expiry login_source id, type, name, *is_active, *is_sync_enabled, cfg, created_unix, updated_unix two_factor id, uid, scratch_salt, scratch_hash, last_used_passcode, created_unix, updated_unix, secret webauthn_credential id, name, lower_name, user_id, credential_id, public_key, attestation_type, aaguid, sign_count, *clone_warning, created_unix, updated_unix, *backup_eligible, *backup_state, *legacy abuse_report id, status, reporter_id, content_type, content_id, category, remarks, shadow_copy_id, created_unix, resolved_unix abuse_report_shadow_copy id, raw_value, created_unix app_state id, revision, content notice id, type, description, created_unix system_setting id, setting_key, setting_value, version, created, updated hook_task id, hook_id, uuid, payload_content, payload_version, event_type, *is_delivered, delivered, *is_succeed, request_content, response_content webhook id, repo_id, owner_id, *is_system_webhook, url, http_method, content_type, secret, events, *is_active, type, meta, last_status, header_authorization_encrypted, created_unix, updated_unix federation_host id, host_fqdn, software_name, !latest_activity, created, updated, key_id, public_key, host_port, host_schema badge id, description, image_url user_badge id, badge_id, user_id forgejo_blocked_user id, block_id, user_id, created_unix email_address id, uid, email, lower_email, *is_activated, *is_primary external_login_user external_id, user_id, login_source_id, raw_data, provider, email, name, first_name, last_name, nick_name, description, avatar_url, location, access_token, access_token_secret, refresh_token, !expires_at follow id, user_id, follow_id, created_unix user_open_id id, uid, uri, *show user_redirect id, lower_name, redirect_user_id, created_unix user_setting id, user_id, setting_key, setting_value federated_user id, user_id, external_id, federation_host_id, key_id, public_key, normalized_original_url, inbox_path federated_user_follower id, followed_user_id, following_user_id repo_archive_download_count id, repo_id, release_id, type, count repo_archiver id, repo_id, type, status, commit_id, created_unix user id, lower_name, name, full_name, email, *keep_email_private, email_notifications_preference, passwd, passwd_hash_algo, *must_change_password, login_type, login_name, type, location, website, pronouns, rands, salt, language, description, created_unix, last_login_unix, *last_repo_visibility, max_repo_creation, *is_active, *is_admin, *is_restricted, *allow_git_hook, *allow_import_local, *allow_create_organization, *prohibit_login, avatar, avatar_email, *use_custom_avatar, num_followers, num_following, num_stars, num_repos, num_teams, num_members, visibility, *repo_admin_change_team_access, diff_view_style, theme, *keep_activity_private, *enable_repo_unit_hints, *keep_pronouns_private, login_source, updated_unix attachment id, uuid, repo_id, issue_id, release_id, uploader_id, comment_id, name, download_count, size, created_unix, external_url language_stat id, repo_id, commit_id, *is_primary, language, size, created_unix mirror id, repo_id, interval, *enable_prune, updated_unix, next_update_unix, *lfs_enabled, lfs_endpoint, remote_address push_mirror id, repo_id, remote_name, remote_address, *sync_on_commit, interval, created_unix, last_update, last_error, public_key, private_key, branch_filter repo_redirect id, owner_id, lower_name, redirect_repo_id release id, repo_id, publisher_id, tag_name, original_author, original_author_id, lower_tag_name, target, title, sha1, *hide_archive_links, num_commits, note, *is_draft, *is_prerelease, *is_tag, created_unix forgejo_repo_flag id, repo_id, name repo_indexer_status id, repo_id, commit_sha, indexer_type following_repo id, repo_id, external_id, federation_host_id, uri repo_unit id, repo_id, type, config, created_unix, default_permissions star id, uid, repo_id, created_unix topic id, name, repo_count, created_unix, updated_unix repo_topic repo_id, topic_id upload id, uuid, name watch id, user_id, repo_id, mode, created_unix, updated_unix action_artifact id, run_id, runner_id, repo_id, owner_id, commit_sha, storage_path, file_size, file_compressed_size, content_encoding, artifact_path, artifact_name, status, created_unix, updated_unix, expired_unix action_run id, title, repo_id, owner_id, workflow_id, index, trigger_user_id, schedule_id, ref, commit_sha, *is_fork_pull_request, *need_approval, approved_by, event, event_payload, trigger_event, status, version, started, stopped, previous_duration, created, updated, *notify_email, pre_execution_error, concurrency_group, concurrency_type, pull_request_poster_id, pull_request_id, workflow_directory, pre_execution_error_code, pre_execution_error_details action_run_index group_id, max_index action_run_job id, run_id, repo_id, owner_id, commit_sha, *is_fork_pull_request, name, attempt, workflow_payload, job_id, needs, runs_on, task_id, status, started, stopped, created, updated action_runner id, uuid, name, version, owner_id, repo_id, description, base, repo_range, token_hash, token_salt, last_online, last_active, agent_labels, created, updated, deleted action_runner_token id, token, owner_id, repo_id, *is_active, created, updated, deleted action_schedule id, title, specs, repo_id, owner_id, workflow_id, trigger_user_id, ref, commit_sha, event, event_payload, content, created, updated, workflow_directory action_schedule_spec id, repo_id, schedule_id, next, prev, spec, created, updated action_task id, job_id, attempt, runner_id, status, started, stopped, repo_id, owner_id, commit_sha, *is_fork_pull_request, token_hash, token_salt, token_last_eight, log_filename, *log_in_storage, log_length, log_size, log_indexes, *log_expired, created, updated action_task_output id, task_id, output_key, output_value action_task_step id, name, task_id, index, repo_id, status, log_index, log_length, started, stopped, created, updated action_tasks_version id, owner_id, repo_id, version, created_unix, updated_unix action_variable id, owner_id, repo_id, name, data, created_unix, updated_unix task id, doer_id, owner_id, repo_id, type, status, start_time, end_time, payload_content, message, created gpg_key id, owner_id, key_id, primary_key_id, content, created_unix, expired_unix, added_unix, emails, *verified, *can_sign, *can_encrypt_comms, *can_encrypt_storage, *can_certify gpg_key_import key_id, content public_key id, owner_id, name, fingerprint, content, mode, type, login_source_id, created_unix, updated_unix, *verified deploy_key id, key_id, repo_id, name, fingerprint, mode, created_unix, updated_unix project_board id, title, *default, sorting, color, project_id, creator_id, created_unix, updated_unix project_issue id, issue_id, project_id, project_board_id, sorting project id, title, description, owner_id, repo_id, creator_id, *is_closed, board_type, card_type, type, created_unix, updated_unix, closed_date_unix pull_auto_merge id, pull_id, doer_id, merge_style, message, created_unix, *delete_branch_after_merge review_state id, user_id, pull_id, commit_sha, updated_files, updated_unix secret id, owner_id, repo_id, name, created_unix, data org_user id, uid, org_id, *is_public team id, org_id, lower_name, name, description, authorize, num_repos, num_members, *includes_all_repositories, *can_create_org_repo team_user id, org_id, team_id, uid team_repo id, org_id, team_id, repo_id team_unit id, org_id, team_id, type, access_mode team_invite id, token, inviter_id, org_id, team_id, email, created_unix, updated_unix branch id, repo_id, name, commit_id, commit_message, pusher_id, *is_deleted, deleted_by_id, deleted_unix, commit_time, created_unix, updated_unix renamed_branch id, repo_id, from, to, created_unix commit_status id, index, repo_id, state, sha, target_url, description, context_hash, context, creator_id, created_unix, updated_unix commit_status_index id, repo_id, sha, max_index commit_status_summary id, repo_id, sha, state, target_url lfs_meta_object id, oid, size, repository_id, created_unix, updated_unix lfs_lock id, repo_id, owner_id, path, !created protected_branch id, repo_id, branch_name, *can_push, *enable_whitelist, whitelist_user_i_ds, whitelist_team_i_ds, *enable_merge_whitelist, whitelist_deploy_keys, merge_whitelist_user_i_ds, merge_whitelist_team_i_ds, *enable_status_check, status_check_contexts, *enable_approvals_whitelist, approvals_whitelist_user_i_ds, approvals_whitelist_team_i_ds, required_approvals, *block_on_rejected_reviews, *block_on_official_review_requests, *block_on_outdated_branch, *dismiss_stale_approvals, *require_signed_commits, protected_file_patterns, unprotected_file_patterns, *apply_to_admins, created_unix, updated_unix, *ignore_stale_approvals protected_tag id, repo_id, name_pattern, allowlist_user_i_ds, allowlist_team_i_ds, created_unix, updated_unix package id, owner_id, repo_id, type, name, lower_name, *semver_compatible, *is_internal package_blob id, size, hash_md5, hash_sha1, hash_sha256, hash_sha512, created_unix, hash_blake2b package_blob_upload id, bytes_received, hash_state_bytes, created_unix, updated_unix package_cleanup_rule id, *enabled, owner_id, type, keep_count, keep_pattern, remove_days, remove_pattern, *match_full_name, created_unix, updated_unix package_file id, version_id, blob_id, name, lower_name, composite_key, *is_lead, created_unix package_property id, ref_type, ref_id, name, value package_version id, package_id, creator_id, version, lower_version, created_unix, *is_internal, metadata_json, download_count quota_rule name, limit, subjects quota_group name quota_group_rule_mapping id, group_name, rule_name quota_group_mapping id, kind, mapped_id, group_name issue_assignees id, assignee_id, issue_id comment id, type, poster_id, original_author, original_author_id, issue_id, label_id, old_project_id, project_id, old_milestone_id, milestone_id, time_id, assignee_id, *removed_assignee, assignee_team_id, resolve_doer_id, old_title, new_title, old_ref, new_ref, dependent_issue_id, commit_id, line, tree_path, content, content_version, patch, created_unix, updated_unix, commit_sha, review_id, *invalidated, ref_repo_id, ref_issue_id, ref_comment_id, ref_action, *ref_is_pull issue_content_history id, poster_id, issue_id, comment_id, edited_unix, content_text, *is_first_created, *is_deleted issue_dependency id, user_id, issue_id, dependency_id, created_unix, updated_unix issue_index group_id, max_index issue_user id, uid, issue_id, *is_read, *is_mentioned issue_watch id, user_id, issue_id, *is_watching, created_unix, updated_unix label id, repo_id, org_id, name, *exclusive, description, color, num_issues, num_closed_issues, created_unix, updated_unix, archived_unix issue_label id, issue_id, label_id milestone id, repo_id, name, content, *is_closed, num_issues, num_closed_issues, completeness, created_unix, updated_unix, deadline_unix, closed_date_unix reaction id, type, issue_id, comment_id, user_id, original_author_id, original_author, created_unix review id, type, reviewer_id, reviewer_team_id, original_author, original_author_id, issue_id, content, *official, commit_id, *stale, *dismissed, created_unix, updated_unix repo_transfer id, doer_id, recipient_id, repo_id, team_i_ds, created_unix, updated_unix action id, user_id, op_type, act_user_id, repo_id, comment_id, ref_name, *is_private, content, created_unix federated_user_activity id, user_id, actor_id, actor_uri, note_content, note_url, original_note, created notification id, user_id, repo_id, status, source, issue_id, comment_id, created_unix, updated_unix forgejo_auth_token id, uid, lookup_key, hashed_validator, expiry, purpose repository id, owner_id, owner_name, lower_name, name, description, website, original_service_type, original_url, default_branch, wiki_branch, num_watches, num_stars, num_forks, num_milestones, num_closed_milestones, num_projects, num_closed_projects, *is_private, *is_empty, *is_archived, *is_mirror, status, *is_fork, fork_id, *is_template, template_id, size, git_size, lfs_size, *is_fsck_enabled, *close_issues_via_commit_in_any_branch, object_format_name, trust_model, avatar, created_unix, updated_unix, archived_unix, topics collaboration id, repo_id, user_id, mode, created_unix, updated_unix action_user id, user_id, repo_id, *trusted_with_pull_requests, last_access access id, user_id, repo_id, mode issue id, repo_id, index, poster_id, original_author, original_author_id, name, content, content_version, milestone_id, priority, *is_closed, *is_pull, num_comments, ref, pin_order, deadline_unix, created, created_unix, updated_unix, closed_unix, *is_locked pull_request id, type, status, conflicted_files, commits_ahead, commits_behind, changed_protected_files, issue_id, index, head_repo_id, head_branch, base_branch, merge_base, *allow_maintainer_edit, *has_merged, merger_id, flow, base_repo_id, merged_unix, merged_commit_id stopwatch id, issue_id, user_id, created_unix tracked_time id, issue_id, user_id, created_unix, time, *deleted

import sqlite3 import psycopg2 import re from datetime import datetime # Change these SQLITE_DB = "forgejo.db" DB_NAME = "forgejodb" DB_USER = "forgejo" DB_PASS = "hunter2" DB_HOST = "localhost" DB_PORT = "5432" rules = {x.split("\n")[0].strip(): x.split("\n")[1].strip().split(", ") for x in re.split(r'\n\s*\n', open("lol").read().strip())} print("[OK] Rules loaded.") # Prefix: ! def timestamp_conversion(ts): if not ts: return None return datetime.strptime(ts, "%Y-%m-%d %H:%M:%S") # Prefix: * def int_to_bool(integ): return integ == 1 # Dry run, verify the thingies all exist print("[...] Starting dry run...") sqdb = sqlite3.connect(SQLITE_DB) sqursor = sqdb.cursor() for table, col in rules.items(): colstring = ", ".join(["\"" + x.replace("*", "").replace("!", "") + "\"" for x in col]) items = sqursor.execute(f"SELECT {colstring} FROM {table};").fetchall() for row in items: newrow = [] for i, cols in enumerate(col): if cols[0] == "*": newrow.append(int_to_bool(row[i])) elif cols[0] == "!": newrow.append(timestamp_conversion(row[i])) bad = 0 for table, col in rules.items(): sqursor.execute(f"PRAGMA table_info({table});") tables_there = sqursor.fetchall() if len(col) != len(tables_there): extras = [x[1] for x in tables_there] for i in col: extras.remove(i.replace("*", "").replace("!", "")) print("[!!!] " + table + f" extra columns: {extras}") bad += 1 if bad: print(f"[!!!] {bad} tables found with not enough items") input("Press enter to start, press ctrl+c to stop...") conn = psycopg2.connect(database=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT) cursor = conn.cursor() for table, col in rules.items(): print("[...] Working on " + table, end="", flush=True) colstring = ", ".join(["\"" + x.replace("*", "").replace("!", "") + "\"" for x in col]) ph = ", ".join(["%s"] * len(col)) items = sqursor.execute(f"SELECT {colstring} FROM {table};").fetchall() for row in items: newrow = [] for i, cols in enumerate(col): if cols[0] == "*": newrow.append(int_to_bool(row[i])) elif cols[0] == "!": newrow.append(timestamp_conversion(row[i])) else: newrow.append(row[i]) cursor.execute(f"INSERT INTO \"{table}\" ({colstring}) VALUES ({ph})", newrow) print("\r[OK] Done working on " + table) print("Fixing sequences..") seqs = sqursor.execute("SELECT * FROM sqlite_sequence").fetchall() for table, seq in seqs: if seq: cursor.execute(f"SELECT setval(%s, %s, true);", (f"{table}_id_seq", seq)) conn.commit() print("[FIN] Migration is done! Enjoy!")