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_index_create_camelcase(self):
"""test that mixed-case index identifiers are legal"""
metadata = self.metadata
employees = Table(
"companyEmployees",
metadata,
Column("id", Integer, primary_key=True),
Column("firstName", String(30)),
Column("lastName", String(30)),
Column("emailAddress", String(30)),
)
Index("employeeNameIndex", employees.c.lastName, employees.c.firstName)
Index("employeeEmailIndex", employees.c.emailAddress, unique=True)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
RegexSQL("^CREATE TABLE"),
AllOf(
CompiledSQL(
'CREATE INDEX "employeeNameIndex" ON '
'"companyEmployees" ("lastName", "firstName")',
[],
),
CompiledSQL(
'CREATE UNIQUE INDEX "employeeEmailIndex" ON '
'"companyEmployees" ("emailAddress")',
[],
),
),
)
def test_no_pk(self):
metadata = self.metadata
Table(
"sometable",
metadata,
Column("id_a", Unicode(255)),
Column("id_b", Unicode(255)),
Index("pk_idx_1", "id_a", "id_b", unique=True),
Index("pk_idx_2", "id_b", "id_a", unique=True),
)
metadata.create_all()
insp = inspect(testing.db)
eq_(
insp.get_indexes("sometable"),
[
{
"name": "pk_idx_1",
"column_names": ["id_a", "id_b"],
"dialect_options": {},
"unique": True,
},
{
"name": "pk_idx_2",
"column_names": ["id_b", "id_a"],
"dialect_options": {},
"unique": True,
},
],
)
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_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_reflect_fulltext_comment(self):
mt = Table(
"mytable",
self.metadata,
Column("id", Integer, primary_key=True),
Column("textdata", String(50)),
mysql_engine="InnoDB",
)
Index(
"textdata_ix",
mt.c.textdata,
mysql_prefix="FULLTEXT",
mysql_with_parser="ngram",
)
self.metadata.create_all(testing.db)
mt = Table("mytable", MetaData(), autoload_with=testing.db)
idx = list(mt.indexes)[0]
eq_(idx.name, "textdata_ix")
eq_(idx.dialect_options["mysql"]["prefix"], "FULLTEXT")
eq_(idx.dialect_options["mysql"]["with_parser"], "ngram")
self.assert_compile(
CreateIndex(idx),
"CREATE FULLTEXT INDEX textdata_ix ON mytable "
"(textdata) WITH PARSER ngram",
)
def test_drop_index_w_schema(self):
m = MetaData()
t1 = Table("foo", m, Column("x", Integer), schema="bar")
self.assert_compile(
schema.DropIndex(Index("idx_foo", t1.c.x)),
"DROP INDEX idx_foo ON bar.foo",
)
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_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_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_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_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_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_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_index_create_inline(self):
# test an index create using index=True, unique=True
metadata = self.metadata
events = Table(
"events",
metadata,
Column("id", Integer, primary_key=True),
Column("name", String(30), index=True, unique=True),
Column("location", String(30), index=True),
Column("sport", String(30)),
Column("announcer", String(30)),
Column("winner", String(30)),
)
Index("sport_announcer",
events.c.sport,
events.c.announcer,
unique=True)
Index("idx_winners", events.c.winner)
eq_(
set(ix.name for ix in events.indexes),
set([
"ix_events_name",
"ix_events_location",
"sport_announcer",
"idx_winners",
]),
)
self.assert_sql_execution(
testing.db,
lambda: events.create(testing.db),
RegexSQL("^CREATE TABLE events"),
AllOf(
CompiledSQL("CREATE UNIQUE INDEX ix_events_name ON events "
"(name)"),
CompiledSQL("CREATE INDEX ix_events_location ON events "
"(location)"),
CompiledSQL("CREATE UNIQUE INDEX sport_announcer ON events "
"(sport, announcer)"),
CompiledSQL("CREATE INDEX idx_winners ON events (winner)"),
),
)
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_indexes_cols(self):
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer), Column("y", Integer))
Index("foo", t1.c.x, t1.c.y)
metadata.create_all()
m2 = MetaData()
t2 = Table("t", m2, autoload=True, autoload_with=testing.db)
eq_(set(list(t2.indexes)[0].columns), set([t2.c["x"], t2.c.y]))
def test_include_indexes_resembling_pk(self, explicit_pk):
metadata = self.metadata
t = Table(
"sometable",
metadata,
Column("id_a", Unicode(255), primary_key=True),
Column("id_b", Unicode(255), primary_key=True),
Column("group", Unicode(255), primary_key=True),
Column("col", Unicode(255)),
# Oracle won't let you do this unless the indexes have
# the columns in different order
Index("pk_idx_1", "id_b", "id_a", "group", unique=True),
Index("pk_idx_2", "id_b", "group", "id_a", unique=True),
)
if explicit_pk:
t.append_constraint(
PrimaryKeyConstraint("id_a",
"id_b",
"group",
name="some_primary_key"))
metadata.create_all()
insp = inspect(testing.db)
eq_(
insp.get_indexes("sometable"),
[
{
"name": "pk_idx_1",
"column_names": ["id_b", "id_a", "group"],
"dialect_options": {},
"unique": True,
},
{
"name": "pk_idx_2",
"column_names": ["id_b", "group", "id_a"],
"dialect_options": {},
"unique": True,
},
],
)
def test_index_create(self):
metadata = self.metadata
employees = Table(
"employees",
metadata,
Column("id", Integer, primary_key=True),
Column("first_name", String(30)),
Column("last_name", String(30)),
Column("email_address", String(30)),
)
i = Index(
"employee_name_index",
employees.c.last_name,
employees.c.first_name,
)
assert i in employees.indexes
i2 = Index("employee_email_index",
employees.c.email_address,
unique=True)
assert i2 in employees.indexes
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
RegexSQL("^CREATE TABLE"),
AllOf(
CompiledSQL(
"CREATE INDEX employee_name_index ON "
"employees (last_name, first_name)",
[],
),
CompiledSQL(
"CREATE UNIQUE INDEX employee_email_index ON "
"employees (email_address)",
[],
),
),
)
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_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_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_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_name_that_is_greater_than_16_chars(self):
metadata = self.metadata
Table(
"ABCDEFGHIJKLMNOPQRSTUVWXYZ",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Index("foo_idx", "foo"),
)
metadata.create_all()
t = Table("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
MetaData(),
autoload_with=testing.db)
eq_(t.name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
def test_index_functional_create(self):
metadata = self.metadata
t = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String(50)),
)
Index("myindex", t.c.data.desc())
self.assert_sql_execution(
testing.db,
lambda: t.create(testing.db),
CompiledSQL("CREATE TABLE sometable (id INTEGER NOT NULL, "
"data VARCHAR(50), PRIMARY KEY (id))"),
CompiledSQL("CREATE INDEX myindex ON sometable (data DESC)"),
)
def test_non_column_index(self):
m1 = self.metadata
t1 = Table("add_ix",
m1,
Column("x", String(50)),
mysql_engine="InnoDB")
Index("foo_idx", t1.c.x.desc())
m1.create_all()
insp = inspect(testing.db)
eq_(
insp.get_indexes("add_ix"),
[{
"name": "foo_idx",
"column_names": ["x"],
"unique": False
}],
)
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))",
)
