2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2015 Best Practical Solutions, LLC
7 # <sales@bestpractical.com>
9 # (Except where explicitly superseded by other copyright notices)
14 # This work is made available to you under the terms of Version 2 of
15 # the GNU General Public License. A copy of that license should have
16 # been provided with this software, but in any event can be snarfed
19 # This work is distributed in the hope that it will be useful, but
20 # WITHOUT ANY WARRANTY; without even the implied warranty of
21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
22 # General Public License for more details.
24 # You should have received a copy of the GNU General Public License
25 # along with this program; if not, write to the Free Software
26 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
27 # 02110-1301 or visit their web page on the internet at
28 # http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
31 # CONTRIBUTION SUBMISSION POLICY:
33 # (The following paragraph is not intended to limit the rights granted
34 # to you to modify and distribute this software under the terms of
35 # the GNU General Public License and is only of importance to you if
36 # you choose to contribute your changes and enhancements to the
37 # community by submitting them to Best Practical Solutions, LLC.)
39 # By intentionally submitting any modifications, corrections or
40 # derivatives to this work, or any other work intended for use with
41 # Request Tracker, to Best Practical Solutions, LLC, you confirm that
42 # you are the copyright holder for those contributions and you grant
43 # Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable,
44 # royalty-free, perpetual, license to use, copy, create derivative
45 # works based on those contributions, and sublicense and distribute
46 # those contributions and any derivatives thereof.
48 # END BPS TAGGED BLOCK }}}
53 # fix lib paths, some may be relative
54 BEGIN { # BEGIN RT CMD BOILERPLATE
57 my @libs = ("@RT_LIB_PATH@", "@LOCAL_LIB_PATH@");
61 unless ( File::Spec->file_name_is_absolute($lib) ) {
62 $bin_path ||= ( File::Spec->splitpath(Cwd::abs_path(__FILE__)) )[1];
63 $lib = File::Spec->catfile( $bin_path, File::Spec->updir, $lib );
75 use RT::Interface::CLI ();
78 type => scalar RT->Config->Get('DatabaseType'),
79 user => scalar RT->Config->Get('DatabaseUser'),
80 admin => scalar RT->Config->Get('DatabaseAdmin'),
81 admin_password => undef,
92 if ( $DB{'type'} eq 'Pg' ) {
94 table => 'AttachmentsIndex',
95 column => 'ContentIndex',
98 elsif ( $DB{'type'} eq 'mysql' ) {
100 table => 'AttachmentsIndex',
103 elsif ( $DB{'type'} eq 'Oracle' ) {
109 use Getopt::Long qw(GetOptions);
111 'h|help!' => \$OPT{'help'},
112 'ask!' => \$OPT{'ask'},
113 'dry-run!' => \$OPT{'dryrun'},
114 'attachments!' => \$OPT{'attachments'},
116 'table=s' => \$OPT{'table'},
117 'column=s' => \$OPT{'column'},
118 'url=s' => \$OPT{'url'},
119 'maxmatches=i' => \$OPT{'maxmatches'},
120 'index-type=s' => \$OPT{'index-type'},
122 'dba=s' => \$DB{'admin'},
123 'dba-password=s' => \$DB{'admin_password'},
124 'limit=i' => \$DB{'batch-size'},
127 if ( $OPT{'help'} || (!$DB{'admin'} && $DB{'type'} eq 'Oracle' ) ) {
128 show_help( !$OPT{'help'} );
131 my $dbh = $RT::Handle->dbh;
132 $dbh->{'RaiseError'} = 1;
133 $dbh->{'PrintError'} = 1;
135 # MySQL could either be native of sphinx; find out which
136 if ($DB{'type'} eq "mysql") {
137 my $index_type = lc($OPT{'index-type'} || '');
139 # Default to sphinx on < 5.6, and error if they provided mysql
141 if ($RT::Handle->dbh->{mysql_serverversion} < 50600) {
142 $msg = "Complete support for full-text search requires MySQL 5.6 or higher. For prior\n"
143 ."versions such as yours, full-text indexing can either be provided using MyISAM\n"
144 ."tables, or the external Sphinx indexer. Using MyISAM tables requires that your\n"
145 ."database be tuned to support them, as RT uses InnoDB tables for all other content.\n"
146 ."Using Sphinx will require recompiling MySQL. Which indexing solution would you\n"
149 $msg = "MySQL 5.6 and above support native full-text indexing; for compatibility\n"
150 ."with earlier versions of RT, the external Sphinx indexer is still supported.\n"
151 ."Which indexing solution would you prefer?"
154 while ( $index_type ne 'sphinx' and $index_type ne 'mysql' ) {
155 $index_type = lc prompt(
158 silent => !$OPT{'ask'},
161 $DB{'type'} = $index_type;
164 if ( $DB{'type'} eq 'mysql' ) {
165 # MySQL 5.6 has FTS on InnoDB "text" columns -- which the
166 # Attachments table doesn't have, but we can make it have.
167 my $table = $OPT{'table'} || prompt(
168 message => "Enter the name of a new MySQL table that will be used to store the\n"
169 . "full-text content and indexes:",
170 default => $DEFAULT{'table'},
171 silent => !$OPT{'ask'},
173 do_error_is_ok( dba_handle() => "DROP TABLE $table" )
174 unless $OPT{'dryrun'};
176 my $engine = $RT::Handle->dbh->{mysql_serverversion} < 50600 ? "MyISAM" : "InnoDB";
177 my $schema = "CREATE TABLE $table ( "
178 ."id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
179 ."Content LONGTEXT ) ENGINE=$engine CHARACTER SET utf8";
180 insert_schema( $schema );
182 insert_data( Table => $table, Engine => $engine );
184 insert_schema( "CREATE FULLTEXT INDEX $table ON $table(Content)" );
186 print_rt_config( Table => $table );
187 } elsif ($DB{'type'} eq 'sphinx') {
189 my $table = $OPT{'table'} || prompt(
190 message => "Enter name of a new MySQL table that will be used to connect to the\n"
192 default => $DEFAULT{'table'},
193 silent => !$OPT{'ask'},
196 my $url = 'sphinx://localhost:3312/rt';
197 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
198 $url = 'sphinx://127.0.0.1:3312/rt'
199 if $version and $version =~ /^(\d+\.\d+)/ and $1 >= 5.5;
201 $url = $OPT{'url'} || prompt(
202 message => "Enter URL of the sphinx search server; this should be of the form\n"
203 . "sphinx://<server>:<port>/<index name>",
205 silent => !$OPT{'ask'},
207 my $maxmatches = $OPT{'maxmatches'} || prompt(
208 message => "Maximum number of matches to return; this is the maximum number of\n"
209 . "attachment records returned by the search, not the maximum number\n"
210 . "of tickets. Both your RT_SiteConfig.pm and your sphinx.conf must\n"
211 . "agree on this value. Larger values cause your Sphinx server to\n"
212 . "consume more memory and CPU time per query.",
214 silent => !$OPT{'ask'},
218 CREATE TABLE $table (
220 weight INTEGER NOT NULL,
221 query VARCHAR(3072) NOT NULL,
223 ) ENGINE=SPHINX CONNECTION="$url" CHARACTER SET utf8
226 do_error_is_ok( dba_handle() => "DROP TABLE $table" )
227 unless $OPT{'dryrun'};
228 insert_schema( $schema );
230 print_rt_config( Table => $table, MaxMatches => $maxmatches );
233 my $urlo = URI->new( $url );
234 my ($host, $port) = split /:/, $urlo->authority;
235 my $index = $urlo->path;
238 my $var_path = $RT::VarPath;
240 my %sphinx_conf = ();
241 $sphinx_conf{'host'} = RT->Config->Get('DatabaseHost');
242 $sphinx_conf{'db'} = RT->Config->Get('DatabaseName');
243 $sphinx_conf{'user'} = RT->Config->Get('DatabaseUser');
244 $sphinx_conf{'pass'} = RT->Config->Get('DatabasePassword');
248 Below is a simple Sphinx configuration which can be used to index all
249 text/plain attachments in your database. This configuration is not
250 ideal; you should read the Sphinx documentation to understand how to
251 configure it to better suit your needs. It assumes that you create the
252 $var_path/sphinx/ directory, and that is is writable by the sphinx
258 sql_host = $sphinx_conf{'host'}
259 sql_db = $sphinx_conf{'db'}
260 sql_user = $sphinx_conf{'user'}
261 sql_pass = $sphinx_conf{'pass'}
263 sql_query_pre = SET NAMES utf8
265 SELECT a.id, a.content FROM Attachments a \\
266 JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \\
267 JOIN Tickets t ON txn.ObjectId = t.id \\
268 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
270 sql_query_info = SELECT * FROM Attachments WHERE id=\$id
275 path = $var_path/sphinx/index
286 log = $var_path/sphinx/searchd.log
287 query_log = $var_path/sphinx/query.log
290 pid_file = $var_path/sphinx/searchd.pid
291 max_matches = $maxmatches
295 # For sphinx >= 1.10:
296 binlog_path = $var_path/sphinx/
302 elsif ( $DB{'type'} eq 'Pg' ) {
304 my $table = $OPT{'table'} || prompt(
305 message => "Enter the name of a DB table that will be used to store the Pg tsvector.\n"
306 . "You may either use the existing Attachments table, or create a new\n"
307 . "table. Creating a new table makes initial indexing faster.",
308 default => $DEFAULT{'table'},
309 silent => !$OPT{'ask'},
311 my $column = $OPT{'column'} || prompt(
312 message => 'Enter the name of a column that will be used to store the Pg tsvector:',
313 default => $DEFAULT{'column'},
314 silent => !$OPT{'ask'},
319 if ( lc($table) eq 'attachments' ) {
320 $drop = "ALTER TABLE $table DROP COLUMN $column";
321 push @schema, "ALTER TABLE $table ADD COLUMN $column tsvector";
323 $drop = "DROP TABLE $table";
324 push @schema, split /;\n+/, <<SCHEMA;
325 CREATE TABLE $table (
329 GRANT SELECT, INSERT, UPDATE, DELETE ON $table TO "$DB{user}"
333 my $index_type = lc($OPT{'index-type'} || '');
334 while ( $index_type ne 'gist' and $index_type ne 'gin' ) {
335 $index_type = lc prompt(
336 message => "You may choose between GiST or GIN indexes; the GiST takes less space on\n"
337 . "disk and is faster to update, but is an order of magnitude slower to query.",
339 silent => !$OPT{'ask'},
343 do_error_is_ok( dba_handle() => $drop )
344 unless $OPT{'dryrun'};
345 insert_schema( $_ ) for @schema;
347 insert_data( Table => $table, Column => $column );
349 insert_schema( "CREATE INDEX ${column}_idx ON $table USING $index_type($column)" );
351 print_rt_config( Table => $table, Column => $column );
353 elsif ( $DB{'type'} eq 'Oracle' ) {
355 my $dbah = dba_handle();
356 do_print_error( $dbah => 'GRANT CTXAPP TO '. $DB{'user'} );
357 do_print_error( $dbah => 'GRANT EXECUTE ON CTXSYS.CTX_DDL TO '. $DB{'user'} );
362 type => 'DIRECT_DATASTORE',
365 type => 'AUTO_FILTER',
367 # timeout => 120, # seconds
368 # timeout_type => 'HEURISTIC', # or 'FIXED'
372 type => 'WORLD_LEXER',
375 type => 'BASIC_WORDLIST',
378 fuzzy_match => 'AUTO',
379 # fuzzy_score => undef,
380 # fuzzy_numresults => undef,
381 # substring_index => undef,
382 # prefix_index => undef,
383 # prefix_length_min => undef,
384 # prefix_length_max => undef,
385 # wlidcard_maxterms => undef,
389 type => 'NULL_SECTION_GROUP',
393 type => 'BASIC_STORAGE',
395 R_TABLE_CLAUSE => 'lob (data) store as (cache)',
396 I_INDEX_CLAUSE => 'compress 2',
402 push @params, ora_create_datastore( %{ $PREFERENCES{'datastore'} } );
403 push @params, ora_create_filter( %{ $PREFERENCES{'filter'} } );
404 push @params, ora_create_lexer( %{ $PREFERENCES{'lexer'} } );
405 push @params, ora_create_word_list( %{ $PREFERENCES{'word_list'} } );
406 push @params, ora_create_stop_list();
407 push @params, ora_create_section_group( %{ $PREFERENCES{'section_group'} } );
408 push @params, ora_create_storage( %{ $PREFERENCES{'storage'} } );
410 my $index_params = join "\n", @params;
411 my $index_name = $DEFAULT{prefix} .'index';
412 do_error_is_ok( $dbh => "DROP INDEX $index_name" )
413 unless $OPT{'dryrun'};
415 "CREATE INDEX $index_name ON Attachments(Content)
416 indextype is ctxsys.context parameters('
419 ) unless $OPT{'dryrun'};
421 print_rt_config( IndexName => $index_name );
424 die "Full-text indexes on $DB{type} are not yet supported";
428 my $dbh = $RT::Handle->dbh;
429 my $fts = ($dbh->selectrow_array(<<EOQ))[0];
430 SELECT 1 FROM information_schema.routines WHERE routine_name = 'plainto_tsquery'
435 Your PostgreSQL server does not include full-text support. You will
436 need to upgrade to PostgreSQL version 8.3 or higher to use full-text
445 return if $RT::Handle->CheckSphinxSE;
449 Your MySQL server has not been compiled with the Sphinx storage engine
450 (sphinxse). You will need to recompile MySQL according to the
451 instructions in Sphinx's documentation at
452 http://sphinxsearch.com/docs/current.html#sphinxse-installing
458 sub ora_create_datastore {
459 return sprintf 'datastore %s', ora_create_preference(
465 sub ora_create_filter {
467 $res .= sprintf "format column %s\n", ora_create_format_column();
468 $res .= sprintf 'filter %s', ora_create_preference(
475 sub ora_create_lexer {
476 return sprintf 'lexer %s', ora_create_preference(
482 sub ora_create_word_list {
483 return sprintf 'wordlist %s', ora_create_preference(
489 sub ora_create_stop_list {
490 my $file = shift || 'etc/stopwords/en.txt';
491 return '' unless -e $file;
493 my $name = $DEFAULT{'prefix'} .'stop_list';
494 unless ($OPT{'dryrun'}) {
495 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_stoplist(?); end;', $name );
498 'begin ctx_ddl.create_stoplist(?, ?); end;',
499 undef, $name, 'BASIC_STOPLIST'
502 open( my $fh, '<:utf8', $file )
503 or die "couldn't open file '$file': $!";
504 while ( my $word = <$fh> ) {
507 'begin ctx_ddl.add_stopword(?, ?); end;',
513 return sprintf 'stoplist %s', $name;
516 sub ora_create_section_group {
518 my $name = $DEFAULT{'prefix'} .'section_group';
519 unless ($OPT{'dryrun'}) {
520 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_section_group(?); end;', $name );
522 'begin ctx_ddl.create_section_group(?, ?); end;',
523 undef, $name, $args{'type'}
526 return sprintf 'section group %s', $name;
529 sub ora_create_storage {
530 return sprintf 'storage %s', ora_create_preference(
536 sub ora_create_format_column {
537 my $column_name = 'ContentOracleFormat';
538 return $column_name if $OPT{'dryrun'};
541 undef, undef, uc('Attachments'), uc( $column_name )
545 ALTER TABLE Attachments ADD $column_name VARCHAR2(10)
549 my $detect_format = qq{
550 CREATE OR REPLACE FUNCTION $DEFAULT{prefix}detect_format_simple(
553 encoding IN VARCHAR2,
562 unless ( $OPT{'attachments'} ) {
563 $detect_format .= qq{
564 WHEN fname IS NOT NULL THEN 'ignore'
567 $detect_format .= qq{
568 WHEN type = 'text' THEN 'text'
569 WHEN type = 'text/rtf' THEN 'ignore'
570 WHEN type LIKE 'text/%' THEN 'text'
571 WHEN type LIKE 'message/%' THEN 'text'
577 ora_create_procedure( $detect_format );
581 SET $column_name = $DEFAULT{prefix}detect_format_simple(
583 ContentType, ContentEncoding,
586 WHERE $column_name IS NULL
589 CREATE OR REPLACE TRIGGER $DEFAULT{prefix}set_format
594 :new.$column_name := $DEFAULT{prefix}detect_format_simple(
596 :new.ContentType, :new.ContentEncoding,
604 sub ora_create_preference {
606 my $name = $DEFAULT{'prefix'} . $info{'name'};
607 return $name if $OPT{'dryrun'};
608 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_preference(?); end;', $name );
610 'begin ctx_ddl.create_preference(?, ?); end;',
611 undef, $name, $info{'type'}
613 return $name unless $info{'attributes'};
615 while ( my ($attr, $value) = each %{ $info{'attributes'} } ) {
617 'begin ctx_ddl.set_attribute(?, ?, ?); end;',
618 undef, $name, $attr, $value
625 sub ora_create_procedure {
628 return if $OPT{'dryrun'};
629 my $status = $dbh->do($text, { RaiseError => 0 });
631 # Statement succeeded
634 if ( 6550 != $dbh->err ) {
639 my $msg = $dbh->func( 'plsql_errstr' );
640 die $dbh->errstr if !defined $msg;
646 if ( $DB{'type'} eq 'Oracle' ) {
647 $ENV{'NLS_LANG'} = "AMERICAN_AMERICA.AL32UTF8";
648 $ENV{'NLS_NCHAR'} = "AL32UTF8";
650 my $dsn = do { my $h = new RT::Handle; $h->BuildDSN; $h->DSN };
651 my $dbh = DBI->connect(
652 $dsn, $DB{admin}, $DB{admin_password},
653 { RaiseError => 1, PrintError => 1 },
656 die "Failed to connect to $dsn as user '$DB{admin}': ". $DBI::errstr;
663 local $dbh->{'RaiseError'} = 0;
664 local $dbh->{'PrintError'} = 0;
665 return $dbh->do(shift, undef, @_);
670 local $dbh->{'RaiseError'} = 0;
671 local $dbh->{'PrintError'} = 1;
672 return $dbh->do(shift, undef, @_);
677 return $args{'default'} if $args{'silent'};
680 print $args{'message'};
681 if ( $args{'default'} ) {
682 print "\n[". $args{'default'} .']: ';
690 return $args{'default'} if !$res && $args{'default'};
694 sub verbose { print @_, "\n" if $OPT{verbose} || $OPT{verbose}; 1 }
695 sub debug { print @_, "\n" if $OPT{debug}; 1 }
696 sub error { $RT::Logger->error( @_ ); verbose(@_); 1 }
697 sub warning { $RT::Logger->warning( @_ ); verbose(@_); 1 }
701 RT::Interface::CLI->ShowHelp(
703 Sections => 'NAME|DESCRIPTION',
707 sub print_rt_config {
711 You can now configure RT to use the newly-created full-text index by
712 adding the following to your RT_SiteConfig.pm:
714 Set( %FullTextSearch,
719 $config .= sprintf(" %-10s => '$args{$_}',\n",$_)
720 foreach grep defined $args{$_}, keys %args;
727 my $dbh = dba_handle();
728 my $message = "Going to run the following in the DB:";
734 return if $OPT{'dryrun'};
736 my $res = $dbh->do( $schema );
738 die "Couldn't run DDL query: ". $dbh->errstr;
743 return if $OPT{dryrun};
745 print "Indexing existing data...\n";
747 $ENV{RT_FTS_CONFIG} = JSON::to_json( {Enable => 1, Indexed => 1, @_});
748 system( "$RT::SbinPath/rt-fulltext-indexer", "--all",
749 ($DB{'batch-size'} ? ("--limit", $DB{'batch-size'}) : ()));
754 rt-setup-fulltext-index - Create indexes for full text search
758 This script creates the appropriate tables, columns, functions, and / or
759 views necessary for full-text searching for your database type. It will
760 drop any existing indexes in the process.
762 Please read F<docs/full_text_indexing.pod> for complete documentation on
763 full-text indexing for your database type.
765 If you have a non-standard database administrator user or password, you
766 may use the C<--dba> and C<--dba-password> parameters to set them
769 rt-setup-fulltext-index --dba sysdba --dba-password 'secret'
771 To test what will happen without running any DDL, pass the C<--dryrun>
774 The Oracle index determines which content-types it will index at
775 creation time. By default, textual message bodies and textual uploaded
776 attachments (attachments with filenames) are indexed; to ignore textual
777 attachments, pass the C<--no-attachments> flag when the index is
783 Ruslan Zakirov E<lt>ruz@bestpractical.comE<gt>,
784 Alex Vandiver E<lt>alexmv@bestpractical.comE<gt>