<?php /*

 Composr
 Copyright (c) ocProducts, 2004-2016

 See text/EN/licence.txt for full licencing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  ocProducts Ltd
 * @package    core
 */

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__database_search()
{
    $GLOBALS['TOTAL_SEARCH_RESULTS'] = 0;

    $maximum_result_count_point = get_value('maximum_result_count_point');
    if ($maximum_result_count_point === null) {
        define('MAXIMUM_RESULT_COUNT_POINT', 1000);
    } else {
        define('MAXIMUM_RESULT_COUNT_POINT', intval($maximum_result_count_point));
    }
}

/**
 * Get minimum search length.
 * This is broadly MySQL-specific. For other databases we will usually return 4, although there may truly not be a limit on it.
 *
 * @return integer    Search length
 */
function get_minimum_search_length()
{
    static $min_word_length = null;
    if (is_null($min_word_length)) {
        $min_word_length = 4;
        if (get_db_type() == 'postgresql') {
            $min_word_length = 1; // PostgreSQL uses a dictionary based limiter, so even a 1-character word in the dictionary would work.
        }
        if (substr(get_db_type(), 0, 5) == 'mysql') {
            $_min_word_length = $GLOBALS['SITE_DB']->query('SHOW VARIABLES LIKE \'ft_min_word_len\'', null, null, true);
            if (isset($_min_word_length[0])) {
                $min_word_length = intval($_min_word_length[0]['Value']);
            }
        }
    }
    return $min_word_length;
}

/**
 * Get a list of MySQL stopwords.
 * May be overridden for other databases, if you want to tune your stopword list.
 *
 * @return array List of stopwords (actually a map of stopword to true)
 */
function get_stopwords_list()
{
    // Hard-coded from MySQL manual (https://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html). No way to read it dynamically.
    return array(
        'a\'s' => true,
        'able' => true,
        'about' => true,
        'above' => true,
        'according' => true,
        'accordingly' => true,
        'across' => true,
        'actually' => true,
        'after' => true,
        'afterwards' => true,
        'again' => true,
        'against' => true,
        'ain\'t' => true,
        'all' => true,
        'allow' => true,
        'allows' => true,
        'almost' => true,
        'alone' => true,
        'along' => true,
        'already' => true,
        'also' => true,
        'although' => true,
        'always' => true,
        'am' => true,
        'among' => true,
        'amongst' => true,
        'an' => true,
        'and' => true,
        'another' => true,
        'any' => true,
        'anybody' => true,
        'anyhow' => true,
        'anyone' => true,
        'anything' => true,
        'anyway' => true,
        'anyways' => true,
        'anywhere' => true,
        'apart' => true,
        'appear' => true,
        'appreciate' => true,
        'appropriate' => true,
        'are' => true,
        'aren\'t' => true,
        'around' => true,
        'as' => true,
        'aside' => true,
        'ask' => true,
        'asking' => true,
        'associated' => true,
        'at' => true,
        'available' => true,
        'away' => true,
        'awfully' => true,
        'be' => true,
        'became' => true,
        'because' => true,
        'become' => true,
        'becomes' => true,
        'becoming' => true,
        'been' => true,
        'before' => true,
        'beforehand' => true,
        'behind' => true,
        'being' => true,
        'believe' => true,
        'below' => true,
        'beside' => true,
        'besides' => true,
        'best' => true, // Could be a surname, hence issue
        'better' => true,
        'between' => true,
        'beyond' => true,
        'both' => true,
        'brief' => true,
        'but' => true,
        'by' => true,
        'c\'mon' => true,
        'c\'s' => true,
        'came' => true,
        'can' => true,
        'can\'t' => true,
        'cannot' => true,
        'cant' => true,
        'cause' => true,
        'causes' => true,
        'certain' => true,
        'certainly' => true,
        'changes' => true,
        'clearly' => true,
        'co' => true,
        'com' => true,
        'come' => true,
        'comes' => true,
        'concerning' => true,
        'consequently' => true,
        'consider' => true,
        'considering' => true,
        'contain' => true,
        'containing' => true,
        'contains' => true,
        'corresponding' => true,
        'could' => true,
        'couldn\'t' => true,
        'course' => true,
        'currently' => true,
        'definitely' => true,
        'described' => true,
        'despite' => true,
        'did' => true,
        'didn\'t' => true,
        'different' => true,
        'do' => true,
        'does' => true,
        'doesn\'t' => true,
        'doing' => true,
        'don\'t' => true,
        'done' => true,
        'down' => true,
        'downwards' => true,
        'during' => true,
        'each' => true,
        'edu' => true,
        'eg' => true,
        'eight' => true,
        'either' => true,
        'else' => true,
        'elsewhere' => true,
        'enough' => true,
        'entirely' => true,
        'especially' => true,
        'et' => true,
        'etc' => true,
        'even' => true,
        'ever' => true,
        'every' => true,
        'everybody' => true,
        'everyone' => true,
        'everything' => true,
        'everywhere' => true,
        'ex' => true,
        'exactly' => true,
        'example' => true,
        'except' => true,
        'far' => true,
        'few' => true,
        'fifth' => true,
        'first' => true,
        'five' => true,
        'followed' => true,
        'following' => true,
        'follows' => true,
        'for' => true,
        'former' => true,
        'formerly' => true,
        'forth' => true,
        'four' => true,
        'from' => true,
        'further' => true,
        'furthermore' => true,
        'get' => true,
        'gets' => true,
        'getting' => true,
        'given' => true,
        'gives' => true,
        'go' => true,
        'goes' => true,
        'going' => true,
        'gone' => true,
        'got' => true,
        'gotten' => true,
        'greetings' => true,
        'had' => true,
        'hadn\'t' => true,
        'happens' => true,
        'hardly' => true,
        'has' => true,
        'hasn\'t' => true,
        'have' => true,
        'haven\'t' => true,
        'having' => true,
        'he' => true,
        'he\'s' => true,
        'hello' => true,
        'help' => true,
        'hence' => true,
        'her' => true,
        'here' => true,
        'here\'s' => true,
        'hereafter' => true,
        'hereby' => true,
        'herein' => true,
        'hereupon' => true,
        'hers' => true,
        'herself' => true,
        'hi' => true,
        'him' => true,
        'himself' => true,
        'his' => true,
        'hither' => true,
        'hopefully' => true,
        'how' => true,
        'howbeit' => true,
        'however' => true,
        'i\'d' => true,
        'i\'ll' => true,
        'i\'m' => true,
        'i\'ve' => true,
        'ie' => true,
        'if' => true,
        'ignored' => true,
        'immediate' => true,
        'in' => true,
        'inasmuch' => true,
        'inc' => true,
        'indeed' => true,
        'indicate' => true,
        'indicated' => true,
        'indicates' => true,
        'inner' => true,
        'insofar' => true,
        'instead' => true,
        'into' => true,
        'inward' => true,
        'is' => true,
        'isn\'t' => true,
        'it' => true,
        'it\'d' => true,
        'it\'ll' => true,
        'it\'s' => true,
        'its' => true,
        'itself' => true,
        'just' => true,
        'keep' => true,
        'keeps' => true,
        'kept' => true,
        'know' => true,
        'known' => true,
        'knows' => true,
        'last' => true,
        'lately' => true,
        'later' => true,
        'latter' => true,
        'latterly' => true,
        'least' => true,
        'less' => true,
        'lest' => true,
        'let' => true,
        'let\'s' => true,
        'like' => true,
        'liked' => true,
        'likely' => true,
        'little' => true,
        'look' => true,
        'looking' => true,
        'looks' => true,
        'ltd' => true,
        'mainly' => true,
        'many' => true,
        'may' => true,
        'maybe' => true,
        'me' => true,
        'mean' => true,
        'meanwhile' => true,
        'merely' => true,
        'might' => true,
        'more' => true,
        'moreover' => true,
        'most' => true,
        'mostly' => true,
        'much' => true,
        'must' => true,
        'my' => true,
        'myself' => true,
        'name' => true,
        'namely' => true,
        'nd' => true,
        'near' => true,
        'nearly' => true,
        'necessary' => true,
        'need' => true,
        'needs' => true,
        'neither' => true,
        'never' => true,
        'nevertheless' => true,
        'new' => true,
        'next' => true,
        'nine' => true,
        'no' => true,
        'nobody' => true,
        'non' => true,
        'none' => true,
        'noone' => true,
        'nor' => true,
        'normally' => true,
        'not' => true,
        'nothing' => true,
        'novel' => true,
        'now' => true,
        'nowhere' => true,
        'obviously' => true,
        'of' => true,
        'off' => true,
        'often' => true,
        'oh' => true,
        'ok' => true,
        'okay' => true,
        'old' => true,
        'on' => true,
        'once' => true,
        'one' => true,
        'ones' => true,
        'only' => true,
        'onto' => true,
        'or' => true,
        'other' => true,
        'others' => true,
        'otherwise' => true,
        'ought' => true,
        'our' => true,
        'ours' => true,
        'ourselves' => true,
        'out' => true,
        'outside' => true,
        'over' => true,
        'overall' => true,
        'own' => true,
        'particular' => true,
        'particularly' => true,
        'per' => true,
        'perhaps' => true,
        'placed' => true,
        'please' => true,
        'plus' => true,
        'possible' => true,
        'presumably' => true,
        'probably' => true,
        'provides' => true,
        'que' => true,
        'quite' => true,
        'qv' => true,
        'rather' => true,
        'rd' => true,
        're' => true,
        'really' => true,
        'reasonably' => true,
        'regarding' => true,
        'regardless' => true,
        'regards' => true,
        'relatively' => true,
        'respectively' => true,
        'right' => true,
        'said' => true,
        'same' => true,
        'saw' => true,
        'say' => true,
        'saying' => true,
        'says' => true,
        'second' => true,
        'secondly' => true,
        'see' => true,
        'seeing' => true,
        'seem' => true,
        'seemed' => true,
        'seeming' => true,
        'seems' => true,
        'seen' => true,
        'self' => true,
        'selves' => true,
        'sensible' => true,
        'sent' => true,
        'serious' => true,
        'seriously' => true,
        'seven' => true,
        'several' => true,
        'shall' => true,
        'she' => true,
        'should' => true,
        'shouldn\'t' => true,
        'since' => true,
        'six' => true,
        'so' => true,
        'some' => true,
        'somebody' => true,
        'somehow' => true,
        'someone' => true,
        'something' => true,
        'sometime' => true,
        'sometimes' => true,
        'somewhat' => true,
        'somewhere' => true,
        'soon' => true,
        'sorry' => true,
        'specified' => true,
        'specify' => true,
        'specifying' => true,
        'still' => true,
        'sub' => true,
        'such' => true,
        'sup' => true,
        'sure' => true,
        't\'s' => true,
        'take' => true,
        'taken' => true,
        'tell' => true,
        'tends' => true,
        'th' => true,
        'than' => true,
        'thank' => true,
        'thanks' => true,
        'thanx' => true,
        'that' => true,
        'that\'s' => true,
        'thats' => true,
        'the' => true,
        'their' => true,
        'theirs' => true,
        'them' => true,
        'themselves' => true,
        'then' => true,
        'thence' => true,
        'there' => true,
        'there\'s' => true,
        'thereafter' => true,
        'thereby' => true,
        'therefore' => true,
        'therein' => true,
        'theres' => true,
        'thereupon' => true,
        'these' => true,
        'they' => true,
        'they\'d' => true,
        'they\'ll' => true,
        'they\'re' => true,
        'they\'ve' => true,
        'think' => true,
        'third' => true,
        'this' => true,
        'thorough' => true,
        'thoroughly' => true,
        'those' => true,
        'though' => true,
        'three' => true,
        'through' => true,
        'throughout' => true,
        'thru' => true,
        'thus' => true,
        'to' => true,
        'together' => true,
        'too' => true,
        'took' => true,
        'toward' => true,
        'towards' => true,
        'tried' => true,
        'tries' => true,
        'truly' => true,
        'try' => true,
        'trying' => true,
        'twice' => true,
        'two' => true,
        'un' => true,
        'under' => true,
        'unfortunately' => true,
        'unless' => true,
        'unlikely' => true,
        'until' => true,
        'unto' => true,
        'up' => true,
        'upon' => true,
        'us' => true,
        'use' => true,
        'used' => true,
        'useful' => true,
        'uses' => true,
        'using' => true,
        'usually' => true,
        'value' => true,
        'various' => true,
        'very' => true,
        'via' => true,
        'viz' => true,
        'vs' => true,
        'want' => true,
        'wants' => true,
        'was' => true,
        'wasn\'t' => true,
        'way' => true,
        'we' => true,
        'we\'d' => true,
        'we\'ll' => true,
        'we\'re' => true,
        'we\'ve' => true,
        'welcome' => true,
        'well' => true,
        'went' => true,
        'were' => true,
        'weren\'t' => true,
        'what' => true,
        'what\'s' => true,
        'whatever' => true,
        'when' => true,
        'whence' => true,
        'whenever' => true,
        'where' => true,
        'where\'s' => true,
        'whereafter' => true,
        'whereas' => true,
        'whereby' => true,
        'wherein' => true,
        'whereupon' => true,
        'wherever' => true,
        'whether' => true,
        'which' => true,
        'while' => true,
        'whither' => true,
        'who' => true,
        'who\'s' => true,
        'whoever' => true,
        'whole' => true,
        'whom' => true,
        'whose' => true,
        'why' => true,
        'will' => true,
        'willing' => true,
        'wish' => true,
        'with' => true,
        'within' => true,
        'without' => true,
        'won\'t' => true,
        'wonder' => true,
        'would' => true,
        'wouldn\'t' => true,
        'yes' => true,
        'yet' => true,
        'you' => true,
        'you\'d' => true,
        'you\'ll' => true,
        'you\'re' => true,
        'you\'ve' => true,
        'your' => true,
        'yours' => true,
        'yourself' => true,
        'yourselves' => true,
        'zero' => true,
    );
}

