56e2ce8e2ffa_utf8mb4_charset_and_collation.py 2.97 KB
Newer Older
1
2
3
4
5
6
7
8
9
"""utf8mb4 charset and collation

Revision ID: 56e2ce8e2ffa
Revises: ef39fcd6e1cd
Create Date: 2021-05-17 14:23:00.008479

"""
from alembic import op

10
11
import aurweb.config

12
# revision identifiers, used by Alembic.
13
14
revision = "56e2ce8e2ffa"
down_revision = "ef39fcd6e1cd"
15
16
17
18
branch_labels = None
depends_on = None

# Tables affected by charset/collate change
19
tables = [
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
    ("AccountTypes", "utf8mb4", "utf8mb4_general_ci"),
    ("ApiRateLimit", "utf8mb4", "utf8mb4_general_ci"),
    ("Bans", "utf8mb4", "utf8mb4_general_ci"),
    ("DependencyTypes", "utf8mb4", "utf8mb4_general_ci"),
    ("Groups", "utf8mb4", "utf8mb4_general_ci"),
    ("Licenses", "utf8mb4", "utf8mb4_general_ci"),
    ("OfficialProviders", "utf8mb4", "utf8mb4_bin"),
    ("PackageBases", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageBlacklist", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageComments", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageDepends", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageKeywords", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageRelations", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageRequests", "utf8mb4", "utf8mb4_general_ci"),
    ("PackageSources", "utf8mb4", "utf8mb4_general_ci"),
    ("Packages", "utf8mb4", "utf8mb4_general_ci"),
    ("RelationTypes", "utf8mb4", "utf8mb4_general_ci"),
    ("RequestTypes", "utf8mb4", "utf8mb4_general_ci"),
    ("SSHPubKeys", "utf8mb4", "utf8mb4_bin"),
    ("Sessions", "utf8mb4", "utf8mb4_bin"),
    ("TU_VoteInfo", "utf8mb4", "utf8mb4_general_ci"),
    ("Terms", "utf8mb4", "utf8mb4_general_ci"),
    ("Users", "utf8mb4", "utf8mb4_general_ci"),
43
]
44
45
46

# Indexes affected by charset/collate change
# Map of Unique Indexes key = index_name, value = [table_name, column1, column2]
47
indexes = {"ProviderNameProvides": ["OfficialProviders", "Name", "Provides"]}
48
49
50
51
52

# Source charset/collation, before this migration is run.
src_charset = "utf8"
src_collate = "utf8_general_ci"

53
db_backend = aurweb.config.get("database", "backend")
54
55
56
57
58


def rebuild_unique_indexes_with_str_cols():
    for idx_name in indexes:
        sql = f"""
59
DROP INDEX IF EXISTS {idx_name}
60
61
62
63
ON {indexes.get(idx_name)[0]}
"""
        op.execute(sql)
        sql = f"""
64
65
66
CREATE UNIQUE INDEX {idx_name}
ON {indexes.get(idx_name)[0]}
({indexes.get(idx_name)[1]}, {indexes.get(idx_name)[2]})
67
68
69
70
71
72
73
74
75
76
"""
        op.execute(sql)


def do_all(iterable, fn):
    for element in iterable:
        fn(element)


def upgrade():
77
78
    def op_execute(table_meta):
        table, charset, collate = table_meta
79
        sql = f"""
80
81
ALTER TABLE {table}
CONVERT TO CHARACTER SET {charset}
82
COLLATE {collate}
83
84
85
86
87
88
89
90
"""
        op.execute(sql)

    do_all(tables, op_execute)
    rebuild_unique_indexes_with_str_cols()


def downgrade():
91
92
93
94
95
    if db_backend == "sqlite":
        return None

    def op_execute(table_meta):
        table, charset, collate = table_meta
96
        sql = f"""
97
98
ALTER TABLE {table}
CONVERT TO CHARACTER SET {src_charset}
99
100
101
102
103
104
COLLATE {src_collate}
"""
        op.execute(sql)

    do_all(tables, op_execute)
    rebuild_unique_indexes_with_str_cols()