from sqlalchemy.test.testing import eq_, assert_raises, assert_raises_message
import StringIO, unicodedata
from sqlalchemy import types as sql_types
from sqlalchemy import schema
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy import MetaData
from sqlalchemy.test.schema import Table, Column
import sqlalchemy as sa
from sqlalchemy.test import TestBase, ComparesTables, \
                            testing, engines, AssertsCompiledSQL

create_inspector = Inspector.from_engine

metadata, users = None, None

class ReflectionTest(TestBase, ComparesTables):

    @testing.exclude('mssql', '<', (10, 0, 0),
                     'Date is only supported on MSSQL 2008+')
    @testing.exclude('mysql', '<', (4, 1, 1),
                     'early types are squirrely')
    def test_basic_reflection(self):
        meta = MetaData(testing.db)

        users = Table('engine_users', meta,
            Column('user_id', sa.INT, primary_key=True),
            Column('user_name', sa.VARCHAR(20), nullable=False),
            Column('test1', sa.CHAR(5), nullable=False),
            Column('test2', sa.Float(5), nullable=False),
            Column('test3', sa.Text),
            Column('test4', sa.Numeric(10, 2), nullable = False),
            Column('test5', sa.Date),
            Column('parent_user_id', sa.Integer,
                   sa.ForeignKey('engine_users.user_id')),
            Column('test6', sa.Date, nullable=False),
            Column('test7', sa.Text),
            Column('test8', sa.LargeBinary),
            Column('test_passivedefault2', sa.Integer, server_default='5'),
            Column('test9', sa.LargeBinary(100)),
            Column('test10', sa.Numeric(10, 2)),
            test_needs_fk=True,
        )

        addresses = Table(
            'engine_email_addresses',
            meta,
            Column('address_id', sa.Integer, primary_key=True),
            Column('remote_user_id', sa.Integer,
                   sa.ForeignKey(users.c.user_id)),
            Column('email_address', sa.String(20)),
            test_needs_fk=True,
            )
        meta.create_all()

        try:
            meta2 = MetaData()
            reflected_users = Table('engine_users', meta2,
                                    autoload=True,
                                    autoload_with=testing.db)
            reflected_addresses = Table('engine_email_addresses',
                    meta2, autoload=True, autoload_with=testing.db)
            self.assert_tables_equal(users, reflected_users)
            self.assert_tables_equal(addresses, reflected_addresses)
        finally:
            meta.drop_all()
    
    def test_two_foreign_keys(self):
        meta = MetaData(testing.db)
        t1 = Table(
            't1',
            meta,
            Column('id', sa.Integer, primary_key=True),
            Column('t2id', sa.Integer, sa.ForeignKey('t2.id')),
            Column('t3id', sa.Integer, sa.ForeignKey('t3.id')),
            test_needs_fk=True,
            )
        t2 = Table('t2', meta, Column('id', sa.Integer,
                   primary_key=True), test_needs_fk=True)
        t3 = Table('t3', meta, Column('id', sa.Integer,
                   primary_key=True), test_needs_fk=True)
        meta.create_all()
        try:
            meta2 = MetaData()
            t1r, t2r, t3r = [Table(x, meta2, autoload=True,
                             autoload_with=testing.db) for x in ('t1',
                             't2', 't3')]
            assert t1r.c.t2id.references(t2r.c.id)
            assert t1r.c.t3id.references(t3r.c.id)
        finally:
            meta.drop_all()
    
    def test_nonexistent(self):
        meta = MetaData(testing.db)
        assert_raises(sa.exc.NoSuchTableError, Table, 'nonexistent',
                      meta, autoload=True)
        
    def test_include_columns(self):
        meta = MetaData(testing.db)
        foo = Table('foo', meta, *[Column(n, sa.String(30))
                                   for n in ['a', 'b', 'c', 'd', 'e', 'f']])
        meta.create_all()
        try:
            meta2 = MetaData(testing.db)
            foo = Table('foo', meta2, autoload=True,
                        include_columns=['b', 'f', 'e'])
            # test that cols come back in original order
            eq_([c.name for c in foo.c], ['b', 'e', 'f'])
            for c in ('b', 'f', 'e'):
                assert c in foo.c
            for c in ('a', 'c', 'd'):
                assert c not in foo.c

            # test against a table which is already reflected
            meta3 = MetaData(testing.db)
            foo = Table('foo', meta3, autoload=True)
            foo = Table('foo', meta3, include_columns=['b', 'f', 'e'],
                        useexisting=True)
            eq_([c.name for c in foo.c], ['b', 'e', 'f'])
            for c in ('b', 'f', 'e'):
                assert c in foo.c
            for c in ('a', 'c', 'd'):
                assert c not in foo.c
        finally:
            meta.drop_all()

    @testing.emits_warning(r".*omitted columns")
    def test_include_columns_indexes(self):
        m = MetaData(testing.db)
        
        t1 = Table('t1', m, Column('a', sa.Integer), Column('b', sa.Integer))
        sa.Index('foobar', t1.c.a, t1.c.b)
        sa.Index('bat', t1.c.a)
        m.create_all()
        try:
            m2 = MetaData(testing.db)
            t2 = Table('t1', m2, autoload=True)
            assert len(t2.indexes) == 2

            m2 = MetaData(testing.db)
            t2 = Table('t1', m2, autoload=True, include_columns=['a'])
            assert len(t2.indexes) == 1

            m2 = MetaData(testing.db)
            t2 = Table('t1', m2, autoload=True, include_columns=['a', 'b'])
            assert len(t2.indexes) == 2
        finally:
            m.drop_all()

    def test_autoincrement_col(self):
        """test that 'autoincrement' is reflected according to sqla's policy.
        
        Don't mark this test as unsupported for any backend !
        
        (technically it fails with MySQL InnoDB since "id" comes before "id2")
        
        """
        
        meta = MetaData(testing.db)
        t1 = Table('test', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('data', sa.String(50)),
        )
        t2 = Table('test2', meta,
            Column('id', sa.Integer, sa.ForeignKey('test.id'),
                                        primary_key=True),
            Column('id2', sa.Integer, primary_key=True),
            Column('data', sa.String(50)),
        )
        meta.create_all()
        try:
            m2 = MetaData(testing.db)
            t1a = Table('test', m2, autoload=True)
            assert t1a._autoincrement_column is t1a.c.id
            
            t2a = Table('test2', m2, autoload=True)
            assert t2a._autoincrement_column is t2a.c.id2
            
        finally:
            meta.drop_all()
            
    def test_unknown_types(self):
        meta = MetaData(testing.db)
        t = Table("test", meta,
            Column('foo', sa.DateTime))

        ischema_names = testing.db.dialect.ischema_names
        t.create()
        testing.db.dialect.ischema_names = {}
        try:
            m2 = MetaData(testing.db)
            assert_raises(sa.exc.SAWarning, Table, "test", m2, autoload=True)

            @testing.emits_warning('Did not recognize type')
            def warns():
                m3 = MetaData(testing.db)
                t3 = Table("test", m3, autoload=True)
                assert t3.c.foo.type.__class__ == sa.types.NullType

        finally:
            testing.db.dialect.ischema_names = ischema_names
            t.drop()

    def test_basic_override(self):
        meta = MetaData(testing.db)
        table = Table(
            'override_test', meta,
            Column('col1', sa.Integer, primary_key=True),
            Column('col2', sa.String(20)),
            Column('col3', sa.Numeric)
        )
        table.create()

        meta2 = MetaData(testing.db)
        try:
            table = Table(
                'override_test', meta2,
                Column('col2', sa.Unicode()),
                Column('col4', sa.String(30)), autoload=True)

            self.assert_(isinstance(table.c.col1.type, sa.Integer))
            self.assert_(isinstance(table.c.col2.type, sa.Unicode))
            self.assert_(isinstance(table.c.col4.type, sa.String))
        finally:
            table.drop()

    def test_override_pkfk(self):
        """test that you can override columns which contain foreign keys
        to other reflected tables, where the foreign key column is also
        a primary key column"""

        meta = MetaData(testing.db)
        users = Table('users', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('name', sa.String(30)))
        addresses = Table('addresses', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('street', sa.String(30)))


        meta.create_all()
        try:
            meta2 = MetaData(testing.db)
            a2 = Table('addresses', meta2,
                Column('id', sa.Integer,
                       sa.ForeignKey('users.id'), primary_key=True),
                autoload=True)
            u2 = Table('users', meta2, autoload=True)

            assert list(a2.primary_key) == [a2.c.id]
            assert list(u2.primary_key) == [u2.c.id]
            assert u2.join(a2).onclause.compare(u2.c.id==a2.c.id)

            meta3 = MetaData(testing.db)
            u3 = Table('users', meta3, autoload=True)
            a3 = Table('addresses', meta3,
                Column('id', sa.Integer, sa.ForeignKey('users.id'),
                       primary_key=True),
                autoload=True)

            assert list(a3.primary_key) == [a3.c.id]
            assert list(u3.primary_key) == [u3.c.id]
            assert u3.join(a3).onclause.compare(u3.c.id==a3.c.id)

        finally:
            meta.drop_all()

    def test_override_nonexistent_fk(self):
        """test that you can override columns and create new foreign
        keys to other reflected tables which have no foreign keys.  this
        is common with MySQL MyISAM tables."""

        meta = MetaData(testing.db)
        users = Table('users', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('name', sa.String(30)))
        addresses = Table('addresses', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('street', sa.String(30)),
            Column('user_id', sa.Integer))

        meta.create_all()
        try:
            meta2 = MetaData(testing.db)
            a2 = Table('addresses', meta2, 
                    Column('user_id',sa.Integer, sa.ForeignKey('users.id')),
                   autoload=True)
            u2 = Table('users', meta2, autoload=True)
            assert len(a2.c.user_id.foreign_keys) == 1
            assert len(a2.foreign_keys) == 1
            assert [c.parent for c in a2.foreign_keys] == [a2.c.user_id]
            assert [c.parent for c in a2.c.user_id.foreign_keys] \
                == [a2.c.user_id]
            assert list(a2.c.user_id.foreign_keys)[0].parent \
                is a2.c.user_id
            assert u2.join(a2).onclause.compare(u2.c.id == a2.c.user_id)
            meta3 = MetaData(testing.db)

            u3 = Table('users', meta3, autoload=True)

            a3 = Table('addresses', meta3, Column('user_id',
                       sa.Integer, sa.ForeignKey('users.id')),
                       autoload=True)
            assert u3.join(a3).onclause.compare(u3.c.id == a3.c.user_id)

            meta4 = MetaData(testing.db)

            u4 = Table('users', meta4, 
                    Column('id', sa.Integer, key='u_id', primary_key=True),
                    autoload=True)
                    
            a4 = Table(
                'addresses',
                meta4,
                Column('id', sa.Integer, key='street',
                       primary_key=True),
                Column('street', sa.String(30), key='user_id'),
                Column('user_id', sa.Integer, sa.ForeignKey('users.u_id'
                       ), key='id'),
                autoload=True,
                )
            assert u4.join(a4).onclause.compare(u4.c.u_id == a4.c.id)
            assert list(u4.primary_key) == [u4.c.u_id]
            assert len(u4.columns) == 2
            assert len(u4.constraints) == 1
            assert len(a4.columns) == 3
            assert len(a4.constraints) == 2
        finally:
            meta.drop_all()

    def test_override_keys(self):
        """test that columns can be overridden with a 'key', 
        and that ForeignKey targeting during reflection still works."""
        

        meta = MetaData(testing.db)
        a1 = Table('a', meta,
            Column('x', sa.Integer, primary_key=True),
            Column('z', sa.Integer),
            test_needs_fk=True
        )
        b1 = Table('b', meta,
            Column('y', sa.Integer, sa.ForeignKey('a.x')),
            test_needs_fk=True
        )
        meta.create_all()
        try:
            m2 = MetaData(testing.db)
            a2 = Table('a', m2, 
                    Column('x', sa.Integer, primary_key=True, key='x1'),
                    autoload=True)
            b2 = Table('b', m2, autoload=True)
            assert a2.join(b2).onclause.compare(a2.c.x1 == b2.c.y)
            assert b2.c.y.references(a2.c.x1)
        finally:
            meta.drop_all()
    
    def test_nonreflected_fk_raises(self):
        """test that a NoReferencedColumnError is raised when reflecting
        a table with an FK to another table which has not included the target
        column in its reflection.
        
        """
        meta = MetaData(testing.db)
        a1 = Table('a', meta,
            Column('x', sa.Integer, primary_key=True),
            Column('z', sa.Integer),
            test_needs_fk=True
        )
        b1 = Table('b', meta,
            Column('y', sa.Integer, sa.ForeignKey('a.x')),
            test_needs_fk=True
        )
        meta.create_all()
        try:
            m2 = MetaData(testing.db)
            a2 = Table('a', m2, include_columns=['z'], autoload=True)
            b2 = Table('b', m2, autoload=True)
            
            assert_raises(sa.exc.NoReferencedColumnError, a2.join, b2)
        finally:
            meta.drop_all()
        
        
    @testing.exclude('mysql', '<', (4, 1, 1), 'innodb funkiness')
    def test_override_existing_fk(self):
        """test that you can override columns and specify new foreign
        keys to other reflected tables, on columns which *do* already
        have that foreign key, and that the FK is not duped. """

        meta = MetaData(testing.db)
        users = Table('users', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('name', sa.String(30)),
            test_needs_fk=True)
        addresses = Table('addresses', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('user_id', sa.Integer, sa.ForeignKey('users.id')),
            test_needs_fk=True)

        meta.create_all()
        try:
            meta2 = MetaData(testing.db)
            a2 = Table('addresses', meta2, 
                    Column('user_id',sa.Integer, sa.ForeignKey('users.id')),
                    autoload=True)
            u2 = Table('users', meta2, autoload=True)
            s = sa.select([a2])
            
            assert s.c.user_id is not None
            assert len(a2.foreign_keys) == 1
            assert len(a2.c.user_id.foreign_keys) == 1
            assert len(a2.constraints) == 2
            assert [c.parent for c in a2.foreign_keys] == [a2.c.user_id]
            assert [c.parent for c in a2.c.user_id.foreign_keys] \
                == [a2.c.user_id]
            assert list(a2.c.user_id.foreign_keys)[0].parent \
                is a2.c.user_id
            assert u2.join(a2).onclause.compare(u2.c.id == a2.c.user_id)
            
            meta2 = MetaData(testing.db)
            u2 = Table('users', meta2, Column('id', sa.Integer,
                       primary_key=True), autoload=True)
            a2 = Table('addresses', meta2, Column('id', sa.Integer,
                       primary_key=True), Column('user_id', sa.Integer,
                       sa.ForeignKey('users.id')), autoload=True)
            s = sa.select([a2])
            
            assert s.c.user_id is not None
            assert len(a2.foreign_keys) == 1
            assert len(a2.c.user_id.foreign_keys) == 1
            assert len(a2.constraints) == 2
            assert [c.parent for c in a2.foreign_keys] == [a2.c.user_id]
            assert [c.parent for c in a2.c.user_id.foreign_keys] \
                == [a2.c.user_id]
            assert list(a2.c.user_id.foreign_keys)[0].parent \
                is a2.c.user_id
            assert u2.join(a2).onclause.compare(u2.c.id == a2.c.user_id)
            
        finally:
            meta.drop_all()

    @testing.exclude('mysql', '<', (4, 1, 1), 'innodb funkiness')
    def test_use_existing(self):
        meta = MetaData(testing.db)
        users = Table('users', meta, 
                    Column('id', sa.Integer, primary_key=True), 
                    Column('name', sa.String(30)),
                      test_needs_fk=True)
        addresses = Table(
            'addresses',
            meta,
            Column('id', sa.Integer, primary_key=True),
            Column('user_id', sa.Integer, sa.ForeignKey('users.id')),
            Column('data', sa.String(100)),
            test_needs_fk=True,
            )
        meta.create_all()
        try:
            meta2 = MetaData(testing.db)
            addresses = Table('addresses', meta2, Column('data',
                              sa.Unicode), autoload=True)
            try:
                users = Table('users', meta2, Column('name',
                              sa.Unicode), autoload=True)
                assert False
            except sa.exc.InvalidRequestError, err:
                assert str(err) \
                    == "Table 'users' is already defined for this "\
                    "MetaData instance.  Specify 'useexisting=True' "\
                    "to redefine options and columns on an existing "\
                    "Table object."
            users = Table('users', meta2, Column('name', sa.Unicode),
                          autoload=True, useexisting=True)
            assert isinstance(users.c.name.type, sa.Unicode)
            assert not users.quote
            users = Table('users', meta2, quote=True, autoload=True,
                          useexisting=True)
            assert users.quote
        finally:
            meta.drop_all()

    def test_pks_not_uniques(self):
        """test that primary key reflection not tripped up by unique
        indexes"""

        testing.db.execute("""
        CREATE TABLE book (
            id INTEGER NOT NULL,
            title VARCHAR(100) NOT NULL,
            series INTEGER,
            series_id INTEGER,
            UNIQUE(series, series_id),
            PRIMARY KEY(id)
        )""")
        try:
            metadata = MetaData(bind=testing.db)
            book = Table('book', metadata, autoload=True)
            assert book.primary_key.contains_column(book.c.id)
            assert not book.primary_key.contains_column(book.c.series)
            assert len(book.primary_key) == 1
        finally:
            testing.db.execute("drop table book")

    def test_fk_error(self):
        metadata = MetaData(testing.db)
        slots_table = Table('slots', metadata,
            Column('slot_id', sa.Integer, primary_key=True),
            Column('pkg_id', sa.Integer, sa.ForeignKey('pkgs.pkg_id')),
            Column('slot', sa.String(128)),
            )
            
        assert_raises_message(sa.exc.InvalidRequestError,
                              "Could not find table 'pkgs' with which "
                              "to generate a foreign key",
                              metadata.create_all)

    def test_composite_pks(self):
        """test reflection of a composite primary key"""

        testing.db.execute("""
        CREATE TABLE book (
            id INTEGER NOT NULL,
            isbn VARCHAR(50) NOT NULL,
            title VARCHAR(100) NOT NULL,
            series INTEGER,
            series_id INTEGER,
            UNIQUE(series, series_id),
            PRIMARY KEY(id, isbn)
        )""")
        try:
            metadata = MetaData(bind=testing.db)
            book = Table('book', metadata, autoload=True)
            assert book.primary_key.contains_column(book.c.id)
            assert book.primary_key.contains_column(book.c.isbn)
            assert not book.primary_key.contains_column(book.c.series)
            assert len(book.primary_key) == 2
        finally:
            testing.db.execute("drop table book")

    @testing.exclude('mysql', '<', (4, 1, 1), 'innodb funkiness')
    def test_composite_fk(self):
        """test reflection of composite foreign keys"""

        meta = MetaData(testing.db)
        multi = Table(
            'multi', meta,
            Column('multi_id', sa.Integer, primary_key=True),
            Column('multi_rev', sa.Integer, primary_key=True),
            Column('multi_hoho', sa.Integer, primary_key=True),
            Column('name', sa.String(50), nullable=False),
            Column('val', sa.String(100)),
            test_needs_fk=True,
        )
        multi2 = Table('multi2', meta,
            Column('id', sa.Integer, primary_key=True),
            Column('foo', sa.Integer),
            Column('bar', sa.Integer),
            Column('lala', sa.Integer),
            Column('data', sa.String(50)),
            sa.ForeignKeyConstraint(['foo', 'bar', 'lala'],
             ['multi.multi_id', 'multi.multi_rev', 'multi.multi_hoho'
             ]), 
            test_needs_fk=True,
        )
        meta.create_all()

        try:
            meta2 = MetaData()
            table = Table('multi', meta2, autoload=True,
                          autoload_with=testing.db)
            table2 = Table('multi2', meta2, autoload=True,
                           autoload_with=testing.db)
            self.assert_tables_equal(multi, table)
            self.assert_tables_equal(multi2, table2)
            j = sa.join(table, table2)
            self.assert_(sa.and_(table.c.multi_id == table2.c.foo,
                         table.c.multi_rev == table2.c.bar,
                         table.c.multi_hoho
                         == table2.c.lala).compare(j.onclause))
        finally:
            meta.drop_all()


    @testing.crashes('oracle', 'FIXME: unknown, confirm not fails_on')
    def test_reserved(self):

        # check a table that uses an SQL reserved name doesn't cause an
        # error

        meta = MetaData(testing.db)
        table_a = Table('select', meta, Column('not', sa.Integer,
                        primary_key=True), Column('from',
                        sa.String(12), nullable=False),
                        sa.UniqueConstraint('from', name='when'))
        sa.Index('where', table_a.c['from'])

        # There's currently no way to calculate identifier case
        # normalization in isolation, so...

        if testing.against('firebird', 'oracle', 'maxdb'):
            check_col = 'TRUE'
        else:
            check_col = 'true'
        quoter = meta.bind.dialect.identifier_preparer.quote_identifier
        
        table_b = Table('false', meta, 
                    Column('create', sa.Integer, primary_key=True), 
                    Column('true', sa.Integer,sa.ForeignKey('select.not')),
                    sa.CheckConstraint('%s <> 1'
                        % quoter(check_col), name='limit')
                    )
                    
        table_c = Table('is', meta, 
                Column('or', sa.Integer, nullable=False, primary_key=True), 
                Column('join', sa.Integer, nullable=False, primary_key=True),
                sa.PrimaryKeyConstraint('or', 'join', name='to')
                )
        index_c = sa.Index('else', table_c.c.join)
        meta.create_all()
        index_c.drop()
        meta2 = MetaData(testing.db)
        try:
            table_a2 = Table('select', meta2, autoload=True)
            table_b2 = Table('false', meta2, autoload=True)
            table_c2 = Table('is', meta2, autoload=True)
        finally:
            meta.drop_all()

    def test_reflect_all(self):
        existing = testing.db.table_names()

        names = ['rt_%s' % name for name in ('a','b','c','d','e')]
        nameset = set(names)
        for name in names:
            # be sure our starting environment is sane
            self.assert_(name not in existing)
        self.assert_('rt_f' not in existing)

        baseline = MetaData(testing.db)
        for name in names:
            Table(name, baseline, Column('id', sa.Integer, primary_key=True))
        baseline.create_all()

        try:
            m1 = MetaData(testing.db)
            self.assert_(not m1.tables)
            m1.reflect()
            self.assert_(nameset.issubset(set(m1.tables.keys())))

            m2 = MetaData()
            m2.reflect(testing.db, only=['rt_a', 'rt_b'])
            self.assert_(set(m2.tables.keys()) == set(['rt_a', 'rt_b']))

            m3 = MetaData()
            c = testing.db.connect()
            m3.reflect(bind=c, only=lambda name, meta: name == 'rt_c')
            self.assert_(set(m3.tables.keys()) == set(['rt_c']))

            m4 = MetaData(testing.db)
            try:
                m4.reflect(only=['rt_a', 'rt_f'])
                self.assert_(False)
            except sa.exc.InvalidRequestError, e:
                self.assert_(e.args[0].endswith('(rt_f)'))

            m5 = MetaData(testing.db)
            m5.reflect(only=[])
            self.assert_(not m5.tables)

            m6 = MetaData(testing.db)
            m6.reflect(only=lambda n, m: False)
            self.assert_(not m6.tables)

            m7 = MetaData(testing.db, reflect=True)
            self.assert_(nameset.issubset(set(m7.tables.keys())))

            try:
                m8 = MetaData(reflect=True)
                self.assert_(False)
            except sa.exc.ArgumentError, e:
                self.assert_(e.args[0]
                             == 'A bind must be supplied in '
                             'conjunction with reflect=True')
        finally:
            baseline.drop_all()

        if existing:
            print "Other tables present in database, skipping some checks."
        else:
            m9 = MetaData(testing.db)
            m9.reflect()
            self.assert_(not m9.tables)

    def test_index_reflection(self):
        m1 = MetaData(testing.db)
        t1 = Table('party', m1,
            Column('id', sa.Integer, nullable=False),
            Column('name', sa.String(20), index=True)
            )
        i1 = sa.Index('idx1', t1.c.id, unique=True)
        i2 = sa.Index('idx2', t1.c.name, t1.c.id, unique=False)
        m1.create_all()
        try:
            m2 = MetaData(testing.db)
            t2 = Table('party', m2, autoload=True)

            print len(t2.indexes), t2.indexes
            assert len(t2.indexes) == 3
            # Make sure indexes are in the order we expect them in
            tmp = [(idx.name, idx) for idx in t2.indexes]
            tmp.sort()
            r1, r2, r3 = [idx[1] for idx in tmp]

            assert r1.name == 'idx1'
            assert r2.name == 'idx2'
            assert r1.unique == True
            assert r2.unique == False
            assert r3.unique == False
            assert set([t2.c.id]) == set(r1.columns)
            assert set([t2.c.name, t2.c.id]) == set(r2.columns)
            assert set([t2.c.name]) == set(r3.columns)
        finally:
            m1.drop_all()

    def test_views(self):
        meta = MetaData(testing.db)
        users, addresses = createTables(meta, None)
        meta.create_all()
        createViews(meta.bind, None)
        try:
            m2 = MetaData(testing.db)
            users_v = Table("users_v", m2, autoload=True)
            addresses_v = Table("email_addresses_v", m2, autoload=True)
            
            for c1, c2 in zip(users.c, users_v.c):
                eq_(c1.name, c2.name)
                self.assert_types_base(c1, c2)
                
            for c1, c2 in zip(addresses.c, addresses_v.c):
                eq_(c1.name, c2.name)
                self.assert_types_base(c1, c2)
            
        finally:
            dropViews(meta.bind, None)
            meta.drop_all()
        
