root/trunk/storage/storeado.py

##import sys
### Put COM in free-threaded mode. This first thread will have
### CoInitializeEx called automatically when pythoncom is imported.
##sys.coinit_flags = 0
##import pythoncom

import win32com.client
import pywintypes
import datetime
try:
    import cPickle as pickle
except ImportError:
    import pickle

try:
    import fixedpoint
except ImportError:
    pass

import dejavu
from dejavu import storage, logic
from dejavu.storage import db
from recur import sane_time

adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3

adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4

adUseClient = 3

# 12/30/1899, the zero-Date for ADO = 693594
zeroHour = datetime.date(1899, 12, 30).toordinal()


def time_from_com(com_date):
    """Return a valid (day, datetime.time) from a COM date or time object."""
    hour, mins = divmod(86400 * (float(com_date) % 1), 3600)
    mins, sec = divmod(mins, 60)
    # Must do both int() and round() or we'll be up to 1 second off.
    hour = int(round(hour))
    mins = int(round(mins))
    sec = int(round(sec))
    return sane_time(0, hour, mins, sec)


class AdapterFromADO(db.AdapterFromDB):
    """Coerce incoming values from ADO to Dejavu datatypes."""
    
    def coerce_datetime_datetime(self, value, coltype):
        # Illegal Date/Time values will crash the
        # app when using value.Format(). Therefore,
        # grab the value and figure the date ourselves.
        # Use 1-second resolution only.
        if isinstance(value, basestring):
            return datetime.datetime(int(value[0:4]), int(value[4:6]),
                                     int(value[6:8]))
        else:
            # For some reason, we need both float and int.
            aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
            day, aTime = time_from_com(value)
            return datetime.datetime.combine(aDate, aTime)
    
    def coerce_datetime_date(self, value, coltype):
        # See coerce_datetime
        if isinstance(value, basestring):
            return datetime.date(int(value[0:4]), int(value[4:6]),
                                 int(value[6:8]))
        else:
            return datetime.date.fromordinal(int(float(value)) + zeroHour)
    
    def coerce_datetime_time(self, value, coltype):
        # See coerce_datetime
        day, aTime = time_from_com(value)
        return aTime
    
    def coerce_fixedpoint_FixedPoint(self, value, coltype):
        if coltype == 0x06:
            # Currency
            value = value[1] / 10000.0
        return fixedpoint.FixedPoint(value)
    
    def coerce_float(self, value, coltype):
        if coltype == 0x06:
            # Currency
            value = value[1] / 10000.0
        return float(value)
    
    def coerce_int(self, value, coltype):
        if coltype == 0x0b:
            # Boolean
            return value != 0
        return int(value)
    
    coerce_bool = coerce_int
    
    def coerce_unicode(self, value, coltype):
        if isinstance(value, unicode):
            # For some reason, inValue is already a unicode object.
            return value
        if isinstance(value, str):
            try:
                return unicode(value, "ISO-8859-1")
            except UnicodeError:
                raise StandardError(type(value))
        return unicode(value)



class AdapterToADOFields(storage.Adapter):
    """Coerce outgoing values from Dejavu datatypes to ADO.Field types."""
    
    def noop(self, value):
        return value
    
    def coerce_bool(self, value):
        if value:
            return True
        return False
    
    def coerce_datetime_datetime(self, value):
        if value is None:
            return None
        return self.coerce_datetime_date(value) + self.coerce_datetime_time(value)
    
    def coerce_datetime_date(self, value):
        if value is None:
            return None
        return value.toordinal() - zeroHour
    
    def coerce_datetime_time(self, value):
        if value is None:
            return None
        return ((value.second + (value.minute * 60) + (value.hour * 3600))
                / 86400.0)
    
    def pickle(self, value):
        # We must not use a pickle format other than 0, because binary
        # strings are not safe for all DB string fields.
        return pickle.dumps(value)
    
    coerce_dict = pickle
    
    def coerce_fixedpoint_FixedPoint(self, value):
        if value is None:
            return None
        return float(value)
    
    coerce_float = noop
    coerce_int = noop
    
    coerce_list = pickle
    
    coerce_long = noop
    coerce_str = noop
    
    coerce_tuple = pickle
    
    coerce_unicode = noop


