Mercurial > hg > rlgallery-misc
comparison py/rlgalldb.py @ 18:5731d2ecaec4
Store arogue5 results in the database.
The ARogueGame class is functional enough to put game results into the
database, though it still can't get them back out.
author | John "Elwin" Edwards <elwin@sdf.org> |
---|---|
date | Mon, 17 Sep 2012 09:14:26 -0700 |
parents | 7f7b3da664d6 |
children | 78580bffc13d |
comparison
equal
deleted
inserted
replaced
17:7f7b3da664d6 | 18:5731d2ecaec4 |
---|---|
196 self.postprocess(newlist) | 196 self.postprocess(newlist) |
197 self.putIntoDB(newlist, conn) | 197 self.putIntoDB(newlist, conn) |
198 cur.close() | 198 cur.close() |
199 conn.close() | 199 conn.close() |
200 return updatenames | 200 return updatenames |
201 # End Game class definition | |
202 | |
203 class RogueGame(Game): | |
204 def __init__(self, name, uname, suffix): | |
205 self.name = name | |
206 self.uname = uname | |
207 self.scores = logdir + uname + ".log" | |
208 self.logspec = ["endt", "score", "name", "xl", "fate"] | |
209 self.sqltypes = {"endt": "timestamptz", "score": "int", | |
210 "name": "varchar(20)", "xl": "int", "fate": "text", | |
211 "ttyrecs": "text ARRAY", "startt": "timestamptz"} | |
212 self.logdelim = " " | |
213 # Class variables, used by some methods | |
214 fields = ["name", "score", "xl", "fate", "endt"] | |
215 rankfields = ["rank", "score", "name", "xl", "fate", "endt"] | |
216 pfields = ["score", "xl", "fate", "endt"] | |
217 def getRecent(self, n=20): | |
218 "Gets the n most recent games out of the database, returning a list \ | |
219 of dicts." | |
220 try: | |
221 n = int(n) | |
222 except (ValueError, TypeError): | |
223 return [] | |
224 if n <= 0: | |
225 return [] | |
226 dictlist = [] | |
227 conn = psycopg2.connect("dbname=rlg") | |
228 cur = conn.cursor() | |
229 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY endt DESC \ | |
230 LIMIT %s;".format(self.uname) | |
231 cur.execute(qstr, [n]) | |
232 for record in cur: | |
233 # This should be less hardcodish | |
234 ndict = {"game": self} | |
235 ndict["endt"] = record[0] | |
236 ndict["score"] = record[1] | |
237 ndict["name"] = record[2] | |
238 ndict["xl"] = record[3] | |
239 ndict["fate"] = record[4] | |
240 ndict["startt"] = record[5] | |
241 dictlist.append(ndict) | |
242 cur.close() | |
243 conn.close() | |
244 return dictlist | |
245 def getHigh(self, n=10, offset=0): | |
246 "Gets the n highest scores (starting at offset) from the database, \ | |
247 returning a list of dicts." | |
248 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname) | |
249 qvals = [] | |
250 try: | |
251 n = int(n) | |
252 except (ValueError, TypeError): | |
253 return [] | |
254 if n <= 0: | |
255 return [] | |
256 qstr += " LIMIT %s" | |
257 qvals.append(n) | |
258 try: | |
259 offset = int(offset) | |
260 except (ValueError, TypeError): | |
261 return [] | |
262 if n > 0: | |
263 qstr += " OFFSET %s" | |
264 qvals.append(offset) | |
265 qstr += ";" | |
266 conn = psycopg2.connect("dbname=rlg") | |
267 cur = conn.cursor() | |
268 cur.execute(qstr, qvals) | |
269 dictlist = [] | |
270 for record in cur: | |
271 ndict = {"game": self} | |
272 ndict["endt"] = record[0] | |
273 ndict["score"] = record[1] | |
274 ndict["name"] = record[2] | |
275 ndict["xl"] = record[3] | |
276 ndict["fate"] = record[4] | |
277 ndict["startt"] = record[5] | |
278 dictlist.append(ndict) | |
279 cur.close() | |
280 conn.close() | |
281 return dictlist | |
282 def getPlayer(self, player): | |
283 "Gets all player's games from the database." | |
284 qstr = "SELECT endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;" | |
285 conn = getconn() | |
286 if conn == None: | |
287 return [] | |
288 cur = conn.cursor() | |
289 entries = [] | |
290 cur.execute(qstr, [player]) | |
291 for record in cur: | |
292 ndict = {"game": self} | |
293 ndict["endt"] = record[0] | |
294 ndict["score"] = record[1] | |
295 ndict["name"] = record[2] | |
296 ndict["xl"] = record[3] | |
297 ndict["fate"] = record[4] | |
298 ndict["startt"] = record[5] | |
299 entries.append(ndict) | |
300 cur.close() | |
301 conn.close() | |
302 return entries | |
303 def putIntoDB(self, dictlist, conn): | |
304 "Add the entries in dictlist to the database through connection conn, \ | |
305 which needs the INSERT privilege." | |
306 # FIXME this monster needs to be procedurally generated | |
307 qstr = "INSERT INTO " + self.uname + " (endt, score, name, xl, fate, ttyrecs, startt) \ | |
308 VALUES (%(endt)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(ttyrecs)s, %(startt)s);" | |
309 cur = conn.cursor() | |
310 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ]) | |
311 conn.commit() | |
312 cur.close() | |
313 return | |
314 def postprocess(self, gamelist): | 201 def postprocess(self, gamelist): |
202 "Default postprocessing function: adds start time and ttyrec list" | |
315 names = set([ e["name"] for e in gamelist ]) | 203 names = set([ e["name"] for e in gamelist ]) |
316 conn = getconn() | 204 conn = getconn() |
317 if conn == None: | 205 if conn == None: |
318 return [] | 206 return [] |
319 cur = conn.cursor() | 207 cur = conn.cursor() |
343 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ] | 231 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ] |
344 itsEntries[i]["startt"] = recnameToTS(recs[0]) | 232 itsEntries[i]["startt"] = recnameToTS(recs[0]) |
345 itsEntries[i]["ttyrecs"] = recs | 233 itsEntries[i]["ttyrecs"] = recs |
346 cur.close() | 234 cur.close() |
347 conn.close() | 235 conn.close() |
236 def putIntoDB(self, dictlist, conn): | |
237 cur = conn.cursor() | |
238 cur.executemany(self.insertq, [ d for d in dictlist if d["game"] == self ]) | |
239 conn.commit() | |
240 cur.close() | |
241 return | |
242 # End Game class definition | |
243 | |
244 class RogueGame(Game): | |
245 def __init__(self, name, uname, suffix): | |
246 self.name = name | |
247 self.uname = uname | |
248 self.scores = logdir + uname + ".log" | |
249 self.logspec = ["endt", "score", "name", "xl", "fate"] | |
250 self.sqltypes = {"endt": "timestamptz", "score": "int", | |
251 "name": "varchar(20)", "xl": "int", "fate": "text", | |
252 "ttyrecs": "text ARRAY", "startt": "timestamptz"} | |
253 self.logdelim = " " | |
254 colspec = "(" | |
255 valspec = "(" | |
256 for i, col in enumerate(self.sqltypes.keys()): | |
257 colspec += col | |
258 valspec += "%({0})s".format(col) | |
259 if i == len(self.sqltypes) - 1: | |
260 colspec += ")" | |
261 valspec += ")" | |
262 else: | |
263 colspec += ", " | |
264 valspec += ", " | |
265 self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname, | |
266 colspec, valspec) | |
267 # Class variables, used by some methods | |
268 fields = ["name", "score", "xl", "fate", "endt"] | |
269 rankfields = ["rank", "score", "name", "xl", "fate", "endt"] | |
270 pfields = ["score", "xl", "fate", "endt"] | |
271 def getRecent(self, n=20): | |
272 "Gets the n most recent games out of the database, returning a list \ | |
273 of dicts." | |
274 try: | |
275 n = int(n) | |
276 except (ValueError, TypeError): | |
277 return [] | |
278 if n <= 0: | |
279 return [] | |
280 dictlist = [] | |
281 conn = psycopg2.connect("dbname=rlg") | |
282 cur = conn.cursor() | |
283 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY endt DESC \ | |
284 LIMIT %s;".format(self.uname) | |
285 cur.execute(qstr, [n]) | |
286 for record in cur: | |
287 # This should be less hardcodish | |
288 ndict = {"game": self} | |
289 ndict["endt"] = record[0] | |
290 ndict["score"] = record[1] | |
291 ndict["name"] = record[2] | |
292 ndict["xl"] = record[3] | |
293 ndict["fate"] = record[4] | |
294 ndict["startt"] = record[5] | |
295 dictlist.append(ndict) | |
296 cur.close() | |
297 conn.close() | |
298 return dictlist | |
299 def getHigh(self, n=10, offset=0): | |
300 "Gets the n highest scores (starting at offset) from the database, \ | |
301 returning a list of dicts." | |
302 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname) | |
303 qvals = [] | |
304 try: | |
305 n = int(n) | |
306 except (ValueError, TypeError): | |
307 return [] | |
308 if n <= 0: | |
309 return [] | |
310 qstr += " LIMIT %s" | |
311 qvals.append(n) | |
312 try: | |
313 offset = int(offset) | |
314 except (ValueError, TypeError): | |
315 return [] | |
316 if n > 0: | |
317 qstr += " OFFSET %s" | |
318 qvals.append(offset) | |
319 qstr += ";" | |
320 conn = psycopg2.connect("dbname=rlg") | |
321 cur = conn.cursor() | |
322 cur.execute(qstr, qvals) | |
323 dictlist = [] | |
324 for record in cur: | |
325 ndict = {"game": self} | |
326 ndict["endt"] = record[0] | |
327 ndict["score"] = record[1] | |
328 ndict["name"] = record[2] | |
329 ndict["xl"] = record[3] | |
330 ndict["fate"] = record[4] | |
331 ndict["startt"] = record[5] | |
332 dictlist.append(ndict) | |
333 cur.close() | |
334 conn.close() | |
335 return dictlist | |
336 def getPlayer(self, player): | |
337 "Gets all player's games from the database." | |
338 qstr = "SELECT endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;" | |
339 conn = getconn() | |
340 if conn == None: | |
341 return [] | |
342 cur = conn.cursor() | |
343 entries = [] | |
344 cur.execute(qstr, [player]) | |
345 for record in cur: | |
346 ndict = {"game": self} | |
347 ndict["endt"] = record[0] | |
348 ndict["score"] = record[1] | |
349 ndict["name"] = record[2] | |
350 ndict["xl"] = record[3] | |
351 ndict["fate"] = record[4] | |
352 ndict["startt"] = record[5] | |
353 entries.append(ndict) | |
354 cur.close() | |
355 conn.close() | |
356 return entries | |
357 def putIntoDB(self, dictlist, conn): | |
358 "Add the entries in dictlist to the database through connection conn, \ | |
359 which needs the INSERT privilege." | |
360 # FIXME this monster needs to be procedurally generated | |
361 qstr = "INSERT INTO " + self.uname + " (endt, score, name, xl, fate, ttyrecs, startt) \ | |
362 VALUES (%(endt)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(ttyrecs)s, %(startt)s);" | |
363 cur = conn.cursor() | |
364 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ]) | |
365 conn.commit() | |
366 cur.close() | |
367 return | |
348 # End RogueGame class definition | 368 # End RogueGame class definition |
349 | 369 |
350 class ARogueGame(Game): | 370 class ARogueGame(Game): |
351 def __init__(self, name, uname, suffix): | 371 def __init__(self, name, uname, suffix): |
352 self.name = name | 372 self.name = name |
357 self.sqltypes = {"endt": "timestamptz", "score": "int", | 377 self.sqltypes = {"endt": "timestamptz", "score": "int", |
358 "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int", | 378 "name": "varchar(20)", "xl": "int", "class": "text", "depth": "int", |
359 "maxdepth": "int", "quest": "int", "hadquest": "bool", | 379 "maxdepth": "int", "quest": "int", "hadquest": "bool", |
360 "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"} | 380 "fate": "text", "ttyrecs": "text ARRAY", "startt": "timestamptz"} |
361 self.logdelim = " " | 381 self.logdelim = " " |
382 colspec = "(" | |
383 valspec = "(" | |
384 for i, col in enumerate(self.sqltypes.keys()): | |
385 colspec += col | |
386 valspec += "%({0})s".format(col) | |
387 if i == len(self.sqltypes) - 1: | |
388 colspec += ")" | |
389 valspec += ")" | |
390 else: | |
391 colspec += ", " | |
392 valspec += ", " | |
393 self.insertq = "INSERT INTO {0} {1} VALUES {2};".format(self.uname, | |
394 colspec, valspec) | |
362 # Class variables | 395 # Class variables |
363 fields = ["name", "score", "class", "xl", "fate", "endt"] | 396 fields = ["name", "score", "class", "xl", "fate", "endt"] |
364 rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"] | 397 rankfields = ["rank", "score", "name", "class", "xl", "fate", "endt"] |
365 pfields = ["score", "class", "xl", "fate", "endt"] | 398 pfields = ["score", "class", "xl", "fate", "endt"] |
366 def postprocess(self, gamelist): | |
367 "Not Implemented" | |
368 return | |
369 def putIntoDB(self, dictlist, conn): | |
370 "Not Implemented" | |
371 conn.commit() | |
372 return | |
373 def getHigh(self, n=10, offset=0): | 399 def getHigh(self, n=10, offset=0): |
374 return [] | 400 return [] |
375 def getRecent(self, n=20): | 401 def getRecent(self, n=20): |
376 return [] | 402 return [] |
377 def getPlayer(self, player): | 403 def getPlayer(self, player): |