[copr] master: Add fulltext search capability to copr model (8edd8b2)

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

On branch  : master

> ---------------------------------------------------------------
commit 8edd8b29e5da6161a2218ac80a6746597310b27c
Author: Bohuslav Kabrda <bkabrda(a)redhat.com&gt;
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'