User:Hef/rt search

Going to run the following in the DB:
    CREATE TABLE AttachmentsIndex (
        id     BIGINT NOT NULL,
        weight INTEGER NOT NULL,
        query  VARCHAR(3072) NOT NULL,
        INDEX(query)
    ) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:3312/rt" CHARACTER SET utf8



You can now configure RT to use the newly-created full-text index by
adding the following to your RT_SiteConfig.pm:

Set( %FullTextSearch,
    Enable     => 1,
    Indexed    => 1,
    MaxMatches => '10000',
    Table      => 'AttachmentsIndex',
);

Below is a simple Sphinx configuration which can be used to index all
text/plain attachments in your database.  This configuration is not
ideal; you should read the Sphinx documentation to understand how to
configure it to better suit your needs.

source rt {
    type            = mysql

    sql_host        = localhost
    sql_db          = rt4
    sql_user        = rt_user
    sql_pass        = rt_pass

    sql_query_pre   = SET NAMES utf8
    sql_query       = \
        SELECT a.id, a.content FROM Attachments a \
        JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \
        JOIN Tickets t ON txn.ObjectId = t.id \
        WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'

    sql_query_info  = SELECT * FROM Attachments WHERE id=$id
}

index rt {
    source                  = rt
    path                    = /opt/rt4/var/sphinx/index
    docinfo                 = extern
    charset_type            = utf-8
}

indexer {
    mem_limit               = 32M
}

searchd {
    port                    = 3312
    log                     = /opt/rt4/var/sphinx/searchd.log
    query_log               = /opt/rt4/var/sphinx/query.log
    read_timeout            = 5
    max_children            = 30
    pid_file                = /opt/rt4/var/sphinx/searchd.pid
    max_matches             = 10000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
}