Ego_Search_Sql.php

gehe zur Dokumentation dieser Datei

<?php
require_once('base/Ego_Search.php');
 
class Ego_Search_Sql extends Ego_Search
{
    static $replace_source = array('ä', 'ö', 'ü', 'ß');
    static $replace_target = array('ae', 'oe', 'ue', 'ß');
    private  $_table; 
    private  $_count; 
    function __construct($table, $suffix='_fulltext')
    {
        $this->_table = $table.$suffix;
        $this->_count = $table.'_fullnum';
    }
 
    function delete($index)
    {
        assert(is_numeric($index));
        $db = new_db_connection(); /* @var $db Ego_Sql_Abstract */
        $db->delete(array(
            'table' => $this->_table,
            'where' => 'id='.$index,
            'nobackup'  => 1
        ));
 
        $db->delete(array(
            'table' => $this->_count,
            'where' => 'id='.$index,
            'nobackup'  => 1
        ));
        return true;
    }
 
    function reset()
    {
        if (Ego_System::getDbDriver()=='mysql')
        {
            throw new Exception('Die SQL Suche kann mit dem mysql Treiber nicht verwendet werden, bitte verwenden Sie den mysqli Treiber.');
        }
        $db = new_db_connection(); /* @var $db Ego_Sql_Abstract */
        $db->createTable(
            $this->_table,
            array(
                'name' => 'varchar(255)/*bin*/',
                'id' => 'bigint',
                'count' => 'int',
                'PRIMARY KEY' => 'name,id',
                'KEY id' => 'id'
            )
        );
 
        $db->createTable(
            $this->_count,
            array(
                'id' => 'bigint',
                'count' => 'int',
                'PRIMARY KEY' => 'id'
            )
        );
        return true;
    }
 
    function update($index, $page, $count = array(), $recursive = true)
    {
        $count_keys = array();
        $count_words = $this->_countAllWords($page, $count);
 
        foreach ($count as $k => $c)
        {
            // Der Wert des Feldes ist relativ zu allen Wörtern
            $c = ceil($count_words * ($c / 100));
 
            // Den Inhalt an den Leerzeichen aufspalten.
            $content_keys = explode(' ', $this->_getContent($page, $k));
 
            foreach ($content_keys as $key)
            {
                set_time_limit(0);
                if ($key)
                {
                    if (strlen($key)<250)
                    {
                        $key = mb_strtolower($key);
                        if ($count_keys[$key])
                        {
                            $count_keys[$key] += $c;
                        } else {
                            $count_keys[$key] = $c;
                        }
 
                        $content2 = strtr($key,'"\'(),-+;:.#~*@_/', '                ');
                        $content_keys2 = explode(' ', $content2); // Den Inhalt an den Leerzeichen aufspalten.
 
                        foreach ($content_keys2 as $key2)
                        {
                            set_time_limit(0);
                            if ($key2)
                            {
                                while (mb_strlen($key2)>=2)
                                {
                                    if ($key!=$key2)
                                    {
                                        if ($count_keys[$key2])
                                        {
                                            $count_keys[$key2] += $c;
                                        } else {
                                            $count_keys[$key2] = $c;
                                        }
                                    }
                                    $key2 = mb_substr($key2, 1);
                                }
                            }
                        }
                    }
                }
            }
        }
 
        $this->delete($index);
        $db = new_db_connection();
        $db->begin();
        foreach ($count_keys as $key => $count)
        {
            set_time_limit(0);
            $db->insert(array(
                'table' => $this->_table,
                'set'   => array('name' => $key, 'id' => $index, 'count' => $count),
                'nobackup'  => 1
            ));
        }
 
        //
        $db->insert(array(
            'table' => $this->_count,
            'set'   => array('id' => $index, 'count' => $count_words),
            'nobackup'  => 1
        ));
 
        $db->commit();
        return true;
    }
 
    private function _countAllWords($page, $count)
    {
        $count_words = 0;
 
        foreach ($count as $k => $c)
        {
            $content_keys = explode(' ', $this->_getContent($page, $k)); // Den Inhalt an den Leerzeichen aufspalten.
 
            foreach ($content_keys as $key)
            {
                set_time_limit(0);
                if ($key)
                {
                    if (strlen($key)>=2 && strlen($key)<250)
                    {
                        $count_words++;
                    }
                }
            }
        }
 
        return $count_words;
    }
 
    function replace($part, $reverse=false)
    {
        if ($reverse)
        {
            return str_replace(Ego_Search_Sql::$replace_target, Ego_Search_Sql::$replace_source, $part);
        } else {
            return str_replace(Ego_Search_Sql::$replace_source, Ego_Search_Sql::$replace_target, $part);
        }
    }
 
