made logging work

1 files changed, 99 insertions, 66 deletions

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"))