class CreateDropTest(TestBase):

    @classmethod
    def setup_class(cls):
        global metadata, users
        metadata = MetaData()
        users = Table('users', metadata, 
                    Column('user_id', sa.Integer,
                      sa.Sequence('user_id_seq', optional=True),
                      primary_key=True), 
                    Column('user_name',sa.String(40)))
                      
        addresses = Table('email_addresses', metadata,
                      Column('address_id', sa.Integer,
                          sa.Sequence('address_id_seq', optional=True),
                          primary_key=True), 
                      Column('user_id',
                          sa.Integer, sa.ForeignKey(users.c.user_id)),
                      Column('email_address', sa.String(40)))

        orders = Table(
            'orders',
            metadata,
            Column('order_id', sa.Integer, sa.Sequence('order_id_seq',
                   optional=True), primary_key=True),
            Column('user_id', sa.Integer,
                   sa.ForeignKey(users.c.user_id)),
            Column('description', sa.String(50)),
            Column('isopen', sa.Integer),
            )
        orderitems = Table('items', metadata, Column('item_id', sa.INT,
                           sa.Sequence('items_id_seq', optional=True),
                           primary_key=True), Column('order_id',
                           sa.INT, sa.ForeignKey('orders')),
                           Column('item_name', sa.VARCHAR(50)))

    def test_sorter( self ):
        tables = metadata.sorted_tables
        table_names = [t.name for t in tables]
        ua = [n for n in table_names if n in ('users', 'email_addresses')]
        oi = [n for n in table_names if n in ('orders', 'items')]
        
        eq_(ua, ['users', 'email_addresses'])
        eq_(oi, ['orders', 'items'])
        

    def testcheckfirst(self):
        try:
            assert not users.exists(testing.db)
            users.create(bind=testing.db)
            assert users.exists(testing.db)
            users.create(bind=testing.db, checkfirst=True)
            users.drop(bind=testing.db)
            users.drop(bind=testing.db, checkfirst=True)
            assert not users.exists(bind=testing.db)
            users.create(bind=testing.db, checkfirst=True)
            users.drop(bind=testing.db)
        finally:
            metadata.drop_all(bind=testing.db)

    def test_createdrop(self):
        metadata.create_all(bind=testing.db)
        eq_( testing.db.has_table('items'), True )
        eq_( testing.db.has_table('email_addresses'), True )
        metadata.create_all(bind=testing.db)
        eq_( testing.db.has_table('items'), True )

        metadata.drop_all(bind=testing.db)
        eq_( testing.db.has_table('items'), False )
        eq_( testing.db.has_table('email_addresses'), False )
        metadata.drop_all(bind=testing.db)
        eq_( testing.db.has_table('items'), False )

    def test_tablenames(self):
        metadata.create_all(bind=testing.db)

        # we only check to see if all the explicitly created tables are
        # there, rather than assertEqual -- the test db could have
        # "extra" tables if there is a misconfigured template.  (*cough*
        # tsearch2 w/ the pg windows installer.)

        self.assert_(not set(metadata.tables)
                     - set(testing.db.table_names()))
        metadata.drop_all(bind=testing.db)

