def test_overlapping_attribute_error(self):
        place, Transition, place_input, Place, transition = (
            self.tables.place,
            self.classes.Transition,
            self.tables.place_input,
            self.classes.Place,
            self.tables.transition,
        )

        mapper(
            Place,
            place,
            properties={
                "transitions": relationship(
                    Transition, secondary=place_input, backref="places"
                )
            },
        )
        mapper(
            Transition,
            transition,
            properties={
                "places": relationship(
                    Place, secondary=place_input, backref="transitions"
                )
            },
        )
        assert_raises_message(
            sa.exc.ArgumentError,
            "property of that name exists",
            sa.orm.configure_mappers,
        )

    def test_flush_size(self):
        foobars, nodes = self.tables.foobars, self.tables.nodes

        class Node(fixtures.ComparableEntity):
            pass
        class FooBar(fixtures.ComparableEntity):
            pass

        mapper(Node, nodes, properties={
            'children':relationship(Node),
            'foobars':relationship(FooBar)
        })
        mapper(FooBar, foobars)

        sess = create_session()
        n1 = Node(data='n1')
        n2 = Node(data='n2')
        n1.children.append(n2)
        sess.add(n1)
        # ensure "foobars" doesn't get yanked in here
        self._assert_uow_size(sess, 3)

        n1.foobars.append(FooBar())
        # saveupdateall/deleteall for FooBar added here,
        # plus processstate node.foobars 
        # currently the "all" procs stay in pairs
        self._assert_uow_size(sess, 6)

        sess.flush()

    def test_path_entity(self):
        """test the legacy *addl_attrs argument."""

        User = self.classes.User
        Order = self.classes.Order
        Item = self.classes.Item

        users = self.tables.users
        orders = self.tables.orders
        items = self.tables.items
        order_items = self.tables.order_items

        mapper(User, users, properties={
                "orders": relationship(Order, lazy="joined")
            })
        mapper(Order, orders, properties={
                "items": relationship(Item, secondary=order_items, lazy="joined")
            })
        mapper(Item, items)

        sess = create_session()

        exp = ("SELECT users.id AS users_id, users.name AS users_name, "
            "items_1.id AS items_1_id, orders_1.id AS orders_1_id, "
            "orders_1.user_id AS orders_1_user_id, orders_1.address_id "
            "AS orders_1_address_id, orders_1.description AS "
            "orders_1_description, orders_1.isopen AS orders_1_isopen "
            "FROM users LEFT OUTER JOIN orders AS orders_1 "
            "ON users.id = orders_1.user_id LEFT OUTER JOIN "
            "(order_items AS order_items_1 JOIN items AS items_1 "
                "ON items_1.id = order_items_1.item_id) "
            "ON orders_1.id = order_items_1.order_id")

        q = sess.query(User).options(defer(User.orders, Order.items, Item.description))
        self.assert_compile(q, exp)

    def setup_mappers(cls):
        Right, Middle, middle, right, left, Left = (
            cls.classes.Right,
            cls.classes.Middle,
            cls.tables.middle,
            cls.tables.right,
            cls.tables.left,
            cls.classes.Left,
        )

        # set up bi-directional eager loads
        mapper(Left, left)
        mapper(Right, right)
        mapper(
            Middle,
            middle,
            properties=dict(
                left=relationship(
                    Left,
                    lazy="joined",
                    backref=backref("middle", lazy="joined"),
                ),
                right=relationship(
                    Right,
                    lazy="joined",
                    backref=backref("middle", lazy="joined"),
                ),
            ),
        ),

    def test_distinct(self):
        users, items, order_items, orders, Item, User, Address, Order, addresses = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.tables.orders,
            self.classes.Item,
            self.classes.User,
            self.classes.Address,
            self.classes.Order,
            self.tables.addresses,
        )

        mapper(Item, items)
        mapper(Order, orders, properties={"items": relationship(Item, secondary=order_items, lazy="select")})
        mapper(
            User,
            users,
            properties={
                "addresses": relationship(mapper(Address, addresses), lazy="select"),
                "orders": relationship(Order, lazy="select"),
            },
        )

        sess = create_session()
        q = sess.query(User)

        # use a union all to get a lot of rows to join against
        u2 = users.alias("u2")
        s = sa.union_all(u2.select(use_labels=True), u2.select(use_labels=True), u2.select(use_labels=True)).alias("u")
        l = q.filter(s.c.u2_id == User.id).order_by(User.id).distinct().all()
        eq_(self.static.user_all_result, l)

    def test_non_orphan(self):
        """test that an entity can have two parent delete-orphan
        cascades, and persists normally."""

        class Address(_fixtures.Base):
            pass
        class Home(_fixtures.Base):
            pass
        class Business(_fixtures.Base):
            pass

        mapper(Address, addresses)
        mapper(Home, homes, properties={'address'
               : relationship(Address, cascade='all,delete-orphan',
               single_parent=True)})
        mapper(Business, businesses, properties={'address'
               : relationship(Address, cascade='all,delete-orphan',
               single_parent=True)})

        session = create_session()
        h1 = Home(description='home1', address=Address(street='address1'))
        b1 = Business(description='business1',
                      address=Address(street='address2'))
        session.add_all((h1,b1))
        session.flush()
        session.expunge_all()

        eq_(session.query(Home).get(h1.id), Home(description='home1',
            address=Address(street='address1')))
        eq_(session.query(Business).get(b1.id),
            Business(description='business1',
            address=Address(street='address2')))

 def setup_mappers(cls):
     mapper(T1, t1, properties=dict(t2=relationship(T2,
            cascade='all, delete-orphan', single_parent=True)))
     mapper(T2, t2, properties=dict(t3=relationship(T3,
            cascade='all, delete-orphan', single_parent=True,
            backref=backref('t2', uselist=False))))
     mapper(T3, t3)

    def test_cycle(self):
        """
        This test has a peculiar aspect in that it doesn't create as many
        dependent relationships as the other tests, and revealed a small
        glitch in the circular dependency sorting.

        """

        person, ball, Ball, Person = (self.tables.person,
                                self.tables.ball,
                                self.classes.Ball,
                                self.classes.Person)

        mapper(Ball, ball)
        mapper(Person, person, properties=dict(
            balls=relationship(Ball,
                           primaryjoin=ball.c.person_id == person.c.id,
                           remote_side=ball.c.person_id),
            favorite=relationship(Ball,
                              primaryjoin=person.c.favorite_ball_id == ball.c.id,
                              remote_side=ball.c.id)))

        b = Ball()
        p = Person()
        p.balls.append(b)
        sess = create_session()
        sess.add(p)
        sess.flush()

    def upgrade(self):
        self.loadVersion()
        log.warning('Upgrading Database schema from version {0} to version {1}'.format(self._schema_version, self._version))
        
        for version in range(1, self._version + 1):
            log.warning('Upgrading schema version to {0}'.format(version))
            schema_fun = 'upgrade_{0}_Schema'.format(version)
            ddl_fun = 'upgrade_{0}_DDL'.format(version)
            if self._schema_version < version:
                if hasattr(self.__class__, ddl_fun) and callable(getattr(self.__class__, ddl_fun)):
                    log.warning('Executing ddl function: {0}'.format(ddl_fun))
                    getattr(self, ddl_fun)()
                self._setSchemaVersion(version)
                
            if hasattr(self.__class__, schema_fun) and callable(getattr(self.__class__, schema_fun)):
                log.warning('Executing schema function: {0}'.format(schema_fun))
                getattr(self, schema_fun)()

        
        
        mapper(FirmwareType, Table('firmware_type', self._metadata))
        
        mapper(Firmware, Table('firmware', self._metadata), properties={
            'type' : relationship(FirmwareType)
        })
        
        mapper(Node, Table('node', self._metadata), properties={
            'firmware' : relationship(Firmware)
        })
        
        mapper(Sensor, Table('sensor', self._metadata), properties={
            'node' : relationship(Node)
        })

    def setup_mappers(cls):
        Category, owners, Option, tests, Thing, Owner, options, categories = (
            cls.classes.Category,
            cls.tables.owners,
            cls.classes.Option,
            cls.tables.tests,
            cls.classes.Thing,
            cls.classes.Owner,
            cls.tables.options,
            cls.tables.categories)

        mapper(Owner, owners)

        mapper(Category, categories)

        mapper(Option, options, properties=dict(
            owner=relationship(Owner, viewonly=True),
            test=relationship(Thing, viewonly=True)))

        mapper(Thing, tests, properties=dict(
            owner=relationship(Owner, backref='tests'),
            category=relationship(Category),
            owner_option=relationship(
                Option, primaryjoin=sa.and_(
                    tests.c.id == options.c.test_id,
                    tests.c.owner_id == options.c.owner_id),
                foreign_keys=[options.c.test_id, options.c.owner_id],
                uselist=False)))

    def test_limit(self):
        """Limit operations combined with lazy-load relationships."""

        users, items, order_items, orders, Item, User, Address, Order, addresses = (self.tables.users,
                                self.tables.items,
                                self.tables.order_items,
                                self.tables.orders,
                                self.classes.Item,
                                self.classes.User,
                                self.classes.Address,
                                self.classes.Order,
                                self.tables.addresses)


        mapper(Item, items)
        mapper(Order, orders, properties={
            'items':relationship(Item, secondary=order_items, lazy='subquery',
                order_by=items.c.id)
        })
        mapper(User, users, properties={
            'addresses':relationship(mapper(Address, addresses),
                            lazy='subquery',
                            order_by=addresses.c.id),
            'orders':relationship(Order, lazy='select', order_by=orders.c.id)
        })

        sess = create_session()
        q = sess.query(User)

        l = q.order_by(User.id).limit(2).offset(1).all()
        eq_(self.static.user_all_result[1:3], l)

        sess = create_session()
        l = q.order_by(sa.desc(User.id)).limit(2).offset(2).all()
        eq_(list(reversed(self.static.user_all_result[0:2])), l)

