forked from Budabot/Tyrbot
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathupgrade.py
166 lines (134 loc) · 7.86 KB
/
upgrade.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
from core.db import DB
from core.logger import Logger
from core.registry import Registry
db = Registry.get_instance("db")
logger = Logger("core.upgrade")
def table_info(table_name):
if db.type == DB.MYSQL:
data = db.query("DESCRIBE %s" % table_name)
def normalize_table_info(row):
row.name = row.Field
row.type = row.Type.upper()
return row
return list(map(normalize_table_info, data))
elif db.type == DB.SQLITE:
return db.query("PRAGMA table_info(%s)" % table_name)
else:
raise Exception("Unknown database type '%s'" % db.type)
def table_exists(table_name):
try:
db.query("SELECT * FROM %s LIMIT 1" % table_name)
return True
except Exception:
return False
def column_exists(table_name, column_name):
try:
db.query("SELECT %s FROM %s LIMIT 1" % (column_name, table_name))
return True
except Exception:
return False
def update_version(v):
v += 1
logger.info("Upgrading db to version '%d'" % v)
db.exec("UPDATE db_version SET version = ? WHERE file = 'db_version'", [v])
return v
def get_version():
row = db.query_single("SELECT version FROM db_version WHERE file = 'db_version'")
if row:
return int(row.version)
else:
return 0
def run_upgrades():
version = get_version()
logger.info("Database at version '%d'" % version)
if version == 0:
db.exec("INSERT INTO db_version (file, version, verified) VALUES ('db_version', ?, 1)", [0])
version = update_version(version)
if version == 1:
if table_exists("org_member"):
db.exec("ALTER TABLE org_member ADD COLUMN last_seen INT NOT NULL DEFAULT 0")
version = update_version(version)
if version == 2:
if table_exists("org_member"):
if db.type == DB.MYSQL:
db.exec("ALTER TABLE org_member MODIFY mode VARCHAR(20) NOT NULL")
db.exec("UPDATE org_member SET mode = ? WHERE mode = ?", ["add_manual", "manual"])
db.exec("UPDATE org_member SET mode = ? WHERE mode = ?", ["rem_manual", "ignore"])
db.exec("UPDATE org_member SET mode = ? WHERE mode = ?", ["add_auto", "auto"])
version = update_version(version)
if version == 3:
if table_exists("news"):
db.exec("ALTER TABLE news RENAME TO news_old")
db.exec("CREATE TABLE news (id INT PRIMARY KEY AUTO_INCREMENT, time INT NOT NULL, char_id INT NOT NULL, news TEXT, sticky SMALLINT NOT NULL, deleted SMALLINT NOT NULL)")
db.exec("INSERT INTO news SELECT news_id, time, p.char_id, news, sticky, deleted FROM news_old n LEFT JOIN player p ON n.author = p.name")
db.exec("DROP TABLE news_old")
version = update_version(version)
if version == 4:
if table_exists("news"):
db.exec("ALTER TABLE news RENAME TO news_old")
db.exec("CREATE TABLE news (id INT PRIMARY KEY AUTO_INCREMENT, char_id INT NOT NULL, news TEXT, sticky SMALLINT NOT NULL, created_at INT NOT NULL, deleted_at INT NOT NULL)")
db.exec("INSERT INTO news (id, char_id, news, sticky, created_at, deleted_at) SELECT id, char_id, news, sticky, time, deleted FROM news_old")
db.exec("DROP TABLE news_old")
version = update_version(version)
if version == 5:
if table_exists("command_config"):
db.exec("UPDATE command_config SET access_level = 'org_member' WHERE access_level = 'superadmin' AND command = 'member'")
version = update_version(version)
if version == 6:
if table_exists("command_alias"):
db.exec("DELETE FROM command_alias WHERE command = 'loud'")
version = update_version(version)
if version == 7:
if table_exists("player"):
db.exec("ALTER TABLE player RENAME TO player_old")
db.exec("CREATE TABLE player ( char_id BIGINT PRIMARY KEY, first_name VARCHAR(30) NOT NULL, name VARCHAR(20) NOT NULL, last_name VARCHAR(30) NOT NULL, "
"level SMALLINT NOT NULL, breed VARCHAR(20) NOT NULL, gender VARCHAR(20) NOT NULL, faction VARCHAR(20) NOT NULL, profession VARCHAR(20) NOT NULL, "
"profession_title VARCHAR(50) NOT NULL, ai_rank VARCHAR(20) NOT NULL, ai_level SMALLINT, org_id INT DEFAULT NULL, org_name VARCHAR(255) NOT NULL, "
"org_rank_name VARCHAR(20) NOT NULL, org_rank_id SMALLINT NOT NULL, dimension SMALLINT NOT NULL, head_id INT NOT NULL, pvp_rating SMALLINT NOT NULL, "
"pvp_title VARCHAR(20) NOT NULL, source VARCHAR(50) NOT NULL, last_updated INT NOT NULL )")
db.exec("INSERT INTO player (char_id, first_name, name, last_name, level, breed, gender, faction, profession, profession_title, ai_rank, ai_level, org_id, "
"org_name, org_rank_name, org_rank_id, dimension, head_id, pvp_rating, pvp_title, source, last_updated) SELECT char_id, first_name, name, last_name, "
"level, breed, gender, faction, profession, profession_title, ai_rank, ai_level, org_id, org_name, org_rank_name, org_rank_id, dimension, head_id, "
"pvp_rating, pvp_title, source, last_updated FROM player_old")
db.exec("DROP TABLE player_old")
version = update_version(version)
if version == 8:
if table_exists("roll"):
db.exec("DROP TABLE roll")
version = update_version(version)
if version == 9:
if table_exists("event_config"):
db.exec("ALTER TABLE event_config RENAME TO event_config_old")
db.exec("CREATE TABLE IF NOT EXISTS event_config (event_type VARCHAR(50) NOT NULL, event_sub_type VARCHAR(50) NOT NULL, handler VARCHAR(255) NOT NULL, "
"description VARCHAR(255) NOT NULL, module VARCHAR(50) NOT NULL, enabled SMALLINT NOT NULL, verified SMALLINT NOT NULL, is_hidden SMALLINT NOT NULL)")
db.exec("INSERT INTO event_config SELECT event_type, event_sub_type, handler, description, module, enabled, verified, 0 FROM event_config_old")
db.exec("DROP TABLE event_config_old")
version = update_version(version)
if version == 10:
if table_exists("discord"):
db.exec("ALTER TABLE discord RENAME TO discord_old")
db.exec("CREATE TABLE IF NOT EXISTS discord (channel_id INTEGER(64) NOT NULL UNIQUE, server_name VARCHAR(256) NOT NULL, channel_name VARCHAR(256) NOT NULL, "
"relay_ao SMALLINT NOT NULL DEFAULT 0, relay_dc SMALLINT NOT NULL DEFAULT 0)")
db.exec("INSERT INTO discord SELECT channel_id, server_name, channel_name, relay_ao, relay_dc FROM discord_old")
db.exec("DROP TABLE discord_old")
version = update_version(version)
if version == 11:
if table_exists("discord"):
db.exec("DROP TABLE discord")
version = update_version(version)
if version == 12:
if table_exists("broadcast"):
db.exec("ALTER TABLE broadcast RENAME TO broadcast_old")
db.exec("CREATE TABLE broadcast (char_id INT NOT NULL PRIMARY KEY, alias VARCHAR(50), created_at INT NOT NULL)")
db.exec("INSERT INTO broadcast SELECT char_id, NULL, created_at FROM broadcast_old")
db.exec("DROP TABLE broadcast_old")
version = update_version(version)
if version == 13:
if table_exists("org_member"):
db.exec("ALTER TABLE org_member RENAME TO org_member_old")
db.exec("CREATE TABLE org_member (char_id INT NOT NULL PRIMARY KEY, mode VARCHAR(20) NOT NULL)")
db.exec("INSERT INTO org_member SELECT char_id, mode FROM org_member_old")
db.exec("CREATE TABLE last_seen (char_id INT NOT NULL PRIMARY KEY, dt INT NOT NULL DEFAULT 0)")
db.exec("INSERT INTO last_seen SELECT char_id, last_seen FROM org_member_old")
db.exec("DROP TABLE org_member_old")
version = update_version(version)