def test_insert_from_select_fn_defaults(self):
        data = self._fixture()

        counter = itertools.count(1)

        def foo(ctx):
            return next(counter)

        table = Table('sometable', self.metadata,
                      Column('x', Integer),
                      Column('foo', Integer, default=foo),
                      Column('y', Integer))

        table.create()

        sel = select([data.c.x, data.c.y])

        ins = table.insert().\
            from_select(["x", "y"], sel)
        testing.db.execute(ins)

        # counter is only called once!
        eq_(
            testing.db.execute(table.select().order_by(table.c.x)).fetchall(),
            [(2, 1, 5), (7, 1, 12)]
        )

    def setup(self):
        meta = MetaData(testing.db)
        global table, GoofyType

        class GoofyType(TypeDecorator):
            impl = String

            def process_bind_param(self, value, dialect):
                if value is None:
                    return None
                return "FOO" + value

            def process_result_value(self, value, dialect):
                if value is None:
                    return None
                return value + "BAR"

        table = Table(
            'tables', meta,
            Column(
                'id', Integer, primary_key=True,
                test_needs_autoincrement=True),
            Column('persons', Integer),
            Column('full', Boolean),
            Column('goofy', GoofyType(50)))
        table.create(checkfirst=True)

    def test_limit_offset_for_update(self):
        metadata = self.metadata
        # oracle can't actually do the ROWNUM thing with FOR UPDATE
        # very well.

        t = Table(
            "t1",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", Integer),
        )
        metadata.create_all()

        t.insert().execute(
            {"id": 1, "data": 1},
            {"id": 2, "data": 7},
            {"id": 3, "data": 12},
            {"id": 4, "data": 15},
            {"id": 5, "data": 32},
        )

        # here, we can't use ORDER BY.
        eq_(
            t.select().with_for_update().limit(2).execute().fetchall(),
            [(1, 1), (2, 7)],
        )

        # here, its impossible.  But we'd prefer it to raise ORA-02014
        # instead of issuing a syntax error.
        assert_raises_message(
            exc.DatabaseError,
            "ORA-02014",
            t.select().with_for_update().limit(2).offset(3).execute,
        )

 def test_nullable_reflection(self):
     t = Table("t", self.metadata, Column("a", Integer, nullable=True), Column("b", Integer, nullable=False))
     t.create()
     eq_(
         dict((col["name"], col["nullable"]) for col in inspect(self.metadata.bind).get_columns("t")),
         {"a": True, "b": False},
     )

    def _test_get_unique_constraints(self, schema=None):
        uniques = sorted(
            [
                {"name": "unique_a", "column_names": ["a"]},
                {"name": "unique_a_b_c", "column_names": ["a", "b", "c"]},
                {"name": "unique_c_a_b", "column_names": ["c", "a", "b"]},
                {"name": "unique_asc_key", "column_names": ["asc", "key"]},
            ],
            key=operator.itemgetter("name"),
        )
        orig_meta = self.metadata
        table = Table(
            "testtbl",
            orig_meta,
            Column("a", sa.String(20)),
            Column("b", sa.String(30)),
            Column("c", sa.Integer),
            # reserved identifiers
            Column("asc", sa.String(30)),
            Column("key", sa.String(30)),
            schema=schema,
        )
        for uc in uniques:
            table.append_constraint(sa.UniqueConstraint(*uc["column_names"], name=uc["name"]))
        orig_meta.create_all()

        inspector = inspect(orig_meta.bind)
        reflected = sorted(inspector.get_unique_constraints("testtbl", schema=schema), key=operator.itemgetter("name"))

        for orig, refl in zip(uniques, reflected):
            # Different dialects handle duplicate index and constraints
            # differently, so ignore this flag
            refl.pop("duplicates_index", None)
            eq_(orig, refl)

    def test_numeric_nan_float(self):
        m = self.metadata
        t1 = Table(
            "t1",
            m,
            Column("intcol", Integer),
            Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)),
        )
        t1.create()
        t1.insert().execute(
            [
                dict(intcol=1, numericcol=float("nan")),
                dict(intcol=2, numericcol=float("-nan")),
            ]
        )

        eq_(
            [
                tuple(str(col) for col in row)
                for row in select([t1.c.numericcol])
                .order_by(t1.c.intcol)
                .execute()
            ],
            [("nan",), ("nan",)],
        )

        eq_(
            [
                tuple(str(col) for col in row)
                for row in testing.db.execute(
                    "select numericcol from t1 order by intcol"
                )
            ],
            [("nan",), ("nan",)],
        )

    def test_long_type(self):
        metadata = self.metadata

        t = Table("t", metadata, Column("data", oracle.LONG))
        metadata.create_all(testing.db)
        testing.db.execute(t.insert(), data="xyz")
        eq_(testing.db.scalar(select([t.c.data])), "xyz")

    def test_column_accessor_shadow(self):
        shadowed = Table(
            'test_shadowed', self.metadata,
            Column('shadow_id', INT, primary_key=True),
            Column('shadow_name', VARCHAR(20)),
            Column('parent', VARCHAR(20)),
            Column('row', VARCHAR(40)),
            Column('_parent', VARCHAR(20)),
            Column('_row', VARCHAR(20)),
        )
        self.metadata.create_all()
        shadowed.insert().execute(
            shadow_id=1, shadow_name='The Shadow', parent='The Light',
            row='Without light there is no shadow',
            _parent='Hidden parent', _row='Hidden row')
        r = shadowed.select(shadowed.c.shadow_id == 1).execute().first()

        eq_(r.shadow_id, 1)
        eq_(r['shadow_id'], 1)
        eq_(r[shadowed.c.shadow_id], 1)

        eq_(r.shadow_name, 'The Shadow')
        eq_(r['shadow_name'], 'The Shadow')
        eq_(r[shadowed.c.shadow_name], 'The Shadow')

        eq_(r.parent, 'The Light')
        eq_(r['parent'], 'The Light')
        eq_(r[shadowed.c.parent], 'The Light')

        eq_(r.row, 'Without light there is no shadow')
        eq_(r['row'], 'Without light there is no shadow')
        eq_(r[shadowed.c.row], 'Without light there is no shadow')

        eq_(r['_parent'], 'Hidden parent')
        eq_(r['_row'], 'Hidden row')

    def test_misordered_lastrow(self):
        metadata = self.metadata

        related = Table(
            'related', metadata,
            Column('id', Integer, primary_key=True),
            mysql_engine='MyISAM'
        )
        t6 = Table(
            "t6", metadata,
            Column(
                'manual_id', Integer, ForeignKey('related.id'),
                primary_key=True),
            Column(
                'auto_id', Integer, primary_key=True,
                test_needs_autoincrement=True),
            mysql_engine='MyISAM'
        )

        metadata.create_all()
        r = related.insert().values(id=12).execute()
        id_ = r.inserted_primary_key[0]
        eq_(id_, 12)

        r = t6.insert().values(manual_id=id_).execute()
        eq_(r.inserted_primary_key, [12, 1])

    def test_column_label_overlap_fallback(self):
        content = Table(
            'content', self.metadata,
            Column('type', String(30)),
        )
        bar = Table(
            'bar', self.metadata,
            Column('content_type', String(30))
        )
        self.metadata.create_all(testing.db)
        testing.db.execute(content.insert().values(type="t1"))

        row = testing.db.execute(content.select(use_labels=True)).first()
        in_(content.c.type, row)
        not_in_(bar.c.content_type, row)
        in_(sql.column('content_type'), row)

        row = testing.db.execute(
            select([content.c.type.label("content_type")])).first()
        in_(content.c.type, row)

        not_in_(bar.c.content_type, row)

        in_(sql.column('content_type'), row)

        row = testing.db.execute(select([func.now().label("content_type")])). \
            first()
        not_in_(content.c.type, row)

        not_in_(bar.c.content_type, row)

        in_(sql.column('content_type'), row)

 def test_reflect_remote_synonyms(self):
     meta = MetaData(testing.db)
     parent = Table(
         "ptable",
         meta,
         autoload=True,
         schema=testing.config.test_schema,
         oracle_resolve_synonyms=True,
     )
     child = Table(
         "ctable",
         meta,
         autoload=True,
         schema=testing.config.test_schema,
         oracle_resolve_synonyms=True,
     )
     self.assert_compile(
         parent.join(child),
         "%(test_schema)s.ptable JOIN "
         "%(test_schema)s.ctable "
         "ON %(test_schema)s.ptable.id = "
         "%(test_schema)s.ctable.parent_id"
         % {"test_schema": testing.config.test_schema},
     )
     select([parent, child]).select_from(
         parent.join(child)
     ).execute().fetchall()

    def test_select_doesnt_pollute_result(self):
        class MyType(TypeDecorator):
            impl = Integer

            def process_result_value(self, value, dialect):
                raise Exception("I have not been selected")

        t1 = Table(
            't1', self.metadata,
            Column('x', MyType())
        )

        t2 = Table(
            't2', self.metadata,
            Column('x', Integer)
        )

        self.metadata.create_all(testing.db)
        with testing.db.connect() as conn:
            conn.execute(t1.insert().values(x=5))

            stmt = t2.insert().values(
                x=select([t1.c.x]).as_scalar()).returning(t2.c.x)

            result = conn.execute(stmt)
            eq_(result.scalar(), 5)

    def test_string_default_none_on_insert(self):
        """Test that without implicit returning, we return None for
        a string server default.

        That is, we don't want to attempt to pre-execute "server_default"
        generically - the user should use a Python side-default for a case
        like this.   Testing that all backends do the same thing here.

        """

        metadata = self.metadata
        t = Table(
            'x', metadata,
            Column(
                'y', String(10), server_default='key_one', primary_key=True),
            Column('data', String(10)),
            implicit_returning=False
        )
        metadata.create_all()
        r = t.insert().execute(data='data')
        eq_(r.inserted_primary_key, [None])
        eq_(
            t.select().execute().fetchall(),
            [('key_one', 'data')]
        )

    def _test_get_unique_constraints(self, schema=None):
        uniques = sorted(
            [
                {'name': 'unique_a', 'column_names': ['a']},
                {'name': 'unique_a_b_c', 'column_names': ['a', 'b', 'c']},
                {'name': 'unique_c_a_b', 'column_names': ['c', 'a', 'b']},
                {'name': 'unique_asc_key', 'column_names': ['asc', 'key']},
            ],
            key=operator.itemgetter('name')
        )
        orig_meta = self.metadata
        table = Table(
            'testtbl', orig_meta,
            Column('a', sa.String(20)),
            Column('b', sa.String(30)),
            Column('c', sa.Integer),
            # reserved identifiers
            Column('asc', sa.String(30)),
            Column('key', sa.String(30)),
            schema=schema
        )
        for uc in uniques:
            table.append_constraint(
                sa.UniqueConstraint(*uc['column_names'], name=uc['name'])
            )
        orig_meta.create_all()

        inspector = inspect(orig_meta.bind)
        reflected = sorted(
            inspector.get_unique_constraints('testtbl', schema=schema),
            key=operator.itemgetter('name')
        )

        for orig, refl in zip(uniques, reflected):
            eq_(orig, refl)

    def test_reflect_nvarchar(self):
        metadata = self.metadata
        Table(
            "tnv",
            metadata,
            Column("nv_data", sqltypes.NVARCHAR(255)),
            Column("c_data", sqltypes.NCHAR(20)),
        )
        metadata.create_all()
        m2 = MetaData(testing.db)
        t2 = Table("tnv", m2, autoload=True)
        assert isinstance(t2.c.nv_data.type, sqltypes.NVARCHAR)
        assert isinstance(t2.c.c_data.type, sqltypes.NCHAR)

        if testing.against("oracle+cx_oracle"):
            assert isinstance(
                t2.c.nv_data.type.dialect_impl(testing.db.dialect),
                cx_oracle._OracleUnicodeStringNCHAR,
            )

            assert isinstance(
                t2.c.c_data.type.dialect_impl(testing.db.dialect),
                cx_oracle._OracleUnicodeStringNCHAR,
            )

        data = u("m’a réveillé.")
        with testing.db.connect() as conn:
            conn.execute(t2.insert(), dict(nv_data=data, c_data=data))
            nv_data, c_data = conn.execute(t2.select()).first()
            eq_(nv_data, data)
            eq_(c_data, data + (" " * 7))  # char is space padded
            assert isinstance(nv_data, util.text_type)
            assert isinstance(c_data, util.text_type)

    def test_default_exec(self):
        metadata = MetaData(testing.db)
        t1 = Table(
            "t1",
            metadata,
            Column("special_col", Integer, Sequence("special_col"), primary_key=True),
            Column("data", String(50)),  # to appease SQLite without DEFAULT VALUES
        )
        metadata.create_all()

        try:
            engine = metadata.bind

            # reset the identifier preparer, so that we can force it to cache
            # a unicode identifier
            engine.dialect.identifier_preparer = engine.dialect.preparer(engine.dialect)
            select([column("special_col")]).select_from(t1).execute().close()
            assert isinstance(engine.dialect.identifier_preparer.format_sequence(Sequence("special_col")), str)

            # now execute, run the sequence.  it should run in u"Special_col.nextid" or similar as
            # a unicode object; cx_oracle asserts that this is None or a String (postgresql lets it pass thru).
            # ensure that executioncontext._exec_default() is encoding.
            t1.insert().execute(data="foo")
        finally:
            metadata.drop_all()

    def _dont_test_numeric_nan_decimal(self):
        m = self.metadata
        t1 = Table('t1', m,
                   Column("intcol", Integer),
                   Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)))
        t1.create()
        t1.insert().execute([
            dict(
                intcol=1,
                numericcol=decimal.Decimal("NaN")
            ),
            dict(
                intcol=2,
                numericcol=decimal.Decimal("-NaN")
            ),
        ])

        eq_(
            select([t1.c.numericcol]).
            order_by(t1.c.intcol).execute().fetchall(),
            [(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )]
        )

        eq_(
            testing.db.execute(
                "select numericcol from t1 order by intcol").fetchall(),
            [(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )]
        )

 def _dont_test_reflect_all_types_schema(self):
     types_table = Table('all_types', MetaData(testing.db),
                         Column('owner', String(30), primary_key=True),
                         Column('type_name', String(30), primary_key=True),
                         autoload=True, oracle_resolve_synonyms=True)
     for row in types_table.select().execute().fetchall():
         [row[k] for k in row.keys()]

    def test_fixed_char(self):
        m = MetaData(testing.db)
        t = Table('t1', m,
                  Column('id', Integer, primary_key=True),
                  Column('data', CHAR(30), nullable=False))

        t.create()
        try:
            t.insert().execute(
                dict(id=1, data="value 1"),
                dict(id=2, data="value 2"),
                dict(id=3, data="value 3")
            )

            eq_(
                t.select().where(t.c.data == 'value 2').execute().fetchall(),
                [(2, 'value 2                       ')]
            )

            m2 = MetaData(testing.db)
            t2 = Table('t1', m2, autoload=True)
            assert type(t2.c.data.type) is CHAR
            eq_(
                t2.select().where(t2.c.data == 'value 2').execute().fetchall(),
                [(2, 'value 2                       ')]
            )

        finally:
            t.drop()

    def test_numeric_infinity_float(self):
        m = self.metadata
        t1 = Table('t1', m,
                   Column("intcol", Integer),
                   Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)))
        t1.create()
        t1.insert().execute([
            dict(
                intcol=1,
                numericcol=float("inf")
            ),
            dict(
                intcol=2,
                numericcol=float("-inf")
            ),
        ])

        eq_(
            select([t1.c.numericcol]).
            order_by(t1.c.intcol).execute().fetchall(),
            [(float('inf'), ), (float('-inf'), )]
        )

        eq_(
            testing.db.execute(
                "select numericcol from t1 order by intcol").fetchall(),
            [(float('inf'), ), (float('-inf'), )]
        )