2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2016 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 );
71 use RT::Interface::CLI ();
74 use Getopt::Long qw(GetOptions);
75 my %OPT = ( memory => '2M', limit => 0 );
88 RT::Interface::CLI->ShowHelp if $OPT{help};
91 if ( !flock main::DATA, LOCK_EX | LOCK_NB ) {
93 RT::Logger->info("$0 is already running; aborting silently, as requested");
97 print STDERR "$0 is already running\n";
102 my $max_size = RT->Config->Get('MaxFulltextAttachmentSize');
104 my $db_type = RT->Config->Get('DatabaseType');
105 my $fts_config = $ENV{RT_FTS_CONFIG} ? JSON::from_json($ENV{RT_FTS_CONFIG})
106 : RT->Config->Get('FullTextSearch') || {};
107 unless ( $fts_config->{'Enable'} ) {
110 Full text search is disabled in your RT configuration. Run
111 @RT_SBIN_PATH_R@/rt-setup-fulltext-index to configure and enable it.
116 unless ( $fts_config->{'Indexed'} ) {
119 Full text search is enabled in your RT configuration, but not with any
120 full-text database indexing -- hence this tool is not required. Read
121 the documentation for %FullTextSearch in your RT_Config for more details.
127 if ( $db_type eq 'Oracle' ) {
128 my $index = $fts_config->{'IndexName'} || 'rt_fts_index';
129 $RT::Handle->dbh->do(
130 "begin ctx_ddl.sync_index(?, ?); end;", undef,
131 $index, $OPT{'memory'}
134 } elsif ( $fts_config->{Sphinx} ) {
137 Updates to the external Sphinx index are done via running the sphinx
146 # Skip ACL checks. This saves a large number of unnecessary queries
147 # (for tickets, ACLs, and users) which are unnecessary, as we are
148 # running as the system user.
150 no warnings 'redefine';
152 *RT::Attachment::_Value = \&DBIx::SearchBuilder::Record::_Value;
153 *RT::Attachments::Next = \&DBIx::SearchBuilder::Next;
157 if ($db_type eq 'mysql') {
159 } elsif ($db_type eq 'Pg') {
163 sub attachment_loop {
165 my $table = $fts_config->{'Table'};
168 # Fetch in batches of size --limit
170 # Indexes all text/plain and text/html attachments
171 my $attachments = RT::Attachments->new( RT->SystemUser );
173 FIELD => 'ContentType',
175 VALUE => ['text/plain', 'text/html'],
177 $attachments->Limit( FIELD => 'id', OPERATOR => '>', VALUE => $LAST );
178 $attachments->OrderBy( FIELD => 'id', ORDER => 'asc' );
179 $attachments->RowsPerPage( $OPT{'limit'} );
181 # Call back to the DB-specific part
182 $subref->($attachments);
184 $LAST = $attachments->Last->id if $attachments->Count;
186 redo if $OPT{'all'} and $attachments->Count == $OPT{'limit'};
190 sub process_bulk_insert {
191 my $dbh = $RT::Handle->dbh;
192 my ($statement, $error) = @_;
194 # Doing large inserts is faster than individual statements, but
195 # comes at a parsing cost; cache the statement handles (99% of which
196 # will be the same size) for a notable (2x) speed gain.
200 $dbh->prepare($statement->(1));
202 attachment_loop( sub {
203 my ($attachments) = @_;
207 while ( my $a = $attachments->Next ) {
208 debug("Found attachment #". $a->id );
209 if ( $max_size and $a->ContentLength > $max_size ){
210 debug("Attachment #" . $a->id . " is " . $a->ContentLength .
211 " bytes which is larger than configured MaxFulltextAttachmentSize " .
212 " of " . $max_size . ", skipping");
216 my $text = $a->Content // "";
217 HTML::Entities::decode_entities($text) if $a->ContentType eq "text/html";
218 push @insert, $text, $a->id;
221 return unless $found;
223 # $found should be the limit size on all but the last go-around.
224 $sthandles{$found} ||= $dbh->prepare($statement->($found));
226 return if eval { $sthandles{$found}->execute(@insert); };
228 # We can catch and recover from some errors; re-do row-by-row to
229 # know which row had which errors
231 my ($content, $id) = splice(@insert,0,2);
232 next if eval { $sthandles{1}->execute($content, $id); };
233 $error->($id, $content);
235 # If this was a semi-expected error, insert an empty
236 # tsvector, so we count this row as "indexed" for
237 # purposes of knowing where to pick up
238 eval { $sthandles{1}->execute( "", $id ) }
239 or die "Failed to insert empty row for attachment $id: " . $dbh->errstr;
245 my $dbh = $RT::Handle->dbh;
246 my $table = $fts_config->{'Table'};
248 ($LAST) = $dbh->selectrow_array("SELECT MAX(id) FROM $table");
250 my $insert = $fts_config->{Engine} eq "MyISAM" ? "INSERT DELAYED" : "INSERT";
255 return "$insert INTO $table(Content, id) VALUES "
256 . join(", ", ("(?,?)") x $n);
260 if ($dbh->err == 1366 and $dbh->state eq "HY000") {
261 warn "Attachment $id cannot be indexed. Most probably it contains invalid UTF8 bytes. ".
262 "Error: ". $dbh->errstr;
264 die "Attachment $id cannot be indexed: " . $dbh->errstr;
272 if ( $fts_config->{'Table'} ne 'Attachments' ) {
279 sub process_pg_insert {
280 my $dbh = $RT::Handle->dbh;
281 my $table = $fts_config->{'Table'};
282 my $column = $fts_config->{'Column'};
283 ($LAST) = $dbh->selectrow_array("SELECT MAX(id) FROM $table");
288 return "INSERT INTO $table($column, id) VALUES "
289 . join(", ", ("(TO_TSVECTOR(?),?)") x $n);
293 if ( $dbh->err == 7 && $dbh->state eq '54000' ) {
294 warn "Attachment $id cannot be indexed. Most probably it contains too many unique words. ".
295 "Error: ". $dbh->errstr;
296 } elsif ( $dbh->err == 7 && $dbh->state eq '22021' ) {
297 warn "Attachment $id cannot be indexed. Most probably it contains invalid UTF8 bytes. ".
298 "Error: ". $dbh->errstr;
300 die "Attachment $id cannot be indexed: " . $dbh->errstr;
306 sub process_pg_update {
307 my $dbh = $RT::Handle->dbh;
308 my $column = $fts_config->{'Column'};
310 ($LAST) = $dbh->selectrow_array("SELECT MAX(id) FROM Attachments WHERE $column IS NOT NULL");
312 my $sth = $dbh->prepare("UPDATE Attachments SET $column = TO_TSVECTOR(?) WHERE id = ?");
314 attachment_loop( sub {
315 my ($attachments) = @_;
318 while ( my $a = $attachments->Next ) {
319 debug("Found attachment #". $a->id );
321 if ( $max_size and $a->ContentLength > $max_size ){
322 debug("Attachment #" . $a->id . " is " . $a->ContentLength .
323 " bytes which is larger than configured MaxFulltextAttachmentSize " .
324 " of " . $max_size . ", skipping");
328 my $text = $a->Content // "";
329 HTML::Entities::decode_entities($text) if $a->ContentType eq "text/html";
331 push @insert, [$text, $a->id];
334 # Try in one database transaction; if it fails, we roll it back
335 # and try one statement at a time.
339 $ok = eval { $sth->execute( $_->[0], $_->[1] ) };
348 # Things didn't go well. Retry the UPDATE statements one row at
349 # a time, outside of the transaction.
351 my ($content, $id) = ($_->[0], $_->[1]);
352 next if eval { $sth->execute( $content, $id ) };
353 if ( $dbh->err == 7 && $dbh->state eq '54000' ) {
354 warn "Attachment $id cannot be indexed. Most probably it contains too many unique words. ".
355 "Error: ". $dbh->errstr;
356 } elsif ( $dbh->err == 7 && $dbh->state eq '22021' ) {
357 warn "Attachment $id cannot be indexed. Most probably it contains invalid UTF8 bytes. ".
358 "Error: ". $dbh->errstr;
360 die "Attachment $id cannot be indexed: " . $dbh->errstr;
363 # If this was a semi-expected error, insert an empty
364 # tsvector, so we count this row as "indexed" for
365 # purposes of knowing where to pick up
366 eval { $sth->execute( "", $id ) }
367 or die "Failed to insert empty row for attachment $id: " . $dbh->errstr;
374 sub debug { print @_, "\n" if $OPT{debug}; 1 }
375 sub error { $RT::Logger->error(_(@_)); 1 }
376 sub warning { $RT::Logger->warn(_(@_)); 1 }
380 rt-fulltext-indexer - Indexer for full text search
384 This is a helper script to keep full text indexes in sync with data.
385 Read F<docs/full_text_indexing.pod> for complete details on how and when