Difference between revisions of "User:Hef/rt search"

From Pumping Station: One Wiki
Jump to: navigation, search
(Create Table)
(More output)
 
(6 intermediate revisions by the same user not shown)
Line 11: Line 11:
 
     group_id    INTEGER,
 
     group_id    INTEGER,
 
     INDEX(query)
 
     INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/rt";
+
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/rt" CHARACTER SET utf8;
 
</pre>
 
</pre>
  
 
== More output ==
 
== More output ==
<pre>
 
  
You can now configure RT to use the newly-created full-text index by
+
I added The following to RT_SiteConfig.pm
adding the following to your RT_SiteConfig.pm:
 
  
 +
<pre>
 
Set( %FullTextSearch,
 
Set( %FullTextSearch,
 
     Enable    => 1,
 
     Enable    => 1,
Line 26: Line 25:
 
     Table      => 'AttachmentsIndex',
 
     Table      => 'AttachmentsIndex',
 
);
 
);
 +
</pre>
  
 
Below is a simple Sphinx configuration which can be used to index all
 
Below is a simple Sphinx configuration which can be used to index all
Line 31: Line 31:
 
ideal; you should read the Sphinx documentation to understand how to
 
ideal; you should read the Sphinx documentation to understand how to
 
configure it to better suit your needs.
 
configure it to better suit your needs.
 +
<pre>
  
 
source rt {
 
source rt {
Line 73: Line 74:
 
     unlink_old              = 1
 
     unlink_old              = 1
 
}
 
}
 +
 
</pre>
 
</pre>
 
  
 
== Prime things ==
 
== Prime things ==

Latest revision as of 23:39, 26 August 2014

Log into mariadb as root and run INSTALL SONAME 'ha_sphinx'; then show engines; to confirm it worked

Create Table

CREATE TABLE AttachmentsIndex
(
    id          BIGINT NOT NULL,
    weight      INTEGER NOT NULL,
    query       VARCHAR(3072) NOT NULL,
    group_id    INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/rt" CHARACTER SET utf8;

More output

I added The following to 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                    = 9312
    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
}

Prime things

   sudo groupadd sphinx
   sudo useradd -g sphinx -d /usr/share/sphinx/ -s /bin/false sphinx
   sudo chown -R sphinx:sphinx /var/lib/sphinx/
   sudo -u sphinx indexer --all
   sudo systemctl start sphinx