Mercurial > hg > rlgallery-misc

annotate py/rlgalldb.py @ 2:8f49df4074d7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Convert web/archive.cgi to use the new SQL timestamps. The Archivist has replaced UNIX timestamps with python datetime objects derived from SQL timestamps. There are still a lot of struct_time objects which should be converted too.
author John "Elwin" Edwards <elwin@sdf.org>
date Thu, 26 Jul 2012 08:07:00 -0700
parents def7fecbd437
children a943cfdfbad9
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 sys
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
6 import time
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
7 import calendar
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
8 import re
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
9 import psycopg2
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
10 from datetime import datetime, tzinfo, timedelta
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
11
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
12 # Configuration
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
13 logdir = "/var/dgl/var/games/roguelike/"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
14 webdir = "/var/www/lighttpd/scoring/"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
15 ppagename = webdir + "players/{0}.html"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
16 hpagename = webdir + "highscores.html"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
17
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
18 # HTML fragments for templating
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
19 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
20 <html><head>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
21 <title>{0}</title>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
22 <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
23 </head>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
24 """
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 ptop = """<body>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
27 <h1>Yendor Guild</h1>
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
28 """
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
29
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
30 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
31 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
32 <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
33 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
34 <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
35 -&gt; {0}</div>'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
36
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
37 pti = '<h2>{0}</h2>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
38
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
39 secthead = '<h3>{0}</h3>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
40 tblhead = '<div class="stable">\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
41 rowstart = '<div class="sentry">\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
42 rowend = '</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
43 cell = ' <span class="sdata">{0}</span>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
44 hcell = ' <span class="shdata">{0}</span>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
45 tblend = '</div>\n'
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
46 pend = "</body></html>\n"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
47
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
48 # 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
49 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
50 "fate":"Fate", "rank":"Rank", "game":"Game"}
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
51 # Queries for the games table
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
52 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
53 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
54
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
55 # 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
56 # this madness.
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
57 class UTC(tzinfo):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
58 def utcoffset(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
59 return timedelta(0)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
60 def dst(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
61 return timedelta(0)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
62 def tzname(self, dt):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
63 return "UTC"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
64 utc = UTC()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
65
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
66 def getconn():
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
67 "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
68 try:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
69 conn = psycopg2.connect("dbname=rlg")
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
70 except psycopg2.OperationalError:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
71 return None
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
72 return conn
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
73
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
74 def recnameToInt(filename):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
75 recre = r"(\d{4})-(\d{2})-(\d{2})\.(\d{2}):(\d{2}):(\d{2})\.ttyrec$"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
76 match = re.match(recre, filename)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
77 if not match:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
78 return None
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
79 return calendar.timegm([int(val) for val in match.groups()])
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
80
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
81 def recnameToTS(filename):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
82 pattern = "%Y-%m-%d.%H:%M:%S.ttyrec"
5ba2123d2c20 Put this project under version control, finally.