class SchemaManipulationTest(TestBase):
    def test_append_constraint_unique(self):
        meta = MetaData()

        users = Table('users', meta, Column('id', sa.Integer))
        addresses = Table('addresses', meta, 
                        Column('id', sa.Integer), 
                        Column('user_id', sa.Integer))

        fk = sa.ForeignKeyConstraint(['user_id'],[users.c.id])

        addresses.append_constraint(fk)
        addresses.append_constraint(fk)
        assert len(addresses.c.user_id.foreign_keys) == 1
        assert addresses.constraints == set([addresses.primary_key, fk])

class UnicodeReflectionTest(TestBase):

    @testing.requires.unicode_connections
    def test_basic(self):
        try:

            # the 'convert_unicode' should not get in the way of the
            # reflection process.  reflecttable for oracle, postgresql
            # (others?) expect non-unicode strings in result sets/bind
            # params

            bind = engines.utf8_engine(options={'convert_unicode'
                    : True})
            metadata = MetaData(bind)
            if testing.against('sybase', 'maxdb', 'oracle', 'mssql'):
                names = set([u'plain'])
            else:
                names = set([u'plain', u'Unit\u00e9ble', u'\u6e2c\u8a66'
                            ])
            for name in names:
                Table(name, metadata, Column('id', sa.Integer,
                      sa.Sequence(name + '_id_seq'), primary_key=True))
            metadata.create_all()
            reflected = set(bind.table_names())

            # Jython 2.5 on Java 5 lacks unicodedata.normalize

            if not names.issubset(reflected) and hasattr(unicodedata,
                    'normalize'):

                # Python source files in the utf-8 coding seem to
                # normalize literals as NFC (and the above are
                # explicitly NFC).  Maybe this database normalizes NFD
                # on reflection.

                nfc = set([unicodedata.normalize('NFC', n) for n in
                          names])
                self.assert_(nfc == names)

                # Yep.  But still ensure that bulk reflection and
                # create/drop work with either normalization.

            r = MetaData(bind, reflect=True)
            r.drop_all()
            r.create_all()
        finally:
            metadata.drop_all()
            bind.dispose()


