view py/setupdb.py @ 2:8f49df4074d7

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 5ba2123d2c20
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()