Python sqlalchemy.Index() Examples

def test_new_table_added(self):
        m1 = MetaData()
        m2 = MetaData()
        Table(
            "extra",
            m2,
            Column("foo", Integer, index=True),
            Column("bar", Integer),
            Index("newtable_idx", "bar"),
        )

        diffs = self._fixture(m1, m2)

        eq_(diffs[0][0], "add_table")

        eq_(diffs[1][0], "add_index")
        eq_(
            sqla_compat._get_constraint_final_name(
                diffs[1][1], config.db.dialect
            ),
            "ix_extra_foo",
        )

        eq_(diffs[2][0], "add_index")
        eq_(diffs[2][1].name, "newtable_idx") 

def test_render_add_index_func(self):
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("code", String(255)),
        )
        idx = Index("test_lower_code_idx", func.lower(t.c.code))
        op_obj = ops.CreateIndexOp.from_index(idx)

        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index('test_lower_code_idx', 'test', "
            "[sa.text(!U'lower(code)')], unique=False)",
        ) 

def test_render_add_index_batch(self):
        """
        autogenerate.render._add_index
        """
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
        )
        idx = Index("test_active_code_idx", t.c.active, t.c.code)
        op_obj = ops.CreateIndexOp.from_index(idx)
        with self.autogen_context._within_batch():
            eq_ignore_whitespace(
                autogenerate.render_op_text(self.autogen_context, op_obj),
                "batch_op.create_index('test_active_code_idx', "
                "['active', 'code'], unique=False)",
            ) 

def test_render_add_index_pg_where(self):
        autogen_context = self.autogen_context

        m = MetaData()
        t = Table("t", m, Column("x", String), Column("y", String))

        idx = Index(
            "foo_idx", t.c.x, t.c.y, postgresql_where=(t.c.y == "something")
        )

        op_obj = ops.CreateIndexOp.from_index(idx)

        eq_ignore_whitespace(
            autogenerate.render_op_text(autogen_context, op_obj),
            """op.create_index('foo_idx', 't', \
['x', 'y'], unique=False, """
            """postgresql_where=sa.text(!U"y = 'something'"))""",
        ) 

def upgrade():
    op.create_table(
        'allocation',
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('resource_provider_id', sa.Integer(), nullable=False),
        sa.Column('consumer_id', sa.String(36), nullable=False),
        sa.Column('resource_class_id', sa.Integer(), nullable=False),
        sa.Column('used', sa.Integer(), nullable=False),
        sa.Column('is_nested', sa.Integer(), nullable=False),
        sa.Column('blob', zun.db.sqlalchemy.models.JSONEncodedList(),
                  nullable=True),
        sa.Index('allocation_resource_provider_class_used_idx',
                 'resource_provider_id', 'resource_class_id', 'used'),
        sa.Index('allocation_consumer_id_idx', 'consumer_id'),
        sa.Index('allocation_resource_class_id_idx', 'resource_class_id'),
        sa.PrimaryKeyConstraint('id'),
    ) 

def test_indexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("id", INTEGER), Column("number", INTEGER), Column("text", VARCHAR))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))
    simple_items.indexes.add(Index("idx_text_number", simple_items.c.text, simple_items.c.number, unique=True))
    simple_items.indexes.add(Index("idx_text", simple_items.c.text, unique=True))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, Index, Integer, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('id', Integer),
    Column('number', Integer, index=True),
    Column('text', String, unique=True),
    Index('idx_text_number', 'text', 'number', unique=True)
)
"""
    ) 

def test_noconstraints_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noconstraints=True)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer, index=True)
)
"""
    ) 

