From 8f4c9d81a5c78f5f7fb86c1934ce31abd8290ad7 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Sun, 26 Jul 2020 22:00:04 +0100
Subject: started on MySQL development branch

---
 database.py | 239 ++++++++++++++++++++++++++++++++++++++++++------------------
 1 file changed, 167 insertions(+), 72 deletions(-)

(limited to 'database.py')

diff --git a/database.py b/database.py
index cbd3308..bf3e317 100755
--- a/database.py
+++ b/database.py
@@ -1,34 +1,95 @@
+import pymysql
 import sqlite3
+import subprocess
+import subreddit
+import time
 
 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):
+        conn = sqlite3.connect(sqlitefile)
+        cur = conn.cursor()
+        cur.execute("SELECT * FROM blacklist;")
+        blacklist = [i[1] for i in cur.fetchall()]
+        cur.close()
+        conn.close()
+
+        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;
+            """)
+
+            #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, ))
+        
+        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 +97,113 @@ 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,
+        "-d", "SmallYTChannel",
+        "-u", subreddit.CONFIG["mysql"]["user"], 
+        "-p", subreddit.CONFIG["mysql"]["passwd"]
+    ])
+
+    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.get_lambda_leaderboard())
+
+
-- 
cgit v1.2.3


From e29f94fcdeddfc563b86545fb22a3b8fca77beb3 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Mon, 27 Jul 2020 19:51:36 +0100
Subject: started on switching logging to database

---
 database.py | 128 +++++++++++++++++++++++++++++++++++++++++-------------------
 1 file changed, 87 insertions(+), 41 deletions(-)

(limited to 'database.py')

diff --git a/database.py b/database.py
index bf3e317..b14a25e 100755
--- a/database.py
+++ b/database.py
@@ -3,6 +3,8 @@ import sqlite3
 import subprocess
 import subreddit
 import time
+import datetime
+import re
 
 class Database:
     def __enter__(self):
@@ -17,41 +19,84 @@ class Database:
         self.__connection.close()
 
     def migrate(self, sqlitefile):
-        conn = sqlite3.connect(sqlitefile)
-        cur = conn.cursor()
-        cur.execute("SELECT * FROM blacklist;")
-        blacklist = [i[1] for i in cur.fetchall()]
-        cur.close()
-        conn.close()
+        # 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;
-            """)
+        #     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 NOT NULL,
+            #     datetime_ DATETIME NOT NULL,
+            #     category VARCHAR(10) NOT NULL DEFAULT 'INFO',
+            #     data_ VARCHAR(500) NOT NULL,
+            #     reddit_id VARCHAR(30) NULL
+            # );""")
+            # print("Added logging table...")
+
+            with open("actions.log", "r") as f:
+                for line in f:
+                    self.append_log(line, commit = False)
+            print("Done.")
 
-            #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, ))
-        
         self.__connection.commit()
 
+    def append_log(self, line, permalink = None, commit = True):
+        s = line.split("\t")
+        if len(s) == 3:
+            pid = int(s[0])
+            try:
+                date = datetime.datetime.strptime(s[1][1:-1], "%Y-%m-%d %H:%M:%S")
+            except ValueError:
+                date = datetime.datetime.strptime(s[1][1:-1], "%b %d %Y %H:%M:%S")
+            misc = s[2].rstrip()
+            if re.search(r"{ERROR", misc) is not None:
+                category = "ERROR"
+            else:
+                category = "INFO"
+            
+            if len(misc) > 3:
+                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.
@@ -189,21 +234,22 @@ class Database:
         
 
 def migrate(sqlitefile):
-    subprocess.run([
-        "sqlite3mysql", 
-        "-f", sqlitefile,
-        "-d", "SmallYTChannel",
-        "-u", subreddit.CONFIG["mysql"]["user"], 
-        "-p", subreddit.CONFIG["mysql"]["passwd"]
-    ])
+    # subprocess.run([
+    #     "sqlite3mysql", 
+    #     "-f", sqlitefile,
+    #     "-d", "SmallYTChannel",
+    #     "-u", subreddit.CONFIG["mysql"]["user"], 
+    #     "-p", subreddit.CONFIG["mysql"]["passwd"]
+    # ])
+    print("Converted table...")
 
     with Database() as db:
         db.migrate(sqlitefile)
 
 if __name__ == "__main__":
-    # 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.get_lambda_leaderboard())
+    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.get_lambda_leaderboard())
 
 
-- 
cgit v1.2.3


From 2caf723beae4e1a515e754dcbe5a0ea1cb448d98 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Fri, 31 Jul 2020 16:12:29 +0100
Subject: made logging work

---
 database.py | 165 ++++++++++++++++++++++++++++++++++++------------------------
 1 file changed, 99 insertions(+), 66 deletions(-)

(limited to 'database.py')

diff --git a/database.py b/database.py
index b14a25e..edfff74 100755
--- a/database.py
+++ b/database.py
@@ -4,6 +4,7 @@ import subprocess
 import subreddit
 import time
 import datetime
+import logging
 import re
 
 class Database:
@@ -19,52 +20,59 @@ class Database:
         self.__connection.close()
 
     def migrate(self, sqlitefile):
-        # 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...")
+        """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 NOT NULL,
-            #     datetime_ DATETIME NOT NULL,
-            #     category VARCHAR(10) NOT NULL DEFAULT 'INFO',
-            #     data_ VARCHAR(500) NOT NULL,
-            #     reddit_id VARCHAR(30) NULL
-            # );""")
-            # print("Added logging table...")
+            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(30) NULL
+            );""")
+            print("Added logging table...")
 
             with open("actions.log", "r") as f:
                 for line in f:
@@ -74,28 +82,53 @@ class Database:
         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])
-            try:
-                date = datetime.datetime.strptime(s[1][1:-1], "%Y-%m-%d %H:%M:%S")
-            except ValueError:
-                date = datetime.datetime.strptime(s[1][1:-1], "%b %d %Y %H:%M:%S")
+            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"
-            
-            if len(misc) > 3:
-                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()
+            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:
@@ -231,16 +264,16 @@ class Database:
             GROUP BY lambdas.userID ORDER BY times_helped DESC LIMIT 10;
             """)
             return cursor.fetchall()
-        
 
 def migrate(sqlitefile):
-    # subprocess.run([
-    #     "sqlite3mysql", 
-    #     "-f", sqlitefile,
-    #     "-d", "SmallYTChannel",
-    #     "-u", subreddit.CONFIG["mysql"]["user"], 
-    #     "-p", subreddit.CONFIG["mysql"]["passwd"]
-    # ])
+    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:
@@ -250,6 +283,6 @@ if __name__ == "__main__":
     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.get_lambda_leaderboard())
+    #     print(db.id_in_blacklist("hyy6v0"))
 
 
-- 
cgit v1.2.3


From 4286c4a895548bdaaa190b248b43b1388e630a62 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Fri, 31 Jul 2020 20:02:13 +0100
Subject: fixed a bug with logging

---
 database.py | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'database.py')

diff --git a/database.py b/database.py
index edfff74..2491c08 100755
--- a/database.py
+++ b/database.py
@@ -70,7 +70,7 @@ class Database:
                 datetime_ DATETIME NOT NULL,
                 category VARCHAR(10) NOT NULL DEFAULT 'INFO',
                 data_ VARCHAR(500) NOT NULL,
-                reddit_id VARCHAR(30) NULL
+                reddit_id VARCHAR(120) NULL
             );""")
             print("Added logging table...")
 
-- 
cgit v1.2.3