social media crossposting tool. 3rd time's the charm
zenfyr.dev
mastodon
misskey
crossposting
bluesky
1import sqlite3
2
3
4def migrate(conn: sqlite3.Connection):
5 cursor = conn.cursor()
6
7 old_posts = cursor.execute("SELECT * FROM posts;").fetchall()
8 old_mappings = cursor.execute("SELECT * FROM mappings;").fetchall()
9
10 _ = cursor.execute("DROP TABLE posts;")
11 _ = cursor.execute("DROP TABLE mappings;")
12
13 _ = cursor.execute("""
14 CREATE TABLE posts (
15 id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
16 user TEXT NOT NULL,
17 service TEXT NOT NULL,
18 identifier TEXT NOT NULL,
19 parent INTEGER NULL REFERENCES posts(id),
20 root INTEGER NULL REFERENCES posts(id),
21 reposted INTEGER NULL REFERENCES posts(id),
22 extra_data TEXT NULL
23 );
24 """)
25
26 _ = cursor.execute("""
27 CREATE TABLE mappings (
28 original INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
29 mapped INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
30 UNIQUE(original, mapped)
31 );
32 """)
33
34 for old_post in old_posts:
35 _ = cursor.execute(
36 """
37 INSERT INTO posts (id, user, service, identifier, parent, root, reposted, extra_data)
38 VALUES (:id, :user_id, :service, :identifier, :parent_id, :root_id, :reposted_id, :extra_data)
39 """,
40 dict(old_post),
41 )
42
43 for mapping in old_mappings:
44 original, mapped = mapping["original_post_id"], mapping["mapped_post_id"]
45 _ = cursor.execute(
46 "INSERT OR IGNORE INTO mappings (original, mapped) VALUES (?, ?)",
47 (original, mapped),
48 )
49 _ = cursor.execute(
50 "INSERT OR IGNORE INTO mappings (original, mapped) VALUES (?, ?)",
51 (mapped, original),
52 )