def criterion_as_pairs(expression, consider_as_foreign_keys=None, consider_as_referenced_keys=None, any_operator=False):
    """traverse an expression and locate binary criterion pairs."""

    if consider_as_foreign_keys and consider_as_referenced_keys:
        raise exc.ArgumentError("Can only specify one of 'consider_as_foreign_keys' or 'consider_as_referenced_keys'")

    def visit_binary(binary):
        if not any_operator and binary.operator is not operators.eq:
            return
        if not isinstance(binary.left, sql.ColumnElement) or not isinstance(binary.right, sql.ColumnElement):
            return

        if consider_as_foreign_keys:
            if binary.left in consider_as_foreign_keys:
                pairs.append((binary.right, binary.left))
            elif binary.right in consider_as_foreign_keys:
                pairs.append((binary.left, binary.right))
        elif consider_as_referenced_keys:
            if binary.left in consider_as_referenced_keys:
                pairs.append((binary.left, binary.right))
            elif binary.right in consider_as_referenced_keys:
                pairs.append((binary.right, binary.left))
        else:
            if isinstance(binary.left, schema.Column) and isinstance(binary.right, schema.Column):
                if binary.left.references(binary.right):
                    pairs.append((binary.right, binary.left))
                elif binary.right.references(binary.left):
                    pairs.append((binary.left, binary.right))

    pairs = []
    visitors.traverse(expression, {}, {"binary": visit_binary})
    return pairs

def sort_tables(tables, extra_dependencies=None):
    """sort a collection of Table objects in order of their foreign-key dependency."""

    tables = list(tables)
    tuples = []
    if extra_dependencies:
        tuples.extend(extra_dependencies)

    def visit_foreign_key(fkey):
        if fkey.use_alter:
            return
        parent_table = fkey.column.table
        if parent_table in tables:
            child_table = fkey.parent.table
            if parent_table is not child_table:
                tuples.append((parent_table, child_table))

    for table in tables:
        visitors.traverse(table,
                            {'schema_visitor': True},
                            {'foreign_key': visit_foreign_key})

        tuples.extend(
            [parent, table] for parent in table._extra_dependencies
        )

    return list(topological.sort(tuples, tables))

def folded_equivalents(join, equivs=None):
    """Returns the column list of the given Join with all equivalently-named,
    equated columns folded into one column, where 'equated' means they are
    equated to each other in the ON clause of this join.

    This function is used by Join.select(fold_equivalents=True).
    
    TODO: deprecate ?
    """

    if equivs is None:
        equivs = util.Set()
    def visit_binary(binary):
        if binary.operator == operators.eq and binary.left.name == binary.right.name:
            equivs.add(binary.right)
            equivs.add(binary.left)
    visitors.traverse(join.onclause, visit_binary=visit_binary)
    collist = []
    if isinstance(join.left, expression.Join):
        left = folded_equivalents(join.left, equivs)
    else:
        left = list(join.left.columns)
    if isinstance(join.right, expression.Join):
        right = folded_equivalents(join.right, equivs)
    else:
        right = list(join.right.columns)
    used = util.Set()
    for c in left + right:
        if c in equivs:
            if c.name not in used:
                collist.append(c)
                used.add(c.name)
        else:
            collist.append(c)
    return collist

def find_tables(
    clause, check_columns=False, include_aliases=False, include_joins=False, include_selects=False, include_crud=False
):
    """locate Table objects within the given expression."""

    tables = []
    _visitors = {}

    if include_selects:
        _visitors["select"] = _visitors["compound_select"] = tables.append

    if include_joins:
        _visitors["join"] = tables.append

    if include_aliases:
        _visitors["alias"] = tables.append

    if include_crud:
        _visitors["insert"] = _visitors["update"] = _visitors["delete"] = lambda ent: tables.append(ent.table)

    if check_columns:

        def visit_column(column):
            tables.append(column.table)

        _visitors["column"] = visit_column

    _visitors["table"] = tables.append

    visitors.traverse(clause, {"column_collections": False}, _visitors)
    return tables

