From: hernani <hernani@0cfe37f9-358a-4d5e-be75-b63607b5c754>
Date: Wed, 4 May 2011 12:13:04 +0000 (+0000)
Subject: Several improvements in full text search operations.
X-Git-Tag: live~293
X-Git-Url: https://git.openstreetmap.org./osqa.git/commitdiff_plain/55eea8d5a3f8fcc3a509f2eaf45005bba20fc76a

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 2b71dbd..46c1e89 100644
--- 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 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
diff --git a/forum/views/readers.py b/forum/views/readers.py
index 91b70bc..67d2c38 100644
--- 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:
+        sort_order = None
+
+        if isinstance(can_rank, basestring):
+            sort_order = can_rank
+
         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
diff --git a/forum_modules/mysqlfulltext/fts_install.sql b/forum_modules/mysqlfulltext/fts_install.sql
index 5ad821e..c32bc31 100644
--- 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,
+	title varchar(300),
+	tagnames varchar(255),
 	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;
@@ -14,7 +16,7 @@ delimiter |
 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;
 |
 
@@ -23,9 +25,9 @@ delimiter |
 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;
 
 |
 
-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
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 5dcb186..8f64e3f 100644
--- a/forum_modules/mysqlfulltext/settings.py
+++ b/forum_modules/mysqlfulltext/settings.py
@@ -1,3 +1,4 @@
 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 b1031a5..e2bc7ef 100644
--- 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
 
+VERSION = 2
+
+f_name = None
+
 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()
@@ -17,6 +26,7 @@ if not bool(settings.MYSQL_FTS_INSTALLED):
         transaction.commit_unless_managed()
 
         settings.MYSQL_FTS_INSTALLED.set_value(True)
+        settings.MYSQL_FTS_VERSION.set_value(VERSION)
 
     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):
-    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 780bfa9..2b40266 100644
--- 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"%%"
 
-    return True, self.extra(
+    return '-ranking', self.extra(
             tables = ['forum_rootnode_doc'],
             select={
             'ranking': """