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"

import threading
from geniusql import xray

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(cls, name, options):
    """Create a Database model object for the given cls, name, and options.
    
    cls: Either a subclass of geniusql.Database or a 'shortcut name'
        registered in geniusql.providers.providers.
    name: the database name as used by the underlying database.
    
    This function does not call CREATE DATABASE, nor should it open any
    database connections. It simply instantiates the proper Database class.
    """
    if isinstance(cls, basestring):
        try:
            cls = providers.providers[cls]
        except KeyError:
            pass
    
    if isinstance(cls, basestring):
        cls = xray.classes(cls)
    
    opts = dict([(str(k), v) for k, v in options.iteritems()])
    opts.pop('name', None)
    
    return cls(name, **opts)


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.
        """
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__setitem__(self, newname, obj)
    
    def __setitem__(self, key, index):
        """Drop the specified index."""
        t = self.table
        if t.created:
            t.db.lock("Creating index. Transactions not allowed.")
            try:
                t.db.execute('CREATE INDEX %s ON %s (%s);' %
                             (index.qname, t.qname,
                              t.db.quote(index.colname)))
            finally:
                t.db.unlock()
        dict.__setitem__(self, key, index)
    
    def __delitem__(self, key):
        """Drop the specified index."""
        t = self.table
        if t.created:
            t.db.lock("Dropping index. Transactions not allowed.")
            try:
                t.db.execute('DROP INDEX %s ON %s;' % (self[key].qname, t.qname))
            finally:
                t.db.unlock()
        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).
    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).
    db: the database for this table. If None (the default), then changes to
        Table items can be made with impunity. If not None, then appropriate
        ALTER TABLE commands are executed whenever a consumer adds or deletes
        items from the Table, or calls methods like 'rename'. Therefore,
        when creating Table objects from an existing database, you should
        set the 'db' arg late.
    indices: a dict-like IndexSet of Index objects.
    references: a dict of the form: {name: (nearColKey, farTableKey, farColKey)}.
    """
    
    indexsetclass = IndexSet
    
    def __new__(cls, name, qname, db, created=False):
        return dict.__new__(cls)
    
    def __init__(self, name, qname, db, created=False):
        dict.__init__(self)
        
        self.name = name
        self.qname = qname
        self.db = db
        self.created = created
        
        self.indices = self.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.db)
        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.
        """
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__setitem__(self, newname, obj)
    
    def _add_column(self, column):
        """Internal function to add the column to the database."""
        coldef = self.db.columnclause(column)
        self.db.execute("ALTER TABLE %s ADD COLUMN %s;" % (self.qname, coldef))
    
    def __setitem__(self, key, column):
        if column.name is None:
            column.name = self.db._column_name(self.name, key)
            column.qname = self.db.quote(column.name)
        
        if not self.created:
            dict.__setitem__(self, key, column)
            return
        
        if key in self:
            del self[key]
        
        self.db.lock("Adding property. Transactions not allowed.")
        try:
            if column.autoincrement:
                # This may or may not be a no-op, depending on the DB.
                self.db.create_sequence(self, column)
            self._add_column(column)
            dict.__setitem__(self, key, column)
        finally:
            self.db.unlock()
    
    def _drop_column(self, column):
        """Internal function to drop the column from the database."""
        self.db.execute("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
        
        self.db.lock("Dropping property. Transactions not allowed.")
        try:
            column = self[key]
            self._drop_column(column)
            if column.autoincrement:
                # This may or may not be a no-op, depending on the DB.
                self.db.drop_sequence(column)
            dict.__delitem__(self, key)
        finally:
            self.db.unlock()
    
    def _rename(self, oldcol, newcol):
        # Override this to do the actual rename at the DB level.
        self.db.execute("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.db._column_name(self.name, newkey)
        
        if oldname != newname:
            newcol = oldcol.copy()
            newcol.name = newname
            newcol.qname = self.db.quote(newname)
            self.db.lock("Renaming property. Transactions not allowed.")
            try:
                self._rename(oldcol, newcol)
            finally:
                self.db.unlock()
        
        # 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.db.table_name("i" + self.name + colname)
        i = Index(name, self.db.quote(name), self.name, colname)
        self.indices[columnkey] = i
        return i
    
    def select_all(self, restriction=None):
        """Yield data dicts matching the given restriction."""
        attrs = self.keys()
        data = self.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):
        """Return a single data dict matching the given restriction (or None)."""
        try:
            return self.select_all(restriction).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 Database(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 Database 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 Database.
    """
    
    adaptertosql = AdapterToSQL()
    adapterfromdb = AdapterFromDB()
    typeadapter = TypeAdapter()
    
    decompiler = SQLDecompiler
    joinwrapper = TableWrapper
    
    selectwriter = SelectWriter
    tableclass = Table
    
    def __new__(cls, name, **kwargs):
        return dict.__new__(cls)
    
    def __init__(self, name, **kwargs):
        self._discover_lock = threading.Lock()
        
        dict.__init__(self)
        for k, v in kwargs.iteritems():
            setattr(self, k, v)
        
        self.name = self.sql_name(name)
        self.qname = self.quote(self.name)
        self.transactions = {}
        self.connect()
        self.discover_dbinfo()
    
    def __repr__(self):
        name = getattr(self, "name", "<unknown>")
        return "%s.%s(%r)" % (self.__module__, self.__class__.__name__, name)
    
    def version(self):
        """Return a string containing version info for this database."""
        raise NotImplementedError
    
    def log(self, msg):
        pass
    
    
    #                              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 Database, 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 Database, 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.
        """
        obj = self[oldname]
        if newname in self:
            dict.__delitem__(self, newname)
        dict.__setitem__(self, newname, obj)
    
    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
    
    
    #                              Container                              #
    
    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.adaptertosql.coerce(default, dbtype)
            default = " DEFAULT %s" % default
        
        return "%s %s%s" % (column.qname, dbtype, default)
    
    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 __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
        
        self.lock("Creating storage. Transactions not allowed.")
        try:
            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.execute('CREATE TABLE %s (%s%s);' %
                         (table.qname, ", ".join(fields), pk))
            
            for index in table.indices.itervalues():
                self.execute('CREATE INDEX %s ON %s (%s);' %
                             (index.qname, table.qname,
                              self.quote(index.colname)))
            dict.__setitem__(self, key, table)
        finally:
            self.unlock()
    
    def __delitem__(self, key):
        self.lock("Dropping storage. Transactions not allowed.")
        try:
            table = self[key]
            self.execute('DROP TABLE %s;' % table.qname)
            for col in table.itervalues():
                if col.autoincrement:
                    self.drop_sequence(col)
            dict.__delitem__(self, key)
        finally:
            self.unlock()
    
    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.table_name(newkey)
        
        if oldname != newname:
            newtable = oldtable.copy()
            newtable.db = self
            newtable.name = newname
            newtable.qname = self.quote(newname)
            self.lock("Renaming storage. Transactions not allowed.")
            try:
                self._rename(oldtable, newname)
            finally:
                self.unlock()
        
        # Use the superclass calls to avoid DROP TABLE/CREATE TABLE.
        dict.__delitem__(self, oldkey)
        dict.__setitem__(self, newkey, newtable)
    
    #                               Naming                               #
    
    sql_name_max_length = 64
    sql_name_caseless = False
    Prefix = ""
    
    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 _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.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.typeadapter.coerce(col, pytype)
        pytype2 = self.python_type(col.dbtype)
        col.imperfect_type = not self.isrelatedtype(pytype, pytype2)
        
        return col
    
    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.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.quote(name), self)
    
    #                             Connecting                              #
    
    poolsize = 10
    
    def connect(self):
        if self.poolsize > 0:
            self.connection = ConnectionPool(self._get_conn, self._del_conn,
                                             self.poolsize)
        else:
            self.connection = ConnectionFactory(self._get_conn, self._del_conn)
    
    def _get_conn(self):
        """Create and return a connection object."""
        # Override this with the connection call for your DB. Example:
        #     return libpq.PQconnectdb(self.connstring)
        raise NotImplementedError
    
    def _del_conn(self, conn):
        """Close a connection object."""
        # Override this with the close call (if any) for your DB.
        conn.close()
    
    def disconnect(self):
        """Release all database connections."""
        self.connection.shutdown()
    
    def execute(self, query, conn=None):
        """Return a native response for the given query."""
        if conn is None:
            conn = self.connection()
        if isinstance(query, unicode):
            query = query.encode(self.adaptertosql.encoding)
        self.log(query)
        return conn.query(query)
    
    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), self.get_transaction())
        return ResultSet(data, sel.columns, sel.imperfect)
    
    def create_database(self):
        self.lock("Creating database. Transactions not allowed.")
        try:
            self.execute("CREATE DATABASE %s;" % self.qname)
            self.clear()
        finally:
            self.unlock()
    
    def drop_database(self):
        self.lock("Dropping database. Transactions not allowed.")
        try:
            # Must shut down all connections to avoid
            # "being accessed by other users" error.
            self.connection.shutdown()
            self.execute("DROP DATABASE %s;" % self.qname)
            self.clear()
        finally:
            self.unlock()
    
    #                            Transactions                             #
    
    transaction_key = threading._get_ident
    implicit_trans = False
    
    # The "default_isolation" value should be a value native to the DB.
    default_isolation = None
    
    # The values in "isolation_levels" should match the names of
    # IsolationLevel objects in isolation.py
    isolation_levels = ["READ UNCOMMITTED", "READ COMMITTED",
                        "REPEATABLE READ", "SERIALIZABLE"]
    
    def get_transaction(self, new=False, isolation=None):
        """Return the (possibly new) connection for the current transaction.
        
        If we are already in a transaction, this returns the connection for
        that transaction. The "current transaction" is determined by a key
        (obtained by a call to self.transaction_key); by default, the key
        is the current thread ID (but subclasses are free to change this).
        
        If there is no "current transaction", a new connection object is
        obtained by calling self.connection (which is usually a connection
        pool object). If self.implicit_trans is True, new connections will
        be associated with self.transaction_key(), and repeated calls to
        get_transaction will then return the same connection object.
        If self.implicit_trans is False, you'll get a new connection
        (from the pool) each time.
        """
        key = self.transaction_key()
        if key in self.transactions:
            conn = self.transactions[key]
            if isinstance(conn, errors.TransactionLock):
                raise conn
        else:
            conn = self.connection()
            if self.implicit_trans or new:
                self.transactions[key] = conn
                if not new:
                    self.start(isolation)
        return conn
    
    def is_lock_error(self, exc):
        """If the given exception instance is a lock timeout, return True.
        
        This should return True for errors which arise from transaction
        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 isolate(self, conn, isolation=None):
        """Set the isolation level of the given connection.
        
        If 'isolation' is None, our default_isolation will be used for new
        connections. Valid values for the 'isolation' argument may be native
        values for your particular database. However, it is recommended you
        pass items from the global 'levels' list instead; these will be
        automatically replaced with native values.
        
        For many databases, this must be executed after START TRANSACTION.
        """
        if isolation is None:
            isolation = self.default_isolation
        
        if isinstance(isolation, IsolationLevel):
            # Map the given IsolationLevel object to a native value.
            isolation = isolation.name
            if isolation not in self.isolation_levels:
                raise ValueError("IsolationLevel %r not allowed by %s. "
                                 "Try one of %r instead."
                                 % (isolation, self.__class__.__name__,
                                    self.isolation_levels))
        
        # This is SQL92 syntax, and should work with most DB's.
        self.execute("SET TRANSACTION ISOLATION LEVEL %s;" % isolation, conn)
    
    def start(self, isolation=None):
        """Start a transaction. Not needed if self.implicit_trans is True."""
        conn = self.get_transaction(new=True)
        self.execute("START TRANSACTION;", conn)
        self.isolate(conn, isolation)
    
    def rollback(self):
        """Roll back the current transaction, if any."""
        key = self.transaction_key()
        if key in self.transactions:
            self.execute("ROLLBACK;", self.transactions[key])
            del self.transactions[key]
        else:
            # This is critical in order to support polygonal SM structures
            # (same store being called twice by separate proxies).
            pass
    
    def commit(self):
        """Commit the current transaction, if any."""
        key = self.transaction_key()
        try:
            conn = self.transactions.pop(key)
        except KeyError:
            # This is critical in order to support polygonal SM structures
            # (same store being called twice by separate proxies).
            pass
        else:
            self.execute("COMMIT;", conn)
    
    # Change this to 'error' if you don't want autocommit on schema ops.
    lock_contention = 'commit'
    
    def lock(self, msg=None):
        """Deny transactions during schema operations (DDL statements).
        
        Any code which calls this should also call 'unlock' in a try/finally:
        
        db.lock('dropping storage')
        try:
            drop_storage(cls)
        finally:
            db.unlock()
        """
        key = self.transaction_key()
        if key in self.transactions:
            if isinstance(self.transactions[key], errors.TransactionLock):
                return
            if self.lock_contention == 'error':
                raise errors.TransactionLock("Schema operations are not "
                                             "allowed inside transactions.")
            self.commit()
        
        if msg is None:
            msg = "Transactions not allowed at the moment."
        self.transactions[key] = errors.TransactionLock(msg)
    
    def unlock(self):
        """Allow transactions."""
        key = self.transaction_key()
        trans = self.transactions.get(key, None)
        if trans is None:
            return
        if not isinstance(trans, errors.TransactionLock):
            raise errors.TransactionLock("Unlock called inside transaction.")
        del self.transactions[key]
    
    #                              OLTP/CRUD                               #
    
    def id_clause(self, tablekey, **inputs):
        """Return an SQL expression for the identifiers of the given table."""
        t = self[tablekey]
        coerce = self.adaptertosql.coerce
        pairs = []
        for key, col in t.iteritems():
            if col.key:
                val = coerce(inputs[key], col.dbtype)
                pairs.append("%s = %s" % (col.qname, val))
        return " AND ".join(pairs)
    
    def insert(self, tablekey, **inputs):
        """Insert a row and return {idcolkey: newid}."""
        t = self[tablekey]
        coerce_out = self.adaptertosql.coerce
        coerce_in = self.adapterfromdb.coerce
        
        fields = []
        idkeys = []
        values = []
        for key, col in t.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)
        
        transconn = self.get_transaction()
        
        fields = ", ".join(fields)
        values = ", ".join(values)
        self.execute('INSERT INTO %s (%s) VALUES (%s);' %
                     (t.qname, fields, values), transconn)
        
        if idkeys:
            newids = self._grab_new_ids(t, idkeys, transconn)
            for key in newids.keys():
                col = t[key]
                newids[key] = coerce_in(newids[key], col.dbtype, col.pytype)
            return newids
        else:
            return {}
    
    def _grab_new_ids(self, table, idkeys):
        # Override this to fetch and return new autoincrement values.
        raise NotImplementedError
    
    def save(self, tablekey, **inputs):
        """Update a row using the given inputs."""
        t = self[tablekey]
        
        parms = []
        coerce = self.adaptertosql.coerce
        for key, val in inputs.iteritems():
            col = t[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;' %
                   (t.qname, ", ".join(parms), self.id_clause(tablekey, **inputs)))
            self.execute(sql, self.get_transaction())


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.db.adapterfromdb.coerce(val, col.dbtype, col.pytype)
            coerced_row.append(val)
        return coerced_row