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)")