Merge branch 'tmp' into user-groups

8 files changed, 238 insertions, 26 deletions

diff --git a/askbot/migrations/0123_setup_postgres_user_search.py b/askbot/migrations/0123_setup_postgres_user_search.py
new file mode 100644
index 00000000..152fbde4
--- /dev/null
+++ b/askbot/migrations/0123_setup_postgres_user_search.py
@@ -0,0 +1,29 @@
+# encoding: utf-8
+import askbot
+from askbot.search import postgresql
+import os
+from south.v2 import DataMigration
+
+class Migration(DataMigration):
+    """this migration is the same as 22 and 106
+    just ran again to update the postgres search setup
+    """
+
+    def forwards(self, orm):
+        "Write your forwards methods here."
+
+        db_engine_name = askbot.get_database_engine_name()
+        if 'postgresql_psycopg2' in db_engine_name:
+            script_path = os.path.join(
+                                askbot.get_install_directory(),
+                                'search',
+                                'postgresql',
+                                'user_profile_search_051312.plsql'
+                            )
+            postgresql.setup_full_text_search(script_path)
+
+    def backwards(self, orm):
+        "Write your backwards methods here."
+        pass
+
+    models = {}#we don't need orm for this migration
diff --git a/askbot/models/__init__.py b/askbot/models/__init__.py
index 4931125b..967880be 100644
--- a/askbot/models/__init__.py
+++ b/askbot/models/__init__.py
@@ -53,6 +53,26 @@ def get_admins_and_moderators():
         models.Q(is_superuser=True) | models.Q(status='m')
     )
 
