From f8bdcf43ec75e04c30cb26e6c35e1a9f3cf262be Mon Sep 17 00:00:00 2001 From: hernani Date: Fri, 16 Apr 2010 22:14:09 +0000 Subject: [PATCH] improved text search git-svn-id: http://svn.osqa.net/svnroot/osqa/trunk@41 0cfe37f9-358a-4d5e-be75-b63607b5c754 --- forum/skins/default/templates/questions.html | 14 ----- forum/views/readers.py | 17 +++--- forum_modules/pgfulltext/handlers.py | 4 +- forum_modules/pgfulltext/management.py | 36 ++++++------- forum_modules/pgfulltext/pg_fts_install.sql | 56 +++++++++++++++----- 5 files changed, 71 insertions(+), 56 deletions(-) diff --git a/forum/skins/default/templates/questions.html b/forum/skins/default/templates/questions.html index 31644e7..ddf4f69 100644 --- a/forum/skins/default/templates/questions.html +++ b/forum/skins/default/templates/questions.html @@ -5,20 +5,6 @@ {% load extra_tags %} {% block title %}{% spaceless %}{% trans "Questions" %}{% endspaceless %}{% endblock %} -{% block forejs %} - -{% endblock %} {% block content %}
{% question_list_title %} diff --git a/forum/views/readers.py b/forum/views/readers.py index 15ac86b..b260343 100644 --- a/forum/views/readers.py +++ b/forum/views/readers.py @@ -83,14 +83,15 @@ def question_list(request, initial, list_description=_('questions'), sort=None, questions = questions.filter( ~Q(tags__id__in=request.user.marked_tags.filter(user_selections__reason='bad'))) - if sort is None: - sort = request.utils.sort_method('latest') - else: - request.utils.set_sort_method(sort) - - view_dic = {"latest":"-added_at", "active":"-last_activity_at", "hottest":"-answer_count", "mostvoted":"-score" } + if sort is not False: + if sort is None: + sort = request.utils.sort_method('latest') + else: + request.utils.set_sort_method(sort) + + view_dic = {"latest":"-added_at", "active":"-last_activity_at", "hottest":"-answer_count", "mostvoted":"-score" } - questions=questions.order_by(view_dic.get(sort, '-added_at')) + questions=questions.order_by(view_dic.get(sort, '-added_at')) return { "questions" : questions, @@ -128,7 +129,7 @@ def question_search(request, keywords): initial = question_search(keywords) return question_list(request, initial, _("questions matching '%(keywords)s'") % {'keywords': keywords}, - base_path="%s?t=question&q=%s" % (reverse('search'), django_urlquote(keywords))) + base_path="%s?t=question&q=%s" % (reverse('search'), django_urlquote(keywords)), sort=False) def tags(request):#view showing a listing of available tags - plain list diff --git a/forum_modules/pgfulltext/handlers.py b/forum_modules/pgfulltext/handlers.py index 45ab2e7..4cac36f 100644 --- a/forum_modules/pgfulltext/handlers.py +++ b/forum_modules/pgfulltext/handlers.py @@ -3,9 +3,9 @@ from forum.models import Question def question_search(keywords): return Question.objects.extra( select={ - 'ranking': "ts_rank_cd(tsv, plainto_tsquery(%s), 32)", + 'ranking': "node_ranking(id, %s)", }, - where=["tsv @@ plainto_tsquery(%s)"], + where=["node_ranking(id, %s) > 0"], params=[keywords], select_params=[keywords] ).order_by('-ranking') \ No newline at end of file diff --git a/forum_modules/pgfulltext/management.py b/forum_modules/pgfulltext/management.py index 487580f..c2abfaf 100644 --- a/forum_modules/pgfulltext/management.py +++ b/forum_modules/pgfulltext/management.py @@ -8,22 +8,20 @@ import forum.models if settings.DATABASE_ENGINE in ('postgresql_psycopg2', 'postgresql', ): from django.db.models.signals import post_syncdb - def setup_pgfulltext(sender, **kwargs): - if sender == forum.models: - install_pg_fts() - - post_syncdb.connect(setup_pgfulltext) - -def install_pg_fts(): - f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r') - - try: - cursor = connection.cursor() - cursor.execute(f.read()) - transaction.commit_unless_managed() - except: - pass - finally: - cursor.close() - - f.close() + def install_pg_fts(): + f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r') + + try: + cursor = connection.cursor() + cursor.execute(f.read()) + transaction.commit_unless_managed() + except: + pass + finally: + cursor.close() + + f.close() + + post_syncdb.connect(install_pg_fts, sender=forum.models, weak=False) + + diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql index 6008789..21cbf95 100644 --- a/forum_modules/pgfulltext/pg_fts_install.sql +++ b/forum_modules/pgfulltext/pg_fts_install.sql @@ -18,37 +18,67 @@ DROP FUNCTION public.create_plpgsql_language (); - CREATE OR REPLACE FUNCTION set_question_tsv() RETURNS TRIGGER AS $$ + CREATE OR REPLACE FUNCTION node_ranking(node_id int, srch text) RETURNS float AS $$ + declare + v tsvector; begin - new.tsv := - setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || - setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || - setweight(to_tsvector('english', coalesce(new.html,'')), 'C'); + SELECT tsv INTO v FROM forum_node WHERE id = node_id; + RETURN ts_rank_cd(v || children_tsv(node_id), plainto_tsquery(srch), 32); + end + $$ LANGUAGE plpgsql; + + CREATE OR REPLACE FUNCTION children_tsv(id int) RETURNS tsvector AS $$ + declare + v tsvector := ''::tsvector; + r record; + begin + FOR r IN SELECT * FROM forum_node WHERE parent_id = id LOOP + v := v || r.tsv || children_tsv(r.id); + END LOOP; + RETURN v; + end + $$ LANGUAGE plpgsql; + + CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$ + begin + IF (tg_op = 'INSERT') THEN + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.body,'')), 'C'); + ELSIF (new.active_revision_id <> old.active_revision_id) OR (new.tsv IS NULL) THEN + new.tsv := + setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || + setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || + setweight(to_tsvector('english', coalesce(new.body,'')), 'C'); + END IF; RETURN new; end $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION public.create_tsv_question_column () + CREATE OR REPLACE FUNCTION public.create_tsv_node_column () RETURNS TEXT AS $$ - ALTER TABLE question ADD COLUMN tsv tsvector; - + ALTER TABLE forum_node ADD COLUMN tsv tsvector; + CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE - ON question FOR EACH ROW EXECUTE PROCEDURE set_question_tsv(); + ON forum_node FOR EACH ROW EXECUTE PROCEDURE set_node_tsv(); - CREATE INDEX question_tsv ON question USING gin(tsv); + CREATE INDEX node_tsv ON forum_node USING gin(tsv); SELECT 'tsv column created'::TEXT; $$ LANGUAGE 'sql'; SELECT CASE WHEN - (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'question') AND attname = 'tsv') + (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_node') AND attname = 'tsv') THEN (SELECT 'Tsv column already exists'::TEXT) ELSE - (SELECT public.create_tsv_question_column()) + (SELECT public.create_tsv_node_column()) END; - DROP FUNCTION public.create_tsv_question_column (); + DROP FUNCTION public.create_tsv_node_column (); + + UPDATE forum_node SET id=id WHERE TRUE; -- 2.39.5