from pydal import DAL, Field
from ._compat import unittest
from ._adapt import DEFAULT_URI, IS_POSTGRESQL, drop


class TestIndexesBasic(unittest.TestCase):
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'])
        db.define_table('tt', Field('aa'))
        rv = db.tt.create_index('idx_aa', db.tt.aa)
        self.assertTrue(rv)
        rv = db.tt.drop_index('idx_aa')
        self.assertTrue(rv)
        with self.assertRaises(Exception):
            db.tt.drop_index('idx_aa')
        db.rollback()
        drop(db.tt)


@unittest.skipUnless(IS_POSTGRESQL, 'Expressions in indexes are not supported')
class TestIndexesExpressions(unittest.TestCase):
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'], entity_quoting=True)
        db.define_table('tt', Field('aa'), Field('bb', 'datetime'))
        sql = db._adapter.dialect.create_index(
            'idx_aa_and_bb', db.tt, [db.tt.aa, db.tt.bb.coalesce(None)]
        )
        with db._adapter.index_expander():
            coalesce_sql = str(db.tt.bb.coalesce(None))
        expected_sql = 'CREATE INDEX %s ON %s (%s,%s);' % (
            db._adapter.dialect.quote('idx_aa_and_bb'), db.tt.sql_shortref,
            db.tt.aa.sqlsafe_name, coalesce_sql)
        self.assertEqual(sql, expected_sql)
        rv = db.tt.create_index(
            'idx_aa_and_bb', db.tt.aa, db.tt.bb.coalesce(None))
        self.assertTrue(rv)
        rv = db.tt.drop_index('idx_aa_and_bb')
        self.assertTrue(rv)
        drop(db.tt)


@unittest.skipUnless(IS_POSTGRESQL, 'Partial indexes are not supported')
class TestIndexesWhere(unittest.TestCase):
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'], entity_quoting=True)
        db.define_table('tt', Field('aa'), Field('bb', 'boolean'))
        sql = db._adapter.dialect.create_index(
            'idx_aa_f', db.tt, [db.tt.aa], where=str(db.tt.bb == False)
        )
        self.assertEqual(
            sql,
            'CREATE INDEX "idx_aa_f" ON "tt" ("aa") WHERE ("tt"."bb" = \'F\');'
        )
        rv = db.tt.create_index(
            'idx_aa_f', db.tt.aa, where=(db.tt.bb == False))
        self.assertTrue(rv)
        rv = db.tt.drop_index('idx_aa_f')
        self.assertTrue(rv)
        drop(db.tt)