changeset 1:def7fecbd437

Switch to SQL timestamps. Use SQL timestamps whenever possible, and don't store UNIX timestamps in the database at all.
author John "Elwin" Edwards <elwin@sdf.org>
date Wed, 25 Jul 2012 23:05:12 -0700
parents 5ba2123d2c20
children 8f49df4074d7
files py/rlgalldb.py
diffstat 1 files changed, 35 insertions(+), 44 deletions(-) [+]
line wrap: on
line diff
--- a/py/rlgalldb.py	Wed Jul 25 21:59:42 2012 -0700
+++ b/py/rlgalldb.py	Wed Jul 25 23:05:12 2012 -0700
@@ -46,7 +46,7 @@
 pend = "</body></html>\n"
 
 # This would be more useful if we had to do translation
-headerbook = {"etime":"End time", "score":"Score", "name":"Name", "xl":"XL", 
+headerbook = {"endt":"End time", "score":"Score", "name":"Name", "xl":"XL", 
               "fate":"Fate", "rank":"Rank", "game":"Game"}
 # Queries for the games table
 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
@@ -81,8 +81,7 @@
 def recnameToTS(filename):
   pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
   try:
-    dt = datetime.strptime(filename, pattern)
-    dt.replace(tzinfo=utc)
+    dt = datetime.strptime(filename, pattern).replace(tzinfo=utc)
     return dt
   except ValueError:
     return None
@@ -100,10 +99,9 @@
 def linktoArchive(entry):
   "Takes an entry dict and returns a link to the ttyrec archivist."
   lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
-  #linktext = time.strftime("%Y/%m/%d %H:%M:%S", time.gmtime(entry["etime"]))
-  linktext = entry["etstamp"].strftime("%Y/%m/%d %H:%M:%S")
-  return lstr.format(entry["name"], entry["game"].uname, entry["etime"], 
-                     linktext)
+  linktext = entry["endt"].strftime("%Y/%m/%d %H:%M:%S")
+  stamp = calendar.timegm(entry["endt"].utctimetuple())
+  return lstr.format(entry["name"], entry["game"].uname, stamp, linktext)
 
 def maketablerow(cells, isheader=None):
   "Takes a list of strings and returns a HTML table row with each string \
@@ -145,7 +143,7 @@
           clist.append(playerlink(thing))
         elif field == "fate":
           clist.append(thing)
-        elif field == "etime":
+        elif field == "endt":
           clist.append(linktoArchive(entry))
         else:
           clist.append("{0}".format(thing))
@@ -174,16 +172,15 @@
     self.name = name
     self.uname = uname
     self.scores = logdir + uname + ".log"
-    self.logspec = ["etime", "score", "name", "xl", "fate"]
-    self.sqltypes = {"etime": "int", "score": "int", "name": "varchar(20)", 
-                     "xl": "int", "fate": "text", "stime": "int",
-                     "ttyrecs": "text ARRAY", "ststamp": "timestamptz",
-                     "etstamp": "timestamptz"}
+    self.logspec = ["endt", "score", "name", "xl", "fate"]
+    self.sqltypes = {"endt": "timestamptz", "score": "int", 
+            "name": "varchar(20)", "xl": "int", "fate": "text",
+            "ttyrecs": "text ARRAY", "startt": "timestamptz"}
     self.logdelim = " "
   # Class variables, used by some methods
-  fields = ["name", "score", "xl", "fate", "etime"]
-  rankfields = ["rank", "score", "name", "xl", "fate", "etime"]
-  pfields = ["score", "xl", "fate", "etime"]
+  fields = ["name", "score", "xl", "fate", "endt"]
+  rankfields = ["rank", "score", "name", "xl", "fate", "endt"]
+  pfields = ["score", "xl", "fate", "endt"]
   def logtoDict(self, entry):
     "Processes a log entry string, returning a dict."
     ndict = {"game": self}
@@ -191,6 +188,8 @@
     for item, value in zip(self.logspec, entrylist):
       if self.sqltypes[item] == "int":
         ndict[item] = int(value)
+      elif self.sqltypes[item] == "timestamptz":
+        ndict[item] = datetime.fromtimestamp(int(value), utc)
       else:
         ndict[item] = value
     return ndict
@@ -217,19 +216,18 @@
     dictlist = []
     conn = psycopg2.connect("dbname=rlg")
     cur = conn.cursor()
-    qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY etstamp DESC \
+    qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY endt DESC \
             LIMIT %s;".format(self.uname)
     cur.execute(qstr, [n])
     for record in cur:
       # This should be less hardcodish
       ndict = {"game": self}
-      ndict["etime"] = record[0]
+      ndict["endt"] = record[0]
       ndict["score"] = record[1]
       ndict["name"] = record[2]
       ndict["xl"] = record[3]
       ndict["fate"] = record[4]
-      ndict["ststamp"] = record[5]
-      ndict["etstamp"] = record[6]
+      ndict["startt"] = record[5]
       dictlist.append(ndict)
     cur.close()
     conn.close()
@@ -237,8 +235,7 @@
   def getHigh(self, n=10, offset=0):
     "Gets the n highest scores (starting at offset) from the database, \
      returning a list of dicts."
-    qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY score DESC\
-           ".format(self.uname)
+    qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname)
     qvals = []
     try:
       n = int(n)