/**
 * Highlight keywords in an extracted portion of a piece of text.
 *
 * @param  string $_temp_summary What was searched
 * @param  array $words_searched List of words searched
 * @return string Highlighted portion
 */
function generate_text_summary($_temp_summary, $words_searched)
{
    require_code('xhtml');

    $summary = '';

    global $SEARCH__CONTENT_BITS;

    $_temp_summary_lower = strtolower($_temp_summary);

    // Add in some highlighting direct to XHTML
    $all_occurrences = array();
    foreach ($words_searched as $content_bit) {
        if ($content_bit == '') {
            continue;
        }

        $last_pos = 0;
        $content_bit_pos = 0;
        do {
            $content_bit_matched = $content_bit;
            if (strtoupper($content_bit) == $content_bit) { // all upper case so don't want case sensitive
                $content_bit_pos = strpos($_temp_summary, $content_bit, $last_pos);
            } else {
                $content_bit_pos = stripos($_temp_summary_lower, $content_bit, $last_pos);
                if (strpos($content_bit, '-') !== false) {
                    $content_bit_pos_2 = strpos($_temp_summary_lower, str_replace('-', '', $content_bit), $last_pos);
                    if (($content_bit_pos_2 !== false) && (($content_bit_pos === false) || ($content_bit_pos_2 < $content_bit_pos))) {
                        $content_bit_pos = $content_bit_pos_2;
                        $content_bit_matched = str_replace('-', '', $content_bit);
                    }
                }
            }

            if ($content_bit_pos !== false) {
                $last_gt = strrpos(substr($_temp_summary, 0, $content_bit_pos), '>');
                $last_lt = strrpos(substr($_temp_summary, 0, $content_bit_pos), '<');

                if (($last_gt === false) || ($last_gt > $last_lt)) {
                    $extra_pre = '<span class="comcode_highlight">';
                    $extra_post = '</span>';
                    $_temp_summary = substr($_temp_summary, 0, $content_bit_pos) .
                                     $extra_pre .
                                     substr($_temp_summary, $content_bit_pos, strlen($content_bit_matched)) .
                                     $extra_post .
                                     substr($_temp_summary, $content_bit_pos + strlen($content_bit_matched));
                    $_temp_summary_lower = strtolower($_temp_summary);
                    $last_pos = $content_bit_pos + strlen($extra_pre) + strlen($content_bit_matched) + strlen($extra_post);

                    // Adjust all stores occurrence offsets
                    foreach ($all_occurrences as $i => $occ) {
                        if ($occ[0] > $last_pos) {
                            $all_occurrences[$i] = array($all_occurrences[$i][0] + strlen($extra_pre) + strlen($extra_post), $all_occurrences[$i][0] + strlen($extra_pre) + strlen($extra_post));
                        } elseif ($occ[0] > $content_bit_pos) {
                            $all_occurrences[$i] = array($all_occurrences[$i][0] + strlen($extra_pre), $all_occurrences[$i][0] + strlen($extra_pre));
                        }
                    }

                    $all_occurrences[] = array($content_bit_pos, $last_pos);
                } else {
                    $last_pos = $content_bit_pos + strlen($content_bit_matched);
                }
            }
        } while ($content_bit_pos !== false);
    }

    if (strlen($_temp_summary) < 500) {
        $summary = $_temp_summary;
    } else {
        // Find optimal position
        $len = strlen($_temp_summary);
        $best_yet = 0;
        $best_pos_min = 250;
        $best_pos_max = 250;
        if (count($all_occurrences) < 60) { // Only bother doing this if we need to dig for the keyword
            for ($i = 250; $i < $len - 250; $i++) { // Move window along all possible positions
                $count = 0;
                $i_pre = $i - 250;
                $i_post = $i + 250;
                foreach ($all_occurrences as $occ) {
                    $occ_pre = $occ[0];
                    $occ_post = $occ[1];
                    if (($occ_pre >= $i_pre) && ($occ_pre <= $i_post) && ($occ_post >= $i_pre) && ($occ_post <= $i_post)) {
                        $count++;

                        if ($count > 5) {
                            break; // Good enough
                        }
                    }
                }
                if (($count > $best_yet) || (($best_yet == $count) && ($i - 500 < $best_pos_min))) {
                    if ($best_yet == $count) {
                        $best_pos_max = $i;
                    } else {
                        $best_yet = $count;
                        $best_pos_min = $i;
                        $best_pos_max = $i;
                    }

                    if ($count > 5) {
                        break; // Good enough
                    }
                }
            }
            $best_pos = intval(floatval($best_pos_min + $best_pos_max) / 2.0) - 250; // Move it from center pos, to where we want to start from
        } else {
            $best_pos = 0;
        }

        // Render (with ellipses if required)
        if (false) { // Far far too slow
            $summary = xhtml_substr($_temp_summary, $best_pos, min(500, $len - $best_pos), true, true);
        } else {
            $summary = substr($_temp_summary, $best_pos, min(500, $len - $best_pos));
            $summary = xhtmlise_html($summary, true);
            if ($best_pos > 0) {
                $summary = '&hellip;' . $summary;
            }
            if ($best_pos + 500 < strlen($_temp_summary)) {
                $summary .= '&hellip;';
            }
        }
    }

    return $summary;
}

