Several improvements in full text search operations.

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

diff --git a/forum/utils/pagination.py b/forum/utils/pagination.py
index 2b71dbd4fefa3fc433e4e230f2ad38a4486c09ed..46c1e89c8b7d4a79fe91f3881c767148af23cbeb 100644 (file)
--- a/forum/utils/pagination.py
+++ b/forum/utils/pagination.py
@@ -34,7 +34,10 @@ class SimpleSort(SortBase):
         return isinstance(self.order_by, (list, tuple)) and self.order_by or [self.order_by]
 
     def apply(self, objects):
         return isinstance(self.order_by, (list, tuple)) and self.order_by or [self.order_by]
 
     def apply(self, objects):

-        return objects.order_by(*self._get_order_by())
+        if self.order_by:
+            return objects.order_by(*self._get_order_by())
+
+        return objects

 
 class PaginatorContext(object):
     visible_page_range = 5
 
 class PaginatorContext(object):
     visible_page_range = 5

diff --git a/forum/views/readers.py b/forum/views/readers.py
index 91b70bcb68ba70f5f332db55e74612d459ce3f26..67d2c38d94e5d6486b7997eacca7dd7873878117 100644 (file)
--- a/forum/views/readers.py
+++ b/forum/views/readers.py
@@ -220,8 +220,13 @@ def question_search(request, keywords):
     can_rank, initial = Question.objects.search(keywords)
 
     if can_rank:
     can_rank, initial = Question.objects.search(keywords)
 
     if can_rank:

+        sort_order = None
+
+        if isinstance(can_rank, basestring):
+            sort_order = can_rank
+

         paginator_context = QuestionListPaginatorContext()
         paginator_context = QuestionListPaginatorContext()

-        paginator_context.sort_methods[_('ranking')] = pagination.SimpleSort(_('relevance'), '-ranking', _("most relevant questions"))
+        paginator_context.sort_methods[_('ranking')] = pagination.SimpleSort(_('relevance'), sort_order, _("most relevant questions"))

         paginator_context.force_sort = _('ranking')
     else:
         paginator_context = None
         paginator_context.force_sort = _('ranking')
     else:
         paginator_context = None

diff --git a/forum_modules/mysqlfulltext/fts_install.sql b/forum_modules/mysqlfulltext/fts_install.sql
index 5ad821e36017956fcfb02cc36c56f8fcd6742e26..c32bc311b91a6f137ae942fde0089cfb451bf7e0 100644 (file)
--- a/forum_modules/mysqlfulltext/fts_install.sql
+++ b/forum_modules/mysqlfulltext/fts_install.sql
@@ -2,9 +2,11 @@ CREATE TABLE forum_mysqlftsindex (
        id int NOT NULL AUTO_INCREMENT,
        node_id int NOT NULL UNIQUE,
        body longtext NOT NULL,
        id int NOT NULL AUTO_INCREMENT,
        node_id int NOT NULL UNIQUE,
        body longtext NOT NULL,

+       title varchar(300),
+       tagnames varchar(255),

        PRIMARY KEY (id),
        FOREIGN KEY (node_id) REFERENCES forum_node (id)   ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY (id),
        FOREIGN KEY (node_id) REFERENCES forum_node (id)   ON UPDATE CASCADE ON DELETE CASCADE,

-       FULLTEXT (body)
+       FULLTEXT (body, title, tagnames)

 ) ENGINE=`MyISAM`;
 
 ALTER TABLE forum_mysqlftsindex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
 ) ENGINE=`MyISAM`;
 
 ALTER TABLE forum_mysqlftsindex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;


@@ -14,7 +16,7 @@ delimiter |
 CREATE TRIGGER fts_on_insert AFTER INSERT ON forum_node
   FOR EACH ROW
   BEGIN
 CREATE TRIGGER fts_on_insert AFTER INSERT ON forum_node
   FOR EACH ROW
   BEGIN

