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_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_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_with_length(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", String(255)))
idx1 = Index("test_idx1", tbl.c.data, mysql_length=10)
idx2 = Index("test_idx2", tbl.c.data, mysql_length=5)
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl (data(10))",
)
self.assert_compile(
schema.CreateIndex(idx2),
"CREATE INDEX test_idx2 ON testtbl (data(5))",
)
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_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_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_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_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_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_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_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_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 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_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_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_composite_index_with_length_quoted_3085_workaround(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_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_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)")