/**
 * Server opensearch requests.
 */
function opensearch_script()
{
    if (!has_actual_page_access(get_member(), 'search')) {
        return; // No access
    }

    $type = get_param_string('type', 'browse');
    switch ($type) {
        // Make a search suggestion (like Google Suggest)
        case 'suggest':
            require_code('search');

            header('Content-type: application/x-suggestions+json; charset=' . get_charset());
            $request = get_param_string('request', false, true);

            $suggestions = find_search_suggestions($request);

            require_lang('search');

            safe_ini_set('ocproducts.xss_detect', '0');

            // JSON format
            echo '[' . "\n";
            // Original request
            echo '"' . php_addslashes($request) . '",' . "\n";

            // Suggestions
            echo '[';
            foreach ($suggestions as $i => $suggestion) {
                if ($i != 0) {
                    echo ',';
                }
                echo '"' . php_addslashes($suggestion) . '"';
            }
            echo '],' . "\n";

            // Descriptions of suggestions
            echo '[';
            foreach (array_values($suggestions) as $i => $suggestion) {
                if ($i != 0) {
                    echo ',';
                }
                echo '"' . php_addslashes(do_lang('NUM_RESULTS', integer_format($suggestion))) . '"';
            }
            echo '],' . "\n";

            // URLs to search suggestions
            $filter = get_param_string('filter', '');
            $filter_map = array();
            if ($filter != '') {
                foreach (explode(':', $filter) as $f) {
                    if ($f != '') {
                        $parts = explode('=', $f, 2);
                        if (count($parts) == 1) {
                            $parts = array($parts[0], '1');
                        }
                        $filter_map[$parts[0]] = $parts[1];
                    }
                }
            }
            echo '[';
            foreach (array_keys($suggestions) as $i => $suggestion) {
                if ($i != 0) {
                    echo ',';
                }
                $map = array('page' => 'search', 'type' => 'results', 'content' => $suggestion) + $filter_map;
                $_search_url = build_url($map, get_param_string('zone', get_module_zone('search')));
                $search_url = $_search_url->evaluate();
                echo '"' . php_addslashes($search_url) . '"';
            }
            echo ']' . "\n";
            echo ']' . "\n";
            break;

        // Provide details about the site search engine
        default:
            //header('Content-Type: application/opensearchdescription+xml');
            header('Content-Type: text/xml');
            $tpl = do_template('OPENSEARCH', array('_GUID' => '1fe46743805ade5958dcba0d58c4b0f2', 'DESCRIPTION' => get_option('description')), null, false, null, '.xml', 'xml');
            $tpl->evaluate_echo();
            break;
    }
}

/**
 * Build up a submitter search clause, taking into account members, authors, usernames, and usergroups.
 *
 * @param  ?ID_TEXT $member_field_name The field name for member IDs (null: Cannot match against member IDs)
 * @param  ?MEMBER $member_id Member ID (null: Unknown, so cannot search)
 * @param  ID_TEXT $author Author
 * @param  ?ID_TEXT $author_field_name The field name for authors (null: Cannot match against member IDs)
 * @return ?string An SQL fragment (null: block query)
 */
function build_search_submitter_clauses($member_field_name, $member_id, $author, $author_field_name = null)
{
    $clauses = '';

    // Member ID
    if ((!is_null($member_id)) && (!is_null($member_field_name))) {
        if ($clauses != '') {
            $clauses .= ' OR ';
        }
        $clauses .= $member_field_name . '=' . strval($member_id);
    }

    // Groups
    if ((!is_null($member_field_name)) && ($author != '')) {
        $all_usergroups = $GLOBALS['FORUM_DRIVER']->get_usergroup_list(true);
        foreach ($all_usergroups as $usergroup => $usergroup_name) {
            if ($usergroup_name == $author) {
                $members_in_group = $GLOBALS['FORUM_DRIVER']->member_group_query(array($usergroup), 50);
                if (count($members_in_group) < 50) { // Let's be reasonable with how long the SQL could get!
                    foreach (array_keys($members_in_group) as $group_member_id) {
                        if ($clauses != '') {
                            $clauses .= ' OR ';
                        }
                        $clauses .= $member_field_name . '=' . strval($group_member_id);
                    }
                }
                break;
            }
        }
    }

    // Author
    if ((!is_null($author_field_name)) && ($author != '')) {
        if ($clauses != '') {
            $clauses .= ' OR ';
        }
        $clauses .= db_string_equal_to($author_field_name, $author);
    }

    if ($clauses == '') {
        if ($author != '') {
            return null; // Query should never succeed
        }

        return '';
    }

    return ' AND (' . $clauses . ')';
}

/**
 * Get special SQL from POSTed parameters for a catalogue search field that is to be exact-matched.
 *
 * @param  array $field The field details
 * @param  integer $i We're processing for the ith row
 * @param  ID_TEXT $type Table type
 * @set short long
 * @param  ?string $param Search term (null: lookup from environment)
 * @param string $table_alias Table alias for catalogue entry table
 * @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (null: nothing special)
 */
function exact_match_sql($field, $i, $type = 'short', $param = null, $table_alias = 'r')
{
    $table = ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_' . $type . ' f' . strval($i) . ' ON (f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']) . ')';
    $search_field = 'f' . strval($i) . '.cv_value';
    if (is_null($param)) {
        $param = get_param_string('option_' . strval($field['id']), '');
    }
    $where_clause = '';
    if ($param != '') {
        if ($type == 'float' || $type == 'integer') {
            $where_clause .= $search_field . '=' . $param;
        } else {
            $where_clause = db_string_equal_to($search_field, $param);
        }
    }
    return array(array(), array('f' . strval($i) . '.cv_value'), $table, $search_field, $where_clause);
}

/**
 * Get special SQL from POSTed parameters for a catalogue search field for a multi-input field that is to be exact-matched.
 *
 * @param  array $field The field details
 * @param  integer $i We're processing for the ith row
 * @param  ID_TEXT $type Table type
 * @set short long
 * @param  ?string $param Search term (null: lookup from environment)
 * @param string $table_alias Table alias for catalogue entry table
 * @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (null: nothing special)
 */
function nl_delim_match_sql($field, $i, $type = 'short', $param = null, $table_alias = 'r')
{
    $table = ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'catalogue_efv_' . $type . ' f' . strval($i) . ' ON (f' . strval($i) . '.ce_id=' . $table_alias . '.id AND f' . strval($i) . '.cf_id=' . strval($field['id']) . ')';
    $search_field = 'f' . strval($i) . '.cv_value';
    if (is_null($param)) {
        $param = get_param_string('option_' . strval($field['id']), '');
    }
    $where_clause = '';
    if ($param != '') {
        $where_clause = '(' . $search_field . ' LIKE \'' . db_encode_like($param) . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like('%' . "\n" . $param) . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like($param . "\n" . '%') . '\' OR ' . $search_field . ' LIKE \'' . db_encode_like('%' . "\n" . $param . "\n" . '%') . '\')';
    }
    return array(array(), array('f' . strval($i) . '.cv_value'), $table, $search_field, $where_clause);
}

/**
 * Get some rows, queried from the database according to the search parameters.
 *
 * @param  ?ID_TEXT $meta_type The META type used by our content (null: Cannot support META search)
 * @param  ?ID_TEXT $meta_id_field The name of the field that retrieved META IDs will relate to (null: Cannot support META search)
 * @param  string $content Search string
 * @param  boolean $boolean_search Whether to do a boolean search
 * @param  ID_TEXT $boolean_operator Boolean operator
 * @set OR AND
 * @param  boolean $only_search_meta Whether to only do a META (tags) search
 * @param  ID_TEXT $direction Order direction
 * @param  integer $max Start position in total results
 * @param  integer $start Maximum results to return in total
 * @param  boolean $only_titles Whether to only search titles (as opposed to both titles and content)
 * @param  ID_TEXT $table The table name
 * @param  array $fields The translateable fields to search over (or an ! which is skipped). The first of these must be the title field or an '!'; if it is '!' then the title field will be the first raw-field
 * @param  string $where_clause The WHERE clause
 * @param  string $content_where The WHERE clause that applies specifically for content (this will be duplicated to check against multiple fields). ? refers to the yet-unknown field name
 * @param  ID_TEXT $order What to order by
 * @param  string $select What to select
 * @param  ?array $raw_fields The non-translateable fields to search over (null: there are none)
 * @param  ?string $permissions_module The permission module to check category access for (null: none)
 * @param  ?string $permissions_field The field that specifies the permissions ID to check category access for (null: none)
 * @param  boolean $permissions_field_is_string Whether the permissions field is a string
 * @return array The rows found
 */
