/tests/store/base.py

Large files files are truncated, but you can click here to view the full file

# -*- coding: utf-8 -*-
#
# Copyright (c) 2006, 2007 Canonical
#
# Written by Gustavo Niemeyer <gustavo@niemeyer.net>
#
# This file is part of Storm Object Relational Mapper.
#
# Storm is free software; you can redistribute it and/or modify
# it under the terms of the GNU Lesser General Public License as
# published by the Free Software Foundation; either version 2.1 of
# the License, or (at your option) any later version.
#
# Storm is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#

from cStringIO import StringIO
import decimal
import gc
import operator
import weakref

from storm.references import Reference, ReferenceSet, Proxy
from storm.database import Result
from storm.properties import Int, Float, RawStr, Unicode, Property, Pickle
from storm.properties import PropertyPublisherMeta, Decimal
from storm.variables import PickleVariable
from storm.expr import (
    Asc, Desc, Select, LeftJoin, SQL, Count, Sum, Avg, And, Or, Eq, Lower, Alias)
from storm.variables import Variable, UnicodeVariable, IntVariable
from storm.info import get_obj_info, ClassAlias
from storm.exceptions import (
    ClosedError, ConnectionBlockedError, FeatureError, LostObjectError,
    NoStoreError, NotFlushedError, NotOneError, OrderLoopError, UnorderedError,
    WrongStoreError)
from storm.cache import Cache
from storm.store import AutoReload, EmptyResultSet, Store, ResultSet
from storm.tracer import debug

from tests.info import Wrapper
from tests.helper import TestHelper


class Foo(object):
    __storm_table__ = "foo"
    id = Int(primary=True)
    title = Unicode()

class Bar(object):
    __storm_table__ = "bar"
    id = Int(primary=True)
    title = Unicode()
    foo_id = Int()
    foo = Reference(foo_id, Foo.id)

class Blob(object):
    __storm_table__ = "bin"
    id = Int(primary=True)
    bin = RawStr()

class Link(object):
    __storm_table__ = "link"
    __storm_primary__ = "foo_id", "bar_id"
    foo_id = Int()
    bar_id = Int()

class SelfRef(object):
    __storm_table__ = "selfref"
    id = Int(primary=True)
    title = Unicode()
    selfref_id = Int()
    selfref = Reference(selfref_id, id)
    selfref_on_remote = Reference(id, selfref_id, on_remote=True)

class FooRef(Foo):
    bar = Reference(Foo.id, Bar.foo_id)

class FooRefSet(Foo):
    bars = ReferenceSet(Foo.id, Bar.foo_id)

class FooRefSetOrderID(Foo):
    bars = ReferenceSet(Foo.id, Bar.foo_id, order_by=Bar.id)

class FooRefSetOrderTitle(Foo):
    bars = ReferenceSet(Foo.id, Bar.foo_id, order_by=Bar.title)

class FooIndRefSet(Foo):
    bars = ReferenceSet(Foo.id, Link.foo_id, Link.bar_id, Bar.id)

class FooIndRefSetOrderID(Foo):
    bars = ReferenceSet(Foo.id, Link.foo_id, Link.bar_id, Bar.id,
                        order_by=Bar.id)

class FooIndRefSetOrderTitle(Foo):
    bars = ReferenceSet(Foo.id, Link.foo_id, Link.bar_id, Bar.id,
                        order_by=Bar.title)


class FooValue(object):
    __storm_table__ = "foovalue"
    id = Int(primary=True)
    foo_id = Int()
    value1 = Int()
    value2 = Int()

class BarProxy(object):
    __storm_table__ = "bar"
    id = Int(primary=True)
    title = Unicode()
    foo_id = Int()
    foo = Reference(foo_id, Foo.id)
    foo_title = Proxy(foo, Foo.title)

class Money(object):
    __storm_table__ = "money"
    id = Int(primary=True)
    value = Decimal()


class DecorateVariable(Variable):

    def parse_get(self, value, to_db):
        return u"to_%s(%s)" % (to_db and "db" or "py", value)

    def parse_set(self, value, from_db):
        return u"from_%s(%s)" % (from_db and "db" or "py", value)


class FooVariable(Foo):
    title = Property(variable_class=DecorateVariable)


class DummyDatabase(object):

    def connect(self, event=None):
        return None


class StoreCacheTest(TestHelper):

    def test_wb_custom_cache(self):
        cache = Cache(25)
        store = Store(DummyDatabase(), cache=cache)
        self.assertEquals(store._cache, cache)

    def test_wb_default_cache_size(self):
        store = Store(DummyDatabase())
        self.assertEquals(store._cache._size, 1000)


class StoreDatabaseTest(TestHelper):

    def test_store_has_reference_to_its_database(self):
        database = DummyDatabase()
        store = Store(database)
        self.assertIdentical(store.get_database(), database)