class ADOSQLDecompiler(db.SQLDecompiler):
    
    def visit_COMPARE_OP(self, lo, hi):
        op2, op1 = self.stack.pop(), self.stack.pop()
        if op1 is db.cannot_represent or op2 is db.cannot_represent:
            self.stack.append(db.cannot_represent)
            return
        
        op = lo + (hi << 8)
        if op in (6, 7):     # in, not in
            # Looking for text in a field. Use Like (reverse terms).
            # LIKE is case-insensitive in MS SQL Server (and there
            # doesn't seem to be a way around it). Use icontainedby
            # and just mark imperfect.
            value = self.dejavu_icontainedby(op1, op2)
            if op == 7:
                value = "NOT " + value
            self.stack.append(value)
            self.imperfect = True
        elif op1 == 'NULL':
            if op == 2:
                self.stack.append(op2 + " IS NULL")
            elif op == 3:
                self.stack.append(op2 + " IS NOT NULL")
            else:
                raise ValueError("Non-equality Null comparisons not allowed.")
        elif op2 == 'NULL':
            if op == 2:
                self.stack.append(op1 + " IS NULL")
            elif op == 3:
                self.stack.append(op1 + " IS NOT NULL")
            else:
                raise ValueError("Non-equality Null comparisons not allowed.")
        else:
            if (isinstance(op2, db.ConstWrapper)
                and isinstance(op2.basevalue, basestring)):
                # ADO comparison operators for strings are case-insensitive
                # by default. Rather than determine which columns in the DB
                # might be case-sensitive, just flag them all as imperfect.
                # TODO: might be possible to cast both to varbinary, but
                # that may cause problems with unicode columns.
                self.imperfect = True
            self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
    
    def column_name(self, name):
        return '%s.[%s]' % (self.tablename, name)
    
    # --------------------------- Dispatchees --------------------------- #
    
    def attr_startswith(self, tos, arg):
        self.imperfect = True
        return tos + " LIKE '" + arg.strip("'\"") + "%'"
    
    def attr_endswith(self, tos, arg):
        self.imperfect = True
        return tos + " LIKE '%" + arg.strip("'\"") + "'"
    
    def containedby(self, op1, op2):
        self.imperfect = True
        if isinstance(op1, ConstWrapper):
            # Looking for text in a field. Use Like (reverse terms).
            return op2 + " LIKE '%" + op1.strip("'\"") + "%'"
        else:
            # Looking for field in (a, b, c)
            atoms = [self.adapter.coerce(x) for x in op2.basevalue]
            return op1 + " IN (" + ", ".join(atoms) + ")"
    
    def dejavu_icontainedby(self, op1, op2):
        if isinstance(op1, db.ConstWrapper):
            # Looking for text in a field. Use Like (reverse terms).
            # LIKE is already case-insensitive in MS SQL Server;
            # so don't use LOWER().
            value = op2 + " LIKE '%" + op1.strip("'\"") + "%'"
        else:
            # Looking for field in (a, b, c)
            atoms = [self.adapter.coerce(x) for x in op2.basevalue]
            value = op1 + " IN (" + ", ".join(atoms) + ")"
        return value
    
    def dejavu_istartswith(self, x, y):
        # Like is already case-insensitive in ADO; so don't use LOWER().
        y = y.strip("'\"")
        return x + " LIKE '" + y + "%'"
    
    def dejavu_iendswith(self, x, y):
        # Like is already case-insensitive in ADO; so don't use LOWER().
        y = y.strip("'\"")
        return x + " LIKE '%" + y + "'"
    
    def dejavu_ieq(self, x, y):
        # = is already case-insensitive in ADO.
        return x + " = " + y
    
    def dejavu_now(self):
        return "getdate()"
    
    def dejavu_today(self):
        return "DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"
    
    def func__builtin___len(self, x):
        return "Len(" + x + ")"


