Python sqlalchemy.schema.CreateIndex() Examples

def test_create_index_concurrently(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", Integer))

        idx1 = Index("test_idx1", tbl.c.data, postgresql_concurrently=True)
        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)",
        )

        dialect_8_1 = postgresql.dialect()
        dialect_8_1._supports_create_index_concurrently = False
        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (data)",
            dialect=dialect_8_1,
        ) 

def test_create_index_bitmap_compress(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", Integer))
        idx1 = Index("idx1", tbl.c.data, oracle_compress=True)
        idx2 = Index("idx2", tbl.c.data, oracle_compress=1)
        idx3 = Index("idx3", tbl.c.data, oracle_bitmap=True)

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX idx1 ON testtbl (data) COMPRESS",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX idx2 ON testtbl (data) COMPRESS 1",
        )
        self.assert_compile(
            schema.CreateIndex(idx3),
            "CREATE BITMAP INDEX idx3 ON testtbl (data)",
        ) 

def test_create_index_with_multiple_options(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String))

        idx1 = Index(
            "test_idx1",
            tbl.c.data,
            postgresql_using="btree",
            postgresql_tablespace="atablespace",
            postgresql_with={"fillfactor": 60},
            postgresql_where=and_(tbl.c.data > 5, tbl.c.data < 10),
        )

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl "
            "USING btree (data) "
            "WITH (fillfactor = 60) "
            "TABLESPACE atablespace "
            "WHERE data > 5 AND data < 10",
            dialect=postgresql.dialect(),
        ) 

def test_create_index_with_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String))

        idx1 = Index("test_idx1", tbl.c.data)
        idx2 = Index("test_idx2", tbl.c.data, postgresql_using="btree")
        idx3 = Index("test_idx3", tbl.c.data, postgresql_using="hash")

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl " "(data)",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl " "USING btree (data)",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx3),
            "CREATE INDEX test_idx3 ON testtbl " "USING hash (data)",
            dialect=postgresql.dialect(),
        ) 

def test_colliding_col_label_from_index_flag_no_conv(self):
        t1 = self._colliding_name_fixture({"ck": "foo"}, {"index": True})

        idx = list(t1.indexes)[0]

        # this behavior needs to fail, as of #4911 since we are testing it,
        # ensure it raises a CompileError.  In #4289 we may want to revisit
        # this in some way, most likely specifically to Postgresql only.
        assert_raises_message(
            exc.CompileError,
            "CREATE INDEX requires that the index have a name",
            CreateIndex(idx).compile,
        )

        assert_raises_message(
            exc.CompileError,
            "DROP INDEX requires that the index have a name",
            DropIndex(idx).compile,
        ) 

def print_create_table(tables):
    app.config.from_object('config.default')
    database.init_app(app)

    engine = database.session.get_bind()

    for class_name in tables:
        cls = get_class(class_name)

        for c in cls.__table__.columns:
            if not isinstance(c.type, Enum):
                continue
            t = c.type
            sql = str(CreateEnumType(t).compile(engine))
            click.echo(sql.strip() + ';')

        for index in cls.__table__.indexes:
            sql = str(CreateIndex(index).compile(engine))
            click.echo(sql.strip() + ';')

        sql = str(CreateTable(cls.__table__).compile(engine))
        click.echo(sql.strip() + ';') 

def test_create_index_with_parser(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx = Index(
            "test_idx1",
            tbl.c.data,
            mysql_length=10,
            mysql_prefix="FULLTEXT",
            mysql_with_parser="ngram",
        )

        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE FULLTEXT INDEX test_idx1 "
            "ON testtbl (data(10)) WITH PARSER ngram",
        ) 

def create_table(self, table):
        table.dispatch.before_create(
            table, self.connection, checkfirst=False, _ddl_runner=self
        )
        self._exec(schema.CreateTable(table))
        table.dispatch.after_create(
            table, self.connection, checkfirst=False, _ddl_runner=self
        )
        for index in table.indexes:
            self._exec(schema.CreateIndex(index))

        with_comment = (
            sqla_compat._dialect_supports_comments(self.dialect)
            and not self.dialect.inline_comments
        )
        comment = sqla_compat._comment_attribute(table)
        if comment and with_comment:
            self.create_table_comment(table)

        for column in table.columns:
            comment = sqla_compat._comment_attribute(column)
            if comment and with_comment:
                self.create_column_comment(column) 