function get_search_rows($meta_type, $meta_id_field, $content, $boolean_search, $boolean_operator, $only_search_meta, $direction, $max, $start, $only_titles, $table, $fields, $where_clause, $content_where, &$order, $select = '*', $raw_fields = null, $permissions_module = null, $permissions_field = null, $permissions_field_is_string = false)
{
    if (multi_lang_content()) {
        @ignore_user_abort(false); // If the user multi-submits a search, we don't want to run parallel searches (very slow!). That said, this currently doesn't work in PHP, because PHP does not realise the connection has died until way too late :(. So we also use a different tact (dedupe_mode) but hope PHP will improve with time.
    }

    if (substr($where_clause, 0, 5) == ' AND ') {
        $where_clause = substr($where_clause, 5);
    }
    if (substr($where_clause, -5) == ' AND ') {
        $where_clause = substr($where_clause, 0, strlen($where_clause) - 5);
    }

    $db = (substr($table, 0, 2) != 'f_') ? $GLOBALS['SITE_DB'] : $GLOBALS['FORUM_DB'];

    if ((!is_null($permissions_module)) && (!$GLOBALS['FORUM_DRIVER']->is_super_admin(get_member()))) {
        $g_or = _get_where_clause_groups(get_member());

        // this destroys mysqls query optimiser by forcing complexed OR's into the join, so we'll do this in PHP code
        /*$table .= ' LEFT JOIN ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'group_category_access z ON (' . db_string_equal_to('z.module_the_name', $permissions_module) . ' AND z.category_name=' . $permissions_field . (($g_or != '') ? (' AND ' . str_replace('group_id', 'z.group_id', $g_or)) : '') . ')';
        $where_clause .= ' AND ';
        $where_clause .= 'z.category_name IS NOT NULL';*/

        $cat_sql = '';
        $cat_sql .= 'SELECT DISTINCT category_name FROM ' . $db->get_table_prefix() . 'group_category_access WHERE (' . $g_or . ') AND ' . db_string_equal_to('module_the_name', $permissions_module);
        $cat_sql .= ' UNION ALL ';
        $cat_sql .= 'SELECT DISTINCT category_name FROM ' . $db->get_table_prefix() . 'member_category_access WHERE (member_id=' . strval((integer)get_member()) . ' AND active_until>' . strval(time()) . ') AND ' . db_string_equal_to('module_the_name', $permissions_module);
        $cat_access = list_to_map('category_name', $db->query($cat_sql, null, null, false, true));
    }

    if (key($fields) == '') {
        if (($only_titles) && (count($fields) != 0)) {
            return array();
        }
    }

    if (is_null($raw_fields)) {
        $raw_fields = array();
    }

    if (strpos(get_db_type(), 'mysql') !== false) {
        $db->query('SET SESSION MAX_EXECUTION_TIME=30000', null, null, true); // Only works in MySQL 5.7+
    }

    // This is so for example catalogue_entries.php can use brackets in it's table specifier while avoiding the table prefix after the first bracket. A bit weird, but that's our convention and it does save a small amount of typing
    $table_clause = $db->get_table_prefix() . (($table[0] == '(') ? (substr($table, 1)) : $table);
    if ($table[0] == '(') {
        $table_clause = '(' . $table_clause;
    }

    $t_rows = array();
    $t_count = 0;

    // Rating ordering, via special encoding
    if (strpos($order, 'compound_rating:') !== false) {
        list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
        $select .= ',(SELECT SUM(rating-1) FROM ' . get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS compound_rating';
        $order = 'compound_rating';
    }
    if (strpos($order, 'average_rating:') !== false) {
        list(, $rating_type, $meta_rating_id_field) = explode(':', $order);
        $select .= ',(SELECT AVG(rating) FROM ' . get_table_prefix() . 'rating WHERE ' . db_string_equal_to('rating_for_type', $rating_type) . ' AND rating_for_id=' . db_cast($meta_rating_id_field, 'CHAR') . ') AS average_rating';
        $order = 'average_rating';
    }

    // Defined-keywords/tags search
    if ((get_param_integer('keep_just_show_query', 0) == 0) && (!is_null($meta_type)) && ($content != '') && (!$only_titles)) {
        if (strpos($content, '"') !== false || preg_match('#(^|\s)-#', $content) != 0 || strpos($content, ' ') !== false) {
            list($meta_content_where) = build_content_where($content, $boolean_search, $boolean_operator, true);
            $meta_content_where = $meta_content_where; // A full-text search on keywords
        } else {
            $meta_content_where = db_string_equal_to('?', $content); // Consider it a simple keyword search
        }
        if (multi_lang_content()) {
            $keywords_where = preg_replace('#\?#', 'tm.text_original', $meta_content_where);
        } else {
            $keywords_where = preg_replace('#\?#', 'meta_keyword', $meta_content_where);
        }

        if ($keywords_where != '') {
            if ($meta_id_field == 'the_zone:the_page') { // Special case
                $meta_join = 'm.meta_for_id=' . db_function('CONCAT', array('r.the_zone', '\':\'', 'r.the_page'));
            } else {
                $meta_join = 'm.meta_for_id=' . db_cast('r.' . $meta_id_field, 'CHAR');
            }
            $extra_join = '';
            if (multi_lang_content()) {
                foreach (array_keys($fields) as $i => $field) { // Translatable fields present in 'select'
                    if (($field == '') || ($field == '!') || (strpos($select, 't1.text_original') === false)) {
                        continue;
                    }

                    $extra_join .= ' JOIN ' . $db->get_table_prefix() . 'translate t' . strval($i) . ' ON t' . strval($i) . '.id=' . $field . ' AND ' . db_string_equal_to('t' . strval($i) . '.language', user_lang());
                }
            }
            $keywords_join = ' JOIN ' . $db->get_table_prefix() . 'seo_meta_keywords m ON (' . db_string_equal_to('m.meta_for_type', $meta_type) . ' AND ' . $meta_join . ')';
            if (multi_lang_content()) {
                $keywords_join .= ' JOIN ' . $db->get_table_prefix() . 'translate tm ON tm.id=m.meta_keyword AND ' . db_string_equal_to('tm.language', user_lang());
            }
            $_keywords_query = $table_clause . $keywords_join . $extra_join;
            $_keywords_query .= ' WHERE ' . $keywords_where;
            $_keywords_query .= (($where_clause != '') ? (' AND ' . $where_clause) : '');

            $keywords_query = 'SELECT ' . $select . ' FROM ' . $_keywords_query;
            if (!db_has_subqueries($db->connection_read)) {
                $_count_query_keywords_search = 'SELECT COUNT(*) FROM ' . $_keywords_query;
            } else {
                $tmp_subquery = 'SELECT 1 AS x FROM ' . $_keywords_query;
                $GLOBALS['SITE_DB']->static_ob->apply_sql_limit_clause($tmp_subquery, MAXIMUM_RESULT_COUNT_POINT);

                $_count_query_keywords_search = '(SELECT COUNT(*) FROM (' . $tmp_subquery . ') counter)';
            }

            if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC')) {
                $keywords_query .= ' ORDER BY ' . $order;
                if ($direction == 'DESC') {
                    $keywords_query .= ' DESC';
                }
            }

            $db->dedupe_mode = true;

            cms_profile_start_for('SEARCH:t_keyword_search_rows_count');
            $t_keyword_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_keywords_search), true);
            cms_profile_end_for('SEARCH:t_keyword_search_rows_count', $_count_query_keywords_search);
            if (is_null($t_keyword_search_rows_count)) {
                $t_keyword_search_rows_count = MAXIMUM_RESULT_COUNT_POINT; // Too slow, so just put in a maximum
            }
            $t_count += $t_keyword_search_rows_count;

            cms_profile_start_for('SEARCH:t_keyword_search_rows');
            $t_keyword_search_rows = $db->query(remove_unneeded_joins_rough($keywords_query), $max + $start);
            cms_profile_end_for('SEARCH:t_keyword_search_rows', $keywords_query);
            if (is_null($t_keyword_search_rows)) {
                warn_exit(do_lang_tempcode('SEARCH_QUERY_TOO_SLOW'));
            }
            $t_rows = array_merge($t_rows, $t_keyword_search_rows);

            $db->dedupe_mode = false;
        } else {
            $_count_query_keywords_search = null;
            $t_keyword_search_rows = array();
        }
    } else {
        $_count_query_keywords_search = null;
        $t_keyword_search_rows = array();
    }

    $orig_table_clause = $table_clause;

    // Main content search
    if (!$only_search_meta) {
        if (multi_lang_content()) {
            $where_alternative_matches = array();

            if (($content_where != '') || (preg_match('#t\d+\.text_original#', $where_clause) != 0) || (preg_match('#t\d+\.text_original#', $select) != 0)) {
                // Each of the fields represents an 'OR' match, so we put it together into a list ($where_alternative_matches) of specifiers for each. Hopefully we will 'UNION' them rather than 'OR' them as it is much more efficient in terms of table index usage

                $where_alternative_matches = array();
                foreach (array_keys($fields) as $i => $field) { // Referenced fields in where condition must result in the shared table clause having a reference to the translate for that
                    if ((strpos($select, 't' . strval($i) . '.text_original') !== false) || (strpos($where_clause, 't' . strval($i) . '.text_original') !== false)) {
                        $tc_add = ' JOIN ' . $db->get_table_prefix() . 'translate t' . strval($i) . ' ON t' . strval($i) . '.id=' . $field . ' AND ' . db_string_equal_to('t' . strval($i) . '.language', user_lang());
                        $orig_table_clause .= $tc_add;
                    }
                }
                foreach (array_keys($fields) as $i => $field) { // Translatable fields
                    if (($field == '') || ($field == '!')) {
                        continue;
                    }

                    if ($field == $order) {
                        $order = 't' . $i . '.text_original'; // Ah, remap to the textual equivalent then
                    }

                    $tc_add = ' JOIN ' . $db->get_table_prefix() . 'translate t' . strval($i) . ' ON t' . strval($i) . '.id=' . $field . ' AND ' . db_string_equal_to('t' . strval($i) . '.language', user_lang());
                    if (strpos($orig_table_clause, $tc_add) !== false) {
                        $tc_add = '';
                    }

                    if ((!$only_titles) || ($i == 0)) {
                        $where_clause_2 = preg_replace('#\?#', 't' . strval($i) . '.text_original', $content_where);
                        $where_clause_3 = $where_clause;
                        if (($table == 'f_members') && (substr($field, 0, 6) == 'field_') && (db_has_subqueries($db->connection_read))) { // HACKHACK
                            $where_clause_3 .= (($where_clause == '') ? '' : ' AND ') . 'NOT EXISTS (SELECT * FROM ' . $db->get_table_prefix() . 'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id=' . substr($field, 6) . ' AND cpfp.guest_view=0)';
                        }

                        if (($order == '') && (db_has_expression_ordering($db->connection_read)) && ($content_where != '')) {
                            $_select = preg_replace('#\?#', 't' . strval($i) . '.text_original', $content_where) . ' AS contextual_relevance';
                        } else {
                            $_select = '1';
                        }

                        $_table_clause = $orig_table_clause . $tc_add;

                        if (preg_match('#^f\d+\.cv_value$#', $field) != 0) {
                            // Needed to make the where condition indexed
                            $_table_clause = str_replace(' LEFT JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', ' JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', $_table_clause);
                        }

                        $where_alternative_matches[] = array($where_clause_2, $where_clause_3, $_select, $_table_clause, 't' . strval($i));
                    } else {
                        $_table_clause = $orig_table_clause . $tc_add;

                        $where_alternative_matches[] = array('1=0', '', '1', $_table_clause, 't' . strval($i));
                    }
                }
                if ($content_where != '') { // Non-translatable fields
                    foreach ($raw_fields as $i => $field) {
                        if (($only_titles) && ($i != 0)) {
                            break;
                        }

                        $where_clause_2 = preg_replace('#\?#', $field, $content_where);
                        $where_clause_3 = $where_clause;
                        if (($table == 'f_members') && (substr($field, 0, 6) == 'field_') && (db_has_subqueries($db->connection_read))) {
                            $where_clause_3 .= (($where_clause == '') ? '' : ' AND ') . 'NOT EXISTS (SELECT * FROM ' . $db->get_table_prefix() . 'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id=' . substr($field, 6) . ' AND cpfp.guest_view=0)';
                        }

                        if (($order == '') && (db_has_expression_ordering($db->connection_read)) && ($content_where != '')) {
                            $_select = preg_replace('#\?#', $field, $content_where) . ' AS contextual_relevance';
                        } else {
                            $_select = '1';
                        }

                        $_table_clause = $orig_table_clause;

                        if (preg_match('#^f\d+\.cv_value$#', $field) != 0) {
                            // Needed to make the where condition indexed
                            $_table_clause = str_replace(' LEFT JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', ' JOIN ' . get_table_prefix() . 'catalogue_entry_linkage l', $_table_clause);
                        }

                        $where_alternative_matches[] = array($where_clause_2, $where_clause_3, $_select, $_table_clause, null);
                    }
                }
            }

            if (count($where_alternative_matches) == 0) {
                $where_alternative_matches[] = array($where_clause, '', '', $table_clause, null);
            } else {
                if (($order == '') && (db_has_expression_ordering($db->connection_read)) && ($content_where != '')) {
                    $order = 'contextual_relevance DESC';
                }
            }

            $group_by_ok = (can_arbitrary_groupby() && $meta_id_field === 'id');
            if (strpos($table, ' LEFT JOIN') === false) {
                $group_by_ok = false; // Don't actually need to do a group by, as no duplication possible. We want to avoid GROUP BY as it forces MySQL to create a temporary table, slowing things down a lot.
            }

            // Work out main query
            $query = '';
            $main_query_parts = array();
            foreach ($where_alternative_matches as $parts) { // We UNION them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
                list($where_clause_2, $where_clause_3, $_select, $_table_clause, $tid) = $parts;

                $where_clause_3 = $where_clause_2 . (($where_clause_3 == '') ? '' : ((($where_clause_2 == '') ? '' : ' AND ') . $where_clause_3));

                $main_query_part = 'SELECT ' . $select . (($_select == '') ? '' : ',') . $_select . ' FROM ' . $_table_clause . (($where_clause_3 == '') ? '' : ' WHERE ' . $where_clause_3);
                if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                    $main_query_part .= ' ORDER BY ' . $order;
                    if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                        $main_query_part .= ' DESC';
                    }
                }

                $GLOBALS['SITE_DB']->static_ob->apply_sql_limit_clause($main_query_part, $max + $start);

                $main_query_parts[] = remove_unneeded_joins_rough($main_query_part);
            }
            foreach ($main_query_parts as $part_i => $main_query_part) {
                if ($part_i != 0) {
                    $query .= ' UNION ';
                }
                $query .= '(' . $main_query_part . ')';
            }
            // Work out COUNT(*) query using one of a few possible methods. It's not efficient and stops us doing proper merge-sorting between content types (and possible not accurate - if we use an efficient but non-deduping COUNT strategy) if we have to use this, so we only do it if there are too many rows to fetch in one go.
            $_query = '';
            if (!db_has_subqueries($db->connection_read)) {
                foreach ($where_alternative_matches as $parts) {
                    list($where_clause_2, $where_clause_3, , $_table_clause, $tid) = $parts;

                    $where_clause_3 = $where_clause_2 . (($where_clause_3 == '') ? '' : ((($where_clause_2 == '') ? '' : ' AND ') . $where_clause_3));

                    $_query .= (($where_clause_3 != '') ? ((($_query == '') ? ' WHERE ' : ' OR ') . $where_clause_3) : '');
                }
                $_count_query_main_search = 'SELECT COUNT(*) FROM ' . $table_clause . $_query;
            } else { // This is inaccurate (does not filter dupes from each +'d query) but much more efficient on MySQL
                foreach ($where_alternative_matches as $parts) { // We "+" them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
                    list($where_clause_2, $where_clause_3, $_select, $_table_clause, $tid) = $parts;

                    if ($_query != '') {
                        $_query .= '+';
                    }

                    $where_clause_3 = $where_clause_2 . (($where_clause_3 == '') ? '' : ((($where_clause_2 == '') ? '' : ' AND ') . $where_clause_3));

                    if (!db_has_subqueries($db->connection_read)) {
                        $_query .= remove_unneeded_joins_rough('(SELECT COUNT(*) FROM ' . $_table_clause . (($where_clause_3 == '') ? '' : (' WHERE ' . $where_clause_3)) . ')');
                    } else { // Has to do a nested subquery to reduce scope of COUNT(*), because the unbounded full-text's binary tree descendance can be extremely slow on physical disks if common words exist that aren't defined as MySQL stop words
                        $tmp_subquery = 'SELECT 1 AS x FROM ' . $_table_clause . (($where_clause_3 == '') ? '' : (' WHERE ' . $where_clause_3));
                        $GLOBALS['SITE_DB']->static_ob->apply_sql_limit_clause($tmp_subquery, MAXIMUM_RESULT_COUNT_POINT);

                        $_query .= remove_unneeded_joins_rough('(SELECT COUNT(*) FROM (' . $tmp_subquery . ') counter)');
                    }
                }
                $_count_query_main_search = 'SELECT (' . $_query . ')';
            }

            if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                $query .= ' ORDER BY ' . $order;
                if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                    $query .= ' DESC';
                }
            }

            if (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN'])) {
                if (get_param_integer('keep_show_query', 0) == 1) {
                    attach_message($query, 'inform');
                }
                if (get_param_integer('keep_just_show_query', 0) == 1) {
                    safe_ini_set('ocproducts.xss_detect', '0');
                    header('Content-type: text/plain; charset=' . get_charset());
                    exit($query);
                }
            }

            $db->dedupe_mode = true;

            cms_profile_start_for('SEARCH:t_main_search_rows_count');
            $t_main_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_main_search));
            if (is_null($t_main_search_rows_count)) {
                $t_main_search_rows_count = MAXIMUM_RESULT_COUNT_POINT; // Too slow, so just put in a maximum
            }
            cms_profile_end_for('SEARCH:t_main_search_rows_count', $_count_query_main_search);
            $t_count += $t_main_search_rows_count;

            cms_profile_start_for('SEARCH:t_main_search_rows');
            $t_main_search_rows = $db->query(remove_unneeded_joins_rough($query), $max + $start, null, false, true);
            cms_profile_end_for('SEARCH:t_main_search_rows', $query);
            if (is_null($t_main_search_rows)) {
                warn_exit(do_lang_tempcode('SEARCH_QUERY_TOO_SLOW'));
            }

            $db->dedupe_mode = false;
        } else { // Much simpler code if we don't have multi-lang-content
            list(, $boolean_operator, $body_where, $include_where, $disclude_where) = build_content_where($content, $boolean_search, $boolean_operator);

            $simple_table = preg_replace('# .*#', '', $table);
            $indices_for_table = $GLOBALS['SITE_DB']->query_select('db_meta_indices', array('i_name', 'i_fields'), array('i_table' => $simple_table));

            $where_clause_and = '';
            $all_fields = array_merge($raw_fields, array_keys($fields));
            reset($raw_fields);
            reset($fields);
            $search_clause_sets = array(array($include_where, 'AND'), array($body_where, $boolean_operator));
            foreach ($search_clause_sets as $search_clause_set) {
                list($_where, $_operator) = $search_clause_set;

                foreach ($_where as $__where) {
                    // See if we have a combined fulltext index covering multiple columns
                    $has_combined_index_coverage = false;
                    foreach ($indices_for_table as $index) {
                        if (substr($index['i_name'], 0, 1) == '#') {
                            $index_coverage = explode(',', $index['i_fields']);

                            $has_combined_index_coverage = true;
                            foreach ($all_fields as $field) {
                                if (($field == '') || ($field == '!')) {
                                    continue;
                                }

                                $field_stripped = preg_replace('#.*\.#', '', $field);
                                if (!in_array($field_stripped, $index_coverage)) {
                                    $has_combined_index_coverage = false;
                                    break;
                                }
                            }

                            if ($has_combined_index_coverage) {
                                break;
                            }
                        }
                    }

                    $fields_keys = array_keys($fields);

                    $where_clause_or = '';
                    $where_clause_or_fields = '';
                    foreach ($all_fields as $field) {
                        if (($field == '') || ($field == '!')) {
                            continue;
                        }

                        if (($only_titles) && ($field !== current($raw_fields)) && (!isset($fields_keys[0]) || $field !== $fields_keys[0]) && (!isset($fields_keys[1]) || $fields_keys[0] !== '!' || $field !== $fields_keys[1])) {
                            break;
                        }

                        if (($table == 'f_members') && (substr($field, 0, 6) == 'field_') && (db_has_subqueries($db->connection_read))) {
                            if ($where_clause_or != '') {
                                $where_clause_or .= ' OR ';
                            }
                            $where_clause_or .= preg_replace('#\?#', $field, $__where);
                            $where_clause_or .= ' AND NOT EXISTS (SELECT * FROM ' . $db->get_table_prefix() . 'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id=' . substr($field, 6) . ' AND cpfp.guest_view=0)';
                        } else {
                            if ((strpos($__where, ' AGAINST ') !== false) && ($has_combined_index_coverage)) {
                                if ($where_clause_or_fields != '') {
                                    $where_clause_or_fields .= ',';
                                }
                                $where_clause_or_fields .= $field;
                            } else {
                                if ($where_clause_or != '') {
                                    $where_clause_or .= ' OR ';
                                }
                                $where_clause_or .= preg_replace('#\?#', $field, $__where);
                            }
                        }
                    }

                    if ($where_clause_or_fields != '') {
                        if ($where_clause_or != '') {
                            $where_clause_or .= ' OR ';
                        }
                        $where_clause_or .= preg_replace('#\?#', $where_clause_or_fields, $__where);
                    }

                    if ($where_clause_or != '') {
                        if ($where_clause_and != '') {
                            $where_clause_and .= ' ' . $boolean_operator . ' ';
                        }

                        $where_clause_and .= '(' . $where_clause_or . ')';
                    }
                }
            }
            if ($disclude_where != '') {
                foreach ($all_fields as $field) {
                    if (($field == '') || ($field == '!')) {
                        continue;
                    }

                    if (($only_titles) && ($field !== current($raw_fields)) && ($field !== key($fields))) {
                        break;
                    }

                    if ($where_clause != '') {
                        $where_clause .= ' AND ';
                    }
                    $where_clause .= preg_replace('#\?#', $field, $disclude_where);
                }
            }

            $group_by_ok = (can_arbitrary_groupby() && $meta_id_field === 'id');
            if (strpos($table, ' LEFT JOIN') === false) {
                $group_by_ok = false; // Don't actually need to do a group by, as no duplication possible. We want to avoid GROUP BY as it forces MySQL to create a temporary table, slowing things down a lot.
            }

            // Work out our queries
            $query = ' FROM ' . $table_clause . (($where_clause == '') ? '' : (' WHERE ' . $where_clause));
            if ($where_clause_and != '') {
                $query .= (($where_clause == '') ? ' WHERE ' : ' AND ') . '(' . $where_clause_and . ')';
            }
            if ($group_by_ok && false/*Actually we cannot assume that r.id exists*/) {
                $_count_query_main_search = 'SELECT COUNT(DISTINCT r.id)' . $query;
            } else {
                if (!db_has_subqueries($db->connection_read)) {
                    $_count_query_main_search = 'SELECT COUNT(*) ' . $query;
                } else { // Has to do a nested subquery to reduce scope of COUNT(*), because the unbounded full-text's binary tree descendance can be extremely slow on physical disks if common words exist that aren't defined as MySQL stop words
                    $tmp_subquery = 'SELECT 1 AS x' . $query;
                    $GLOBALS['SITE_DB']->static_ob->apply_sql_limit_clause($tmp_subquery, MAXIMUM_RESULT_COUNT_POINT);

                    $_count_query_main_search = '(SELECT COUNT(*) FROM (' . $tmp_subquery . ') counter)';
                }
            }
            $query = 'SELECT ' . $select . $query . ($group_by_ok ? ' GROUP BY r.id' : '');
            if (($order != '') && ($order . ' ' . $direction != 'contextual_relevance DESC') && ($order != 'contextual_relevance DESC')) {
                $query .= ' ORDER BY ' . $order;
                if (($direction == 'DESC') && (substr($order, -4) != ' ASC') && (substr($order, -5) != ' DESC')) {
                    $query .= ' DESC';
                }
            }

            if (get_param_integer('keep_show_query', 0) == 1) {
                attach_message($query, 'inform');
            }
            if (get_param_integer('keep_just_show_query', 0) == 1) {
                safe_ini_set('ocproducts.xss_detect', '0');
                @header('Content-type: text/plain; charset=' . get_charset());
                exit($query);
            }

            $db->dedupe_mode = true;

            cms_profile_start_for('SEARCH:t_main_search_rows_count');
            $t_main_search_rows_count = $db->query_value_if_there(remove_unneeded_joins_rough($_count_query_main_search));
            cms_profile_end_for('SEARCH:t_main_search_rows_count', $_count_query_main_search);
            $t_count += $t_main_search_rows_count;

            cms_profile_start_for('SEARCH:t_main_search_rows');
            $t_main_search_rows = $db->query(remove_unneeded_joins_rough($query), $max + $start, null, false, true/*, $fields Actually will hurt performance - we usually won't show text_parsed fields as we re-parse Comcode with syntax highlighting*/);
            cms_profile_end_for('SEARCH:t_main_search_rows', $query);
            if ($t_main_search_rows === null) {
                $t_main_search_rows = array(); // In case of a failed search query
            }

            $db->dedupe_mode = false;
        }
    } else {
        $t_main_search_rows = array();
    }

    // Clean results and return
    // NB: We don't use the count_query's any more (except when using huge data sets, see above), because you can't actually just add them because they overlap. So instead we fetch all results and throw some away.

    $t_rows = array_merge($t_rows, $t_main_search_rows);
    if (count($t_rows) > 0) {
        $t_rows_new = array();
        if ((array_key_exists('id', $t_rows[0])) || (array_key_exists('_primary_id', $t_rows[0]))) {
            $done = array();
            foreach ($t_rows as $t_row) {
                if (array_key_exists('id', $t_row)) {
                    if (array_key_exists($t_row['id'], $done)) {
                        continue;
                    }
                    $done[$t_row['id']] = 1;
                } elseif (array_key_exists('_primary_id', $t_row)) {
                    if (array_key_exists($t_row['_primary_id'], $done)) {
                        continue;
                    }
                    $done[$t_row['_primary_id']] = true;
                }
                $t_rows_new[] = $t_row;
            }
        } else {
            foreach ($t_rows as $t_row) {
                $t_row_copy = $t_row;
                unset($t_row_copy['contextual_relevance']);
                foreach ($t_rows_new as $_t_row) {
                    $_t_row_copy = $_t_row;
                    unset($_t_row_copy['contextual_relevance']);
                    if ($_t_row_copy == $t_row_copy) {
                        continue 2;
                    }
                }
                $t_rows_new[] = $t_row;
            }
        }
        $t_rows = $t_rows_new;
    }
    if ((get_param_integer('keep_show_query', 0) == 1) && (($GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())) || ($GLOBALS['IS_ACTUALLY_ADMIN']))) {
        if ((array_key_exists(0, $t_rows)) && (array_key_exists('id', $t_rows[0]))) {
            $results = var_export(array_unique(collapse_1d_complexity('id', $t_rows)), true);
        } else {
            $results = var_export($t_rows, true);
        }
        attach_message(do_lang('COUNT_RESULTS') . ': ' . $results, 'inform');
    }

    if (isset($cat_access)) {
        $before = count($t_rows);
        foreach ($t_rows as $i => $row) {
            if (!array_key_exists(@strval($row[$permissions_field]), $cat_access)) {
                unset($t_rows[$i]);
            }
        }
    }
    $final_result_rows = $t_rows;
    array_splice($final_result_rows, $max * 2 + $start); // We return more than max in case our search hook does some extra in-code filtering (Catalogues, Comcode pages). It shouldn't really but sometimes it has to, and it certainly shouldn't filter more than 50%. Also so our overall ordering can be better.
    if ((count($t_main_search_rows) < $max) && (count($t_keyword_search_rows) < $max)) {
        $GLOBALS['TOTAL_SEARCH_RESULTS'] += min($t_count, count($final_result_rows));
    } else {
        $GLOBALS['TOTAL_SEARCH_RESULTS'] += $t_count;
    }

    return $final_result_rows;
}