-    INSERT INTO forum_mysqlftsindex (node_id, body) VALUES (NEW.id, UPPER(CONCAT_WS('\n', NEW.title, NEW.body, NEW.tagnames)));
+    INSERT INTO forum_mysqlftsindex (node_id, title, body, tagnames) VALUES (NEW.id, UPPER(NEW.title), UPPER(NEW.body), UPPER(NEW.tagnames));

   END;
 |
 
   END;
 |
 


@@ -23,9 +25,9 @@ delimiter |
 CREATE TRIGGER fts_on_update AFTER UPDATE ON forum_node
   FOR EACH ROW
   BEGIN
 CREATE TRIGGER fts_on_update AFTER UPDATE ON forum_node
   FOR EACH ROW
   BEGIN

-    UPDATE forum_mysqlftsindex SET body = UPPER(CONCAT_WS('\n', NEW.title, NEW.body, NEW.tagnames)) WHERE node_id = NEW.id;
+    UPDATE forum_mysqlftsindex SET title = UPPER(NEW.title), body = UPPER(NEW.body), tagnames = UPPER(NEW.tagnames) WHERE node_id = NEW.id;

   END;
 
 |
 
   END;
 
 |
 

-INSERT INTO forum_mysqlftsindex (node_id, body) SELECT id, UPPER(CONCAT_WS('\n', title, body, tagnames)) FROM forum_node;
\ No newline at end of file
+INSERT INTO forum_mysqlftsindex (node_id, title, body, tagnames) SELECT id, UPPER(title), UPPER(body), UPPER(tagnames) FROM forum_node;
\ No newline at end of file

diff --git a/forum_modules/mysqlfulltext/fts_update.sql b/forum_modules/mysqlfulltext/fts_update.sql
new file mode 100644 (file)
index 0000000..a95c827
--- /dev/null
+++ b/forum_modules/mysqlfulltext/fts_update.sql
@@ -0,0 +1,26 @@
+DROP TRIGGER `fts_on_insert`;
+DROP TRIGGER `fts_on_update`;
+
+ALTER TABLE forum_mysqlftsindex ADD COLUMN title varchar(300) NOT NULL, ADD COLUMN tagnames varchar(255) NOT NULL;
+ALTER TABLE forum_mysqlftsindex ENGINE = MYISAM;
+ALTER TABLE forum_mysqlftsindex ADD FULLTEXT `title`(title), ADD FULLTEXT `tagnames`(tagnames);
+UPDATE forum_mysqlftsindex ind JOIN forum_node node ON ind.node_id = node.id SET ind.body = UPPER(node.body), ind.title = UPPER(node.title), ind.tagnames = UPPER(node.tagnames);
+
+delimiter |
+
+CREATE TRIGGER fts_on_insert AFTER INSERT ON forum_node
+  FOR EACH ROW
+  BEGIN
+    INSERT INTO forum_mysqlftsindex (node_id, title, body, tagnames) VALUES (NEW.id, UPPER(NEW.title), UPPER(NEW.body), UPPER(NEW.tagnames));
+  END;
+|
+
+delimiter |
+
+CREATE TRIGGER fts_on_update AFTER UPDATE ON forum_node
+  FOR EACH ROW
+  BEGIN
+    UPDATE forum_mysqlftsindex SET title = UPPER(NEW.title), body = UPPER(NEW.body), tagnames = UPPER(NEW.tagnames) WHERE node_id = NEW.id;
+  END;
+
+|

diff --git a/forum_modules/mysqlfulltext/settings.py b/forum_modules/mysqlfulltext/settings.py
index 5dcb186765ebd32a96ee19fb6f1675dcbb6561f6..8f64e3fe6f2a8bdf740f96c1777af4ce446a58b6 100644 (file)
--- a/forum_modules/mysqlfulltext/settings.py
+++ b/forum_modules/mysqlfulltext/settings.py
@@ -1,3 +1,4 @@
 from forum.settings.base import Setting
 
 from forum.settings.base import Setting
 