class StoreMultiIteratorADO(object):
    """Iterator for populating Units (from multiple classes) from storage."""
    
    def __init__(self, store, unitClass, expr, pairs):
        self.store  = store
        self.unitClass = unitClass
        self.expr = expr
        self.pairs = pairs
        self.fieldTypes = []
        
        sel = store.multiselect(unitClass, expr, pairs)
        self.sql, self.imperfect, self.columns = sel
    
    def populate_unit(self, unit, row):
        """Populate a Unit from a database row."""
        consume = AdapterFromADO().consume
        cls = unit.__class__
        for key in cls.properties():
            try:
                col = self.columns.index((cls, key))
            except ValueError, x:
                x.args += (cls, key)
                raise x
            else:
                consume(unit, key, self.data[col][row], self.fieldTypes[col])
    
    def load_data(self):
        anRS = self.store.fetch(self.sql)
        
        for col, x in enumerate(anRS.Fields):
            self.fieldTypes.append(x.Type)
        
        self.data = []
        if not(anRS.BOF and anRS.EOF):
            # We tried .MoveNext() and lots of Fields.Item() calls.
            # Using GetRows() beats that time by about 2/3.
            self.data = anRS.GetRows()
        anRS.Close()
    
    def units(self):
        self.load_data()
        if len(self.data) > 0:
            for row in range(len(self.data[0])):
                unit = self.unitClass()
                self.populate_unit(unit, row)
                # If our SQL is imperfect, don't yield it to the
                # caller unless it passes evaluate().
                if (not self.imperfect) or self.expr.evaluate(unit):
                    cls, expr = self.pairs[0]
                    farUnit = cls()
                    self.populate_unit(farUnit, row)
                    if farUnit.ID is None:
                        yield unit, None
                    elif ((not self.imperfect) or expr is None
                          or expr.evaluate(farUnit)):
                        yield unit, farUnit



class StorageManagerADO(db.StorageManagerDB):
    """StoreManager to save and retrieve Units via ADO 2.7.
    
    You must run makepy on ADO 2.7 before installing.
    """
    
    close_connection_method = 'Close'
    decompiler = ADOSQLDecompiler
    fromAdapter = AdapterFromADO()
    
    def connatoms(self):
        atoms = {}
        for pair in self.connstring.split(";"):
            if pair:
                k, v = pair.split("=", 1)
                atoms[k.upper().strip()] = v.strip()
        return atoms
    
    def identifier(self, *atoms):
        ident = ''.join(map(str, atoms))
        return '[' + ident + ']'
    
    def _get_conn(self):
        conn = win32com.client.Dispatch(r'ADODB.Connection')
        try:
            conn.Open(self.connstring)
            return conn
        except pywintypes.com_error, x:
            if x.args[2][5] == -2147467259:
                msg = x.args[2][2]
                if (
                    # SQL Server: "Cannot open database requested in login
                    # 'dejavu_test'. Login fails."
                    msg.startswith("Cannot open database") or
                    # MSAccess: "Could not find file
                    # 'C:\Python23\Lib\site-packages\dejavu\storage\zoo.mdb'."
                    msg.startswith("Could not find file")):
                    if self.CreateIfMissing:
                        self.create_database()
                        conn.Open(self.connstring)
                        return conn
            raise
    
    def _join(self, path=[]):
        if not path:
            return u''
        firstcls = path.pop(0)
        if not path:
            return firstcls.__name__
        
        spath = self.arena.associations.shortest_path(firstcls, path[0])
        spath.pop(0)
        cls = spath[0]
        
        p = self.prefix
        i = self.identifier
        left = i(p, firstcls.__name__)
        right = i(p, cls.__name__)
        if len(spath) == 1:
            child = right
        else:
            child = u"(%s)" % self._join(spath)
        leftkey, rightkey = firstcls._associations[cls]
        
        return (u"%s LEFT JOIN %s ON %s.%s = %s.%s" %
                (left, child, left, leftkey, right, rightkey))
    
    def multiselect(self, firstcls, firstexpr, pairs):
        firstwhere, imp = self.where(firstcls, firstexpr)
        cols = [(firstcls, k) for k in firstcls.properties()]
        
        if len(pairs) != 1:
            raise ValueError("Multiselect does not yet work on multiple pairs.")
        
        for cls, expr in pairs:
            if expr is None:
                expr = logic.Expression(lambda x: True)
            
            j = self._join([firstcls, cls])
            
            w, new_imp = self.where(cls, expr)
            imp |= new_imp
            if w and w != "TRUE":
                w = " WHERE %s AND %s" % (w, firstwhere)
            else:
                w = " WHERE %s" % firstwhere
            
            cols += [(cls, k) for k in cls.properties()]
            colnames = ["%s.%s" % (self.identifier(self.prefix, colcls.__name__),
                                   self.identifier(k))
                        for colcls, k in cols]
            
            statement = "SELECT %s FROM %s%s" % (u', '.join(colnames), j, w)
            
            return statement, imp, cols
    
    def execute(self, query, conn=None):
        if conn is None:
            conn = self.connection()
        try:
            conn.Execute(query)
        except pywintypes.com_error, x:
            x.args += (query, )
            conn = None
            raise x
    
    def fetch(self, query, conn=None):
        """fetch(query, conn=None) -> rowdata, columns."""
        if conn is None:
            conn = self.connection()
        
        res = win32com.client.Dispatch(r'ADODB.Recordset')
        # Uncomment the following to get .Recordcount
        # res.CursorLocation = adUseClient
        try:
            # 'conn' will be a ConnectionWrapper object, which .Open
            # won't accept. Pass the unwrapped connection instead.
            res.Open(query, conn.conn, adOpenForwardOnly, adLockReadOnly)
        except pywintypes.com_error, x:
            try:
                res.Close()
            except:
                pass
            x.args += (query, )
            raise x
        
        columns = [(x.Name, x.Type) for x in res.Fields]
        
        data = []
        if not(res.BOF and res.EOF):
            # We tried .MoveNext() and lots of Fields.Item() calls.
            # Using GetRows() beats that time by about 2/3.
            data = res.GetRows()
            # Convert cols x rows -> rows x cols
            data = zip(*data)
        res.Close()
        
        return data, columns


