started on MySQL development branch

1 files changed, 167 insertions, 72 deletions

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())
+
+