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