added files

1 files changed, 86 insertions, 0 deletions

diff --git a/database.py b/database.py
new file mode 100644
index 0000000..f31a338
--- /dev/null
+++ b/database.py
@@ -0,0 +1,86 @@
+import sqlite3
+
+class Database:
+    def __init__(self):   
+        self.connection = sqlite3.connect("SmallYTChannelDatabase.db")
+        self.cursor = self.connection.cursor()
+
+    def change_lambda(self, user, changeby):
+        #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()
+
+    def give_lambda(self, user, link, 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) VALUES ((SELECT userID FROM users WHERE user_name = ?), ?);", (user, link))
+            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) VALUES (?, ?);", (id_, link))
+        
+            self.connection.commit()
+
+        #give one lambda to both the user and the OP
+        give(user, link)
+        if op is not None:
+            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
+
+    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 = []
+
+        return link in links
+
+    def add_to_blacklist(self, id):
+        self.cursor.execute("INSERT INTO blacklist (prawID) VALUES (?);", (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 = []
+
+        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()
+
+
+    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()