Mercurial > hg > rlgallery-misc

comparison py/rlgalldb.py @ 0:5ba2123d2c20

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Put this project under version control, finally. Scripts for rlgallery.org, using a PostgreSQL backend. The recorder system is in py/, CGI scripts are in web/.
author John "Elwin" Edwards <elwin@sdf.org>
date Wed, 25 Jul 2012 21:59:42 -0700
parents
children def7fecbd437
comparison
equal deleted inserted replaced
-1:000000000000 0:5ba2123d2c20
1 # rlgalldb.py
2 # Module for the Roguelike Gallery, using a postgres database
3
4 import os
5 import sys
6 import time
7 import calendar
8 import re
9 import psycopg2
10 from datetime import datetime, tzinfo, timedelta
11
12 # Configuration
13 logdir = "/var/dgl/var/games/roguelike/"
14 webdir = "/var/www/lighttpd/scoring/"
15 ppagename = webdir + "players/{0}.html"
16 hpagename = webdir + "highscores.html"
17
18 # HTML fragments for templating
19 phead = """<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
20 <html><head>
21 <title>{0}</title>
22 <link rel="stylesheet" href="/scoring/scores.css" type="text/css">
23 </head>
24 """
25
26 ptop = """<body>
27 <h1>Yendor Guild</h1>
28 """
29
30 navtop = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; {0}</div>\n'
31 navscore = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
32 <a href="/scoring/">Scores</a> -&gt; {0}</div>\n'
33 navplayer = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
34 <a href="/scoring/">Scores</a> -&gt; <a href="/scoring/players/">Players</a> \
35 -&gt; {0}</div>'
36
37 pti = '<h2>{0}</h2>\n'
38
39 secthead = '<h3>{0}</h3>\n'
40 tblhead = '<div class="stable">\n'
41 rowstart = '<div class="sentry">\n'
42 rowend = '</div>\n'
43 cell = ' <span class="sdata">{0}</span>\n'
44 hcell = ' <span class="shdata">{0}</span>\n'
45 tblend = '</div>\n'
46 pend = "</body></html>\n"
47
48 # This would be more useful if we had to do translation
49 headerbook = {"etime":"End time", "score":"Score", "name":"Name", "xl":"XL",
50 "fate":"Fate", "rank":"Rank", "game":"Game"}
51 # Queries for the games table
52 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
53 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"
54
55 # A representation of the UTC time zone. They say Py3k can better handle
56 # this madness.
57 class UTC(tzinfo):
58 def utcoffset(self, dt):
59 return timedelta(0)
60 def dst(self, dt):
61 return timedelta(0)
62 def tzname(self, dt):
63 return "UTC"
64 utc = UTC()
65
66 def getconn():
67 "Returns a database connection, or None if the connection fails."
68 try:
69 conn = psycopg2.connect("dbname=rlg")
70 except psycopg2.OperationalError:
71 return None
72 return conn
73
74 def recnameToInt(filename):
75 recre = r"(\d{4})-(\d{2})-(\d{2})\.(\d{2}):(\d{2}):(\d{2})\.ttyrec$"
76 match = re.match(recre, filename)
77 if not match:
78 return None
79 return calendar.timegm([int(val) for val in match.groups()])
80
81 def recnameToTS(filename):
82 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
83 try:
84 dt = datetime.strptime(filename, pattern)
85 dt.replace(tzinfo=utc)
86 return dt
87 except ValueError:
88 return None
89
90 def ttyreclink(text, name, game, gtime):
91 "Returns a link to the ttyrec archivist"
92 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
93 return lstr.format(name, game, gtime, text)
94
95 def playerlink(name):
96 "Returns a link to a player's page"
97 lstr = '<a href="/scoring/players/' + name + '.html">' + name + '</a>'
98 return lstr
99
100 def linktoArchive(entry):
101 "Takes an entry dict and returns a link to the ttyrec archivist."
102 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
103 #linktext = time.strftime("%Y/%m/%d %H:%M:%S", time.gmtime(entry["etime"]))
104 linktext = entry["etstamp"].strftime("%Y/%m/%d %H:%M:%S")
105 return lstr.format(entry["name"], entry["game"].uname, entry["etime"],
106 linktext)
107
108 def maketablerow(cells, isheader=None):
109 "Takes a list of strings and returns a HTML table row with each string \
110 in its own cell. isheader will make them header cells, obviously."
111 if isheader:
112 celler = hcell
113 else:
114 celler = cell
115 rowstr = rowstart
116 for entry in cells:
117 rowstr = rowstr + celler.format(entry)
118 rowstr = rowstr + rowend
119 return rowstr
120
121 def printTable(entries, fields, of):
122 "Takes a list of entry dicts and a list of field strings and writes a \
123 HTML table to of."
124 of.write(tblhead)
125 clist = []
126 for field in fields:
127 if field in headerbook:
128 clist.append(headerbook[field])
129 else:
130 clist.append("{0}".format(field))
131 of.write(maketablerow(clist, True))
132 rnum = 0
133 for i, entry in enumerate(entries):
134 clist = []
135 for field in fields:
136 if field == "rank":
137 clist.append("{0}".format(i + 1))
138 elif field in entry:
139 thing = entry[field]
140 if field == "game":
141 clist.append(thing.name)
142 elif field == "xl" or field == "score": # Numerics
143 clist.append(str(thing))
144 elif field == "name":
145 clist.append(playerlink(thing))
146 elif field == "fate":
147 clist.append(thing)
148 elif field == "etime":
149 clist.append(linktoArchive(entry))
150 else:
151 clist.append("{0}".format(thing))
152 else:
153 clist.append("N/A")
154 of.write(maketablerow(clist))
155 of.write(tblend)
156 return
157
158 def readentries(entfile, entlist):
159 "Reads a list of entries from a file object"
160 while True:
161 nextentry = entfile.readline()
162 if not nextentry:
163 break
164 if nextentry[-1] != '\n': # The line is incomplete
165 break
166 entlist.append(nextentry.split(None, 4))
167 return
168
169 class Game:
170 pass
171
172 class RogueGame(Game):
173 def __init__(self, name, uname, suffix):
174 self.name = name
175 self.uname = uname
176 self.scores = logdir + uname + ".log"
177 self.logspec = ["etime", "score", "name", "xl", "fate"]
178 self.sqltypes = {"etime": "int", "score": "int", "name": "varchar(20)",
179 "xl": "int", "fate": "text", "stime": "int",
180 "ttyrecs": "text ARRAY", "ststamp": "timestamptz",
181 "etstamp": "timestamptz"}
182 self.logdelim = " "
183 # Class variables, used by some methods
184 fields = ["name", "score", "xl", "fate", "etime"]
185 rankfields = ["rank", "score", "name", "xl", "fate", "etime"]
186 pfields = ["score", "xl", "fate", "etime"]
187 def logtoDict(self, entry):
188 "Processes a log entry string, returning a dict."
189 ndict = {"game": self}
190 entrylist = entry.strip().split(self.logdelim, len(self.logspec) - 1)
191 for item, value in zip(self.logspec, entrylist):
192 if self.sqltypes[item] == "int":
193 ndict[item] = int(value)
194 else:
195 ndict[item] = value
196 return ndict
197 def getEntryDicts(self, entfile, entlist):
198 "Reads logfile entries from entfile, interprets them according to the \
199 instructions in self.logspec/logdelim, and adds them as dicts to entlist."
200 while True:
201 nextentry = entfile.readline()
202 if not nextentry:
203 break
204 if nextentry[-1] != '\n':
205 break
206 entlist.append(self.logtoDict(nextentry))
207 return
208 def getRecent(self, n=20):
209 "Gets the n most recent games out of the database, returning a list \
210 of dicts."
211 try:
212 n = int(n)
213 except (ValueError, TypeError):
214 return []
215 if n <= 0:
216 return []
217 dictlist = []
218 conn = psycopg2.connect("dbname=rlg")
219 cur = conn.cursor()
220 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY etstamp DESC \
221 LIMIT %s;".format(self.uname)
222 cur.execute(qstr, [n])
223 for record in cur:
224 # This should be less hardcodish
225 ndict = {"game": self}
226 ndict["etime"] = record[0]
227 ndict["score"] = record[1]
228 ndict["name"] = record[2]
229 ndict["xl"] = record[3]
230 ndict["fate"] = record[4]
231 ndict["ststamp"] = record[5]
232 ndict["etstamp"] = record[6]
233 dictlist.append(ndict)
234 cur.close()
235 conn.close()
236 return dictlist
237 def getHigh(self, n=10, offset=0):
238 "Gets the n highest scores (starting at offset) from the database, \
239 returning a list of dicts."
240 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY score DESC\
241 ".format(self.uname)
242 qvals = []
243 try:
244 n = int(n)
245 except (ValueError, TypeError):
246 return []
247 if n <= 0:
248 return []
249 qstr += " LIMIT %s"
250 qvals.append(n)
251 try:
252 offset = int(offset)
253 except (ValueError, TypeError):
254 return []
255 if n > 0:
256 qstr += " OFFSET %s"
257 qvals.append(offset)
258 qstr += ";"
259 conn = psycopg2.connect("dbname=rlg")
260 cur = conn.cursor()
261 cur.execute(qstr, qvals)
262 dictlist = []
263 for record in cur:
264 ndict = {"game": self}
265 ndict["etime"] = record[0]
266 ndict["score"] = record[1]
267 ndict["name"] = record[2]
268 ndict["xl"] = record[3]
269 ndict["fate"] = record[4]
270 ndict["ststamp"] = record[5]
271 ndict["etstamp"] = record[6]
272 dictlist.append(ndict)
273 cur.close()
274 conn.close()
275 return dictlist
276 def getPlayer(self, player):
277 "Gets all player's games from the database."
278 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM " + self.uname + " WHERE \
279 name = %s;"
280 conn = getconn()
281 if conn == None:
282 return []
283 cur = conn.cursor()
284 entries = []
285 cur.execute(qstr, [player])
286 for record in cur:
287 ndict = {"game": self}
288 ndict["etime"] = record[0]
289 ndict["score"] = record[1]
290 ndict["name"] = record[2]
291 ndict["xl"] = record[3]
292 ndict["fate"] = record[4]
293 ndict["ststamp"] = record[5]
294 ndict["etstamp"] = record[6]
295 entries.append(ndict)
296 cur.close()
297 conn.close()
298 return entries
299 def putIntoDB(self, dictlist, conn):
300 "Add the entries in dictlist to the database through connection conn, \
301 which needs the INSERT privilege."
302 # FIXME this monster needs to be procedurally generated
303 qstr = "INSERT INTO " + self.uname + " (etime, score, name, xl, fate, stime, ttyrecs, ststamp, etstamp) \
304 VALUES (%(etime)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(stime)s, %(ttyrecs)s, %(ststamp)s, %(etstamp)s);"
305 cur = conn.cursor()
306 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ])
307 conn.commit()
308 cur.close()
309 return
310 def postprocess(self, gamelist):
311 names = set([ e["name"] for e in gamelist ])
312 conn = getconn()
313 if conn == None:
314 return []
315 cur = conn.cursor()
316 for nameF in names:
317 # Get all this player's games ordered by time
318 itsEntries = [ entry for entry in gamelist if entry["name"] == nameF ]
319 itsEntries.sort(key=lambda e: e["etime"])
320 # Find the end time of the latest game already in the db
321 tquery = "SELECT etstamp FROM {0} WHERE name = %s ORDER BY etstamp DESC LIMIT 1;".format(self.uname)
322 cur.execute(tquery, [nameF])
323 result = cur.fetchone()
324 if result:
325 prev = calendar.timegm(result[0].utctimetuple())
326 else:
327 prev = 0
328 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
329 allfilekeys = [ (recnameToInt(f), f) for f in os.listdir(ttyrecdir) ]
330 vfilekeys = [ e for e in allfilekeys if e[0] > prev ]
331 vfilekeys.sort(key=lambda e: e[0])
332 # Now determine stime and ttyrecs for each game
333 for i in range(0, len(itsEntries)):
334 if i == 0:
335 lowlim = prev
336 else:
337 lowlim = itsEntries[i-1]["etime"]
338 hilim = itsEntries[i]["etime"]
339 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ]
340 itsEntries[i]["stime"] = recnameToInt(recs[0])
341 itsEntries[i]["ttyrecs"] = recs
342 # While we're at it, replace numeric timestamps with SQL timestamps.
343 itsEntries[i]["ststamp"] = datetime.fromtimestamp(itsEntries[i]["stime"], utc)
344 itsEntries[i]["etstamp"] = datetime.fromtimestamp(itsEntries[i]["etime"], utc)
345 cur.close()
346 conn.close()
347 def loadnew(self):
348 conn = getconn()
349 if conn == None:
350 return []
351 cur = conn.cursor()
352 # Get the previous offset
353 cur.execute(offselstr, [self.uname])
354 offset = cur.fetchone()[0]
355 newlist = []
356 try:
357 scr = open(self.scores)
358 scr.seek(offset)
359 self.getEntryDicts(scr, newlist)
360 except IOError:
361 noffset = offset # Can't read anything, assume no new games
362 else:
363 noffset = scr.tell()
364 scr.close()
365 cur.execute(newoffstr, [noffset, self.uname])
366 # The new players must already be added to the players table.
367 updatenames = set([ e["name"] for e in newlist ])
368 self.postprocess(newlist)
369 self.putIntoDB(newlist, conn)
370 cur.close()
371 conn.close()
372 return updatenames
373 # End RogueGame class definition
374
375 rogue3 = RogueGame("Rogue V3", "rogue3", "r3")
376 rogue4 = RogueGame("Rogue V4", "rogue4", "r4")
377 rogue5 = RogueGame("Rogue V5", "rogue5", "r5")
378 srogue = RogueGame("Super-Rogue", "srogue", "sr")
379
380 gamelist = [rogue3, rogue4, rogue5, srogue]
381
382 def playerpage(pname):
383 "Generate a player's HTML page"
384 # Write the beginning of the page
385 ppagefi = open(ppagename.format(pname), "w")
386 ppagefi.write(phead.format(pname))
387 ppagefi.write(ptop)
388 ppagefi.write(navplayer.format(pname))
389 ppagefi.write(pti.format("Results for " + pname))
390 for game in gamelist:
391 ppagefi.write(secthead.format(game.name))
392 entries = game.getPlayer(pname)
393 if not entries:
394 ppagefi.write("<div>" + pname + " has not yet completed an expedition\
395 in this dungeon.</div>\n")
396 else:
397 printTable(entries, RogueGame.pfields, ppagefi)
398 scoresum = 0
399 for entry in entries:
400 scoresum += int(entry["score"])
401 avgscr = scoresum / len(entries)
402 ppagefi.write("<p>Average score: {0}</p>\n".format(avgscr))
403 # That should settle it. Print the end; then stop.
404 ppagefi.write(pend)
405 ppagefi.close()
406 return
407
408 def highpage():
409 # open the page and print the beginning
410 highpfi = open(hpagename, "w")
411 highpfi.write(phead.format("High Scores"))
412 highpfi.write(ptop)
413 highpfi.write(navscore.format("High Scores"))
414 highpfi.write(pti.format("List of High Scores"))
415 for game in gamelist:
416 highpfi.write(secthead.format(game.name))
417 entries = game.getHigh(40)
418 if not entries:
419 highpfi.write("<div>No one has braved this dungeon yet.</div>\n")
420 else:
421 printTable(entries, game.rankfields, highpfi)
422 # That should finish it.
423 highpfi.write(pend)
424 highpfi.close()
425 return