Mercurial > hg > rlgallery-misc

comparison py/rlgalldb.py @ 1:def7fecbd437

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Switch to SQL timestamps. Use SQL timestamps whenever possible, and don't store UNIX timestamps in the database at all.
author John "Elwin" Edwards <elwin@sdf.org>
date Wed, 25 Jul 2012 23:05:12 -0700
parents 5ba2123d2c20
children a943cfdfbad9
comparison
equal deleted inserted replaced
0:5ba2123d2c20 1:def7fecbd437
44 hcell = ' <span class="shdata">{0}</span>\n' 44 hcell = ' <span class="shdata">{0}</span>\n'
45 tblend = '</div>\n' 45 tblend = '</div>\n'
46 pend = "</body></html>\n" 46 pend = "</body></html>\n"
47 47
48 # This would be more useful if we had to do translation 48 # This would be more useful if we had to do translation
49 headerbook = {"etime":"End time", "score":"Score", "name":"Name", "xl":"XL", 49 headerbook = {"endt":"End time", "score":"Score", "name":"Name", "xl":"XL",
50 "fate":"Fate", "rank":"Rank", "game":"Game"} 50 "fate":"Fate", "rank":"Rank", "game":"Game"}
51 # Queries for the games table 51 # Queries for the games table
52 offselstr = "SELECT offbytes FROM games WHERE gname = %s;" 52 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
53 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;" 53 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"
54 54
79 return calendar.timegm([int(val) for val in match.groups()]) 79 return calendar.timegm([int(val) for val in match.groups()])
80 80
81 def recnameToTS(filename): 81 def recnameToTS(filename):
82 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec" 82 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
83 try: 83 try:
84 dt = datetime.strptime(filename, pattern) 84 dt = datetime.strptime(filename, pattern).replace(tzinfo=utc)
85 dt.replace(tzinfo=utc)
86 return dt 85 return dt
87 except ValueError: 86 except ValueError:
88 return None 87 return None
89 88
90 def ttyreclink(text, name, game, gtime): 89 def ttyreclink(text, name, game, gtime):
98 return lstr 97 return lstr
99 98
100 def linktoArchive(entry): 99 def linktoArchive(entry):
101 "Takes an entry dict and returns a link to the ttyrec archivist." 100 "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>' 101 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"])) 102 linktext = entry["endt"].strftime("%Y/%m/%d %H:%M:%S")
104 linktext = entry["etstamp"].strftime("%Y/%m/%d %H:%M:%S") 103 stamp = calendar.timegm(entry["endt"].utctimetuple())
105 return lstr.format(entry["name"], entry["game"].uname, entry["etime"], 104 return lstr.format(entry["name"], entry["game"].uname, stamp, linktext)
106 linktext)
107 105
108 def maketablerow(cells, isheader=None): 106 def maketablerow(cells, isheader=None):
109 "Takes a list of strings and returns a HTML table row with each string \ 107 "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." 108 in its own cell. isheader will make them header cells, obviously."
111 if isheader: 109 if isheader:
143 clist.append(str(thing)) 141 clist.append(str(thing))
144 elif field == "name": 142 elif field == "name":
145 clist.append(playerlink(thing)) 143 clist.append(playerlink(thing))
146 elif field == "fate": 144 elif field == "fate":
147 clist.append(thing) 145 clist.append(thing)
148 elif field == "etime": 146 elif field == "endt":
149 clist.append(linktoArchive(entry)) 147 clist.append(linktoArchive(entry))
150 else: 148 else:
151 clist.append("{0}".format(thing)) 149 clist.append("{0}".format(thing))
152 else: 150 else:
153 clist.append("N/A") 151 clist.append("N/A")
172 class RogueGame(Game): 170 class RogueGame(Game):
173 def __init__(self, name, uname, suffix): 171 def __init__(self, name, uname, suffix):
174 self.name = name 172 self.name = name
175 self.uname = uname 173 self.uname = uname
176 self.scores = logdir + uname + ".log" 174 self.scores = logdir + uname + ".log"
177 self.logspec = ["etime", "score", "name", "xl", "fate"] 175 self.logspec = ["endt", "score", "name", "xl", "fate"]
178 self.sqltypes = {"etime": "int", "score": "int", "name": "varchar(20)", 176 self.sqltypes = {"endt": "timestamptz", "score": "int",
179 "xl": "int", "fate": "text", "stime": "int", 177 "name": "varchar(20)", "xl": "int", "fate": "text",
180 "ttyrecs": "text ARRAY", "ststamp": "timestamptz", 178 "ttyrecs": "text ARRAY", "startt": "timestamptz"}
181 "etstamp": "timestamptz"}
182 self.logdelim = " " 179 self.logdelim = " "
183 # Class variables, used by some methods 180 # Class variables, used by some methods
184 fields = ["name", "score", "xl", "fate", "etime"] 181 fields = ["name", "score", "xl", "fate", "endt"]
185 rankfields = ["rank", "score", "name", "xl", "fate", "etime"] 182 rankfields = ["rank", "score", "name", "xl", "fate", "endt"]
186 pfields = ["score", "xl", "fate", "etime"] 183 pfields = ["score", "xl", "fate", "endt"]
187 def logtoDict(self, entry): 184 def logtoDict(self, entry):
188 "Processes a log entry string, returning a dict." 185 "Processes a log entry string, returning a dict."
189 ndict = {"game": self} 186 ndict = {"game": self}
190 entrylist = entry.strip().split(self.logdelim, len(self.logspec) - 1) 187 entrylist = entry.strip().split(self.logdelim, len(self.logspec) - 1)
191 for item, value in zip(self.logspec, entrylist): 188 for item, value in zip(self.logspec, entrylist):
192 if self.sqltypes[item] == "int": 189 if self.sqltypes[item] == "int":
193 ndict[item] = int(value) 190 ndict[item] = int(value)
191 elif self.sqltypes[item] == "timestamptz":
192 ndict[item] = datetime.fromtimestamp(int(value), utc)
194 else: 193 else:
195 ndict[item] = value 194 ndict[item] = value
196 return ndict 195 return ndict
197 def getEntryDicts(self, entfile, entlist): 196 def getEntryDicts(self, entfile, entlist):
198 "Reads logfile entries from entfile, interprets them according to the \ 197 "Reads logfile entries from entfile, interprets them according to the \
215 if n <= 0: 214 if n <= 0:
216 return [] 215 return []
217 dictlist = [] 216 dictlist = []
218 conn = psycopg2.connect("dbname=rlg") 217 conn = psycopg2.connect("dbname=rlg")
219 cur = conn.cursor() 218 cur = conn.cursor()
220 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY etstamp DESC \ 219 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY endt DESC \
221 LIMIT %s;".format(self.uname) 220 LIMIT %s;".format(self.uname)
222 cur.execute(qstr, [n]) 221 cur.execute(qstr, [n])
223 for record in cur: 222 for record in cur:
224 # This should be less hardcodish 223 # This should be less hardcodish
225 ndict = {"game": self} 224 ndict = {"game": self}
226 ndict["etime"] = record[0] 225 ndict["endt"] = record[0]
227 ndict["score"] = record[1] 226 ndict["score"] = record[1]
228 ndict["name"] = record[2] 227 ndict["name"] = record[2]
229 ndict["xl"] = record[3] 228 ndict["xl"] = record[3]
230 ndict["fate"] = record[4] 229 ndict["fate"] = record[4]
231 ndict["ststamp"] = record[5] 230 ndict["startt"] = record[5]
232 ndict["etstamp"] = record[6]
233 dictlist.append(ndict) 231 dictlist.append(ndict)
234 cur.close() 232 cur.close()
235 conn.close() 233 conn.close()
236 return dictlist 234 return dictlist
237 def getHigh(self, n=10, offset=0): 235 def getHigh(self, n=10, offset=0):
238 "Gets the n highest scores (starting at offset) from the database, \ 236 "Gets the n highest scores (starting at offset) from the database, \
239 returning a list of dicts." 237 returning a list of dicts."
240 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM {0} ORDER BY score DESC\ 238 qstr = "SELECT endt, score, name, xl, fate, startt FROM {0} ORDER BY score DESC ".format(self.uname)
241 ".format(self.uname)
242 qvals = [] 239 qvals = []
243 try: 240 try:
244 n = int(n) 241 n = int(n)
245 except (ValueError, TypeError): 242 except (ValueError, TypeError):
246 return [] 243 return []
260 cur = conn.cursor() 257 cur = conn.cursor()
261 cur.execute(qstr, qvals) 258 cur.execute(qstr, qvals)
262 dictlist = [] 259 dictlist = []
263 for record in cur: 260 for record in cur:
264 ndict = {"game": self} 261 ndict = {"game": self}
265 ndict["etime"] = record[0] 262 ndict["endt"] = record[0]
266 ndict["score"] = record[1] 263 ndict["score"] = record[1]
267 ndict["name"] = record[2] 264 ndict["name"] = record[2]
268 ndict["xl"] = record[3] 265 ndict["xl"] = record[3]
269 ndict["fate"] = record[4] 266 ndict["fate"] = record[4]
270 ndict["ststamp"] = record[5] 267 ndict["startt"] = record[5]
271 ndict["etstamp"] = record[6]
272 dictlist.append(ndict) 268 dictlist.append(ndict)
273 cur.close() 269 cur.close()
274 conn.close() 270 conn.close()
275 return dictlist 271 return dictlist
276 def getPlayer(self, player): 272 def getPlayer(self, player):
277 "Gets all player's games from the database." 273 "Gets all player's games from the database."
278 qstr = "SELECT etime, score, name, xl, fate, ststamp, etstamp FROM " + self.uname + " WHERE \ 274 qstr = "SELECT endt, score, name, xl, fate, startt FROM " + self.uname + " WHERE name = %s;"
279 name = %s;"
280 conn = getconn() 275 conn = getconn()
281 if conn == None: 276 if conn == None:
282 return [] 277 return []
283 cur = conn.cursor() 278 cur = conn.cursor()
284 entries = [] 279 entries = []
285 cur.execute(qstr, [player]) 280 cur.execute(qstr, [player])
286 for record in cur: 281 for record in cur:
287 ndict = {"game": self} 282 ndict = {"game": self}
288 ndict["etime"] = record[0] 283 ndict["endt"] = record[0]
289 ndict["score"] = record[1] 284 ndict["score"] = record[1]
290 ndict["name"] = record[2] 285 ndict["name"] = record[2]
291 ndict["xl"] = record[3] 286 ndict["xl"] = record[3]
292 ndict["fate"] = record[4] 287 ndict["fate"] = record[4]
293 ndict["ststamp"] = record[5] 288 ndict["startt"] = record[5]
294 ndict["etstamp"] = record[6]
295 entries.append(ndict) 289 entries.append(ndict)
296 cur.close() 290 cur.close()
297 conn.close() 291 conn.close()
298 return entries 292 return entries
299 def putIntoDB(self, dictlist, conn): 293 def putIntoDB(self, dictlist, conn):
300 "Add the entries in dictlist to the database through connection conn, \ 294 "Add the entries in dictlist to the database through connection conn, \
301 which needs the INSERT privilege." 295 which needs the INSERT privilege."
302 # FIXME this monster needs to be procedurally generated 296 # FIXME this monster needs to be procedurally generated
303 qstr = "INSERT INTO " + self.uname + " (etime, score, name, xl, fate, stime, ttyrecs, ststamp, etstamp) \ 297 qstr = "INSERT INTO " + self.uname + " (endt, score, name, xl, fate, ttyrecs, startt) \
304 VALUES (%(etime)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(stime)s, %(ttyrecs)s, %(ststamp)s, %(etstamp)s);" 298 VALUES (%(endt)s, %(score)s, %(name)s, %(xl)s, %(fate)s, %(ttyrecs)s, %(startt)s);"
305 cur = conn.cursor() 299 cur = conn.cursor()
306 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ]) 300 cur.executemany(qstr, [ d for d in dictlist if d["game"] == self ])
307 conn.commit() 301 conn.commit()
308 cur.close() 302 cur.close()
309 return 303 return
314 return [] 308 return []
315 cur = conn.cursor() 309 cur = conn.cursor()
316 for nameF in names: 310 for nameF in names:
317 # Get all this player's games ordered by time 311 # Get all this player's games ordered by time
318 itsEntries = [ entry for entry in gamelist if entry["name"] == nameF ] 312 itsEntries = [ entry for entry in gamelist if entry["name"] == nameF ]
319 itsEntries.sort(key=lambda e: e["etime"]) 313 itsEntries.sort(key=lambda e: e["endt"])
320 # Find the end time of the latest game already in the db 314 # 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) 315 tquery = "SELECT endt FROM {0} WHERE name = %s ORDER BY endt DESC LIMIT 1;".format(self.uname)
322 cur.execute(tquery, [nameF]) 316 cur.execute(tquery, [nameF])
323 result = cur.fetchone() 317 result = cur.fetchone()
324 if result: 318 if result:
325 prev = calendar.timegm(result[0].utctimetuple()) 319 prev = result[0]
326 else: 320 else:
327 prev = 0 321 prev = datetime.fromtimestamp(0, utc);
328 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname) 322 ttyrecdir = "/var/dgl/dgldir/ttyrec/{0}/{1}/".format(nameF, self.uname)
329 allfilekeys = [ (recnameToInt(f), f) for f in os.listdir(ttyrecdir) ] 323 allfilekeys = [ (recnameToTS(f), f) for f in os.listdir(ttyrecdir) ]
330 vfilekeys = [ e for e in allfilekeys if e[0] > prev ] 324 vfilekeys = [ e for e in allfilekeys if e[0] > prev ]
331 vfilekeys.sort(key=lambda e: e[0]) 325 vfilekeys.sort(key=lambda e: e[0])
332 # Now determine stime and ttyrecs for each game 326 # Now determine startt and ttyrecs for each game
333 for i in range(0, len(itsEntries)): 327 for i in range(0, len(itsEntries)):
334 if i == 0: 328 if i == 0:
335 lowlim = prev 329 lowlim = prev
336 else: 330 else:
337 lowlim = itsEntries[i-1]["etime"] 331 lowlim = itsEntries[i-1]["endt"]
338 hilim = itsEntries[i]["etime"] 332 hilim = itsEntries[i]["endt"]
339 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ] 333 recs = [ k[1] for k in vfilekeys if lowlim <= k[0] < hilim ]
340 itsEntries[i]["stime"] = recnameToInt(recs[0]) 334 itsEntries[i]["startt"] = recnameToTS(recs[0])
341 itsEntries[i]["ttyrecs"] = recs 335 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() 336 cur.close()
346 conn.close() 337 conn.close()
347 def loadnew(self): 338 def loadnew(self):
348 conn = getconn() 339 conn = getconn()
349 if conn == None: 340 if conn == None: