Repository :
http://git.fedorahosted.org/cgit/copr.git
On branch : master
---------------------------------------------------------------
commit 8edd8b29e5da6161a2218ac80a6746597310b27c
Author: Bohuslav Kabrda <bkabrda(a)redhat.com>
Date: Fri Feb 1 12:24:16 2013 +0100
Add fulltext search capability to copr model
- TODO: we will also need to search user model for name
- TODO: make it work on sqlite for development
---------------------------------------------------------------
.../versions/3a035889852c_add_copr_fulltext.py | 44 ++++++++++++++++++++
coprs_frontend/coprs/models.py | 16 +++++++
coprs_frontend/coprs/sql_types.py | 10 ++++
3 files changed, 70 insertions(+), 0 deletions(-)
diff --git a/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
b/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
new file mode 100644
index 0000000..ac0c71a
--- /dev/null
+++ b/coprs_frontend/alembic/versions/3a035889852c_add_copr_fulltext.py
@@ -0,0 +1,44 @@
+"""add_copr_fulltext
+
+Revision ID: 3a035889852c
+Revises: 3c3cce7a5fe0
+Create Date: 2013-02-01 10:06:37.034495
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '3a035889852c'
+down_revision = '3c3cce7a5fe0'
+
+from alembic import op
+import sqlalchemy as sa
+
+from coprs import sql_types
+
+def upgrade():
+ ### commands auto generated by Alembic - please adjust! ###
+ op.add_column('copr', sa.Column('copr_ts_col', sql_types.Tsvector(),
nullable=True))
+ op.create_index('copr_ts_idx', 'copr', ['copr_ts_col'],
postgresql_using='gin')
+
+ session = sa.orm.sessionmaker(bind=op.get_bind())()
+ metadata = sa.MetaData()
+ op.execute("UPDATE copr \
+ SET copr_ts_col = to_tsvector('pg_catalog.english',
coalesce(name, '') || ' ' || \
+ coalesce(description, '') || ' ' ||
coalesce(instructions, ''))")
+ if op.get_bind().dialect.name == 'postgresql':
+ # no need to coalesce here, the trigger doesn't need it
+ op.execute("CREATE TRIGGER copr_ts_update BEFORE INSERT OR UPDATE \
+ ON copr \
+ 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
+ ### end Alembic commands ###
+
+
+def downgrade():
+ ### commands auto generated by Alembic - please adjust! ###
+ op.drop_column('copr', 'copr_ts_col')
+ if op.get_bind().dialect.name == 'postgresql':
+ op.execute("DROP TRIGGER copr_ts_update ON copr")
+ ### end Alembic commands ###
diff --git a/coprs_frontend/coprs/models.py b/coprs_frontend/coprs/models.py
index cfa6857..eef1082 100644
--- a/coprs_frontend/coprs/models.py
+++ b/coprs_frontend/coprs/models.py
@@ -1,10 +1,12 @@
import datetime
+import sqlalchemy
from sqlalchemy.ext.associationproxy import association_proxy
from coprs import constants
from coprs import db
from coprs import helpers
+from coprs import sql_types
class Serializer(object):
def to_dict(self, options = {}):
@@ -114,6 +116,9 @@ class Copr(db.Model, Serializer):
instructions = db.Column(db.Text)
# 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_idx = db.Index('copr_ts_col', postgresql_using='gin')
# relations
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'))
@@ -132,6 +137,17 @@ 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
+sqlalchemy.event.listen(
+ Copr.__table__,
+ 'after_create',
+ sqlalchemy.schema.DDL("CREATE TRIGGER copr_ts_update BEFORE INSERT OR UPDATE \
+ ON copr \
+ FOR EACH ROW EXECUTE PROCEDURE \
+ tsvector_update_trigger(copr_ts_col, 'pg_catalog.english', name,
description, instructions);").\
+ execute_if(dialect='postgresql')
+)
+
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_types.py b/coprs_frontend/coprs/sql_types.py
new file mode 100644
index 0000000..ea6376d
--- /dev/null
+++ b/coprs_frontend/coprs/sql_types.py
@@ -0,0 +1,10 @@
+from sqlalchemy import types
+from sqlalchemy.ext import compiler
+
+class Tsvector(types.UnicodeText):
+ # TODO: define the custom operator to perform fulltext searches ?
+ pass
+
+(a)compiler.compiles(Tsvector, 'postgresql')
+def compile_tsvector(element, compiler, **kw):
+ return 'tsvector'