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

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

package GT::SQL::Search::MSSQL::Search;
#--------------------------------------------------------------------------------
    use strict;
    use vars qw/ @ISA $ATTRIBS $VERSION $DEBUG $AUTOLOAD /;
    use GT::SQL::Search::Base::Search;
    @ISA = qw( GT::SQL::Search::Base::Search );

#--------------------------------------------------------------------------------
# Preamble information related to the object

    $DEBUG      = 0;
    $VERSION    = sprintf "%d.%03d", q$Revision: 1.9 $ =~ /(\d+)\.(\d+)/;
    $ATTRIBS    = {
        min_word_size => 2,
    };

sub load {
    shift;
    return GT::SQL::Search::MSSQL::Search->new(@_)
}

sub query {
#--------------------------------------------------------------------------------
# overruns the usual query system with the mssql version
#
    my $self    = shift;

# Find out what sort of a parameter we're dealing with
    my $input   = $self->common_param(@_);

# Add additional parameters if required
    foreach my $parameter ( keys %{$ATTRIBS} ) {
        if ( not exists $input->{$parameter} ) {
            $input->{$parameter} = $self->{$parameter};
        }
    }

# Parse query...,
    my ( $query, $rejected )     = $self->_parse_query_string( $input->{'query'} );
    $self->{'rejected_keywords'} = $rejected;

# Setup the additional input parameters
    $query = $self->_preset_options( $query, $input );

# Now sort into distinct buckets
    my $buckets = GT::SQL::Search::Base::Search::_create_buckets( $query );
    my $tbl     = $self->{table} or $self->error( 'BADARGS', 'FATAL', 'Must have table object defined' );
    my $string  = $self->_string ($buckets);

    return $self->sth({}) unless ($string =~ /\w/);

    my $table_name = $tbl->name();
    my ($pk)       = $tbl->pk;

# create the filter
    my $filter_sql = $self->{filter} ? "WHERE ( " . $self->{filter}->sql . ' )' : '';

# If we have a callback, we need all results.
    if ($self->{callback}) {
        $query = qq!
            SELECT $pk, K.RANK
            FROM $table_name AS T INNER JOIN
                CONTAINSTABLE ( $table_name, *,
                    '$string'
                ) AS K
                ON T.$pk = K.[KEY]
            $filter_sql
        !;
        my %results   = $tbl->do_query($query)->fetchall_list;
        my $results   = $self->{callback}->($self, \%results);
        $self->{rows} = $results ? scalar keys %$results : 0;
        return $self->sth($results);
    }
    else {
        my $mh = (defined $input->{nh} and $input->{nh} =~ /^(\d+)$/) ? $1 : 1;
        my $nh = (defined $input->{mh} and $input->{mh} =~ /^(\d+)$/) ? $1 : 25;
# First get the total.
        $query = qq!
            SELECT COUNT(*)
            FROM $table_name AS T INNER JOIN
                CONTAINSTABLE ( $table_name, *,
                    '$string'
                ) AS K
                ON T.$pk = K.[KEY]
            $filter_sql
        !;
        my ($count) = $tbl->do_query($query)->fetchrow;

# Now get results.
        $query = qq!
            SELECT $pk, K.RANK
            FROM $table_name AS T INNER JOIN
                CONTAINSTABLE ( $table_name, *,
                    '$string'
                ) AS K
                ON T.$pk = K.[KEY]
            $filter_sql
            ORDER BY K.RANK DESC
        !;
        my %results   = $tbl->do_query($query)->fetchall_list;
        $self->{rows} = $count;
        return $self->sth(\%results);
    }
}

sub _string {
# -------------------------------------------------------------------
# Returns the string to use for containstable.
#
    my ($self, $buckets) = @_;

# union
    my $tmp_bucket = $buckets->{keywords};
    my $union_request_str = join(
        " or ",
        map(
            qq!"$_"!,
            keys %{$buckets->{phrases}}
        ),
        map(
            ($tmp_bucket->{$_}->{substring} ? "$_*" : $_),
            keys %$tmp_bucket
        )
    );

# intersect
    $tmp_bucket = $buckets->{keywords_must};
    my $intersect_request_str = join(
        " and ",
        map(
            qq!"$_"!,
            keys %{$buckets->{phrases_must}}
        ),
        map(
            ($tmp_bucket->{$_}->{substring} ? "$_*" : $_),
            keys %$tmp_bucket
        )
    );

# disjoin
    $tmp_bucket = $buckets->{keywords_cannot};
    my $disjoin_request_str = join(
        " and ",
        map(
            qq!"$_"!,
            keys %{$buckets->{phrases_cannot}}
        ),
        map(
            ($tmp_bucket->{$_}->{substring} ? "$_*" : $_),
            keys %$tmp_bucket
        )
    );

# now build the query
    my $tmp_request_str = join(
        " and ",
        ($union_request_str     ?     "( $union_request_str )"     : ()),
        ($intersect_request_str ?     "( $intersect_request_str )" : ()),
        ($disjoin_request_str   ? "NOT ( $disjoin_request_str )"   : ())
    );
    return $tmp_request_str;
}

1;