#
# creatdb.py: create the tables in the PyCon demo database
#
# PostrgeSQL 8 version
#
from db import conn, curs, Error

tables = []

tables.append(("sponsorship_contact", """
CREATE TABLE sponsorship_contact
(
  cntid serial NOT NULL,
  cntname varchar(50),
  cntemail varchar(50),
  cntorgid int4,
  cnterrflag int2 DEFAULT 0,
  cntnotes varchar,
  cnttelephone varchar(25),
  CONSTRAINT sponsorship_contact_pkey PRIMARY KEY (cntid),
  CONSTRAINT "cntEmail_UQ" UNIQUE (cntemail)
) 
WITH OIDS;
"""))

tables.append(("sponsorship_spclass", """
CREATE TABLE sponsorship_spclass
(
  spcid serial NOT NULL,
  spcname varchar(10),
  spccost int4,
  CONSTRAINT "spc_PK" PRIMARY KEY (spcid)
) 
WITHOUT OIDS;
"""))

tables.append(("sponsorship_organization", """
CREATE TABLE sponsorship_organization
(
  orgid serial NOT NULL,
  orgname varchar(50),
  orgcntid int4,
  orgspcid int4,
  orgspridate timestamp(0),
  orgaddr1 varchar(30),
  orgaddr2 varchar(30),
  orgaddr3 varchar(30),
  orgaddr4 varchar(30),
  orgaddr5 varchar(30),
  orginvno int2,
  orginvdt date,
  orgbillcntid int4,
  orgporder varchar(16),
  CONSTRAINT "org_PK" PRIMARY KEY (orgid),
  CONSTRAINT "org_billcnt_FK" FOREIGN KEY (orgbillcntid) REFERENCES sponsorship_contact (cntid) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT "org_cnt_FK" FOREIGN KEY (orgcntid) REFERENCES sponsorship_contact (cntid) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT "org_spc_FK" FOREIGN KEY (orgspcid) REFERENCES sponsorship_spclass (spcid) ON UPDATE RESTRICT ON DELETE RESTRICT
) 
WITHOUT OIDS;
"""))

tables.append(("sponsorship_message", """
CREATE TABLE sponsorship_message
(
  msgid serial NOT NULL,
  msgname varchar(20),
  msgsubject varchar(80),
  msgbody varchar,
  CONSTRAINT sponsorship_message_pkey PRIMARY KEY (msgid),
  CONSTRAINT sponsorship_message_msgname_key UNIQUE (msgname)
) 
WITH OIDS;
"""))

tables.append(("sponsorship_mailing", """
CREATE TABLE sponsorship_mailing
(
  mlgid serial NOT NULL,
  mlgtime timestamp DEFAULT ('now'::text)::timestamp(0) with time zone,
  mlgcntid int4,
  mlgmsgid int4,
  CONSTRAINT sponsorship_mailing_pkey PRIMARY KEY (mlgid),
  CONSTRAINT sponsorship_mailing_mlgcntid_fkey FOREIGN KEY (mlgcntid) REFERENCES sponsorship_contact (cntid) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT sponsorship_mailing_mlgmsgid_fkey FOREIGN KEY (mlgmsgid) REFERENCES sponsorship_message (msgid) ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH OIDS;
"""))

for table, sql in reversed(tables):
    print "Deleting table", table
    try:
        curs.execute("DROP TABLE %s" % table)
    except Error:
        print "No current table to delete"
    conn.commit() # If drop fails, allow continuation

for table, sql in tables:
    try:
        print "Creating table", table
        curs.execute(sql)
    except Error, why:
        print "DB Error:", why
    conn.commit() # We'll try to continue even in the presence of errors