# FAHdb.py
# object for interfacing with a Folding@home project's database, as built
# by the analysis module, FAHAnalysis3.
#
# Daniel L. Ensign
# Pande Group
# Department of Chemistry
# Stanford University 
# 

from FAHBaseType import FAHBaseType

try:
	import MySQLdb
except:
	print "WARNING: no MySQLdb detected for FAHdb"

class FAHdb( FAHBaseType ):

	def __init__( self, dbname, hostname="vspmf92", username="server", verbose=False ):
		self.dbname = dbname
		self.hostname = hostname
		self.username = username
		self.verbose = verbose

		# set up connection and cursor
		self.conn = self.connect()	
		if self.conn :
			self.cursor = self.conn.cursor() # doohickey that does the work

		# you can make a list of queries
		self._qlist = []
		self._nqueries = 0
		self._currentQuery = None

	def __del__( self ):
		self.close()	

	def addquery( self, query ):
		if not query :
			self._vmesg( "No query specified, none added" )
			return

		self._qlist.append( query )
		self._nqueries += 1 

		# if this is the first in the list, set current to zero
		if not self._currentQuery :
			self._currentQuery = 0

	def delquery( self, qnum = -1 ):
		pass

	def resetq( self ):
		self._currentQuery = 0

	def showquery( self, qnum = -1 ):
		# show query 'qnum' in the list
		# if qnum is -1, then show them all
		if qnum == -1 :
			for q in self._qlist :
				print q
		else:
			try:
				print self._qlist[ qnum ]
			except:
				self._vmesg( "No such query" )

	def connect( self ):
		# actually make the connection. This function is run automatically by __init__
		try:
			c = MySQLdb.connect( host = self.hostname, user = self.username, db = self.dbname )
		except:
			fields = ( self.dbname, self.hostname, self.username )
			self._vmesg( "Error connecting to '%s' on host '%s' user '%s'" % fields )
			c = None
		return c

	def close( self ):
		if self.conn:
			self.conn.close() 

	def query( self, query="" ):
		# wrapper for cursor.exectue(q) and cursor.fetchall(). 
		# returns rows.

		if not self.conn :
			self._vmesg( "Can't query, no connection" )
			return None

		# convenience for running a set of queries in a list
		if not query :
			try:
				query = self._qlist[ self._currentQuery ]
				self._currentQuery += 1
 
			except:
				self._vmesg( "No query" )
				return None

		# now that all the administration is over, let's make the query
		try:
			self.cursor.execute( query )
			dat = self.cursor.fetchall()
		except:
			self._vmesg( "Query error" )
			return None
		return dat

	def qtranspose( self, query="" ):
		# as self.query, but returns columns
		data = self.query( query )
		if data :
			ncols = len( data[0] )
			nrows = len( data )

			# generate empty lists
			tdata = []	
			col = 0
			while col < ncols :
				tdata.append( [] )
				col += 1
	
			# for each row ..
			row = 0
			while row < nrows :
				currentRow = data[ row ]
				
				# append each element to each column
				col = 0
				while col < ncols :
					tdata[ col ].append( currentRow[ col ] )
					col += 1

				row += 1

			# convert to tuples
			newtdata = []
			col = 0
			while col < ncols :
				newtdata.append( tuple( tdata[ col ] ) )
				col += 1
			tdata = tuple( newtdata )

		else:
			tdata = None

		return tdata