# L-22 MCS 275 Wed 5 Mar 2008 : normdb.py

# The database OurPyFiles has one table scripts
# with fields type, number, date, and file.
# There are two problems:
# (1) only file is unique for every record
#     and can be used as key.
# (2) redundant data occurs as many scripts
#     belong to the same date and type.
# Using the data in scripts, we create two 
# new tables:
# (1) typedate stores types and dates,
#     fields are (id, type, number, date)
# (2) filedata stores the file names,
#     fields are (id, file, tnd), 
# where tnd is the id of the corresponding
# record in the typedate table.

import MySQLdb

def CreateTypeDate(c):
   """
   The table typedate stores type and date,
   e.g.: L-4 and 2008-01-23, repesented by
   four fields: id, type, number, and date.
   The input parameter c is the cursor.
   """
   try:
      td = 'create table typedate ' + \
        '( i INT, t CHAR(1), n INT, d DATE )'
      c.execute(td)
      print td + ' succeeded'
   except:
      print td + ' went wrong...'

def CreateFileData(c):
   """
   The table filedata stores file names,
   represented by three fields: id, name,
   and id of the corresponding entry in
   the typedate table.  c is the cursor.
   """
   try:
      fd = 'create table filedata ' + \
        '( i INT, f CHAR(20), t INT )'
      c.execute(fd)
      print fd + ' succeeded'
   except:
      print fd + ' went wrong...'

def CreateTables(c):
   """
   Executes the MySQL commands to create
   the tables typedate and filedata.
   The input parameter c is the cursor.
   """
   CreateTypeDate(c)
   CreateFileData(c)

def SplitRecords(R):
   """
   Returns two lists: records that go
   in typedate and in filedata.
   """
   L = []; T = []; F = []; cnt = -1
   for i in range(0,len(R)):
      d = R[i]
      p = (d[0],d[1],d[2])
      if p in L: 
        ind = L.index(p)
      else:
        cnt = cnt + 1
        L.append((d[0],d[1],d[2]))
        T.append((cnt,d[0],d[1],d[2]))
        ind = cnt
      F.append((i,d[3],ind))
   return (T,F)

def InsertTypeDate(c,T):
   """
   Given the cursor and a list of values for
   the table typedate, all records are added.
   """
   for i in range(0,len(T)):
      p = T[i]
      q = 'insert into typedate values ' + \
          '(\"%s\", \"%s\", \"%s\", \"%s\") ' \
          % (str(p[0]), str(p[1]), str(p[2]), str(p[3]))
      c.execute(q)

def InsertFileData(c,F):
   """
   Given the cursor and a list of values for
   the table filedata, all records are added.
   """
   for i in range(0,len(F)):
      n = F[i]
      q = 'insert into filedata values ' + \
          '(\"%s\", \"%s\", \"%s\") ' \
          % (str(n[0]), n[1], str(n[2]))
      c.execute(q)

def DateFiles(c):
   """
   As a final check, selects file name
   and corresonding date from the newly
   created tables.  c is the cursor.
   """
   q = 'select f,d from filedata, typedate' \
     + ' where filedata.t = typedate.i'
   c.execute(q)
   R = c.fetchall()
   print R
   print '#records :', len(R)

def main():
   """
   Splits scripts in two tables.
   """
   db = MySQLdb.connect(db="OurPyFiles")
   c = db.cursor() 
   q = 'select * from scripts order by d'
   lc = c.execute(q)
   print 'found %d records' % int(lc)
   A = c.fetchall()
   CreateTables(c)
   (T,F) = SplitRecords(A)
   print 'T = ', T
   print 'F = ', F
   InsertTypeDate(c,T)
   InsertFileData(c,F)
   DateFiles(c)

if __name__ == "__main__": main()