###########################################################################
##                                                                       ##
##                             SQL Server                                ##
##                                                                       ##
###########################################################################


class AdapterToADOSQL_SQLServer(db.AdapterToSQL):
    
    escapes = [("'", "''"), ("%", "[%]"), ("_", "[_]")]
    
    # These are not the same as coerce_bool (which is used on one side of 
    # a comparison). Instead, these are used when the whole (sub)expression
    # is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
    bool_true = "(1=1)"
    bool_false = "(1=0)"
    
    def coerce_bool(self, value):
        if value:
            return '(1=1)'
        return '(1=0)'


class FieldTypeAdapter_SQLServer(db.FieldTypeAdapter):
    
    numeric_max_precision = 38
    
    def coerce_datetime_datetime(self, cls, key):
        return u"DATETIME"
    
    def coerce_datetime_date(self, cls, key):
        return u"DATETIME"
    
    def coerce_datetime_time(self, cls, key):
        return u"DATETIME"
    
    def coerce_str(self, cls, key):
        # The bytes hint does not reflect the usual 4-byte base for varchar.
        prop = getattr(cls, key)
        bytes = int(prop.hints.get(u'bytes', '0'))
        if bytes == 0:
            # Okay, what the @#$%& is wrong with Redmond??!?! We can't even
            # compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
            # warn the deployer with less swearing.
            import warnings
            warnings.warn("You have defined a string property without "
                          "limiting its length. Microsoft SQL Server does "
                          "not allow comparisons on string fields larger "
                          "than 8000 characters. Some of your data may be "
                          "truncated.")
            bytes = 8000
        # 8000 *bytes* is the absolute upper limit, based on T_SQL docs for
        # varchar/varbinary. If there are further fields defined for the
        # class, or the codepage uses a double-byte character set, we still
        # might exceed the max size (8060) for a record. We could calc the
        # total requested record size, and adjust accordingly. Meh.
        return u"VARCHAR(%s)" % bytes