/**
 * Optimise out JOIN queries we don't need, using a fairly simple MySQL-centric algorithm.
 * We do this as apparently on at least some MySQL versions unused LEFT JOINs are not optimised out.
 * Assumes that any fields from a join are referenced using the JOINs alias.
 *
 * @param  string $query The SQL query
 * @return string Optimised query
 */
function remove_unneeded_joins_rough($query)
{
    $left_joins = array();

    $max = 0;
    $matches = array();
    $matches2 = array();
    do {
        $l_pos = strpos($query, ' LEFT JOIN ', $max);
        if ($l_pos !== false) {
            $match_attempt = preg_match('#^( LEFT JOIN \w+( AS)? (\w+) ON )(.*)#', substr($query, $l_pos), $matches);
            if ($match_attempt != 0) {
                $join_length = strlen($matches[1]); // Will be incremented
                $alias = $matches[3];
                $equate_term_1 = '[\w\.]+=[\w\.]+';
                $equate_term_2 = '[\w\.]+=\'[^\']+\'';
                $equate_term_3 = '[\w\.]+=CAST\([\w\.]+ AS \w+(\(\d+\))?\)';
                $equate_terms = '(' . implode('|', array($equate_term_1, $equate_term_2, $equate_term_3)) . ')';
                $multiplied_equate_terms = $equate_terms . '( AND ' . $equate_terms . ')*';
                $components = preg_match('#^(\(' . $multiplied_equate_terms . '\)|' . $multiplied_equate_terms . ')(?= (LEFT|JOIN|WHERE))#', $matches[4], $matches2);
                if ($components != 0) {
                    $join_length += strlen($matches2[0]);
                    $left_joins[$alias] = substr($query, $l_pos, $join_length);
                }
            }
            $max = $l_pos + 1;
        }
    } while ($l_pos !== false);

    $left_joins = array_reverse($left_joins, true);
    foreach ($left_joins as $alias => $join) {
        if (preg_match('#[\(\s=]' . $alias . '\.#', str_replace($join, '', $query)) == 0) {
            // Alias not used
            $query = str_replace($join, '', $query);
        }
    }

    return $query;
}

