root/trunk/storage/db.py

"""Base classes and tools for writing database Storage Managers.

DATA TYPES
==========
Database Storage Manager modules are mostly adapters to support round-trip
data coercion:

Unit type -> [SQL repr ->] DB -> incoming Python value -> Unit type

Since Dejavu relies on external database servers for its persistence,
Python datatypes must be converted to column types in the DB. When writing
a StorageManager, you should make sure that your type conversions can handle
at least the following limitations: If possible, implement the type with no
limits. Also, follow UnitProperty.hints['bytes'] where possible. A value
of zero for hints['bytes'] implies no limit. If no value is given, try to
assume no limit, although you may choose whatever default size you wish
(255 is common for strings).

ENCODING ISSUES
===============
All SQL sent to the database must be strings, not unicode. You can set the
encoding of the Adapters (I may add a more centralized encoding context in
the future). We must use encoded strings so that we can mix encodings
within the same string; for example, we might have a DB which understands
utf8, but a pickle value which will be encoded in raw-unicode-escape inline
with that. All values, therefore, must be coerced before we try to join
them into an SQL statement string.

"""


try:
    # Builtin in Python 2.5?
    decimal
except NameError:
    try:
        # Module in Python 2.3, 2.4
        import decimal
    except ImportError:
        decimal = None

try:
    import fixedpoint
except ImportError:
    fixedpoint = None

import sys
for maxint_bytes in xrange(9):
    if sys.maxint <= 2 ** ((maxint_bytes * 8) - 1):
        break


import threading
import warnings


import dejavu
from dejavu import logic, storage, LOGSQL, xray
from dbmodel import *


