timb: testing using redis to store a sorted set of post favcounts

4 files changed, 105 insertions, 0 deletions

diff --git a/scripts/hiscores/.month.py.swp b/scripts/hiscores/.month.py.swp
new file mode 100644
index 0000000..4293f41
--- /dev/null
+++ b/scripts/hiscores/.month.py.swp
diff --git a/scripts/hiscores/day.py b/scripts/hiscores/day.py
new file mode 100644
index 0000000..d59ecdf
--- /dev/null
+++ b/scripts/hiscores/day.py
@@ -0,0 +1,35 @@
+# this needs python 3
+
+import re
+import sys
+import postgresql
+from urllib.parse import urlparse
+import redis
+
+db = postgresql.open("pq://postgres:root@localhost/dumpfm")
+db.execute("SET CLIENT_ENCODING to 'UNICODE'")
+
+r = redis.Redis("localhost")
+
+def clear_redis():
+  r.delete("hiscore.day")
+
+def fetch_favs():
+  ps = db.prepare("SELECT user_id, message_id, created_on FROM tags WHERE created_on > (now() - INTERVAL '1 day') AND LOWER(tag) = 'favorite'")
+  rows = ps()
+  return rows
+
+def add_favs_to_redis(favs):
+  for fav in favs:
+    user_id = fav[0]
+    message_id = fav[1]
+    r.zincrby("hiscore.day", message_id, 1)
+
+def rm_low_scores():
+  r.zremrangebyscore("hiscore.day", 0, 5)
+
+if __name__ == "__main__":
+  clear_redis()
+  favs = fetch_favs()
+  add_favs_to_redis(favs)
+  rm_low_scores()
diff --git a/scripts/hiscores/month.py b/scripts/hiscores/month.py
new file mode 100644
index 0000000..616cccc
--- /dev/null
+++ b/scripts/hiscores/month.py
@@ -0,0 +1,35 @@
+# this needs python 3
+
+import re
+import sys
+import postgresql
+from urllib.parse import urlparse
+import redis
+
+db = postgresql.open("pq://postgres:root@localhost/dumpfm")
+db.execute("SET CLIENT_ENCODING to 'UNICODE'")
+
+r = redis.Redis("localhost")
+
+def clear_redis():
+  r.delete("hiscore.day")
+
+def fetch_favs():
+  ps = db.prepare("SELECT user_id, message_id, created_on FROM tags WHERE created_on > (now() - INTERVAL '30 day') AND LOWER(tag) = 'favorite'")
+  rows = ps()
+  return rows
+
+def add_favs_to_redis(favs):
+  for fav in favs:
+    user_id = fav[0]
+    message_id = fav[1]
+    r.zincrby("hiscore.month", message_id, 1)
+
+def rm_low_scores():
+  r.zremrangebyscore("hiscore.month", 0, 5)
+
+if __name__ == "__main__":
+  clear_redis()
+  favs = fetch_favs()
+  add_favs_to_redis(favs)
+  rm_low_scores()
diff --git a/scripts/hiscores/week.py b/scripts/hiscores/week.py
new file mode 100644
index 0000000..3853abd
--- /dev/null
+++ b/scripts/hiscores/week.py
@@ -0,0 +1,35 @@
+# this needs python 3
+
+import re
+import sys
+import postgresql
+from urllib.parse import urlparse
+import redis
+
+db = postgresql.open("pq://postgres:root@localhost/dumpfm")
+db.execute("SET CLIENT_ENCODING to 'UNICODE'")
+
+r = redis.Redis("localhost")
+
+def clear_redis():
+  r.delete("hiscore.day")
+
+def fetch_favs():
+  ps = db.prepare("SELECT user_id, message_id, created_on FROM tags WHERE created_on > (now() - INTERVAL '7 day') AND LOWER(tag) = 'favorite'")
+  rows = ps()
+  return rows
+
+def add_favs_to_redis(favs):
+  for fav in favs:
+    user_id = fav[0]
+    message_id = fav[1]
+    r.zincrby("hiscore.week", message_id, 1)
+
+def rm_low_scores():
+  r.zremrangebyscore("hiscore.week", 0, 5)
+
+if __name__ == "__main__":
+  clear_redis()
+  favs = fetch_favs()
+  add_favs_to_redis(favs)
+  rm_low_scores()