Repository : http://git.fedorahosted.org/cgit/copr.git
On branch : master
commit 92e74af303736c3857f7044f1f8f36456d155807 Author: Bohuslav Kabrda bkabrda@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 + +@compiler.compiles(Tsvector, 'postgresql') +def compile_tsvector(element, compiler, **kw): + return 'tsvector' + +@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 - -@compiler.compiles(Tsvector, 'postgresql') -def compile_tsvector(element, compiler, **kw): - return 'tsvector'
copr-devel@lists.fedorahosted.org
-
bkabrda＠fedoraproject.org