def fk_rel(cls, attrib='id', nullable=False, index=True, primary_key=False,
           doc=None, ondelete='CASCADE', backref=None, order_by=None):
    '''Returns a ForeignKey column and a relationship,
        while automatically setting the type of the foreign key.

        Usage::

            # A relationship in an Address model pointing to a parent Person:
            person_id, person = fk_rel(Person, nullable=False,
                index=True, backref='addresses', ondelete='CASCADE')

        A backref is created only if you provide its name in the argument.
        ``nullable`` and ``index`` are usually ommited, because these are the
        default values and they are good.
        ``ondelete`` is "CASCADE" by default, but you can set it to "SET NULL",
        or None which translates to "NO ACTION" (less interesting).
        If provided, ``order_by`` is used on the backref.
        You may also pass an ``attrib`` which is the column name for
        the foreign key.
        '''
    # http://docs.sqlalchemy.org/en/latest/orm/collections.html#passive-deletes
    if ondelete == 'CASCADE':
        cascade = CASC
        passive_deletes = True
    else:
        cascade = False  # meaning "save-update, merge"
        passive_deletes = False

    return (fk(getattr(cls, attrib), nullable=nullable, index=index,
               primary_key=primary_key, doc=doc, ondelete=ondelete),
            relationship(cls, backref=_backref(
                backref, cascade=cascade, passive_deletes=passive_deletes,
                order_by=order_by))
            if backref else relationship(cls))

    def test_collection_setstate(self):
        """test a particular cycle that requires CollectionAdapter
        to not rely upon InstanceState to deserialize."""

        m = MetaData()
        c1 = Table('c1', m,
            Column('parent_id', String,
                        ForeignKey('p.id'), primary_key=True)
        )
        c2 = Table('c2', m,
            Column('parent_id', String,
                        ForeignKey('p.id'), primary_key=True)
        )
        p = Table('p', m,
            Column('id', String, primary_key=True)
        )

        mapper(Parent, p, properties={
            'children1':relationship(Child1),
            'children2':relationship(Child2)
        })
        mapper(Child1, c1)
        mapper(Child2, c2)

        obj = Parent()
        screen1 = Screen(obj)
        screen1.errors = [obj.children1, obj.children2]
        screen2 = Screen(Child2(), screen1)
        pickle.loads(pickle.dumps(screen2))