def find_tables(clause, check_columns=False, include_aliases=False, include_joins=False, include_selects=False):
    """locate Table objects within the given expression."""

    tables = []
    _visitors = {}

    def visit_something(elem):
        tables.append(elem)

    if include_selects:
        _visitors["select"] = _visitors["compound_select"] = visit_something

    if include_joins:
        _visitors["join"] = visit_something

    if include_aliases:
        _visitors["alias"] = visit_something

    if check_columns:

        def visit_column(column):
            tables.append(column.table)

        _visitors["column"] = visit_column

    _visitors["table"] = visit_something

    visitors.traverse(clause, {"column_collections": False}, _visitors)
    return tables

    def select(self, *clauses):
        """Run a select query
        
        Arguments
        *clauses -- SQLAlchemy index clauses

        Returns:
        [records matching clauses]
        """
        if not clauses:
            return []

        clauses = reduce(sqlalchemy.and_, clauses) if clauses else []

        tables = []
        def check_unique_table(column):
            if tables and column.table not in tables:
                raise NotImplementedError("Can't join indices yet")
            tables.append(column.table)
        visitors.traverse(clauses, {}, {'column': functools.partial(check_unique_table)})
        assert tables

        index_vals = []
        for index in self.indices:
            if index.table == tables[0]:
                index_vals.extend(index.select(clauses))
                break
        ids = set(i.id for i in index_vals)
        return self.lookup(ids)

def _params_from_query(query):
    """Pull the bind parameter values from a query.

    This takes into account any scalar attribute bindparam set up.

    E.g. params_from_query(query.filter(Cls.foo==5).filter(Cls.bar==7)))
    would return [5, 7].

    """
    v = []
    def visit_bindparam(bind):

        if bind.key in query._params:
            value = query._params[bind.key]
        elif bind.callable:
            # lazyloader may dig a callable in here, intended
            # to late-evaluate params after autoflush is called.
            # convert to a scalar value.
            value = bind.callable()
        else:
            value = bind.value

        v.append(value)
    if query._criterion is not None:
        visitors.traverse(query._criterion, {}, {'bindparam':visit_bindparam})
    for f in query._from_obj:
        visitors.traverse(f, {}, {'bindparam':visit_bindparam})
    return v

def find_tables(clause, check_columns=False, 
                include_aliases=False, include_joins=False, 
                include_selects=False, include_crud=False):
    """locate Table objects within the given expression."""

    tables = []
    _visitors = {}

    if include_selects:
        _visitors['select'] = _visitors['compound_select'] = tables.append

    if include_joins:
        _visitors['join'] = tables.append

    if include_aliases:
        _visitors['alias']  = tables.append

    if include_crud:
        _visitors['insert'] = _visitors['update'] = \
                    _visitors['delete'] = lambda ent: tables.append(ent.table)

    if check_columns:
        def visit_column(column):
            tables.append(column.table)
        _visitors['column'] = visit_column

    _visitors['table'] = tables.append

    visitors.traverse(clause, {'column_collections':False}, _visitors)
    return tables

def bind_values(clause):
    """Return an ordered list of "bound" values in the given clause.

    E.g.::

        >>> expr = and_(
        ...    table.c.foo==5, table.c.foo==7
        ... )
        >>> bind_values(expr)
        [5, 7]
    """

    v = []

    def visit_bindparam(bind):
        value = bind.value

        # evaluate callables
        if callable(value):
            value = value()

        v.append(value)

    visitors.traverse(clause, {}, {"bindparam": visit_bindparam})
    return v

def _params_from_query(query):
    """Pull the bind parameter values from a query.

    This takes into account any scalar attribute bindparam set up.

    E.g. params_from_query(query.filter(Cls.foo==5).filter(Cls.bar==7)))
    would return [5, 7].

    """
    v = []
    def visit_bindparam(bind):

        if bind.key in query._params:
            value = query._params[bind.key]
        elif bind.callable:
            # lazyloader may dig a callable in here, intended
            # to late-evaluate params after autoflush is called.
            # convert to a scalar value.
            value = bind.callable()
        else:
            value = bind.value

        v.append(value)

    # TODO: this pulls the binds from the final compiled statement.
    # ideally, this would be a little more performant if it pulled
    # from query._criterion and others directly, however this would
    # need to be implemented not to miss anything, including
    # subqueries in the columns clause.  See
    # http://stackoverflow.com/questions/9265900/sqlalchemy-how-to-traverse-bindparam-values-in-a-subquery/
    visitors.traverse(query.statement, {}, {'bindparam':visit_bindparam})
    return v

