Merge branch 'master' of git.eda.gay:eda.gay

1 files changed, 59 insertions, 16 deletions

diff --git a/database.py b/database.py
index 78cf237..1877ab7 100755
--- a/database.py
+++ b/database.py
@@ -3,7 +3,9 @@ from dataclasses import dataclass
 from github import Github
 from lxml import html
 import configparser
+import curiouscat
 import threading
+import operator
 import datetime
 import requests
 import twython
@@ -225,7 +227,9 @@ class Database:
         threading.Thread(target = update_cache).start()
         out = {}
         with self.__connection.cursor() as cursor:
-            cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL ORDER BY tweeted_at DESC;")
+            # cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL ORDER BY tweeted_at DESC;")
+            # attempt to ignore curiouscat automatic tweets by comparing with the q&a table
+            cursor.execute("SELECT tweet_id, tweeted_at, tweet FROM diary WHERE replying_to IS NULL AND tweet_id NOT IN (SELECT tweet_id FROM diary INNER JOIN qnas ON SUBSTRING(tweet, 1, 16) = SUBSTRING(question, 1, 16)) ORDER BY tweeted_at DESC;")
             for tweet_id, tweeted_at, tweet_text in cursor.fetchall():
                 # print(tweet_id, tweeted_at, tweet_text)
                 out[tweeted_at] = [{
@@ -304,9 +308,45 @@ class Database:
 
         self.__connection.commit()
 
+    def get_curiouscat_username(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT link FROM headerLinks WHERE name = 'curiouscat';")
+        return urlparse(cursor.fetchone()[0]).path.split("/")[1]
+
+    def append_curiouscat_qnas(self, qnas):
+        with self.__connection.cursor() as cursor:
+            for qna in qnas:
+                cursor.execute("SELECT curiouscat_id FROM qnas WHERE curiouscat_id = %s;", (qna["id"], ))
+                if cursor.fetchone() is None:
+
+                    cursor.execute("INSERT INTO `qnas` VALUES (%s, %s, %s, %s, %s);", (
+                        qna["id"], qna["link"], qna["datetime"], qna["question"], qna["answer"]
+                    ))
+                    print("Appended question with timestamp %s" % datetime.datetime.fromtimestamp(qna["id"]).isoformat())
+
+                else:
+                    print("Skipped question with timestamp %s" % datetime.datetime.fromtimestamp(qna["id"]).isoformat())
+        self.__connection.commit()
+
+    def get_biggest_curiouscat_timestamp(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT MAX(`timestamp`) FROM `qnas`;")
+            return cursor.fetchone()[0]
+
+    def get_curiouscat_qnas(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT * FROM qnas;")
+            return sorted(cursor.fetchall(), key = operator.itemgetter(2), reverse = True)
+
 def update_cache():
     # print("updating cache...")
     with Database() as db:
+        db.append_curiouscat_qnas(
+            curiouscat.get_all_curiouscat_qnas_before(
+                db.get_curiouscat_username(), 
+                db.get_biggest_curiouscat_timestamp()
+            )
+        )
         db.fetch_diary()
         db.update_twitter_cache(request_recent_tweets(10000))
         # print("Done updating twitter cache...")
@@ -339,23 +379,26 @@ def request_recent_commits(since = datetime.datetime.now() - datetime.timedelta(
     out = []
     for repo in g.get_user().get_repos():
         # print(repo.name, list(repo.get_branches()))
-        for commit in repo.get_commits(since = since):
-            out.append({
-                "repo": repo.name,
-                "message": commit.commit.message,
-                "url": commit.html_url,
-                "datetime": commit.commit.author.date,
-                "stats": {
-                    "additions": commit.stats.additions,
-                    "deletions": commit.stats.deletions,
-                    "total": commit.stats.total
-                }
-            })
+        try:
+            for commit in repo.get_commits(since = since):
+                out.append({
+                    "repo": repo.name,
+                    "message": commit.commit.message,
+                    "url": commit.html_url,
+                    "datetime": commit.commit.author.date,
+                    "stats": {
+                        "additions": commit.stats.additions,
+                        "deletions": commit.stats.deletions,
+                        "total": commit.stats.total
+                    }
+                })
+        except Exception as e:
+            print(e)
+
     return sorted(out, key = lambda a: a["datetime"], reverse = True) 
 
 
 if __name__ == "__main__":
-    import app
+    # print(request_recent_commits())
     with Database() as db:
-        print(app.get_sidebar_img(db))
-        # print(db.get_sidebar_images())
+        print(db.get_curiouscat_qnas())