discourse-legacysite-perl/site/slowtwitch.com/cgi-bin/articles/GT/SQL/Search/MYSQL/Indexer.pm

# ==================================================================
# Gossamer Threads Module Library - http://gossamer-threads.com/
#
#   GT::Search::MYSQL::Indexer
#   Author  : Aki Mimoto
#   CVS Info : 087,071,086,086,085      
#   $Id: Indexer.pm,v 1.17 2004/08/28 03:53:49 jagerman Exp $
#
# Copyright (c) 2004 Gossamer Threads Inc.  All Rights Reserved.
# ==================================================================
#
# Description:
#   Class used to search indexed tables.
#

package GT::SQL::Search::MYSQL::Indexer;
# ------------------------------------------------------------------------------
use strict;
use vars qw/@ISA $VERSION $DEBUG $ERRORS $ERROR_MESSAGE/;
use GT::SQL::Search::Base::Indexer;
@ISA = qw/GT::SQL::Search::Base::Indexer/;
$DEBUG   = 0;
$VERSION = sprintf "%d.%03d", q$Revision: 1.17 $ =~ /(\d+)\.(\d+)/;

$ERRORS = {
    NOTFROMWEB      => 'There are far too many records in table %s for create/destroy of this indexing scheme from the web. Please use alternative method.',
    MYSQLNONSUPPORT => 'Driver MYSQL requires MySQL version 3.23.23 or greater. Currently MySQL version: %s'
};

@$GT::SQL::ERRORS{ keys %$ERRORS } = values %$ERRORS;

$ERROR_MESSAGE = 'GT::SQL';

sub load {
    my $class = shift;
    return $class->new(@_);
}

sub ok {
# ------------------------------------------------------------------------------
    my ($class, $tbl) = @_;
    unless (uc $tbl->{connect}->{driver} eq 'MYSQL') {
        return $class->error ('MYSQLNONSUPPORT', 'WARN', $tbl->{connect}->{driver});
    }
    my $sth = $tbl->do_query(qq!SELECT VERSION()!);
    my $version = $sth->fetchrow;
    my ($maj, $min) = split (/\./, $version);
    unless ($maj > 3 or ($maj == 3 and $min >= 23)) {
        return $class->error(MYSQLNONSUPPORT => WARN => $version);
    }
    return 1;
}

sub drop_search_driver {
# ------------------------------------------------------------------------------
    my $self = shift;

    $self->too_much() and return;

    my $tbl = $self->{table} or return;
    $tbl->connect();

    my %weights = $tbl->weight() or return;
    my $tblname = $tbl->name();

# Group the fulltext columns by value of the weight
    my %cols_grouped;
    foreach ( keys %weights ) {
        my $val = $weights{$_} or next;
        push @{$cols_grouped{$val}}, $_;
    }

# Drop unified fulltext columns if required
    if ( keys %cols_grouped > 1 ) {
        $cols_grouped{-1} = [ grep { $weights{$_} } keys %weights ];
    }

# For each value grouped column set create a full text 
# column
    foreach my $v ( keys %cols_grouped ) {

        my $ft_name = 'ft_'.join("_", sort @{$cols_grouped{$v}});

        my $res     = eval {
            $tbl->do_query(qq!
                ALTER TABLE $tblname
                DROP INDEX $ft_name
            !);
        };

# Break on errors that can't be handled
        if ( $@ ) {
            next if $@ !~ /exist/i;
            $self->warn( "$@" );
            return;
        }

    }

    return 1;
}

sub add_search_driver {
# ------------------------------------------------------------------------------
    my $self = shift;

    $self->too_much() and return;

    my $tbl     = $self->{table} or return $self->error(BADARGS   => FATAL => "table must be passed into add_search_driver.");
    my %weights = $tbl->weight() or return $self->error(NOWEIGHTS => 'WARN');
    my $tblname = $tbl->name()   or return $self->error(BADARGS   => FATAL => "table does not have a name?");

# group the fulltext columns by value of the weight
    my %cols_grouped;
    foreach ( keys %weights ) {
        my $val = $weights{$_} or next;
        push @{$cols_grouped{$val}}, $_;
    }

# Create unified fulltext columns if required
    if ( keys %cols_grouped > 1 ) {
        $cols_grouped{-1} = [ grep { $weights{$_} } keys %weights ];
    }

# for each value grouped column set create a full text 
# column
    foreach my $v ( keys %cols_grouped ) {

        my $cols    = join(",", sort @{$cols_grouped{$v}});
        my $ft_name = 'ft_'.join("_", sort @{$cols_grouped{$v}});

        my $res     = eval {
            $tbl->do_query(qq!
                ALTER TABLE $tblname
                ADD FULLTEXT $ft_name ( $cols )
            !);
        };

# break on errors that can't be handled
        if ( $@ ) {
            next if $@ =~ /duplicate/i;
            $self->warn( "$@" );
            return;
        }

    }

    return 1;

}

sub too_much {
# ------------------------------------------------------------------------------
# returns true if there are too many records to be used on the Web
#
    if ( $ENV{REQUEST_METHOD} ) {
        my $self = shift;
        my $tbl = $self->{table};
        if ( $tbl->count() > 5000 ) {
            $self->error( 'NOTFROMWEB', 'WARN', $tbl->name() );
            return 1
        }
    }
    return;
}

sub post_create_table {
# ------------------------------------------------------------------------------
    shift->add_search_driver(@_);
}

sub reindex_all {
# ------------------------------------------------------------------------------
# this will drop all the fulltext columns and reindex all of them. This should
# not be required unless the user changes the weights on one of their columns.
# Unfortunately, this method is not particularly smart and risks not dropping
# certain index columns and reindexes even when it's not required. It must be
# recoded at a future date, but as this action won't happen frequently and will 
# rarely affect the user, it is not a priority.
#
    my $self = shift;

    $self->drop_search_driver;
    $self->add_search_driver;
}

1;