class SchemaTest(TestBase):

    def test_iteration(self):
        metadata = MetaData()
        table1 = Table('table1', metadata, Column('col1', sa.Integer,
                       primary_key=True), schema='someschema')
        table2 = Table('table2', metadata, Column('col1', sa.Integer,
                       primary_key=True), Column('col2', sa.Integer,
                       sa.ForeignKey('someschema.table1.col1')),
                       schema='someschema')

        t1 = str(schema.CreateTable(table1).compile(bind=testing.db))
        t2 = str(schema.CreateTable(table2).compile(bind=testing.db))
        if testing.db.dialect.preparer(testing.db.dialect).omit_schema:
            assert t1.index("CREATE TABLE table1") > -1
            assert t2.index("CREATE TABLE table2") > -1
        else:
            assert t1.index("CREATE TABLE someschema.table1") > -1
            assert t2.index("CREATE TABLE someschema.table2") > -1

    @testing.crashes('firebird', 'No schema support')
    @testing.fails_on('sqlite', 'FIXME: unknown')
    # fixme: revisit these below.
    @testing.fails_on('access', 'FIXME: unknown')
    @testing.fails_on('sybase', 'FIXME: unknown')
    def test_explicit_default_schema(self):
        engine = testing.db

        if testing.against('sqlite'):
            # Works for CREATE TABLE main.foo, SELECT FROM main.foo, etc.,
            # but fails on:
            #   FOREIGN KEY(col2) REFERENCES main.table1 (col1)
            schema = 'main'
        else:
            schema = engine.dialect.default_schema_name

        assert bool(schema)
        
        metadata = MetaData(engine)
        table1 = Table('table1', metadata,
                       Column('col1', sa.Integer, primary_key=True),
                       test_needs_fk=True,
                       schema=schema)
        table2 = Table('table2', metadata,
                       Column('col1', sa.Integer, primary_key=True),
                       Column('col2', sa.Integer,
                              sa.ForeignKey('%s.table1.col1' % schema)),
                       test_needs_fk=True,
                       schema=schema)
        try:
            metadata.create_all()
            metadata.create_all(checkfirst=True)
            assert len(metadata.tables) == 2
            metadata.clear()

            table1 = Table('table1', metadata, autoload=True, schema=schema)
            table2 = Table('table2', metadata, autoload=True, schema=schema)
            assert len(metadata.tables) == 2
        finally:
            metadata.drop_all()


