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
54 BEGIN { # BEGIN RT CMD BOILERPLATE
57 my @libs = ("lib", "local/lib");
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 => 'Attachments',
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'},
126 if ( $OPT{'help'} || (!$DB{'admin'} && $DB{'type'} eq 'Oracle' ) ) {
127 show_help( !$OPT{'help'} );
130 my $dbh = $RT::Handle->dbh;
131 $dbh->{'RaiseError'} = 1;
132 $dbh->{'PrintError'} = 1;
134 if ( $DB{'type'} eq 'mysql' ) {
136 my $table = $OPT{'table'} || prompt(
137 message => "Enter name of a new MySQL table that will be used to connect to the\n"
139 default => $DEFAULT{'table'},
140 silent => !$OPT{'ask'},
143 my $url = 'sphinx://localhost:3312/rt';
144 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
145 $url = 'sphinx://127.0.0.1:3312/rt'
146 if $version and $version =~ /^(\d+\.\d+)/ and $1 >= 5.5;
148 $url = $OPT{'url'} || prompt(
149 message => "Enter URL of the sphinx search server; this should be of the form\n"
150 . "sphinx://<server>:<port>/<index name>",
152 silent => !$OPT{'ask'},
154 my $maxmatches = $OPT{'maxmatches'} || prompt(
155 message => "Maximum number of matches to return; this is the maximum number of\n"
156 . "attachment records returned by the search, not the maximum number\n"
157 . "of tickets. Both your RT_SiteConfig.pm and your sphinx.conf must\n"
158 . "agree on this value. Larger values cause your Sphinx server to\n"
159 . "consume more memory and CPU time per query.",
161 silent => !$OPT{'ask'},
165 CREATE TABLE $table (
167 weight INTEGER NOT NULL,
168 query VARCHAR(3072) NOT NULL,
170 ) ENGINE=SPHINX CONNECTION="$url" CHARACTER SET utf8
173 do_error_is_ok( dba_handle() => "DROP TABLE $table" )
174 unless $OPT{'dryrun'};
175 insert_schema( $schema );
177 print_rt_config( Table => $table, MaxMatches => $maxmatches );
180 my $urlo = URI->new( $url );
181 my ($host, $port) = split /:/, $urlo->authority;
182 my $index = $urlo->path;
185 my $var_path = $RT::VarPath;
187 my %sphinx_conf = ();
188 $sphinx_conf{'host'} = RT->Config->Get('DatabaseHost');
189 $sphinx_conf{'db'} = RT->Config->Get('DatabaseName');
190 $sphinx_conf{'user'} = RT->Config->Get('DatabaseUser');
191 $sphinx_conf{'pass'} = RT->Config->Get('DatabasePassword');
195 Below is a simple Sphinx configuration which can be used to index all
196 text/plain attachments in your database. This configuration is not
197 ideal; you should read the Sphinx documentation to understand how to
198 configure it to better suit your needs.
203 sql_host = $sphinx_conf{'host'}
204 sql_db = $sphinx_conf{'db'}
205 sql_user = $sphinx_conf{'user'}
206 sql_pass = $sphinx_conf{'pass'}
208 sql_query_pre = SET NAMES utf8
210 SELECT a.id, a.content FROM Attachments a \\
211 JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \\
212 JOIN Tickets t ON txn.ObjectId = t.id \\
213 WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'
215 sql_query_info = SELECT * FROM Attachments WHERE id=\$id
220 path = $var_path/sphinx/index
231 log = $var_path/sphinx/searchd.log
232 query_log = $var_path/sphinx/query.log
235 pid_file = $var_path/sphinx/searchd.pid
236 max_matches = $maxmatches
245 elsif ( $DB{'type'} eq 'Pg' ) {
247 my $table = $OPT{'table'} || prompt(
248 message => "Enter the name of a DB table that will be used to store the Pg tsvector.\n"
249 . "You may either use the existing Attachments table, or create a new\n"
251 default => $DEFAULT{'table'},
252 silent => !$OPT{'ask'},
254 my $column = $OPT{'column'} || prompt(
255 message => 'Enter the name of a column that will be used to store the Pg tsvector:',
256 default => $DEFAULT{'column'},
257 silent => !$OPT{'ask'},
262 if ( lc($table) eq 'attachments' ) {
263 $drop = "ALTER TABLE $table DROP COLUMN $column";
264 $schema = "ALTER TABLE $table ADD COLUMN $column tsvector";
266 $drop = "DROP TABLE $table";
267 $schema = "CREATE TABLE $table ( "
268 ."id INTEGER NOT NULL,"
269 ."$column tsvector )";
272 my $index_type = lc($OPT{'index-type'} || '');
273 while ( $index_type ne 'gist' and $index_type ne 'gin' ) {
274 $index_type = lc prompt(
275 message => "You may choose between GiST or GIN indexes; the former is several times\n"
276 . "slower to search, but takes less space on disk and is faster to update.",
278 silent => !$OPT{'ask'},
282 do_error_is_ok( dba_handle() => $drop )
283 unless $OPT{'dryrun'};
284 insert_schema( $schema );
285 insert_schema("CREATE INDEX ${column}_idx ON $table USING $index_type($column)");
287 print_rt_config( Table => $table, Column => $column );
289 elsif ( $DB{'type'} eq 'Oracle' ) {
291 my $dbah = dba_handle();
292 do_print_error( $dbah => 'GRANT CTXAPP TO '. $DB{'user'} );
293 do_print_error( $dbah => 'GRANT EXECUTE ON CTXSYS.CTX_DDL TO '. $DB{'user'} );
298 type => 'DIRECT_DATASTORE',
301 type => 'AUTO_FILTER',
303 # timeout => 120, # seconds
304 # timeout_type => 'HEURISTIC', # or 'FIXED'
308 type => 'WORLD_LEXER',
311 type => 'BASIC_WORDLIST',
314 fuzzy_match => 'AUTO',
315 # fuzzy_score => undef,
316 # fuzzy_numresults => undef,
317 # substring_index => undef,
318 # prefix_index => undef,
319 # prefix_length_min => undef,
320 # prefix_length_max => undef,
321 # wlidcard_maxterms => undef,
325 type => 'NULL_SECTION_GROUP',
329 type => 'BASIC_STORAGE',
331 R_TABLE_CLAUSE => 'lob (data) store as (cache)',
332 I_INDEX_CLAUSE => 'compress 2',
338 push @params, ora_create_datastore( %{ $PREFERENCES{'datastore'} } );
339 push @params, ora_create_filter( %{ $PREFERENCES{'filter'} } );
340 push @params, ora_create_lexer( %{ $PREFERENCES{'lexer'} } );
341 push @params, ora_create_word_list( %{ $PREFERENCES{'word_list'} } );
342 push @params, ora_create_stop_list();
343 push @params, ora_create_section_group( %{ $PREFERENCES{'section_group'} } );
344 push @params, ora_create_storage( %{ $PREFERENCES{'storage'} } );
346 my $index_params = join "\n", @params;
347 my $index_name = $DEFAULT{prefix} .'index';
348 do_error_is_ok( $dbh => "DROP INDEX $index_name" )
349 unless $OPT{'dryrun'};
351 "CREATE INDEX $index_name ON Attachments(Content)
352 indextype is ctxsys.context parameters('
355 ) unless $OPT{'dryrun'};
357 print_rt_config( IndexName => $index_name );
360 die "Full-text indexes on $DB{type} are not yet supported";
364 my $dbh = $RT::Handle->dbh;
365 my $fts = ($dbh->selectrow_array(<<EOQ))[0];
366 SELECT 1 FROM information_schema.routines WHERE routine_name = 'plainto_tsquery'
371 Your PostgreSQL server does not include full-text support. You will
372 need to upgrade to PostgreSQL version 8.3 or higher to use full-text
381 return if $RT::Handle->CheckSphinxSE;
385 Your MySQL server has not been compiled with the Sphinx storage engine
386 (sphinxse). You will need to recompile MySQL according to the
387 instructions in Sphinx's documentation at
388 http://sphinxsearch.com/docs/current.html#sphinxse-installing
394 sub ora_create_datastore {
395 return sprintf 'datastore %s', ora_create_preference(
401 sub ora_create_filter {
403 $res .= sprintf "format column %s\n", ora_create_format_column();
404 $res .= sprintf 'filter %s', ora_create_preference(
411 sub ora_create_lexer {
412 return sprintf 'lexer %s', ora_create_preference(
418 sub ora_create_word_list {
419 return sprintf 'wordlist %s', ora_create_preference(
425 sub ora_create_stop_list {
426 my $file = shift || 'etc/stopwords/en.txt';
427 return '' unless -e $file;
429 my $name = $DEFAULT{'prefix'} .'stop_list';
430 unless ($OPT{'dryrun'}) {
431 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_stoplist(?); end;', $name );
434 'begin ctx_ddl.create_stoplist(?, ?); end;',
435 undef, $name, 'BASIC_STOPLIST'
438 open( my $fh, '<:utf8', $file )
439 or die "couldn't open file '$file': $!";
440 while ( my $word = <$fh> ) {
443 'begin ctx_ddl.add_stopword(?, ?); end;',
449 return sprintf 'stoplist %s', $name;
452 sub ora_create_section_group {
454 my $name = $DEFAULT{'prefix'} .'section_group';
455 unless ($OPT{'dryrun'}) {
456 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_section_group(?); end;', $name );
458 'begin ctx_ddl.create_section_group(?, ?); end;',
459 undef, $name, $args{'type'}
462 return sprintf 'section group %s', $name;
465 sub ora_create_storage {
466 return sprintf 'storage %s', ora_create_preference(
472 sub ora_create_format_column {
473 my $column_name = 'ContentOracleFormat';
474 return $column_name if $OPT{'dryrun'};
477 undef, undef, uc('Attachments'), uc( $column_name )
481 ALTER TABLE Attachments ADD $column_name VARCHAR2(10)
485 my $detect_format = qq{
486 CREATE OR REPLACE FUNCTION $DEFAULT{prefix}detect_format_simple(
489 encoding IN VARCHAR2,
498 unless ( $OPT{'attachments'} ) {
499 $detect_format .= qq{
500 WHEN fname IS NOT NULL THEN 'ignore'
503 $detect_format .= qq{
504 WHEN type = 'text' THEN 'text'
505 WHEN type = 'text/rtf' THEN 'ignore'
506 WHEN type LIKE 'text/%' THEN 'text'
507 WHEN type LIKE 'message/%' THEN 'text'
513 ora_create_procedure( $detect_format );
517 SET $column_name = $DEFAULT{prefix}detect_format_simple(
519 ContentType, ContentEncoding,
522 WHERE $column_name IS NULL
525 CREATE OR REPLACE TRIGGER $DEFAULT{prefix}set_format
530 :new.$column_name := $DEFAULT{prefix}detect_format_simple(
532 :new.ContentType, :new.ContentEncoding,
540 sub ora_create_preference {
542 my $name = $DEFAULT{'prefix'} . $info{'name'};
543 return $name if $OPT{'dryrun'};
544 do_error_is_ok( $dbh => 'begin ctx_ddl.drop_preference(?); end;', $name );
546 'begin ctx_ddl.create_preference(?, ?); end;',
547 undef, $name, $info{'type'}
549 return $name unless $info{'attributes'};
551 while ( my ($attr, $value) = each %{ $info{'attributes'} } ) {
553 'begin ctx_ddl.set_attribute(?, ?, ?); end;',
554 undef, $name, $attr, $value
561 sub ora_create_procedure {
564 return if $OPT{'dryrun'};
565 my $status = $dbh->do($text, { RaiseError => 0 });
567 # Statement succeeded
570 if ( 6550 != $dbh->err ) {
575 my $msg = $dbh->func( 'plsql_errstr' );
576 die $dbh->errstr if !defined $msg;
582 if ( $DB{'type'} eq 'Oracle' ) {
583 $ENV{'NLS_LANG'} = "AMERICAN_AMERICA.AL32UTF8";
584 $ENV{'NLS_NCHAR'} = "AL32UTF8";
586 my $dsn = do { my $h = new RT::Handle; $h->BuildDSN; $h->DSN };
587 my $dbh = DBI->connect(
588 $dsn, $DB{admin}, $DB{admin_password},
589 { RaiseError => 1, PrintError => 1 },
592 die "Failed to connect to $dsn as user '$DB{admin}': ". $DBI::errstr;
599 local $dbh->{'RaiseError'} = 0;
600 local $dbh->{'PrintError'} = 0;
601 return $dbh->do(shift, undef, @_);
606 local $dbh->{'RaiseError'} = 0;
607 local $dbh->{'PrintError'} = 1;
608 return $dbh->do(shift, undef, @_);
613 return $args{'default'} if $args{'silent'};
616 print $args{'message'};
617 if ( $args{'default'} ) {
618 print "\n[". $args{'default'} .']: ';
626 return $args{'default'} if !$res && $args{'default'};
630 sub verbose { print @_, "\n" if $OPT{verbose} || $OPT{verbose}; 1 }
631 sub debug { print @_, "\n" if $OPT{debug}; 1 }
632 sub error { $RT::Logger->error( @_ ); verbose(@_); 1 }
633 sub warning { $RT::Logger->warning( @_ ); verbose(@_); 1 }
637 RT::Interface::CLI->ShowHelp(
639 Sections => 'NAME|DESCRIPTION',
643 sub print_rt_config {
647 You can now configure RT to use the newly-created full-text index by
648 adding the following to your RT_SiteConfig.pm:
650 Set( %FullTextSearch,
655 $config .= sprintf(" %-10s => '$args{$_}',\n",$_)
656 foreach grep defined $args{$_}, keys %args;
663 my $dbh = dba_handle();
664 my $message = "Going to run the following in the DB:";
670 return if $OPT{'dryrun'};
672 my $res = $dbh->do( $schema );
674 die "Couldn't run DDL query: ". $dbh->errstr;
680 rt-setup-fulltext-index - Create indexes for full text search
684 This script creates the appropriate tables, columns, functions, and / or
685 views necessary for full-text searching for your database type. It will
686 drop any existing indexes in the process.
688 Please read F<docs/full_text_indexing.pod> for complete documentation on
689 full-text indexing for your database type.
691 If you have a non-standard database administrator user or password, you
692 may use the C<--dba> and C<--dba-password> parameters to set them
695 rt-setup-fulltext-index --dba sysdba --dba-password 'secret'
697 To test what will happen without running any DDL, pass the C<--dryrun>
700 The Oracle index determines which content-types it will index at
701 creation time. By default, textual message bodies and textual uploaded
702 attachments (attachments with filenames) are indexed; to ignore textual
703 attachments, pass the C<--no-attachments> flag when the index is
709 Ruslan Zakirov E<lt>ruz@bestpractical.comE<gt>,
710 Alex Vandiver E<lt>alexmv@bestpractical.comE<gt>