def _key_from_query(query, qualifier=None):
    """Given a Query, create a cache key.

    There are many approaches to this; here we use the simplest,
    which is to create an md5 hash of the text of the SQL statement,
    combined with stringified versions of all the bound parameters
    within it.     There's a bit of a performance hit with
    compiling out "query.statement" here; other approaches include
    setting up an explicit cache key with a particular Query,
    then combining that with the bound parameter values.

    """

    v = []
    def visit_bindparam(bind):

        if bind.key in query._params:
            value = query._params[bind.key]
        elif bind.callable:
            value = bind.callable()
        else:
            value = bind.value

        v.append(unicode(value))

    stmt = query.statement
    visitors.traverse(stmt, {}, {'bindparam': visit_bindparam})

    # here we return the key as a long string.  our "key mangler"
    # set up with the region will boil it down to an md5.
    return " ".join([unicode(stmt)] + v)

def find_columns(clause):
    """locate Column objects within the given expression."""

    cols = util.column_set()
    def visit_column(col):
        cols.add(col)
    visitors.traverse(clause, {}, {'column':visit_column})
    return cols

def reduce_columns(columns, *clauses, **kw):
    """given a list of columns, return a 'reduced' set based on natural equivalents.

    the set is reduced to the smallest list of columns which have no natural
    equivalent present in the list.  A "natural equivalent" means that two columns
    will ultimately represent the same value because they are related by a foreign key.

    \*clauses is an optional list of join clauses which will be traversed
    to further identify columns that are "equivalent".

    \**kw may specify 'ignore_nonexistent_tables' to ignore foreign keys
    whose tables are not yet configured.

    This function is primarily used to determine the most minimal "primary key"
    from a selectable, by reducing the set of primary key columns present
    in the the selectable to just those that are not repeated.

    """
    ignore_nonexistent_tables = kw.pop("ignore_nonexistent_tables", False)

    columns = util.ordered_column_set(columns)

    omit = util.column_set()
    for col in columns:
        for fk in chain(*[c.foreign_keys for c in col.proxy_set]):
            for c in columns:
                if c is col:
                    continue
                try:
                    fk_col = fk.column
                except exc.NoReferencedTableError:
                    if ignore_nonexistent_tables:
                        continue
                    else:
                        raise
                if fk_col.shares_lineage(c):
                    omit.add(col)
                    break

    if clauses:

        def visit_binary(binary):
            if binary.operator == operators.eq:
                cols = util.column_set(chain(*[c.proxy_set for c in columns.difference(omit)]))
                if binary.left in cols and binary.right in cols:
                    for c in columns:
                        if c.shares_lineage(binary.right):
                            omit.add(c)
                            break

        for clause in clauses:
            visitors.traverse(clause, {}, {"binary": visit_binary})

    return expression.ColumnSet(columns.difference(omit))

def sort_tables(tables):
    """sort a collection of Table objects in order of their foreign-key dependency."""

    tables = list(tables)
    tuples = []
    def visit_foreign_key(fkey):
        if fkey.use_alter:
            return
        parent_table = fkey.column.table
        if parent_table in tables:
            child_table = fkey.parent.table
            tuples.append( ( parent_table, child_table ) )

    for table in tables:
        visitors.traverse(table, {'schema_visitor':True}, {'foreign_key':visit_foreign_key})
    return topological.sort(tuples, tables)

    def _get_nonansi_join_whereclause(self, froms):
        clauses = []

        def visit_join(join):
            if join.isouter:
                def visit_binary(binary):
                    if binary.operator == sql_operators.eq:
                        if binary.left.table is join.right:
                            binary.left = _OuterJoinColumn(binary.left)
                        elif binary.right.table is join.right:
                            binary.right = _OuterJoinColumn(binary.right)
                clauses.append(visitors.cloned_traverse(join.onclause, {}, {'binary':visit_binary}))
            else:
                clauses.append(join.onclause)

        for f in froms:
            visitors.traverse(f, {}, {'join':visit_join})
        return sql.and_(*clauses)

def bind_values(clause):
    """Return an ordered list of "bound" values in the given clause.

    E.g.::

        >>> expr = and_(
        ...    table.c.foo==5, table.c.foo==7
        ... )
        >>> bind_values(expr)
        [5, 7]
    """

    v = []
    def visit_bindparam(bind):
        v.append(bind.effective_value)

    visitors.traverse(clause, {}, {'bindparam':visit_bindparam})
    return v

