From 521a574aa6c029649e18f4592f04990ab69c896c Mon Sep 17 00:00:00 2001 From: Evgeny Fadeev Date: Sun, 13 May 2012 18:51:26 -0400 Subject: search for users now works for the user profile --- .../migrations/0123_setup_postgres_user_search.py | 29 ++++++++++++ askbot/models/__init__.py | 20 ++++++++ askbot/models/question.py | 12 +---- askbot/search/mysql.py | 54 ++++++++++++++++++++++ askbot/search/postgresql/__init__.py | 29 ++++++++++++ askbot/skins/default/templates/users.html | 4 +- askbot/views/users.py | 27 ++++++----- 7 files changed, 149 insertions(+), 26 deletions(-) create mode 100644 askbot/migrations/0123_setup_postgres_user_search.py create mode 100644 askbot/search/mysql.py 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 3eca3c0e..bfab105a 100644 --- a/askbot/models/__init__.py +++ b/askbot/models/__init__.py @@ -52,6 +52,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 597a95ae..58325ccb 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/skins/default/templates/users.html b/askbot/skins/default/templates/users.html index 343a3494..bce3f930 100644 --- a/askbot/skins/default/templates/users.html +++ b/askbot/skins/default/templates/users.html @@ -50,8 +50,8 @@
-{% if suser %} -

{% trans %}users matching query {{suser}}:{% endtrans %}

+{% if search_query %} +

{% trans %}users matching query {{search_query}}:{% endtrans %}

{% endif %} {% if not users.object_list %}

{% trans %}Nothing found.{% endtrans %}

diff --git a/askbot/views/users.py b/askbot/views/users.py index ef0aea57..10f643f5 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, -- cgit v1.2.3-1-g7c22