def test_create_composite_index_with_length(self):
        m = MetaData()
        tbl = Table(
            "testtbl", m, Column("a", String(255)), Column("b", String(255))
        )

        idx1 = Index(
            "test_idx1", tbl.c.a, tbl.c.b, mysql_length={"a": 10, "b": 20}
        )
        idx2 = Index("test_idx2", tbl.c.a, tbl.c.b, mysql_length={"a": 15})
        idx3 = Index("test_idx3", tbl.c.a, tbl.c.b, mysql_length=30)

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (a(10), b(20))",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (a(15), b)",
        )
        self.assert_compile(
            schema.CreateIndex(idx3),
            "CREATE INDEX test_idx3 ON testtbl (a(30), b(30))",
        ) 

def test_create_composite_index_with_length_quoted(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("some Quoted a", String(255), key="a"),
            Column("some Quoted b", String(255), key="b"),
        )
        idx1 = Index(
            "test_idx1",
            tbl.c.a,
            tbl.c.b,
            mysql_length={"some Quoted a": 10, "some Quoted b": 20},
        )

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl "
            "(`some Quoted a`(10), `some Quoted b`(20))",
        ) 

def test_create_index_with_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx1 = Index("test_idx1", tbl.c.data, mysql_using="btree")
        idx2 = Index("test_idx2", tbl.c.data, mysql_using="hash")

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl (data) USING btree",
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (data) USING hash",
        ) 

def test_create_index_expr(self):
        m = MetaData()
        t1 = Table("foo", m, Column("x", Integer))
        self.assert_compile(
            schema.CreateIndex(Index("bar", t1.c.x > 5)),
            "CREATE INDEX bar ON foo (x > 5)",
        ) 

def test_index_against_text_inline(self):
        metadata = MetaData()
        idx = Index("y", text("some_function(q)"))
        Table("x", metadata, Column("q", String(50)), idx)

        self.assert_compile(
            schema.CreateIndex(idx), "CREATE INDEX y ON x (some_function(q))"
        ) 

def test_too_long_index(self):
        exp = "ix_zyrenian_zyme_zyzzogeton_zyzzogeton_zyrenian_zyme_zyz_5cd2"
        tname = "zyrenian_zyme_zyzzogeton_zyzzogeton"
        cname = "zyrenian_zyme_zyzzogeton_zo"

        t1 = Table(tname, MetaData(), Column(cname, Integer, index=True))
        ix1 = list(t1.indexes)[0]

        self.assert_compile(
            schema.CreateIndex(ix1),
            "CREATE INDEX %s " "ON %s (%s)" % (exp, tname, cname),
        ) 

def _assert_create(self, elements, generator, argument):
        self._assert_ddl(
            (schema.CreateTable, schema.CreateSequence, schema.CreateIndex),
            elements,
            generator,
            argument,
        ) 

def test_quote_flag_propagate_index(self):
        m = MetaData()
        t = Table("t", m, Column("x", Integer, quote=True))
        idx = Index("foo", t.c.x)
        self.assert_compile(
            schema.CreateIndex(idx), 'CREATE INDEX foo ON t ("x")'
        ) 

def test_create_index_plain(self):
        t = Table("t", MetaData(), Column("x", Integer))
        i = Index("xyz", t.c.x)
        self.assert_compile(schema.CreateIndex(i), "CREATE INDEX xyz ON t (x)") 

def test_create_index_schema(self):
        t = Table("t", MetaData(), Column("x", Integer), schema="foo")
        i = Index("xyz", t.c.x)
        self.assert_compile(
            schema.CreateIndex(i), "CREATE INDEX xyz ON foo.t (x)"
        ) 

def test_too_long_index_name(self):
        dialect = testing.db.dialect.__class__()

        for max_ident, max_index in [(22, None), (256, 22)]:
            dialect.max_identifier_length = max_ident
            dialect.max_index_name_length = max_index

            for tname, cname, exp in [
                ("sometable", "this_name_is_too_long", "ix_sometable_t_09aa"),
                ("sometable", "this_name_alsois_long", "ix_sometable_t_3cf1"),
            ]:

                t1 = Table(
                    tname, MetaData(), Column(cname, Integer, index=True)
                )
                ix1 = list(t1.indexes)[0]

                self.assert_compile(
                    schema.CreateIndex(ix1),
                    "CREATE INDEX %s " "ON %s (%s)" % (exp, tname, cname),
                    dialect=dialect,
                )

        dialect.max_identifier_length = 22
        dialect.max_index_name_length = None

        t1 = Table("t", MetaData(), Column("c", Integer))
        assert_raises(
            exc.IdentifierError,
            schema.CreateIndex(
                Index(
                    "this_other_name_is_too_long_for_what_were_doing", t1.c.c
                )
            ).compile,
            dialect=dialect,
        ) 

