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_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_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_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_create_index_simple(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", String(255)))
idx = Index("test_idx1", tbl.c.data)
self.assert_compile(schema.CreateIndex(idx),
"CREATE INDEX test_idx1 ON testtbl (data)")
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_create_index_with_arbitrary_column_element(self):
from sqlalchemy.ext.compiler import compiles
class _textual_index_element(sql.ColumnElement):
"""alembic's wrapper"""
__visit_name__ = "_textual_idx_element"
def __init__(self, table, text):
self.table = table
self.text = text
@compiles(_textual_index_element)
def _render_textual_index_column(element, compiler, **kw):
return compiler.process(element.text, **kw)
m = MetaData()
tbl = Table("testtbl", m, Column("data", String(255)))
idx = Index(
"test_idx1",
_textual_index_element(tbl, text("created_at desc")),
_table=tbl,
)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE INDEX test_idx1 ON testtbl (created_at desc)",
)
def test_create_index_simple(self):
m = MetaData()
tbl = Table('testtbl', m, Column('data', String(255)))
idx = Index('test_idx1', tbl.c.data)
self.assert_compile(schema.CreateIndex(idx),
'CREATE INDEX test_idx1 ON testtbl (data)')
def test_index_against_text_separate(self):
metadata = MetaData()
idx = Index('y', text("some_function(q)"))
t = Table('x', metadata, Column('q', String(50)))
t.append_constraint(idx)
self.assert_compile(schema.CreateIndex(idx),
"CREATE INDEX y ON x (some_function(q))")
def test_index_extra_include_2(self):
metadata = MetaData()
tbl = Table('test', metadata, Column('x', Integer),
Column('y', Integer), Column('z', Integer))
idx = Index("foo", tbl.c.x, mssql_include=[tbl.c.y])
self.assert_compile(schema.CreateIndex(idx),
"CREATE INDEX foo ON test (x) INCLUDE (y)")
def test_index_ordering(self):
metadata = MetaData()
tbl = Table('test', metadata, Column('x', Integer),
Column('y', Integer), Column('z', Integer))
idx = Index("foo", tbl.c.x.desc(), "y")
self.assert_compile(schema.CreateIndex(idx),
"CREATE INDEX foo ON test (x DESC, y)")
def test_create_index_literals(self):
m = MetaData()
tbl = Table('testtbl', m, Column('data', Integer))
idx1 = Index('test_idx1', tbl.c.data + 5)
self.assert_compile(schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl ((data + 5))")
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_alt_schema(self):
m = MetaData()
t1 = Table("foo", m, Column("x", Integer), schema="alt_schema")
self.assert_compile(
schema.CreateIndex(Index("bar", t1.c.x)),
"CREATE INDEX alt_schema.bar ON alt_schema.foo (x)",
)
def test_create_index_with_length_quoted(self):
m = MetaData()
tbl = Table('testtbl', m, Column('some quoted data',
String(255), key='s'))
idx1 = Index('test_idx1', tbl.c.s, mysql_length=10)
self.assert_compile(schema.CreateIndex(idx1),
'CREATE INDEX test_idx1 ON testtbl (`some quoted data`(10))')
def test_index_where(self):
metadata = MetaData()
tbl = Table("test", metadata, Column("data", Integer))
idx = Index("test_idx_data_1", tbl.c.data, mssql_where=tbl.c.data > 1)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE INDEX test_idx_data_1 ON test (data) WHERE data > 1",
)
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)")
def test_index_clustering(self):
metadata = MetaData()
tbl = Table('test', metadata,
Column('id', Integer))
idx = Index("foo", tbl.c.id, mssql_clustered=True)
self.assert_compile(schema.CreateIndex(idx),
"CREATE CLUSTERED INDEX foo ON test (id)"
)
def test_functional_index(self):
metadata = MetaData()
x = Table('x', metadata,
Column('q', String(50))
)
idx = Index('y', func.lower(x.c.q))
self.assert_compile(
schema.CreateIndex(idx),
"CREATE INDEX y ON x (lower(q))"
)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE INDEX y ON x (lower(q))",
dialect=testing.db.dialect
)
def test_create_index_expr_gets_parens(self):
m = MetaData()
tbl = Table('testtbl', m, Column('x', Integer), Column('y', Integer))
idx1 = Index('test_idx1', 5 / (tbl.c.x + tbl.c.y))
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl ((5 / (x + y)))")
def test_create_index_expr_func_unary(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", Integer))
idx1 = Index("test_idx1", -tbl.c.data)
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl ((-data))",
)
def test_create_index_with_text(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", String(255)))
idx = Index("test_idx1", text("created_at desc"), _table=tbl)
self.assert_compile(
schema.CreateIndex(idx),
"CREATE INDEX test_idx1 ON testtbl (created_at desc)",
)
def test_table_idx_explicit_nonclustered(self):
metadata = MetaData()
tbl = Table('test', metadata, Column('x', Integer,
autoincrement=False),
Column('y', Integer, autoincrement=False))
idx = Index("myidx", tbl.c.x, tbl.c.y, mssql_clustered=False)
self.assert_compile(schema.CreateIndex(idx),
"CREATE NONCLUSTERED INDEX myidx ON test (x, y)")
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 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 test_create_index_with_length_quoted(self):
m = MetaData()
tbl = Table("testtbl", m,
Column("some quoted data", String(255), key="s"))
idx1 = Index("test_idx1", tbl.c.s, mysql_length=10)
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl (`some quoted data`(10))",
)
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_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_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_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))",
)