class FieldTypeAdapter(object):
    """For a UnitProperty, return a database type.
    
    This base class is designed to work out-of-the-box with PostgreSQL 8.
    """
    
    # Max binary precision for floating-point columns (= 53 for PostgreSQL 8).
    float_max_precision = 53
    
    # Max decimal precision for NUMERIC columns (= 1000 for PostgreSQL 8).
    numeric_max_precision = 1000
    
    # "The actual storage requirement is two bytes for each group of four
    # decimal digits, plus eight bytes overhead." Note we omit the overhead.
    numeric_max_bytes = 500
    
    def coerce(self, cls, key):
        """Return a column object for the given UnitProperty (cls and key)."""
        prop = cls.property(key)
        
        # Obtain the DB type
        valuetype = prop.type
        mod = valuetype.__module__
        if mod == "__builtin__":
            xform = "coerce_%s" % valuetype.__name__
        else:
            xform = "coerce_%s_%s" % (mod, valuetype.__name__)
        xform = xform.replace(".", "_")
        try:
            xform = getattr(self, xform)
        except AttributeError:
            raise TypeError("'%s' is not handled by %s." %
                            (valuetype, self.__class__))
        dbtype = xform(cls, key)
        
        return dbtype
    
    def float_type(self, precision):
        """Return a datatype which can handle floats of the given binary precision."""
        if precision <= 24:
            return "REAL"
        else:
            # Python floats are implemented using C doubles;
            # actual precision depends on platform.
            # PostgreSQL DOUBLE is 53 binary-digit precision.
            return "DOUBLE PRECISION"
    
    def coerce_float(self, cls, key):
        prop = getattr(cls, key)
        # Note that 'precision' is binary digits, not decimal
        precision = int(prop.hints.get('precision', 0))
        if precision == 0:
            precision = self.float_max_precision
        elif precision > self.float_max_precision:
            warnings.warn("Float precision %s > maximum %s for %s.%s, "
                          "using %s. Values may be stored incorrectly."
                          % (precision, self.float_max_precision,
                             cls.__name__, key, self.__class__.__name__),
                          dejavu.StorageWarning)
            precision = self.float_max_precision
        return self.float_type(precision)
    
    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('bytes', 0))
        if bytes:
            return "VARCHAR(%s)" % bytes
        else:
            # TEXT is not an SQL standard, but it's common.
            return "TEXT"
    
    def coerce_dict(self, cls, key):
        return self.coerce_str(cls, key)
    def coerce_list(self, cls, key):
        return self.coerce_str(cls, key)
    def coerce_tuple(self, cls, key):
        return self.coerce_str(cls, key)
    def coerce_unicode(self, cls, key):
        return self.coerce_str(cls, key)
    
    def coerce_bool(self, cls, key): return "BOOLEAN"
    
    def coerce_datetime_datetime(self, cls, key): return "TIMESTAMP"
    def coerce_datetime_date(self, cls, key): return "DATE"
    def coerce_datetime_time(self, cls, key): return "TIME"
    
    # I was seriously disinterested in writing a parser for interval.
    def coerce_datetime_timedelta(self, cls, key):
        return self.coerce_float(cls, key)
    
    def coerce_decimal_Decimal(self, cls, key):
        prop = getattr(cls, key)
        precision = int(prop.hints.get('precision', decimal.getcontext().prec))
        if precision == 0:
            precision = self.numeric_max_precision
        elif precision > self.numeric_max_precision:
            warnings.warn("Decimal precision %s > maximum %s for %s.%s, "
                          "using %s. Values may be stored incorrectly."
                          % (precision, self.numeric_max_precision,
                             cls.__name__, key, self.__class__.__name__),
                          dejavu.StorageWarning)
            precision = self.numeric_max_precision
        
        # Assume most people use decimal for money; default scale = 2.
        scale = int(prop.hints.get('scale', 2))
        if scale > precision:
            scale = precision
        return "NUMERIC(%s, %s)" % (precision, scale)
    
    def coerce_decimal(self, cls, key):
        # If decimal ever becomes a builtin. Python 2.5?
        return self.coerce_decimal_Decimal(cls, key)
    
    def coerce_fixedpoint_FixedPoint(self, cls, key):
        prop = getattr(cls, key)
        # Note that fixedpoint has no theoretical precision limit.
        precision = int(prop.hints.get('precision', 0))
        if precision == 0:
            precision = self.numeric_max_precision
        elif precision > self.numeric_max_precision:
            warnings.warn("Fixedpoint precision %s > maximum %s for %s.%s, "
                          "using %s. Values may be stored incorrectly."
                          % (precision, self.numeric_max_precision,
                             cls.__name__, key, self.__class__.__name__),
                          dejavu.StorageWarning)
            precision = self.numeric_max_precision
        
        # Assume most people use fixedpoint for money; default scale = 2.
        scale = int(prop.hints.get('scale', 2))
        if scale > precision:
            scale = precision
        return "NUMERIC(%s, %s)" % (precision, scale)
    
    def int_type(self, bytes):
        """Return a datatype which can handle the given number of bytes."""
        if bytes == 1:
            return "BOOLEAN"
        elif bytes == 2:
            return "SMALLINT"
        elif bytes <= 4:
            return "INTEGER"
        elif bytes <= 8:
            # BIGINT is usually 8 bytes
            return "BIGINT"
        else:
            # Anything larger than 8 bytes, use decimal/numeric.
            # For PostgreSQL, "The actual storage requirement is two bytes
            # for each group of four decimal digits, plus eight bytes
            # overhead." Note we omit the overhead in our calculation.
            return "NUMERIC(%s, 0)" % (bytes * 2)
    
    def coerce_long(self, cls, key):
        prop = getattr(cls, key)
        bytes = int(prop.hints.get('bytes', self.numeric_max_precision))
        if bytes == 0:
            bytes = self.numeric_max_bytes
        elif bytes > self.numeric_max_bytes:
            warnings.warn("Long bytes %s > maximum %s for %s.%s, "
                          "using %s. Values may be stored incorrectly."
                          % (bytes, self.numeric_max_bytes,
                             cls.__name__, key, self.__class__.__name__),
                          dejavu.StorageWarning)
            bytes = self.numeric_max_bytes
        
        return self.int_type(bytes)
    
    def coerce_int(self, cls, key):
        prop = getattr(cls, key)
        bytes = int(prop.hints.get('bytes', maxint_bytes))
        if bytes == 0:
            bytes = maxint_bytes
        elif bytes > maxint_bytes:
            warnings.warn("Integer bytes %s > maximum %s for %s.%s, "
                          "using %s. Values may be stored incorrectly."
                          % (bytes, maxint_bytes,
                             cls.__name__, key, self.__class__.__name__),
                          dejavu.StorageWarning)
            bytes = maxint_bytes
        
        return self.int_type(bytes)