+def get_users_by_text_query(search_query):
+    """Runs text search in user names and profile.
+    For postgres, search also runs against user group names.
+    """
+    import askbot
+    if 'postgresql_psycopg2' in askbot.get_database_engine_name():
+        from askbot.search import postgresql
+        return postgresql.run_full_text_search(User.objects.all(), search_query)
+    else:
+        return User.objects.filter(
+            models.Q(username__icontains=search_query) |
+            models.Q(about__icontains=search_query)
+        )
+    #if askbot.get_database_engine_name().endswith('mysql') \
+    #    and mysql.supports_full_text_search():
+    #    return User.objects.filter(
+    #        models.Q(username__search = search_query) |
+    #        models.Q(about__search = search_query)
+    #    )
+
 User.add_to_class(
             'status',
             models.CharField(
diff --git a/askbot/models/question.py b/askbot/models/question.py
index 7d1c3758..a18e719b 100644
--- a/askbot/models/question.py
+++ b/askbot/models/question.py
@@ -142,16 +142,8 @@ class ThreadManager(models.Manager):
                 models.Q(posts__deleted=False, posts__text__search = search_query)
             )
         elif 'postgresql_psycopg2' in askbot.get_database_engine_name():
-            rank_clause = "ts_rank(askbot_thread.text_search_vector, plainto_tsquery(%s))"
-            search_query = '&'.join(search_query.split())
-            extra_params = (search_query,)
-            extra_kwargs = {
-                'select': {'relevance': rank_clause},
-                'where': ['askbot_thread.text_search_vector @@ plainto_tsquery(%s)'],
-                'params': extra_params,
-                'select_params': extra_params,
-            }
-            return qs.extra(**extra_kwargs)
+            from askbot.search import postgresql
+            return postgresql.run_full_text_search(qs, search_query)
         else:
             return qs.filter(
                 models.Q(title__icontains=search_query) |
diff --git a/askbot/search/mysql.py b/askbot/search/mysql.py
new file mode 100644
index 00000000..df86070d
--- /dev/null
+++ b/askbot/search/mysql.py
@@ -0,0 +1,54 @@
+from django.db import connection
+
+SUPPORTS_FTS = None
+HINT_TABLE = None
+NO_FTS_WARNING = """
+!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+!!                                                  !!
+!!  WARNING: Your database engine does not support  !!
+!!  full text search. Please switch to PostgresQL   !!
+!!  or select MyISAM engine for MySQL               !!
+!!                                                  !!
+!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+"""
+
+def supports_full_text_search(hint_table = None):
+    """True if the database engine is MyISAM
+    hint_table - is the table that we look into to determine
+    whether database supports FTS or not.
+    """
+    global SUPPORTS_FTS
+    global HINT_TABLE
+    if SUPPORTS_FTS is None:
+        cursor = connection.cursor()
+        if hint_table:
+            table_name = hint_table
+            HINT_TABLE = hint_table
+        else:
+            from askbot.models import Post
+            table_name = Post._meta.db_table
+        cursor.execute("SHOW CREATE TABLE %s" % table_name)
+        data = cursor.fetchone()
+        if 'ENGINE=MyISAM' in data[1]:
+            SUPPORTS_FTS = True
+        else:
+            SUPPORTS_FTS = False
+    return SUPPORTS_FTS
+
+                question_index_sql = get_create_full_text_index_sql(
+                                                index_name,
+                                                table_namee,
+                                                ('title','text','tagnames',)
+                                            )
+def get_create_full_text_index_sql(index_name, table_name, column_list):
+    cursor = connection.cursor()
+    column_sql = '(%s)' % ','.join(column_list)
+    sql = 'CREATE FULLTEXT INDEX %s on %s %s' % (index_name, table_name, column_sql)
+    cursor.execute(question_index_sql)
+    return sql
+            else:
+                print NO_FTS_WARNING
+
+def get_drop_index_sql(index_name, table_name):
+    return 'ALTER TABLE %s DROP INDEX %s' % (table_name, index_name)
+
diff --git a/askbot/search/postgresql/__init__.py b/askbot/search/postgresql/__init__.py
index a802a5eb..5b893129 100644
--- a/askbot/search/postgresql/__init__.py
+++ b/askbot/search/postgresql/__init__.py
@@ -19,3 +19,32 @@ def setup_full_text_search(script_path):
         cursor.execute(fts_init_query)
     finally:
         cursor.close()
+
+def run_full_text_search(query_set, query_text):
+    """runs full text search against the query set and
+    the search text. All words in the query text are
+    added to the search with the & operator - i.e.
+    the more terms in search, the narrower it is.
+
+    It is also assumed that we ar searching in the same
+    table as the query set was built against, also
+    it is assumed that the table has text search vector
+    stored in the column called `text_search_vector`.
+    """
+    table_name = query_set.model._meta.db_table
+
+    rank_clause = 'ts_rank(' + table_name + \
+        '.text_search_vector, plainto_tsquery(%s))'
+
+    where_clause = table_name + '.text_search_vector @@ plainto_tsquery(%s)'
+
+    search_query = '&'.join(query_text.split())#apply "AND" operator
+    extra_params = (search_query,)
+    extra_kwargs = {
+        'select': {'relevance': rank_clause},
+        'where': [where_clause,],
+        'params': extra_params,
+        'select_params': extra_params,
+    }
+
+    return query_set.extra(**extra_kwargs)
diff --git a/askbot/search/postgresql/user_profile_search_051312.plsql b/askbot/search/postgresql/user_profile_search_051312.plsql
new file mode 100644
index 00000000..99e3121d
--- /dev/null
+++ b/askbot/search/postgresql/user_profile_search_051312.plsql
@@ -0,0 +1,89 @@
+/* 
+Script depends on functions defined for general askbot full text search.
+to_tsvector(), add_tsvector_column()
+
+calculates text search vector for the user profile
+the searched fields are: 
+1) user name
+2) user profile
+3) group names - for groups to which user belongs
+*/
+CREATE OR REPLACE FUNCTION get_auth_user_tsv(user_id integer)
+RETURNS tsvector AS
+$$
+DECLARE
+    group_query text;
+    user_query text;
+    onerow record;
+    tsv tsvector;
+BEGIN
+    group_query = 
+        'SELECT user_group.name as group_name ' ||
+        'FROM tag AS user_group ' ||
+        'INNER JOIN askbot_groupmembership AS gm ' ||
+        'ON gm.user_id= ' || user_id || ' AND gm.group_id=user_group.id';
+
+    tsv = to_tsvector('');
+    FOR onerow in EXECUTE group_query LOOP
+        tsv = tsv || to_tsvector(onerow.group_name);
+    END LOOP;
+
+    user_query = 'SELECT username, about FROM auth_user WHERE id=' || user_id;
+    FOR onerow in EXECUTE user_query LOOP
+        tsv = tsv || to_tsvector(onerow.username) || to_tsvector(onerow.about);
+    END LOOP;
+    RETURN tsv;
+END;
+$$ LANGUAGE plpgsql;
+
+/* create tsvector columns in the content tables */
+SELECT add_tsvector_column('text_search_vector', 'auth_user');
+
+/* populate tsvectors with data */
+UPDATE auth_user SET text_search_vector = get_auth_user_tsv(id);
+
+/* one trigger per table for tsv updates */
+
+/* set up auth_user triggers */
+CREATE OR REPLACE FUNCTION auth_user_tsv_update_handler()
+RETURNS trigger AS
+$$
+BEGIN
+    new.text_search_vector = get_auth_user_tsv(new.id);
+    RETURN new;
+END;
+$$ LANGUAGE plpgsql;
+DROP TRIGGER IF EXISTS auth_user_tsv_update_trigger ON auth_user;
+
+CREATE TRIGGER auth_user_tsv_update_trigger
+BEFORE INSERT OR UPDATE ON auth_user 
+FOR EACH ROW EXECUTE PROCEDURE auth_user_tsv_update_handler();
+
+/* group membership trigger */
+CREATE OR REPLACE FUNCTION group_membership_tsv_update_handler()
+RETURNS trigger AS
+$$
+DECLARE
+    tsv tsvector;
+    user_query text;
+BEGIN
+    user_query = 'UPDATE auth_user SET username=username WHERE ' ||
+        'id=' || new.user_id;
+    /* just trigger the tsv update on user */
+    EXECUTE user_query;
+    RETURN new;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS group_membership_tsv_update_trigger
+ON askbot_groupmembership;
+
+CREATE TRIGGER group_membership_tsv_update_trigger
+AFTER INSERT OR DELETE
+ON askbot_groupmembership
+FOR EACH ROW EXECUTE PROCEDURE group_membership_tsv_update_handler();
+
+DROP INDEX IF EXISTS auth_user_search_idx;
+
+CREATE INDEX auth_user_search_idx ON auth_user
+USING gin(text_search_vector);
diff --git a/askbot/skins/default/templates/users.html b/askbot/skins/default/templates/users.html
index b926b428..a6346fc3 100644
--- a/askbot/skins/default/templates/users.html
+++ b/askbot/skins/default/templates/users.html
@@ -50,8 +50,8 @@
     </div>
 </div>
 <div class="clean"></div>
-{% if suser %}
-    <p>{% trans %}users matching query {{suser}}:{% endtrans %}</p>
+{% if search_query %}
+    <p>{% trans %}users matching query {{search_query}}:{% endtrans %}</p>
 {% endif %}
 {% if not users.object_list %}
     <p><span>{% trans %}Nothing found.{% endtrans %}</span></p>
diff --git a/askbot/views/users.py b/askbot/views/users.py
index 6c1743ca..a5860b14 100644
--- a/askbot/views/users.py
+++ b/askbot/views/users.py
@@ -57,7 +57,6 @@ def owner_or_moderator_required(f):
 
 def users(request, by_group = False, group_id = None, group_slug = None):
     """Users view, including listing of users by group"""
-    users = models.User.objects.all()
     group = None
     group_email_moderation_enabled = False
     user_can_join_group = False
@@ -80,11 +79,15 @@ def users(request, by_group = False, group_id = None, group_slug = None):
                 except models.Tag.DoesNotExist:
                     raise Http404
                 if group_slug == slugify(group.name):
-                    users = models.User.objects.filter(
+                    group_users = models.User.objects.filter(
                         group_memberships__group__id = group_id
                     )
                     if request.user.is_authenticated():
-                        user_is_group_member = bool(users.filter(id = request.user.id).count())
+                        user_is_group_member = bool(
+                                                    group_users.filter(
+                                                        id = request.user.id
+                                                    ).count()
+                                                )
                 else:
                     group_page_url = reverse(
                                         'users_by_group',
@@ -102,13 +105,13 @@ def users(request, by_group = False, group_id = None, group_slug = None):
     if askbot_settings.KARMA_MODE == 'private' and sortby == 'reputation':
         sortby = 'newest'
 
-    suser = request.REQUEST.get('query',  "")
     try:
         page = int(request.GET.get('page', '1'))
     except ValueError:
         page = 1
 
-    if suser == "":
+    search_query = request.REQUEST.get('query',  "")
+    if search_query == "":
         if sortby == "newest":
             order_by_parameter = '-date_joined'
         elif sortby == "last":
@@ -120,21 +123,18 @@ def users(request, by_group = False, group_id = None, group_slug = None):
             order_by_parameter = '-reputation'
 
         objects_list = Paginator(
-                            users.order_by(order_by_parameter),
+                            models.User.objects.order_by(order_by_parameter),
                             const.USERS_PAGE_SIZE
                         )
         base_url = request.path + '?sort=%s&' % sortby
     else:
         sortby = "reputation"
+        matching_users = models.get_users_by_text_query(search_query)
         objects_list = Paginator(
-                            users.filter(
-                                username__icontains = suser
-                            ).order_by(
-                                '-reputation'
-                            ),
+                            matching_users.order_by('-reputation'),
                             const.USERS_PAGE_SIZE
                         )
-        base_url = request.path + '?name=%s&sort=%s&' % (suser, sortby)
+        base_url = request.path + '?name=%s&sort=%s&' % (search_query, sortby)
 
     try:
         users_page = objects_list.page(page)
@@ -157,8 +157,7 @@ def users(request, by_group = False, group_id = None, group_slug = None):
         'page_class': 'users-page',
         'users' : users_page,
         'group': group,
-        'suser' : suser,
-        'keywords' : suser,
+        'search_query' : search_query,
         'tab_id' : sortby,
         'paginator_context' : paginator_context,
         'group_email_moderation_enabled': group_email_moderation_enabled,