    function search($search, $relation, $query, $use_query_andorscore=false, $id_field='id', $name_field='name',
        $count_field='count', $cond_field='', $more_tables='', $more_where='', $score_select='', $lower=false)
    {
        $this->checkSearch($search);
 
        if ($lower)
        {
            $name_field = 'LOWER('.$this->_table.'.'.$name_field.')';
        } else {
            $name_field = $this->_table.'.'.$name_field;
        }
        $search = mb_strtolower(trim($search));
        $cond_field2 = $cond_field?$cond_field:$this->_table.'.'.$id_field;
        $search_asterix = ''; // Die Suche mit * erweitern.
        $in_string = '';
        $in_search = '';
        for ($i=0; $i<strlen($search); $i++)
        {
            $character = $search[$i];
            switch ($character)
            {
                case '"':
                    if ($in_string=='"')
                    {
                        $in_string = '';
                        if (!$query['having']) {
                            $query['having'] = "1=1";
                        }
                        $query['having'].= " AND (name LIKE :insearch OR title LIKE :insearch OR short LIKE :insearch OR content LIKE :insearch OR extra LIKE :insearch)";
                        $query['bind']['insearch'] = '%'.$in_search.'%';
                    } else
                    {
                        $in_string = '"';
                        $in_search = '';
                        $search_asterix.= '+';
                    }
                    break;
                case ' ':
                    if (!$in_string)
                    {
                        $search_asterix.= '*';
                    }
                    $search_asterix.= $character;
                    if ($in_string) {
                        $in_search.= $character;
                        $search_asterix.= '+';
                    }
                    break;
                default:
                    if ($in_string) {
                        $in_search.= $character;
                    }
                    $search_asterix.= $character;
            }
        }
        if ($character!='"')
        { // Am Ende noch einen Asterix hinzufügen.
            $search_asterix.= '*';
        }
        $search = $search_asterix;
 
        $search = strtr($search, '*', '%');
        $search = preg_replace('/\+ +/', '+', $search);
        $search = preg_replace('/\- +/', '-', $search);
        $parts = explode(' ', $search);
 
        $query_and = array();
        $query_or = array();
        $query_score = array();
        $i = 0;
        foreach($parts as $part) // Bearbeiten der einzelnen Suchbegriffsteile
        {
            $i++;
            //str_replace ' => \'
            // oder unten binds
            //
            $part = mb_strtolower($part, 'UTF-8'); // Groß-/Kleinschreibung ignorieren.
            if ($part[0] == '+')
            {
                $part = substr($part, 1);
                $and = $relation.' IN (SELECT '.$cond_field2.' FROM '.$this->_table.$more_tables.
                    ' WHERE '.$name_field.' LIKE :part'.$i.($more_where?' AND '.$more_where:'').')';
                $query['bind']['part'.$i] = $part;
                if ($use_query_andorscore)
                {
                    $query['andor'][$part][] = $and;
                } else {
                    $query_and[] = $and;
                }
                $query_score[] = $name_field.' LIKE :part'.$i;
            } elseif ($part[0] == '-')
            {
                $part = substr($part, 1);
                $and= $relation.' NOT IN (SELECT '.$cond_field2.' FROM '.$this->_table.$more_tables.
                    ' WHERE '.$name_field.' LIKE :part'.$i.($more_where?' AND '.$more_where:'').')';
                $query['bind']['part'.$i] = $part;
                if ($use_query_andorscore)
                {
                    $query['and'][] = $and;
                } else {
                    $query_and[] = $and;
                }
            }
            else
            {
                $like = $name_field.' LIKE :part'.$i;
                $query['bind']['part'.$i] = $part;
                $part2 = $this->replace($part);
                if ($part!=$part2)
                {
                    $like2 = $name_field.' LIKE :parta'.$i;
                    $query['bind']['parta'.$i] = $part2;
                    $like = '('.$like.' OR '.$like2.')';
                }
                $part3 = $this->replace($part, true);
                if ($part!=$part3)
                {
                    $like3 = $name_field.' LIKE :partb'.$i;
                    $query['bind']['partb'.$i] = $part3;
                    $like = '('.$like.' OR '.$like3.')';
                }
                $query_or[] = $relation.' IN (SELECT '.$cond_field2.' FROM '.$this->_table.$more_tables.
                ' WHERE '.$like.($more_where?' AND '.$more_where:'').')';
                $query_score[] = $like;
            }
        }
 
        if ($query_and) // Zusammenbauen der Suchanfrage
        {
            $query['where'] .= "\n AND ".join(' AND ', $query_and);
        }
        if ($query_or)
        {
            if ($use_query_andorscore)
            {
                if (is_array($query['or']))
                {
                    $query['or'] = array_merge($query['or'], $query_or);
                } else {
                    $query['or'] = $query_or;
                }
            } else {
                $query['where'] .= "\n AND ( (".join(') OR (', $query_or).') )';
            }
        }
        if ($count_field && $query_score)
        {
            $score = '(SELECT SUM('.$this->_table.'.'.$count_field.') FROM '.$this->_table.' WHERE '.
                $relation.'='.$this->_table.'.'.$id_field.' AND ('.join(' OR ',$query_score).') GROUP BY '.$this->_table.'.'.$id_field.') * '.
                round(50/sizeof($query_score)).' / '.
                '(SELECT '.$count_field.' FROM '.$this->_count.' WHERE '.$this->_count.'.'.$id_field.'='.$relation.')';
            if ($use_query_andorscore)
            {
                $query['score'][] = $score;
            } else {
                $query['fields2'][] = '('.$score.') AS score';
            }
            if (!$query['order'])
            {
                $query['order'] = 'score DESC';
            }
        }
        if ($score_select && $query_score)
        {
            $score = $score_select.' AND ('.join(' OR ',$query_score).') AND '.$relation.'='.$cond_field2;
 
            $query['score'] = array($score);
 
            if (!$query['order'])
            {
                $query['order'] = 'score DESC';
            }
        }
        if ($this->extraQuery) {
            if ($query['where']) {
                $query['where'] .= " AND {$this->extraQuery}";
            } else {
                $query['where'] = $this->extraQuery;
            }
        }
        return $query;
    }
}
?>