/**
 * Take a search string and find boolean search parameters from it.
 *
 * @param  string $search_filter The search string
 * @return array Words to search under the boolean operator, words that must be included, words that must not be included.
 *
 * @ignore
 */
function _boolean_search_prepare($search_filter)
{
    $content_explode = explode(' ', $search_filter);

    $body_words = array();
    $include_words = array();
    $disclude_words = array();
    for ($i = 0; $i < count($content_explode); $i++) {
        $word = trim($content_explode[$i]);
        if (($word == '') || ($word == '+') || ($word == '-')) {
            continue;
        }

        // Handle quotes
        if ($word[0] == '"') {
            $i++;
            while ($i < count($content_explode)) {
                $word2 = trim($content_explode[$i]);
                if ($word2 != '') {
                    $word .= ' ' . $word2;
                }
                if (substr($word2, -1) == '"') {
                    break;
                } else {
                    $i++;
                }
            }
            if (substr($word, -1) != '"') {
                $word .= '"';
            }
            $word = substr($word, 1, strlen($word) - 2);
        }

        if ($word[0] == '+') {
            $include_words[] = $word;
        } elseif ($word[0] == '-') {
            $disclude_words[] = $word;
        } else {
            $body_words[] = $word;
        }
    }

    return array($body_words, $include_words, $disclude_words);
}

/**
 * Perform a database-style in-memory boolean search on single item.
 *
 * @param  array $filter A map of POST data in search-form style. May contain 'only_titles', 'content' (the critical one!) and 'conjunctive_operator'
 * @param  string $title The title to try and match
 * @param  ?string $post The post to try and match (null: not used)
 * @return boolean Whether we have a match
 */
function in_memory_search_match($filter, $title, $post = null)
{
    if ((!array_key_exists('content', $filter)) || ($filter['content'] == '')) {
        return true;
    }

    $search_filter = $filter['content'];
    if (((array_key_exists('only_titles', $filter)) && ($filter['only_titles'] == 1)) || (is_null($post))) {
        $context = $title;
    } else {
        $context = $title . ' ' . $post;
    }

    $boolean_operator = array_key_exists('conjunctive_operator', $filter) ? $filter['conjunctive_operator'] : 'OR';

    list($body_words, $include_words, $disclude_words) = _boolean_search_prepare($search_filter);
    foreach ($include_words as $word) {
        if (!simulated_wildcard_match($context, $word)) {
            return false;
        }
    }
    foreach ($disclude_words as $word) {
        if (simulated_wildcard_match($context, $word)) {
            return false;
        }
    }
    if ($boolean_operator == 'OR') {
        $count = 0;
        foreach ($body_words as $word) {
            if (simulated_wildcard_match($context, $word)) {
                $count++;
            }
        }
        if ($count == 0) {
            return false;
        }
    } else {
        foreach ($body_words as $word) {
            if (!simulated_wildcard_match($context, $word)) {
                return false;
            }
        }
    }
    return true;
}

/**
 * Build a fulltext query WHERE clause from given content.
 *
 * @param  string $content The search content
 * @param  boolean $boolean_search Whether it's a boolean search
 * @param  string $boolean_operator Boolean operation to use
 * @set    AND OR
 * @param  boolean $full_coverage Whether we can assume we require full coverage (i.e. not substring matches)
 * @return array A tuple (any SQL component may be blank): The combined where clause SQL, the boolean operator, body where clause SQL, positive where clause SQL, negative where clause SQL
 */
