135 lines
3.4 KiB
Python
135 lines
3.4 KiB
Python
import aiosqlite
|
|
import logging
|
|
|
|
migrations = [
|
|
"""
|
|
CREATE TABLE deleted_items (
|
|
id INTEGER PRIMARY KEY,
|
|
timestamp INTEGER NOT NULL,
|
|
item TEXT NOT NULL
|
|
);
|
|
""",
|
|
"""
|
|
CREATE INDEX deleted_items_timestamps ON deleted_items(timestamp);
|
|
""",
|
|
"""
|
|
CREATE TABLE reminders (
|
|
id INTEGER PRIMARY KEY,
|
|
remind_timestamp INTEGER NOT NULL,
|
|
created_timestamp INTEGER NOT NULL,
|
|
reminder TEXT NOT NULL,
|
|
expired INTEGER NOT NULL,
|
|
extra TEXT NOT NULL
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE telephone_config (
|
|
id TEXT PRIMARY KEY,
|
|
guild_id INTEGER NOT NULL,
|
|
channel_id INTEGER NOT NULL UNIQUE,
|
|
webhook TEXT
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE calls (
|
|
from_id TEXT NOT NULL REFERENCES telephone_config(id) UNIQUE,
|
|
to_id TEXT NOT NULL REFERENCES telephone_config(id),
|
|
start_time INTEGER NOT NULL
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE guild_config (
|
|
id INTEGER PRIMARY KEY,
|
|
achievement_messages INTEGER
|
|
);
|
|
|
|
CREATE TABLE user_config (
|
|
id INTEGER PRIMARY KEY,
|
|
achievement_tracking_enabled INTEGER
|
|
);
|
|
|
|
CREATE TABLE stats (
|
|
user_id INTEGER NOT NULL REFERENCES user_config (id),
|
|
stat TEXT NOT NULL COLLATE NOCASE,
|
|
value BLOB NOT NULL,
|
|
UNIQUE (user_id, stat)
|
|
);
|
|
|
|
CREATE TABLE achievements (
|
|
user_id INTEGER NOT NULL REFERENCES user_config (id),
|
|
achievement TEXT NOT NULL,
|
|
achieved_time INTEGER NOT NULL,
|
|
UNIQUE (user_id, achievement)
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE assets (
|
|
identifier TEXT PRIMARY KEY,
|
|
url TEXT NOT NULL
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE user_data (
|
|
user_id INTEGER NOT NULL,
|
|
guild_id INTEGER,
|
|
key TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
UNIQUE (user_id, guild_id, key)
|
|
);
|
|
""",
|
|
"""
|
|
CREATE TABLE links (
|
|
to_type TEXT NOT NULL,
|
|
to_id BLOB NOT NULL,
|
|
from_type TEXT NOT NULL,
|
|
from_id BLOB NOT NULL,
|
|
established_at INTEGER NOT NULL,
|
|
UNIQUE (to_type, to_id, from_type, from_id)
|
|
);
|
|
CREATE TABLE discord_webhooks (
|
|
channel_id INTEGER PRIMARY KEY,
|
|
webhook TEXT NOT NULL
|
|
);
|
|
""",
|
|
"""
|
|
UPDATE user_data SET guild_id = '_global' WHERE rowid IN
|
|
(SELECT
|
|
(SELECT rowid FROM user_data d WHERE d.key = user_data.key AND d.user_id = user_data.user_id ORDER BY rowid DESC LIMIT 1)
|
|
FROM user_data WHERE guild_id IS NULL GROUP BY user_id, key);
|
|
DELETE FROM user_data WHERE guild_id IS NULL;
|
|
""",
|
|
"""
|
|
ALTER TABLE links ADD COLUMN cause TEXT;
|
|
""",
|
|
"""
|
|
ALTER TABLE telephone_config ADD COLUMN disabled INTEGER;
|
|
"""
|
|
]
|
|
|
|
async def execute_fetchone(self, sql, params=None):
|
|
if params == None: params = ()
|
|
return await self._execute(self._fetchone, sql, params)
|
|
|
|
def _fetchone(self, sql, params):
|
|
cursor = self._conn.execute(sql, params)
|
|
return cursor.fetchone()
|
|
|
|
async def init(db_path):
|
|
db = await aiosqlite.connect(db_path)
|
|
await db.execute("PRAGMA foreign_keys = ON")
|
|
|
|
db.row_factory = aiosqlite.Row
|
|
aiosqlite.Connection._fetchone = _fetchone
|
|
aiosqlite.Connection.execute_fetchone = execute_fetchone
|
|
|
|
version = (await db.execute_fetchone("PRAGMA user_version"))[0]
|
|
for i in range(version, len(migrations)):
|
|
await db.executescript(migrations[i])
|
|
# Normally interpolating like this would be a terrible idea because of SQL injection.
|
|
# However, in this case there is not an obvious alternative (the parameter-based way apparently doesn't work)
|
|
# and i + 1 will always be an integer anyway
|
|
await db.execute(f"PRAGMA user_version = {i + 1}")
|
|
await db.commit()
|
|
logging.info(f"Migrated DB to schema {i + 1}")
|
|
|
|
return db |