Mercurial > hg > rlgallery-misc

# rlgalldb.py
# Module for the Roguelike Gallery, using a postgres database

import os
import calendar
import psycopg2
from datetime import datetime, tzinfo, timedelta

# Configuration
logdir = "/var/dgl/var/games/roguelike/"
webdir = "/var/www/lighttpd/scoring/"
ppagename = webdir + "players/{0}.html"
hpagename = webdir + "highscores.html"

# HTML fragments for templating
phead = """<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>{0}</title>
<link rel="stylesheet" href="/scoring/scores.css" type="text/css">
</head>
"""

ptop = """<body>
<h1>Yendor Guild</h1>
"""

navtop = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; {0}</div>\n'
navscore = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
<a href="/scoring/">Scores</a> -&gt; {0}</div>\n'
navplayer = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
<a href="/scoring/">Scores</a> -&gt; <a href="/scoring/players/">Players</a> \
-&gt; {0}</div>'

pti = '<h2>{0}</h2>\n'

secthead = '<h3>{0}</h3>\n'
tblhead = '<div class="stable">\n'
rowstart = '<div class="sentry">\n'
rowend = '</div>\n'
cell = '  <span class="sdata">{0}</span>\n'
hcell = '  <span class="shdata">{0}</span>\n'
tblend = '</div>\n'
pend = "</body></html>\n"

# This would be more useful if we had to do translation
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;"
newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"

# A representation of the UTC time zone.  They say Py3k can better handle
# this madness.
class UTC(tzinfo):
  def utcoffset(self, dt):
    return timedelta(0)
  def dst(self, dt):
    return timedelta(0)
  def tzname(self, dt):
    return "UTC"
utc = UTC()

def getconn():
  "Returns a database connection, or None if the connection fails."
  try:
    conn = psycopg2.connect("dbname=rlg")
  except psycopg2.OperationalError:
    return None
  return conn

def recnameToTS(filename):
  pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
  try:
    dt = datetime.strptime(filename, pattern).replace(tzinfo=utc)
    return dt
  except ValueError:
    return None

def ttyreclink(text, name, game, gtime):
  "Returns a link to the ttyrec archivist"
  lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
  return lstr.format(name, game, gtime, text)

def playerlink(name):
  "Returns a link to a player's page"
  lstr = '<a href="/scoring/players/' + name + '.html">' + name + '</a>'
  return lstr

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 = 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 \
   in its own cell.  isheader will make them header cells, obviously."
  if isheader:
    celler = hcell
  else:
    celler = cell
  rowstr = rowstart
  for entry in cells:
    rowstr = rowstr + celler.format(entry)
  rowstr = rowstr + rowend
  return rowstr

def printTable(entries, fields, of):
  "Takes a list of entry dicts and a list of field strings and writes a \
   HTML table to of."
  of.write(tblhead)
  clist = []
  for field in fields:
    if field in headerbook:
      clist.append(headerbook[field])
    else:
      clist.append("{0}".format(field))
  of.write(maketablerow(clist, True))
  rnum = 0
  for i, entry in enumerate(entries):
    clist = []
    for field in fields:
      if field == "rank":
        clist.append("{0}".format(i + 1))
      elif field in entry:
        thing = entry[field]
        if field == "game":
          clist.append(thing.name)
        elif field == "xl" or field == "score": # Numerics
          clist.append(str(thing))
        elif field == "name":
          clist.append(playerlink(thing))
        elif field == "fate":
          clist.append(thing)
        elif field == "endt":
          clist.append(linktoArchive(entry))
        else:
          clist.append("{0}".format(thing))
      else:
        clist.append("N/A")
    of.write(maketablerow(clist))
  of.write(tblend)
  return

