view py/setupdb.py @ 0:5ba2123d2c20

Put this project under version control, finally. Scripts for rlgallery.org, using a PostgreSQL backend. The recorder system is in py/, CGI scripts are in web/.
author John "Elwin" Edwards <elwin@sdf.org>
date Wed, 25 Jul 2012 21:59:42 -0700
parents
children e8f3b7994d88
line wrap: on
line source

#!/usr/bin/python
# setupdb.py: initializes the database tables used by the rlg system.

import rlgalldb as rlgall
import psycopg2

webuser = "webserver"
allowquery = "GRANT SELECT ON {0} TO " + webuser + ";"

dbconn = psycopg2.connect("dbname=rlg")
dbcur = dbconn.cursor()

dbcur.execute("CREATE TABLE games ( gname varchar(20), offbytes int );")
dbcur.execute("CREATE TABLE players (pname varchar(20) PRIMARY KEY);")
dbconn.commit()

for game in rlgall.gamelist:
  dbcur.execute("INSERT INTO games VALUES (%s, %s);", (game.uname, 0))

  createquery = "CREATE TABLE " + game.uname + " ( "
  for i, field in enumerate(game.sqltypes.keys()):
    createquery += "{0} {1}".format(field, game.sqltypes[field])
    if field == "name":
      createquery += " REFERENCES players(pname)"
    if i == len(game.sqltypes) - 1:
      createquery += " )"
    else:
      createquery += ", "
  createquery += ";"
  #print createquery
  dbcur.execute(createquery)
  dbcur.execute(allowquery.format(game.uname))
  dbconn.commit()

dbcur.close()
dbconn.close()
exit()