2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2014 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
56 my @libs = ("@RT_LIB_PATH@", "@LOCAL_LIB_PATH@");
60 unless ( File::Spec->file_name_is_absolute($lib) ) {
62 if ( File::Spec->file_name_is_absolute(__FILE__) ) {
63 $bin_path = ( File::Spec->splitpath(__FILE__) )[1];
68 $bin_path = $FindBin::Bin;
71 $lib = File::Spec->catfile( $bin_path, File::Spec->updir, $lib );
82 use RT::Interface::CLI ();
85 type => scalar RT->Config->Get('DatabaseType'),
86 user => scalar RT->Config->Get('DatabaseUser'),
88 admin_password => undef,
99 if ( $DB{'type'} eq 'Pg' ) {
101 table => 'Attachments',
102 column => 'ContentIndex',
105 elsif ( $DB{'type'} eq 'mysql' ) {
107 table => 'AttachmentsIndex',
110 elsif ( $DB{'type'} eq 'Oracle' ) {
116 use Getopt::Long qw(GetOptions);
118 'h|help!' => \$OPT{'help'},
119 'ask!' => \$OPT{'ask'},
120 'dry-run!' => \$OPT{'dryrun'},
121 'attachments!' => \$OPT{'attachments'},
123 'table=s' => \$OPT{'table'},
124 'column=s' => \$OPT{'column'},
125 'url=s' => \$OPT{'url'},
126 'maxmatches=i' => \$OPT{'maxmatches'},
127 'index-type=s' => \$OPT{'index-type'},
129 'dba=s' => \$DB{'admin'},
130 'dba-password=s' => \$DB{'admin_password'},
133 if ( $OPT{'help'} || (!$DB{'admin'} && $DB{'type'} eq 'Oracle' ) ) {
134 show_help( !$OPT{'help'} );
137 my $dbh = $RT::Handle->dbh;
138 $dbh->{'RaiseError'} = 1;
139 $dbh->{'PrintError'} = 1;
141 if ( $DB{'type'} eq 'mysql' ) {
143 my $table = $OPT{'table'} || prompt(
144 message => "Enter name of a new MySQL table that will be used to connect to the\n"
146 default => $DEFAULT{'table'},
147 silent => !$OPT{'ask'},
150 my $url = 'sphinx://localhost:3312/rt';
151 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
152 $url = 'sphinx://127.0.0.1:3312/rt'
153 if $version and $version =~ /^(\d+\.\d+)/ and $1 >= 5.5;
155 $url = $OPT{'url'} || prompt(
156 message => "Enter URL of the sphinx search server; this should be of the form\n"
157 . "sphinx://<server>:<port>/<index name>",
159 silent => !$OPT{'ask'},
161 my $maxmatches = $OPT{'maxmatches'} || prompt(
162 message => "Maximum number of matches to return; this is the maximum number of\n"
163 . "attachment records returned by the search, not the maximum number\n"
164 . "of tickets. Both your RT_SiteConfig.pm and your sphinx.conf must\n"
165 . "agree on this value. Larger values cause your Sphinx server to\n"
166 . "consume more memory and CPU time per query.",
168 silent => !$OPT{'ask'},
172 CREATE TABLE $table (
173 id INTEGER UNSIGNED NOT NULL,
174 weight INTEGER NOT NULL,
175 query VARCHAR(3072) NOT NULL,
177 ) ENGINE=SPHINX CONNECTION="$url" CHARACTER SET utf8
180 do_error_is_ok( dba_handle() => "DROP TABLE $table" )
181 unless $OPT{'dryrun'};
182 insert_schema( $schema );
184 print_rt_config( Table => $table, MaxMatches => $maxmatches );
187 my $urlo = URI->new( $url );
188 my ($host, $port) = split /:/, $urlo->authority;
189 my $index = $urlo->path;
192 my $var_path = $RT::VarPath;
194 my %sphinx_conf = ();
195 $sphinx_conf{'host'} = RT->Config->Get('DatabaseHost');
196 $sphinx_conf{'db'} = RT->Config->Get('DatabaseName');
197 $sphinx_conf{'user'} = RT->Config->Get('DatabaseUser');
198 $sphinx_conf{'pass'} = RT->Config->Get('DatabasePassword');
202 Below is a simple Sphinx configuration which can be used to index all
203 text/plain attachments in your database. This configuration is not
204 ideal; you should read the Sphinx documentation to understand how to
205 configure it to better suit your needs.
210 sql_host = $sphinx_conf{'host'}
211 sql_db = $sphinx_conf{'db'}
212 sql_user = $sphinx_conf{'user'}
213 sql_pass = $sphinx_conf{'pass'}
215 sql_query_pre = SET NAMES utf8
217 SELECT a.id, a.content FROM Attachments a \\
218 JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \\
219 JOIN Tickets t ON txn.ObjectId = t.id \\
220 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
222 sql_query_info = SELECT * FROM Attachments WHERE id=\$id
227 path = $var_path/sphinx/index
238 log = $var_path/sphinx/searchd.log
239 query_log = $var_path/sphinx/query.log
242 pid_file = $var_path/sphinx/searchd.pid
243 max_matches = $maxmatches
252 elsif ( $DB{'type'} eq 'Pg' ) {
254 my $table = $OPT{'table'} || prompt(
255 message => "Enter the name of a DB table that will be used to store the Pg tsvector.\n"
256 . "You may either use the existing Attachments table, or create a new\n"
258 default => $DEFAULT{'table'},
259 silent => !$OPT{'ask'},
261 my $column = $OPT{'column'} || prompt(
262 message => 'Enter the name of a column that will be used to store the Pg tsvector:',
263 default => $DEFAULT{'column'},
264 silent => !$OPT{'ask'},
269 if ( lc($table) eq 'attachments' ) {
270 $drop = "ALTER TABLE $table DROP COLUMN $column";
271 $schema = "ALTER TABLE $table ADD COLUMN $column tsvector";
273 $drop = "DROP TABLE $table";
274 $schema = "CREATE TABLE $table ( "
275 ."id INTEGER NOT NULL,"
276 ."$column tsvector )";
279 my $index_type = lc($OPT{'index-type'} || '');
280 while ( $index_type ne 'gist' and $index_type ne 'gin' ) {
281 $index_type = lc prompt(
282 message => "You may choose between GiST or GIN indexes; the former is several times\n"
283 . "slower to search, but takes less space on disk and is faster to update.",
285 silent => !$OPT{'ask'},
289 do_error_is_ok( dba_handle() => $drop )
290 unless $OPT{'dryrun'};
291 insert_schema( $schema );
292 insert_schema("CREATE INDEX ${column}_idx ON $table USING $index_type($column)");
294 print_rt_config( Table => $table, Column => $column );
296 elsif ( $DB{'type'} eq 'Oracle' ) {
298 my $dbah = dba_handle();
299 do_print_error( $dbah => 'GRANT CTXAPP TO '. $DB{'user'} );
300 do_print_error( $dbah => 'GRANT EXECUTE ON CTXSYS.CTX_DDL TO '. $DB{'user'} );
305 type => 'DIRECT_DATASTORE',
308 type => 'AUTO_FILTER',
310 # timeout => 120, # seconds
311 # timeout_type => 'HEURISTIC', # or 'FIXED'
315 type => 'WORLD_LEXER',
318 type => 'BASIC_WORDLIST',
321 fuzzy_match => 'AUTO',
322 # fuzzy_score => undef,
323 # fuzzy_numresults => undef,
324 # substring_index => undef,
325 # prefix_index => undef,
326 # prefix_length_min => undef,
327 # prefix_length_max => undef,
328 # wlidcard_maxterms => undef,
332 type => 'NULL_SECTION_GROUP',
336 type => 'BASIC_STORAGE',
338 R_TABLE_CLAUSE => 'lob (data) store as (cache)',
339 I_INDEX_CLAUSE => 'compress 2',
345 push @params, ora_create_datastore( %{ $PREFERENCES{'datastore'} } );
346 push @params, ora_create_filter( %{ $PREFERENCES{'filter'} } );
347 push @params, ora_create_lexer( %{ $PREFERENCES{'lexer'} } );
348 push @params, ora_create_word_list( %{ $PREFERENCES{'word_list'} } );
349 push @params, ora_create_stop_list();
350 push @params, ora_create_section_group( %{ $PREFERENCES{'section_group'} } );
351 push @params, ora_create_storage( %{ $PREFERENCES{'storage'} } );
353 my $index_params = join "\n", @params;
354 my $index_name = $DEFAULT{prefix} .'index';
355 do_error_is_ok( $dbh => "DROP INDEX $index_name" )
356 unless $OPT{'dryrun'};
358 "CREATE INDEX $index_name ON Attachments(Content)
359 indextype is ctxsys.context parameters('
362 ) unless $OPT{'dryrun'};
364 print_rt_config( IndexName => $index_name );
367 die "Full-text indexes on $DB{type} are not yet supported";
371 my $dbh = $RT::Handle->dbh;
372 my $fts = ($dbh->selectrow_array(<<EOQ))[0];
373 SELECT 1 FROM information_schema.routines WHERE routine_name = 'plainto_tsquery'
378 Your PostgreSQL server does not include full-text support. You will
379 need to upgrade to PostgreSQL version 8.3 or higher to use full-text
388 return if $RT::Handle->CheckSphinxSE;
392 Your MySQL server has not been compiled with the Sphinx storage engine
393 (sphinxse). You will need to recompile MySQL according to the
394 instructions in Sphinx's documentation at
395 http://sphinxsearch.com/docs/current.html#sphinxse-installing
401 sub ora_create_datastore {
402 return sprintf 'datastore %s', ora_create_preference(
408 sub ora_create_filter {
410 $res .= sprintf "format column %s\n", ora_create_format_column();
411 $res .= sprintf 'filter %s', ora_create_preference(
418 sub ora_create_lexer {
419 return sprintf 'lexer %s', ora_create_preference(
425 sub ora_create_word_list {
426 return sprintf 'wordlist %s', ora_create_preference(
432 sub ora_create_stop_list {
433 my $file = shift || 'etc/stopwords/en.txt';
434 return '' unless -e $file;
436 my $name = $DEFAULT{'prefix'} .'stop_list';
437 unless ($OPT{'dryrun'}) {
438 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_stoplist(?); end;', $name );
441 'begin ctx_ddl.create_stoplist(?, ?); end;',
442 undef, $name, 'BASIC_STOPLIST'
445 open( my $fh, '<:utf8', $file )
446 or die "couldn't open file '$file': $!";
447 while ( my $word = <$fh> ) {
450 'begin ctx_ddl.add_stopword(?, ?); end;',
456 return sprintf 'stoplist %s', $name;
459 sub ora_create_section_group {
461 my $name = $DEFAULT{'prefix'} .'section_group';
462 unless ($OPT{'dryrun'}) {
463 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_section_group(?); end;', $name );
465 'begin ctx_ddl.create_section_group(?, ?); end;',
466 undef, $name, $args{'type'}
469 return sprintf 'section group %s', $name;
472 sub ora_create_storage {
473 return sprintf 'storage %s', ora_create_preference(
479 sub ora_create_format_column {
480 my $column_name = 'ContentOracleFormat';
481 return $column_name if $OPT{'dryrun'};
484 undef, undef, uc('Attachments'), uc( $column_name )
488 ALTER TABLE Attachments ADD $column_name VARCHAR2(10)
492 my $detect_format = qq{
493 CREATE OR REPLACE FUNCTION $DEFAULT{prefix}detect_format_simple(
496 encoding IN VARCHAR2,
505 unless ( $OPT{'attachments'} ) {
506 $detect_format .= qq{
507 WHEN fname IS NOT NULL THEN 'ignore'
510 $detect_format .= qq{
511 WHEN type = 'text' THEN 'text'
512 WHEN type = 'text/rtf' THEN 'ignore'
513 WHEN type LIKE 'text/%' THEN 'text'
514 WHEN type LIKE 'message/%' THEN 'text'
520 ora_create_procedure( $detect_format );
524 SET $column_name = $DEFAULT{prefix}detect_format_simple(
526 ContentType, ContentEncoding,
529 WHERE $column_name IS NULL
532 CREATE OR REPLACE TRIGGER $DEFAULT{prefix}set_format
537 :new.$column_name := $DEFAULT{prefix}detect_format_simple(
539 :new.ContentType, :new.ContentEncoding,
547 sub ora_create_preference {
549 my $name = $DEFAULT{'prefix'} . $info{'name'};
550 return $name if $OPT{'dryrun'};
551 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_preference(?); end;', $name );
553 'begin ctx_ddl.create_preference(?, ?); end;',
554 undef, $name, $info{'type'}
556 return $name unless $info{'attributes'};
558 while ( my ($attr, $value) = each %{ $info{'attributes'} } ) {
560 'begin ctx_ddl.set_attribute(?, ?, ?); end;',
561 undef, $name, $attr, $value
568 sub ora_create_procedure {
571 return if $OPT{'dryrun'};
572 my $status = $dbh->do($text, { RaiseError => 0 });
574 # Statement succeeded
577 if ( 6550 != $dbh->err ) {
582 my $msg = $dbh->func( 'plsql_errstr' );
583 die $dbh->errstr if !defined $msg;
589 if ( $DB{'type'} eq 'Oracle' ) {
590 $ENV{'NLS_LANG'} = "AMERICAN_AMERICA.AL32UTF8";
591 $ENV{'NLS_NCHAR'} = "AL32UTF8";
593 my $dsn = do { my $h = new RT::Handle; $h->BuildDSN; $h->DSN };
594 my $dbh = DBI->connect(
595 $dsn, $DB{admin}, $DB{admin_password},
596 { RaiseError => 1, PrintError => 1 },
599 die "Failed to connect to $dsn as user '$DB{admin}': ". $DBI::errstr;
606 local $dbh->{'RaiseError'} = 0;
607 local $dbh->{'PrintError'} = 0;
608 return $dbh->do(shift, undef, @_);
613 local $dbh->{'RaiseError'} = 0;
614 local $dbh->{'PrintError'} = 1;
615 return $dbh->do(shift, undef, @_);
620 return $args{'default'} if $args{'silent'};
623 print $args{'message'};
624 if ( $args{'default'} ) {
625 print "\n[". $args{'default'} .']: ';
633 return $args{'default'} if !$res && $args{'default'};
637 sub verbose { print @_, "\n" if $OPT{verbose} || $OPT{verbose}; 1 }
638 sub debug { print @_, "\n" if $OPT{debug}; 1 }
639 sub error { $RT::Logger->error( @_ ); verbose(@_); 1 }
640 sub warning { $RT::Logger->warning( @_ ); verbose(@_); 1 }
644 RT::Interface::CLI->ShowHelp(
646 Sections => 'NAME|DESCRIPTION',
650 sub print_rt_config {
654 You can now configure RT to use the newly-created full-text index by
655 adding the following to your RT_SiteConfig.pm:
657 Set( %FullTextSearch,
662 $config .= sprintf(" %-10s => '$args{$_}',\n",$_)
663 foreach grep defined $args{$_}, keys %args;
670 my $dbh = dba_handle();
671 my $message = "Going to run the following in the DB:";
677 return if $OPT{'dryrun'};
679 my $res = $dbh->do( $schema );
681 die "Couldn't run DDL query: ". $dbh->errstr;
687 rt-setup-fulltext-index - Create indexes for full text search
691 This script creates the appropriate tables, columns, functions, and / or
692 views necessary for full-text searching for your database type. It will
693 drop any existing indexes in the process.
695 Please read F<docs/full_text_indexing.pod> for complete documentation on
696 full-text indexing for your database type.
698 If you have a non-standard database administrator user or password, you
699 may use the C<--dba> and C<--dba-password> parameters to set them
702 rt-setup-fulltext-index --dba sysdba --dba-password 'secret'
704 To test what will happen without running any DDL, pass the C<--dryrun>
707 The Oracle index determines which content-types it will index at
708 creation time. By default, textual message bodies and textual uploaded
709 attachments (attachments with filenames) are indexed; to ignore textual
710 attachments, pass the C<--no-attachments> flag when the index is
716 Ruslan Zakirov E<lt>ruz@bestpractical.comE<gt>,
717 Alex Vandiver E<lt>alexmv@bestpractical.comE<gt>