[copr] master: Enable fulltext-like search on sqlite, too (92e74af)

bkabrda at fedoraproject.org bkabrda at fedoraproject.org
Fri Feb 1 13:22:40 UTC 2013


Repository : http://git.fedorahosted.org/cgit/copr.git

On branch  : master

>---------------------------------------------------------------

commit 92e74af303736c3857f7044f1f8f36456d155807
Author: Bohuslav Kabrda <bkabrda at redhat.com>
Date:   Fri Feb 1 13:44:54 2013 +0100

    Enable fulltext-like search on sqlite, too
    
    - Introduce a new Query subclass that can do 'fulltext' in both DBs


>---------------------------------------------------------------

 .../versions/3a035889852c_add_copr_fulltext.py     |   25 +++++++++++--
 coprs_frontend/coprs/logic/coprs_logic.py          |    2 +-
 coprs_frontend/coprs/models.py                     |   38 ++++++++++++++++++--
 coprs_frontend/coprs/sql_custom.py                 |   24 ++++++++++++
 coprs_frontend/coprs/sql_types.py                  |   10 -----
 5 files changed, 82 insertions(+), 17 deletions(-)

diff --git a/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py b/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
index c7292ff..8fa6a5e 100644
--- a/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
+++ b/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
@@ -13,11 +13,11 @@ down_revision = '3c3cce7a5fe0'
 from alembic import op
 import sqlalchemy as sa
 
-from coprs import sql_types
+from coprs import sql_custom
 
 def upgrade():
     ### commands auto generated by Alembic - please adjust! ###
-    op.add_column('copr', sa.Column('copr_ts_col', sql_types.Tsvector(), nullable=True))
+    op.add_column('copr', sa.Column('copr_ts_col', sql_custom.Tsvector(), nullable=True))
     op.create_index('copr_ts_idx', 'copr', ['copr_ts_col'], postgresql_using='gin')
 
     session = sa.orm.sessionmaker(bind=op.get_bind())()
@@ -32,7 +32,26 @@ def upgrade():
                     FOR EACH ROW EXECUTE PROCEDURE \
                     tsvector_update_trigger(copr_ts_col, 'pg_catalog.english', name, description, instructions);")
     elif op.get_bind().dialect.name == 'sqlite':
