Mercurial > hg > rlgallery-misc

annotate py/setupdb.py @ 21:453278a81371

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add tablerecent() so recent.cgi will work as before. Port the tablerecent() function, which reads the most recent games directly from the logfile instead of the database. This allows recent.cgi to show games immediately without waiting for the recorder to put them in the database.
author John "Elwin" Edwards <elwin@sdf.org>
date Mon, 17 Sep 2012 13:41:10 -0700
parents 5ba2123d2c20
children e8f3b7994d88
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 #!/usr/bin/python
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
2 # setupdb.py: initializes the database tables used by the rlg system.
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 rlgalldb as rlgall
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
5 import psycopg2
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
6
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
7 webuser = "webserver"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
8 allowquery = "GRANT SELECT ON {0} TO " + webuser + ";"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
9
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
10 dbconn = psycopg2.connect("dbname=rlg")
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
11 dbcur = dbconn.cursor()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
12
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
13 dbcur.execute("CREATE TABLE games ( gname varchar(20), offbytes int );")
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
14 dbcur.execute("CREATE TABLE players (pname varchar(20) PRIMARY KEY);")
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
15 dbconn.commit()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
16
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
17 for game in rlgall.gamelist:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
18 dbcur.execute("INSERT INTO games VALUES (%s, %s);", (game.uname, 0))
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
19
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
20 createquery = "CREATE TABLE " + game.uname + " ( "
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
21 for i, field in enumerate(game.sqltypes.keys()):
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
22 createquery += "{0} {1}".format(field, game.sqltypes[field])
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
23 if field == "name":
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
24 createquery += " REFERENCES players(pname)"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
25 if i == len(game.sqltypes) - 1:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
26 createquery += " )"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
27 else:
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
28 createquery += ", "
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
29 createquery += ";"
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
30 #print createquery
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
31 dbcur.execute(createquery)
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
32 dbcur.execute(allowquery.format(game.uname))
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
33 dbconn.commit()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
34
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
35 dbcur.close()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
36 dbconn.close()
5ba2123d2c20 Put this project under version control, finally.
John "Elwin" Edwards <elwin@sdf.org>
parents:
diff changeset
37 exit()