class Game:
  def __init__(self, name, uname):
    pass
  def logtoDict(self, entry):
    "Processes a log entry string, returning a dict."
    ndict = {"game": self}
    entrylist = entry.strip().split(self.logdelim, len(self.logspec) - 1)
    for item, value in zip(self.logspec, entrylist):
      if self.sqltypes[item] == "int":
        ndict[item] = int(value)
      if self.sqltypes[item] == "bool":
        ndict[item] = bool(int(value))
      elif self.sqltypes[item] == "timestamptz":
        ndict[item] = datetime.fromtimestamp(int(value), utc)
      else:
        ndict[item] = value
    return ndict
  def getEntryDicts(self, entfile, entlist):
    "Reads logfile entries from entfile, interprets them according to the \
     instructions in self.logspec/logdelim, and adds them as dicts to entlist."
    while True:
      nextentry = entfile.readline()
      if not nextentry:
        break
      if nextentry[-1] != '\n':
        break
      entlist.append(self.logtoDict(nextentry))
    return
  # TODO how does this work with postprocess?
  def loadnew(self):
    conn = getconn()
    if conn == None:
      return []
    cur = conn.cursor()
    # Get the previous offset
    cur.execute(offselstr, [self.uname])
    offset = cur.fetchone()[0]
    newlist = []
    try:
      scr = open(self.scores)
      scr.seek(offset)
      self.getEntryDicts(scr, newlist)
    except IOError:
      noffset = offset # Can't read anything, assume no new games
    else:
      noffset = scr.tell()
      scr.close()
      cur.execute(newoffstr, [noffset, self.uname])
    # The new players must already be added to the players table.
    updatenames = set([ e["name"] for e in newlist ])
    self.postprocess(newlist)
    self.putIntoDB(newlist, conn)
    cur.close()
    conn.close()
    return updatenames
  def postprocess(self, gamelist):
    "Default postprocessing function: adds start time and ttyrec list"
    names = set([ e["name"] for e in gamelist ])
    conn = getconn()
    if conn == None:
      return []
    cur = conn.cursor()
    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["endt"])
      # Find the end time of the latest game already in the db
      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 = result[0]
      else:
        prev = datetime.fromtimestamp(0, utc);
      ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
      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 startt and ttyrecs for each game
      for i in range(0, len(itsEntries)):
        if i == 0:
          lowlim = prev
        else:
          lowlim = itsEntries[i-1]["endt"]
        hilim = itsEntries[i]["endt"]
        recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ]
        itsEntries[i]["startt"] = recnameToTS(recs[0])
        itsEntries[i]["ttyrecs"] = recs
    cur.close()
    conn.close()
  def putIntoDB(self, dictlist, conn):
    cur = conn.cursor()
    cur.executemany(self.insertq, [ d for d in dictlist if d["game"] == self ])
    conn.commit()
    cur.close()
    return
# End Game class definition

