def define_tables(self, metadata):
            global User, Address
            Base = decl.declarative_base(metadata=metadata)

            class User(Base, ComparableEntity):
                __tablename__ = 'users'
                id = Column(Integer, primary_key=True)
                name = Column(String(50))
            
            class Address(Base, ComparableEntity):
                __tablename__ = 'addresses'
                id = Column(Integer, primary_key=True)
                email = Column(String(50))
                user_id = Column(Integer, ForeignKey('users.id'))
                if inline:
                    if stringbased:
                        user = relation("User", primaryjoin="User.id==Address.user_id", backref="addresses")
                    else:
                        user = relation(User, primaryjoin=User.id==user_id, backref="addresses")
            
            if not inline:
                compile_mappers()
                if stringbased:
                    Address.user = relation("User", primaryjoin="User.id==Address.user_id", backref="addresses")
                else:
                    Address.user = relation(User, primaryjoin=User.id==Address.user_id, backref="addresses")

    def testone(self):
        """
        Tests eager load of a many-to-one attached to a one-to-many.  this
        testcase illustrated the bug, which is that when the single Company is
        loaded, no further processing of the rows occurred in order to load
        the Company's second Address object.

        """
        mapper(Address, addresses)

        mapper(Company, companies, properties={"addresses": relation(Address, lazy=False)})

        mapper(Invoice, invoices, properties={"company": relation(Company, lazy=False)})

        a1 = Address(address="a1 address")
        a2 = Address(address="a2 address")
        c1 = Company(company_name="company 1", addresses=[a1, a2])
        i1 = Invoice(date=datetime.datetime.now(), company=c1)

        session = create_session()
        session.add(i1)
        session.flush()

        company_id = c1.company_id
        invoice_id = i1.invoice_id

        session.clear()
        c = session.query(Company).get(company_id)

        session.clear()
        i = session.query(Invoice).get(invoice_id)

        eq_(c, i.company)

 def setup_mappers(self):
     mapper(Address, addresses)
     mapper(User, users, properties = dict(
         addresses = relation(Address, cascade="all, delete-orphan", backref="user"),
         orders = relation(
             mapper(Order, orders), cascade="all, delete-orphan")
     ))
     mapper(Dingaling,dingalings, properties={
         'address':relation(Address)
     })

    def test_nesting_with_functions(self):
        mapper(Data, datas)
        mapper(Foo, foo, properties={"data": relation(Data, backref=backref("foo", uselist=False))})

        mapper(Stat, stats, properties={"data": relation(Data)})

        session = create_session()

        data = [Data(a=x) for x in range(5)]
        session.add_all(data)

        session.add_all(
            (
                Stat(data=data[0], somedata=1),
                Stat(data=data[1], somedata=2),
                Stat(data=data[2], somedata=3),
                Stat(data=data[3], somedata=4),
                Stat(data=data[4], somedata=5),
                Stat(data=data[0], somedata=6),
                Stat(data=data[1], somedata=7),
                Stat(data=data[2], somedata=8),
                Stat(data=data[3], somedata=9),
                Stat(data=data[4], somedata=10),
            )
        )
        session.flush()

        arb_data = sa.select(
            [stats.c.data_id, sa.func.max(stats.c.somedata).label("max")],
            stats.c.data_id <= 5,
            group_by=[stats.c.data_id],
        ).alias("arb")

        arb_result = arb_data.execute().fetchall()

        # order the result list descending based on 'max'
        arb_result.sort(key=lambda a: a["max"], reverse=True)

        # extract just the "data_id" from it
        arb_result = [row["data_id"] for row in arb_result]

        # now query for Data objects using that above select, adding the
        # "order by max desc" separately
        q = (
            session.query(Data)
            .options(sa.orm.eagerload("foo"))
            .select_from(datas.join(arb_data, arb_data.c.data_id == datas.c.id))
            .order_by(sa.desc(arb_data.c.max))
            .limit(10)
        )

        # extract "data_id" from the list of result objects
        verify_result = [d.id for d in q]

        eq_(verify_result, arb_result)

    def test_dontload_with_eager(self):
        """

        This test illustrates that with dont_load=True, we can't just copy the
        committed_state of the merged instance over; since it references
        collection objects which themselves are to be merged.  This
        committed_state would instead need to be piecemeal 'converted' to
        represent the correct objects.  However, at the moment I'd rather not
        support this use case; if you are merging with dont_load=True, you're
        typically dealing with caching and the merged objects shouldnt be
        'dirty'.

        """
        mapper(User, users, properties={
            'addresses':relation(mapper(Address, addresses))
        })
        sess = create_session()
        u = User()
        u.id = 7
        u.name = "fred"
        a1 = Address()
        a1.email_address='[email protected]'
        u.addresses.append(a1)

        sess.add(u)
        sess.flush()

        sess2 = create_session()
        u2 = sess2.query(User).options(sa.orm.eagerload('addresses')).get(7)

        sess3 = create_session()
        u3 = sess3.merge(u2, dont_load=True)
        def go():
            sess3.flush()
        self.assert_sql_count(testing.db, go, 0)

    def test_dontload_with_backrefs(self):
        """dontload populates relations in both directions without requiring a load"""
        mapper(User, users, properties={
            'addresses':relation(mapper(Address, addresses), backref='user')
        })

        u = User(id=7, name='fred', addresses=[
            Address(email_address='ad1'),
            Address(email_address='ad2')])
        sess = create_session()
        sess.add(u)
        sess.flush()
        sess.close()
        assert 'user' in u.addresses[1].__dict__

        sess = create_session()
        u2 = sess.merge(u, dont_load=True)
        assert 'user' in u2.addresses[1].__dict__
        eq_(u2.addresses[1].user, User(id=7, name='fred'))

        sess.expire(u2.addresses[1], ['user'])
        assert 'user' not in u2.addresses[1].__dict__
        sess.close()

        sess = create_session()
        u = sess.merge(u2, dont_load=True)
        assert 'user' not in u.addresses[1].__dict__
        eq_(u.addresses[1].user, User(id=7, name='fred'))

    def test_transient_to_pending_collection(self):
        mapper(User, users, properties={
            'addresses': relation(Address, backref='user',
                                  collection_class=OrderedSet)})
        mapper(Address, addresses)
        on_load = self.on_load_tracker(User)
        self.on_load_tracker(Address, on_load)

        u = User(id=7, name='fred', addresses=OrderedSet([
            Address(id=1, email_address='fred1'),
            Address(id=2, email_address='fred2'),
            ]))
        eq_(on_load.called, 0)

        sess = create_session()
        sess.merge(u)
        eq_(on_load.called, 3)

        merged_users = [e for e in sess if isinstance(e, User)]
        eq_(len(merged_users), 1)
        assert merged_users[0] is not u

        sess.flush()
        sess.clear()

        eq_(sess.query(User).one(),
            User(id=7, name='fred', addresses=OrderedSet([
                Address(id=1, email_address='fred1'),
                Address(id=2, email_address='fred2'),
            ]))
        )

    def test_one_to_one_cascade(self):

        mapper(User, users, properties={
            'address':relation(mapper(Address, addresses),uselist = False)
        })
        on_load = self.on_load_tracker(User)
        self.on_load_tracker(Address, on_load)
        sess = create_session()

        u = User()
        u.id = 7
        u.name = "fred"
        a1 = Address()
        a1.email_address='[email protected]'
        u.address = a1

        sess.add(u)
        sess.flush()

        eq_(on_load.called, 0)

        sess2 = create_session()
        u2 = sess2.query(User).get(7)
        eq_(on_load.called, 1)
        u2.name = 'fred2'
        u2.address.email_address = '[email protected]'
        eq_(on_load.called, 2)

        u3 = sess.merge(u2)
        eq_(on_load.called, 2)
        assert u3 is u

    def test_detached_to_persistent_collection(self):
        mapper(User, users, properties={
            'addresses':relation(Address,
                                 backref='user',
                                 collection_class=OrderedSet)})
        mapper(Address, addresses)
        on_load = self.on_load_tracker(User)
        self.on_load_tracker(Address, on_load)

        a = Address(id=1, email_address='fred1')
        u = User(id=7, name='fred', addresses=OrderedSet([
            a,
            Address(id=2, email_address='fred2'),
        ]))
        sess = create_session()
        sess.add(u)
        sess.flush()
        sess.clear()

        u.name='fred jones'
        u.addresses.add(Address(id=3, email_address='fred3'))
        u.addresses.remove(a)

        eq_(on_load.called, 0)
        u = sess.merge(u)
        eq_(on_load.called, 4)
        sess.flush()
        sess.clear()

        eq_(sess.query(User).first(),
            User(id=7, name='fred jones', addresses=OrderedSet([
                Address(id=2, email_address='fred2'),
                Address(id=3, email_address='fred3')])))

    def test_expired_eager(self):
        mapper(User, users, properties={
            'addresses':relation(Address, backref='user', lazy=False),
            })
        mapper(Address, addresses)

        sess = create_session()
        u = sess.query(User).get(7)

        sess.expire(u)
        assert 'name' not in u.__dict__
        assert 'addresses' not in u.__dict__

        def go():
            assert u.addresses[0].email_address == '[email protected]'
            assert u.name == 'jack'
        # two loads, since relation() + scalar are
        # separate right now on per-attribute load
        self.assert_sql_count(testing.db, go, 2)
        assert 'name' in u.__dict__
        assert 'addresses' in u.__dict__

        sess.expire(u, ['name', 'addresses'])
        assert 'name' not in u.__dict__
        assert 'addresses' not in u.__dict__

        def go():
            sess.query(User).filter_by(id=7).one()
            assert u.addresses[0].email_address == '[email protected]'
            assert u.name == 'jack'
        # one load, since relation() + scalar are
        # together when eager load used with Query
        self.assert_sql_count(testing.db, go, 1)

    def test_compileonattr_rel_backref_b(self):
        m = MetaData()
        t1 = Table("t1", m, Column("id", Integer, primary_key=True), Column("x", Integer))
        t2 = Table("t2", m, Column("id", Integer, primary_key=True), Column("t1_id", Integer, ForeignKey("t1.id")))

        class Base(object):
            def __init__(self):
                pass

        class Base_AKW(object):
            def __init__(self, *args, **kwargs):
                pass

        for base in object, Base, Base_AKW:

            class A(base):
                pass

            class B(base):
                pass

            mapper(A, t1)
            mapper(B, t2, properties=dict(a=relation(A, backref="bs")))

            a = A()
            b = B()
            b.a = a

            session = create_session()
            session.add(a)
            assert b in session, "base: %s" % base

    def test_basic(self):
        mapper(Employee, employees)
        mapper(Department, departments, properties=dict(employees=relation(Employee, lazy=False, backref="department")))

        d1 = Department(name="One")
        for e in "Jim", "Jack", "John", "Susan":
            d1.employees.append(Employee(name=e))

        d2 = Department(name="Two")
        for e in "Joe", "Bob", "Mary", "Wally":
            d2.employees.append(Employee(name=e))

        sess = create_session()
        sess.add_all((d1, d2))
        sess.flush()

        q = (
            sess.query(Department)
            .join("employees")
            .filter(Employee.name.startswith("J"))
            .distinct()
            .order_by([sa.desc(Department.name)])
        )

        eq_(q.count(), 2)
        assert q[0] is d2