def create_mapper(molecule_design_tbl, molecule_design_structure_tbl,
                  single_supplier_molecule_design_tbl,
                  molecule_design_gene_tbl, refseq_gene_tbl):
    "Mapper factory."
    md = molecule_design_tbl
    ssmd = single_supplier_molecule_design_tbl
    mdg = molecule_design_gene_tbl
    rsg = refseq_gene_tbl
    m = mapper(MoleculeDesign, molecule_design_tbl,
          id_attribute='molecule_design_id',
          properties=dict(
            molecule_type=relationship(MoleculeType), #, lazy='joined'),
            chemical_structures=relationship(
                                    ChemicalStructure,
                                    secondary=molecule_design_structure_tbl,
                                    back_populates='molecule_designs'),
            supplier_molecule_designs=
                        relationship(SupplierMoleculeDesign,
                                     secondary=ssmd,
                                     back_populates='molecule_design'),
            genes=relationship(Gene, viewonly=True, secondary=mdg,
                primaryjoin=(mdg.c.molecule_design_id ==
                                                    md.c.molecule_design_id),
                secondaryjoin=(mdg.c.gene_id == rsg.c.gene_id),
                foreign_keys=(mdg.c.molecule_design_id, mdg.c.gene_id),
                back_populates='molecule_designs',
                ),
            ),
          polymorphic_on=molecule_design_tbl.c.molecule_type_id,
          polymorphic_identity=MOLECULE_TYPE,
          )
    return m

    def test_pending_expunge(self):
        class Order(_fixtures.Base):
            pass
        class Item(_fixtures.Base):
            pass
        class Attribute(_fixtures.Base):
            pass

        mapper(Attribute, attributes)
        mapper(Item, items, properties=dict(
            attributes=relationship(Attribute, cascade="all,delete-orphan",
                                    backref="item")
        ))
        mapper(Order, orders, properties=dict(
            items=relationship(Item, cascade="all,delete-orphan",
                                    backref="order")
        ))

        s = create_session()
        order = Order(name="order1")
        s.add(order)

        attr = Attribute(name="attr1")
        item = Item(name="item1", attributes=[attr])

        order.items.append(item)
        order.items.remove(item)

        assert item not in s
        assert attr not in s

        s.flush()
        assert orders.count().scalar() == 1
        assert items.count().scalar() == 0
        assert attributes.count().scalar() == 0

    def test_limit(self):
        """test limit operations combined with lazy-load relationships."""

        users, items, order_items, orders, Item, \
            User, Address, Order, addresses = (
                self.tables.users,
                self.tables.items,
                self.tables.order_items,
                self.tables.orders,
                self.classes.Item,
                self.classes.User,
                self.classes.Address,
                self.classes.Order,
                self.tables.addresses)

        mapper(Item, items)
        mapper(Order, orders, properties={
            'items': relationship(Item, secondary=order_items, lazy='select')
        })
        mapper(User, users, properties={
            'addresses': relationship(
                mapper(Address, addresses), lazy='select'),
            'orders': relationship(Order, lazy='select')
        })

        sess = create_session()
        q = sess.query(User)

        if testing.against('mssql'):
            result = q.limit(2).all()
            assert self.static.user_all_result[:2] == result
        else:
            result = q.limit(2).offset(1).all()
            assert self.static.user_all_result[1:3] == result

    def test_orphan(self):
        """test that an entity can have two parent delete-orphan
        cascades, and is detected as an orphan when saved without a
        parent."""

        class Address(_fixtures.Base):
            pass

        class Home(_fixtures.Base):
            pass

        class Business(_fixtures.Base):
            pass

        mapper(Address, addresses)
        mapper(Home, homes, properties={'address'
               : relationship(Address, cascade='all,delete-orphan',
               single_parent=True)})
        mapper(Business, businesses, properties={'address'
               : relationship(Address, cascade='all,delete-orphan',
               single_parent=True)})
        session = create_session()
        a1 = Address()
        session.add(a1)
        try:
            session.flush()
            assert False
        except orm_exc.FlushError, e:
            assert True

    def test_distinct(self):
        users, items, order_items, orders, \
            Item, User, Address, Order, addresses = (
                self.tables.users,
                self.tables.items,
                self.tables.order_items,
                self.tables.orders,
                self.classes.Item,
                self.classes.User,
                self.classes.Address,
                self.classes.Order,
                self.tables.addresses)

        mapper(Item, items)
        mapper(Order, orders, properties={
            'items': relationship(Item, secondary=order_items, lazy='select')
        })
        mapper(User, users, properties={
            'addresses': relationship(
                mapper(Address, addresses), lazy='select'),
            'orders': relationship(Order, lazy='select')
        })

        sess = create_session()
        q = sess.query(User)

        # use a union all to get a lot of rows to join against
        u2 = users.alias('u2')
        s = sa.union_all(
            u2.select(use_labels=True),
            u2.select(use_labels=True), u2.select(use_labels=True)).alias('u')
        result = q.filter(s.c.u2_id == User.id).order_by(User.id).distinct() \
            .all()
        eq_(self.static.user_all_result, result)

    def _u_ad_fixture(self, populate_user, dont_use_get=False):
        users, Address, addresses, User = (
            self.tables.users,
            self.classes.Address,
            self.tables.addresses,
            self.classes.User)

        mapper(User, users, properties={
            'addresses': relationship(Address, back_populates='user')
        })
        mapper(Address, addresses, properties={
            'user': relationship(
                User,
                primaryjoin=and_(
                    users.c.id == addresses.c.user_id, users.c.id != 27)
                if dont_use_get else None,
                back_populates='addresses'
            )
        })

        sess = create_session()
        a1 = Address(email_address='a1')
        sess.add(a1)
        if populate_user:
            a1.user = User(name='ed')
        sess.flush()
        if populate_user:
            sess.expire_all()
        return User, Address, sess, a1

    def test_limit(self):
        """test limit operations combined with lazy-load relationships."""

        users, items, order_items, orders, Item, User, Address, Order, addresses = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.tables.orders,
            self.classes.Item,
            self.classes.User,
            self.classes.Address,
            self.classes.Order,
            self.tables.addresses,
        )

        mapper(Item, items)
        mapper(Order, orders, properties={"items": relationship(Item, secondary=order_items, lazy="select")})
        mapper(
            User,
            users,
            properties={
                "addresses": relationship(mapper(Address, addresses), lazy="select"),
                "orders": relationship(Order, lazy="select"),
            },
        )

        sess = create_session()
        q = sess.query(User)

        if testing.against("maxdb", "mssql"):
            l = q.limit(2).all()
            assert self.static.user_all_result[:2] == l
        else:
            l = q.limit(2).offset(1).all()
            assert self.static.user_all_result[1:3] == l