Mercurial > hg > rlgallery-misc

annotate py/rlgalldb.py @ 15:f501c8dd6884

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
dgl conf: Let the cursor be placed in the proper position automatically.
author John "Elwin" Edwards <elwin@sdf.org>
date Sat, 15 Sep 2012 09:40:26 -0700
parents f5a37cc7f41f
children 7f7b3da664d6
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)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>