'''MySQL database access

Copyright 2004-2005 AG Projects
'''

__all__ = ['MySQL', 'MySQLError', 'DatabaseError', 'literal', 'MySQLdb']

import MySQLdb
from _mysql import escape_dict
from MySQLdb.constants import CR
from threading import RLock
from utilities import *
from configuration import readSettings, ConfigSection


class MySQLConfig(ConfigSection):
    user = ''
    password = ''
    host = 'localhost'
    database = 'radius'

## We use this to overwrite some of the settings above on a local basis if needed
readSettings('Database', MySQLConfig)

## Specific converters for MySQL
def unchanged(obj, d):
    return obj
class literal(str):
    pass

from MySQLdb.converters import conversions as _mysql_conversions
conversions = _mysql_conversions.copy()
conversions[literal] = unchanged

## Exceptions

MySQLError = MySQLdb.Error
class DatabaseError(Exception): pass


## Classes

class MySQL(object):
    '''
    Handles basic connection to a MySQL server: connecting, querying, logging.
    Connection can be shared among subclasses and instances as long as they
    use the same username/password/host/database parameters
    '''

    class ThreadSafeConnection:
        def __init__(self, user, passwd, host, db):
            try:
                self.db = MySQLdb.connect(host, user, passwd, db, conv=conversions)
            except MySQLError, why:
                abort("cannot connect to the MySQL server: %s" % why[1])
            self.lock = RLock() ## We need a recursive lock here

    _connections = {}
    _lock = RLock()

    def __init__(self, user=None, passwd=None, host=None, db=None, shared=True):
        user   = user   or MySQLConfig.user
        passwd = passwd or MySQLConfig.password
        host   = host   or MySQLConfig.host
        db     = db     or MySQLConfig.database

        connectionId = "%s%s%s%s" % (user, passwd, host, db)

        if shared:
            MySQL._lock.acquire()
            try:
                try:
                    conn = MySQL._connections[connectionId]
                except LookupError:
                    conn = MySQL.ThreadSafeConnection(user, passwd, host, db)
                    MySQL._connections[connectionId] = conn
            finally:
                MySQL._lock.release()
        else:
            conn = MySQL.ThreadSafeConnection(user, passwd, host, db)

        self.shared = shared
        self.conn = conn
        self.autocommit = self.conn.db.autocommit
        self.commit = self.conn.db.commit
        self.rollback = self.conn.db.rollback
        self.affected_rows = self.conn.db.affected_rows
        self.literal = self.conn.db.literal
        self.escape = self.conn.db.escape
        self.escape_string = self.conn.db.escape_string
        self.__cursor  = self.conn.db.cursor()
        self.__dcursor = self.conn.db.cursor(MySQLdb.cursors.DictCursor)
        self.cursor = self.__cursor
        self.fetchall = self.cursor.fetchall
        self.fetchone = self.cursor.fetchone
        self.fetchmany = self.cursor.fetchmany
        self.mapping = False
        self.autocommit(True)

    def getmapping(self):
        return (self.cursor == self.__dcursor)
    def setmapping(self, val):
        self.cursor = (val and self.__dcursor) or self.__cursor
        self.fetchall = self.cursor.fetchall
        self.fetchone = self.cursor.fetchone
        self.fetchmany = self.cursor.fetchmany

    mapping = property(getmapping, setmapping)
    del(getmapping, setmapping)
    
    def __del__(self):
        try:
            self.__cursor.close()
            self.__dcursor.close()
        except:
            pass
        if hasattr(self, 'shared') and not self.shared:
            self.conn.db.close()

    def begin(self):
        self.conn.db.query('BEGIN')

    def query(self, query):
        self._query = query
        for c in range(0, 3):
            try:
                return self.cursor.execute(query)
            except MySQLdb.OperationalError, why:
                if why[0] not in (CR.SERVER_GONE_ERROR, CR.SERVER_LOST):
                    raise
        raise

    def insert(self, table, data):
        elements = len(data)
        fields = values = ", ".join(elements * ["%s"])
        data = self.literal(data) ## quote everything we need to quote
        items = tuple([table, ] + data.keys() + data.values())
        query = ("INSERT INTO %s (" + fields + ") VALUES (" + values + ")") % items
        return self.query(query)

    def delete(self, table, match, conditions={}):
        match = self.literal(match) ## quote everything we need to quote
        pairs = ['%s%s%s' % (k, conditions.get(k, '='), match[k]) for k in match]
        query = "DELETE FROM %s WHERE %s" % (table, " AND ".join(pairs))
        return self.query(query)

    def update(self, table, data, match, conditions={}):
        if not data:
            return 0
        data = self.literal(data)   ## quote everything we need to quote
        match = self.literal(match) ## quote everything we need to quote
        dataPairs  = ['%s=%s' % elem for elem in data.items()]
        matchPairs = ["%s%s%s" % (k, conditions.get(k, '='), match[k]) for k in match]
        dataString = ', '.join(dataPairs)
        matchString = ' AND '.join(matchPairs)
        query = "UPDATE %s SET %s WHERE %s" % (table, dataString, matchString)
        return self.query(query)

    def select(self, table, columns, match, conditions={}):
        match = self.literal(match) ## quote everything we need to quote
        pairs = ["%s%s%s" % (k, conditions.get(k, '='), match[k]) for k in match]
        retString = ', '.join(columns)
        matchString = ' AND '.join(pairs) or '(1=1)'
        query = "SELECT %s FROM %s WHERE %s" % (retString, table, matchString)
        return self.query(query)

    def lock(self, tables, type=None):
        type = type or 'write'
        assert type=='write' or type=='read'
        n = len(tables)
        if n==0:
            return 1
        query = (("LOCK TABLES %s %s" + (n-1)*", %s %s") %
                 reduce(lambda x, y: x+y, zip(tables, n*(type,))))
        return self.query(query)

    def unlock(self):
        return self.query('UNLOCK TABLES')

    def matchedRows(self):
        try:    return int(self.conn.db.info().split('matched:')[1].split()[0])
        except: raise DatabaseError, "cannot determine number of matched rows"


