added minimum comment length, added some logging

1 files changed, 247 insertions, 72 deletions

diff --git a/database.py b/database.py
index cbd3308..6eaddbb 100755
--- a/database.py
+++ b/database.py
@@ -1,34 +1,173 @@
+import pymysql
 import sqlite3
+import subprocess
+import subreddit
+import time
+import datetime
+import logging
+import re
 
 class Database:
-    def __init__(self):   
-        self.connection = sqlite3.connect("SmallYTChannelDatabase.db")
-        self.cursor = self.connection.cursor()
+    def __enter__(self):
+        self.__connection = pymysql.connect(
+            **subreddit.CONFIG["mysql"],
+            charset = "utf8mb4",
+            # cursorclass = pymysql.cursors.DictCursor
+        )
+        return self
+
+    def __exit__(self, type, value, traceback):
+        self.__connection.close()
+
+    def migrate(self, sqlitefile):
+        """Function for converting data from an SQLite3 database to
+        MySQL. Will only be called once ever probably. First the data is
+        converted using migrate() global function.
+
+        Args:
+            sqlitefile (str): Path to the SQLite3 file
+        """
+        conn = sqlite3.connect(sqlitefile)
+        cur = conn.cursor()
+        cur.execute("SELECT * FROM blacklist;")
+        blacklist = [i[1] for i in cur.fetchall()]
+        cur.close()
+        conn.close()
+        print("Got blacklist ids...")
+
+        with self.__connection.cursor() as cursor:
+            cursor.execute("DELETE FROM blacklist;")
+            cursor.execute("""
+            ALTER TABLE blacklist CHANGE blacklistID blacklistID int(11) AUTO_INCREMENT;
+            """)
+            cursor.execute("""
+            ALTER TABLE lambdas CHANGE lambdaID lambdaID int(11) AUTO_INCREMENT;
+            """)
+            cursor.execute("""
+            ALTER TABLE lambdas DROP FOREIGN KEY lambdas_FK_0_0;
+            """)
+            cursor.execute("""
+            ALTER TABLE users CHANGE userID userID int(11) AUTO_INCREMENT;
+            """)
+            cursor.execute("""
+            ALTER TABLE lambdas ADD FOREIGN KEY (userID) REFERENCES users(userID);
+            """)
+            cursor.execute("""
+            ALTER TABLE stats CHANGE statID statID int(11) AUTO_INCREMENT;
+            """)
+            print("Finished altering tables...")
+
+            #still cant get executemany to work :/
+            # cursor.executemany("INSERT INTO blacklist (prawID) VALUES (%s);", (blacklist, ))
+            for prawID in blacklist:
+                cursor.execute("INSERT INTO blacklist (prawID) VALUES (%s);", (prawID, ))
+            print("Finised adding blacklist ids...")
+
+            cursor.execute("""
+            CREATE TABLE IF NOT EXISTS log (
+                log_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                pid INT UNSIGNED NULL,
+                datetime_ DATETIME NOT NULL,
+                category VARCHAR(10) NOT NULL DEFAULT 'INFO',
+                data_ VARCHAR(500) NOT NULL,
+                reddit_id VARCHAR(120) NULL
+            );""")
+            print("Added logging table...")
+
+            with open("actions.log", "r") as f:
+                for line in f:
+                    self.append_log(line, commit = False)
+            print("Done.")
+
+        self.__connection.commit()
+
+    def append_log(self, line, permalink = None, commit = True):
+        """Function for adding a log file line to the database. Switched to
+        use the database for logging at the same time as switched to MySQL.
+
+        Args:
+            line (str): a line of a log
+            permalink (str, optional): a url about which the log line converns. Defaults to None.
+            commit (bool, optional): autocommit. Defaults to True.
+        """        
+        def get_date(stri):
+            # strip microseconds
+            stri = stri.split(",")[0]
+            try:
+                return datetime.datetime.strptime(stri, "%Y-%m-%d %H:%M:%S")
+            except ValueError:
+                return datetime.datetime.strptime(stri, "%b %d %Y %H:%M:%S")
+
+        addFlag = False    
+        s = line.split("\t")
+        if len(s) == 3:
+            pid = int(s[0])
+            date = get_date(s[1][1:-1])
+            misc = s[2].rstrip()
+            addFlag = True
+ 
+        elif len(s) == 1:
+            s = s[0].rstrip()
+            result = re.search("\[(.*)\]", s)
+            if result is not None:
+                pid = None
+                date = get_date(result.group(1))
+                misc = s.replace("[%s]" % result.group(1), "")
+                addFlag = True
+
+        if addFlag:
+            if re.search(r"{ERROR", misc) is not None:
+                category = "ERROR"
+            else:
+                category = "INFO"
+
+            with self.__connection.cursor() as cursor:
+                cursor.execute("""
+                INSERT INTO log (pid, datetime_, category, data_, reddit_id) VALUES (
+                    %s, %s, %s, %s, %s
+                );""", (pid, date, category, misc, permalink))
+
+            if commit:
+                self.__connection.commit()
 
     def change_lambda(self, user, changeby):
