#
# COPYRIGHT:
#
-# This software is Copyright (c) 1996-2012 Best Practical Solutions, LLC
+# This software is Copyright (c) 1996-2016 Best Practical Solutions, LLC
# <sales@bestpractical.com>
#
# (Except where explicitly superseded by other copyright notices)
no warnings 'once';
# fix lib paths, some may be relative
-BEGIN {
+BEGIN { # BEGIN RT CMD BOILERPLATE
require File::Spec;
+ require Cwd;
my @libs = ("@RT_LIB_PATH@", "@LOCAL_LIB_PATH@");
my $bin_path;
for my $lib (@libs) {
unless ( File::Spec->file_name_is_absolute($lib) ) {
- unless ($bin_path) {
- if ( File::Spec->file_name_is_absolute(__FILE__) ) {
- $bin_path = ( File::Spec->splitpath(__FILE__) )[1];
- }
- else {
- require FindBin;
- no warnings "once";
- $bin_path = $FindBin::Bin;
- }
- }
+ $bin_path ||= ( File::Spec->splitpath(Cwd::abs_path(__FILE__)) )[1];
$lib = File::Spec->catfile( $bin_path, File::Spec->updir, $lib );
}
unshift @INC, $lib;
}
+
}
BEGIN {
my %DB = (
type => scalar RT->Config->Get('DatabaseType'),
user => scalar RT->Config->Get('DatabaseUser'),
- admin => '@DB_DBA@',
+ admin => scalar RT->Config->Get('DatabaseAdmin'),
admin_password => undef,
);
my %DEFAULT;
if ( $DB{'type'} eq 'Pg' ) {
%DEFAULT = (
- table => 'Attachments',
+ table => 'AttachmentsIndex',
column => 'ContentIndex',
);
}
'dba=s' => \$DB{'admin'},
'dba-password=s' => \$DB{'admin_password'},
+ 'limit=i' => \$DB{'batch-size'},
) or show_help();
if ( $OPT{'help'} || (!$DB{'admin'} && $DB{'type'} eq 'Oracle' ) ) {
$dbh->{'RaiseError'} = 1;
$dbh->{'PrintError'} = 1;
+# MySQL could either be native of sphinx; find out which
+if ($DB{'type'} eq "mysql") {
+ my $index_type = lc($OPT{'index-type'} || '');
+
+ # Default to sphinx on < 5.6, and error if they provided mysql
+ my $msg;
+ if ($RT::Handle->dbh->{mysql_serverversion} < 50600) {
+ $msg = "Complete support for full-text search requires MySQL 5.6 or higher. For prior\n"
+ ."versions such as yours, full-text indexing can either be provided using MyISAM\n"
+ ."tables, or the external Sphinx indexer. Using MyISAM tables requires that your\n"
+ ."database be tuned to support them, as RT uses InnoDB tables for all other content.\n"
+ ."Using Sphinx will require recompiling MySQL. Which indexing solution would you\n"
+ ."prefer?"
+ } else {
+ $msg = "MySQL 5.6 and above support native full-text indexing; for compatibility\n"
+ ."with earlier versions of RT, the external Sphinx indexer is still supported.\n"
+ ."Which indexing solution would you prefer?"
+ }
+
+ while ( $index_type ne 'sphinx' and $index_type ne 'mysql' ) {
+ $index_type = lc prompt(
+ message => $msg,
+ default => 'mysql',
+ silent => !$OPT{'ask'},
+ );
+ };
+ $DB{'type'} = $index_type;
+}
+
if ( $DB{'type'} eq 'mysql' ) {
+ # MySQL 5.6 has FTS on InnoDB "text" columns -- which the
+ # Attachments table doesn't have, but we can make it have.
+ my $table = $OPT{'table'} || prompt(
+ message => "Enter the name of a new MySQL table that will be used to store the\n"
+ . "full-text content and indexes:",
+ default => $DEFAULT{'table'},
+ silent => !$OPT{'ask'},
+ );
+ do_error_is_ok( dba_handle() => "DROP TABLE $table" )
+ unless $OPT{'dryrun'};
+
+ my $engine = $RT::Handle->dbh->{mysql_serverversion} < 50600 ? "MyISAM" : "InnoDB";
+ my $schema = "CREATE TABLE $table ( "
+ ."id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
+ ."Content LONGTEXT ) ENGINE=$engine CHARACTER SET utf8";
+ insert_schema( $schema );
+
+ insert_data( Table => $table, Engine => $engine );
+
+ insert_schema( "CREATE FULLTEXT INDEX $table ON $table(Content)" );
+
+ print_rt_config( Table => $table );
+} elsif ($DB{'type'} eq 'sphinx') {
check_sphinx();
my $table = $OPT{'table'} || prompt(
message => "Enter name of a new MySQL table that will be used to connect to the\n"
default => $DEFAULT{'table'},
silent => !$OPT{'ask'},
);
- my $url = $OPT{'url'} || prompt(
+
+ my $url = 'sphinx://localhost:3312/rt';
+ my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
+ $url = 'sphinx://127.0.0.1:3312/rt'
+ if $version and $version =~ /^(\d+\.\d+)/ and $1 >= 5.5;
+
+ $url = $OPT{'url'} || prompt(
message => "Enter URL of the sphinx search server; this should be of the form\n"
. "sphinx://<server>:<port>/<index name>",
- default => 'sphinx://localhost:3312/rt',
+ default => $url,
silent => !$OPT{'ask'},
);
my $maxmatches = $OPT{'maxmatches'} || prompt(
my $schema = <<END;
CREATE TABLE $table (
- id INTEGER UNSIGNED NOT NULL,
+ id BIGINT NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
INDEX(query)
Below is a simple Sphinx configuration which can be used to index all
text/plain attachments in your database. This configuration is not
ideal; you should read the Sphinx documentation to understand how to
-configure it to better suit your needs.
+configure it to better suit your needs. It assumes that you create the
+$var_path/sphinx/ directory, and that is is writable by the sphinx
+user.
source rt {
type = mysql
seamless_rotate = 1
preopen_indexes = 0
unlink_old = 1
+ # For sphinx >= 1.10:
+ binlog_path = $var_path/sphinx/
}
END
my $table = $OPT{'table'} || prompt(
message => "Enter the name of a DB table that will be used to store the Pg tsvector.\n"
. "You may either use the existing Attachments table, or create a new\n"
- . "table.",
+ . "table. Creating a new table makes initial indexing faster.",
default => $DEFAULT{'table'},
silent => !$OPT{'ask'},
);
silent => !$OPT{'ask'},
);
- my $schema;
+ my @schema;
my $drop;
if ( lc($table) eq 'attachments' ) {
$drop = "ALTER TABLE $table DROP COLUMN $column";
- $schema = "ALTER TABLE $table ADD COLUMN $column tsvector";
+ push @schema, "ALTER TABLE $table ADD COLUMN $column tsvector";
} else {
$drop = "DROP TABLE $table";
- $schema = "CREATE TABLE $table ( "
- ."id INTEGER NOT NULL,"
- ."$column tsvector )";
+ push @schema, split /;\n+/, <<SCHEMA;
+CREATE TABLE $table (
+ id SERIAL,
+ $column tsvector
+);
+GRANT SELECT, INSERT, UPDATE, DELETE ON $table TO "$DB{user}"
+SCHEMA
}
my $index_type = lc($OPT{'index-type'} || '');
while ( $index_type ne 'gist' and $index_type ne 'gin' ) {
$index_type = lc prompt(
- message => "You may choose between GiST or GIN indexes; the former is several times\n"
- . "slower to search, but takes less space on disk and is faster to update.",
- default => 'GiST',
+ message => "You may choose between GiST or GIN indexes; the GiST takes less space on\n"
+ . "disk and is faster to update, but is an order of magnitude slower to query.",
+ default => 'GIN',
silent => !$OPT{'ask'},
);
}
do_error_is_ok( dba_handle() => $drop )
unless $OPT{'dryrun'};
- insert_schema( $schema );
- insert_schema("CREATE INDEX ${column}_idx ON $table USING $index_type($column)");
+ insert_schema( $_ ) for @schema;
+
+ insert_data( Table => $table, Column => $column );
+
+ insert_schema( "CREATE INDEX ${column}_idx ON $table USING $index_type($column)" );
print_rt_config( Table => $table, Column => $column );
}
}
}
+sub insert_data {
+ return if $OPT{dryrun};
+
+ print "Indexing existing data...\n";
+
+ $ENV{RT_FTS_CONFIG} = JSON::to_json( {Enable => 1, Indexed => 1, @_});
+ system( "$RT::SbinPath/rt-fulltext-indexer", "--all",
+ ($DB{'batch-size'} ? ("--limit", $DB{'batch-size'}) : ()));
+}
+
=head1 NAME
rt-setup-fulltext-index - Create indexes for full text search
projects / freeside.git / blobdiff