"""
Created By: Nick Verbeck
Date: 7/25/2007
"""

import MySQLdb
import md5
from threading import *
import thread

class MySQLPool:
    """
    MySQL Connection Pool Manager
    
    Uses The Borg Pattern to store all connections for the application
    """
    
    #Dictionary used for storing all Locks and connections
    __Pool = {} 
    
    def __init__(self, silent = False):
        #This is where we use The Borg pattern to store the Pool between each instance
        self.__dict__ = self.__Pool
        
        #Create a lock for the whole collection if it doesn't already exist
        if not self.__Pool.has_key('lock'):
            self.__Pool['lock'] = Condition()
            
        #Create the connection collection dictionary if it doesn't already exist
        if not self.__Pool.has_key('conn'):
            self.__Pool['conn'] = {}
            
        #This variable is used for turning on/off all the print statments
        self.silent = silent
        
        #Notify the console that the instance is created
        if len(self.__Pool['conn']) == 0 and not self.silent:
            print "MySQLPool.__init__() \n\tMySQL Pool Started"
        
    def Terminate(self):
        """
        Function to loop though all the connections
        and commit all queries and close all the connections.
        
        This should be called at the end of your application.
        """
        
        for key in self.__Pool['conn']:
            try:
                self.__Pool['conn'][key]['conn'].commit()
                self.__Pool['conn'][key]['conn'].close()
                self.__Pool['conn'][key]['lock'] = None
            except Exception, e:
                print e.args
            
        if not self.silent:
            print "MySQLPool.__del__() \n\tMySQL Pool Terminated"
        
    def GetConnection(self, connInfo = {"host":None,"user":None,"passwd":None,"db":None}):
        """
        Return a current open connection if one exists.
        If not a new connection is created and added to the pool.
        """
        
        #Lock the Connection Collection/Pool
        self.__Pool['lock'].acquire()
        
        #Create an MD5 hash to use as the dicionary key
        key = md5.new(connInfo["host"] + connInfo["user"] + connInfo["passwd"] + connInfo["db"]).hexdigest()
        
        #Check if the connection already exists in the dicionary/pool
        if self.__Pool['conn'].has_key(key):
            #Connection was found
            if not self.silent:
                print "MySQLPool.GetConnection() \n\tReturing Connection"
        else:
            #connection wasn't found. Creating a new one
            if not self.silent:
                print "MySQLPool.GetConnection() \n\tCreating a new Connection"
            
            #Start a new dictionary for storing this connection
            self.__Pool['conn'][key] = {}
            
            #Create the new connection
            self.__Pool['conn'][key]['conn'] = MySQLdb.connect(host=connInfo["host"], user=connInfo["user"], passwd=connInfo["passwd"], db=connInfo["db"])
            
            #Create a new lock for this connection
            self.__Pool['conn'][key]['lock'] = Condition()
        
        #Release the Lock on the Connection Collection/Pool
        self.__Pool['lock'].release()
        return self.__Pool['conn'][key]
        
class MySQL:
    """
    Class used for exicuting MySQL queries using the connection pool
    """
    
    def __init__(self, connInfo = {"host":None,"user":None,"passwd":None,"db":None}, silent = False):
        #Print to console this instance was created
        if not silent:
            print "MySQL.__init__() \n\tCreating a new MySQL Query Object"
        
        #Get a new instance of the connection pool
        Pool = MySQLPool(silent)
        
        #Get a connection to use from the pool
        self.conn = Pool.GetConnection(connInfo)
        
        #Dicionary used for storing the SQL results
        self.record = {}
        
        #Number of rows returned from the query
        self.rowcount = 0
        
        #This variable is used for turning on/off all the print statments
        self.silent = silent
        
    def Query(self, query, args = None):
        #Lock the current connection. Just incase another thread tries to query the DB using the same connection
        self.conn['lock'].acquire()
        
        #Print to console that we are exicuting a new query
        if not self.silent:
            print "MySQL.Query() \n\tNew MySQL Query"
        
        #Create a new Cursor and execute the query.
        cursor = self.conn['conn'].cursor(MySQLdb.cursors.DictCursor)
        cursor.execute(query, args)
        self.rowcount = cursor.rowcount
        
        #Get all rows and assign them to the internal result set
        self.record = cursor.fetchall()
        
        #Close cursor
        cursor.close()
        
        #Release the lock on the connection. So the other threads can now run there queries.
        self.conn['lock'].release()