+        with self.__connection.cursor() as cursor:
         #this will make it go negative. You must check this operation is allowed.
-        self.cursor.execute("UPDATE users SET lambda = ((SELECT lambda FROM users WHERE user_name = ?) + ?) WHERE user_name = ?;", (user, changeby, user))
-        self.connection.commit()
+            cursor.execute("""
+            UPDATE users SET lambda = (
+                (SELECT lambda FROM users WHERE user_name = %s) + %s
+            ) WHERE user_name = %s;
+            """, (user, changeby, user))
+        
+        self.__connection.commit()
 
-    def give_lambda(self, user, link, timestamp = None, op = None):
+    def give_lambda(self, user, link, timestamp = int(time.time()), op = None):
         def give(user, link = None):
-            #check if the user has an entry in the database
-            self.cursor.execute("SELECT userID FROM users WHERE user_name = ?;", (user, ))
-            try:
-                id_ = self.cursor.fetchone()[0]
-            except TypeError:
-                #the user isn't in the database
-                self.cursor.execute("INSERT INTO users (user_name, lambda) VALUES (?, 1);", (user, ))
-                self.connection.commit()
-                if link is not None:
-                    self.cursor.execute("INSERT INTO lambdas (userID, permalink, created) VALUES ((SELECT userID FROM users WHERE user_name = ?), ?, ?);", (user, link, timestamp))
-            else:
-                #update their lambda and add to lambdas
-                self.change_lambda(user, 1)
-                if link is not None:
-                    self.cursor.execute("INSERT INTO lambdas (userID, permalink, created) VALUES (?, ?, ?);", (id_, link, timestamp))
+            with self.__connection.cursor() as cursor:
+                #check if the user has an entry in the database
+                cursor.execute("SELECT userID FROM users WHERE user_name = %s;", (user, ))
+                try:
+                    id_ = cursor.fetchone()[0]
+                except TypeError:
+                    #the user isn't in the database
+                    cursor.execute("""
+                    INSERT INTO users (user_name, lambda) VALUES (%s, 1);
+                    """, (user, ))
+                    if link is not None:
+                        cursor.execute("""
+                        INSERT INTO lambdas (userID, permalink, created) VALUES ((
+                            SELECT userID FROM users WHERE user_name = %s
+                        ), %s, %s);
+                        """, (user, link, timestamp))
+                else:
+                    #update their lambda and add to lambdas
+                    self.change_lambda(user, 1)
+                    if link is not None:
+                        cursor.execute("""
+                        INSERT INTO lambdas (userID, permalink, created) VALUES (%s, %s, %s);
+                        """, (id_, link, timestamp))
         
-            self.connection.commit()
+            self.__connection.commit()
 
         #give one lambda to both the user and the OP
         give(user, link)
@@ -36,79 +175,115 @@ class Database:
             give(op)
 
     def get_lambda(self, user):
-        self.cursor.execute("SELECT lambda FROM users WHERE user_name = ?", (user, ))
-        try:
-            lambda_ = self.cursor.fetchone()[0]
-        except TypeError:
-            #the user isn't in the database, and therefore has no lambda 
-            return 0, []
-        else:
-            self.cursor.execute("SELECT permalink FROM lambdas WHERE userID = (SELECT userID FROM users WHERE user_name = ?);", (user, ))
-            links = [i[0] for i in self.cursor.fetchall()]
-
-            return lambda_, links
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT lambda FROM users WHERE user_name = %s", (user, ))
+            try:
+                lambda_ = cursor.fetchone()[0]
+            except TypeError:
+                #the user isn't in the database, and therefore has no lambda 
+                return 0, []
+            else:
+                cursor.execute("SELECT permalink FROM lambdas WHERE userID = (SELECT userID FROM users WHERE user_name = %s);", (user, ))
+                links = [i[0] for i in cursor.fetchall()]
+
+                return lambda_, links
 
     def link_in_db(self, link):
-        self.cursor.execute("SELECT permalink FROM lambdas;")
-        try:
-            links = [i[0] for i in self.cursor.fetchall()]
-        except TypeError:
-            links = []
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT permalink FROM lambdas WHERE permalink = %s;", (link, ))
+            try:
+                links = [i[0] for i in cursor.fetchall()]
+            except TypeError:
+                links = []
 
-        return link in links
+            return link in links
 
     def add_to_blacklist(self, id):
-        self.cursor.execute("INSERT INTO blacklist (prawID) VALUES (?);", (id, ))
-        self.connection.commit()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("INSERT INTO blacklist (prawID) VALUES (%s);", (id, ))
+        self.__connection.commit()
 
     def id_in_blacklist(self, id):