class RogueGame(Game):
  def __init__(self, name, uname, suffix):
    self.name = name
    self.uname = uname
    self.scores = logdir + uname + ".log"
    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 = " "
    colspec = "("
    valspec = "("
    for i, col in enumerate(self.sqltypes.keys()):
      colspec += col
      valspec += "%({0})s".format(col)
      if i == len(self.sqltypes) - 1:
        colspec += ")"
        valspec += ")"
      else:
        colspec += ", "
        valspec += ", "
    self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname,
                    colspec, valspec)
  # Class variables, used by some methods
  fields = ["name", "score", "xl", "fate", "endt"]
  rankfields = ["rank", "score", "name", "xl", "fate", "endt"]
  pfields = ["score", "xl", "fate", "endt"]
  def getRecent(self, n=20):
    "Gets the n most recent games out of the database, returning a list \
     of dicts."
    try:
      n = int(n)
    except (ValueError, TypeError):
      return []
    if n <= 0:
      return []
    dictlist = []
    conn = psycopg2.connect("dbname=rlg")
    cur = conn.cursor()
    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["endt"] = record[0]
      ndict["score"] = record[1]
      ndict["name"] = record[2]
      ndict["xl"] = record[3]
      ndict["fate"] = record[4]
      ndict["startt"] = record[5]
      dictlist.append(ndict)
    cur.close()
    conn.close()
    return dictlist
  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 endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname)
    qvals = []
    try:
      n = int(n)
    except (ValueError, TypeError):
      return []
    if n <= 0:
      return []
    qstr += " LIMIT %s"
    qvals.append(n)
    try:
      offset = int(offset)
    except (ValueError, TypeError):
      return []
    if n > 0:
      qstr += " OFFSET %s"
      qvals.append(offset)
    qstr += ";"
    conn = psycopg2.connect("dbname=rlg")
    cur = conn.cursor()
    cur.execute(qstr, qvals)
    dictlist = []
    for record in cur:
      ndict = {"game": self}
      ndict["endt"] = record[0]
      ndict["score"] = record[1]
      ndict["name"] = record[2]
      ndict["xl"] = record[3]
      ndict["fate"] = record[4]
      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 endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;"
    conn = getconn()
    if conn == None:
      return []
    cur = conn.cursor()
    entries = []
    cur.execute(qstr, [player])
    for record in cur:
      ndict = {"game": self}
      ndict["endt"] = record[0]
      ndict["score"] = record[1]
      ndict["name"] = record[2]
      ndict["xl"] = record[3]
      ndict["fate"] = record[4]
      ndict["startt"] = record[5]
      entries.append(ndict)
    cur.close()
    conn.close()
    return entries
  def putIntoDB(self, dictlist, conn):
    "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 + " (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()
    cur.close()
    return
# End RogueGame class definition

class ARogueGame(Game):
  def __init__(self, name, uname, suffix):
    self.name = name
    self.uname = uname
    self.scores = logdir + uname + ".log"
    self.logspec = ["endt", "score", "name", "xl", "class", "depth",
                    "maxdepth", "quest", "hadquest", "fate"]
    self.sqltypes = {"endt": "timestamptz", "score": "int",
            "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int",
            "maxdepth": "int", "quest": "int", "hadquest": "bool",
            "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"}
    self.logdelim = " "
    colspec = "("
    valspec = "("
    for i, col in enumerate(self.sqltypes.keys()):
      colspec += col
      valspec += "%({0})s".format(col)
      if i == len(self.sqltypes) - 1:
        colspec += ")"
        valspec += ")"
      else:
        colspec += ", "
        valspec += ", "
    self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname,
                    colspec, valspec)
  # Class variables
  fields = ["name", "score", "class", "xl", "fate", "endt"]
  rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"]
  pfields = ["score", "class", "xl", "fate", "endt"]
  def getHigh(self, n=10, offset=0):
    return []
  def getRecent(self, n=20):
    return []
  def getPlayer(self, player):
    return []

rogue3 = RogueGame("Rogue V3", "rogue3", "r3")
rogue4 = RogueGame("Rogue V4", "rogue4", "r4")
rogue5 = RogueGame("Rogue V5", "rogue5", "r5")
srogue = RogueGame("Super-Rogue", "srogue", "sr")
arogue5 = ARogueGame("Advanced Rogue 5", "arogue5", "ar5")

gamelist = [rogue3, rogue4, rogue5, srogue, arogue5]

def playerpage(pname):
  "Generate a player's HTML page"
  # Write the beginning of the page
  ppagefi = open(ppagename.format(pname), "w")
  ppagefi.write(phead.format(pname))
  ppagefi.write(ptop)
  ppagefi.write(navplayer.format(pname))
  ppagefi.write(pti.format("Results for " + pname))
  for game in gamelist:
    ppagefi.write(secthead.format(game.name))
    entries = game.getPlayer(pname)
    if not entries:
      ppagefi.write("<div>" + pname + " has not yet completed an expedition\
        in this dungeon.</div>\n")
    else:
      printTable(entries, RogueGame.pfields, ppagefi)
      scoresum = 0
      for entry in entries:
        scoresum += int(entry["score"])
      avgscr = scoresum / len(entries)
      ppagefi.write("<p>Average score: {0}</p>\n".format(avgscr))
  # That should settle it.  Print the end; then stop.
  ppagefi.write(pend)
  ppagefi.close()
  return

def highpage():
  # open the page and print the beginning
  highpfi = open(hpagename, "w")
  highpfi.write(phead.format("High Scores"))
  highpfi.write(ptop)
  highpfi.write(navscore.format("High Scores"))
  highpfi.write(pti.format("List of High Scores"))
  for game in gamelist:
    highpfi.write(secthead.format(game.name))
    entries = game.getHigh(40)
    if not entries:
      highpfi.write("<div>No one has braved this dungeon yet.</div>\n")
    else:
      printTable(entries, game.rankfields, highpfi)
  # That should finish it.
  highpfi.write(pend)
  highpfi.close()
  return
author	John "Elwin" Edwards <elwin@sdf.org>
date	Mon, 17 Sep 2012 09:14:26 -0700
parents	7f7b3da664d6
children	78580bffc13d