X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fsbin%2Frt-setup-fulltext-index.in;h=01b0cb8f6f1e06a65938aca0af6dfa40bd8e5395;hp=eab720352157ceb7a196eeb2d0e468dbae26c355;hb=681a340f6be4184b1472a8e1fa9cd5d074f6f325;hpb=3d0a1bb06b895c5be6e3f0517d355442a6b1e125

diff --git a/rt/sbin/rt-setup-fulltext-index.in b/rt/sbin/rt-setup-fulltext-index.in
index eab720352..01b0cb8f6 100644
--- a/rt/sbin/rt-setup-fulltext-index.in
+++ b/rt/sbin/rt-setup-fulltext-index.in
@@ -3,7 +3,7 @@
 #
 # COPYRIGHT:
 #
-# This software is Copyright (c) 1996-2013 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)
@@ -51,27 +51,20 @@ use warnings;
 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 {
@@ -84,7 +77,7 @@ use RT::Interface::CLI ();
 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,
 );
 
@@ -98,7 +91,7 @@ my %OPT = (
 my %DEFAULT;
 if ( $DB{'type'} eq 'Pg' ) {
     %DEFAULT = (
-        table  => 'Attachments',
+        table  => 'AttachmentsIndex',
         column => 'ContentIndex',
     );
 }
@@ -128,6 +121,7 @@ GetOptions(
 
     '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' ) ) {
@@ -138,7 +132,59 @@ my $dbh = $RT::Handle->dbh;
 $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"
@@ -146,10 +192,16 @@ if ( $DB{'type'} eq 'mysql' ) {
         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(
@@ -164,7 +216,7 @@ if ( $DB{'type'} eq 'mysql' ) {
 
     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)
@@ -196,7 +248,9 @@ END
 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
@@ -238,6 +292,8 @@ searchd {
     seamless_rotate         = 1
     preopen_indexes         = 0
     unlink_old              = 1
+    # For sphinx >= 1.10:
+    binlog_path             = $var_path/sphinx/
 }
 
 END
@@ -248,7 +304,7 @@ elsif ( $DB{'type'} eq 'Pg' ) {
     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'},
     );
@@ -258,32 +314,39 @@ elsif ( $DB{'type'} eq 'Pg' ) {
         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 );
 }
@@ -676,6 +739,16 @@ sub insert_schema {
     }
 }
 
+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