Productivity of roundup can be raised if substring search for the all
text search would be possible.

I am attaching to hacks that have been in use at Intevation a few
years ago. One is probably very inefficient, the other specific to 
backends with the LIKE sql statement.

Hacks done by Frank Koormann:
2008-roundup_indexer_rdbms_pattern_full_textsearch.patch
2008-roundup_indexer_dbm_pattern_search.diff

-re.findall(r'\b\w{2,25}\b', request.search_text), klass)
+re.findall(r'[\w_%]{2,25}', request.search_text), klass)

\w already includes _, so [\w%] is enough here.


There are also a few deprecated/obsolete idioms in the patches:

+if word.find('_') == -1 and word.find('%') == -1:
These should use the "in" operator.

+if not entries.has_key(word):
This (and others) should use "not in".

+raise ValueError, 'Index is corrupted: re-generate it'
This should use "raise ValueError(...)"


The patch(es) should also include some documentation that explains how
to use this new feature.
We also had some problem[0] with case-sensitive searches, however I'm
not sure if this is related to this issue.

[0]: http://psf.upfronthosting.co.za/roundup/meta/issue298

Ezio,
thanks for taking a look. As I wrote, those are old patches
and we need to evaluate if they at all provide a generic solution I
guess. To me there are two parts of it:
a) how would the user interface to the substring search look like?
Using the same as the "LIKE" operator?

Here is some describtion:
"""
string LIKE pattern 

If pattern does not contain percent signs or underscore, then the
pattern only represents the string itself; in that case LIKE acts like
the equals operator. An underscore (_) in pattern stands for (matches)
any single character; a percent sign (%) matches any string of zero or
more characters."""

Given that we want to use an index of some kind, I haven't looked into
solutions how to do a pattern match that is not directly supported
by the index engine (like "LIKE").

About http://psf.upfronthosting.co.za/roundup/meta/issue298 (realname
search should be case insensitive): To me this looks like a different
case, as it is specific to finding users. 

The issue at hand would be about substring searches in the msg texts.
(And case insensitive as those searches are already.)

Maybe issue2550805 is related. Do you use the postgresql backend for the python 
tracker or something else?

bugs.python.org uses Postgres

Ezio, could you try the change in Issue2550805, it may or may not affect
user name search . It definately affected Postgresql. :)

Attached is an updated version of
2008-roundup_indexer_rdbms_pattern_full_textsearch.patch
named roundup_indexer_rdbms_pattern_full_textsearch2.patch
which still is just a quick hack, but works for us.
(PostgreSQL backend, Xapian is disabled)

oops, restored the title

I assume it is necessary to use 'roundup-admin reindex' after applying 
the roundup_indexer_rdbms_pattern_full_textsearch2.patch?

roundup-admin reindex should not be needed, unless you are switching
from Xapian.

Ralf,

We have substring search in the rest interface. Could that be leveraged 
to allow us to resolve this ticket as well?

Also I don't see any test cases for this. Am I missing something?

On Sat, Nov 09, 2019 at 07:28:07PM +0000, John Rouillard wrote:
> 
> John Rouillard added the comment:
> 
> Ralf,
> 
> We have substring search in the rest interface. Could that be leveraged 
> to allow us to resolve this ticket as well?
> 
> Also I don't see any test cases for this. Am I missing something?

As I read it it applies to fulltext search.
And doing a substring search in the fulltext index is probably always
ending up with performance problems. So I would not accept a patch that
uses this?

Ralf
-- 
Dr. Ralf Schlatterbeck                  Tel:   +43/2243/26465-16
Open Source Consulting                  www:   http://www.runtux.com
Reichergasse 131, A-3411 Weidling       email: office@runtux.com

> And doing a substring search in the fulltext index is probably always
> ending up with performance problems.

For PostgreSQL there is a a trigram (trigraph) index that support
fast substring searches.
https://www.postgresql.org/docs/current/pgtrgm.html

Would switching to a search that does stemming or prefix search
(like postgres FTS, whoosh or SQLite FTS) work here?

It does look like postgres and sqlite at least support trigram so
patches that
work with that would be more promising.