@@ -262,21 +259,19 @@
     dictlist = []
     for record in cur:
       ndict = {"game": self}
-      ndict["etime"] = record[0]
+      ndict["endt"] = record[0]
       ndict["score"] = record[1]
       ndict["name"] = record[2]
       ndict["xl"] = record[3]
       ndict["fate"] = record[4]
-      ndict["ststamp"] = record[5]
-      ndict["etstamp"] = record[6]
+      ndict["startt"] = record[5]
       dictlist.append(ndict)
     cur.close()
     conn.close()
     return dictlist
   def getPlayer(self, player):
     "Gets all player's games from the database."
-    qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM " + self.uname + " WHERE \
-            name = %s;"
+    qstr = "SELECT endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;"
     conn = getconn()
     if conn == None:
       return []
@@ -285,13 +280,12 @@
     cur.execute(qstr, [player])
     for record in cur:
       ndict = {"game": self}
-      ndict["etime"] = record[0]
+      ndict["endt"] = record[0]
       ndict["score"] = record[1]
       ndict["name"] = record[2]
       ndict["xl"] = record[3]
       ndict["fate"] = record[4]
-      ndict["ststamp"] = record[5]
-      ndict["etstamp"] = record[6]
+      ndict["startt"] = record[5]
       entries.append(ndict)
     cur.close()
     conn.close()
@@ -300,8 +294,8 @@
     "Add the entries in dictlist to the database through connection conn, \
      which needs the INSERT privilege."
     # FIXME this monster needs to be procedurally generated
-    qstr = "INSERT INTO " + self.uname + " (etime, score, name, xl, fate, stime, ttyrecs, ststamp, etstamp) \
-            VALUES (%(etime)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(stime)s, %(ttyrecs)s, %(ststamp)s, %(etstamp)s);"
+    qstr = "INSERT INTO " + self.uname + " (endt, score, name, xl, fate, ttyrecs, startt) \
+            VALUES (%(endt)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(ttyrecs)s, %(startt)s);"
     cur = conn.cursor()
     cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ])
     conn.commit()
@@ -316,32 +310,29 @@
     for nameF in names:
       # Get all this player's games ordered by time
       itsEntries = [ entry for entry in gamelist if entry["name"] == nameF ]
-      itsEntries.sort(key=lambda e: e["etime"])
+      itsEntries.sort(key=lambda e: e["endt"])
       # Find the end time of the latest game already in the db
-      tquery = "SELECT etstamp FROM {0} WHERE name = %s ORDER BY etstamp DESC LIMIT 1;".format(self.uname)
+      tquery = "SELECT endt FROM {0} WHERE name = %s ORDER BY endt DESC LIMIT 1;".format(self.uname)
       cur.execute(tquery, [nameF])
       result = cur.fetchone()
       if result:
-        prev = calendar.timegm(result[0].utctimetuple())
+        prev = result[0]
       else:
-        prev = 0
+        prev = datetime.fromtimestamp(0, utc);
       ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
-      allfilekeys = [ (recnameToInt(f), f) for f in os.listdir(ttyrecdir) ]
+      allfilekeys = [ (recnameToTS(f), f) for f in os.listdir(ttyrecdir) ]
       vfilekeys = [ e for e in allfilekeys if e[0] > prev ]
       vfilekeys.sort(key=lambda e: e[0])
-      # Now determine stime and ttyrecs for each game
+      # Now determine startt and ttyrecs for each game
       for i in range(0, len(itsEntries)):
         if i == 0:
           lowlim = prev
         else:
-          lowlim = itsEntries[i-1]["etime"]
-        hilim = itsEntries[i]["etime"]
+          lowlim = itsEntries[i-1]["endt"]
+        hilim = itsEntries[i]["endt"]
         recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ]
-        itsEntries[i]["stime"] = recnameToInt(recs[0])
+        itsEntries[i]["startt"] = recnameToTS(recs[0])
         itsEntries[i]["ttyrecs"] = recs
-        # While we're at it, replace numeric timestamps with SQL timestamps.
-        itsEntries[i]["ststamp"] = datetime.fromtimestamp(itsEntries[i]["stime"], utc)
-        itsEntries[i]["etstamp"] = datetime.fromtimestamp(itsEntries[i]["etime"], utc)
     cur.close()
     conn.close()
   def loadnew(self):