Mercurial > hg > rlgallery-misc

annotate py/rlgalldb.py @ 18:5731d2ecaec4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
1 # rlgalldb.py
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
2 # Module for the Roguelike Gallery, using a postgres database
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
3
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
4 import os
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
5 import calendar
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
6 import psycopg2
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
7 from datetime import datetime, tzinfo, timedelta
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
8
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
9 # Configuration
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
10 logdir = "/var/dgl/var/games/roguelike/"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
11 webdir = "/var/www/lighttpd/scoring/"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
12 ppagename = webdir + "players/{0}.html"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
13 hpagename = webdir + "highscores.html"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
14
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
15 # HTML fragments for templating
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
16 phead = """<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
17 <html><head>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
18 <title>{0}</title>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
19 <link rel="stylesheet" href="/scoring/scores.css" type="text/css">
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
20 </head>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
21 """
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
22
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
23 ptop = """<body>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
24 <h1>Yendor Guild</h1>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
25 """
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
26
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
27 navtop = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; {0}</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
28 navscore = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
29 <a href="/scoring/">Scores</a> -&gt; {0}</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
30 navplayer = '<div class="nav"><a href="/">rlgallery.org</a> -&gt; \
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
31 <a href="/scoring/">Scores</a> -&gt; <a href="/scoring/players/">Players</a> \
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
32 -&gt; {0}</div>'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
33
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
34 pti = '<h2>{0}</h2>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
35
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
36 secthead = '<h3>{0}</h3>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
37 tblhead = '<div class="stable">\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
38 rowstart = '<div class="sentry">\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
39 rowend = '</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
40 cell = ' <span class="sdata">{0}</span>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
41 hcell = ' <span class="shdata">{0}</span>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
42 tblend = '</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
43 pend = "</body></html>\n"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
44
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
45 # This would be more useful if we had to do translation
1
def7fecbd437 Switch to SQL timestamps.
John "Elwin" Edwards <elwin@sdf.org>
parents: 0
diff changeset
46 headerbook = {"endt":"End time", "score":"Score", "name":"Name", "xl":"XL",
0
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
47 "fate":"Fate", "rank":"Rank", "game":"Game"}
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
48 # Queries for the games table
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
49 offselstr = "SELECT offbytes FROM games WHERE gname = %s;"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
50 newoffstr = "UPDATE games SET offbytes = %s WHERE gname = %s;"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
51
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
52 # A representation of the UTC time zone. They say Py3k can better handle
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
53 # this madness.
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
54 class UTC(tzinfo):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
55 def utcoffset(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
56 return timedelta(0)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
57 def dst(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
58 return timedelta(0)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
59 def tzname(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
60 return "UTC"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
61 utc = UTC()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
62
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
63 def getconn():
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
64 "Returns a database connection, or None if the connection fails."
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
65 try:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
66 conn = psycopg2.connect("dbname=rlg")
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
67 except psycopg2.OperationalError:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
68 return None
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
69 return conn
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
70
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
71 def recnameToTS(filename):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
72 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
73 try:
1
def7fecbd437 Switch to SQL timestamps.
John "Elwin" Edwards <elwin@sdf.org>
parents: 0
diff changeset
74 dt = datetime.strptime(filename, pattern).replace(tzinfo=utc)
0
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
75 return dt
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
76 except ValueError:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
77 return None
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
78
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
79 def ttyreclink(text, name, game, gtime):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
80 "Returns a link to the ttyrec archivist"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
81 lstr = '<a href="/archive.cgi?name={0};game={1};time={2}">{3}</a>'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
82 return lstr.format(name, game, gtime, text)