-        self.cursor.execute("SELECT prawID FROM blacklist;")
-        try:
-            ids = [i[0] for i in self.cursor.fetchall()]
-        except TypeError:
-            ids = []
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT prawID FROM blacklist WHERE prawID = %s;", (id, ))
+            try:
+                ids = [i[0] for i in cursor.fetchall()]
+            except TypeError:
+                ids = []
 
-        return id in ids
+            return id in ids
 
     def get_scores(self):
-        self.cursor.execute("SELECT users.user_name, users.lambda, COUNT(users.user_name) FROM lambdas INNER JOIN users ON users.userID = lambdas.userID GROUP BY users.user_name;")
-        return self.cursor.fetchall()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            SELECT users.user_name, users.lambda, COUNT(users.user_name) 
+            FROM lambdas INNER JOIN users ON users.userID = lambdas.userID 
+            GROUP BY users.user_name;
+            """)
+            return cursor.fetchall()
 
     def update_stats(self):
-        query = """INSERT INTO stats (lambdaCount, helpGiven, uniqueUsers, date) VALUES (
-        (SELECT SUM(lambda) FROM users),
-        (SELECT COUNT(lambdaID) FROM lambdas),
-        (SELECT COUNT(user_name) FROM users),
-        (SELECT strftime('%Y-%m-%d')));"""
-
-        self.cursor.execute(query)
-        self.connection.commit()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            INSERT INTO stats (lambdaCount, helpGiven, uniqueUsers, date) VALUES (
+                (SELECT SUM(lambda) FROM users),
+                (SELECT COUNT(lambdaID) FROM lambdas),
+                (SELECT COUNT(user_name) FROM users),
+            (SELECT DATE_FORMAT(NOW(), "%Y-%m-%d")));
+            """)
+            self.__connection.commit()
 
     def get_stats(self):
-        self.cursor.execute("SELECT * FROM stats;")
-        return self.cursor.fetchall()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT * FROM stats;")
+            return cursor.fetchall()
 
     def user_given_lambda(self, user, permalink):
         links = self.get_lambda(user)[1]
         return permalink in links or permalink.replace("https://www.reddit.com", "") in links
 
     def get_all_lambdas(self):
-        self.cursor.execute("SELECT lambdas.lambdaID, lambdas.permalink, users.user_name, lambdas.created FROM lambdas INNER JOIN users ON lambdas.userID = users.userID;")
-        return self.cursor.fetchall()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            SELECT lambdas.lambdaID, lambdas.permalink, users.user_name, lambdas.created 
+            FROM lambdas INNER JOIN users ON lambdas.userID = users.userID;
+            """)
+            return cursor.fetchall()
 
     def add_date_to_permalink(self, permalink, date):
-        self.cursor.execute("UPDATE lambdas SET created = ? WHERE permalink = ?;", (date, permalink))
-        self.connection.commit()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("UPDATE lambdas SET created = %s WHERE permalink = %s;", (date, permalink))
+        self.__connection.commit()
 
     def get_lambda_leaderboard(self):
-        self.cursor.execute("""
-        SELECT users.user_name, COUNT(lambdas.userID) AS times_helped, users.lambda 
-        FROM lambdas INNER JOIN users ON users.userID = lambdas.userID 
-        WHERE created > (strftime('%s','now') - (60 * 60 * 24 * 30)) 
-        GROUP BY lambdas.userID ORDER BY times_helped DESC LIMIT 10;
-        """)
-        return self.cursor.fetchall()
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            SELECT users.user_name, COUNT(lambdas.userID) AS times_helped, users.lambda 
+            FROM lambdas INNER JOIN users ON users.userID = lambdas.userID 
+            WHERE created > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 30)) 
+            GROUP BY lambdas.userID ORDER BY times_helped DESC LIMIT 10;
+            """)
+            return cursor.fetchall()
+
+def migrate(sqlitefile):
+    subprocess.run([
+        "sqlite3mysql", 
+        "-f", sqlitefile,
+        "-h", subreddit.CONFIG["mysql"]["host"],
+        "-d", subreddit.CONFIG["mysql"]["database"],
+        "-u", subreddit.CONFIG["mysql"]["user"], 
+        "-p", subreddit.CONFIG["mysql"]["passwd"]
+    ])
+    print("Converted table...")
+
+    with Database() as db:
+        db.migrate(sqlitefile)
 
 if __name__ == "__main__":
-    import subreddit
-    print(subreddit.every_day())
\ No newline at end of file
+    #migrate("SmallYTChannelDatabase.db")
+    # with Database() as db:
+    #     #db.give_lambda("floofleberries", "https://www.reddit.com/r/SmallYTChannel/comments/ho5b5p/new_video_advice_would_help_but_even_just_a_watch/")
+    #     print(db.id_in_blacklist("hyy6v0"))
+    print(subreddit.format_monthly_leaderboard())
+
+