-MYSQL_FTS_INSTALLED = Setting('MYSQL_FTS_INSTALLED', False)
\ No newline at end of file
+MYSQL_FTS_INSTALLED = Setting('MYSQL_FTS_INSTALLED', False)
+MYSQL_FTS_VERSION = Setting('MYSQL_FTS_VERSION', 1)
\ No newline at end of file

diff --git a/forum_modules/mysqlfulltext/startup.py b/forum_modules/mysqlfulltext/startup.py
index b1031a532bccf8519a12f50159155deec9f05483..e2bc7efc91a6f36c34097a3036353b3c5f00cf9a 100644 (file)
--- a/forum_modules/mysqlfulltext/startup.py
+++ b/forum_modules/mysqlfulltext/startup.py
@@ -8,8 +8,17 @@ from forum.models.question import Question, QuestionManager
 from forum.models.node import Node
 from forum.modules import decorate
 
 from forum.models.node import Node
 from forum.modules import decorate
 

+VERSION = 2
+
+f_name = None
+

 if not bool(settings.MYSQL_FTS_INSTALLED):
 if not bool(settings.MYSQL_FTS_INSTALLED):

-    f = open(os.path.join(os.path.dirname(__file__), 'fts_install.sql'), 'r')
+    f_name = os.path.join(os.path.dirname(__file__), 'fts_install.sql')
+elif int(settings.MYSQL_FTS_VERSION < VERSION):
+    f_name = os.path.join(os.path.dirname(__file__), 'fts_update.sql')
+
+if f_name:
+    f = open(f_name, 'r')

 
     try:
         cursor = connection.cursor()
 
     try:
         cursor = connection.cursor()


@@ -17,6 +26,7 @@ if not bool(settings.MYSQL_FTS_INSTALLED):
         transaction.commit_unless_managed()
 
         settings.MYSQL_FTS_INSTALLED.set_value(True)
         transaction.commit_unless_managed()
 
         settings.MYSQL_FTS_INSTALLED.set_value(True)

+        settings.MYSQL_FTS_VERSION.set_value(VERSION)

 
     except Exception, e:
         #import sys, traceback
 
     except Exception, e:
         #import sys, traceback


@@ -31,4 +41,18 @@ word_re = re.compile(r'\w+', re.UNICODE)
 
 @decorate(QuestionManager.search, needs_origin=False)
 def question_search(self, keywords):
 
 @decorate(QuestionManager.search, needs_origin=False)
 def question_search(self, keywords):

-    return False, self.filter(models.Q(ftsindex__body__search=keywords.upper()))
\ No newline at end of file
+    keywords = keywords.upper()
+
+    return '-ranking', self.filter(
+            models.Q(ftsindex__body__search=keywords) or models.Q(ftsindex__title__search=keywords) or models.Q(ftsindex__tagnames__search=keywords)
+
+    ).extra(
+        select={
+            'ranking': """
+                match(forum_mysqlftsindex.tagnames) against (%s in boolean mode) * 4 +
+                match(forum_mysqlftsindex.title) against (%s in boolean mode) * 2 +
+                match(forum_mysqlftsindex.body) against (%s in boolean mode) * 1
+                                """,
+            },
+        select_params=[keywords, keywords, keywords]
+    )

diff --git a/forum_modules/pgfulltext/handlers.py b/forum_modules/pgfulltext/handlers.py
index 780bfa97e60cf687b5d2f24b8c400124a7fbbb87..2b402666c6429f70d816788d34cf058f907f8006 100644 (file)
--- a/forum_modules/pgfulltext/handlers.py
+++ b/forum_modules/pgfulltext/handlers.py
@@ -12,7 +12,7 @@ def question_search(self, keywords):
     tsquery = " | ".join(word_re.findall(keywords))
     ilike = keywords + u"%%"
 
     tsquery = " | ".join(word_re.findall(keywords))
     ilike = keywords + u"%%"
 

-    return True, self.extra(
+    return '-ranking', self.extra(

             tables = ['forum_rootnode_doc'],
             select={
             'ranking': """
             tables = ['forum_rootnode_doc'],
             select={
             'ranking': """