def test_functional_index_w_string_cols_combo(self):
        metadata = MetaData()
        x = Table(
            "x",
            metadata,
            Column("q", String(50)),
            Column("p", Integer),
            Column("z", Integer),
        )

        for idx, ddl in [
            (
                Index("y", func.lower(x.c.q), "p", x.c.z),
                "CREATE INDEX y ON x (lower(q), p, z)",
            ),
            (
                Index("y", "p", func.lower(x.c.q), "z"),
                "CREATE INDEX y ON x (p, lower(q), z)",
            ),
            (
                Index("y", "p", "z", func.lower(x.c.q)),
                "CREATE INDEX y ON x (p, z, lower(q))",
            ),
            (
                Index("y", func.foo("foob"), x.c.p, "z"),
                "CREATE INDEX y ON x (foo('foob'), p, z)",
            ),
            (
                Index("y", x.c.p, func.foo("foob"), "z"),
                "CREATE INDEX y ON x (p, foo('foob'), z)",
            ),
            (
                Index("y", func.foo("foob"), "p", "z"),
                "CREATE INDEX y ON x (foo('foob'), p, z)",
            ),
        ]:
            x.append_constraint(idx)
            self.assert_compile(schema.CreateIndex(idx), ddl) 

def test_create_index_with_prefix(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String(255)))
        idx = Index(
            "test_idx1", tbl.c.data, mysql_length=10, mysql_prefix="FULLTEXT"
        )

        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE FULLTEXT INDEX test_idx1 " "ON testtbl (data(10))",
        ) 

def test_index_declaration_inline(self):
        metadata = MetaData()

        t1 = Table(
            "t1",
            metadata,
            Column("x", Integer),
            Column("y", Integer),
            Index("foo", "x", "y"),
        )
        self.assert_compile(
            schema.CreateIndex(list(t1.indexes)[0]),
            "CREATE INDEX foo ON t1 (x, y)",
        ) 

def test_render_index_sql_literal(self):
        t, t2 = self._constraint_create_fixture()

        constraint = Index("name", t.c.a + 5)

        self.assert_compile(
            schema.CreateIndex(constraint), "CREATE INDEX name ON tbl (a + 5)"
        ) 

def test_raise_expr_no_column(self):
        idx = Index("foo", func.lower(5))

        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
            dialect=testing.db.dialect,
        )
        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
        ) 

def test_no_warning_w_no_columns(self):
        idx = Index(name="foo")

        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
            dialect=testing.db.dialect,
        )
        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
        ) 

def test_colliding_col_label_from_index_obj(self):
        t1 = self._colliding_name_fixture({"ix": "ix_%(column_0_label)s"}, {})

        idx = Index(None, t1.c.id)
        is_(idx, list(t1.indexes)[0])
        eq_(idx.name, "ix_foo_id")
        self.assert_compile(
            CreateIndex(idx), "CREATE INDEX ix_foo_id ON foo (id)"
        ) 

def test_ix_allcols_truncation(self):
        u1 = self._fixture(
            naming_convention={"ix": "ix_%(table_name)s_%(column_0N_name)s"}
        )
        ix = Index(None, u1.c.data, u1.c.data2, u1.c.data3)
        dialect = default.DefaultDialect()
        dialect.max_identifier_length = 15
        self.assert_compile(
            schema.CreateIndex(ix),
            "CREATE INDEX ix_user_2de9 ON " '"user" (data, "Data2", "Data3")',
            dialect=dialect,
        ) 

def test_expression_index(self):
        m = MetaData(naming_convention={"ix": "ix_%(column_0_label)s"})
        t = Table("t", m, Column("q", Integer), Column("p", Integer))
        ix = Index(None, t.c.q + 5)
        t.append_constraint(ix)

        # huh.  pretty cool
        self.assert_compile(
            CreateIndex(ix), "CREATE INDEX ix_t_q ON t (q + 5)"
        ) 

def create_table(self, table):
        table.dispatch.before_create(table, self.connection,
                                     checkfirst=False,
                                     _ddl_runner=self)
        self._exec(schema.CreateTable(table))
        table.dispatch.after_create(table, self.connection,
                                    checkfirst=False,
                                    _ddl_runner=self)
        for index in table.indexes:
            self._exec(schema.CreateIndex(index)) 

def create_index(self, index):
        self._exec(schema.CreateIndex(index))