# L-29 MCS 275 Fri 19 Mar 2010 : scripts_sortdb.py

# Database server to scripts in OurPyFiles database.
# Sends number of scripts in the table 'scripts'
# in the MySQL database 'OurPyFiles' to the client.
# Requirements for a successful run:
# (1) MySQL must be started (sudo mysqld_safe); and
# (2) run this script as sudo python scripts_count.py.
# This server will listen to two clients:
# (1) first to scripts_sort.py for sort order
# (2) then to scripts_sortall.py.

import MySQLdb
from socket import *

hostname = ''   # use any address
number = 11267  # number for the port
buffer = 80     # size of the buffer

def ConnectClient():
   """
   Returns client and server socket
   to communicate with one client.
   """
   server_address = (hostname, number)
   server = socket(AF_INET, SOCK_STREAM)
   server.bind(server_address)
   server.listen(2)
   print 'server waits for connection'
   client, client_address = server.accept()
   print 'server accepted connection from ',\
      client_address
   return client, server

def CountRecords(c):
   """
   Returns the number of scripts,
   given the cursor c.
   """
   q = 'select count(*) from scripts'
   c.execute(q)
   r = c.fetchone()
   n = int(r[0])
   return n

def RetrieveRecords(c,sortorder):
   """
   Given cursor c, returns all records,
   taking sortorder into account.
   """
   q = 'select * from scripts'
   if sortorder[0] == '0':
      q = q + ' order by t,n'
   elif sortorder[0] == '1':
      q = q + ' order by d'
   else:
      q = q + ' order by f'
   if sortorder[1] == '+':
      q = q + ' asc'
   else:
      q = q + ' desc'
   c.execute(q)
   return c.fetchall()

def PackTuple(t):
   """
   Packs the tuple as string with items
   separated by colons.  Notice padding!
   """
   s = t[0] + '-' + str(int(t[1])) + ':'
   s = s + str(t[2]) + ':' + t[3] + ':'
   r = s + (buffer - len(s))*' '
   return r

def main():
   """
   Accepts connection and sends records.
   """
   db = MySQLdb.connect(db='OurPyFiles')
   cr = db.cursor()
   nb = CountRecords(cr)
   sortclient, server = ConnectClient()
   sortclient.send(str(nb))
   print 'wait for submit client'
   submitclient, adr = server.accept()
   print 'submit client is connected'
   submitclient.send(str(nb))
   sortorder = submitclient.recv(buffer)
   print 'received sort order \"' + sortorder + '\"'
   R = RetrieveRecords(cr,sortorder)
   print 'sending records ...'
   for i in range(0,len(R)):
      submitclient.send(PackTuple(R[i]))
   print 'closing connection'
   server.close()

main()