function build_content_where($content, $boolean_search, &$boolean_operator, $full_coverage = false)
{
    list($body_words, $include_words, $disclude_words) = _boolean_search_prepare($content);

    require_code('search');

    $under_radar = false;
    if ((is_under_radar($content)) && ($content != '')) {
        $under_radar = true;
    }
    if (($under_radar) || ($boolean_search) || (!db_has_full_text($GLOBALS['SITE_DB']->connection_read))) {
        if (!in_array(strtoupper($boolean_operator), array('AND', 'OR'))) {
            log_hack_attack_and_exit('ORDERBY_HACK');
        }

        if ($content == '') {
            $content_where = '';
            $body_where = array();
            $include_where = array();
            $disclude_where = '';
        } else {
            if ((get_param_integer('force_like', 0) == 0) && (db_has_full_text($GLOBALS['SITE_DB']->connection_read)) && (method_exists($GLOBALS['SITE_DB']->static_ob, 'db_has_full_text_boolean')) && ($GLOBALS['SITE_DB']->static_ob->db_has_full_text_boolean()) && (!$under_radar)) {
                $content_where = db_full_text_assemble($content, true);
                $body_where = array($content_where);
                $include_where = array();
                $disclude_where = '';
            } else {
                list($content_where, $boolean_operator, $body_where, $include_where, $disclude_where) = db_like_assemble($content, $boolean_operator, $full_coverage);
                if ($content_where == '') {
                    $content_where = '1=1';
                }
            }
        }
    } else {
        if ($content == '') {
            $content_where = '';
            $body_where = array();
            $include_where = array();
            $disclude_where = '';
        } else {
            $content_where = db_full_text_assemble($content, false);
            $body_where = array($content_where);
            $include_where = array();
            $disclude_where = '';
        }
        $boolean_operator = 'OR';
    }

    return array($content_where, $boolean_operator, $body_where, $include_where, $disclude_where);
}

/**
 * Generate SQL for a boolean search.
 *
 * @param  string $content Boolean search string
 * @param  string $boolean_operator Boolean operator to use
 * @set    AND OR
 * @param  boolean $full_coverage Whether we can assume we require full coverage
 * @return array A tuple (any SQL component may be blank): The combined where clause SQL, the boolean operator, body where clause SQL, positive where clause SQL, negative where clause SQL
 */
function db_like_assemble($content, $boolean_operator = 'AND', $full_coverage = false)
{
    $content = str_replace('?', '_', $content);
    $content = str_replace('*', '%', $content);

    list($body_words, $include_words, $disclude_words) = _boolean_search_prepare($content);

    $fc_before = $full_coverage ? '' : '%';
    $fc_after = $full_coverage ? '' : '%';

    $body_where = array();
    foreach ($body_words as $word) {
        if ((strtoupper($word) == $word) && (method_exists($GLOBALS['SITE_DB']->static_ob, 'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word))) {
            $body_where[] = 'CONVERT(? USING latin1) LIKE _latin1\'' . db_encode_like($fc_before . $word . $fc_after) . '\' COLLATE latin1_general_cs';
        } else {
            $body_where[] = '? LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
        }
    }
    $include_where = array();
    foreach ($include_words as $word) {
        if ((strtoupper($word) == $word) && (method_exists($GLOBALS['SITE_DB']->static_ob, 'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word))) {
            $include_where[] = 'CONVERT(? USING latin1) LIKE _latin1\'' . db_encode_like($fc_before . $word . $fc_after) . '\' COLLATE latin1_general_cs';
        } else {
            $include_where[] = '? LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
        }
    }
    $disclude_where = '';
    foreach ($disclude_words as $word) {
        if ($disclude_where != '') {
            $disclude_where .= ' AND ';
        }
        if ((strtoupper($word) == $word) && (method_exists($GLOBALS['SITE_DB']->static_ob, 'db_has_collate_settings')) && ($GLOBALS['SITE_DB']->static_ob->db_has_collate_settings($GLOBALS['SITE_DB']->connection_read)) && (!is_numeric($word))) {
            $disclude_where .= 'CONVERT(? USING latin1) NOT LIKE _latin1\'' . db_encode_like($fc_before . $word . $fc_after) . '\' COLLATE latin1_general_cs';
        } else {
            $disclude_where .= '? NOT LIKE \'' . db_encode_like($fc_before . $word . $fc_after) . '\'';
        }
    }

    // $content_where combines all
    $content_where = '';
    if ($body_words != array()) {
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . implode($boolean_operator, $body_where) . ')';
    }
    if ($include_where != array()) {
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . implode(' AND ', $include_where) . ')';
    }
    if ($disclude_where != '') {
        if ($content_where != '') {
            $content_where .= ' AND ';
        }
        $content_where .= '(' . $disclude_where . ')';
    }

    return array($content_where, $boolean_operator, $body_where, $include_where, $disclude_where);
}

/**
 * Sort search results as returned by the search hook.
 *
 * @param  array $hook_results Search results from the search hook, assumed already sorted
 * @param  array $results Existing array of results (originally starts blank)
 * @return array Sorted results
 * @param  string $direction Sort direction
 * @set    ASC DESC
 */
function sort_search_results($hook_results, $results, $direction)
{
    // Do a merge sort
    $results_position = 0;
    $done_all = false;
    foreach ($hook_results as $i => $result) {
        while (true) {
            if (!array_key_exists($results_position, $results)) { // If we've run off the end of our current results
                $results = array_merge($results, array_slice($hook_results, $i));
                $done_all = true;
                break;
            }
            if ((array_key_exists('orderer', $result)) && (array_key_exists('orderer', $results[$results_position])) && ((($direction == 'ASC') && ($result['orderer'] <= $results[$results_position]['orderer'])) || (($direction == 'DESC') && ($result['orderer'] >= $results[$results_position]['orderer'])))) { // If it definitely beats, put in front. If it's unknown (no orderer on one - which is very common) it has to go on the end so FIFO is preserved
                $results = array_merge(array_slice($results, 0, $results_position), array($result), array_slice($results, $results_position));
                break;
            }
            $results_position++;
        }
        if ($done_all) {
            break;
        }
    }

    return $results;
}

/**
 * Build a templated list of the given search results, for viewing.
 *
 * @param  array $results Search results
 * @param  integer $start Start index
 * @param  integer $max Maximum index
 * @param  string $direction Sort direction
 * @set    ASC DESC
 * @param  boolean $general_search Whether this is a general search, rather than a search for a specific result-type (such as all members)
 * @return Tempcode Interface
 */
function build_search_results_interface($results, $start, $max, $direction, $general_search = false)
{
    require_code('content');

    $out = new Tempcode();
    $i = 0;
    $tabular_results = array();
    foreach ($results as $result) {
        if (array_key_exists('restricted', $result)) {
            continue; // This has been blanked out due to insufficient access permissions or some other reason
        }

        $content_type = convert_composr_type_codes('search_hook', $result['type'], 'content_type');
        $id = mixed();
        if ($content_type != '') {
            require_code('content');
            $cma_ob = get_content_object($content_type);
            $cma_info = $cma_ob->info();
            $id = extract_content_str_id_from_data($result['data'], $cma_info);
        }

        if (($i >= $start) && ($i < $start + $max)) {
            if (array_key_exists('template', $result)) {
                $rendered_result = $result['template'];
            } else {
                $rendered_result = $result['object']->render($result['data']);
            }
            if (!is_null($rendered_result)) {
                if (is_array($rendered_result)) {
                    $class = get_class($result['object']);
                    if (!array_key_exists($class, $tabular_results)) {
                        $tabular_results[$class] = array();
                    }
                    $tabular_results[$class][] = $rendered_result;
                } else {
                    $out->attach(do_template('SEARCH_RESULT', array('_GUID' => '47da093f9ace87819e246f0cec1402a9', 'TYPE' => $content_type, 'ID' => $id, 'CONTENT' => $rendered_result)));
                }
            }
        } else {
            $out->attach(static_evaluate_tempcode(do_template('SEARCH_RESULT', array('_GUID' => 'd8422a971f55a8a94d090861d519ca7a', 'TYPE' => $content_type, 'ID' => $id))));
        }
        $i++;
    }
    foreach ($tabular_results as $tabular_type => $types_results) {
        // Normalisation process
        $ultimate_field_map = array();
        foreach ($types_results as $r) {
            $ultimate_field_map += $r;
        }
        $ultimate_field_map = array_keys($ultimate_field_map);
        foreach ($types_results as $i => $r) {
            $r2d2 = array();
            foreach ($ultimate_field_map as $key) {
                if (!array_key_exists($key, $r)) {
                    $r[$key] = '';
                }
                $r2d2[$key] = $r[$key];
            }
            $r = $r2d2;
            $types_results[$i] = array('R' => $r);
        }

        // Output
        $out->attach(do_template('SEARCH_RESULT_TABLE', array('_GUID' => '816ec14dc0df432ca6e1e1014ef1f3d1', 'HEADERS' => $ultimate_field_map, 'ROWS' => $types_results)));
    }

    set_extra_request_metadata(array(
        'opensearch_totalresults' => strval($i),
        'opensearch_startindex' => strval($start),
        'opensearch_itemsperpage' => strval($max),
    ));

    $SEARCH__CONTENT_BITS = null;

    return $out;
}
