clean-up codes

[otptools/sfmstat.git] / databases.py

diff --git a/databases.py b/databases.py
index 5af5bd0..6f6afea 100755 (executable)
--- a/databases.py
+++ b/databases.py
@@ -5,6 +5,82 @@ import sys
 import getopt
 import os.path
 
+class StoryDB(object):
+    "Story database"
+    DB_FILE = "database/story_dat"
+    def __init__(self):
+        self._con = None
+        self._cur = None
+
+    def find_author(self, sid, writers):
+        cur = self._con.cursor()
+        author = ""
+        cur.execute("""select topic from topics where sid=?""", (sid,))
+        for row in cur:
+            if row[0] in writers:
+                author = row[0]
+        cur.close()
+        return author
+
+    def query(self, topic1, topic2, period_begin, period_end):
+        if topic2:
+            cmd = """select sid, title, date from stories
+                     where date >= ? and date < ? and sid in (
+                       select sid from topics where topic == ? and sid in (
+                         select sid from topics where topic == ? 
+                     )) order by date"""
+            param = (period_begin, period_end, topic1, topic2)
+        elif topic1:
+            cmd = """select sid, title, date from stories
+                     where date >= ? and date < ? and sid in (
+                       select sid from topics where topic == ?
+                     ) order by date"""
+            param = (period_begin, period_end, topic1)
+        else:
+            cmd = """select sid, title, date from stories
+                     where date >= ? and date < ? and sid in (
+                      select sid from topics) order by date"""
+            param = (period_begin, period_end)
+
+        self._cur.execute(cmd, param)
+        return self._cur
+        
+    def result(self):
+        return self._cur
+
+    def __enter__(self):
+        self._con = sqlite3.connect(self.DB_FILE)
+        self._cur = self._con.cursor()
+
+    def __exit__(self, exc_type, exc_value, traceback):
+        if exc_type:
+            self._con = None
+            self._cur = None
+            return False
+        self._cur.close()
+        self._con.close()
+        self._con = None
+        self._cur = None
+        return True
+        
+
+class GALogDB(object):
+    "Google Analytics logdata database"
+    DB_FILE = "database/analytics_dat"
+
+    def get_PV(self, sid):
+        con = sqlite3.connect(self.DB_FILE)
+        cur = con.cursor()
+        param = "/magazine/" + sid + "%"
+        cur.execute("""select * from logdata where url like ?""", (param,))
+        sum = 0
+        for row in cur:
+            sum = sum + row[2]
+        cur.close()
+        con.close()
+        return sum
+
+
 class HatebuDB(object):
     "Hatena Bookmark Counts Database"
     DB_FILE = "database/hatebu_dat"
@@ -63,6 +139,7 @@ class HatebuDB(object):
         con.close()
         return count
         
+# for database initialize
 def main():
     usage = "%s [-c]" % sys.argv[0]
     (optlist, args) = getopt.getopt(sys.argv[1:], "c")