# --------------------------- Storage Manager --------------------------- #


class UnitClassWrapper(object):
    """Unit class wrapper, for use in parsing multiselect joins."""
    
    def __init__(self, wclass, db):
        self.cls = wclass
        self.db = db
        
        wclsname = wclass.__name__
        self.tablename = db[wclsname].name
        self.alias = ""
    
    def columns(self):
        """Return [(wclass, UnitProperty.key), ...], ['"tbl"."col"', ...]."""
        wclass = self.cls
        q = self.db.quote
        
        # Place the identifier properties first
        # in case others depend upon them.
        keys = list(wclass.identifiers) + [k for k in wclass.properties
                                           if k not in wclass.identifiers]
        cols = [(wclass, k) for k in keys]
        colnames = ['%s.%s' % (q(self.alias or self.tablename),
                               q(self.db.column_name(wclass.__name__, k)))
                    for k in keys]
        return cols, colnames
    
    def _joinname(self):
        q = self.db.quote
        if self.alias:
            return "%s AS %s" % (q(self.tablename), q(self.alias))
        else:
            return q(self.tablename)
    joinname = property(_joinname, doc=("Quoted table name for use in "
                                        "JOIN clause (read-only)."))
    
    def association(self, classes):
        for other in classes:
            ua = self.cls._associations.get(other.cls.__name__, None)
            if ua:
                nearClass = self.alias or self.tablename
                farClass = other.alias or other.tablename
                return ua, nearClass, farClass
            ua = other.cls._associations.get(self.cls.__name__, None)
            if ua:
                nearClass = other.alias or other.tablename
                farClass = self.alias or self.tablename
                return ua, nearClass, farClass
        return None


