Full text indexing in RT
While all of the below solutions can search for Unicode characters, they are not otherwise Unicode aware, and do no case folding, normalization, or the like. That is, a string that contains U+0065 LATIN SMALL LETTER E followed by U+0301 COMBINING ACUTE ACCENT will not match a search for U+00E9 LATIN SMALL LETTER E WITH ACUTE. They also only know how to tokenize latin-1-ish languages where words are separated by whitespace or similar characters; as such, support for searching for Japanese and Chinese content is extremely limited.
Postgres 8.3 and above support full-text searching natively; to set up the required ts_vector column, and create either a GiN or GiST index on it, run:
sbin/rt-setup-fulltext-indexIf you have a non-standard database administrator username or password, you may need to pass the --dba or --dba-password options:
sbin/rt-setup-fulltext-index --dba postgres --dba-password secretThis will also output an appropriate %FullTextSearch configuration to add to your RT_SiteConfig.pm; you will need to restart your webserver after making these changes. However, the index will also need to be filled before it can be used. To update the index initially, run:
sbin/rt-fulltext-indexer --allThis will tokenize and index all existing attachments in your database; it may take quite a while if your database already has a large number of tickets in it.
To keep the index up-to-date, you will need to run:
sbin/rt-fulltext-indexer...at regular intervals. By default, this will only tokenize up to 100 tickets at a time; you can adjust this upwards by passing --limit 500. Larger batch sizes will take longer and consume more memory.
If there is already an instances of rt-fulltext-indexer running, new ones will exit abnormally (with exit code 1) and the error message "rt-fulltext-indexer is already running." You can suppress this message and end those processes normally (with exit code 0) using the --quiet option; this is particularly useful when running the command via cron:
sbin/rt-fulltext-indexer --quietMySQL does not support full-text indexing natively. However, it does integrate with the external Sphinx engine, available from http://sphinxsearch.com. Unfortunately, Sphinx integration (using SphinxSE) does require that you recompile MySQL from source. Most distribution-provided packages for MySQL do not include SphinxSE integration, merely the external Sphinx tools; these are not sufficient for RT's needs.
SphinxSE requires MySQL 5.0 or 5.1; later versions of MySQL have not been tested at this time. Sphinx version 2.0.1 has been tested to work, but version 0.9.9 may work as well. Compilation and installation instructions for MySQL with SphinxSE can be found at http://sphinxsearch.com/docs/current.html#sphinxse-installing.
Once MySQL has been recompiled with SphinxSE, and Sphinx itself is installed, you may create the required SphinxSE communication table via:
sbin/rt-setup-fulltext-indexIf you have a non-standard database administrator username or password, you may need to pass the --dba or --dba-password options:
sbin/rt-setup-fulltext-index --dba root --dba-password secretThis will also provide you with the appropriate %FullTextSearch configuration to add to your RT_SiteConfig.pm; you will need to restart your webserver after making these changes. It will also print a sample Sphinx configuration, which should be placed in /etc/sphinx.conf, or equivalent.
To fill the index, you will need to run the indexer command-line tool provided by Sphinx:
indexer rtFinally, start the Sphinx search daemon:
searchdTo keep the index up-to-date, you will need to run:
indexer rt --rotate...at regular intervals in order to pick up new and updated attachments from RT's database. Failure to do so will result in stale data.
Sphinx only returns a finite number of matches to any query; this number is controlled by max_matches in /etc/sphinx.conf and %FullTextSearch's MaxMatches in RT_SiteConfig.pm, which must be kept in sync. The default, set during rt-setup-fulltext-index, is 10000. This limit may lead to false negatives in search results if the maximum number of matches is reached but the results returned do not match RT's other criteria.
Take, for example, the instance where Sphinx is configured to return a maximum of three results, and tickets 1, 2, 3, 4, and 5 contain the string "target", but only ticket 5 is in status "Open". A search for Content LIKE 'target' AND Status = 'Open' may return no results, despite ticket 5 matching those criteria, as Sphinx will only return tickets 1, 2, and 3 as possible matches.
After index creation, altering MaxMatches in RT_SiteConfig.pm is insufficient to adjust this limit; both max_matches in /etc/sphinx.conf and %FullTextSearch's MaxMatches in RT_SiteConfig.pm must be updated.
Oracle supports full-text indexing natively using the Oracle Text package. Once Oracle Text is installed and configured, run:
sbin/rt-setup-fulltext-indexIf you have a non-standard database administrator username or password, you may need to pass the --dba or --dba-password options:
sbin/rt-setup-fulltext-index --dba sysdba --dba-password secretThis will create an Oracle CONTEXT index on the Content column in the Attachments table, as well as several preferences, functions and triggers to support this index. The script will also output an appropriate %FullTextSearch configuration to add to your RT_SiteConfig.
To update the index, you will need to run the following at regular intervals:
sbin/rt-fulltext-indexerThis, in effect, simply runs:
begin
ctx_ddl.sync_index('rt_fts_index', '2M');
end;The amount of memory used for the sync can be controlled with the --memory option:
rt-fulltext-indexer --memory 10MIf there is already an instance of rt-fulltext-indexer running, new ones will exit abnormally (with exit code 1) and the error message "rt-fulltext-indexer is already running." You can suppress this message and end those processes normally (with exit code 0) using the --quiet option; this is particularly useful when running the command via cron:
sbin/rt-fulltext-indexer --quietInstead of being run via cron, this may instead be run via a DBMS_JOB; read the Managing DML Operations for a CONTEXT Index chapter of Oracle's Text Application Developer's Guide for details how to keep the index optimized, perform garbage collection, and other tasks.