From 55eea8d5a3f8fcc3a509f2eaf45005bba20fc76a Mon Sep 17 00:00:00 2001 From: hernani Date: Wed, 4 May 2011 12:13:04 +0000 Subject: [PATCH] Several improvements in full text search operations. git-svn-id: http://svn.osqa.net/svnroot/osqa/trunk@1020 0cfe37f9-358a-4d5e-be75-b63607b5c754 --- forum/utils/pagination.py | 5 +++- forum/views/readers.py | 7 +++++- forum_modules/mysqlfulltext/fts_install.sql | 10 +++++--- forum_modules/mysqlfulltext/fts_update.sql | 26 +++++++++++++++++++ forum_modules/mysqlfulltext/settings.py | 3 ++- forum_modules/mysqlfulltext/startup.py | 28 +++++++++++++++++++-- forum_modules/pgfulltext/handlers.py | 2 +- 7 files changed, 71 insertions(+), 10 deletions(-) create mode 100644 forum_modules/mysqlfulltext/fts_update.sql 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': """ -- 2.39.5