class HasSequenceTest(TestBase):

    @testing.requires.sequences
    def test_has_sequence(self):
        metadata = MetaData()
        users = Table('users', metadata, Column('user_id', sa.Integer,
                      sa.Sequence('user_id_seq'), primary_key=True),
                      Column('user_name', sa.String(40)))
        metadata.create_all(bind=testing.db)
        try:
            eq_(testing.db.dialect.has_sequence(testing.db,
                'user_id_seq'), True)
        finally:
            metadata.drop_all(bind=testing.db)
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq'),
            False)

    @testing.requires.schemas
    @testing.requires.sequences
    def test_has_sequence_schema(self):
        test_schema = 'test_schema'
        s1 = sa.Sequence('user_id_seq', schema=test_schema)
        s2 = sa.Sequence('user_id_seq')
        testing.db.execute(schema.CreateSequence(s1))
        testing.db.execute(schema.CreateSequence(s2))
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq',
            schema=test_schema), True)
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq'),
            True)
        testing.db.execute(schema.DropSequence(s1))
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq',
            schema=test_schema), False)
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq'),
            True)
        testing.db.execute(schema.DropSequence(s2))
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq',
            schema=test_schema), False)
        eq_(testing.db.dialect.has_sequence(testing.db, 'user_id_seq'),
            False)
        
