From f899157ca9f6f5104a358420eba8f1b9019ece6f Mon Sep 17 00:00:00 2001 From: Michael Trier Date: Sun, 13 Jul 2008 04:45:37 +0000 Subject: Added new basic match() operator that performs a full-text search. Supported on PostgreSQL, SQLite, MySQL, MS-SQL, and Oracle backends. --- test/dialect/mysql.py | 71 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 71 insertions(+) (limited to 'test/dialect/mysql.py') diff --git a/test/dialect/mysql.py b/test/dialect/mysql.py index f5719ecb1..b796a56eb 100644 --- a/test/dialect/mysql.py +++ b/test/dialect/mysql.py @@ -968,6 +968,77 @@ class ExecutionTest(TestBase): assert ('mysql', 'charset') in cx.info +class MatchTest(TestBase, AssertsCompiledSQL): + __only_on__ = 'mysql' + + def setUpAll(self): + global metadata, cattable, matchtable + metadata = MetaData(testing.db) + + cattable = Table('cattable', metadata, + Column('id', Integer, primary_key=True), + Column('description', String(50)), + ) + matchtable = Table('matchtable', metadata, + Column('id', Integer, primary_key=True), + Column('title', String(200)), + Column('category_id', Integer, ForeignKey('cattable.id')), + ) + metadata.create_all() + + cattable.insert().execute([ + {'id': 1, 'description': 'Python'}, + {'id': 2, 'description': 'Ruby'}, + ]) + matchtable.insert().execute([ + {'id': 1, 'title': 'Agile Web Development with Rails', 'category_id': 2}, + {'id': 2, 'title': 'Dive Into Python', 'category_id': 1}, + {'id': 3, 'title': 'Programming Matz''s Ruby', 'category_id': 2}, + {'id': 4, 'title': 'The Definitive Guide to Django', 'category_id': 1}, + {'id': 5, 'title': 'Python in a Nutshell', 'category_id': 1} + ]) + + def tearDownAll(self): + metadata.drop_all() + + def test_expression(self): + self.assert_compile(matchtable.c.title.match('somstr'), "MATCH (matchtable.title) AGAINST (%s IN BOOLEAN MODE)") + + def test_simple_match(self): + results = matchtable.select().where(matchtable.c.title.match('python')).order_by(matchtable.c.id).execute().fetchall() + self.assertEquals([2, 5], [r.id for r in results]) + + def test_simple_match_with_apostrophe(self): + results = matchtable.select().where(matchtable.c.title.match('"Matz''s"')).execute().fetchall() + self.assertEquals([3], [r.id for r in results]) + + def test_or_match(self): + results1 = matchtable.select().where(or_(matchtable.c.title.match('nutshell'), + matchtable.c.title.match('ruby')) + ).order_by(matchtable.c.id).execute().fetchall() + self.assertEquals([3, 5], [r.id for r in results1]) + results2 = matchtable.select().where(matchtable.c.title.match('nutshell ruby'), + ).order_by(matchtable.c.id).execute().fetchall() + self.assertEquals([3, 5], [r.id for r in results2]) + + + def test_and_match(self): + results1 = matchtable.select().where(and_(matchtable.c.title.match('python'), + matchtable.c.title.match('nutshell')) + ).execute().fetchall() + self.assertEquals([5], [r.id for r in results1]) + results2 = matchtable.select().where(matchtable.c.title.match('+python +nutshell'), + ).execute().fetchall() + self.assertEquals([5], [r.id for r in results2]) + + def test_match_across_joins(self): + results = matchtable.select().where(and_(cattable.c.id==matchtable.c.category_id, + or_(cattable.c.description.match('Ruby'), + matchtable.c.title.match('nutshell'))) + ).order_by(matchtable.c.id).execute().fetchall() + self.assertEquals([1, 3, 5], [r.id for r in results]) + + def colspec(c): return testing.db.dialect.schemagenerator(testing.db.dialect, testing.db, None, None).get_column_specification(c) -- cgit v1.2.1