57 lines
1.8 KiB
Python
57 lines
1.8 KiB
Python
|
# Used to migrate word stats from prior to v1.19.0
|
||
|
|
# usage: $ python3 migration/v01.19.00-words.py ~/.bitbot/bot.db
|
||
|
|
|
||
|
|
import argparse
|
||
|
|
parser = argparse.ArgumentParser(description="Migrate pre-v1.19.0 word stats")
|
||
|
|
parser.add_argument("database")
|
||
|
|
args = parser.parse_args()
|
||
|
|
|
||
|
|
import datetime, sqlite3
|
||
|
|
database = sqlite3.connect(args.database)
|
||
|
|
cursor = database.cursor()
|
||
|
|
|
||
|
|
cursor.execute(
|
||
|
|
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='words'")
|
||
|
|
if cursor.fetchone()[0] == 0:
|
||
|
|
cursor.execute("""
|
||
|
|
CREATE TABLE words
|
||
|
|
(user_id INTEGER, channel_id INTEGER, date TEXT, count INTEGER,
|
||
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id),
|
||
|
|
FOREIGN KEY (channel_id) REFERENCES channels(channel_id),
|
||
|
|
PRIMARY KEY (user_id, channel_id, date))""")
|
||
|
|
|
||
|
|
cursor.execute("SELECT user_id, nickname FROM users")
|
||
|
|
users = dict(cursor.fetchall())
|
||
|
|
cursor.execute("SELECT server_id, alias FROM servers")
|
||
|
|
servers = dict(cursor.fetchall())
|
||
|
|
|
||
|
|
channels = {}
|
||
|
|
cursor.execute("SELECT server_id, channel_id, name FROM channels")
|
||
|
|
for server_id, channel_id, name in cursor.fetchall():
|
||
|
|
channels[channel_id] = (server_id, name)
|
||
|
|
print(channels)
|
||
|
|
|
||
|
|
date = (datetime.datetime.now().date()-datetime.timedelta(days=1)
|
||
|
|
).strftime("%Y-%m-%d")
|
||
|
|
|
||
|
|
cursor.execute("""
|
||
|
|
SELECT user_id, channel_id, value FROM user_channel_settings
|
||
|
|
WHERE setting='words'""")
|
||
|
|
|
||
|
|
for user_id, channel_id, count in cursor.fetchall():
|
||
|
|
nickname = users[user_id]
|
||
|
|
server_id, channel_name = channels[channel_id]
|
||
|
|
|
||
|
|
print("[%s] Migrating %s/%s (%s)" %
|
||
|
|
(servers[server_id], channel_name, nickname, count))
|
||
|
|
|
||
|
|
cursor.execute("""
|
||
|
|
INSERT INTO words (user_id, channel_id, date, count)
|
||
|
|
VALUES (?, ?, ?, ?)""", [user_id, channel_id, date, count])
|
||
|
|
|
||
|
|
database.commit()
|
||
|
|
database.close()
|
||
|
|
|
||
|
|
print()
|
||
|
|
print("Migration successful!")
|