| 1 | from pydal import DAL, Field |
|---|
| 2 | from ._compat import unittest |
|---|
| 3 | from ._adapt import DEFAULT_URI, IS_POSTGRESQL, drop |
|---|
| 4 | |
|---|
| 5 | |
|---|
| 6 | class TestIndexesBasic(unittest.TestCase): |
|---|
| 7 | def testRun(self): |
|---|
| 8 | db = DAL(DEFAULT_URI, check_reserved=["all"]) |
|---|
| 9 | db.define_table("tt", Field("aa")) |
|---|
| 10 | rv = db.tt.create_index("idx_aa", db.tt.aa) |
|---|
| 11 | self.assertTrue(rv) |
|---|
| 12 | rv = db.tt.drop_index("idx_aa") |
|---|
| 13 | self.assertTrue(rv) |
|---|
| 14 | with self.assertRaises(Exception): |
|---|
| 15 | db.tt.drop_index("idx_aa") |
|---|
| 16 | db.rollback() |
|---|
| 17 | drop(db.tt) |
|---|
| 18 | |
|---|
| 19 | |
|---|
| 20 | @unittest.skipUnless(IS_POSTGRESQL, "Expressions in indexes are not supported") |
|---|
| 21 | class TestIndexesExpressions(unittest.TestCase): |
|---|
| 22 | def testRun(self): |
|---|
| 23 | db = DAL(DEFAULT_URI, check_reserved=["all"], entity_quoting=True) |
|---|
| 24 | db.define_table("tt", Field("aa"), Field("bb", "datetime")) |
|---|
| 25 | sql = db._adapter.dialect.create_index( |
|---|
| 26 | "idx_aa_and_bb", db.tt, [db.tt.aa, db.tt.bb.coalesce(None)] |
|---|
| 27 | ) |
|---|
| 28 | with db._adapter.index_expander(): |
|---|
| 29 | coalesce_sql = str(db.tt.bb.coalesce(None)) |
|---|
| 30 | expected_sql = "CREATE INDEX %s ON %s (%s,%s);" % ( |
|---|
| 31 | db._adapter.dialect.quote("idx_aa_and_bb"), |
|---|
| 32 | db.tt.sql_shortref, |
|---|
| 33 | db.tt.aa.sqlsafe_name, |
|---|
| 34 | coalesce_sql, |
|---|
| 35 | ) |
|---|
| 36 | self.assertEqual(sql, expected_sql) |
|---|
| 37 | rv = db.tt.create_index("idx_aa_and_bb", db.tt.aa, db.tt.bb.coalesce(None)) |
|---|
| 38 | self.assertTrue(rv) |
|---|
| 39 | rv = db.tt.drop_index("idx_aa_and_bb") |
|---|
| 40 | self.assertTrue(rv) |
|---|
| 41 | drop(db.tt) |
|---|
| 42 | |
|---|
| 43 | |
|---|
| 44 | @unittest.skipUnless(IS_POSTGRESQL, "Partial indexes are not supported") |
|---|
| 45 | class TestIndexesWhere(unittest.TestCase): |
|---|
| 46 | def testRun(self): |
|---|
| 47 | db = DAL(DEFAULT_URI, check_reserved=["all"], entity_quoting=True) |
|---|
| 48 | db.define_table("tt", Field("aa"), Field("bb", "boolean")) |
|---|
| 49 | sql = db._adapter.dialect.create_index( |
|---|
| 50 | "idx_aa_f", db.tt, [db.tt.aa], where=str(db.tt.bb == False) |
|---|
| 51 | ) |
|---|
| 52 | self.assertEqual( |
|---|
| 53 | sql, 'CREATE INDEX "idx_aa_f" ON "tt" ("aa") WHERE ("tt"."bb" = \'F\');' |
|---|
| 54 | ) |
|---|
| 55 | rv = db.tt.create_index("idx_aa_f", db.tt.aa, where=(db.tt.bb == False)) |
|---|
| 56 | self.assertTrue(rv) |
|---|
| 57 | rv = db.tt.drop_index("idx_aa_f") |
|---|
| 58 | self.assertTrue(rv) |
|---|
| 59 | drop(db.tt) |
|---|