class StorageManagerADO_SQLServer(StorageManagerADO):
    
    typeAdapter = FieldTypeAdapter_SQLServer()
    toAdapter = AdapterToADOSQL_SQLServer()
    
    def __init__(self, name, arena, allOptions={}):
        db.StorageManagerDB.__init__(self, name, arena, allOptions)
        
        self.connstring = allOptions[u'Connect']
        atoms = self.connatoms()
        self.dbname = atoms[u'INITIAL CATALOG']
        self.cursorType = int(allOptions.get(u'CursorType', adOpenDynamic))
        self.lockType = int(allOptions.get(u'LockType', adLockOptimistic))
    
    def create_database(self):
        # This method hasn't been tested yet for SQL server.
        adoconn = win32com.client.Dispatch(r'ADODB.Connection')
        atoms = self.connatoms()
        atoms['INITIAL CATALOG'] = "tempdb"
        adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
        adoconn.Execute("CREATE DATABASE %s" % self.identifier(self.dbname))
        adoconn.Close()
    
    def drop_database(self):
        adoconn = win32com.client.Dispatch(r'ADODB.Connection')
        atoms = self.connatoms()
        atoms['INITIAL CATALOG'] = "tempdb"
        adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
        adoconn.Execute("DROP DATABASE %s;" % self.identifier(self.dbname))
        adoconn.Close()


###########################################################################
##                                                                       ##
##                             MS Access                                 ##
##                                                                       ##
###########################################################################


class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
    sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
    
    def dejavu_now(self):
        return "Now()"
    
    def dejavu_today(self):
        return "DateValue(Now())"
    
    def dejavu_year(self, x):
        return "Year(" + x + ")"


class FieldTypeAdapter_MSAccess(db.FieldTypeAdapter):
    
    numeric_max_precision = 28
    
    def coerce_str(self, cls, key):
        # The bytes hint shall not reflect the usual 4-byte base for varchar.
        prop = getattr(cls, key)
        bytes = int(prop.hints.get(u'bytes', '0'))
        if bytes and bytes <= 255:
            # 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
            return u"VARCHAR(%s)" % bytes
        else:
            # MEMO is 1 gigabyte when set programatically (only 64K when set
            # in Access UI). But then, 1 GB is the limit for the whole DB.
            return u"MEMO"


class AdapterToADOSQL_MSAccess(db.AdapterToSQL):
    """Coerce Expression constants to ADO SQL."""
    
    escapes = [("'", "''"), ("%", "[%]"), ("_", "[_]")]
    
    def coerce_datetime_datetime(self, value):
        return (u'#%s/%s/%s %02d:%02d:%02d#' %
                (value.month, value.day, value.year,
                 value.hour, value.minute, value.second))
    
    def coerce_datetime_date(self, value):
        return u'#%s/%s/%s#' % (value.month, value.day, value.year)
    
    def coerce_datetime_time(self, value):
        return u'#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)


class StorageManagerADO_MSAccess(StorageManagerADO):
    # Jet Connections and Recordsets are always free-threaded.
    
    decompiler = ADOSQLDecompiler_MSAccess
    typeAdapter = FieldTypeAdapter_MSAccess()
    toAdapter = AdapterToADOSQL_MSAccess()
    
    def __init__(self, name, arena, allOptions={}):
        db.StorageManagerDB.__init__(self, name, arena, allOptions)
        
        self.connstring = allOptions[u'Connect']
        atoms = self.connatoms()
        self.dbname = atoms.get(u'DATA SOURCE') or atoms.get(u'DATA SOURCE NAME')
        self.cursorType = int(allOptions.get(u'CursorType', adOpenDynamic))
        self.lockType = int(allOptions.get(u'LockType', adLockOptimistic))
        self.pool = None
    
    def create_database(self):
        # By not providing an Engine Type, it defaults to 5 = Access 2000.
        cat = win32com.client.Dispatch(r'ADOX.Catalog')
        cat.Create(self.connstring)
        cat.ActiveConnection.Close()
    
    def drop_database(self):
        import os
        # This should accept relative or absolute paths
        if os.path.exists(self.dbname):
            os.remove(self.dbname)


if __name__ == '__main__':
    # Auto generate .py support for ADO 2.7
    print 'Please wait while support for ADO 2.7 is verified...'
    CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
    win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)