adapt fts base to odoo 8.0, change the following
1. fts_base\_openerp_.py
change from update_xml to data : 'data': ["fts_proxy.xml", 'wizard/fts_config.xml'],
2. fts_base\fts_base.py
change line 40 from cr = self.pool.db.cursor() to
cr = self.pool.cursor() # there is no db attribute anymore,
3. fts_base\fts_base.py and fts_base\fts_proxy.py
change from to_tsquery to plainto_tsquery if searching by key words separated by space other than by adding & bebween key words, e.g search by "full text search" other than "full & text & search"
extend website_forum module to enable full text search
here the highlight of the changes needed
1. install fts_mail
2. create new module fts_forum
2.1 activate full text search on forum_post model by inherit from fts_base and specify the content field as full text search field
2.2 extend the search function of forum_post class, adapt the search function from fts_base to search against the forum_post table directly and return the post ID. small tips 1.) there is parent_id = blank in args which means the result will bypass the answers , 2.) the comment stored in mail_message table other than in the forum_post table.
Here the source code
from openerp.addons.fts_base.fts_base import fts_base
from openerp.osv import osv
import logging
from openerp.osv import expression
from openerp import SUPERUSER_ID
import openerp
_logger = logging.getLogger(__name__)
class Fts_Forum(fts_base):
_model = 'forum.post'
_indexed_column = 'content'
_title_column = 'name'
class forum_post(osv.osv):
_inherit = 'forum.post'
_model = 'forum.post'
"""The model this search works on. Required."""
_indexed_column = 'content'
"""The column this search works on. Required.
If this is a list of strings, all of them will be indexed for the fulltext
search.
"""
_table = 'forum_post'
"""The table this search works on. Will be deduced from model if
not set."""
_tsvector_column = 'content_tsvector'
"""The column holding tsvector data. Will be created on init.
If not set, it will be ${_indexed_column}_tsvector."""
_tsvector_column_index = None
"""The name of the index for _tsvector_column.
If not set, it will be ${_indexed_column}_idx."""
_tsvector_column_trigger = None
"""The name of the trigger to update _tsvector_column when _indexed_column
is updated.
If not set, it will be ${_indexed_column}_trigger."""
_tsconfig = 'pg_catalog.simple'
"""The fulltext config (=language) to be used. Will be read from
properties if they exist: A specific one for the current module, then
fts_base."""
_title_column = 'name'
"""The column to be shown as title of a match. This can be an arbitrary SQL
expression"""
_disable_seqscan = True
"""The postgresql query planner (as of 9.0) chooses against using the query
planner way too often. This forces hin to use it which improves speed in all
tested cases. Disable (and report) if this causes problems for you."""
_extra_columns = []
def _get_filter_expression(self, cr, uid, args, context=None):
"""Return a expression for additional filtering"""
orm_model = self.pool.get(self._model)
applicable_args = []
def get_applicable_args(args, index):
if expression.is_leaf(args[index]):
#TODO: also check for inherited fields etc
if ((
args[index][0] in orm_model._columns or
orm_model._log_access and
args[index][0] in ['create_date', 'create_uid',
'write_date', 'write_uid']
)
and
# parent_id = null is in filters, but to search answers also, need to remove it
args[index][0] not in ['name', 'content', 'model','parent_id']
):
return [args[index]], 1
else:
return [], 1
else:
op1 = get_applicable_args(args, index + 1)
op2 = get_applicable_args(args, index + op1[1] + 1)
return (([args[index]]
if len(op1[0]) > 0 and len(op2[0]) > 0
else []) +
op1[0] + op2[0],
op1[1] + op2[1] + 1
)
if openerp.release.version_info[0] <= 6:
args = get_applicable_args(expression.normalize(args), 0)[0]
else:
args = get_applicable_args(expression.normalize_domain(args), 0)[0]
return expression.expression(cr, uid, args, orm_model, context)
def _get_fts_proxy_values(self, cr, uid, row):
"""Returns the values used to create a new fts_proxy object. Override if
you want to modify standard behavior or if you added columns in
_extra_column"""
return {
'model': self._model,
'res_id': row[0],
'rank': row[1],
'name': row[2],
'summary': row[3],
}
def search(self, cr, uid, args, offset=0, limit=None, order=None, context=None, count=False):
res = []
proxy_obj = self.pool.get('fts.proxy')
searchstring = ''
for arg in args:
if arg[0] == 'content' and arg[1] == 'ilike':
searchstring = arg[2]
if searchstring:
_logger.info('search string', searchstring)
else:
_logger.debug('doing nothing because i got no search string')
return super(forum_post, self).search(cr, uid, args, offset=offset, limit=limit, order=order,
context=context, count=count)
if self._disable_seqscan:
cr.execute('set enable_seqscan=off')
filters = self._get_filter_expression(cr, uid, args, context).to_sql()
filters = cr.mogrify(filters[0], filters[1])
cr.execute(
(
"SELECT " +
(
"count(*)" if count else
"""
id,
ts_rank(%(tsvector_column)s,
plainto_tsquery('%(language)s', '%(searchstring)s')),
%(title_column)s,
""" +
(
"""
ts_headline('%(language)s', %(indexed_column)s,
plainto_tsquery('%(language)s', '%(searchstring)s'),
'StartSel = *, StopSel = *')"""
if context.get('fts_summary')
else 'null'
)
+
((', ' + reduce(lambda x, y: ('' if x is None else x + ',' + y),
self._extra_columns))
if self._extra_columns else '')
) +
"""
FROM %(table)s WHERE %(tsvector_column)s @@
plainto_tsquery('%(language)s', '%(searchstring)s')"""
) %
{
'tsvector_column': self._tsvector_column,
'table': self._table,
'language': self._tsconfig,
'indexed_column': ('"' + self._indexed_column + '"'
if isinstance(self._indexed_column, str)
else reduce(lambda x, y: ('' if x is None else
(x + " || ' ' || ")
) +
"coalesce(\"" + y + "\", '')",
self._indexed_column)),
'title_column': self._title_column,
'searchstring': searchstring,
} + ' AND ' + str(filters)
)
record_cnt = 0
for row in cr.fetchall():
if count:
record_cnt = row[0]
res.append(row[0])
# search comment from message.mail table
cr.execute(
( "SELECT " +
( "count(*)" if count else
" res_id " ) +
""" FROM %(table)s WHERE %(tsvector_column)s @@
plainto_tsquery('%(language)s', '%(searchstring)s')
and model ='forum.post' and subtype_id = '1'""")
%{ 'tsvector_column':'body_tsvector',
'table': 'mail_message',
'language': self._tsconfig,
'searchstring': searchstring,}
)
for row in cr.fetchall():
if count:
if record_cnt == 0: #if questions and answers already found, no record count from comment,
record_cnt = row[0]
res.append(row[0])
if self._disable_seqscan:
cr.execute('set enable_seqscan=on')
if count:
return record_cnt
else:
return res
the proposed solution is far from perfect, currently workable on my windows 7 system, the further improvement is to fully integrate with ORM or make ORM natively support full text search by introducing new fulltext column type with relevant additional attribute, in base model and expression to handle the SQL command conversion for those special fulltext field. finally make ORM natrually support fulltext search !