2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2013 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 = ("/opt/rt3/lib", "/opt/rt3/local/lib");
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'},
149 my $url = $OPT{'url'} || prompt(
150 message => "Enter URL of the sphinx search server; this should be of the form\n"
151 . "sphinx://<server>:<port>/<index name>",
152 default => 'sphinx://localhost:3312/rt',
153 silent => !$OPT{'ask'},
155 my $maxmatches = $OPT{'maxmatches'} || prompt(
156 message => "Maximum number of matches to return; this is the maximum number of\n"
157 . "attachment records returned by the search, not the maximum number\n"
158 . "of tickets. Both your RT_SiteConfig.pm and your sphinx.conf must\n"
159 . "agree on this value. Larger values cause your Sphinx server to\n"
160 . "consume more memory and CPU time per query.",
162 silent => !$OPT{'ask'},
166 CREATE TABLE $table (
167 id INTEGER UNSIGNED NOT NULL,
168 weight INTEGER NOT NULL,
169 query VARCHAR(3072) NOT NULL,
171 ) ENGINE=SPHINX CONNECTION="$url" CHARACTER SET utf8
174 do_error_is_ok( dba_handle() => "DROP TABLE $table" )
175 unless $OPT{'dryrun'};
176 insert_schema( $schema );
178 print_rt_config( Table => $table, MaxMatches => $maxmatches );
181 my $urlo = URI->new( $url );
182 my ($host, $port) = split /:/, $urlo->authority;
183 my $index = $urlo->path;
186 my $var_path = $RT::VarPath;
188 my %sphinx_conf = ();
189 $sphinx_conf{'host'} = RT->Config->Get('DatabaseHost');
190 $sphinx_conf{'db'} = RT->Config->Get('DatabaseName');
191 $sphinx_conf{'user'} = RT->Config->Get('DatabaseUser');
192 $sphinx_conf{'pass'} = RT->Config->Get('DatabasePassword');
196 Below is a simple Sphinx configuration which can be used to index all
197 text/plain attachments in your database. This configuration is not
198 ideal; you should read the Sphinx documentation to understand how to
199 configure it to better suit your needs.
204 sql_host = $sphinx_conf{'host'}
205 sql_db = $sphinx_conf{'db'}
206 sql_user = $sphinx_conf{'user'}
207 sql_pass = $sphinx_conf{'pass'}
209 sql_query_pre = SET NAMES utf8
211 SELECT a.id, a.content FROM Attachments a \\
212 JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \\
213 JOIN Tickets t ON txn.ObjectId = t.id \\
214 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
216 sql_query_info = SELECT * FROM Attachments WHERE id=\$id
221 path = $var_path/sphinx/index
232 log = $var_path/sphinx/searchd.log
233 query_log = $var_path/sphinx/query.log
236 pid_file = $var_path/sphinx/searchd.pid
237 max_matches = $maxmatches
246 elsif ( $DB{'type'} eq 'Pg' ) {
248 my $table = $OPT{'table'} || prompt(
249 message => "Enter the name of a DB table that will be used to store the Pg tsvector.\n"
250 . "You may either use the existing Attachments table, or create a new\n"
252 default => $DEFAULT{'table'},
253 silent => !$OPT{'ask'},
255 my $column = $OPT{'column'} || prompt(
256 message => 'Enter the name of a column that will be used to store the Pg tsvector:',
257 default => $DEFAULT{'column'},
258 silent => !$OPT{'ask'},
263 if ( lc($table) eq 'attachments' ) {
264 $drop = "ALTER TABLE $table DROP COLUMN $column";
265 $schema = "ALTER TABLE $table ADD COLUMN $column tsvector";
267 $drop = "DROP TABLE $table";
268 $schema = "CREATE TABLE $table ( "
269 ."id INTEGER NOT NULL,"
270 ."$column tsvector )";
273 my $index_type = lc($OPT{'index-type'} || '');
274 while ( $index_type ne 'gist' and $index_type ne 'gin' ) {
275 $index_type = lc prompt(
276 message => "You may choose between GiST or GIN indexes; the former is several times\n"
277 . "slower to search, but takes less space on disk and is faster to update.",
279 silent => !$OPT{'ask'},
283 do_error_is_ok( dba_handle() => $drop )
284 unless $OPT{'dryrun'};
285 insert_schema( $schema );
286 insert_schema("CREATE INDEX ${column}_idx ON $table USING $index_type($column)");
288 print_rt_config( Table => $table, Column => $column );
290 elsif ( $DB{'type'} eq 'Oracle' ) {
292 my $dbah = dba_handle();
293 do_print_error( $dbah => 'GRANT CTXAPP TO '. $DB{'user'} );
294 do_print_error( $dbah => 'GRANT EXECUTE ON CTXSYS.CTX_DDL TO '. $DB{'user'} );
299 type => 'DIRECT_DATASTORE',
302 type => 'AUTO_FILTER',
304 # timeout => 120, # seconds
305 # timeout_type => 'HEURISTIC', # or 'FIXED'
309 type => 'WORLD_LEXER',
312 type => 'BASIC_WORDLIST',
315 fuzzy_match => 'AUTO',
316 # fuzzy_score => undef,
317 # fuzzy_numresults => undef,
318 # substring_index => undef,
319 # prefix_index => undef,
320 # prefix_length_min => undef,
321 # prefix_length_max => undef,
322 # wlidcard_maxterms => undef,
326 type => 'NULL_SECTION_GROUP',
330 type => 'BASIC_STORAGE',
332 R_TABLE_CLAUSE => 'lob (data) store as (cache)',
333 I_INDEX_CLAUSE => 'compress 2',
339 push @params, ora_create_datastore( %{ $PREFERENCES{'datastore'} } );
340 push @params, ora_create_filter( %{ $PREFERENCES{'filter'} } );
341 push @params, ora_create_lexer( %{ $PREFERENCES{'lexer'} } );
342 push @params, ora_create_word_list( %{ $PREFERENCES{'word_list'} } );
343 push @params, ora_create_stop_list();
344 push @params, ora_create_section_group( %{ $PREFERENCES{'section_group'} } );
345 push @params, ora_create_storage( %{ $PREFERENCES{'storage'} } );
347 my $index_params = join "\n", @params;
348 my $index_name = $DEFAULT{prefix} .'index';
349 do_error_is_ok( $dbh => "DROP INDEX $index_name" )
350 unless $OPT{'dryrun'};
352 "CREATE INDEX $index_name ON Attachments(Content)
353 indextype is ctxsys.context parameters('
356 ) unless $OPT{'dryrun'};
358 print_rt_config( IndexName => $index_name );
361 die "Full-text indexes on $DB{type} are not yet supported";
365 my $dbh = $RT::Handle->dbh;
366 my $fts = ($dbh->selectrow_array(<<EOQ))[0];
367 SELECT 1 FROM information_schema.routines WHERE routine_name = 'plainto_tsquery'
372 Your PostgreSQL server does not include full-text support. You will
373 need to upgrade to PostgreSQL version 8.3 or higher to use full-text
382 return if $RT::Handle->CheckSphinxSE;
386 Your MySQL server has not been compiled with the Sphinx storage engine
387 (sphinxse). You will need to recompile MySQL according to the
388 instructions in Sphinx's documentation at
389 http://sphinxsearch.com/docs/current.html#sphinxse-installing
395 sub ora_create_datastore {
396 return sprintf 'datastore %s', ora_create_preference(
402 sub ora_create_filter {
404 $res .= sprintf "format column %s\n", ora_create_format_column();
405 $res .= sprintf 'filter %s', ora_create_preference(
412 sub ora_create_lexer {
413 return sprintf 'lexer %s', ora_create_preference(
419 sub ora_create_word_list {
420 return sprintf 'wordlist %s', ora_create_preference(
426 sub ora_create_stop_list {
427 my $file = shift || 'etc/stopwords/en.txt';
428 return '' unless -e $file;
430 my $name = $DEFAULT{'prefix'} .'stop_list';
431 unless ($OPT{'dryrun'}) {
432 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_stoplist(?); end;', $name );
435 'begin ctx_ddl.create_stoplist(?, ?); end;',
436 undef, $name, 'BASIC_STOPLIST'
439 open( my $fh, '<:utf8', $file )
440 or die "couldn't open file '$file': $!";
441 while ( my $word = <$fh> ) {
444 'begin ctx_ddl.add_stopword(?, ?); end;',
450 return sprintf 'stoplist %s', $name;
453 sub ora_create_section_group {
455 my $name = $DEFAULT{'prefix'} .'section_group';
456 unless ($OPT{'dryrun'}) {
457 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_section_group(?); end;', $name );
459 'begin ctx_ddl.create_section_group(?, ?); end;',
460 undef, $name, $args{'type'}
463 return sprintf 'section group %s', $name;
466 sub ora_create_storage {
467 return sprintf 'storage %s', ora_create_preference(
473 sub ora_create_format_column {
474 my $column_name = 'ContentOracleFormat';
475 return $column_name if $OPT{'dryrun'};
478 undef, undef, uc('Attachments'), uc( $column_name )
482 ALTER TABLE Attachments ADD $column_name VARCHAR2(10)
486 my $detect_format = qq{
487 CREATE OR REPLACE FUNCTION $DEFAULT{prefix}detect_format_simple(
490 encoding IN VARCHAR2,
499 unless ( $OPT{'attachments'} ) {
500 $detect_format .= qq{
501 WHEN fname IS NOT NULL THEN 'ignore'
504 $detect_format .= qq{
505 WHEN type = 'text' THEN 'text'
506 WHEN type = 'text/rtf' THEN 'ignore'
507 WHEN type LIKE 'text/%' THEN 'text'
508 WHEN type LIKE 'message/%' THEN 'text'
514 ora_create_procedure( $detect_format );
518 SET $column_name = $DEFAULT{prefix}detect_format_simple(
520 ContentType, ContentEncoding,
523 WHERE $column_name IS NULL
526 CREATE OR REPLACE TRIGGER $DEFAULT{prefix}set_format
531 :new.$column_name := $DEFAULT{prefix}detect_format_simple(
533 :new.ContentType, :new.ContentEncoding,
541 sub ora_create_preference {
543 my $name = $DEFAULT{'prefix'} . $info{'name'};
544 return $name if $OPT{'dryrun'};
545 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_preference(?); end;', $name );
547 'begin ctx_ddl.create_preference(?, ?); end;',
548 undef, $name, $info{'type'}
550 return $name unless $info{'attributes'};
552 while ( my ($attr, $value) = each %{ $info{'attributes'} } ) {
554 'begin ctx_ddl.set_attribute(?, ?, ?); end;',
555 undef, $name, $attr, $value
562 sub ora_create_procedure {
565 return if $OPT{'dryrun'};
566 my $status = $dbh->do($text, { RaiseError => 0 });
568 # Statement succeeded
571 if ( 6550 != $dbh->err ) {
576 my $msg = $dbh->func( 'plsql_errstr' );
577 die $dbh->errstr if !defined $msg;
583 if ( $DB{'type'} eq 'Oracle' ) {
584 $ENV{'NLS_LANG'} = "AMERICAN_AMERICA.AL32UTF8";
585 $ENV{'NLS_NCHAR'} = "AL32UTF8";
587 my $dsn = do { my $h = new RT::Handle; $h->BuildDSN; $h->DSN };
588 my $dbh = DBI->connect(
589 $dsn, $DB{admin}, $DB{admin_password},
590 { RaiseError => 1, PrintError => 1 },
593 die "Failed to connect to $dsn as user '$DB{admin}': ". $DBI::errstr;
600 local $dbh->{'RaiseError'} = 0;
601 local $dbh->{'PrintError'} = 0;
602 return $dbh->do(shift, undef, @_);
607 local $dbh->{'RaiseError'} = 0;
608 local $dbh->{'PrintError'} = 1;
609 return $dbh->do(shift, undef, @_);
614 return $args{'default'} if $args{'silent'};
617 print $args{'message'};
618 if ( $args{'default'} ) {
619 print "\n[". $args{'default'} .']: ';
627 return $args{'default'} if !$res && $args{'default'};
631 sub verbose { print @_, "\n" if $OPT{verbose} || $OPT{verbose}; 1 }
632 sub debug { print @_, "\n" if $OPT{debug}; 1 }
633 sub error { $RT::Logger->error( @_ ); verbose(@_); 1 }
634 sub warning { $RT::Logger->warning( @_ ); verbose(@_); 1 }
638 RT::Interface::CLI->ShowHelp(
640 Sections => 'NAME|DESCRIPTION',
644 sub print_rt_config {
648 You can now configure RT to use the newly-created full-text index by
649 adding the following to your RT_SiteConfig.pm:
651 Set( %FullTextSearch,
656 $config .= sprintf(" %-10s => '$args{$_}',\n",$_)
657 foreach grep defined $args{$_}, keys %args;
664 my $dbh = dba_handle();
665 my $message = "Going to run the following in the DB:";
671 return if $OPT{'dryrun'};
673 my $res = $dbh->do( $schema );
675 die "Couldn't run DDL query: ". $dbh->errstr;
681 rt-setup-fulltext-index - Create indexes for full text search
685 This script creates the appropriate tables, columns, functions, and / or
686 views necessary for full-text searching for your database type. It will
687 drop any existing indexes in the process.
689 Please read F<docs/full_text_indexing.pod> for complete documentation on
690 full-text indexing for your database type.
692 If you have a non-standard database administrator user or password, you
693 may use the C<--dba> and C<--dba-password> parameters to set them
696 rt-setup-fulltext-index --dba sysdba --dba-password 'secret'
698 To test what will happen without running any DDL, pass the C<--dryrun>
701 The Oracle index determines which content-types it will index at
702 creation time. By default, textual message bodies and textual uploaded
703 attachments (attachments with filenames) are indexed; to ignore textual
704 attachments, pass the C<--no-attachments> flag when the index is
710 Ruslan Zakirov E<lt>ruz@bestpractical.comE<gt>,
711 Alex Vandiver E<lt>alexmv@bestpractical.comE<gt>