Mercurial > hg > rlgallery-misc

comparison py/rlgalldb.py @ 30:e8f3b7994d88

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Port to Python 3. All scripts and modules have been ported to Python 3 and appear to work. Some changes to the lighttpd configuration were needed.
author John "Elwin" Edwards
date Tue, 31 Dec 2013 13:36:19 -0500
parents 453278a81371
children 7303535b5a5d
comparison
equal deleted inserted replaced
29:23a769aa487e 30:e8f3b7994d88
1 # rlgalldb.py 1 # rlgalldb.py
2 # Module for the Roguelike Gallery, using a postgres database 2 # Module for the Roguelike Gallery, using a postgres database
3 # Requires Python 3.3
3 4
4 import os 5 import os
5 import calendar
6 import psycopg2 6 import psycopg2
7 from datetime import datetime, tzinfo, timedelta 7 from datetime import datetime
8 import pytz
8 9
9 # Configuration 10 # Configuration
10 logdir = "/var/dgl/var/games/roguelike/" 11 logdir = "/var/dgl/var/games/roguelike/"
11 webdir = "/var/www/lighttpd/scoring/" 12 webdir = "/var/www/lighttpd/scoring/"
12 ppagename = webdir + "players/{0}.html" 13 ppagename = webdir + "players/{0}.html"
47 "fate":"Fate", "rank":"Rank", "game":"Game", "class": "Class"} 48 "fate":"Fate", "rank":"Rank", "game":"Game", "class": "Class"}
48 # Queries for the games table 49 # Queries for the games table
49 offselstr = "SELECT offbytes FROM games WHERE gname = %s;" 50 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
50 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;" 51 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"
51 52
52 # A representation of the UTC time zone. They say Py3k can better handle
53 # this madness.
54 class UTC(tzinfo):
55 def utcoffset(self, dt):
56 return timedelta(0)
57 def dst(self, dt):
58 return timedelta(0)
59 def tzname(self, dt):
60 return "UTC"
61 utc = UTC()
62
63 def getconn(): 53 def getconn():
64 "Returns a database connection, or None if the connection fails." 54 "Returns a database connection, or None if the connection fails."
65 try: 55 try:
66 conn = psycopg2.connect("dbname=rlg") 56 conn = psycopg2.connect("dbname=rlg")
67 except psycopg2.OperationalError: 57 except psycopg2.OperationalError:
69 return conn 59 return conn
70 60
71 def recnameToTS(filename): 61 def recnameToTS(filename):
72 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec" 62 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
73 try: 63 try:
74 dt = datetime.strptime(filename, pattern).replace(tzinfo=utc) 64 dt = datetime.strptime(filename, pattern).replace(tzinfo=pytz.utc)
75 return dt 65 return dt
76 except ValueError: 66 except ValueError:
77 return None 67 return None
78 68
79 def ttyreclink(text, name, game, gtime): 69 def ttyreclink(text, name, game, gtime):
88 78
89 def linktoArchive(entry): 79 def linktoArchive(entry):
90 "Takes an entry dict and returns a link to the ttyrec archivist." 80 "Takes an entry dict and returns a link to the ttyrec archivist."
91 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>' 81 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
92 linktext = entry["endt"].strftime("%Y/%m/%d %H:%M:%S") 82 linktext = entry["endt"].strftime("%Y/%m/%d %H:%M:%S")
93 stamp = calendar.timegm(entry["endt"].utctimetuple()) 83 stamp = int(entry["endt"].timestamp())
94 return lstr.format(entry["name"], entry["game"].uname, stamp, linktext) 84 return lstr.format(entry["name"], entry["game"].uname, stamp, linktext)
95 85
96 def maketablerow(cells, isheader=None): 86 def maketablerow(cells, isheader=None):
97 "Takes a list of strings and returns a HTML table row with each string \ 87 "Takes a list of strings and returns a HTML table row with each string \
98 in its own cell. isheader will make them header cells, obviously." 88 in its own cell. isheader will make them header cells, obviously."
154 if self.sqltypes[item] == "int": 144 if self.sqltypes[item] == "int":
155 ndict[item] = int(value) 145 ndict[item] = int(value)
156 if self.sqltypes[item] == "bool": 146 if self.sqltypes[item] == "bool":
157 ndict[item] = bool(int(value)) 147 ndict[item] = bool(int(value))
158 elif self.sqltypes[item] == "timestamptz": 148 elif self.sqltypes[item] == "timestamptz":
159 ndict[item] = datetime.fromtimestamp(int(value), utc) 149 ndict[item] = datetime.fromtimestamp(int(value), pytz.utc)
160 else: 150 else:
161 ndict[item] = value 151 ndict[item] = value
162 return ndict 152 return ndict
163 def getEntryDicts(self, entfile, entlist): 153 def getEntryDicts(self, entfile, entlist):
164 "Reads logfile entries from entfile, interprets them according to the \ 154 "Reads logfile entries from entfile, interprets them according to the \
179 # Get the previous offset 169 # Get the previous offset
180 cur.execute(offselstr, [self.uname]) 170 cur.execute(offselstr, [self.uname])
181 offset = cur.fetchone()[0] 171 offset = cur.fetchone()[0]
182 newlist = [] 172 newlist = []
183 try: 173 try:
184 scr = open(self.scores) 174 scr = open(self.scores, encoding="utf-8")
185 scr.seek(offset) 175 scr.seek(offset)
186 self.getEntryDicts(scr, newlist) 176 self.getEntryDicts(scr, newlist)
187 except IOError: 177 except IOError:
188 noffset = offset # Can't read anything, assume no new games 178 noffset = offset # Can't read anything, assume no new games
189 else: 179 else:
213 cur.execute(tquery, [nameF]) 203 cur.execute(tquery, [nameF])
214 result = cur.fetchone() 204 result = cur.fetchone()
215 if result: 205 if result:
216 prev = result[0] 206 prev = result[0]
217 else: 207 else:
218 prev = datetime.fromtimestamp(0, utc); 208 prev = datetime.fromtimestamp(0, pytz.utc);
219 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname) 209 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
220 allfilekeys = [ (recnameToTS(f), f) for f in os.listdir(ttyrecdir) ] 210 allfilekeys = [ (recnameToTS(f), f) for f in os.listdir(ttyrecdir) ]
221 vfilekeys = [ e for e in allfilekeys if e[0] > prev ] 211 vfilekeys = [ e for e in allfilekeys if e[0] > prev ]
222 vfilekeys.sort(key=lambda e: e[0]) 212 vfilekeys.sort(key=lambda e: e[0])
223 # Now determine startt and ttyrecs for each game 213 # Now determine startt and ttyrecs for each game
240 return 230 return
241 def tablerecent(self, of): 231 def tablerecent(self, of):
242 "Prints the most recent games from the logfile, NOT the database." 232 "Prints the most recent games from the logfile, NOT the database."
243 newest = [] 233 newest = []
244 try: 234 try:
245 scr = open(self.scores) 235 scr = open(self.scores, encoding="utf-8")
246 except IOError: 236 except FileNotFoundError:
247 pass 237 pass
248 else: 238 else:
239 # Text streams don't support random seeking.
249 try: 240 try:
250 scr.seek(self.lookback, 2) 241 scr.buffer.seek(self.lookback, 2)
251 except IOError: 242 except OSError:
252 scr.seek(0) # The file wasn't that long, start at the beginning 243 scr.buffer.seek(0) # The file wasn't that long, start at the beginning
253 if scr.tell() != 0: 244 if scr.buffer.tell() != 0:
254 scr.readline() # Throw away the incomplete line 245 scr.buffer.readline() # Throw away the incomplete line
255 self.getEntryDicts(scr, newest) 246 self.getEntryDicts(scr, newest)
256 newest.reverse() 247 newest.reverse()
257 scr.close() 248 scr.close()
258 of.write(secthead.format(self.name)) 249 of.write(secthead.format(self.name))
259 if not newest: 250 if not newest:
272 self.sqltypes = {"endt": "timestamptz", "score": "int", 263 self.sqltypes = {"endt": "timestamptz", "score": "int",
273 "name": "varchar(20)", "xl": "int", "fate": "text", 264 "name": "varchar(20)", "xl": "int", "fate": "text",
274 "ttyrecs": "text ARRAY", "startt": "timestamptz"} 265 "ttyrecs": "text ARRAY", "startt": "timestamptz"}
275 self.logdelim = " " 266 self.logdelim = " "
276 self.lookback = -1500 267 self.lookback = -1500
277 colspec = "(" 268 # Construct the insert query
278 valspec = "(" 269 fields = self.sqltypes.keys()
279 for i, col in enumerate(self.sqltypes.keys()): 270 colspec = ", ".join(fields)
280 colspec += col 271 valspec = ", ".join([ "%({})s".format(c) for c in fields ])
281 valspec += "%({0})s".format(col) 272 self.insertq = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname,
282 if i == len(self.sqltypes) - 1:
283 colspec += ")"
284 valspec += ")"
285 else:
286 colspec += ", "
287 valspec += ", "
288 self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname,
289 colspec, valspec) 273 colspec, valspec)
290 # Class variables, used by some methods 274 # Class variables, used by some methods
291 fields = ["name", "score", "xl", "fate", "endt"] 275 fields = ["name", "score", "xl", "fate", "endt"]
292 rankfields = ["rank", "score", "name", "xl", "fate", "endt"] 276 rankfields = ["rank", "score", "name", "xl", "fate", "endt"]
293 pfields = ["score", "xl", "fate", "endt"] 277 pfields = ["score", "xl", "fate", "endt"]
378 conn.close() 362 conn.close()
379 return entries 363 return entries
380 def putIntoDB(self, dictlist, conn): 364 def putIntoDB(self, dictlist, conn):
381 "Add the entries in dictlist to the database through connection conn, \ 365 "Add the entries in dictlist to the database through connection conn, \
382 which needs the INSERT privilege." 366 which needs the INSERT privilege."
383 # FIXME this monster needs to be procedurally generated 367 fields = self.sqltypes.keys()
384 qstr = "INSERT INTO " + self.uname + " (endt, score, name, xl, fate, ttyrecs, startt) \ 368 fstr = ", ".join(fields)
385 VALUES (%(endt)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(ttyrecs)s, %(startt)s);" 369 vstr = ", ".join([ "%({})s".format(c) for c in fields ])
386 cur = conn.cursor() 370 qstr = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname, fstr, vstr);
387 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ]) 371 cur = conn.cursor()
372 cur.executemany(self.insertq, [ d for d in dictlist if d["game"] == self ])
388 conn.commit() 373 conn.commit()
389 cur.close() 374 cur.close()
390 return 375 return
391 # End RogueGame class definition 376 # End RogueGame class definition
392 377
401 "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int", 386 "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int",
402 "maxdepth": "int", "quest": "int", "hadquest": "bool", 387 "maxdepth": "int", "quest": "int", "hadquest": "bool",
403 "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"} 388 "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"}
404 self.logdelim = " " 389 self.logdelim = " "
405 self.lookback = -1800 390 self.lookback = -1800
406 colspec = "(" 391 # Construct the insert query
407 valspec = "(" 392 fields = self.sqltypes.keys()
408 for i, col in enumerate(self.sqltypes.keys()): 393 colspec = ", ".join(fields)
409 colspec += col 394 valspec = ", ".join([ "%({})s".format(c) for c in fields ])
410 valspec += "%({0})s".format(col) 395 self.insertq = "INSERT INTO {0} ({1}) VALUES ({2});".format(self.uname,
411 if i == len(self.sqltypes) - 1:
412 colspec += ")"
413 valspec += ")"
414 else:
415 colspec += ", "
416 valspec += ", "
417 self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname,
418 colspec, valspec) 396 colspec, valspec)
419 # Class variables 397 # Class variables
420 fields = ["name", "score", "class", "xl", "fate", "endt"] 398 fields = ["name", "score", "class", "xl", "fate", "endt"]
421 rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"] 399 rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"]
422 pfields = ["score", "class", "xl", "fate", "endt"] 400 pfields = ["score", "class", "xl", "fate", "endt"]
490 gamelist = [rogue3, rogue4, rogue5, srogue, arogue5] 468 gamelist = [rogue3, rogue4, rogue5, srogue, arogue5]
491 469
492 def playerpage(pname): 470 def playerpage(pname):
493 "Generate a player's HTML page" 471 "Generate a player's HTML page"
494 # Write the beginning of the page 472 # Write the beginning of the page
495 ppagefi = open(ppagename.format(pname), "w") 473 ppagefi = open(ppagename.format(pname), "w", encoding="utf-8")
496 ppagefi.write(phead.format(pname)) 474 ppagefi.write(phead.format(pname))
497 ppagefi.write(ptop) 475 ppagefi.write(ptop)
498 ppagefi.write(navplayer.format(pname)) 476 ppagefi.write(navplayer.format(pname))
499 ppagefi.write(pti.format("Results for " + pname)) 477 ppagefi.write(pti.format("Results for " + pname))
500 for game in gamelist: 478 for game in gamelist:
507 entries.sort(key=lambda e: e["endt"]) 485 entries.sort(key=lambda e: e["endt"])
508 printTable(entries, game.pfields, ppagefi) 486 printTable(entries, game.pfields, ppagefi)
509 scoresum = 0 487 scoresum = 0
510 for entry in entries: 488 for entry in entries:
511 scoresum += int(entry["score"]) 489 scoresum += int(entry["score"])
512 avgscr = scoresum / len(entries) 490 avgscr = scoresum // len(entries)
513 ppagefi.write("<p>Average score: {0}</p>\n".format(avgscr)) 491 ppagefi.write("<p>Average score: {0}</p>\n".format(avgscr))
514 # That should settle it. Print the end; then stop. 492 # That should settle it. Print the end; then stop.
515 ppagefi.write(pend) 493 ppagefi.write(pend)
516 ppagefi.close() 494 ppagefi.close()
517 return 495 return
518 496
519 def highpage(): 497 def highpage():
520 # open the page and print the beginning 498 # open the page and print the beginning
521 highpfi = open(hpagename, "w") 499 highpfi = open(hpagename, "w", encoding="utf-8")
522 highpfi.write(phead.format("High Scores")) 500 highpfi.write(phead.format("High Scores"))
523 highpfi.write(ptop) 501 highpfi.write(ptop)
524 highpfi.write(navscore.format("High Scores")) 502 highpfi.write(navscore.format("High Scores"))
525 highpfi.write(pti.format("List of High Scores")) 503 highpfi.write(pti.format("List of High Scores"))
526 for game in gamelist: 504 for game in gamelist: