root/trunk/geniusql/__init__.py

"""Geniusql, a Python database library.

The Column and Index classes model corresponding database objects, and are
intentionally simple. They should rarely contain any SQL or "smarts" of
any kind, besides the "qname", the quoted name, of the column or index.
At most, subclasses and consumers might put implementation-specific data
into them.

The IndexSet, Table, and Database objects are all dict-like containers,
and therefore have a key for each value. Those keys should equate to things
at the consumer layer; for example, a Database may possess a pair of the
form: {'YoYo': Table('yoyo')} -- the key is the "friendly" name, but the
Table.name is a lowercase version of that, because that's what the database
uses in SQL to refer to that table.
"""

__version__ = "1.0alpha"


class _AttributeDocstrings(type):
    """Metaclass for declaring docstrings for class attributes."""
    # The full docstring for this type is down in the __init__ method so
    # that it doesn't show up in help() for every consumer class.
    
    def __init__(cls, name, bases, dct):
        '''Metaclass for declaring docstrings for class attributes.
        
        Base Python doesn't provide any syntax for setting docstrings on
        'data attributes' (non-callables). This metaclass allows class
        definitions to follow the declaration of a data attribute with
        a docstring for that attribute; the attribute docstring will be
        popped from the class dict and folded into the class docstring.
        
        The naming convention for attribute docstrings is: <attrname> + "__doc".
        For example:
        
            class Thing(object):
                """A thing and its properties."""
                
                __metaclass__ = cherrypy._AttributeDocstrings
                
                height = 50
                height__doc = """The height of the Thing in inches."""
        
        In which case, help(Thing) starts like this:
        
            >>> help(mod.Thing)
            Help on class Thing in module pkg.mod:
            
            class Thing(__builtin__.object)
             |  A thing and its properties.
             |  
             |  height [= 50]:
             |      The height of the Thing in inches.
             | 
        
        The benefits of this approach over hand-edited class docstrings:
            1. Places the docstring nearer to the attribute declaration.
            2. Makes attribute docs more uniform ("name (default): doc").
            3. Reduces mismatches of attribute _names_ between
               the declaration and the documentation.
            4. Reduces mismatches of attribute default _values_ between
               the declaration and the documentation.
        
        The benefits of a metaclass approach over other approaches:
            1. Simpler ("less magic") than interface-based solutions.
            2. __metaclass__ can be specified at the module global level
               for classic classes.
        
        The type of the attribute is intentionally not included, because
        that's not How Python Works. Quack.
        '''
        
        newdoc = [cls.__doc__ or ""]
        
        dctnames = dct.keys()
        dctnames.sort()
        
        for name in dctnames:
            if name.endswith("__doc"):
                # Remove the magic doc attribute.
                if hasattr(cls, name):
                    delattr(cls, name)
                
                # Get an inspect-style docstring if possible (usually so).
                val = dct[name]
                try:
                    import inspect
                    val = inspect.getdoc(property(doc=val)).strip()
                except:
                    pass
                
                # Indent the docstring.
                val = '\n'.join(['    ' + line.rstrip()
                                 for line in val.split('\n')])
                
                # Get the default value.
                attrname = name[:-5]
                try:
                    attrval = getattr(cls, attrname)
                except AttributeError:
                    attrval = "missing"
                
                # Add the complete attribute docstring to our list.
                newdoc.append("%s [= %r]:\n%s" % (attrname, attrval, val))
        
        # Add our list of new docstrings to the class docstring.
        cls.__doc__ = "\n\n".join(newdoc)


import threading

from dejavu import logic

from geniusql import errors, typerefs

from geniusql.adapters import *
from geniusql.conn import *
from geniusql.isolation import *
from geniusql.select import *

from geniusql import providers


def db(provider, **options):
    """Return a Database and Schema object for the given provider.
    
    provider: A 'shortcut name' registered in geniusql.providers.registry.
    
    This function does not call CREATE DATABASE (although it may open a
    database connection).
    """
    return providers.registry.open(provider, **options)


class Index:
    """An index on a table column (or columns) in a database."""
    
    def __init__(self, name, qname, tablename, colname, unique=True):
        self.name = name
        self.qname = qname
        self.tablename = tablename
        self.colname = colname
        self.unique = unique
    
    def __repr__(self):
        return ("%s.%s(%r, %r, %r, %r, unique=%r)" %
                (self.__module__, self.__class__.__name__,
                 self.name, self.qname, self.tablename,
                 self.colname, self.unique))
    
    def __copy__(self):
        return self.__class__(self.name, self.qname, self.tablename,
                              self.colname, self.unique)
    copy = __copy__


class IndexSet(dict):
    
    def __new__(cls, table):
        return dict.__new__(cls)
    
    def __init__(self, table):
        dict.__init__(self)
        self.table = table
    
    def alias(self, oldname, newname):
        """Add a new key for the Index with the given, existing key.
        
        Consumer code should call this method when user-supplied index
        names do not match the names in the database. This does not
        remove the old key; both keys may be used to refer to the same
        Index object.
        """
        if oldname == newname:
            return
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__delitem__(self, oldname)
        dict.__setitem__(self, newname, obj)
    
    def __setitem__(self, key, index):
        """Create the specified index."""
        t = self.table
        if t.created:
            t.schema.db.execute_ddl('CREATE INDEX %s ON %s (%s);' %
                                    (index.qname, t.qname,
                                     t.schema.db.quote(index.colname)))
        dict.__setitem__(self, key, index)
    
    def __delitem__(self, key):
        """Drop the specified index."""
        t = self.table
        if t.created:
            t.schema.db.execute_ddl('DROP INDEX %s ON %s;' %
                             (self[key].qname, t.qname))
        dict.__delitem__(self, key)


class Column:
    """A column in a table in a database.
    
    name: the SQL name for this table (unquoted).
    qname: the SQL name for this table (quoted).
    pytype: the Python type (the actual type object, not its name).
    dbtype: the database type name (as used in a CREATE TABLE statement).
    default: default Python value for this column for new rows.
    hints: a dict of implementation hints, such as precision, scale, or bytes.
    key: True if this column is part of the table's primary key.
    
    imperfect_type: if True, signals that we are deliberately using a
        database type other than the default (usually in order to handle
        irregular values, such as huge numbers). When comparing imperfect
        column values with constant values in SQL, the database must be
        able to cast the column value to the constant's type. If that
        cannot be done for the given types, then the query will be marked
        imperfect.
    autoincrement: if True, uses the database's built-in sequencing.
    sequence_name: for databases that use separate statements to create and
        drop sequences, this stores the name of the sequence.
    initial: if autoincrement, holds the initial value for the sequence.
    """
    
    def __init__(self, pytype, dbtype, default=None, hints=None, key=False,
                 name=None, qname=None):
        self.pytype = pytype
        self.dbtype = dbtype
        self.name = name
        self.qname = qname
        self.default = default
        if hints is None:
            hints = {}
        else:
            hints = hints.copy()
        self.hints = hints
        self.key = key
        
        # If autoincrement, the initial value should be put in self.initial.
        self.autoincrement = False
        self.sequence_name = None
        self.initial = 1
        
        self.imperfect_type = False
    
    def __repr__(self):
        return ("%s.%s(%r, %r, default=%r, hints=%r, key=%r, name=%r, qname=%r)" %
                (self.__module__, self.__class__.__name__,
                 self.pytype, self.dbtype,
                 self.default, self.hints, self.key,
                 self.name, self.qname)
                )
    
    def __copy__(self):
        newcol = self.__class__(self.pytype, self.dbtype,
                                self.default, self.hints, self.key,
                                self.name, self.qname)
        newcol.autoincrement = self.autoincrement
        newcol.initial = self.initial
        newcol.imperfect_type = self.imperfect_type
        return newcol
    copy = __copy__


class Table(dict):
    """A table in a database; a dict of Column objects.
    
    Values in this dict must be instances of Column (or a subclass of it).
    Keys should be consumer-friendly names for each Column value.
    
    name: the SQL name for this table (unquoted).
    qname: the SQL name for this table (quoted).
    schema: the schema for this table.
    created: whether or not this Table has a concrete implementation in the
        database. If False (the default), then changes to Table items can be
        made with impunity. If True, then appropriate ALTER TABLE commands
        are executed whenever a consumer adds or deletes items from the
        Table, or calls methods like 'rename'.
    indices: a dict-like IndexSet of Index objects.
    references: a dict of the form: {name: (nearColKey, farTableKey, farColKey)}.
    """
    
    def __new__(cls, name, qname, schema, created=False):
        return dict.__new__(cls)
    
    def __init__(self, name, qname, schema, created=False):
        dict.__init__(self)
        
        self.name = name
        self.qname = qname
        self.schema = schema
        self.created = created
        
        self.indices = schema.indexsetclass(self)
        self.references = {}
    
    def __repr__(self):
        name = getattr(self, "name", "<unknown>")
        qname = getattr(self, "qname", "<unknown>")
        return ("%s.%s(%r, %r)" %
                (self.__module__, self.__class__.__name__, name, qname))
    
    def __copy__(self):
        # Don't set 'created' when copying!
        newtable = self.__class__(self.name, self.qname, self.schema)
        for key, c in self.iteritems():
            dict.__setitem__(newtable, key, c.copy())
        for key, i in self.indices.iteritems():
            dict.__setitem__(newtable.indices, key, i.copy())
        return newtable
    copy = __copy__
    
    def alias(self, oldname, newname):
        """Add a new key for the Column with the given, existing key.
        
        Consumer code should call this method when user-supplied column
        names do not match the names in the database. This does not
        remove the old key; both keys may be used to refer to the same
        Column object.
        """
        if oldname == newname:
            return
        
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__delitem__(self, oldname)
        dict.__setitem__(self, newname, obj)
    
    def _add_column(self, column):
        """Internal function to add the column to the database."""
        coldef = self.schema.columnclause(column)
        self.schema.db.execute("ALTER TABLE %s ADD COLUMN %s;" %
                               (self.qname, coldef))
    
    def __setitem__(self, key, column):
        if column.name is None:
            column.name = self.schema._column_name(self.name, key)
            column.qname = self.schema.db.quote(column.name)
        
        if not self.created:
            dict.__setitem__(self, key, column)
            return
        
        if key in self:
            del self[key]
        
        if column.autoincrement:
            # This may or may not be a no-op, depending on the DB.
            self.schema.create_sequence(self, column)
        self._add_column(column)
        dict.__setitem__(self, key, column)
    
    def _drop_column(self, column):
        """Internal function to drop the column from the database."""
        self.schema.db.execute_ddl("ALTER TABLE %s DROP COLUMN %s;" %
                                   (self.qname, column.qname))
    
    def __delitem__(self, key):
        if key in self.indices:
            del self.indices[key]
        
        if not self.created:
            dict.__delitem__(self, key)
            return
        
        column = self[key]
        self._drop_column(column)
        if column.autoincrement:
            # This may or may not be a no-op, depending on the DB.
            self.schema.drop_sequence(column)
        dict.__delitem__(self, key)
    
    def _rename(self, oldcol, newcol):
        # Override this to do the actual rename at the DB level.
        self.schema.db.execute_ddl("ALTER TABLE %s RENAME COLUMN %s TO %s;" %
                                   (self.qname, oldcol.qname, newcol.qname))
    
    def rename(self, oldkey, newkey):
        """Rename a Column. This will change the table name in the database."""
        oldcol = self[oldkey]
        
        if not self.created:
            dict.__delitem__(self, oldkey)
            dict.__setitem__(self, newkey, oldcol)
            return
        
        oldname = oldcol.name
        newname = self.schema._column_name(self.name, newkey)
        
        if oldname != newname:
            newcol = oldcol.copy()
            newcol.name = newname
            newcol.qname = self.schema.db.quote(newname)
            self._rename(oldcol, newcol)
        
        # Use the superclass calls to avoid DROP COLUMN/ADD COLUMN.
        dict.__delitem__(self, oldkey)
        dict.__setitem__(self, newkey, newcol)
    
    def add_index(self, columnkey):
        """Add and return a new Index for the given column key.
        
        The new Index object will possess the same key as the column.
        In general, the actual SQL name of the new Index will be of
        the form: "i" + table.name + column.name.
        """
        colname = self[columnkey].name
        name = self.schema.table_name("i" + self.name + colname)
        i = Index(name, self.schema.db.quote(name), self.name, colname)
        self.indices[columnkey] = i
        return i
    
    
    # ---------------------------- OLTP/CRUD ---------------------------- #
    
    def whereclause(self, **inputs):
        """Return an SQL WHERE clause for the given input fields.
        
        If the given clause is imperfect, a ValueError is raised.
        """
        tpair = [(self.qname, self)]
        decom = self.schema.db.decompiler(tpair, logic.filter(**inputs),
                                          self.schema.db.adaptertosql)
        code = decom.code()
        if decom.imperfect:
            raise ValueError("The given inputs could not safely be translated "
                             "to SQL.", inputs, code)
        return code
    
    def id_clause(self, **inputs):
        """Return an SQL expression for the identifiers of the given table."""
        for key in inputs.keys():
            if not self[key].key:
                inputs.pop(key)
        return self.whereclause(**inputs)
    
    def insert(self, **inputs):
        """Insert a row and return {idcolkey: newid}."""
        coerce_out = self.schema.db.adaptertosql.coerce
        coerce_in = self.schema.db.adapterfromdb.coerce
        
        fields = []
        idkeys = []
        values = []
        for key, col in self.iteritems():
            if col.autoincrement:
                # Skip this field, since we're using a sequencer
                idkeys.append(key)
                continue
            if key in inputs:
                val = coerce_out(inputs[key], col.dbtype)
                fields.append(col.qname)
                values.append(val)
        
        conn = self.schema.db.connections.get()
        
        fields = ", ".join(fields)
        values = ", ".join(values)
        self.schema.db.execute('INSERT INTO %s (%s) VALUES (%s);' %
                               (self.qname, fields, values), conn)
        
        if idkeys:
            newids = self._grab_new_ids(idkeys, conn)
            for key in newids.keys():
                col = self[key]
                newids[key] = coerce_in(newids[key], col.dbtype, col.pytype)
            return newids
        else:
            return {}
    
    def _grab_new_ids(self, idkeys, conn):
        # Override this to fetch and return new autoincrement values.
        raise NotImplementedError
    
    def save(self, **inputs):
        """Update a row using the given inputs."""
        parms = []
        coerce = self.schema.db.adaptertosql.coerce
        for key, val in inputs.iteritems():
            col = self[key]
            if col.autoincrement:
                # Skip this field, since we're using a sequencer
                pass
            else:
                val = coerce(val, col.dbtype)
                parms.append('%s = %s' % (col.qname, val))
        
        if parms:
            sql = ('UPDATE %s SET %s WHERE %s;' %
                   (self.qname, ", ".join(parms), self.id_clause(**inputs)))
            self.schema.db.execute(sql)
    
    use_asterisk_to_delete_all = False
    
    def delete(self, **inputs):
        """Delete all rows matching the given identifier inputs."""
        if self.use_asterisk_to_delete_all:
            star = " *"
        else:
            star = ""
        self.schema.db.execute('DELETE%s FROM %s WHERE %s;' %
                               (star, self.qname, self.id_clause(**inputs)))
    
    def delete_all(self, **inputs):
        """Delete all rows matching the given inputs."""
        if self.use_asterisk_to_delete_all:
            star = " *"
        else:
            star = ""
        self.schema.db.execute('DELETE%s FROM %s WHERE %s;' %
                               (star, self.qname,
                                self.whereclause(**inputs)))
    
    def select_all(self, restriction=None, **kwargs):
        """Yield data dicts matching the given restriction."""
        if restriction and not isinstance(restriction, logic.Expression):
            restriction = logic.Expression(restriction)
        if kwargs:
            f = logic.filter(**kwargs)
            if restriction:
                restriction += f
            else:
                restriction = f
        
        attrs = self.keys()
        data = self.schema.db.select(self, attrs, restriction)
        for row in data:
            row = dict(zip(attrs, row))
            if restriction and data.imperfect:
                # Run a dummy object through our restriction before yielding.
                if not restriction(ImperfectDummy(**row)):
                    continue
            yield row
    
    def select_one(self, restriction=None, **kwargs):
        """Return a single data dict matching the given restriction (or None)."""
        try:
            return self.select_all(restriction, **kwargs).next()
        except StopIteration:
            return None


class ImperfectDummy(object):
    """A dummy object for resolving imperfect queries."""
    def __init__(self, **kwargs):
        for k, v in kwargs.iteritems():
            setattr(self, k, v)


class Schema(dict):
    """A dict for managing a set of tables.
    
    Values in this dict must be instances of Table. Keys should be
    consumer-friendly names for each Table value. For example, it's
    easiest to use all lowercase table names in MySQL; however, a
    geniusql consumer might want their code to use TitledNames to
    refer to each table.
    
    When a consumer adds and deletes items from a Schema object,
    appropriate CREATE TABLE/DROP TABLE commands are executed.
    This means that a Table object to be added should have all
    of its columns populated before adding it to the Schema.
    """
    
    tableclass = Table
    indexsetclass = IndexSet
    
    def __new__(cls, db, name):
        return dict.__new__(cls)
    
    def __init__(self, db, name):
        dict.__init__(self)
        
        self.db = db
        self.name = self.db.sql_name(name)
        self.qname = self.db.quote(self.name)
        self._discover_lock = threading.Lock()
        self.discover_dbinfo()
    
    def __repr__(self):
        name = getattr(self, "name", "<unknown>")
        return "%s.%s(%r)" % (self.__module__, self.__class__.__name__, name)
    
    #                              Discovery                              #
    
    def _get_dbinfo(self, conn=None):
        return {}
    
    def discover_dbinfo(self, conn=None):
        """Set attributes on self with actual DB metadata, where possible."""
        for k, v in self._get_dbinfo().iteritems():
            setattr(self, k, v)
    
    def _get_tables(self, conn=None):
        raise NotImplementedError
    
    def _get_table(self, tablename, conn=None):
        # Fallback behavior. This is slow and should be optimized by each DB.
        for t in self._get_tables():
            if t.name == tablename:
                return t
        raise errors.MappingError(tablename)
    
    def _get_columns(self, tablename, conn=None):
        raise NotImplementedError
    
    def _get_indices(self, tablename, conn=None):
        raise NotImplementedError
    
    def _discover_table(self, table, conn=None):
        """Populate the columns and indices of the given Table object."""
        for col in self._get_columns(table.name, conn):
            # Use the superclass call to avoid ALTER TABLE
            if col.name in table:
                dict.__delitem__(table, col.name)
            dict.__setitem__(table, col.name, col)
        
        for idx in self._get_indices(table.name, conn):
            # Use the superclass call to avoid CREATE INDEX
            if idx.name in table.indices:
                dict.__delitem__(table.indices, idx.name)
            dict.__setitem__(table.indices, idx.name, idx)
    
    def discover(self, tablename, conn=None):
        """Attach a new Table from the underlying DB to self (and return it).
        
        Table objects (and their Column and Index subobjects) will be
        added to self using keys that match the database's names.
        Consumers should call the "alias(oldname, newname)" method
        of Schema, Table, and IndexSet in order to re-map the
        discovered objects using consumer-friendly names.
        
        If no such table exists, a MappingError should be raised.
        """
        self._discover_lock.acquire()
        try:
            table = self._get_table(tablename)
            self._discover_table(table, conn)
            
            # Use the superclass calls to avoid CREATE TABLE
            if table.name in self:
                dict.__delitem__(self, table.name)
            dict.__setitem__(self, table.name, table)
            
            return table
        finally:
            self._discover_lock.release()
    
    def discover_all(self, conn=None):
        """(Re-)populate self (all table items) from the underlying DB.
        
        Table objects (and their Column and Index subobjects) will be
        added to self using keys that match the database's names.
        Consumers should call the "alias(oldname, newname)" method
        of Schema, Table, and IndexSet in order to re-map the
        discovered objects using consumer-friendly names.
        
        This method is idempotent, but that doesn't mean cheap. Try not
        to call it very often (once at app startup is usually enough).
        If you already know the names of all the tables you want to
        discover, it's often faster to skip this method and just use
        the discover(tablename) method for each known name instead.
        """
        self._discover_lock.acquire()
        try:
            for table in self._get_tables(conn):
                self._discover_table(table, conn)
                
                # Use the superclass calls to avoid CREATE TABLE
                if table.name in self:
                    dict.__delitem__(self, table.name)
                dict.__setitem__(self, table.name, table)
        finally:
            self._discover_lock.release()
    
    def alias(self, oldname, newname):
        """Add a new key for the Table with the given, existing key.
        
        Consumer code should call this method when user-supplied table
        names do not match the names in the database. This does not
        remove the old key; both keys may be used to refer to the same
        Table object.
        """
        if oldname == newname:
            return
        
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__delitem__(self, oldname)
        dict.__setitem__(self, newname, obj)
    
    def _column_name(self, tablename, columnkey):
        "Return the SQL column name for the given table name and column key."
        # If you want to use a map from your ORM's property names
        # to DB column names, override this method (that's why
        # the tablename must be included in the args).
        return self.db.sql_name(columnkey)
    
    def column(self, pytype=unicode, dbtype=None, default=None, hints=None,
               key=False, autoincrement=False):
        """Return a Column object from the given arguments."""
        col = Column(pytype, dbtype, default, hints, key)
        col.autoincrement = autoincrement
        
        if dbtype is None:
            col.dbtype = self.db.typeadapter.coerce(col, pytype)
        pytype2 = self.db.python_type(col.dbtype)
        col.imperfect_type = not self.db.isrelatedtype(pytype, pytype2)
        
        return col
    
    prefix = ""
    
    def table_name(self, key):
        """Return the SQL table name for the given key."""
        # If you want to use a map from your ORM's class names
        # to DB table names, override this method.
        return self.db.sql_name(self.prefix + key)
    
    def table(self, name):
        """Create and return a Table object for the given name."""
        name = self.table_name(name)
        return self.tableclass(name, self.db.quote(name), self)
    
    def create_sequence(self, table, column):
        """Create a SEQUENCE for the given column and set its sequence_name."""
        # By default, this does nothing. Databases which require a separate
        # statement to create a sequence generator should override this.
        pass
    
    def drop_sequence(self, column):
        """Drop a SEQUENCE for the given column and remove its sequence_name."""
        # By default, this does nothing. Databases which require a separate
        # statement to drop a sequence generator should override this.
        pass
    
    def columnclause(self, column):
        """Return a clause for the given column for CREATE or ALTER TABLE.
        
        This will be of the form "name type [DEFAULT x]".
        
        Most subclasses will override this to add autoincrement support.
        """
        dbtype = column.dbtype
        
        default = column.default or ""
        if default:
            default = self.db.adaptertosql.coerce(default, dbtype)
            default = " DEFAULT %s" % default
        
        return "%s %s%s" % (column.qname, dbtype, default)
    
    def __setitem__(self, key, table):
        if key in self:
            del self[key]
        
        # Set table.created to True, which should "turn on"
        # any future ALTER TABLE statements.
        table.created = True
        
        fields = []
        pk = []
        for column in table.itervalues():
            if column.autoincrement:
                # This may or may not be a no-op, depending on the DB.
                self.create_sequence(table, column)
            
            fields.append(self.columnclause(column))
            if column.key:
                pk.append(column.qname)
        
        if pk:
            pk = ", PRIMARY KEY (%s)" % ", ".join(pk)
        else:
            pk = ""
        
        self.db.execute_ddl('CREATE TABLE %s (%s%s);' %
                            (table.qname, ", ".join(fields), pk))
        
        for index in table.indices.itervalues():
            self.db.execute_ddl('CREATE INDEX %s ON %s (%s);' %
                                (index.qname, table.qname,
                                 self.db.quote(index.colname)))
        
        dict.__setitem__(self, key, table)
    
    def __delitem__(self, key):
        table = self[key]
        self.db.execute_ddl('DROP TABLE %s;' % table.qname)
        for col in table.itervalues():
            if col.autoincrement:
                self.drop_sequence(col)
        dict.__delitem__(self, key)
    
    def _rename(self, oldtable, newtable):
        # Override this to do the actual rename at the DB level.
        raise NotImplementedError
        newtable.created = True
    
    def rename(self, oldkey, newkey):
        """Rename a Table."""
        oldtable = self[oldkey]
        oldname = oldtable.name
        newname = self.db.table_name(newkey)
        
        if oldname != newname:
            newtable = oldtable.copy()
            newtable.schema = self.schema
            newtable.name = newname
            newtable.qname = self.db.quote(newname)
            self._rename(oldtable, newname)
        
        # Use the superclass calls to avoid DROP TABLE/CREATE TABLE.
        dict.__delitem__(self, oldkey)
        dict.__setitem__(self, newkey, newtable)
    
    def create_database(self):
        self.db.execute_ddl("CREATE DATABASE %s;" % self.qname)
        self.clear()
    
    def drop_database(self):
        # Must shut down all connections to avoid
        # "being accessed by other users" error.
        self.db.connections.shutdown()
        self.db.execute_ddl("DROP DATABASE %s;" % self.qname)
        self.clear()


class Database(object):
    
    __metaclass__ = _AttributeDocstrings
    
    adaptertosql = AdapterToSQL()
    adapterfromdb = AdapterFromDB()
    typeadapter = TypeAdapter()
    decompiler = SQLDecompiler
    joinwrapper = TableWrapper
    selectwriter = SelectWriter
    connectionmanager = ConnectionManager
    schemaclass = Schema
    
    multischema = True
    multischema__doc = """If True, instances of this Database class may
    spawn multiple Schema instances. This is False, for example, when
    the underlying Database engine binds connections to individual files.
    In most applications (that use a single schema) this presents no
    problems; applications that need to handle more than one schema
    at a time should inspect this value to determine whether they
    need a separate Database instance per Schema instance.
    """
    
    def __init__(self, **kwargs):
        for k, v in kwargs.iteritems():
            setattr(self, k, v)
        
        poolsize = kwargs.get('poolsize', 10)
        self.connections = self.connectionmanager(self, poolsize)
    
    def version(self):
        """Return a string containing version info for this database."""
        raise NotImplementedError
    
    def log(self, msg):
        pass
    
    def python_type(self, dbtype):
        """Return a Python type which can store values of the given dbtype."""
        raise TypeError("Database type %r could not be converted "
                        "to a Python type." % dbtype)
    
    def isrelatedtype(self, pytype1, pytype2):
        """If values of both types are expressed with the same SQL, return True."""
        if issubclass(pytype1, pytype2) or issubclass(pytype2, pytype1):
            return True
        if issubclass(pytype1, basestring) and issubclass(pytype2, basestring):
            return True
        if ((issubclass(pytype1, int) or issubclass(pytype1, long)) and
            (issubclass(pytype2, int) or issubclass(pytype2, long))):
            return True
        if typerefs.fixedpoint:
            if typerefs.decimal:
                if ((issubclass(pytype1, typerefs.fixedpoint.FixedPoint)
                     or issubclass(pytype1, typerefs.decimal.Decimal)) and
                    (issubclass(pytype2, typerefs.fixedpoint.FixedPoint)
                     or issubclass(pytype2, typerefs.decimal.Decimal))):
                    return True
            else:
                if (issubclass(pytype1, typerefs.fixedpoint.FixedPoint) and
                    issubclass(pytype2, typerefs.fixedpoint.FixedPoint)):
                    return True
        else:
            if typerefs.decimal:
                if (issubclass(pytype1, typerefs.decimal.Decimal) and
                    issubclass(pytype2, typerefs.decimal.Decimal)):
                    return True
        return False
    
    #                               Naming                               #
    
    sql_name_max_length = 64
    sql_name_caseless = False
    
    def quote(self, name):
        """Return name, quoted for use in an SQL statement."""
        # This base class doesn't use "quote",
        # but most subclasses will.
        return name
    
    def sql_name(self, key):
        """Return the native SQL version of key."""
        if self.sql_name_caseless:
            key = key.lower()
        
        maxlen = self.sql_name_max_length
        if maxlen and len(key) > maxlen:
            errors.warn("The name '%s' is longer than the maximum of "
                        "%s characters." % (key, maxlen))
            key = key[:maxlen]
        
        return key
    
    def is_timeout_error(self, exc):
        """If the given exception instance is a lock timeout, return True.
        
        This should return True for errors which arise from locking
        timeouts; for example, if the database prevents 'dirty reads'
        by raising an error.
        """
        # You should definitely override this for your database.
        return False
    
    def execute(self, query, conn=None):
        """Return a native response for the given query."""
        if conn is None:
            conn = self.connections.get()
        if isinstance(query, unicode):
            query = query.encode(self.adaptertosql.encoding)
        self.log(query)
        return conn.query(query)
    
    def execute_ddl(self, query, conn=None):
        """Return a native response for the given DDL statement.
        
        In general, DDL statements should lock out other statements
        (especially those isolated in other transactions). Use this
        method to perform a locked DDL statement.
        """
        self.connections.lock("Transaction denied due to DDL: %r" % query)
        try:
