[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