# L-26 MCS 275 Fri 14 Mar 2008 : scripts_servdb.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.

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(1)
   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):
   """
   Given cursor c, returns all records.
   """
   q = 'select * from scripts'
   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)
   client, server = ConnectClient()
   client.send(str(nb))
   R = RetrieveRecords(cr)
   for i in range(0,len(R)):
      client.send(PackTuple(R[i]))
   server.close()

main()