Revision of Forgejo 14.0.0 SQLite to PostgreSQL

swee revised this gist 1 day ago. Go to revision

1 file changed, 17 insertions, 1 deletion

migrate.py

			@@ -32,6 +32,17 @@ print("[...] Starting dry run...")
32	32		sqdb = sqlite3.connect(SQLITE_DB)
33	33		sqursor = sqdb.cursor()
34	34
	35	+	for table, col in rules.items():
	36	+	colstring = ", ".join(["\"" + x.replace("*", "").replace("!", "") + "\"" for x in col])
	37	+	items = sqursor.execute(f"SELECT {colstring} FROM {table};").fetchall()
	38	+	for row in items:
	39	+	newrow = []
	40	+	for i, cols in enumerate(col):
	41	+	if cols[0] == "*":
	42	+	newrow.append(int_to_bool(row[i]))
	43	+	elif cols[0] == "!":
	44	+	newrow.append(timestamp_conversion(row[i]))
	45	+
35	46		bad = 0
36	47
37	48		for table, col in rules.items():
			@@ -47,7 +58,6 @@ for table, col in rules.items():
47	58		if bad:
48	59		print(f"[!!!] {bad} tables found with not enough items")
49	60
50		-	print("[OK] Everything is okay!")
51	61		input("Press enter to start, press ctrl+c to stop...")
52	62
53	63		conn = psycopg2.connect(database=DB_NAME,
			@@ -75,5 +85,11 @@ for table, col in rules.items():
75	85		cursor.execute(f"INSERT INTO \"{table}\" ({colstring}) VALUES ({ph})", newrow)
76	86		print("\r[OK] Done working on " + table)
77	87
	88	+	print("Fixing sequences..")
	89	+	seqs = sqursor.execute("SELECT * FROM sqlite_sequence").fetchall()
	90	+	for table, seq in seqs:
	91	+	if seq:
	92	+	cursor.execute(f"SELECT setval(%s, %s, true);", (f"{table}_id_seq", seq))
	93	+
78	94		conn.commit()
79	95		print("[FIN] Migration is done! Enjoy!")

swee revised this gist 1 day ago. Go to revision

2 files changed, 459 insertions

lol(file created)

		@@ -0,0 +1,380 @@
1	+	version
2	+	id, version
3	+
4	+	forgejo_version
5	+	id, version
6	+
7	+	forgejo_sem_ver
8	+	version
9	+
10	+	forgejo_migration
11	+	id, created_unix
12	+
13	+	dbfs_meta
14	+	id, full_path, block_size, file_size, create_timestamp, modify_timestamp
15	+
16	+	dbfs_data
17	+	id, revision, meta_id, blob_offset, blob_size, blob_data
18	+
19	+	email_hash
20	+	hash, email
21	+
22	+	access_token
23	+	id, uid, name, token_hash, token_salt, token_last_eight, scope, created_unix, updated_unix
24	+
25	+	oauth2_application
26	+	id, uid, name, client_id, client_secret, *confidential_client, redirect_uris, created_unix, updated_unix
27	+
28	+	oauth2_authorization_code
29	+	id, grant_id, code, code_challenge, code_challenge_method, redirect_uri, valid_until
30	+
31	+	oauth2_grant
32	+	id, user_id, application_id, counter, scope, nonce, created_unix, updated_unix
33	+
34	+	session
35	+	key, data, expiry
36	+
37	+	login_source
38	+	id, type, name, is_active, is_sync_enabled, cfg, created_unix, updated_unix
39	+
40	+	two_factor
41	+	id, uid, scratch_salt, scratch_hash, last_used_passcode, created_unix, updated_unix, secret
42	+
43	+	webauthn_credential
44	+	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
45	+
46	+	abuse_report
47	+	id, status, reporter_id, content_type, content_id, category, remarks, shadow_copy_id, created_unix, resolved_unix
48	+
49	+	abuse_report_shadow_copy
50	+	id, raw_value, created_unix
51	+
52	+	app_state
53	+	id, revision, content
54	+
55	+	notice
56	+	id, type, description, created_unix
57	+
58	+	system_setting
59	+	id, setting_key, setting_value, version, created, updated
60	+
61	+	hook_task
62	+	id, hook_id, uuid, payload_content, payload_version, event_type, is_delivered, delivered, is_succeed, request_content, response_content
63	+
64	+	webhook
65	+	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
66	+
67	+	federation_host
68	+	id, host_fqdn, software_name, !latest_activity, created, updated, key_id, public_key, host_port, host_schema
69	+
70	+	badge
71	+	id, description, image_url
72	+
73	+	user_badge
74	+	id, badge_id, user_id
75	+
76	+	forgejo_blocked_user
77	+	id, block_id, user_id, created_unix
78	+
79	+	email_address
80	+	id, uid, email, lower_email, is_activated, is_primary
81	+
82	+	external_login_user
83	+	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
84	+
85	+	follow
86	+	id, user_id, follow_id, created_unix
87	+
88	+	user_open_id
89	+	id, uid, uri, *show
90	+
91	+	user_redirect
92	+	id, lower_name, redirect_user_id, created_unix
93	+
94	+	user_setting
95	+	id, user_id, setting_key, setting_value
96	+
97	+	federated_user
98	+	id, user_id, external_id, federation_host_id, key_id, public_key, normalized_original_url, inbox_path
99	+
100	+	federated_user_follower
101	+	id, followed_user_id, following_user_id
102	+
103	+	repo_archive_download_count
104	+	id, repo_id, release_id, type, count
105	+
106	+	repo_archiver
107	+	id, repo_id, type, status, commit_id, created_unix
108	+
109	+	user
110	+	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
111	+
112	+	attachment
113	+	id, uuid, repo_id, issue_id, release_id, uploader_id, comment_id, name, download_count, size, created_unix, external_url
114	+
115	+	language_stat
116	+	id, repo_id, commit_id, *is_primary, language, size, created_unix
117	+
118	+	mirror
119	+	id, repo_id, interval, enable_prune, updated_unix, next_update_unix, lfs_enabled, lfs_endpoint, remote_address
120	+
121	+	push_mirror
122	+	id, repo_id, remote_name, remote_address, *sync_on_commit, interval, created_unix, last_update, last_error, public_key, private_key, branch_filter
123	+
124	+	repo_redirect
125	+	id, owner_id, lower_name, redirect_repo_id
126	+
127	+	release
128	+	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
129	+
130	+	forgejo_repo_flag
131	+	id, repo_id, name
132	+
133	+	repo_indexer_status
134	+	id, repo_id, commit_sha, indexer_type
135	+
136	+	following_repo
137	+	id, repo_id, external_id, federation_host_id, uri
138	+
139	+	repo_unit
140	+	id, repo_id, type, config, created_unix, default_permissions
141	+
142	+	star
143	+	id, uid, repo_id, created_unix
144	+
145	+	topic
146	+	id, name, repo_count, created_unix, updated_unix
147	+
148	+	repo_topic
149	+	repo_id, topic_id
150	+
151	+	upload
152	+	id, uuid, name
153	+
154	+	watch
155	+	id, user_id, repo_id, mode, created_unix, updated_unix
156	+
157	+	action_artifact
158	+	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
159	+
160	+	action_run
161	+	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
162	+
163	+	action_run_index
164	+	group_id, max_index
165	+
166	+	action_run_job
167	+	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
168	+
169	+	action_runner
170	+	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
171	+
172	+	action_runner_token
173	+	id, token, owner_id, repo_id, *is_active, created, updated, deleted
174	+
175	+	action_schedule
176	+	id, title, specs, repo_id, owner_id, workflow_id, trigger_user_id, ref, commit_sha, event, event_payload, content, created, updated, workflow_directory
177	+
178	+	action_schedule_spec
179	+	id, repo_id, schedule_id, next, prev, spec, created, updated
180	+
181	+	action_task
182	+	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
183	+
184	+	action_task_output
185	+	id, task_id, output_key, output_value
186	+
187	+	action_task_step
188	+	id, name, task_id, index, repo_id, status, log_index, log_length, started, stopped, created, updated
189	+
190	+	action_tasks_version
191	+	id, owner_id, repo_id, version, created_unix, updated_unix
192	+
193	+	action_variable
194	+	id, owner_id, repo_id, name, data, created_unix, updated_unix
195	+
196	+	task
197	+	id, doer_id, owner_id, repo_id, type, status, start_time, end_time, payload_content, message, created
198	+
199	+	gpg_key
200	+	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
201	+
202	+	gpg_key_import
203	+	key_id, content
204	+
205	+	public_key
206	+	id, owner_id, name, fingerprint, content, mode, type, login_source_id, created_unix, updated_unix, *verified
207	+
208	+	deploy_key
209	+	id, key_id, repo_id, name, fingerprint, mode, created_unix, updated_unix
210	+
211	+	project_board
212	+	id, title, *default, sorting, color, project_id, creator_id, created_unix, updated_unix
213	+
214	+	project_issue
215	+	id, issue_id, project_id, project_board_id, sorting
216	+
217	+	project
218	+	id, title, description, owner_id, repo_id, creator_id, *is_closed, board_type, card_type, type, created_unix, updated_unix, closed_date_unix
219	+
220	+	pull_auto_merge
221	+	id, pull_id, doer_id, merge_style, message, created_unix, *delete_branch_after_merge
222	+
223	+	review_state
224	+	id, user_id, pull_id, commit_sha, updated_files, updated_unix
225	+
226	+	secret
227	+	id, owner_id, repo_id, name, created_unix, data
228	+
229	+	org_user
230	+	id, uid, org_id, *is_public
231	+
232	+	team
233	+	id, org_id, lower_name, name, description, authorize, num_repos, num_members, includes_all_repositories, can_create_org_repo
234	+
235	+	team_user
236	+	id, org_id, team_id, uid
237	+
238	+	team_repo
239	+	id, org_id, team_id, repo_id
240	+
241	+	team_unit
242	+	id, org_id, team_id, type, access_mode
243	+
244	+	team_invite
245	+	id, token, inviter_id, org_id, team_id, email, created_unix, updated_unix
246	+
247	+	branch
248	+	id, repo_id, name, commit_id, commit_message, pusher_id, *is_deleted, deleted_by_id, deleted_unix, commit_time, created_unix, updated_unix
249	+
250	+	renamed_branch
251	+	id, repo_id, from, to, created_unix
252	+
253	+	commit_status
254	+	id, index, repo_id, state, sha, target_url, description, context_hash, context, creator_id, created_unix, updated_unix
255	+
256	+	commit_status_index
257	+	id, repo_id, sha, max_index
258	+
259	+	commit_status_summary
260	+	id, repo_id, sha, state, target_url
261	+
262	+	lfs_meta_object
263	+	id, oid, size, repository_id, created_unix, updated_unix
264	+
265	+	lfs_lock
266	+	id, repo_id, owner_id, path, !created
267	+
268	+	protected_branch
269	+	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
270	+
271	+	protected_tag
272	+	id, repo_id, name_pattern, allowlist_user_i_ds, allowlist_team_i_ds, created_unix, updated_unix
273	+
274	+	package
275	+	id, owner_id, repo_id, type, name, lower_name, semver_compatible, is_internal
276	+
277	+	package_blob
278	+	id, size, hash_md5, hash_sha1, hash_sha256, hash_sha512, created_unix, hash_blake2b
279	+
280	+	package_blob_upload
281	+	id, bytes_received, hash_state_bytes, created_unix, updated_unix
282	+
283	+	package_cleanup_rule
284	+	id, enabled, owner_id, type, keep_count, keep_pattern, remove_days, remove_pattern, match_full_name, created_unix, updated_unix
285	+
286	+	package_file
287	+	id, version_id, blob_id, name, lower_name, composite_key, *is_lead, created_unix
288	+
289	+	package_property
290	+	id, ref_type, ref_id, name, value
291	+
292	+	package_version
293	+	id, package_id, creator_id, version, lower_version, created_unix, *is_internal, metadata_json, download_count
294	+
295	+	quota_rule
296	+	name, limit, subjects
297	+
298	+	quota_group
299	+	name
300	+
301	+	quota_group_rule_mapping
302	+	id, group_name, rule_name
303	+
304	+	quota_group_mapping
305	+	id, kind, mapped_id, group_name
306	+
307	+	issue_assignees
308	+	id, assignee_id, issue_id
309	+
310	+	comment
311	+	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
312	+
313	+	issue_content_history
314	+	id, poster_id, issue_id, comment_id, edited_unix, content_text, is_first_created, is_deleted
315	+
316	+	issue_dependency
317	+	id, user_id, issue_id, dependency_id, created_unix, updated_unix
318	+
319	+	issue_index
320	+	group_id, max_index
321	+
322	+	issue_user
323	+	id, uid, issue_id, is_read, is_mentioned
324	+
325	+	issue_watch
326	+	id, user_id, issue_id, *is_watching, created_unix, updated_unix
327	+
328	+	label
329	+	id, repo_id, org_id, name, *exclusive, description, color, num_issues, num_closed_issues, created_unix, updated_unix, archived_unix
330	+
331	+	issue_label
332	+	id, issue_id, label_id
333	+
334	+	milestone
335	+	id, repo_id, name, content, *is_closed, num_issues, num_closed_issues, completeness, created_unix, updated_unix, deadline_unix, closed_date_unix
336	+
337	+	reaction
338	+	id, type, issue_id, comment_id, user_id, original_author_id, original_author, created_unix
339	+
340	+	review
341	+	id, type, reviewer_id, reviewer_team_id, original_author, original_author_id, issue_id, content, official, commit_id, stale, *dismissed, created_unix, updated_unix
342	+
343	+	repo_transfer
344	+	id, doer_id, recipient_id, repo_id, team_i_ds, created_unix, updated_unix
345	+
346	+	action
347	+	id, user_id, op_type, act_user_id, repo_id, comment_id, ref_name, *is_private, content, created_unix
348	+
349	+	federated_user_activity
350	+	id, user_id, actor_id, actor_uri, note_content, note_url, original_note, created
351	+
352	+	notification
353	+	id, user_id, repo_id, status, source, issue_id, comment_id, created_unix, updated_unix
354	+
355	+	forgejo_auth_token
356	+	id, uid, lookup_key, hashed_validator, expiry, purpose
357	+
358	+	repository
359	+	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
360	+
361	+	collaboration
362	+	id, repo_id, user_id, mode, created_unix, updated_unix
363	+
364	+	action_user
365	+	id, user_id, repo_id, *trusted_with_pull_requests, last_access
366	+
367	+	access
368	+	id, user_id, repo_id, mode
369	+
370	+	issue
371	+	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
372	+
373	+	pull_request
374	+	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
375	+
376	+	stopwatch
377	+	id, issue_id, user_id, created_unix
378	+
379	+	tracked_time
380	+	id, issue_id, user_id, created_unix, time, *deleted

migrate.py(file created)

		@@ -0,0 +1,79 @@
1	+	import sqlite3
2	+	import psycopg2
3	+	import re
4	+	from datetime import datetime
5	+
6	+	# Change these
7	+	SQLITE_DB = "forgejo.db"
8	+	DB_NAME = "forgejodb"
9	+	DB_USER = "forgejo"
10	+	DB_PASS = "hunter2"
11	+	DB_HOST = "localhost"
12	+	DB_PORT = "5432"
13	+
14	+	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())}
15	+
16	+	print("[OK] Rules loaded.")
17	+
18	+	# Prefix: !
19	+	def timestamp_conversion(ts):
20	+	if not ts:
21	+	return None
22	+	return datetime.strptime(ts, "%Y-%m-%d %H:%M:%S")
23	+
24	+	# Prefix: *
25	+	def int_to_bool(integ):
26	+	return integ == 1
27	+
28	+	# Dry run, verify the thingies all exist
29	+
30	+	print("[...] Starting dry run...")
31	+
32	+	sqdb = sqlite3.connect(SQLITE_DB)
33	+	sqursor = sqdb.cursor()
34	+
35	+	bad = 0
36	+
37	+	for table, col in rules.items():
38	+	sqursor.execute(f"PRAGMA table_info({table});")
39	+	tables_there = sqursor.fetchall()
40	+	if len(col) != len(tables_there):
41	+	extras = [x[1] for x in tables_there]
42	+	for i in col:
43	+	extras.remove(i.replace("*", "").replace("!", ""))
44	+	print("[!!!] " + table + f" extra columns: {extras}")
45	+	bad += 1
46	+
47	+	if bad:
48	+	print(f"[!!!] {bad} tables found with not enough items")
49	+
50	+	print("[OK] Everything is okay!")
51	+	input("Press enter to start, press ctrl+c to stop...")
52	+
53	+	conn = psycopg2.connect(database=DB_NAME,
54	+	user=DB_USER,
55	+	password=DB_PASS,
56	+	host=DB_HOST,
57	+	port=DB_PORT)
58	+
59	+	cursor = conn.cursor()
60	+
61	+	for table, col in rules.items():
62	+	print("[...] Working on " + table, end="", flush=True)
63	+	colstring = ", ".join(["\"" + x.replace("*", "").replace("!", "") + "\"" for x in col])
64	+	ph = ", ".join(["%s"] * len(col))
65	+	items = sqursor.execute(f"SELECT {colstring} FROM {table};").fetchall()
66	+	for row in items:
67	+	newrow = []
68	+	for i, cols in enumerate(col):
69	+	if cols[0] == "*":
70	+	newrow.append(int_to_bool(row[i]))
71	+	elif cols[0] == "!":
72	+	newrow.append(timestamp_conversion(row[i]))
73	+	else:
74	+	newrow.append(row[i])
75	+	cursor.execute(f"INSERT INTO \"{table}\" ({colstring}) VALUES ({ph})", newrow)
76	+	print("\r[OK] Done working on " + table)
77	+
78	+	conn.commit()
79	+	print("[FIN] Migration is done! Enjoy!")

Newer Older