Mercurial > hg > rlgallery-misc

comparison py/rlgall.py @ 33:25843238434a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Change the Python module's name back to rlgall. It is no longer an experimental variant. Using a database as a backend is a settled feature.
author John "Elwin" Edwards
date Thu, 02 Jan 2014 13:09:48 -0500
parents py/rlgalldb.py@7303535b5a5d
children 86b616d88020
comparison
equal deleted inserted replaced
32:05a4afbe6299 33:25843238434a
1 # rlgall.py
2 # Module for the Roguelike Gallery, using a postgres database
3 # Requires Python 3.3
4
5 import os
6 import psycopg2
7 from datetime import datetime
8 import pytz
9
10 # Configuration
11 logdir = "/var/dgl/var/games/roguelike/"
12 webdir = "/var/www/lighttpd/scoring/"
13 ppagename = webdir + "players/{0}.html"
14 hpagename = webdir + "highscores.html"
15
16 # HTML fragments for templating
17 phead = """<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
18 <html><head>
19 <title>{0}</title>
20 <link rel="stylesheet" href="/scoring/scores.css" type="text/css">
21 </head>
22 """
23
24 ptop = """<body>
25 <h1>Yendor Guild</h1>
26 """
27
28 navtop = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; {0}</div>\n'
29 navscore = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
30 <a href="/scoring/">Scores</a> -&gt; {0}</div>\n'
31 navplayer = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
32 <a href="/scoring/">Scores</a> -&gt; <a href="/scoring/players/">Players</a> \
33 -&gt; {0}</div>'
34
35 pti = '<h2>{0}</h2>\n'
36
37 secthead = '<h3>{0}</h3>\n'
38 tblhead = '<div class="stable">\n'
39 rowstart = '<div class="sentry">\n'
40 rowend = '</div>\n'
41 cell = ' <span class="sdata">{0}</span>\n'
42 rcell = ' <span class="sdatar">{0}</span>\n'
43 hcell = ' <span class="shdata">{0}</span>\n'
44 tblend = '</div>\n'
45 pend = "</body></html>\n"
46
47 # This would be more useful if we had to do translation
48 headerbook = {"endt":"End time", "score":"Score", "name":"Name", "xl":"XL",
49 "fate":"Fate", "rank":"Rank", "game":"Game", "class": "Class"}
50 # Queries for the games table
51 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
52 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"
53
54 def getconn():
55 "Returns a database connection, or None if the connection fails."
56 try:
57 conn = psycopg2.connect("dbname=rlg")
58 except psycopg2.OperationalError:
59 return None
60 return conn
61
62 def recnameToTS(filename):
63 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
64 try:
65 dt = datetime.strptime(filename, pattern).replace(tzinfo=pytz.utc)
66 return dt
67 except ValueError:
68 return None
69
70 def ttyreclink(text, name, game, gtime):
71 "Returns a link to the ttyrec archivist"
72 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
73 return lstr.format(name, game, gtime, text)
74
75 def playerlink(name):
76 "Returns a link to a player's page"
77 lstr = '<a href="/scoring/players/' + name + '.html">' + name + '</a>'
78 return lstr
79
80 def linktoArchive(entry):
81 "Takes an entry dict and returns a link to the ttyrec archivist."
82 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
83 linktext = entry["endt"].strftime("%Y/%m/%d %H:%M:%S")
84 stamp = int(entry["endt"].timestamp())
85 return lstr.format(entry["name"], entry["game"].uname, stamp, linktext)
86
87 def maketablerow(cells, isheader=None):
88 "Takes a list of strings and returns a HTML table row with each string \
89 in its own cell. isheader will make them header cells, obviously."
90 if isheader:
91 celler = hcell
92 else:
93 celler = cell
94 rowstr = rowstart
95 for entry in cells:
96 rowstr = rowstr + celler.format(entry)
97 rowstr = rowstr + rowend
98 return rowstr
99
100 def printTable(entries, fields, of):
101 "Takes a list of entry dicts and a list of field strings and writes a \
102 HTML table to of."
103 of.write(tblhead)
104 clist = []
105 for field in fields:
106 if field in headerbook:
107 clist.append(headerbook[field])
108 else:
109 clist.append("{0}".format(field))
110 of.write(maketablerow(clist, True))
111 rnum = 0
112 for i, entry in enumerate(entries):
113 clist = []
114 for field in fields:
115 if field == "rank":
116 clist.append(("{0}".format(i + 1), rcell))
117 elif field in entry:
118 thing = entry[field]
119 if field == "game":
120 clist.append((thing.name, cell))
121 elif field == "xl" or field == "score": # Numerics
122 clist.append((str(thing), rcell))
123 elif field == "name":
124 clist.append((playerlink(thing), cell))
125 elif field == "fate":
126 clist.append((thing, cell))
127 elif field == "endt":
128 clist.append((linktoArchive(entry), cell))
129 else:
130 clist.append(("{0}".format(thing), cell))
131 else:
132 clist.append(("N/A", cell))
133 rowstr = rowstart + "".join([ t.format(s) for (s, t) in clist ]) + rowend
134 of.write(rowstr)
135 of.write(tblend)
136 return
137
138 class Game:
139 def __init__(self, name, uname):
140 pass
141 def logtoDict(self, entry):
142 "Processes a log entry string, returning a dict."
143 ndict = {"game": self}
144 entrylist = entry.strip().split(self.logdelim, len(self.logspec) - 1)
145 for item, value in zip(self.logspec, entrylist):
146 if self.sqltypes[item] == "int":
147 ndict[item] = int(value)
148 if self.sqltypes[item] == "bool":
149 ndict[item] = bool(int(value))
150 elif self.sqltypes[item] == "timestamptz":
151 ndict[item] = datetime.fromtimestamp(int(value), pytz.utc)
152 else:
153 ndict[item] = value
154 return ndict
155 def getEntryDicts(self, entfile, entlist):
156 "Reads logfile entries from entfile, interprets them according to the \
157 instructions in self.logspec/logdelim, and adds them as dicts to entlist."
158 while True:
159 nextentry = entfile.readline()
160 if not nextentry:
161 break
162 if nextentry[-1] != '\n':
163 break
164 entlist.append(self.logtoDict(nextentry))
165 return
166 def loadnew(self):
167 conn = getconn()
168 if conn == None:
169 return []
170 cur = conn.cursor()
171 # Get the previous offset
172 cur.execute(offselstr, [self.uname])
173 offset = cur.fetchone()[0]
174 newlist = []
175 try:
176 scr = open(self.scores, encoding="utf-8")
177 scr.seek(offset)
178 self.getEntryDicts(scr, newlist)
179 except IOError:
180 noffset = offset # Can't read anything, assume no new games
181 else:
182 noffset = scr.tell()
183 scr.close()
184 cur.execute(newoffstr, [noffset, self.uname])
185 # The new players must already be added to the players table.
186 updatenames = set([ e["name"] for e in newlist ])
187 self.postprocess(newlist)
188 self.putIntoDB(newlist, conn)
189 cur.close()
190 conn.close()
191 return updatenames
192 def postprocess(self, gamelist):
193 "Default postprocessing function: adds start time and ttyrec list"
194 names = set([ e["name"] for e in gamelist ])
195 conn = getconn()
196 if conn == None:
197 return []
198 cur = conn.cursor()
199 for nameF in names:
200 # Get all this player's games ordered by time
201 itsEntries = [ entry for entry in gamelist if entry["name"] == nameF ]
202 itsEntries.sort(key=lambda e: e["endt"])
203 # Find the end time of the latest game already in the db
204 tquery = "SELECT endt FROM {0} WHERE name = %s ORDER BY endt DESC LIMIT 1;".format(self.uname)
205 cur.execute(tquery, [nameF])
206 result = cur.fetchone()
207 if result:
208 prev = result[0]
209 else:
210 prev = datetime.fromtimestamp(0, pytz.utc);
211 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
212 allfilekeys = [ (recnameToTS(f), f) for f in os.listdir(ttyrecdir) ]
213 vfilekeys = [ e for e in allfilekeys if e[0] > prev ]
214 vfilekeys.sort(key=lambda e: e[0])
215 # Now determine startt and ttyrecs for each game
216 for i in range(0, len(itsEntries)):
217 if i == 0:
218 lowlim = prev
219 else:
220 lowlim = itsEntries[i-1]["endt"]
221 hilim = itsEntries[i]["endt"]
222 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ]
223 itsEntries[i]["startt"] = recnameToTS(recs[0])
224 itsEntries[i]["ttyrecs"] = recs
225 cur.close()
226 conn.close()
227 def putIntoDB(self, dictlist, conn):
228 cur = conn.cursor()
229 cur.executemany(self.insertq, [ d for d in dictlist if d["game"] == self ])
230 conn.commit()
231 cur.close()
232 return
233 def tablerecent(self, of):
234 "Prints the most recent games from the logfile, NOT the database."
235 newest = []
236 try:
237 scr = open(self.scores, encoding="utf-8")
238 except FileNotFoundError:
239 pass
240 else:
241 # Text streams don't support random seeking.
242 try:
243 scr.buffer.seek(self.lookback, 2)
244 except OSError:
245 scr.buffer.seek(0) # The file wasn't that long, start at the beginning
246 if scr.buffer.tell() != 0:
247 scr.buffer.readline() # Throw away the incomplete line
248 self.getEntryDicts(scr, newest)
249 newest.reverse()
250 scr.close()
251 of.write(secthead.format(self.name))
252 if not newest:
253 of.write("<div>No one has braved this dungeon yet.</div>\n")
254 else:
255 printTable(newest, self.fields, of)
256 return
257 # End Game class definition
258
259 class RogueGame(Game):
260 def __init__(self, name, uname, suffix):
261 self.name = name
262 self.uname = uname
263 self.scores = logdir + uname + ".log"
264 self.logspec = ["endt", "score", "name", "xl", "fate"]
265 self.sqltypes = {"endt": "timestamptz", "score": "int",
266 "name": "varchar(20)", "xl": "int", "fate": "text",
267 "ttyrecs": "text ARRAY", "startt": "timestamptz"}
268 self.logdelim = " "
269 self.lookback = -1500
270 # Construct the insert query
271 fields = self.sqltypes.keys()
272 colspec = ", ".join(fields)
273 valspec = ", ".join([ "%({})s".format(c) for c in fields ])
274 self.insertq = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname,
275 colspec, valspec)
276 # Class variables, used by some methods
277 fields = ["name", "score", "xl", "fate", "endt"]
278 rankfields = ["rank", "score", "name", "xl", "fate", "endt"]
279 pfields = ["score", "xl", "fate", "endt"]
280 def getRecent(self, n=20):
281 "Gets the n most recent games out of the database, returning a list \
282 of dicts."
283 try:
284 n = int(n)
285 except (ValueError, TypeError):
286 return []
287 if n <= 0:
288 return []
289 dictlist = []
290 conn = psycopg2.connect("dbname=rlg")
291 cur = conn.cursor()
292 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY endt DESC \
293 LIMIT %s;".format(self.uname)
294 cur.execute(qstr, [n])
295 for record in cur:
296 # This should be less hardcodish
297 ndict = {"game": self}
298 ndict["endt"] = record[0]
299 ndict["score"] = record[1]
300 ndict["name"] = record[2]
301 ndict["xl"] = record[3]
302 ndict["fate"] = record[4]
303 ndict["startt"] = record[5]
304 dictlist.append(ndict)
305 cur.close()
306 conn.close()
307 return dictlist
308 def getHigh(self, n=10, offset=0):
309 "Gets the n highest scores (starting at offset) from the database, \
310 returning a list of dicts."
311 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname)
312 qvals = []
313 try:
314 n = int(n)
315 except (ValueError, TypeError):
316 return []
317 if n <= 0:
318 return []
319 qstr += " LIMIT %s"
320 qvals.append(n)
321 try:
322 offset = int(offset)
323 except (ValueError, TypeError):
324 return []
325 if n > 0:
326 qstr += " OFFSET %s"
327 qvals.append(offset)
328 qstr += ";"
329 conn = psycopg2.connect("dbname=rlg")
330 cur = conn.cursor()
331 cur.execute(qstr, qvals)
332 dictlist = []
333 for record in cur:
334 ndict = {"game": self}
335 ndict["endt"] = record[0]
336 ndict["score"] = record[1]
337 ndict["name"] = record[2]
338 ndict["xl"] = record[3]
339 ndict["fate"] = record[4]
340 ndict["startt"] = record[5]
341 dictlist.append(ndict)
342 cur.close()
343 conn.close()
344 return dictlist
345 def getPlayer(self, player):
346 "Gets all player's games from the database."
347 qstr = "SELECT endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;"
348 conn = getconn()
349 if conn == None:
350 return []
351 cur = conn.cursor()
352 entries = []
353 cur.execute(qstr, [player])
354 for record in cur:
355 ndict = {"game": self}
356 ndict["endt"] = record[0]
357 ndict["score"] = record[1]
358 ndict["name"] = record[2]
359 ndict["xl"] = record[3]
360 ndict["fate"] = record[4]
361 ndict["startt"] = record[5]
362 entries.append(ndict)
363 cur.close()
364 conn.close()
365 return entries
366 def putIntoDB(self, dictlist, conn):
367 "Add the entries in dictlist to the database through connection conn, \
368 which needs the INSERT privilege."
369 fields = self.sqltypes.keys()
370 fstr = ", ".join(fields)
371 vstr = ", ".join([ "%({})s".format(c) for c in fields ])
372 qstr = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname, fstr, vstr);
373 cur = conn.cursor()
374 cur.executemany(self.insertq, [ d for d in dictlist if d["game"] == self ])
375 conn.commit()
376 cur.close()
377 return
378 # End RogueGame class definition
379
380 class ARogueGame(Game):
381 def __init__(self, name, uname, suffix):
382 self.name = name
383 self.uname = uname
384 self.scores = logdir + uname + ".log"
385 self.logspec = ["endt", "score", "name", "xl", "class", "depth",
386 "maxdepth", "quest", "hadquest", "fate"]
387 self.sqltypes = {"endt": "timestamptz", "score": "int",
388 "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int",
389 "maxdepth": "int", "quest": "int", "hadquest": "bool",
390 "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"}
391 self.logdelim = " "
392 self.lookback = -1800
393 # Construct the insert query
394 fields = self.sqltypes.keys()
395 colspec = ", ".join(fields)
396 valspec = ", ".join([ "%({})s".format(c) for c in fields ])
397 self.insertq = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname,
398 colspec, valspec)
399 # Class variables
400 fields = ["name", "score", "class", "xl", "fate", "endt"]
401 rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"]
402 pfields = ["score", "class", "xl", "fate", "endt"]
403 def getHigh(self, n=10, offset=0):
404 "Gets the n highest scores (starting at offset) from the database, \
405 returning a list of dicts."
406 qstr = "SELECT endt, score, name, xl, class, fate FROM {0} ORDER BY score DESC ".format(self.uname)
407 qvals = []
408 try:
409 n = int(n)
410 except (ValueError, TypeError):
411 return []
412 if n <= 0:
413 return []
414 qstr += " LIMIT %s"
415 qvals.append(n)
416 try:
417 offset = int(offset)
418 except (ValueError, TypeError):
419 return []
420 if n > 0:
421 qstr += " OFFSET %s"
422 qvals.append(offset)
423 qstr += ";"
424 conn = psycopg2.connect("dbname=rlg")
425 cur = conn.cursor()
426 cur.execute(qstr, qvals)
427 dictlist = []
428 for record in cur:
429 ndict = {"game": self}
430 ndict["endt"] = record[0]
431 ndict["score"] = record[1]
432 ndict["name"] = record[2]
433 ndict["xl"] = record[3]
434 ndict["class"] = record[4]
435 ndict["fate"] = record[5]
436 dictlist.append(ndict)
437 cur.close()
438 conn.close()
439 return dictlist
440 def getRecent(self, n=20):
441 return []
442 def getPlayer(self, player):
443 "Gets all player's games from the database."
444 qstr = "SELECT endt, score, name, xl, class, fate FROM " + self.uname + " WHERE name = %s;"
445 conn = getconn()
446 if conn == None:
447 return []
448 cur = conn.cursor()
449 entries = []
450 cur.execute(qstr, [player])
451 for record in cur:
452 ndict = {"game": self}
453 ndict["endt"] = record[0]
454 ndict["score"] = record[1]
455 ndict["name"] = record[2]
456 ndict["xl"] = record[3]
457 ndict["class"] = record[4]
458 ndict["fate"] = record[5]
459 entries.append(ndict)
460 cur.close()
461 conn.close()
462 return entries
463
464 rogue3 = RogueGame("Rogue V3", "rogue3", "r3")
465 rogue4 = RogueGame("Rogue V4", "rogue4", "r4")
466 rogue5 = RogueGame("Rogue V5", "rogue5", "r5")
467 srogue = RogueGame("Super-Rogue", "srogue", "sr")
468 arogue5 = ARogueGame("Advanced Rogue 5", "arogue5", "ar5")
469
470 gamelist = [rogue3, rogue4, rogue5, srogue, arogue5]
471
472 def playerpage(pname):
473 "Generate a player's HTML page"
474 # Write the beginning of the page
475 ppagefi = open(ppagename.format(pname), "w", encoding="utf-8")
476 ppagefi.write(phead.format(pname))
477 ppagefi.write(ptop)
478 ppagefi.write(navplayer.format(pname))
479 ppagefi.write(pti.format("Results for " + pname))
480 for game in gamelist:
481 ppagefi.write(secthead.format(game.name))
482 entries = game.getPlayer(pname)
483 if not entries:
484 ppagefi.write("<div>" + pname + " has not yet completed an expedition\
485 in this dungeon.</div>\n")
486 else:
487 entries.sort(key=lambda e: e["endt"])
488 printTable(entries, game.pfields, ppagefi)
489 scoresum = 0
490 for entry in entries:
491 scoresum += int(entry["score"])
492 avgscr = scoresum // len(entries)
493 ppagefi.write("<p>Average score: {0}</p>\n".format(avgscr))
494 # That should settle it. Print the end; then stop.
495 ppagefi.write(pend)
496 ppagefi.close()
497 return
498
499 def highpage():
500 # open the page and print the beginning
501 highpfi = open(hpagename, "w", encoding="utf-8")
502 highpfi.write(phead.format("High Scores"))
503 highpfi.write(ptop)
504 highpfi.write(navscore.format("High Scores"))
505 highpfi.write(pti.format("List of High Scores"))
506 for game in gamelist:
507 highpfi.write(secthead.format(game.name))
508 entries = game.getHigh(40)
509 if not entries:
510 highpfi.write("<div>No one has braved this dungeon yet.</div>\n")
511 else:
512 printTable(entries, game.rankfields, highpfi)
513 # That should finish it.
514 highpfi.write(pend)
515 highpfi.close()
516 return