-        pass #TODO
+        op.execute("UPDATE copr \
+                    SET copr_ts_col = coalesce(name, '') || ' ' || \
+                        coalesce(description, '') || ' ' || coalesce(instructions, '')")
+        # two triggers for sqlite...
+        op.execute("CREATE TRIGGER copr_ts_update \
+                    AFTER UPDATE OF name, description, instructions \
+                    ON copr \
+                    FOR EACH ROW \
+                    BEGIN \
+                    UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
+                                                  coalesce(description, '') || ' ' || coalesce(instructions, ''); \
+                    END;")
+        op.execute("CREATE TRIGGER copr_ts_insert \
+                    AFTER INSERT \
+                    ON copr \
+                    FOR EACH ROW \
+                    BEGIN \
+                    UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
+                                                  coalesce(description, '') || ' ' || coalesce(instructions, ''); \
+                    END;")
     ### end Alembic commands ###
 
 
diff --git a/coprs_frontend/coprs/logic/coprs_logic.py b/coprs_frontend/coprs/logic/coprs_logic.py
index ba0266a..36aa51f 100644
--- a/coprs_frontend/coprs/logic/coprs_logic.py
+++ b/coprs_frontend/coprs/logic/coprs_logic.py
@@ -52,7 +52,7 @@ class CoprsLogic(object):
 
     @classmethod
     def get_multiple_fulltext(cls, user, search_string):
-        query = models.Copr.query.filter(models.Copr.copr_ts_col.match(search_string))
+        query = models.Copr.query.fulltext(models.Copr.copr_ts_col, search_string)
         return query
 
     @classmethod
diff --git a/coprs_frontend/coprs/models.py b/coprs_frontend/coprs/models.py
index eef1082..e6b2fb3 100644
--- a/coprs_frontend/coprs/models.py
+++ b/coprs_frontend/coprs/models.py
@@ -6,7 +6,7 @@ from sqlalchemy.ext.associationproxy import association_proxy
 from coprs import constants
 from coprs import db
 from coprs import helpers
-from coprs import sql_types
+from coprs import sql_custom
 
 class Serializer(object):
     def to_dict(self, options = {}):
@@ -108,6 +108,8 @@ class User(db.Model, Serializer):
 
 
 class Copr(db.Model, Serializer):
+    query_class = sql_custom.FullTextQuery
+
     id = db.Column(db.Integer, primary_key = True)
     name = db.Column(db.String(100), nullable = False)
     repos = db.Column(db.Text)
@@ -117,7 +119,7 @@ class Copr(db.Model, Serializer):
     # duplicate information, but speeds up a lot and makes queries simpler
     build_count = db.Column(db.Integer, default = 0)
     # fulltext
-    copr_ts_col = db.Column(sql_types.Tsvector)
+    copr_ts_col = db.Column(sql_custom.Tsvector)
     copr_ts_idx = db.Index('copr_ts_col', postgresql_using='gin')
 
     # relations
@@ -137,7 +139,8 @@ class Copr(db.Model, Serializer):
     def instructions_or_not_filled(self):
         return self.instructions or 'Instructions not filled in by author.'
 
-# fulltext search trigger for copr
+### fulltext search trigger for copr
+# postgres
 sqlalchemy.event.listen(
     Copr.__table__,
     'after_create',
@@ -148,6 +151,35 @@ sqlalchemy.event.listen(
          execute_if(dialect='postgresql')
 )
 
+# sqlite
+sqlalchemy.event.listen(
+    Copr.__table__,
+    'after_create',
+    sqlalchemy.schema.DDL("CREATE TRIGGER copr_ts_update \
+                          AFTER UPDATE OF name, description, instructions \
+                          ON copr \
+                          FOR EACH ROW \
+                          BEGIN \
+                          UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
+                          coalesce(description, '') || ' ' || coalesce(instructions, ''); \
+                          END;").\
+                          execute_if(dialect='sqlite')
+)
+
+sqlalchemy.event.listen(
+    Copr.__table__,
+    'after_create',
+    sqlalchemy.schema.DDL("CREATE TRIGGER copr_ts_insert \
+                          AFTER INSERT \
+                          ON copr \
+                          FOR EACH ROW \
+                          BEGIN \
+                          UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
+                          coalesce(description, '') || ' ' || coalesce(instructions, ''); \
+                          END;").\
+                          execute_if(dialect='sqlite')
+)
+
 class CoprPermission(db.Model, Serializer):
     # 0 = nothing, 1 = asked for, 2 = approved
     # not using enum, as that translates to varchar on some DBs
diff --git a/coprs_frontend/coprs/sql_custom.py b/coprs_frontend/coprs/sql_custom.py
new file mode 100644
index 0000000..056b3f3
--- /dev/null
+++ b/coprs_frontend/coprs/sql_custom.py
@@ -0,0 +1,24 @@
+from sqlalchemy import types
+from sqlalchemy.ext import compiler
+
+from coprs import db
+
+class Tsvector(types.UnicodeText):
+    # TODO: define the custom operator to perform fulltext searches ?
+    pass
+
+ at compiler.compiles(Tsvector, 'postgresql')
+def compile_tsvector(element, compiler, **kw):
+    return 'tsvector'
+
+ at compiler.compiles(Tsvector, 'sqlite')
+def compile_tsvector(element, compiler, **kw):
+    return 'text'
+
+
+class FullTextQuery(db.Query):
+    def fulltext(self, column, search_string):
+        if db.engine.dialect.name == 'postgresql':
+            return self.filter(column.match(search_string))
+        else:
+            return self.filter(column.like('%{0}%'.format(search_string)))
diff --git a/coprs_frontend/coprs/sql_types.py b/coprs_frontend/coprs/sql_types.py
deleted file mode 100644
index ea6376d..0000000
--- a/coprs_frontend/coprs/sql_types.py
+++ /dev/null
@@ -1,10 +0,0 @@
-from sqlalchemy import types
-from sqlalchemy.ext import compiler
-
-class Tsvector(types.UnicodeText):
-    # TODO: define the custom operator to perform fulltext searches ?
-    pass
-
- at compiler.compiles(Tsvector, 'postgresql')
-def compile_tsvector(element, compiler, **kw):
-    return 'tsvector'



More information about the copr-devel mailing list