##            # Must shut down all connections to avoid
##            # "being accessed by other users" error?
##            self.connections.shutdown()
            if conn is None:
                # Important: use _factory(), not get(), to avoid the lock
                conn = self.connections._factory()
            self.execute(query, conn)
        finally:
            self.connections.unlock()
    
    def fetch(self, query, conn=None):
        """Return rowdata, columns (name, type) for the given query.
        
        query should be a SQL query in string format
        rowdata will be an iterable of iterables containing the result values.
        columns will be an iterable of (column name, data type) pairs.
        
        This base class uses _sqlite syntax.
        """
        res = self.execute(query, conn)
        return res.row_list, res.col_defs
    
    def select(self, relation, attributes, restriction=None, distinct=False):
        """Yield matching data, coerced to Python types (where known)."""
        sel = self.selectwriter(self, relation, attributes, restriction)
        data, _ = self.fetch(sel.sql(distinct))
        return ResultSet(data, sel.columns, sel.imperfect)
    
    def schema(self, name):
        return self.schemaclass(self, name)


class ResultSet:
    
    def __init__(self, data, columns, imperfect):
        self.data = data
        self.columns = columns
        self.imperfect = imperfect
        self.cursor = 0
    
    def __iter__(self):
        return self
    
    def next(self):
        try:
            row = self.data[self.cursor]
            self.cursor += 1
        except IndexError:
            raise StopIteration
        
        coerced_row = []
        for i, (table, col, qname) in enumerate(self.columns):
            val = row[i]
            if table and col:
                val = table.schema.db.adapterfromdb.coerce(val, col.dbtype, col.pytype)
            coerced_row.append(val)
        return coerced_row