from win32com.client import Dispatch
import threading, pythoncom, DateTime

# Set this to 1 to debug SQL queries
_debug = 0

# We'll use this thread lock to avoid accidentally creating more than one
# persistent, permanent connection to a given data source.
_threadlock = threading.Lock()

# Like the Nz in Access
def Nz(value, default=""):
    if value == None:
        return default
    else:
        return value

def sqldate(date):
    return date.Format("'%Y-%m-%d'")

def sqldatetime(date):
    return date.Format("'%Y-%m-%d %H:%M:%S'")

# This class can be mixed into a Page-derived class to give it
# the ability to talk to MyDatabase using ADO
# connection pooling.
class DatabaseMixin:
    def connection(self, connection_string='MyDatabase'):
        # See if the application is storing a permanent connection with the given
        # connection string; if not, create it
        _threadlock.acquire()
        try:
            try:
                appconnections = self.application().permanent_connections
            except AttributeError:
                appconnections = self.application().permanent_connections = {}
            # Store a permanently open connection to the database if one isn't already
            # stored there
            if not appconnections.has_key(connection_string):
                conn = Dispatch('ADODB.Connection')
                conn.Open(connection_string)
                appconnections[connection_string] = conn
        finally:
            _threadlock.release()
        # Now open up a connection to the database and return it.  This
        # connection will come out of the ADO connection pool due to
        # the magic of ADO connection pooling.  Basically, as long
        # as your application has at least one open connection to a
        # given data source at one time, ADO will pool the connections
        # to that source.
        conn = Dispatch('ADODB.Connection')
        conn.Open(connection_string)
        return conn

    def recordset(self, Source, ActiveConnection=None, CursorType=-1, LockType=-1, Options=-1):
        if _debug: print Source
        rs = Dispatch('ADODB.Recordset')
        if not ActiveConnection:
            ActiveConnection = self.connection()
        rs.Open(Source, ActiveConnection, CursorType, LockType, Options)
        return Recordset(rs)

# This is a wrapper around an ADO or DAO recordset that allows the following
# shortcuts to access the records and fields:
#
# rs = Recordset(adorecordset)
# print rs.FieldName1, rs.FieldName2
#
# for record in rs:
#     print record.FieldName1, record.FieldName2
#
# Additionally, this wrapper converts datetimes into DateTime objects automatically.
class Recordset:
    def __init__(self, recordset):
        self.recordset = recordset
        self._fields = []
        for field in recordset.Fields:
            self._fields.append( field.Name )
        self._recordnum = 0

    def __del__(self):
        try:
            self.recordset.Close()
        except pywintypes.com_error:
            pass
        del self.recordset

    def __getattr__(self, name):
        if name in self._fields:
            value = self.recordset.Fields(name).Value
            if type(value) == pythoncom.PyTimeType:
                value = DateTime.DateTimeFromCOMDate(value)
            return value
        else:
            return getattr(self.recordset, name)

    def __getitem__(self, i):
        if i != self._recordnum:
            raise RuntimeError, "accessing records out of order"
        if self.recordset.EOF:
            raise IndexError, "EOF reached"
        record = self.record()
        self._recordnum = self._recordnum + 1
        self.recordset.MoveNext()
        return record

    def record(self):
        record = Record()
        for field in self._fields:
            value = self.recordset.Fields(field).Value
            if type(value) == pythoncom.PyTimeType:
                value = DateTime.DateTimeFromCOMDate(value)
            setattr(record, field, value)
        return record

# Dummy class used by Recordset above.
class Record:
    pass