# Tests related to engine.reflection


def createTables(meta, schema=None):
    if schema:
        parent_user_id = Column('parent_user_id', sa.Integer,
            sa.ForeignKey('%s.users.user_id' % schema)
        )
    else:
        parent_user_id = Column('parent_user_id', sa.Integer,
            sa.ForeignKey('users.user_id')
        )

    users = Table('users', meta,
        Column('user_id', sa.INT, primary_key=True),
        Column('user_name', sa.VARCHAR(20), nullable=False),
        Column('test1', sa.CHAR(5), nullable=False),
        Column('test2', sa.Float(5), nullable=False),
        Column('test3', sa.Text),
        Column('test4', sa.Numeric(10, 2), nullable = False),
        Column('test5', sa.Date),
        Column('test5-1', sa.TIMESTAMP),
        parent_user_id,
        Column('test6', sa.Date, nullable=False),
        Column('test7', sa.Text),
        Column('test8', sa.LargeBinary),
        Column('test_passivedefault2', sa.Integer, server_default='5'),
        Column('test9', sa.LargeBinary(100)),
        Column('test10', sa.Numeric(10, 2)),
        schema=schema,
        test_needs_fk=True,
    )
    addresses = Table('email_addresses', meta,
        Column('address_id', sa.Integer),
        Column('remote_user_id', sa.Integer,
               sa.ForeignKey(users.c.user_id)),
        Column('email_address', sa.String(20)),
        sa.PrimaryKeyConstraint('address_id', name='email_ad_pk'),
        schema=schema,
        test_needs_fk=True,
    )
    return (users, addresses)