def test_noindexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noindexes=True)
        == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer),
    CheckConstraint('number > 2')
)
"""
    ) 

def test_render_add_index_schema(self):
        """
        autogenerate.render._add_index using schema
        """
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
            schema="CamelSchema",
        )
        idx = Index("test_active_code_idx", t.c.active, t.c.code)
        op_obj = ops.CreateIndexOp.from_index(idx)
        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index('test_active_code_idx', 'test', "
            "['active', 'code'], unique=False, schema='CamelSchema')",
        ) 

def test_render_add_index_cast(self):
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("code", String(255)),
        )
        idx = Index("test_lower_code_idx", cast(t.c.code, String))
        op_obj = ops.CreateIndexOp.from_index(idx)

        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index('test_lower_code_idx', 'test', "
            "[sa.text(!U'CAST(code AS VARCHAR)')], unique=False)",
        ) 

def test_drop_index(self):
        """
        autogenerate.render._drop_index
        """
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
        )
        idx = Index("test_active_code_idx", t.c.active, t.c.code)
        op_obj = ops.DropIndexOp.from_index(idx)
        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.drop_index('test_active_code_idx', table_name='test')",
        ) 

def test_drop_index_batch(self):
        """
        autogenerate.render._drop_index
        """
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
        )
        idx = Index("test_active_code_idx", t.c.active, t.c.code)
        op_obj = ops.DropIndexOp.from_index(idx)
        with self.autogen_context._within_batch():
            eq_ignore_whitespace(
                autogenerate.render_op_text(self.autogen_context, op_obj),
                "batch_op.drop_index('test_active_code_idx')",
            ) 

def test_drop_index_schema_batch(self):
        """
        autogenerate.render._drop_index using schema
        """
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
            schema="CamelSchema",
        )
        idx = Index("test_active_code_idx", t.c.active, t.c.code)
        op_obj = ops.DropIndexOp.from_index(idx)
        with self.autogen_context._within_batch():
            eq_ignore_whitespace(
                autogenerate.render_op_text(self.autogen_context, op_obj),
                "batch_op.drop_index('test_active_code_idx')",
            ) 

def test_render_add_index_schema(self):
        t = Table(
            "test",
            self.metadata,
            Column("id", Integer, primary_key=True),
            Column("active", Boolean()),
            Column("code", String(255)),
            schema="CamelSchema",
        )
        idx = Index(None, t.c.active, t.c.code)
        op_obj = ops.CreateIndexOp.from_index(idx)
        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index(op.f('ix_ct_CamelSchema_test_active'), 'test', "
            "['active', 'code'], unique=False, schema='CamelSchema')",
        ) 

def test_unique_flag_nothing_changed(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            "unq_idx",
            m1,
            Column("id", Integer, primary_key=True),
            Column("x", String(20)),
            Index("x", "x", unique=True),
        )

        Table(
            "unq_idx",
            m2,
            Column("id", Integer, primary_key=True),
            Column("x", String(20)),
            Index("x", "x", unique=True),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

def upgrade():
    op.create_table(
        'container_actions',
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('action', sa.String(length=255), nullable=True),
        sa.Column('container_uuid', sa.String(length=36), nullable=False),
        sa.Column('request_id', sa.String(length=255), nullable=True),
        sa.Column('user_id', sa.String(length=255), nullable=True),
        sa.Column('project_id', sa.String(length=255), nullable=True),
        sa.Column('start_time', sa.DateTime(), nullable=True),
        sa.Column('finish_time', sa.DateTime(), nullable=True),
        sa.Column('message', sa.String(length=255), nullable=True),
        sa.Index('container_uuid_idx', 'container_uuid'),
        sa.Index('request_id_idx', 'request_id'),
        sa.ForeignKeyConstraint(['container_uuid'], ['container.uuid'], ),
        sa.PrimaryKeyConstraint('id')
    ) 

def test_nothing_changed_ix_w_mixed_case_plain_name(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            "nothing_changed",
            m1,
            Column("id", Integer, primary_key=True),
            Column("x", Integer),
            Index("SomeIndex", "x"),
            mysql_engine="InnoDB",
        )

        Table(
            "nothing_changed",
            m2,
            Column("id", Integer, primary_key=True),
            Column("x", Integer),
            Index("SomeIndex", "x"),
            mysql_engine="InnoDB",
        )
        diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

def test_nothing_changed_index_w_colkeys(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            "nothing_changed",
            m1,
            Column("x", String(20), key="nx"),
            Index("foobar", "nx"),
        )

        Table(
            "nothing_changed",
            m2,
            Column("x", String(20), key="nx"),
            Index("foobar", "nx"),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

def test_new_idx_index_named_as_column(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            "new_idx",
            m1,
            Column("id1", Integer, primary_key=True),
            Column("id2", Integer, primary_key=True),
            Column("x", String(20)),
        )

        idx = Index("x", "x")
        Table(
            "new_idx",
            m2,
            Column("id1", Integer, primary_key=True),
            Column("id2", Integer, primary_key=True),
            Column("x", String(20)),
            idx,
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs, [("add_index", idx)]) 

def test_removed_idx_index_named_as_column(self):
        m1 = MetaData()
        m2 = MetaData()

        idx = Index("x", "x")
        Table(
            "new_idx",
            m1,
            Column("id1", Integer, primary_key=True),
            Column("id2", Integer, primary_key=True),
            Column("x", String(20)),
            idx,
        )

        Table(
            "new_idx",
            m2,
            Column("id1", Integer, primary_key=True),
            Column("id2", Integer, primary_key=True),
            Column("x", String(20)),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs[0][0], "remove_index") 

def test_drop_table_w_indexes(self):
        m1 = MetaData()
        m2 = MetaData()

        t = Table(
            "some_table",
            m1,
            Column("id", Integer, primary_key=True),
            Column("x", String(20)),
            Column("y", String(20)),
        )
        Index("xy_idx", t.c.x, t.c.y)
        Index("y_idx", t.c.y)

        diffs = self._fixture(m1, m2)
        eq_(diffs[0][0], "remove_index")
        eq_(diffs[1][0], "remove_index")
        eq_(diffs[2][0], "remove_table")

        eq_(
            set([diffs[0][1].name, diffs[1][1].name]), set(["xy_idx", "y_idx"])
        ) 

def test_remove_named_unique_index(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            "remove_idx",
            m1,
            Column("x", Integer),
            Index("xidx", "x", unique=True),
        )
        Table("remove_idx", m2, Column("x", Integer))

        diffs = self._fixture(m1, m2)

        if self.reports_unique_constraints:
            diffs = set((cmd, obj.name) for cmd, obj in diffs)
            eq_(diffs, set([("remove_index", "xidx")]))
        else:
            eq_(diffs, []) 

def test_idx_unchanged_schema(self):
        m1 = MetaData()
        m2 = MetaData()
        Table(
            "add_ix",
            m1,
            Column("x", String(50)),
            Index("ix_1", "x"),
            schema="test_schema",
        )
        Table(
            "add_ix",
            m2,
            Column("x", String(50)),
            Index("ix_1", "x"),
            schema="test_schema",
        )

        diffs = self._fixture(m1, m2, include_schemas=True)
        eq_(diffs, []) 

def test_remove_unique_index_not_reported(self):
        m1 = MetaData()
        Table(
            "order",
            m1,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
            Index("oid_ix", "order_id", "user_id", unique=True),
        )
        m2 = MetaData()
        Table(
            "order",
            m2,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

def test_remove_plain_index_is_reported(self):
        m1 = MetaData()
        Table(
            "order",
            m1,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
            Index("oid_ix", "order_id", "user_id"),
        )
        m2 = MetaData()
        Table(
            "order",
            m2,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs[0][0], "remove_index") 

def test_remove_connection_index(self):
        m1 = MetaData()
        m2 = MetaData()

        t1 = Table("t", m1, Column("x", Integer), Column("y", Integer))
        Index("ix1", t1.c.x)
        Index("ix2", t1.c.y)

        Table("t", m2, Column("x", Integer), Column("y", Integer))

        def include_object(object_, name, type_, reflected, compare_to):
            if type_ == "unique_constraint":
                return False
            return not (
                isinstance(object_, Index)
                and type_ == "index"
                and reflected
                and name == "ix1"
            )

        diffs = self._fixture(m1, m2, object_filters=include_object)

        eq_(diffs[0][0], "remove_index")
        eq_(diffs[0][1].name, "ix2")
        eq_(len(diffs), 1) 

def test_add_metadata_index(self):
        m1 = MetaData()
        m2 = MetaData()

        Table("t", m1, Column("x", Integer))

        t2 = Table("t", m2, Column("x", Integer))
        Index("ix1", t2.c.x)
        Index("ix2", t2.c.x)

        def include_object(object_, name, type_, reflected, compare_to):
            return not (
                isinstance(object_, Index)
                and type_ == "index"
                and not reflected
                and name == "ix1"
            )

        diffs = self._fixture(m1, m2, object_filters=include_object)

        eq_(diffs[0][0], "add_index")
        eq_(diffs[0][1].name, "ix2")
        eq_(len(diffs), 1) 

def rename_index(index, name, table=None, engine=None, **kw):
    """Rename an index.

    If Index instance is given,
    table and engine are not used.

    API to :meth:`ChangesetIndex.rename`.

    :param index: Index to be renamed.
    :param name: New name for index.
    :param table: Table to which Index is reffered.
    :param engine: Engine instance.
    :type index: string or Index instance
    :type name: string
    :type table: string or Table instance
    :type engine: obj
    """
    index = _to_index(index, table, engine)
    index.rename(name, **kw) 

def _restore_indexes_on_deleted_columns(engine, table_name, indexes):
    table = get_table(engine, table_name)

    real_indexes = get_indexes(engine, table_name)
    existing_index_names = dict(
        [(index['name'], index['column_names']) for index in real_indexes])

    # NOTE(boris-42): Restore indexes on `deleted` column
    for index in indexes:
        if 'deleted' not in index['column_names']:
            continue
        name = index['name']
        if name in existing_index_names:
            column_names = [table.c[c] for c in existing_index_names[name]]
            old_index = Index(name, *column_names, unique=index["unique"])
            old_index.drop(engine)

        column_names = [table.c[c] for c in index['column_names']]
        new_index = Index(index["name"], *column_names, unique=index["unique"])
        new_index.create(engine) 

def add_index(engine, table_name, index_name, idx_columns):
    """Create an index for given columns.

    :param engine:      sqlalchemy engine
    :param table_name:  name of the table
    :param index_name:  name of the index
    :param idx_columns: tuple with names of columns that will be indexed
    """
    table = get_table(engine, table_name)
    if not index_exists(engine, table_name, index_name):
        index = Index(
            index_name, *[getattr(table.c, col) for col in idx_columns]
        )
        index.create()
    else:
        raise ValueError("Index '%s' already exists!" % index_name)