Revision of Forgejo 14.0.0 SQLite to PostgreSQL

swee revised this gist 7 months ago · d1ab765

1 file changed, 17 insertions, 1 deletion

migrate.py

swee revised this gist 7 months ago · 036d9b2

2 files changed, 459 insertions

lol (file created)

Diff is too large to be shown

migrate.py (file created)

			@@ -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!")

		@@ -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!")