From 004efe662938a38ce47575cfb43276a03f47fbc2 Mon Sep 17 00:00:00 2001 From: skodak Date: Sun, 15 Jun 2008 10:12:27 +0000 Subject: [PATCH] MDL-15245 converted dml searchlib --- lib/searchlib.php | 128 +++++++++++++++++++++++++++------------------- mod/forum/lib.php | 30 ++++------- 2 files changed, 86 insertions(+), 72 deletions(-) diff --git a/lib/searchlib.php b/lib/searchlib.php index 40a0f000cc..7343afa945 100644 --- a/lib/searchlib.php +++ b/lib/searchlib.php @@ -17,8 +17,9 @@ define("TOKEN_INSTANCE","8"); // Class to hold token/value pairs after they're parsed. class search_token { - var $value; - var $type; + private $value; + private $type; + function search_token($type,$value){ $this->type = $type; $this->value = $this->sanitize($value); @@ -40,11 +41,11 @@ class search_token { } - -// This class does the heavy lifting of lexing the search string into tokens. -// Using a full-blown lexer is probably overkill for this application, but -// might be useful for other tasks. - +/** + * This class does the heavy lifting of lexing the search string into tokens. + * Using a full-blown lexer is probably overkill for this application, but + * might be useful for other tasks. + */ class search_lexer extends Lexer{ function search_lexer(&$parser){ @@ -162,15 +163,14 @@ class search_lexer extends Lexer{ - -// This class takes care of sticking the proper token type/value pairs into -// the parsed token array. -// Most functions in this class should only be called by the lexer, the -// one exception being getParseArray() which returns the result. - +/** + * This class takes care of sticking the proper token type/value pairs into + * the parsed token array. + * Most functions in this class should only be called by the lexer, the + * one exception being getParseArray() which returns the result. + */ class search_parser { - var $tokens; - + private $tokens; // This function is called by the code that's interested in the result of the parse operation. function get_parsed_array(){ @@ -302,16 +302,19 @@ class search_parser { } } -// Primitive function to generate a SQL string from a parse tree -// using TEXT indexes. If searches aren't suitable to use TEXT -// this function calls the default search_generate_SQL() one. -// -// $parsetree should be a parse tree generated by a -// search_lexer/search_parser combination. -// Other fields are database table names to search. +/** + * Primitive function to generate a SQL string from a parse tree + * using TEXT indexes. If searches aren't suitable to use TEXT + * this function calls the default search_generate_SQL() one. + * + * $parsetree should be a parse tree generated by a + * search_lexer/search_parser combination. + * Other fields are database table names to search. + */ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfield, $useridfield, $userfirstnamefield, $userlastnamefield, $timefield, $instancefield) { global $CFG; + static $p = 0; /// First of all, search for reasons to switch to standard SQL generation /// Only mysql are supported for now @@ -332,6 +335,7 @@ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfie /// Here we'll acumulate non-textual tokens $non_text_tokens = array(); + $params = array(); $ntokens = count($parsetree); if ($ntokens == 0) { @@ -386,8 +390,9 @@ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfie /// Call to standard search for pending tokens if (!empty($non_text_tokens)) { - $SQLString = search_generate_SQL($non_text_tokens, $datafield, $metafield, $mainidfield, $useridfield, + list($SQLString, $sparams) = search_generate_SQL($non_text_tokens, $datafield, $metafield, $mainidfield, $useridfield, $userfirstnamefield, $userlastnamefield, $timefield, $instancefield); + $params = array_merge($params, $sparams); } /// Build the final SQL clause if (!empty($datasearch_clause)) { @@ -401,7 +406,8 @@ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfie /// Begin with the AGAINST clause $text_sql_string .= ') AGAINST (' . "'"; /// Add the search terms - $text_sql_string .= trim($datasearch_clause); + $text_sql_string .= ':sgt'.$p; + $params['sgt'.$p++] = trim($datasearch_clause); /// Close AGAINST clause $text_sql_string .= "' IN BOOLEAN MODE)"; } @@ -418,7 +424,8 @@ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfie /// Begin with the AGAINST clause $text_sql_string .= ') AGAINST (' . "'"; /// Add the search terms - $text_sql_string .= trim($metasearch_clause); + $text_sql_string .= ':sgt'.$p; + $params['sgt'.$p++] = trim($metasearch_clause); /// Close AGAINST clause $text_sql_string .= "' IN BOOLEAN MODE)"; } @@ -432,29 +439,30 @@ function search_generate_text_SQL($parsetree, $datafield, $metafield, $mainidfie $text_sql_string .= $SQLString; } - return $text_sql_string; + return array($text_sql_string, $params); } -// Primitive function to generate a SQL string from a parse tree. -// Parameters: -// -// $parsetree should be a parse tree generated by a -// search_lexer/search_parser combination. -// Other fields are database table names to search. - +/** + * Primitive function to generate a SQL string from a parse tree. + * Parameters: + * + * $parsetree should be a parse tree generated by a + * search_lexer/search_parser combination. + * Other fields are database table names to search. + */ function search_generate_SQL($parsetree, $datafield, $metafield, $mainidfield, $useridfield, $userfirstnamefield, $userlastnamefield, $timefield, $instancefield) { global $CFG, $DB; + static $p = 0; - $LIKE = $DB->sql_ilike(); - $NOTLIKE = 'NOT ' . $LIKE; - if ($CFG->dbfamily == "postgres") { - $REGEXP = "~*"; - $NOTREGEXP = "!~*"; - } else { - $REGEXP = "REGEXP"; - $NOTREGEXP = "NOT REGEXP"; + if ($DB->sql_regex_supported()) { + $REGEXP = $DB->sql_regex(true); + $NOTREGEXP = $DB->sql_regex(false); } + $LIKE = $DB->sql_ilike(); // case-insensitive + $NOTLIKE = 'NOT ' . $LIKE; + + $params = array(); $ntokens = count($parsetree); if ($ntokens == 0) { @@ -472,49 +480,65 @@ function search_generate_SQL($parsetree, $datafield, $metafield, $mainidfield, $ $value = $parsetree[$i]->getValue(); /// Under Oracle and MSSQL, transform TOKEN searches into STRING searches and trim +- chars - if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') { + if (!$DB->sql_regex_supported()) { $value = trim($value, '+-'); if ($type == TOKEN_EXACT) { $type = TOKEN_STRING; } } + $name1 = 'sq'.$p++; + $name2 = 'sq'.$p++; + switch($type){ case TOKEN_STRING: - $SQLString .= "(($datafield $LIKE '%$value%') OR ($metafield $LIKE '%$value%') )"; + $SQLString .= "(($datafield $LIKE :$name1) OR ($metafield $LIKE :$name2))"; + $params[$name1] = "%$value%"; + $params[$name2] = "%$value%"; break; case TOKEN_EXACT: - $SQLString .= "(($datafield $REGEXP '[[:<:]]".$value."[[:>:]]') OR ($metafield $REGEXP '[[:<:]]".$value."[[:>:]]'))"; + $SQLString .= "(($datafield $REGEXP :$name1) OR ($metafield $REGEXP :$name2))"; + $params[$name1] = "[[:<:]]".$value."[[:>:]]"; + $params[$name2] = "[[:<:]]".$value."[[:>:]]"; break; case TOKEN_META: if ($metafield != '') { - $SQLString .= "($metafield $LIKE '%$value%')"; + $SQLString .= "($metafield $LIKE :$name1)"; + $params[$name1] = "%$value%"; } break; case TOKEN_USER: - $SQLString .= "(($mainidfield = $useridfield) AND (($userfirstnamefield $LIKE '%$value%') OR ($userlastnamefield $LIKE '%$value%')))"; + $SQLString .= "(($mainidfield = $useridfield) AND (($userfirstnamefield $LIKE :$name1) OR ($userlastnamefield $LIKE :$name2)))"; + $params[$name1] = "%$value%"; + $params[$name2] = "%$value%"; break; case TOKEN_USERID: - $SQLString .= "($useridfield = $value)"; + $SQLString .= "($useridfield = :$name1)"; + $params[$name1] = $value; break; case TOKEN_INSTANCE: - $SQLString .= "($instancefield = $value)"; + $SQLString .= "($instancefield = :$name1)"; + $params[$name1] = $value; break; case TOKEN_DATETO: - $SQLString .= "($timefield <= $value)"; + $SQLString .= "($timefield <= :$name1)"; + $params[$name1] = $value; break; case TOKEN_DATEFROM: - $SQLString .= "($timefield >= $value)"; + $SQLString .= "($timefield >= :$name1)"; + $params[$name1] = $value; break; case TOKEN_NEGATE: - $SQLString .= "(NOT (($datafield $LIKE '%$value%') OR ($metafield $LIKE '%$value%')))"; + $SQLString .= "(NOT (($datafield $LIKE :$name1) OR ($metafield $LIKE :$name2)))"; + $params[$name1] = "%$value%"; + $params[$name2] = "%$value%"; break; default: return ''; } } - return $SQLString; + return array($SQLString, $params); } diff --git a/mod/forum/lib.php b/mod/forum/lib.php index fd83d5cbe1..0e79c1896a 100644 --- a/mod/forum/lib.php +++ b/mod/forum/lib.php @@ -1700,13 +1700,13 @@ function forum_search_posts($searchterms, $courseid=0, $limitfrom=0, $limitnum=5 $select = array(); if (!$forum->viewhiddentimedposts) { - $select[] = "(d.userid = ? OR (d.timestart < ? AND (d.timeend = 0 OR d.timeend > ?)))"; - $params = array($USER->id, $now, $now); + $select[] = "(d.userid = :userid OR (d.timestart < : AND (d.timeend = 0 OR d.timeend > :timeend)))"; + $params = array('userid'=>$USER->id, 'timestart'=>$now, 'timeend'=>$now); } if ($forum->type == 'qanda') { if (!empty($forum->onlydiscussions)) { - list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions); + list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda0'); $params = array_merge($params, $discussionid_params); $select[] = "(d.id $discussionid_sql OR p.parent = 0)"; } else { @@ -1715,39 +1715,28 @@ function forum_search_posts($searchterms, $courseid=0, $limitfrom=0, $limitnum=5 } if (!empty($forum->onlygroups)) { - list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups); + list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps0'); $params = array_merge($params, $groupid_params); $select[] = "d.groupid $groupid_sql"; } if ($select) { $selects = implode(" AND ", $select); - $where[] = "(d.forum = ? AND $selects)"; - $params[] = $forumid; + $where[] = "(d.forum = :forum AND $selects)"; + $params['forum'] = $forumid; } else { $fullaccess[] = $forumid; } } if ($fullaccess) { - list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess); + list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula0'); $params = array_merge($params, $fullid_params); $where[] = "(d.forum $fullid_sql)"; } $selectdiscussion = "(".implode(" OR ", $where).")"; - // Some differences SQL - $LIKE = $DB->sql_ilike(); - $NOTLIKE = 'NOT ' . $LIKE; - if ($CFG->dbfamily == 'postgres') { - $REGEXP = '~*'; - $NOTREGEXP = '!~*'; - } else { - $REGEXP = 'REGEXP'; - $NOTREGEXP = 'NOT REGEXP'; - } - $messagesearch = ''; $searchstring = ''; @@ -1774,14 +1763,15 @@ function forum_search_posts($searchterms, $courseid=0, $limitfrom=0, $limitnum=5 // CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message) // Experimental feature under 1.8! MDL-8830 if (!empty($CFG->forum_usetextsearches)) { - $messagesearch = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', + list($messagesearch, $msparams) = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } else { - $messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', + list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } + $params = array_merge($params, $msparams); } $fromsql = "{forum_posts} p, -- 2.39.5