rewrite words.py to track words per-day (in it's own table)

1 files changed, 56 insertions, 0 deletions

diff --git a/migration/v01.19.0-words.py b/migration/v01.19.0-words.py
new file mode 100644
index 00000000..d370e45c
--- /dev/null
+++ b/migration/v01.19.0-words.py
@@ -0,0 +1,56 @@
+# 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!")