improved text search

git-svn-id: http://svn.osqa.net/svnroot/osqa/trunk@41 0cfe37f9-358a-4d5e-be75-b63607b5c754

diff --git a/forum/skins/default/templates/questions.html b/forum/skins/default/templates/questions.html
index 31644e7e5c24e79cbfc1b2df41f660ecf83ffa27..ddf4f694d47d0ed7ddcfe60b6213ad113112eee5 100644 (file)
--- a/forum/skins/default/templates/questions.html
+++ b/forum/skins/default/templates/questions.html
@@ -5,20 +5,6 @@
 {% load extra_tags %}\r
 \r
 {% block title %}{% spaceless %}{% trans "Questions" %}{% endspaceless %}{% endblock %}\r
 {% load extra_tags %}\r
 \r
 {% block title %}{% spaceless %}{% trans "Questions" %}{% endspaceless %}{% endblock %}\r

-{% block forejs %}\r
-       <script type="text/javascript">\r
-           var tags = {{ tags_autocomplete|safe }};\r
-           $().ready(function(){\r
-               var tab_id = "{{ tab_id }}";\r
-               $("#"+tab_id).attr('className',"on");\r
-               var on_tab = {% if is_unanswered %}'#nav_unanswered'{% else %}'#nav_questions'{% endif %};\r
-               $(on_tab).attr('className','on');\r
-               Hilite.exact = false;\r
-               Hilite.elementid = "listA";\r
-               Hilite.debug_referrer = location.href;\r
-           });\r
-       </script>\r
-{% endblock %}\r

 {% block content %}\r
 <div class="tabBar">\r
     {% question_list_title %}\r
 {% block content %}\r
 <div class="tabBar">\r
     {% question_list_title %}\r

diff --git a/forum/views/readers.py b/forum/views/readers.py
index 15ac86b8995a242c16bf59018ff3a30fe90fb649..b260343d19e97e6b79e51b13f73c27a1cbb59263 100644 (file)
--- 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')))
 
         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,
 
     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},
     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
     
 
 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 45ab2e781888d0118dfbde9457868e28fc73ebd1..4cac36f9ae2d6b42bba4a79961653e253f6995d5 100644 (file)
--- 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={
 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
                     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 487580ff6ef66d438aea3be9a0d90b9130c6d8a9..c2abfaffb4f082f30164e007df553b7c3a073f8f 100644 (file)
--- 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
 
 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 60087894f8c947cd67cc292c1afafda1e0abcf77..21cbf959efd766504284700523291abc7658ef69 100644 (file)
--- 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 ();
 
 
   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
   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;
 
     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 $$
       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
           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 '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
   THEN
      (SELECT 'Tsv column already exists'::TEXT)
   ELSE

-     (SELECT public.create_tsv_question_column())
+     (SELECT public.create_tsv_node_column())

 
   END;
 
 
   END;
 

-  DROP FUNCTION public.create_tsv_question_column ();
+  DROP FUNCTION public.create_tsv_node_column ();
+
+  UPDATE forum_node SET id=id WHERE TRUE;