From 658b4c83a68ef3bee04742128743bf0687e9600e Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Barrois?= Date: Tue, 16 Feb 2010 14:46:24 +0100 Subject: [PATCH] Use XDB::formatWildcards() where it is useful. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Raphaël Barrois --- classes/userfilter.php | 67 +++++++++++++---------------------------- include/directory.enums.inc.php | 44 ++++++--------------------- include/ufbuilder.inc.php | 2 +- 3 files changed, 32 insertions(+), 81 deletions(-) diff --git a/classes/userfilter.php b/classes/userfilter.php index 52ad12d..fa155f2 100644 --- a/classes/userfilter.php +++ b/classes/userfilter.php @@ -111,7 +111,7 @@ class UFC_Comment implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $uf->requireProfiles(); - return 'p.freetext LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); + return 'p.freetext ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->text); } } // }}} @@ -227,11 +227,11 @@ class UFC_EducationField implements UserFilterCondition */ class UFC_Name implements UserFilterCondition { - const PREFIX = 1; - const SUFFIX = 2; - const PARTICLE = 7; - const VARIANTS = 8; - const CONTAINS = 3; + const PREFIX = XDB::WILDCARD_PREFIX; // 0x001 + const SUFFIX = XDB::WILDCARD_SUFFIX; // 0x002 + const CONTAINS = XDB::WILDCARD_CONTAINS; // 0x003 + const PARTICLE = 0x007; // self::CONTAINS | 0x004 + const VARIANTS = 0x008; private $type; private $text; @@ -253,21 +253,12 @@ class UFC_Name implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $left = '$ME.name'; - $op = ' LIKE '; if (($this->mode & self::PARTICLE) == self::PARTICLE) { $left = 'CONCAT($ME.particle, \' \', $ME.name)'; } - if (($this->mode & self::CONTAINS) == 0) { - $right = XDB::format('{?}', $this->text); - $op = ' = '; - } else if (($this->mode & self::CONTAINS) == self::PREFIX) { - $right = XDB::format('CONCAT({?}, \'%\')', $this->text); - } else if (($this->mode & self::CONTAINS) == self::SUFFIX) { - $right = XDB::format('CONCAT(\'%\', {?})', $this->text); - } else { - $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); - } - $cond = $left . $op . $right; + $right = XDB::formatWildcards($this->mode & self::CONTAINS, $this->text); + + $cond = $left . $right; $conds = array($this->buildNameQuery($this->type, null, $cond, $uf)); if (($this->mode & self::VARIANTS) != 0 && isset(Profile::$name_variants[$this->type])) { foreach (Profile::$name_variants[$this->type] as $var) { @@ -318,7 +309,7 @@ class UFC_NameTokens implements UserFilterCondition } else { $tokconds = array(); foreach ($this->tokens as $token) { - $tokconds[] = $sub . '.token LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $token); + $tokconds[] = $sub . '.token ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $token); } $conds[] = implode(' OR ', $tokconds); } @@ -718,7 +709,7 @@ abstract class UFC_Address implements UserFilterCondition // {{{ class UFC_AddressText /** Select users based on their address, using full text search * @param $text Text for filter in fulltext search - * @param $textSearchMode Mode for search (PREFIX, SUFFIX, ...) + * @param $textSearchMode Mode for search (one of XDB::WILDCARD_*) * @param $type Filter on address type * @param $flags Filter on address flags * @param $country Filter on address country @@ -726,16 +717,11 @@ abstract class UFC_Address implements UserFilterCondition */ class UFC_AddressText extends UFC_Address { - /** Flags for text search - */ - const PREFIX = 0x0001; - const SUFFIX = 0x0002; - const CONTAINS = 0x0003; private $text; private $textSearchMode; - public function __construct($text = null, $textSearchMode = self::CONTAINS, + public function __construct($text = null, $textSearchMode = XDB::WILDCARD_CONTAINS, $type = null, $flags = self::FLAG_ANY, $country = null, $locality = null) { parent::__construct($type, $flags); @@ -747,18 +733,7 @@ class UFC_AddressText extends UFC_Address private function mkMatch($txt) { - $op = ' LIKE '; - if (($this->textSearchMode & self::CONTAINS) == 0) { - $right = XDB::format('{?}', $this->text); - $op = ' = '; - } else if (($this->mode & self::CONTAINS) == self::PREFIX) { - $right = XDB::format('CONCAT({?}, \'%\')', $this->text); - } else if (($this->mode & self::CONTAINS) == self::SUFFIX) { - $right = XDB::format('CONCAT(\'%\', {?})', $this->text); - } else { - $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->text); - } - return $op . $right; + return XDB::formatWildcards($this->textSearchMode, $txt); } public function buildCondition(PlFilter &$uf) @@ -1000,25 +975,25 @@ class UFC_Job_Description implements UserFilterCondition $conds = array(); if ($this->fields & UserFilter::JOB_USERDEFINED) { $sub = $uf->addJobFilter(); - $conds[] = $sub . '.description LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = $sub . '.description ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } if ($this->fields & UserFilter::JOB_CV) { $uf->requireProfiles(); - $conds[] = 'p.cv LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = 'p.cv ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } if ($this->fields & UserFilter::JOB_SECTOR) { $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SECTOR); - $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } if ($this->fields & UserFilter::JOB_SUBSECTOR) { $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSECTOR); - $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } if ($this->fields & UserFilter::JOB_SUBSUBSECTOR) { $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_SUBSUBSECTOR); - $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); $sub = $uf->addJobSectorizationFilter(UserFilter::JOB_ALTERNATES); - $conds[] = $sub . '.name LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->description); + $conds[] = $sub . '.name ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->description); } return implode(' OR ', $conds); } @@ -1045,7 +1020,7 @@ class UFC_Networking implements UserFilterCondition { $sub = $uf->addNetworkingFilter(); $conds = array(); - $conds[] = $sub . '.address = ' . XDB::format('CONCAT(\'%\', {?}, \'%\')', $this->value); + $conds[] = $sub . '.address ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->value); if ($this->type != -1) { $conds[] = $sub . '.network_type = ' . XDB::format('{?}', $this->type); } @@ -1145,7 +1120,7 @@ class UFC_Mentor_Expertise implements UserFilterCondition public function buildCondition(PlFilter &$uf) { $sub = $uf->addMentorFilter(UserFilter::MENTOR_EXPERTISE); - return $sub . '.expertise LIKE ' . XDB::format('CONCAT(\'%\', {?}, \'%\'', $this->expertise); + return $sub . '.expertise ' . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, $this->expertise); } } // }}} diff --git a/include/directory.enums.inc.php b/include/directory.enums.inc.php index 83a8821..095cd48 100644 --- a/include/directory.enums.inc.php +++ b/include/directory.enums.inc.php @@ -123,13 +123,6 @@ class DirEnum // {{{ class DirEnumeration abstract class DirEnumeration { - /** Modes for LIKE searches - */ - const MODE_EXACT = 0x000; - const MODE_PREFIX = 0x001; - const MODE_SUFFIX = 0x002; - const MODE_CONTAINS = 0x003; - /** An internal array of ID => optionTxt */ protected $options = null; @@ -183,7 +176,7 @@ abstract class DirEnumeration */ public function getIDs($text, $mode) { - if ($mode == self::MODE_EXACT) { + if ($mode == XDB::WILDCARD_EXACT) { $options = $this->getOptions(); return array_keys($options, $text); } else { @@ -193,9 +186,9 @@ abstract class DirEnumeration $where = $this->where . ' AND '; } $conds = array(); - $conds[] = $this->valfield . self::makeSqlConcat($text, $mode); + $conds[] = $this->valfield . XDB::formatWildcards($mode, $text); if ($this->valfield2 != null) { - $conds[] = $this->valfield2 . self::makeSqlConcat($text, $mode); + $conds[] = $this->valfield2 . XDB::formatWildcards($mode, $text); } $where .= '(' . implode(' OR ', $conds) . ')'; @@ -211,10 +204,10 @@ abstract class DirEnumeration private function mkTests($field, $text) { $tests = array(); - $tests[] = $field . self::makeSqlConcat($text, self::MODE_PREFIX); + $tests[] = $field . XDB::formatWildcards(XDB::WILDCARD_PREFIX, $text); if (!$this->ac_beginwith) { - $tests[] = $field . self::makeSqlConcat(' ' . $text, self::MODE_CONTAINS); - $tests[] = $field . self::makeSqlConcat('-' . $text, self::MODE_CONTAINS); + $tests[] = $field . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, ' ' . $text); + $tests[] = $field . XDB::formatWildcards(XDB::WILDCARD_CONTAINS, '-' . $text); } return $tests; } @@ -249,23 +242,6 @@ abstract class DirEnumeration } // }}} - // {{{ function makeSqlConcat - static protected function makeSqlConcat($text, $mode) - { - if ($mode == self::MODE_EXACT) { - return ' = ' . XDB::format('{?}', $text); - } - if ($mode == self::MODE_PREFIX) { - $right = XDB::format('CONCAT({?}, \'%\')', $text); - } else if ($mode == self::MODE_SUFFIX) { - $right = XDB::format('CONCAT(\'%\', {?})', $text); - } else { - $right = XDB::format('CONCAT(\'%\', {?}, \'%\')', $text); - } - return ' LIKE ' . $right; - } - // }}} - // {{{ function loadOptions /** The function used to load options */ @@ -378,12 +354,12 @@ class DE_EducationDegrees extends DirEnumeration if ($eduid == null) { return XDB::fetchColumn('SELECT id FROM profile_education_degree_enum - WHERE degree ' . self::makeSqlConcat($text, $mode)); + WHERE degree ' . XDB::formatWildcards($mode, $text)); } else { return XDB::fetchColumn('SELECT pede.id FROM profile_education_degree AS ped LEFT JOIN profile_education_degree_enum AS pede ON (ped.degreeid = pede.id) - WHERE ped.eduid = {?} AND pede.degree ' . self::makeSqlConcat($text, $mode), $eduid); + WHERE ped.eduid = {?} AND pede.degree ' . XDB::formatWildcards($mode, $text), $eduid); } } } @@ -469,11 +445,11 @@ class DE_AdminAreas extends DirEnumeration if ($country == null) { return XDB::fetchColumn('SELECT id FROM geoloc_administrativeareas - WHERE name ' . self::makeSqlConcat($text, $mode)); + WHERE name ' . XDB::formatWildcards($mode, $text)); } else { return XDB::fetchColumn('SELECT id FROM geoloc_administrativeareas - WHERE country = {?} AND name' . self::makeSqlConcat($text, $mode), $country); + WHERE country = {?} AND name' . XDB::formatWildcards($mode, $text), $country); } } } diff --git a/include/ufbuilder.inc.php b/include/ufbuilder.inc.php index 611756b..ee8f3fd 100644 --- a/include/ufbuilder.inc.php +++ b/include/ufbuilder.inc.php @@ -433,7 +433,7 @@ abstract class UFBF_Mixed extends UFB_Field $this->val = array($index); } else { $indexes = DirEnum::getIDs($this->direnum, $ufb->s($this->envfield), - $ufb->i('exact') ? DirEnumeration::MODE_EXACT : DirEnumeration::MODE_CONTAINS); + $ufb->i('exact') ? XDB::WILDCARD_EXACT : XDB::WILDCARD_CONTAINS); if (count($indexes) == 0) { return false; } -- 2.1.4