Added new basic match() operator that performs a full-text search. Supported on PostgreSQL, SQLite, MySQL, MS-SQL, and Oracle backends.

4 files changed, 333 insertions, 0 deletions

diff --git a/test/dialect/mssql.py b/test/dialect/mssql.py
index c3ce338df..085864415 100755
--- a/test/dialect/mssql.py
+++ b/test/dialect/mssql.py
@@ -251,6 +251,111 @@ class GenerativeQueryTest(TestBase):
         assert list(query[:10]) == orig[:10]
         assert list(query[:10]) == orig[:10]
 
+def full_text_search_missing():
+    """Test if full text search is not implemented and return False if 
+    it is and True otherwise."""
+
+    try:
+        connection = testing.db.connect()
+        connection.execute("CREATE FULLTEXT CATALOG Catalog AS DEFAULT")
+        return False
+    except:
+        return True
+    finally:
+        connection.close()
+
+class MatchTest(TestBase, AssertsCompiledSQL):
+    __only_on__ = 'mssql'
+    __skip_if__ = (full_text_search_missing, )
+
+    def setUpAll(self):
+        global metadata, cattable, matchtable
+        metadata = MetaData(testing.db)
+        
+        cattable = Table('cattable', metadata,
+            Column('id', Integer),
+            Column('description', String(50)),
+            PrimaryKeyConstraint('id', name='PK_cattable'),
+        )
+        matchtable = Table('matchtable', metadata,
+            Column('id', Integer),
+            Column('title', String(200)),
+            Column('category_id', Integer, ForeignKey('cattable.id')),
+            PrimaryKeyConstraint('id', name='PK_matchtable'),
+        )
+        DDL("""CREATE FULLTEXT INDEX 
+                       ON cattable (description) 
+                       KEY INDEX PK_cattable"""
+                   ).execute_at('after-create', matchtable)
+        DDL("""CREATE FULLTEXT INDEX 
+                       ON matchtable (title) 
+                       KEY INDEX PK_matchtable"""
+                   ).execute_at('after-create', matchtable)
+        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}
+        ])
+        DDL("WAITFOR DELAY '00:00:05'").execute(bind=engines.testing_engine())
+
+    def tearDownAll(self):
+        metadata.drop_all()
+        connection = testing.db.connect()
+        connection.execute("DROP FULLTEXT CATALOG Catalog")
+        connection.close()
+
+    def test_expression(self):
+        self.assert_compile(matchtable.c.title.match('somstr'), "CONTAINS (matchtable.title, ?)")
+
+    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_simple_prefix_match(self):
+        results = matchtable.select().where(matchtable.c.title.match('"nut*"')).execute().fetchall()
+        self.assertEquals([5], [r.id for r in results])
+
+    def test_simple_inflectional_match(self):
+        results = matchtable.select().where(matchtable.c.title.match('FORMSOF(INFLECTIONAL, "dives")')).execute().fetchall()
+        self.assertEquals([2], [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 OR 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 AND 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])
+
 
 if __name__ == "__main__":
     testenv.main()
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)
diff --git a/test/dialect/postgres.py b/test/dialect/postgres.py
index 3a565abab..10fbf51f2 100644
--- a/test/dialect/postgres.py
+++ b/test/dialect/postgres.py
@@ -821,6 +821,81 @@ class ServerSideCursorsTest(TestBase, AssertsExecutionResults):
         finally:
             test_table.drop(checkfirst=True)
 
+class MatchTest(TestBase, AssertsCompiledSQL):
+    __only_on__ = 'postgres'
+    __excluded_on__ = (('postgres', '<', (8, 3, 0)),)
+
+    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'), "matchtable.title @@ to_tsquery(%(title_1)s)")
+
+    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_simple_derivative_match(self):
+        results = matchtable.select().where(matchtable.c.title.match('nutshells')).execute().fetchall()
+        self.assertEquals([5], [r.id for r in results])
+
+    def test_or_match(self):
+        results1 = matchtable.select().where(or_(matchtable.c.title.match('nutshells'), 
+                                                 matchtable.c.title.match('rubies'))
+                                            ).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('nutshells | rubies'), 
+                                            ).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('nutshells'))
+                                            ).execute().fetchall()
+        self.assertEquals([5], [r.id for r in results1])
+        results2 = matchtable.select().where(matchtable.c.title.match('python & nutshells'), 
+                                            ).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('nutshells')))
+                                           ).order_by(matchtable.c.id).execute().fetchall()
+        self.assertEquals([1, 3, 5], [r.id for r in results])
+
 
 if __name__ == "__main__":
     testenv.main()
diff --git a/test/dialect/sqlite.py b/test/dialect/sqlite.py
index 70d6923ca..f1e476add 100644
--- a/test/dialect/sqlite.py
+++ b/test/dialect/sqlite.py
@@ -295,6 +295,88 @@ class InsertTest(TestBase, AssertsExecutionResults):
         finally:
             tbl.drop()
 
+def full_text_search_missing():
+    """Test if full text search is not implemented and return False if 
+    it is and True otherwise."""
+
+    try:
+        testing.db.execute("CREATE VIRTUAL TABLE t using FTS3;")
+        testing.db.execute("DROP TABLE t;")
+        return False
+    except:
+        return True
+
+class MatchTest(TestBase, AssertsCompiledSQL):
+    __only_on__ = 'sqlite'
+    __skip_if__ = (full_text_search_missing, )
+
+    def setUpAll(self):
+        global metadata, cattable, matchtable
+        metadata = MetaData(testing.db)
+        
+        testing.db.execute("""
+        CREATE VIRTUAL TABLE cattable using FTS3 (
+            id INTEGER NOT NULL, 
+            description VARCHAR(50), 
+            PRIMARY KEY (id)
+        )
+        """)
+        cattable = Table('cattable', metadata, autoload=True)
+        
+        testing.db.execute("""
+        CREATE VIRTUAL TABLE matchtable using FTS3 (
+            id INTEGER NOT NULL, 
+            title VARCHAR(200),
+            category_id INTEGER NOT NULL, 
+            PRIMARY KEY (id)
+        )
+        """)
+        matchtable = Table('matchtable', metadata, autoload=True)
+        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'), "matchtable.title MATCH ?")
+
+    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_prefix_match(self):
+        results = matchtable.select().where(matchtable.c.title.match('nut*')).execute().fetchall()
+        self.assertEquals([5], [r.id for r in results])
+
+    def test_or_match(self):
+        results2 = matchtable.select().where(matchtable.c.title.match('nutshell OR ruby'), 
+                                            ).order_by(matchtable.c.id).execute().fetchall()
+        self.assertEquals([3, 5], [r.id for r in results2])
+        
+
+    def test_and_match(self):
+        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, 
+                                            cattable.c.description.match('Ruby'))
+                                           ).order_by(matchtable.c.id).execute().fetchall()
+        self.assertEquals([1, 3], [r.id for r in results])
+
 
 if __name__ == "__main__":
     testenv.main()