def folded_equivalents(join, equivs=None):
    """Return a list of uniquely named columns.

    The column list of the given Join will be narrowed 
    down to a list of all equivalently-named,
    equated columns folded into one column, where 'equated' means they are
    equated to each other in the ON clause of this join.

    This function is used by Join.select(fold_equivalents=True).

    Deprecated.   This function is used for a certain kind of 
    "polymorphic_union" which is designed to achieve joined
    table inheritance where the base table has no "discriminator"
    column; [ticket:1131] will provide a better way to 
    achieve this.

    """
    if equivs is None:
        equivs = set()

    def visit_binary(binary):
        if binary.operator == operators.eq and binary.left.name == binary.right.name:
            equivs.add(binary.right)
            equivs.add(binary.left)

    visitors.traverse(join.onclause, {}, {"binary": visit_binary})
    collist = []
    if isinstance(join.left, expression.Join):
        left = folded_equivalents(join.left, equivs)
    else:
        left = list(join.left.columns)
    if isinstance(join.right, expression.Join):
        right = folded_equivalents(join.right, equivs)
    else:
        right = list(join.right.columns)
    used = set()
    for c in left + right:
        if c in equivs:
            if c.name not in used:
                collist.append(c)
                used.add(c.name)
        else:
            collist.append(c)
    return collist

    def __create_lazy_clause(cls, prop, reverse_direction=False):
        binds = {}
        equated_columns = {}

        secondaryjoin = prop.secondaryjoin
        equated = dict(prop.local_remote_pairs)
        
        def should_bind(targetcol, othercol):
            if reverse_direction and not secondaryjoin:
                return othercol in equated
            else:
                return targetcol in equated

        def visit_binary(binary):
            leftcol = binary.left
            rightcol = binary.right

            equated_columns[rightcol] = leftcol
            equated_columns[leftcol] = rightcol

            if should_bind(leftcol, rightcol):
                if leftcol not in binds:
                    binds[leftcol] = sql.bindparam(None, None, type_=binary.right.type)
                binary.left = binds[leftcol]
            elif should_bind(rightcol, leftcol):
                if rightcol not in binds:
                    binds[rightcol] = sql.bindparam(None, None, type_=binary.left.type)
                binary.right = binds[rightcol]

        lazywhere = prop.primaryjoin
        
        if not prop.secondaryjoin or not reverse_direction:
            lazywhere = visitors.traverse(lazywhere, clone=True, visit_binary=visit_binary)
        
        if prop.secondaryjoin is not None:
            if reverse_direction:
                secondaryjoin = visitors.traverse(secondaryjoin, clone=True, visit_binary=visit_binary)
            lazywhere = sql.and_(lazywhere, secondaryjoin)
    
        bind_to_col = dict([(binds[col].key, col) for col in binds])
        
        return (lazywhere, bind_to_col, equated_columns)

 def visit_join(join):
     if join.isouter:
         def visit_binary(binary):
             if binary.operator == sql_operators.eq:
                 if binary.left.table is join.right:
                     binary.left = _OuterJoinColumn(binary.left)
                 elif binary.right.table is join.right:
                     binary.right = _OuterJoinColumn(binary.right)
         clauses.append(visitors.traverse(join.onclause, visit_binary=visit_binary, clone=True))
     else:
         clauses.append(join.onclause)

def reduce_columns(columns, *clauses):
    """given a list of columns, return a 'reduced' set based on natural equivalents.

    the set is reduced to the smallest list of columns which have no natural
    equivalent present in the list.  A "natural equivalent" means that two columns
    will ultimately represent the same value because they are related by a foreign key.

    \*clauses is an optional list of join clauses which will be traversed
    to further identify columns that are "equivalent".

    This function is primarily used to determine the most minimal "primary key"
    from a selectable, by reducing the set of primary key columns present
    in the the selectable to just those that are not repeated.

    """

    columns = util.OrderedSet(columns)

    omit = util.Set()
    for col in columns:
        for fk in col.foreign_keys:
            for c in columns:
                if c is col:
                    continue
                if fk.column.shares_lineage(c):
                    omit.add(col)
                    break

    if clauses:
        def visit_binary(binary):
            if binary.operator == operators.eq:
                cols = util.Set(chain(*[c.proxy_set for c in columns.difference(omit)]))
                if binary.left in cols and binary.right in cols:
                    for c in columns:
                        if c.shares_lineage(binary.right):
                            omit.add(c)
                            break
        for clause in clauses:
            visitors.traverse(clause, visit_binary=visit_binary)

    return expression.ColumnSet(columns.difference(omit))