def createIndexes(con, schema=None):
    fullname = 'users'
    if schema:
        fullname = "%s.%s" % (schema, 'users')
    query = "CREATE INDEX users_t_idx ON %s (test1, test2)" % fullname
    con.execute(sa.sql.text(query))

def createViews(con, schema=None):
    for table_name in ('users', 'email_addresses'):
        fullname = table_name
        if schema:
            fullname = "%s.%s" % (schema, table_name)
        view_name = fullname + '_v'
        query = "CREATE VIEW %s AS SELECT * FROM %s" % (view_name,
                                                                   fullname)
        con.execute(sa.sql.text(query))

def dropViews(con, schema=None):
    for table_name in ('email_addresses', 'users'):
        fullname = table_name
        if schema:
            fullname = "%s.%s" % (schema, table_name)
        view_name = fullname + '_v'
        query = "DROP VIEW %s" % view_name
        con.execute(sa.sql.text(query))


class ReverseCasingReflectTest(TestBase, AssertsCompiledSQL):

    @testing.requires.denormalized_names
    def setup(self):
        testing.db.execute("""
        CREATE TABLE weird_casing(
                col1 char(20),
                "Col2" char(20),
                "col3" char(20)
        )
        """)

    @testing.requires.denormalized_names
    def teardown(self):
        testing.db.execute("drop table weird_casing")

    @testing.requires.denormalized_names
    def test_direct_quoting(self):
        m = MetaData(testing.db)
        t = Table('weird_casing', m, autoload=True)
        self.assert_compile(t.select(),
                            'SELECT weird_casing.col1, '
                            'weird_casing."Col2", weird_casing."col3" '
                            'FROM weird_casing')