class StoreTest(object):

    def setUp(self):
        self.store = None
        self.stores = []
        self.create_database()
        self.connection = self.database.connect()
        self.drop_tables()
        self.create_tables()
        self.create_sample_data()
        self.create_store()

    def tearDown(self):
        self.drop_store()
        self.drop_sample_data()
        self.drop_tables()
        self.drop_database()
        self.connection.close()

    def create_database(self):
        raise NotImplementedError

    def create_tables(self):
        raise NotImplementedError

    def create_sample_data(self):
        connection = self.connection
        connection.execute("INSERT INTO foo (id, title)"
                           " VALUES (10, 'Title 30')")
        connection.execute("INSERT INTO foo (id, title)"
                           " VALUES (20, 'Title 20')")
        connection.execute("INSERT INTO foo (id, title)"
                           " VALUES (30, 'Title 10')")
        connection.execute("INSERT INTO bar (id, foo_id, title)"
                           " VALUES (100, 10, 'Title 300')")
        connection.execute("INSERT INTO bar (id, foo_id, title)"
                           " VALUES (200, 20, 'Title 200')")
        connection.execute("INSERT INTO bar (id, foo_id, title)"
                           " VALUES (300, 30, 'Title 100')")
        connection.execute("INSERT INTO bin (id, bin) VALUES (10, 'Blob 30')")
        connection.execute("INSERT INTO bin (id, bin) VALUES (20, 'Blob 20')")
        connection.execute("INSERT INTO bin (id, bin) VALUES (30, 'Blob 10')")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 100)")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 200)")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 300)")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (20, 100)")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (20, 200)")
        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (30, 300)")
        connection.execute("INSERT INTO money (id, value)"
                           " VALUES (10, '12.3455')")
        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
                           " VALUES (15, 'SelfRef 15', NULL)")
        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
                           " VALUES (25, 'SelfRef 25', NULL)")
        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
                           " VALUES (35, 'SelfRef 35', 15)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (1, 10, 2, 1)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (2, 10, 2, 1)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (3, 10, 2, 1)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (4, 10, 2, 2)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (5, 20, 1, 3)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (6, 20, 1, 3)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (7, 20, 1, 4)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (8, 20, 1, 4)")
        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
                           " VALUES (9, 20, 1, 2)")

        connection.commit()

    def create_store(self):
        store = Store(self.database)
        self.stores.append(store)
        if self.store is None:
            self.store = store
        return store

    def drop_store(self):
        for store in self.stores:
            store.rollback()

            # Closing the store is needed because testcase objects are all
            # instantiated at once, and thus connections are kept open.
            store.close()

    def drop_sample_data(self):
        pass

    def drop_tables(self):
        for table in ["foo", "bar", "bin", "link", "money", "selfref",
                      "foovalue"]:
            try:
                self.connection.execute("DROP TABLE %s" % table)
                self.connection.commit()
            except:
                self.connection.rollback()

    def drop_database(self):
        pass

    def get_items(self):
        # Bypass the store to avoid flushing.
        connection = self.store._connection
        result = connection.execute("SELECT * FROM foo ORDER BY id")
        return list(result)

    def get_committed_items(self):
        connection = self.database.connect()
        result = connection.execute("SELECT * FROM foo ORDER BY id")
        return list(result)

    def get_cache(self, store):
        # We don't offer a public API for this just yet.
        return store._cache

    def test_execute(self):
        result = self.store.execute("SELECT 1")
        self.assertTrue(isinstance(result, Result))
        self.assertEquals(result.get_one(), (1,))

        result = self.store.execute("SELECT 1", noresult=True)
        self.assertEquals(result, None)

    def test_execute_params(self):
        result = self.store.execute("SELECT ?", [1])
        self.assertTrue(isinstance(result, Result))
        self.assertEquals(result.get_one(), (1,))

    def test_execute_flushes(self):
        foo = self.store.get(Foo, 10)
        foo.title = u"New Title"

        result = self.store.execute("SELECT title FROM foo WHERE id=10")
        self.assertEquals(result.get_one(), ("New Title",))

    def test_close(self):
        store = Store(self.database)
        store.close()
        self.assertRaises(ClosedError, store.execute, "SELECT 1")

    def test_get(self):
        foo = self.store.get(Foo, 10)
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.get(Foo, 20)
        self.assertEquals(foo.id, 20)
        self.assertEquals(foo.title, "Title 20")

        foo = self.store.get(Foo, 40)
        self.assertEquals(foo, None)

    def test_get_cached(self):
        foo = self.store.get(Foo, 10)
        self.assertTrue(self.store.get(Foo, 10) is foo)

    def test_wb_get_cached_doesnt_need_connection(self):
        foo = self.store.get(Foo, 10)
        connection = self.store._connection
        self.store._connection = None
        self.store.get(Foo, 10)
        self.store._connection = connection

    def test_cache_cleanup(self):
        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        foo = self.store.get(Foo, 10)
        foo.taint = True

        del foo
        gc.collect()

        foo = self.store.get(Foo, 10)
        self.assertFalse(getattr(foo, "taint", False))

    def test_add_returns_object(self):
        """
        Store.add() returns the object passed to it.  This allows this
        kind of code:

        thing = Thing()
        store.add(thing)
        return thing

        to be simplified as:

        return store.add(Thing())
        """
        foo = Foo()
        self.assertEquals(self.store.add(foo), foo)

    def test_add_and_stop_referencing(self):
        # After adding an object, no references should be needed in
        # python for it still to be added to the database.
        foo = Foo()
        foo.title = u"live"
        self.store.add(foo)

        del foo
        gc.collect()

        self.assertTrue(self.store.find(Foo, title=u"live").one())

    def test_obj_info_with_deleted_object(self):
        # Let's try to put Storm in trouble by killing the object
        # while still holding a reference to the obj_info.

        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        class MyFoo(Foo):
            loaded = False
            def __storm_loaded__(self):
                self.loaded = True

        foo = self.store.get(MyFoo, 20)
        foo.tainted = True
        obj_info = get_obj_info(foo)

        del foo
        gc.collect()

        self.assertEquals(obj_info.get_obj(), None)

        foo = self.store.find(MyFoo, id=20).one()
        self.assertTrue(foo)
        self.assertFalse(getattr(foo, "tainted", False))

        # The object was rebuilt, so the loaded hook must have run.
        self.assertTrue(foo.loaded)

    def test_obj_info_with_deleted_object_and_changed_event(self):
        """
        When an object is collected, the variables disable change notification
        to not create a leak. If we're holding a reference to the obj_info and
        rebuild the object, it should re-enable change notication.
        """
        class PickleBlob(Blob):
            bin = Pickle()

        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        blob = self.store.get(Blob, 20)
        blob.bin = "\x80\x02}q\x01U\x01aK\x01s."
        self.store.flush()
        del blob
        gc.collect()

        pickle_blob = self.store.get(PickleBlob, 20)
        obj_info = get_obj_info(pickle_blob)
        del pickle_blob
        gc.collect()
        self.assertEquals(obj_info.get_obj(), None)

        pickle_blob = self.store.get(PickleBlob, 20)
        pickle_blob.bin = "foobin"
        events = []
        obj_info.event.hook("changed", lambda *args: events.append(args))

        self.store.flush()
        self.assertEquals(len(events), 1)

    def test_wb_flush_event_with_deleted_object_before_flush(self):
        """
        When an object is deleted before flush and it contains mutable
        variables, those variables unhook from the global event system to
        prevent a leak.
        """
        class PickleBlob(Blob):
            bin = Pickle()

        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        blob = self.store.get(Blob, 20)
        blob.bin = "\x80\x02}q\x01U\x01aK\x01s."
        self.store.flush()
        del blob
        gc.collect()

        pickle_blob = self.store.get(PickleBlob, 20)
        pickle_blob.bin = "foobin"
        del pickle_blob

        self.store.flush()
        self.assertEquals(self.store._event._hooks["flush"], set())

    def test_mutable_variable_detect_change_from_alive(self):
        """
        Changes in a mutable variable like a L{PickleVariable} are correctly
        detected, even if the object comes from the alive cache.
        """
        class PickleBlob(Blob):
            bin = Pickle()

        blob = PickleBlob()
        blob.bin = {"k": "v"}
        blob.id = 4000
        self.store.add(blob)
        self.store.commit()

        blob = self.store.find(PickleBlob, PickleBlob.id == 4000).one()
        blob.bin["k1"] = "v1"

        self.store.commit()

        blob = self.store.find(PickleBlob, PickleBlob.id == 4000).one()
        self.assertEquals(blob.bin, {"k1": "v1", "k": "v"})

    def test_wb_checkpoint_doesnt_override_changed(self):
        """
        This test ensures that we don't uselessly checkpoint when getting back
        objects from the alive cache, which would hide changed values from the
        store.
        """
        foo = self.store.get(Foo, 20)
        foo.title = u"changed"
        self.store.block_implicit_flushes()
        foo2 = self.store.find(Foo, Foo.id == 20).one()
        self.store.unblock_implicit_flushes()
        self.store.commit()

        foo3 = self.store.find(Foo, Foo.id == 20).one()
        self.assertEquals(foo3.title, u"changed")

    def test_obj_info_with_deleted_object_with_get(self):
        # Same thing, but using get rather than find.

        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        foo = self.store.get(Foo, 20)
        foo.tainted = True
        obj_info = get_obj_info(foo)

        del foo
        gc.collect()

        self.assertEquals(obj_info.get_obj(), None)

        foo = self.store.get(Foo, 20)
        self.assertTrue(foo)
        self.assertFalse(getattr(foo, "tainted", False))

    def test_delete_object_when_obj_info_is_dirty(self):
        """Object should stay in memory if dirty."""

        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        foo = self.store.get(Foo, 20)
        foo.title = u"Changed"
        foo.tainted = True
        obj_info = get_obj_info(foo)

        del foo
        gc.collect()

        self.assertTrue(obj_info.get_obj())

    def test_get_tuple(self):
        class MyFoo(Foo):
            __storm_primary__ = "title", "id"
        foo = self.store.get(MyFoo, (u"Title 30", 10))
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.get(MyFoo, (u"Title 20", 10))
        self.assertEquals(foo, None)

    def test_of(self):
        foo = self.store.get(Foo, 10)
        self.assertEquals(Store.of(foo), self.store)
        self.assertEquals(Store.of(Foo()), None)
        self.assertEquals(Store.of(object()), None)

    def test_is_empty(self):
        result = self.store.find(Foo, id=300)
        self.assertEquals(result.is_empty(), True)
        result = self.store.find(Foo, id=30)
        self.assertEquals(result.is_empty(), False)

    def test_is_empty_strips_order_by(self):
        """
        L{ResultSet.is_empty} strips the C{ORDER BY} clause, if one is
        present, since it isn't required to actually determine if a result set
        has any matching rows.  This should provide a performance improvement
        when the ordered result set would be large.
        """
        stream = StringIO()
        self.addCleanup(debug, False)
        debug(True, stream)

        result = self.store.find(Foo, Foo.id == 300)
        result.order_by(Foo.id)
        self.assertEqual(True, result.is_empty())
        self.assertNotIn("ORDER BY", stream.getvalue())

    def test_is_empty_with_composed_key(self):
        result = self.store.find(Link, foo_id=300, bar_id=3000)
        self.assertEquals(result.is_empty(), True)
        result = self.store.find(Link, foo_id=30, bar_id=300)
        self.assertEquals(result.is_empty(), False)

    def test_is_empty_with_expression_find(self):
        result = self.store.find(Foo.title, Foo.id == 300)
        self.assertEquals(result.is_empty(), True)
        result = self.store.find(Foo.title, Foo.id == 30)
        self.assertEquals(result.is_empty(), False)

    def test_find_iter(self):
        result = self.store.find(Foo)

        lst = [(foo.id, foo.title) for foo in result]
        lst.sort()
        self.assertEquals(lst, [
                          (10, "Title 30"),
                          (20, "Title 20"),
                          (30, "Title 10"),
                         ])

    def test_find_from_cache(self):
        foo = self.store.get(Foo, 10)
        self.assertTrue(self.store.find(Foo, id=10).one() is foo)

    def test_find_expr(self):
        result = self.store.find(Foo, Foo.id == 20,
                                 Foo.title == u"Title 20")
        self.assertEquals([(foo.id, foo.title) for foo in result], [
                          (20, "Title 20"),
                         ])

        result = self.store.find(Foo, Foo.id == 10,
                                 Foo.title == u"Title 20")
        self.assertEquals([(foo.id, foo.title) for foo in result], [
                         ])

    def test_find_sql(self):
        foo = self.store.find(Foo, SQL("foo.id = 20")).one()
        self.assertEquals(foo.title, "Title 20")

    def test_find_str(self):
        foo = self.store.find(Foo, "foo.id = 20").one()
        self.assertEquals(foo.title, "Title 20")

    def test_find_keywords(self):
        result = self.store.find(Foo, id=20, title=u"Title 20")
        self.assertEquals([(foo.id, foo.title) for foo in result], [
                          (20, u"Title 20")
                         ])

        result = self.store.find(Foo, id=10, title=u"Title 20")
        self.assertEquals([(foo.id, foo.title) for foo in result], [
                         ])

    def test_find_order_by(self, *args):
        result = self.store.find(Foo).order_by(Foo.title)
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [
                          (30, "Title 10"),
                          (20, "Title 20"),
                          (10, "Title 30"),
                         ])

    def test_find_order_asc(self, *args):
        result = self.store.find(Foo).order_by(Asc(Foo.title))
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [
                          (30, "Title 10"),
                          (20, "Title 20"),
                          (10, "Title 30"),
                         ])

    def test_find_order_desc(self, *args):
        result = self.store.find(Foo).order_by(Desc(Foo.title))
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [
                          (10, "Title 30"),
                          (20, "Title 20"),
                          (30, "Title 10"),
                         ])

    def test_find_default_order_asc(self):
        class MyFoo(Foo):
            __storm_order__ = "title"

        result = self.store.find(MyFoo)
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [
                          (30, "Title 10"),
                          (20, "Title 20"),
                          (10, "Title 30"),
                         ])

    def test_find_default_order_desc(self):
        class MyFoo(Foo):
            __storm_order__ = "-title"

        result = self.store.find(MyFoo)
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [
                          (10, "Title 30"),
                          (20, "Title 20"),
                          (30, "Title 10"),
                         ])

    def test_find_default_order_with_tuple(self):
        class MyLink(Link):
            __storm_order__ = ("foo_id", "-bar_id")

        result = self.store.find(MyLink)
        lst = [(link.foo_id, link.bar_id) for link in result]
        self.assertEquals(lst, [
                          (10, 300),
                          (10, 200),
                          (10, 100),
                          (20, 200),
                          (20, 100),
                          (30, 300),
                         ])

    def test_find_default_order_with_tuple_and_expr(self):
        class MyLink(Link):
            __storm_order__ = ("foo_id", Desc(Link.bar_id))

        result = self.store.find(MyLink)
        lst = [(link.foo_id, link.bar_id) for link in result]
        self.assertEquals(lst, [
                          (10, 300),
                          (10, 200),
                          (10, 100),
                          (20, 200),
                          (20, 100),
                          (30, 300),
                         ])

    def test_find_index(self):
        """
        L{ResultSet.__getitem__} returns the object at the specified index.
        if a slice is used, a new L{ResultSet} is returned configured with the
        appropriate offset and limit.
        """
        foo = self.store.find(Foo).order_by(Foo.title)[0]
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, "Title 10")

        foo = self.store.find(Foo).order_by(Foo.title)[1]
        self.assertEquals(foo.id, 20)
        self.assertEquals(foo.title, "Title 20")

        foo = self.store.find(Foo).order_by(Foo.title)[2]
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.find(Foo).order_by(Foo.title)[1:][1]
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        result = self.store.find(Foo).order_by(Foo.title)
        self.assertRaises(IndexError, result.__getitem__, 3)

    def test_find_slice(self):
        result = self.store.find(Foo).order_by(Foo.title)[1:2]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20")])

    def test_find_slice_offset(self):
        result = self.store.find(Foo).order_by(Foo.title)[1:]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20"),
                           (10, "Title 30")])

    def test_find_slice_offset_any(self):
        foo = self.store.find(Foo).order_by(Foo.title)[1:].any()
        self.assertEquals(foo.id, 20)
        self.assertEquals(foo.title, "Title 20")

    def test_find_slice_offset_one(self):
        foo = self.store.find(Foo).order_by(Foo.title)[1:2].one()
        self.assertEquals(foo.id, 20)
        self.assertEquals(foo.title, "Title 20")

    def test_find_slice_offset_first(self):
        foo = self.store.find(Foo).order_by(Foo.title)[1:].first()
        self.assertEquals(foo.id, 20)
        self.assertEquals(foo.title, "Title 20")

    def test_find_slice_offset_last(self):
        foo = self.store.find(Foo).order_by(Foo.title)[1:].last()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

    def test_find_slice_limit(self):
        result = self.store.find(Foo).order_by(Foo.title)[:2]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(30, "Title 10"),
                           (20, "Title 20")])

    def test_find_slice_limit_last(self):
        result = self.store.find(Foo).order_by(Foo.title)[:2]
        self.assertRaises(FeatureError, result.last)

    def test_find_slice_slice(self):
        result = self.store.find(Foo).order_by(Foo.title)[0:2][1:3]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20")])

        result = self.store.find(Foo).order_by(Foo.title)[:2][1:3]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20")])

        result = self.store.find(Foo).order_by(Foo.title)[1:3][0:1]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20")])

        result = self.store.find(Foo).order_by(Foo.title)[1:3][:1]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst,
                          [(20, "Title 20")])

        result = self.store.find(Foo).order_by(Foo.title)[5:5][1:1]
        lst = [(foo.id, foo.title) for foo in result]
        self.assertEquals(lst, [])

    def test_find_slice_order_by(self):
        result = self.store.find(Foo)[2:]
        self.assertRaises(FeatureError, result.order_by, None)

        result = self.store.find(Foo)[:2]
        self.assertRaises(FeatureError, result.order_by, None)

    def test_find_slice_remove(self):
        result = self.store.find(Foo)[2:]
        self.assertRaises(FeatureError, result.remove)

        result = self.store.find(Foo)[:2]
        self.assertRaises(FeatureError, result.remove)

    def test_find_contains(self):
        foo = self.store.get(Foo, 10)
        result = self.store.find(Foo)
        self.assertEquals(foo in result, True)
        result = self.store.find(Foo, Foo.id == 20)
        self.assertEquals(foo in result, False)
        result = self.store.find(Foo, "foo.id = 20")
        self.assertEquals(foo in result, False)

    def test_find_contains_wrong_type(self):
        foo = self.store.get(Foo, 10)
        bar = self.store.get(Bar, 200)
        self.assertRaises(TypeError, operator.contains,
                          self.store.find(Foo), bar)
        self.assertRaises(TypeError, operator.contains,
                          self.store.find((Foo,)), foo)
        self.assertRaises(TypeError, operator.contains,
                          self.store.find(Foo), (foo,))
        self.assertRaises(TypeError, operator.contains,
                          self.store.find((Foo, Bar)), (bar, foo))

    def test_find_contains_does_not_use_iter(self):
        def no_iter(self):
            raise RuntimeError()
        orig_iter = ResultSet.__iter__
        ResultSet.__iter__ = no_iter
        try:
            foo = self.store.get(Foo, 10)
            result = self.store.find(Foo)
            self.assertEquals(foo in result, True)
        finally:
            ResultSet.__iter__ = orig_iter

    def test_find_contains_with_composed_key(self):
        link = self.store.get(Link, (10, 100))
        result = self.store.find(Link, Link.foo_id == 10)
        self.assertEquals(link in result, True)
        result = self.store.find(Link, Link.bar_id == 200)
        self.assertEquals(link in result, False)

    def test_find_contains_with_set_expression(self):
        foo = self.store.get(Foo, 10)
        result1 = self.store.find(Foo, Foo.id == 10)
        result2 = self.store.find(Foo, Foo.id != 10)
        self.assertEquals(foo in result1.union(result2), True)

        if self.__class__.__name__.startswith("MySQL"):
            return
        self.assertEquals(foo in result1.intersection(result2), False)
        self.assertEquals(foo in result1.intersection(result1), True)
        self.assertEquals(foo in result1.difference(result2), True)
        self.assertEquals(foo in result1.difference(result1), False)

    def test_find_any(self, *args):
        """
        L{ResultSet.any} returns an arbitrary objects from the result set.
        """
        self.assertNotEqual(None, self.store.find(Foo).any())
        self.assertEqual(None, self.store.find(Foo, id=40).any())

    def test_find_any_strips_order_by(self):
        """
        L{ResultSet.any} strips the C{ORDER BY} clause, if one is present,
        since it isn't required.  This should provide a performance
        improvement when the ordered result set would be large.
        """
        stream = StringIO()
        self.addCleanup(debug, False)
        debug(True, stream)

        result = self.store.find(Foo, Foo.id == 300)
        result.order_by(Foo.id)
        result.any()
        self.assertNotIn("ORDER BY", stream.getvalue())

    def test_find_first(self, *args):
        self.assertRaises(UnorderedError, self.store.find(Foo).first)

        foo = self.store.find(Foo).order_by(Foo.title).first()
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, "Title 10")

        foo = self.store.find(Foo).order_by(Foo.id).first()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.find(Foo, id=40).order_by(Foo.id).first()
        self.assertEquals(foo, None)

    def test_find_last(self, *args):
        self.assertRaises(UnorderedError, self.store.find(Foo).last)

        foo = self.store.find(Foo).order_by(Foo.title).last()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.find(Foo).order_by(Foo.id).last()
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, "Title 10")

        foo = self.store.find(Foo, id=40).order_by(Foo.id).last()
        self.assertEquals(foo, None)

    def test_find_last_desc(self, *args):
        foo = self.store.find(Foo).order_by(Desc(Foo.title)).last()
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, "Title 10")

        foo = self.store.find(Foo).order_by(Asc(Foo.id)).last()
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, "Title 10")

    def test_find_one(self, *args):
        self.assertRaises(NotOneError, self.store.find(Foo).one)

        foo = self.store.find(Foo, id=10).one()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.find(Foo, id=40).one()
        self.assertEquals(foo, None)

    def test_find_count(self):
        self.assertEquals(self.store.find(Foo).count(), 3)

    def test_find_count_after_slice(self):
        """
        When we slice a ResultSet obtained after a set operation (like union),
        we get a fresh select that doesn't modify the limit and offset
        attribute of the original ResultSet.
        """
        result1 = self.store.find(Foo, Foo.id == 10)
        result2 = self.store.find(Foo, Foo.id == 20)
        result3 = result1.union(result2)
        result3.order_by(Foo.id)
        self.assertEquals(result3.count(), 2)

        result_slice = list(result3[:2])
        self.assertEquals(result3.count(), 2)

    def test_find_count_column(self):
        self.assertEquals(self.store.find(Link).count(Link.foo_id), 6)

    def test_find_count_column_distinct(self):
        count = self.store.find(Link).count(Link.foo_id, distinct=True)
        self.assertEquals(count, 3)

    def test_find_limit_count(self):
        result = self.store.find(Link.foo_id)
        result.config(limit=2)
        count = result.count()
        self.assertEquals(count, 2)

    def test_find_offset_count(self):
        result = self.store.find(Link.foo_id)
        result.config(offset=3)
        count = result.count()
        self.assertEquals(count, 3)

    def test_find_sliced_count(self):
        result = self.store.find(Link.foo_id)
        count = result[2:4].count()
        self.assertEquals(count, 2)

    def test_find_distinct_count(self):
        result = self.store.find(Link.foo_id)
        result.config(distinct=True)
        count = result.count()
        self.assertEquals(count, 3)

    def test_find_distinct_order_by_limit_count(self):
        result = self.store.find(Foo)
        result.order_by(Foo.title)
        result.config(distinct=True, limit=3)
        count = result.count()
        self.assertEquals(count, 3)

    def test_find_distinct_count_multiple_columns(self):
        result = self.store.find((Link.foo_id, Link.bar_id))
        result.config(distinct=True)
        count = result.count()
        self.assertEquals(count, 6)

    def test_find_count_column_with_implicit_distinct(self):
        result = self.store.find(Link)
        result.config(distinct=True)
        count = result.count(Link.foo_id)
        self.assertEquals(count, 6)

    def test_find_max(self):
        self.assertEquals(self.store.find(Foo).max(Foo.id), 30)

    def test_find_max_expr(self):
        self.assertEquals(self.store.find(Foo).max(Foo.id + 1), 31)

    def test_find_max_unicode(self):
        title = self.store.find(Foo).max(Foo.title)
        self.assertEquals(title, "Title 30")
        self.assertTrue(isinstance(title, unicode))

    def test_find_max_with_empty_result_and_disallow_none(self):
        class Bar(object):
            __storm_table__ = "bar"
            id = Int(primary=True)
            foo_id = Int(allow_none=False)

        result = self.store.find(Bar, Bar.id > 1000)
        self.assertTrue(result.is_empty())
        self.assertEquals(result.max(Bar.foo_id), None)

    def test_find_min(self):
        self.assertEquals(self.store.find(Foo).min(Foo.id), 10)

    def test_find_min_expr(self):
        self.assertEquals(self.store.find(Foo).min(Foo.id - 1), 9)

    def test_find_min_unicode(self):
        title = self.store.find(Foo).min(Foo.title)
        self.assertEquals(title, "Title 10")
        self.assertTrue(isinstance(title, unicode))

    def test_find_min_with_empty_result_and_disallow_none(self):
        class Bar(object):
            __storm_table__ = "bar"
            id = Int(primary=True)
            foo_id = Int(allow_none=False)

        result = self.store.find(Bar, Bar.id > 1000)
        self.assertTrue(result.is_empty())
        self.assertEquals(result.min(Bar.foo_id), None)

    def test_find_avg(self):
        self.assertEquals(self.store.find(Foo).avg(Foo.id), 20)

    def test_find_avg_expr(self):
        self.assertEquals(self.store.find(Foo).avg(Foo.id + 10), 30)

    def test_find_avg_float(self):
        foo = Foo()
        foo.id = 15
        foo.title = u"Title 15"
        self.store.add(foo)
        self.assertEquals(self.store.find(Foo).avg(Foo.id), 18.75)

    def test_find_sum(self):
        self.assertEquals(self.store.find(Foo).sum(Foo.id), 60)

    def test_find_sum_expr(self):
        self.assertEquals(self.store.find(Foo).sum(Foo.id * 2), 120)

    def test_find_sum_with_empty_result_and_disallow_none(self):
        class Bar(object):
            __storm_table__ = "bar"
            id = Int(primary=True)
            foo_id = Int(allow_none=False)

        result = self.store.find(Bar, Bar.id > 1000)
        self.assertTrue(result.is_empty())
        self.assertEquals(result.sum(Bar.foo_id), None)

    def test_find_max_order_by(self):
        """Interaction between order by and aggregation shouldn't break."""
        result = self.store.find(Foo)
        self.assertEquals(result.order_by(Foo.id).max(Foo.id), 30)

    def test_find_get_select_expr_without_columns(self):
        """
        A L{FeatureError} is raised if L{ResultSet.get_select_expr} is called
        without a list of L{Column}s.
        """
        result = self.store.find(Foo)
        self.assertRaises(FeatureError, result.get_select_expr)

    def test_find_get_select_expr(self):
        """
        Only the specified L{Column}s are included in the L{Select} expression
        provided by L{ResultSet.get_select_expr}.
        """
        foo = self.store.get(Foo, 10)
        result1 = self.store.find(Foo, Foo.id <= 10)
        subselect = result1.get_select_expr(Foo.id)
        self.assertEqual((Foo.id,), subselect.columns)
        result2 = self.store.find(Foo, Foo.id.is_in(subselect))
        self.assertEqual([foo], list(result2))

    def test_find_get_select_expr_with_set_expression(self):
        """
        A L{FeatureError} is raised if L{ResultSet.get_select_expr} is used
        with a L{ResultSet} that represents a set expression, such as a union.
        """
        result1 = self.store.find(Foo, Foo.id == 10)
        result2 = self.store.find(Foo, Foo.id == 20)
        result3 = result1.union(result2)
        self.assertRaises(FeatureError, result3.get_select_expr, Foo.id)

    def test_find_values(self):
        values = self.store.find(Foo).order_by(Foo.id).values(Foo.id)
        self.assertEquals(list(values), [10, 20, 30])

        values = self.store.find(Foo).order_by(Foo.id).values(Foo.title)
        values = list(values)
        self.assertEquals(values, ["Title 30", "Title 20", "Title 10"])
        self.assertEquals([type(value) for value in values],
                          [unicode, unicode, unicode])

    def test_find_multiple_values(self):
        result = self.store.find(Foo).order_by(Foo.id)
        values = result.values(Foo.id, Foo.title)
        self.assertEquals(list(values),
                          [(10, "Title 30"),
                           (20, "Title 20"),
                           (30, "Title 10")])

    def test_find_values_with_no_arguments(self):
        result = self.store.find(Foo).order_by(Foo.id)
        self.assertRaises(FeatureError, result.values().next)

    def test_find_slice_values(self):
        values = self.store.find(Foo).order_by(Foo.id)[1:2].values(Foo.id)
        self.assertEquals(list(values), [20])

    def test_find_values_with_set_expression(self):
        """
        A L{FeatureError} is raised if L{ResultSet.values} is used with a
        L{ResultSet} that represents a set expression, such as a union.
        """
        result1 = self.store.find(Foo, Foo.id == 10)
        result2 = self.store.find(Foo, Foo.id == 20)
        result3 = result1.union(result2)
        self.assertRaises(FeatureError, list, result3.values(Foo.id))

    def test_find_remove(self):
        self.store.find(Foo, Foo.id == 20).remove()
        self.assertEquals(self.get_items(), [
                          (10, "Title 30"),
                          (30, "Title 10"),
                         ])

    def test_find_cached(self):
        foo = self.store.get(Foo, 20)
        bar = self.store.get(Bar, 200)
        self.assertTrue(foo)
        self.assertTrue(bar)
        self.assertEquals(self.store.find(Foo).cached(), [foo])

    def test_find_cached_where(self):
        foo1 = self.store.get(Foo, 10)
        foo2 = self.store.get(Foo, 20)
        bar = self.store.get(Bar, 200)
        self.assertTrue(foo1)
        self.assertTrue(foo2)
        self.assertTrue(bar)
        self.assertEquals(self.store.find(Foo, title=u"Title 20").cached(),
                          [foo2])

    def test_find_cached_invalidated(self):
        foo = self.store.get(Foo, 20)
        self.store.invalidate(foo)
        self.assertEquals(self.store.find(Foo).cached(), [foo])

    def test_find_cached_invalidated_and_deleted(self):
        foo = self.store.get(Foo, 20)
        self.store.execute("DELETE FROM foo WHERE id=20")
        self.store.invalidate(foo)
        # Do not look for the primary key (id), since it's able to get
        # it without touching the database. Use the title instead.
        self.assertEquals(self.store.find(Foo, title=u"Title 20").cached(), [])

    def test_find_cached_with_info_alive_and_object_dead(self):
        # Disable the cache, which holds strong references.
        self.get_cache(self.store).set_size(0)

        foo = self.store.get(Foo, 20)
        foo.tainted = True
        obj_info = get_obj_info(foo)
        del foo
        gc.collect()
        cached = self.store.find(Foo).cached()
        self.assertEquals(len(cached), 1)
        foo = self.store.get(Foo, 20)
        self.assertFalse(hasattr(foo, "tainted"))

    def test_using_find_join(self):
        bar = self.store.get(Bar, 100)
        bar.foo_id = None

        tables = self.store.using(Foo, LeftJoin(Bar, Bar.foo_id == Foo.id))
        result = tables.find(Bar).order_by(Foo.id, Bar.id)
        lst = [bar and (bar.id, bar.title) for bar in result]
        self.assertEquals(lst, [
                          None,
                          (200, u"Title 200"),
                          (300, u"Title 100"),
                         ])

    def test_using_find_with_strings(self):
        foo = self.store.using("foo").find(Foo, id=10).one()
        self.assertEquals(foo.title, "Title 30")

        foo = self.store.using("foo", "bar").find(Foo, id=10).any()
        self.assertEquals(foo.title, "Title 30")

    def test_using_find_join_with_strings(self):
        bar = self.store.get(Bar, 100)
        bar.foo_id = None

        tables = self.store.using(LeftJoin("foo", "bar",
                                           "bar.foo_id = foo.id"))
        result = tables.find(Bar).order_by(Foo.id, Bar.id)
        lst = [bar and (bar.id, bar.title) for bar in result]
        self.assertEquals(lst, [
                          None,
                          (200, u"Title 200"),
                          (300, u"Title 100"),
                         ])

    def test_find_tuple(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        result = result.order_by(Foo.id)
        lst = [(foo and (foo.id, foo.title), bar and (bar.id, bar.title))
               for (foo, bar) in result]
        self.assertEquals(lst, [
                          ((10, u"Title 30"), (100, u"Title 300")),
                          ((30, u"Title 10"), (300, u"Title 100")),
                         ])

    def test_find_tuple_using(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        tables = self.store.using(Foo, LeftJoin(Bar, Bar.foo_id == Foo.id))
        result = tables.find((Foo, Bar)).order_by(Foo.id)
        lst = [(foo and (foo.id, foo.title), bar and (bar.id, bar.title))
               for (foo, bar) in result]
        self.assertEquals(lst, [
                          ((10, u"Title 30"), (100, u"Title 300")),
                          ((20, u"Title 20"), None),
                          ((30, u"Title 10"), (300, u"Title 100")),
                         ])

    def test_find_tuple_using_with_disallow_none(self):
        class Bar(object):
            __storm_table__ = "bar"
            id = Int(primary=True, allow_none=False)
            title = Unicode()
            foo_id = Int()
            foo = Reference(foo_id, Foo.id)

        bar = self.store.get(Bar, 200)
        self.store.remove(bar)

        tables = self.store.using(Foo, LeftJoin(Bar, Bar.foo_id == Foo.id))
        result = tables.find((Foo, Bar)).order_by(Foo.id)
        lst = [(foo and (foo.id, foo.title), bar and (bar.id, bar.title))
               for (foo, bar) in result]
        self.assertEquals(lst, [
                          ((10, u"Title 30"), (100, u"Title 300")),
                          ((20, u"Title 20"), None),
                          ((30, u"Title 10"), (300, u"Title 100")),
                         ])

    def test_find_tuple_using_skip_when_none(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        tables = self.store.using(Foo,
                                  LeftJoin(Bar, Bar.foo_id == Foo.id),
                                  LeftJoin(Link, Link.bar_id == Bar.id))
        result = tables.find((Bar, Link)).order_by(Foo.id, Bar.id, Link.foo_id)
        lst = [(bar and (bar.id, bar.title),
                link and (link.bar_id, link.foo_id))
               for (bar, link) in result]
        self.assertEquals(lst, [
                          ((100, u"Title 300"), (100, 10)),
                          ((100, u"Title 300"), (100, 20)),
                          (None, None),
                          ((300, u"Title 100"), (300, 10)),
                          ((300, u"Title 100"), (300, 30)),
                         ])

    def test_find_tuple_contains(self):
        foo = self.store.get(Foo, 10)
        bar = self.store.get(Bar, 100)
        bar200 = self.store.get(Bar, 200)
        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        self.assertEquals((foo, bar) in result, True)
        self.assertEquals((foo, bar200) in result, False)

    def test_find_tuple_contains_with_set_expression(self):
        foo = self.store.get(Foo, 10)
        bar = self.store.get(Bar, 100)
        bar200 = self.store.get(Bar, 200)
        result1 = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        result2 = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        self.assertEquals((foo, bar) in result1.union(result2), True)

        if self.__class__.__name__.startswith("MySQL"):
            return
        self.assertEquals((foo, bar) in result1.intersection(result2), True)
        self.assertEquals((foo, bar) in result1.difference(result2), False)

    def test_find_tuple_any(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        foo, bar = result.order_by(Foo.id).any()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, u"Title 30")
        self.assertEquals(bar.id, 100)
        self.assertEquals(bar.title, u"Title 300")

    def test_find_tuple_first(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        foo, bar = result.order_by(Foo.id).first()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, u"Title 30")
        self.assertEquals(bar.id, 100)
        self.assertEquals(bar.title, u"Title 300")

    def test_find_tuple_last(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        foo, bar = result.order_by(Foo.id).last()
        self.assertEquals(foo.id, 30)
        self.assertEquals(foo.title, u"Title 10")
        self.assertEquals(bar.id, 300)
        self.assertEquals(bar.title, u"Title 100")

    def test_find_tuple_one(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None

        result = self.store.find((Foo, Bar),
                                 Bar.foo_id == Foo.id, Foo.id == 10)
        foo, bar = result.order_by(Foo.id).one()
        self.assertEquals(foo.id, 10)
        self.assertEquals(foo.title, u"Title 30")
        self.assertEquals(bar.id, 100)
        self.assertEquals(bar.title, u"Title 300")

    def test_find_tuple_count(self):
        bar = self.store.get(Bar, 200)
        bar.foo_id = None
        result = self.store.find((Foo, Bar), Bar.foo_id == Foo.id)
        self.assertEquals(result.count(), 2)

    def test_find_tuple_remove(self):
        result = self.store.find((Foo, Bar))
        self.assertRaises(FeatureError, result.remove)

    def test_find_tuple_set(self):
        result = self.store.find((Foo, Bar))
        self.assertRaises(FeatureError, result.set, title=u"Title 40")

    def test_find_tuple_kwargs(self):
        self.assertRaises(FeatureError,
                          self.store.find, (Foo, Bar), title=u"Titl…