class StorageManagerDB(storage.StorageManager):
    """StoreManager base class to save and retrieve Units using a DB."""
    
    use_asterisk_to_get_all = False
    
    typeAdapter = FieldTypeAdapter()
    databaseclass = Database
    
    def __init__(self, name, arena, allOptions={}):
        storage.StorageManager.__init__(self, name, arena, allOptions)
        self.reserve_lock = threading.Lock()
        
        # Adapter Overrides
        def get_option(name):
            item = allOptions.get(name)
            if isinstance(item, basestring):
                item = xray.classes(item)
            return item
        
        adapter = get_option('Type Adapter')
        if adapter:
            self.typeAdapter = adapter
        
        adapter = get_option('Database Class')
        if adapter:
            self.databaseclass = adapter
        
        allOptions = dict([(str(k), v) for k, v in allOptions.iteritems()])
        name = allOptions.pop('name')
        self.db = self.databaseclass(name, **allOptions)
        self.db.log = self.arena.log
    
    def version(self):
        return self.db.version()
    
    def shutdown(self):
        self.db.disconnect()
    
    def consume(self, unit, key, value, coltype):
        try:
            expectedType = unit.__class__.property(key).type
            value = self.db.adapterfromdb.coerce(value, coltype, expectedType)
            unit._properties[key] = value
        except UnicodeDecodeError, x:
            x.reason += "[%s][%s][%s]" % (key, value, coltype)
            raise
        except Exception, x:
            x.args += (key, value, coltype)
            raise
    
    def recall(self, cls, expr=None):
        """Yield a sequence of Unit instances which satisfy the expression."""
        clsname = cls.__name__
        
        if expr is None:
            expr = logic.Expression(lambda x: True)
        sql, imperfect = self.db.select(cls.__name__, expr)
        data, col_defs = self.db.fetch(sql)
        if data:
            t = self.db[clsname]
            columns = dict([(col[0], (index, col[1])) for index, col
                            in enumerate(col_defs)])
            
            # Get specs on properties. Put the identifier properties
            # first, in case other fields depend upon them.
            props = []
            idnames = list(cls.identifiers)
            for key in idnames + [x for x in cls.properties if x not in idnames]:
                index, ftype = columns[t.columns[key].name]
                props.append((key, index, ftype))
            
            for row in data:
                unit = cls()
                for key, index, ftype in props:
                    value = row[index]
                    self.consume(unit, key, value, ftype)
                
                # If our SQL is imperfect, don't yield it to the
                # caller unless it passes expr(unit).
                if (not imperfect) or expr(unit):
                    unit.cleanse()
                    yield unit
    
    def reserve(self, unit):
        """reserve(unit). -> Reserve a persistent slot for unit."""
        self.reserve_lock.acquire()
        try:
            # First, see if our db subclass has a handler that
            # uses the DB to generate the appropriate identifier(s).
            seqclass = unit.sequencer.__class__.__name__
            seq_handler = getattr(self, "_seq_%s" % seqclass, None)
            if seq_handler:
                seq_handler(unit)
            else:
                self._manual_reserve(unit)
            unit.cleanse()
        finally:
            self.reserve_lock.release()
    
    def _manual_reserve(self, unit):
        """Use when the DB cannot automatically generate an identifier.
        The identifiers will be supplied by UnitSequencer.assign().
        """
        cls = unit.__class__
        t = self.db[cls.__name__]
        if not unit.sequencer.valid_id(unit.identity()):
            # Examine all existing IDs and grant the "next" one.
            id_fields = [t.columns[key].qname for key in cls.identifiers]
            data, cols = self.db.fetch('SELECT %s FROM %s;' %
                                       (', '.join(id_fields), t.qname))
            if data:
                # sqlite 2, for example, has empty cols tuple if no data.
                coerce = self.db.adapterfromdb.coerce
                coltypes = [cols[x][1] for x in xrange(len(cols))]
                expectedTypes = [getattr(cls, key).type
                                 for key in cls.identifiers]
                newdata = []
                for row in data:
                    newrow = []
                    for x, cell in enumerate(row):
                        newrow.append(coerce(cell, coltypes[x],
                                             expectedTypes[x]))
                    newdata.append(newrow)
                data = newdata
                del newdata
            cls.sequencer.assign(unit, data)
            del data
            del cols
        
        fields = []
        values = []
        for key in cls.properties:
            val = self.db.adaptertosql.coerce(getattr(unit, key))
            fields.append(t.columns[key].qname)
            values.append(val)
        
        fields = ", ".join(fields)
        values = ", ".join(values)
        self.db.execute('INSERT INTO %s (%s) VALUES (%s);' %
                        (t.qname, fields, values))
    
    def id_clause(self, unit):
        """Return an SQL expression for the identifiers of the given Unit."""
        cols = self.db[unit.__class__.__name__].columns
        c = self.db.adaptertosql.coerce
        pairs = ["%s = %s" % (cols[key].qname, c(getattr(unit, key)))
                 for key in unit.identifiers]
        return " AND ".join(pairs)
    
    def save(self, unit, forceSave=False):
        """save(unit, forceSave=False) -> Update storage from unit's data."""
        if unit.dirty() or forceSave:
            cls = unit.__class__
            t = self.db[cls.__name__]
            
            parms = []
            for key in cls.properties:
                if key not in cls.identifiers:
                    val = self.db.adaptertosql.coerce(getattr(unit, key))
                    parms.append('%s = %s' % (t.columns[key].qname, val))
            
            if parms:
                sql = ('UPDATE %s SET %s WHERE %s;' %
                       (t.qname, ", ".join(parms), self.id_clause(unit)))
                self.db.execute(sql)
            unit.cleanse()
    
    def destroy(self, unit):
        """destroy(unit). Delete the unit."""
        if self.use_asterisk_to_get_all:
            star = " *"
        else:
            star = ""
        self.db.execute('DELETE%s FROM %s WHERE %s;' %
                        (star, self.db[unit.__class__.__name__].qname,
                         self.id_clause(unit)))
    
    def view(self, cls, fields, expr=None):
        """view(cls, fields, expr=None) -> All value-tuples for given fields."""
        if expr is None:
            expr = logic.Expression(lambda x: True)
        
        sql, imperfect = self.db.select(cls.__name__, expr, fields)
        if imperfect:
            # ^%$#@! There's no way to handle imperfect queries without
            # creating all involved Units, which defeats the purpose of
            # view, which was a speed issue more than anything else.
            warnings.warn("The requested view() query for %s Units "
                          "cannot produce perfect SQL with a %s datasource. "
                          "It may take an absurd amount of time to run, "
                          "since each unit must be fully-formed. %s"
                          % (cls.__name__, self.__class__.__name__, expr),
                          dejavu.StorageWarning)
            for unit in self.recall(cls, expr):
                # Use tuples for hashability
                yield tuple([getattr(unit, f) for f in fields])
        else:
            data, columns = self.db.fetch(sql)
            actualTypes = [x[1] for x in columns]
            expectedTypes = [cls.property(x).type for x in fields]
            
            coerce = self.db.adapterfromdb.coerce
            # Use tuples for hashability
            for row in data:
                yield tuple([coerce(val, actualTypes[i], expectedTypes[i])
                             for i, val in enumerate(row)])
    
    def distinct(self, cls, fields, expr=None):
        """distinct(cls, fields, expr=None) -> Distinct values for given fields."""
        if expr is None:
            expr = logic.Expression(lambda x: True)
        
        sql, imperfect = self.db.select(cls.__name__, expr,
                                            fields, distinct=True)
        if imperfect:
            # ^%$#@! There's no way to handle imperfect queries without
            # creating all involved Units, which defeats the purpose of
            # distinct, which was a speed issue more than anything.
            warnings.warn("The requested distinct() query for %s Units "
                          "cannot produce perfect SQL with a %s datasource. "
                          "It may take an absurd amount of time to run, "
                          "since each unit must be fully-formed. %s"
                          % (cls.__name__, self.__class__.__name__, expr),
                          dejavu.StorageWarning)
            vals = {}
            for unit in self.recall(cls, expr):
                # Must use tuples for hashability
                val = tuple([getattr(unit, f) for f in fields])
                vals[val] = None
            return vals.keys()
        else:
            data, columns = self.db.fetch(sql)
            actualTypes = [x[1] for x in columns]
            expectedTypes = [cls.property(x).type for x in fields]
            
            coerce = self.db.adapterfromdb.coerce
            # Must use inner tuples for hashability in Sandbox.distinct()
            return [tuple([coerce(val, actualTypes[i], expectedTypes[i])
                           for i, val in enumerate(row)])
                     for row in data]
    
    def join(self, unitjoin):
        """Return an SQL FROM clause for the given unitjoin."""
        cls1, cls2 = unitjoin.class1, unitjoin.class2
        if isinstance(cls1, dejavu.UnitJoin):
            name1 = self.join(cls1)
            classlist1 = iter(cls1)
        else:
            # cls1 is a Unit class wrapper.
            name1 = cls1.joinname
            classlist1 = [cls1]
        
        if isinstance(cls2, dejavu.UnitJoin):
            name2 = self.join(cls2)
            classlist2 = iter(cls2)
        else:
            # cls2 is a Unit class wrapper.
            name2 = cls2.joinname
            classlist2 = [cls2]
        
        j = {None: "INNER", True: "LEFT", False: "RIGHT"}[unitjoin.leftbiased]
        
        # Find an association between the two halves.
        ua = None
        for clsA in classlist1:
            ua = clsA.association(classlist2)
            if ua:
                ua, nearClass, farClass = ua
                break
        if ua is None:
            msg = ("No association found between %s and %s." % (name1, name2))
            raise dejavu.AssociationError(msg)
        
        t = self.db
        near = '%s.%s' % (t.quote(t.table_name(nearClass)),
                          t.quote(t.column_name(nearClass, ua.nearKey)))
        far = '%s.%s' % (t.quote(t.table_name(farClass)),
                         t.quote(t.column_name(farClass, ua.farKey)))
        
        return "(%s %s JOIN %s ON %s = %s)" % (name1, j, name2, near, far)
    
    def multiselect(self, classes, expr):
        """Return an SQL SELECT statement, an imperfect flag, and column names."""
        
        # Create a new unitjoin tree where each class is wrapped.
        # Then we can tag the wrappers with metadata with impunity.
        seen = {}
        aliascount = [0]
        
        def wrap(unitjoin):
            cls1, cls2 = unitjoin.class1, unitjoin.class2
            if isinstance(cls1, dejavu.UnitJoin):
                wclass1 = wrap(cls1)
            else:
                wclass1 = UnitClassWrapper(cls1, self.db)
                if cls1 in seen:
                    aliascount[0] += 1
                    wclass1.alias = "t%d" % aliascount[0]
                else:
                    seen[cls1] = None
            if isinstance(cls2, dejavu.UnitJoin):
                wclass2 = wrap(cls2)
            else:
                wclass2 = UnitClassWrapper(cls2, self.db)
                if cls2 in seen:
                    aliascount[0] += 1
                    wclass2.alias = "t%d" % aliascount[0]
                else:
                    seen[cls2] = None
            return dejavu.UnitJoin(wclass1, wclass2, unitjoin.leftbiased)
        classes = wrap(classes)
        
        joins = self.join(classes)
        
        if expr is None:
            expr = logic.Expression(lambda *args: True)
        w, imp = self.db.where(list(classes), expr)
        
        cols = []
        colnames = []
        for wrapper in classes:
            c, names = wrapper.columns()
            cols.extend(c)
            colnames.extend(names)
        
        statement = ("SELECT %s FROM %s WHERE %s" %
                     (', '.join(colnames), joins, w))
        return statement, imp, cols
    
    def multirecall(self, classes, expr):
        """Yield Unit instance sets which satisfy the expression."""
        sql, imp, supplied_cols = self.multiselect(classes, expr)
        data, recvd_cols = self.db.fetch(sql)
        if data:
            # Get specs on properties.
            props = []
            for sup, rec in zip(supplied_cols, recvd_cols):
                c, key = sup
                name, ftype = rec[0], rec[1]
                props.append((c, key, ftype))
            
            for row in data:
                index = 0
                units = {}
                for c, key, ftype in props:
                    if c in units:
                        unit = units[c]
                    else:
                        units[c] = unit = c()
                    value = row[index]
                    self.consume(unit, key, value, ftype)
                    index += 1
                
                unitset = []
                for cls in classes:
                    unit = units[cls]
                    unit.cleanse()
                    unitset.append(unit)
                
                # If our SQL is imperfect, don't yield units to the
                # caller unless they pass expr(unit).
                acceptable = True
                if imp:
                    acceptable = expr(*unitset)
                if acceptable:
                    yield unitset
    
    #                               Schemas                               #
    
    def create_database(self):
        self.db.create_database()
    
    def drop_database(self):
        self.db.drop_database()
    
    def create_storage(self, cls):
        """Create storage for the given class."""
        # Make a Table object.
        t = self.db.make_table(cls.__name__)
        
        indices = cls.indices()
        fields = []
        for key in cls.properties:
            dbtype = self.typeAdapter.coerce(cls, key)
            col = self.db.make_column(cls.__name__, key, dbtype)
            prop = cls.property(key)
            col.default = prop.default
            col.hints = prop.hints.copy()
            # Use the superclass call to avoid ALTER TABLE.
            dict.__setitem__(t.columns, key, col)
            
            if key in indices:
                i = self.db.make_index(cls.__name__, key)
                # Use the superclass call to avoid CREATE INDEX.
                dict.__setitem__(t.columns.indices, key, i)
        
        # Attach to self.db, which should call CREATE TABLE.
        self.db[cls.__name__] = t
    
    def has_storage(self, cls):
        return cls.__name__ in self.db
    
    def drop_storage(self, cls):
        del self.db[cls.__name__]
    
    def rename_storage(self, oldname, newname):
        self.arena.log("rename table %s to %s" % (oldname, newname), LOGSQL)
        self.db.rename(oldname, newname)
    
    def add_property(self, cls, name):
        if not self.has_property(cls, name):
            dbtype = self.typeAdapter.coerce(cls, name)
            c = self.db.make_column(cls.__name__, name, dbtype)
            prop = getattr(cls, name)
            c.default = prop.default
            c.hints = prop.hints.copy()
            self.db[cls.__name__].columns[name] = c
    
    def has_property(self, cls, name):
        return name in self.db[cls.__name__].columns
    
    def drop_property(self, cls, name):
        if self.has_property(cls, name):
            del self.db[cls.__name__].columns[name]
    
    def rename_property(self, cls, oldname, newname):
        self.db[cls.__name__].columns.rename(oldname, newname)
    
    def has_index(self, cls, name):
        return name in self.db[cls.__name__].columns.indices
    
    def drop_index(self, cls, name):
        del self.db[cls.__name__].columns.indices[name]
    
    def sync(self):
        """Map new Table objects to all registered classes."""
        # Use the superclass call to avoid DROP TABLE.
        dict.clear(self.db)
        dbtables = self.db._get_tables()
        for cls in self.arena._registered_classes:
            # Try to find a matching Table object from _get_tables.
            t = [x for x in dbtables if x.name == self.db.table_name(cls.__name__)]
            if t:
                t = t[0]
                dbcols = self.db._get_columns(t.name)
                for ckey in cls.properties:
                    # Try to find a matching Column object from _get_columns.
                    c = [x for x in dbcols if x.name == self.db.column_name(cls.__name__, ckey)]
                    if c:
                        c = c[0]
                        # Use the superclass call to avoid ALTER TABLE
                        dict.__setitem__(t.columns, ckey, c)
                
                dbindices = self.db._get_indices(t.name)
                for ikey in cls.indices():
                    iname = self.db.table_name("i" + cls.__name__ + ikey)
                    # Try to find a matching Column object from _get_columns.
                    i = [x for x in dbindices if x.name == iname]
                    if i:
                        i = i[0]
                        # Use the superclass call to avoid ALTER TABLE
                        dict.__setitem__(t.columns.indices, ikey, i)
                
                # Use the superclass call to avoid CREATE TABLE
                dict.__setitem__(self.db, cls.__name__, t)
    
    def autoclass(self, table, newclassname=None):
        """Create a Unit class automatically from this table and its columns."""
        class AutoUnitClass(dejavu.Unit):
            pass
        for cname, c in table.columns.iteritems():
            ptype = self.db.python_type(c.dbtype)
            p = AutoUnitClass.set_property(cname, ptype)
            p.default = c.default
            p.hints = c.hints.copy()
        
        if newclassname is None:
            newclassname = table.name
        AutoUnitClass.__name__ = newclassname
        
        return AutoUnitClass