class ComponentReflectionTest(TestBase):

    @testing.requires.schemas
    def test_get_schema_names(self):
        insp = Inspector(testing.db)
        
        self.assert_('test_schema' in insp.get_schema_names())

    def test_dialect_initialize(self):
        engine = engines.testing_engine()
        assert not hasattr(engine.dialect, 'default_schema_name')
        insp = Inspector(engine)
        assert hasattr(engine.dialect, 'default_schema_name')
        
    def test_get_default_schema_name(self):
        insp = Inspector(testing.db)
        eq_(insp.default_schema_name, testing.db.dialect.default_schema_name)
        
    def _test_get_table_names(self, schema=None, table_type='table',
                              order_by=None):
        meta = MetaData(testing.db)
        (users, addresses) = createTables(meta, schema)
        meta.create_all()
        createViews(meta.bind, schema)
        try:
            insp = Inspector(meta.bind)
            if table_type == 'view':
                table_names = insp.get_view_names(schema)
                table_names.sort()
                answer = ['email_addresses_v', 'users_v']
            else:
                table_names = insp.get_table_names(schema,
                                                   order_by=order_by)
                table_names.sort()
                if order_by == 'foreign_key':
                    answer = ['users', 'email_addresses']
                else:
                    answer = ['email_addresses', 'users']
            eq_(table_names, answer)
        finally:
            dropViews(meta.bind, schema)
            addresses.drop()
            users.drop()

    def test_get_table_names(self):
        self._test_get_table_names()

    @testing.requires.schemas
    def test_get_table_names_with_schema(self):
        self._test_get_table_names('test_schema')

    def test_get_view_names(self):
        self._test_get_table_names(table_type='view')

    @testing.requires.schemas
    def test_get_view_names_with_schema(self):
        self._test_get_table_names('test_schema', table_type='view')

    def _test_get_columns(self, schema=None, table_type='table'):
        meta = MetaData(testing.db)
        users, addresses = createTables(meta, schema)
        table_names = ['users', 'email_addresses']
        meta.create_all()
        if table_type == 'view':
            createViews(meta.bind, schema)
            table_names = ['users_v', 'email_addresses_v']
        try:
            insp = Inspector(meta.bind)
            for table_name, table in zip(table_names, (users,
                    addresses)):
                schema_name = schema
                cols = insp.get_columns(table_name, schema=schema_name)
                self.assert_(len(cols) > 0, len(cols))

                # should be in order

                for i, col in enumerate(table.columns):
                    eq_(col.name, cols[i]['name'])
                    ctype = cols[i]['type'].__class__
                    ctype_def = col.type
                    if isinstance(ctype_def, sa.types.TypeEngine):
                        ctype_def = ctype_def.__class__

                    # Oracle returns Date for DateTime.

                    if testing.against('oracle') and ctype_def \
                        in (sql_types.Date, sql_types.DateTime):
                        ctype_def = sql_types.Date

                    # assert that the desired type and return type share
                    # a base within one of the generic types.

                    self.assert_(len(set(ctype.__mro__).
                        intersection(ctype_def.__mro__).intersection([
                        sql_types.Integer,
                        sql_types.Numeric,
                        sql_types.DateTime,
                        sql_types.Date,
                        sql_types.Time,
                        sql_types.String,
                        sql_types._Binary,
                        ])) > 0, '%s(%s), %s(%s)' % (col.name,
                                col.type, cols[i]['name'], ctype))
        finally:
            if table_type == 'view':
                dropViews(meta.bind, schema)
            addresses.drop()
            users.drop()

    def test_get_columns(self):
        self._test_get_columns()

    @testing.requires.schemas
    def test_get_columns_with_schema(self):
        self._test_get_columns(schema='test_schema')

    def test_get_view_columns(self):
        self._test_get_columns(table_type='view')

    @testing.requires.schemas
    def test_get_view_columns_with_schema(self):
        self._test_get_columns(schema='test_schema', table_type='view')

    def _test_get_primary_keys(self, schema=None):
        meta = MetaData(testing.db)
        (users, addresses) = createTables(meta, schema)
        meta.create_all()
        insp = Inspector(meta.bind)
        try:
            users_pkeys = insp.get_primary_keys(users.name,
                                                schema=schema)
            eq_(users_pkeys,  ['user_id'])
            addr_cons = insp.get_pk_constraint(addresses.name,
                                                schema=schema)
                                                
            addr_pkeys = addr_cons['constrained_columns']
            eq_(addr_pkeys,  ['address_id'])
            
            @testing.requires.reflects_pk_names
            def go():
                eq_(addr_cons['name'], 'email_ad_pk')
            go()
            
        finally:
            addresses.drop()
            users.drop()

    def test_get_primary_keys(self):
        self._test_get_primary_keys()

    @testing.fails_on('sqlite', 'no schemas')
    def test_get_primary_keys_with_schema(self):
        self._test_get_primary_keys(schema='test_schema')

    def _test_get_foreign_keys(self, schema=None):
        meta = MetaData(testing.db)
        (users, addresses) = createTables(meta, schema)
        meta.create_all()
        insp = Inspector(meta.bind)
        try:
            expected_schema = schema
            # users
            users_fkeys = insp.get_foreign_keys(users.name,
                                                schema=schema)
            fkey1 = users_fkeys[0]
            self.assert_(fkey1['name'] is not None)
            eq_(fkey1['referred_schema'], expected_schema)
            eq_(fkey1['referred_table'], users.name)
            eq_(fkey1['referred_columns'], ['user_id', ])
            eq_(fkey1['constrained_columns'], ['parent_user_id'])
            #addresses
            addr_fkeys = insp.get_foreign_keys(addresses.name,
                                               schema=schema)
            fkey1 = addr_fkeys[0]
            self.assert_(fkey1['name'] is not None)
            eq_(fkey1['referred_schema'], expected_schema)
            eq_(fkey1['referred_table'], users.name)
            eq_(fkey1['referred_columns'], ['user_id', ])
            eq_(fkey1['constrained_columns'], ['remote_user_id'])
        finally:
            addresses.drop()
            users.drop()

    def test_get_foreign_keys(self):
        self._test_get_foreign_keys()

    @testing.requires.schemas
    def test_get_foreign_keys_with_schema(self):
        self._test_get_foreign_keys(schema='test_schema')

    def _test_get_indexes(self, schema=None):
        meta = MetaData(testing.db)
        (users, addresses) = createTables(meta, schema)
        meta.create_all()
        createIndexes(meta.bind, schema)
        try:
            # The database may decide to create indexes for foreign keys, etc.
            # so there may be more indexes than expected.
            insp = Inspector(meta.bind)
            indexes = insp.get_indexes('users', schema=schema)
            expected_indexes = [
                {'unique': False,
                 'column_names': ['test1', 'test2'],
                 'name': 'users_t_idx'}]
            index_names = [d['name'] for d in indexes]
            for e_index in expected_indexes:
                assert e_index['name'] in index_names
                index = indexes[index_names.index(e_index['name'])]
                for key in e_index:
                    eq_(e_index[key], index[key])

        finally:
            addresses.drop()
            users.drop()

    def test_get_indexes(self):
        self._test_get_indexes()

    @testing.requires.schemas
    def test_get_indexes_with_schema(self):
        self._test_get_indexes(schema='test_schema')

    def _test_get_view_definition(self, schema=None):
        meta = MetaData(testing.db)
        (users, addresses) = createTables(meta, schema)
        meta.create_all()
        createViews(meta.bind, schema)
        view_name1 = 'users_v'
        view_name2 = 'email_addresses_v'
        try:
            insp = Inspector(meta.bind)
            v1 = insp.get_view_definition(view_name1, schema=schema)
            self.assert_(v1)
            v2 = insp.get_view_definition(view_name2, schema=schema)
            self.assert_(v2)
        finally:
            dropViews(meta.bind, schema)
            addresses.drop()
            users.drop()

    def test_get_view_definition(self):
        self._test_get_view_definition()

    @testing.requires.schemas
    def test_get_view_definition_with_schema(self):
        self._test_get_view_definition(schema='test_schema')

    def _test_get_table_oid(self, table_name, schema=None):
        if testing.against('postgresql'):
            meta = MetaData(testing.db)
            (users, addresses) = createTables(meta, schema)
            meta.create_all()
            try:
                insp = create_inspector(meta.bind)
                oid = insp.get_table_oid(table_name, schema)
                self.assert_(isinstance(oid, (int, long)))
            finally:
                addresses.drop()
                users.drop()

    def test_get_table_oid(self):
        self._test_get_table_oid('users')

    @testing.requires.schemas
    def test_get_table_oid_with_schema(self):
        self._test_get_table_oid('users', schema='test_schema')