def test_custom_mapper(self):
        class MyExt(sa.orm.MapperExtension):
            def create_instance(self):
                return "CHECK"

        def mymapper(cls, tbl, **kwargs):
            kwargs['extension'] = MyExt()
            return sa.orm.mapper(cls, tbl, **kwargs)

        from sqlalchemy.orm.mapper import Mapper
        class MyMapper(Mapper):
            def __init__(self, *args, **kwargs):
                kwargs['extension'] = MyExt()
                Mapper.__init__(self, *args, **kwargs)

        from sqlalchemy.orm import scoping
        ss = scoping.ScopedSession(create_session)
        ss.extension = MyExt()
        ss_mapper = ss.mapper

        for mapperfunc in (mymapper, MyMapper, ss_mapper):
            base = decl.declarative_base()
            class Foo(base):
                __tablename__ = 'foo'
                __mapper_cls__ = mapperfunc
                id = Column(Integer, primary_key=True)
            eq_(Foo.__mapper__.compile().extension.create_instance(), 'CHECK')

            base = decl.declarative_base(mapper=mapperfunc)
            class Foo(base):
                __tablename__ = 'foo'
                id = Column(Integer, primary_key=True)
            eq_(Foo.__mapper__.compile().extension.create_instance(), 'CHECK')

    def test_relation_reference(self):
        class Address(Base, ComparableEntity):
            __tablename__ = 'addresses'

            id = Column('id', Integer, primary_key=True)
            email = Column('email', String(50))
            user_id = Column('user_id', Integer, ForeignKey('users.id'))

        class User(Base, ComparableEntity):
            __tablename__ = 'users'

            id = Column('id', Integer, primary_key=True)
            name = Column('name', String(50))
            addresses = relation("Address", backref="user",
                                 primaryjoin=id == Address.user_id)

        User.address_count = sa.orm.column_property(
            sa.select([sa.func.count(Address.id)]).
            where(Address.user_id == User.id).as_scalar())

        Base.metadata.create_all()

        u1 = User(name='u1', addresses=[
            Address(email='one'),
            Address(email='two'),
        ])
        sess = create_session()
        sess.add(u1)
        sess.flush()
        sess.clear()

        eq_(sess.query(User).all(),
            [User(name='u1', address_count=2, addresses=[
              Address(email='one'),
              Address(email='two')])])

    def test_identity_key_1(self):
        mapper(User, users)

        key = util.identity_key(User, 1)
        eq_(key, (User, (1,)))
        key = util.identity_key(User, ident=1)
        eq_(key, (User, (1,)))

    def test_eager_order_by(self):
        class Address(Base, ComparableEntity):
            __tablename__ = 'addresses'

            id = Column('id', Integer, primary_key=True)
            email = Column('email', String(50))
            user_id = Column('user_id', Integer, ForeignKey('users.id'))

        class User(Base, ComparableEntity):
            __tablename__ = 'users'

            id = Column('id', Integer, primary_key=True)
            name = Column('name', String(50))
            addresses = relation("Address", order_by=Address.email)

        Base.metadata.create_all()
        u1 = User(name='u1', addresses=[
            Address(email='two'),
            Address(email='one'),
        ])
        sess = create_session()
        sess.add(u1)
        sess.flush()
        sess.clear()
        eq_(sess.query(User).options(eagerload(User.addresses)).all(), [User(name='u1', addresses=[
            Address(email='one'),
            Address(email='two'),
        ])])

    def test_noorm(self):
        """test the control case"""
        # I want to display a list of tests owned by owner 1
        # if someoption is false or he hasn't specified it yet (null)
        # but not if he set it to true (example someoption is for hiding)

        # desired output for owner 1
        # test_id, cat_name
        # 1 'Some Category'
        # 3  "

        # not orm style correct query
        print "Obtaining correct results without orm"
        result = (
            sa.select(
                [tests.c.id, categories.c.name],
                sa.and_(tests.c.owner_id == 1, sa.or_(options.c.someoption == None, options.c.someoption == False)),
                order_by=[tests.c.id],
                from_obj=[
                    tests.join(categories).outerjoin(
                        options, sa.and_(tests.c.id == options.c.test_id, tests.c.owner_id == options.c.owner_id)
                    )
                ],
            )
            .execute()
            .fetchall()
        )
        eq_(result, [(1, u"Some Category"), (3, u"Some Category")])

    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 test_passive_override(self):
        """
        Primarily for postgres, tests that when we get a primary key column
        back from reflecting a table which has a default value on it, we
        pre-execute that DefaultClause upon insert, even though DefaultClause
        says "let the database execute this", because in postgres we must have
        all the primary key values in memory before insert; otherwise we can't
        locate the just inserted row.

        """
        # TODO: move this to dialect/postgres
        try:
            meta = MetaData(testing.db)
            testing.db.execute("""
             CREATE TABLE speedy_users
             (
                 speedy_user_id   SERIAL     PRIMARY KEY,

                 user_name        VARCHAR    NOT NULL,
                 user_password    VARCHAR    NOT NULL
             );
            """, None)

            t = Table("speedy_users", meta, autoload=True)
            t.insert().execute(user_name='user', user_password='lala')
            l = t.select().execute().fetchall()
            eq_(l, [(1, 'user', 'lala')])
        finally:
            testing.db.execute("drop table speedy_users", None)

    def _test_autoincrement(self, bind):
        ids = set()
        rs = bind.execute(aitable.insert(), int1=1)
        last = rs.last_inserted_ids()[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(), str1='row 2')
        last = rs.last_inserted_ids()[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(), int1=3, str1='row 3')
        last = rs.last_inserted_ids()[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(values={'int1':func.length('four')}))
        last = rs.last_inserted_ids()[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        eq_(list(bind.execute(aitable.select().order_by(aitable.c.id))),
            [(1, 1, None), (2, None, 'row 2'), (3, 3, 'row 3'), (4, 4, None)])

    def test_updatemany(self):
        # MySQL-Python 1.2.2 breaks functions in execute_many :(
        if (testing.against('mysql') and
            testing.db.dialect.dbapi.version_info[:3] == (1, 2, 2)):
            return

        t.insert().execute({}, {}, {})

        t.update(t.c.col1==sa.bindparam('pkval')).execute(
            {'pkval':51,'col7':None, 'col8':None, 'boolcol1':False})

        t.update(t.c.col1==sa.bindparam('pkval')).execute(
            {'pkval':51,},
            {'pkval':52,},
            {'pkval':53,})

        l = t.select().execute()
        ctexec = currenttime.scalar()
        today = datetime.date.today()
        eq_(l.fetchall(),
            [(51, 'im the update', f2, ts, ts, ctexec, False, False,
              13, today, 'py'),
             (52, 'im the update', f2, ts, ts, ctexec, True, False,
              13, today, 'py'),
             (53, 'im the update', f2, ts, ts, ctexec, True, False,
              13, today, 'py')])

 def test_update_values(self):
     r = t.insert().execute()
     pk = r.last_inserted_ids()[0]
     t.update(t.c.col1==pk, values={'col3': 55}).execute()
     l = t.select(t.c.col1==pk).execute()
     l = l.fetchone()
     eq_(55, l['col3'])

    def test_insert(self):
        r = t.insert().execute()
        assert r.lastrow_has_defaults()
        eq_(set(r.context.postfetch_cols),
            set([t.c.col3, t.c.col5, t.c.col4, t.c.col6]))

        r = t.insert(inline=True).execute()
        assert r.lastrow_has_defaults()
        eq_(set(r.context.postfetch_cols),
            set([t.c.col3, t.c.col5, t.c.col4, t.c.col6]))

        t.insert().execute()

        ctexec = sa.select([currenttime.label('now')], bind=testing.db).scalar()
        l = t.select().order_by(t.c.col1).execute()
        today = datetime.date.today()
        eq_(l.fetchall(), [
            (x, 'imthedefault', f, ts, ts, ctexec, True, False,
             12, today, 'py')
            for x in range(51, 54)])

        t.insert().execute(col9=None)
        assert r.lastrow_has_defaults()
        eq_(set(r.context.postfetch_cols),
            set([t.c.col3, t.c.col5, t.c.col4, t.c.col6]))

        eq_(t.select(t.c.col1==54).execute().fetchall(),
            [(54, 'imthedefault', f, ts, ts, ctexec, True, False,
              12, today, None)])

    def test_join(self):
        """Query.join"""

        session = create_session()
        q = (session.query(User).join(['orders', 'addresses']).
             filter(Address.id == 1))
        eq_([User(id=7)], q.all())

 def test_that_mssql_none_nullability_does_not_emit_nullability(self):
     schemagenerator = \
         mssql.MSSQLDialect().schemagenerator(mssql.MSSQLDialect(), None)
     self.column.nullable = None
     column_specification = \
         schemagenerator.get_column_specification(self.column)
     eq_("test_column VARCHAR", column_specification)

 def test_that_mssql_specified_not_nullable_emits_not_null(self):
     schemagenerator = \
         mssql.MSSQLDialect().schemagenerator(mssql.MSSQLDialect(), None)
     self.column.nullable = False
     column_specification = \
         schemagenerator.get_column_specification(self.column)
     eq_("test_column VARCHAR NOT NULL", column_specification)

    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

 def test_length_deprecation(self):
     self.assertRaises(exc.SADeprecationWarning, Numeric, length=8)
     
     @testing.uses_deprecated(".*is deprecated for Numeric")
     def go():
         n = Numeric(length=12)
         assert n.scale == 12
     go()
     
     n = Numeric(scale=12)
     for dialect in engines.all_dialects():
         n2 = dialect.type_descriptor(n)
         eq_(n2.scale, 12, dialect.name)
         
         # test colspec generates successfully using 'scale'
         assert n2.get_col_spec()
         
         # test constructor of the dialect-specific type
         n3 = n2.__class__(scale=5)
         eq_(n3.scale, 5, dialect.name)
         
         @testing.uses_deprecated(".*is deprecated for Numeric")
         def go():
             n3 = n2.__class__(length=6)
             eq_(n3.scale, 6, dialect.name)
         go()

    def test_lazyhistory(self):
        """tests that history functions work with lazy-loading attributes"""

        class Foo(_base.BasicEntity):
            pass
        class Bar(_base.BasicEntity):
            pass

        attributes.register_class(Foo)
        attributes.register_class(Bar)

        bar1, bar2, bar3, bar4 = [Bar(id=1), Bar(id=2), Bar(id=3), Bar(id=4)]
        def func1():
            return "this is func 1"
        def func2():
            return [bar1, bar2, bar3]

        attributes.register_attribute(Foo, 'col1', uselist=False, callable_=lambda o:func1, useobject=True)
        attributes.register_attribute(Foo, 'col2', uselist=True, callable_=lambda o:func2, useobject=True)
        attributes.register_attribute(Bar, 'id', uselist=False, useobject=True)

        x = Foo()
        attributes.instance_state(x).commit_all()
        x.col2.append(bar4)
        eq_(attributes.get_history(attributes.instance_state(x), 'col2'), ([bar4], [bar1, bar2, bar3], []))

    def test_standard(self):
        class A(object):
            pass

        attributes.register_class(A)

        eq_(type(attributes.manager_of_class(A)), attributes.ClassManager)

 def test_configured_order_by(self):
     mapper(User, users, properties={
         'addresses':dynamic_loader(mapper(Address, addresses), order_by=desc(Address.email_address))
     })
     sess = create_session()
     u = sess.query(User).get(8)
     eq_(list(u.addresses), [Address(email_address=u'[email protected]'), Address(email_address=u'[email protected]'), Address(email_address=u'[email protected]')])

    def test_as_declarative(self):
        class User(ComparableEntity):
            __tablename__ = 'users'

            id = Column('id', Integer, primary_key=True)
            name = Column('name', String(50))
            addresses = relation("Address", backref="user")

        class Address(ComparableEntity):
            __tablename__ = 'addresses'

            id = Column('id', Integer, primary_key=True)
            email = Column('email', String(50))
            user_id = Column('user_id', Integer, ForeignKey('users.id'))
        
        reg = {}
        decl.instrument_declarative(User, reg, Base.metadata)
        decl.instrument_declarative(Address, reg, Base.metadata)
        Base.metadata.create_all()
        
        u1 = User(name='u1', addresses=[
            Address(email='one'),
            Address(email='two'),
        ])
        sess = create_session()
        sess.add(u1)
        sess.flush()
        sess.clear()

        eq_(sess.query(User).all(), [User(name='u1', addresses=[
            Address(email='one'),
            Address(email='two'),
        ])])