From 5c77e4381945300ba9a1086b99fac12ae48c52a8 Mon Sep 17 00:00:00 2001 From: brunoais Date: Tue, 24 Feb 2015 23:03:11 +0000 Subject: [feature/sql-bool-builder] First working version PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 92 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 92 insertions(+) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index 8d360fc3e2..5822adceab 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -792,6 +792,98 @@ abstract class driver implements driver_interface return $sql; } + + + protected function _process_boolean_tree_first($operations_ary) + { + if ($operations_ary[0] !== 'AND' && + $operations_ary[0] !== 'OR') + { + $operations_ary = array('AND', $operations_ary); + } + return $this->_process_boolean_tree($operations_ary) . "\n"; + } + + protected function _process_boolean_tree($operations_ary) + { + $operation = array_shift($operations_ary); + + foreach ($operations_ary AS &$condition) + { + switch ($condition[0]) + { + case 'AND': + case 'OR': + + $condition = ' ( ' . $this->_process_boolean_tree($condition) . ') '; + + break; + case 'NOT': + + $condition = ' NOT ' . $this->_process_boolean_tree($condition); + + break; + + default: + + switch (sizeof($condition)) + { + case 3: + + // Typical 3 element clause with {left hand} {operator} {right hand} + switch ($condition[1]) + { + case 'IN': + case 'NOT_IN': + + // As this is used with an IN, assume it is a set of elements for sql_in_set() + $condition = $this->sql_in_set($condition[0], $condition[2], $condition[1] === 'NOT_IN', true); + + break; + + default: + + $condition = implode(' ', $condition); + + break; + } + + break; + + case 5: + + // Subquery with {left hand} {operator} {compare kind} {SELECT Kind } {Sub Query} + + $condition = $condition[0] . ' ' . $condition[1] . ' ' . $condition[2] . ' ( '; + $condition .= $this->sql_build_query($condition[3], $condition[4]); + $condition .= ' )'; + + break; + + default: + // This is an unpredicted clause setup. Just join all elements. + $condition = implode(' ', $condition); + + break; + } + + break; + } + + } + + if($operation === 'NOT') + { + $operations_ary = implode("", $operations_ary); + } + else + { + $operations_ary = implode(" \n $operation ", $operations_ary); + } + + return $operations_ary; + } + /** * {@inheritDoc} -- cgit v1.2.1 From b54adaaabed7546254ff8317763214cce3a40237 Mon Sep 17 00:00:00 2001 From: brunoais Date: Wed, 25 Feb 2015 07:54:31 +0000 Subject: [feature/sql-bool-builder] Removed non-necessary spaces PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 58 ++++++++++++++++++++-------------------- 1 file changed, 29 insertions(+), 29 deletions(-) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index 5822adceab..cdff6bf9de 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -792,11 +792,11 @@ abstract class driver implements driver_interface return $sql; } - - + + protected function _process_boolean_tree_first($operations_ary) { - if ($operations_ary[0] !== 'AND' && + if ($operations_ary[0] !== 'AND' && $operations_ary[0] !== 'OR') { $operations_ary = array('AND', $operations_ary); @@ -807,71 +807,71 @@ abstract class driver implements driver_interface protected function _process_boolean_tree($operations_ary) { $operation = array_shift($operations_ary); - + foreach ($operations_ary AS &$condition) { switch ($condition[0]) { case 'AND': case 'OR': - + $condition = ' ( ' . $this->_process_boolean_tree($condition) . ') '; - + break; case 'NOT': - + $condition = ' NOT ' . $this->_process_boolean_tree($condition); - + break; - + default: - + switch (sizeof($condition)) { case 3: - + // Typical 3 element clause with {left hand} {operator} {right hand} switch ($condition[1]) - { + { case 'IN': case 'NOT_IN': - + // As this is used with an IN, assume it is a set of elements for sql_in_set() $condition = $this->sql_in_set($condition[0], $condition[2], $condition[1] === 'NOT_IN', true); - + break; - + default: - + $condition = implode(' ', $condition); - + break; } - + break; - + case 5: - + // Subquery with {left hand} {operator} {compare kind} {SELECT Kind } {Sub Query} - + $condition = $condition[0] . ' ' . $condition[1] . ' ' . $condition[2] . ' ( '; $condition .= $this->sql_build_query($condition[3], $condition[4]); $condition .= ' )'; - + break; - + default: // This is an unpredicted clause setup. Just join all elements. $condition = implode(' ', $condition); - + break; } - + break; } - + } - + if($operation === 'NOT') { $operations_ary = implode("", $operations_ary); @@ -880,10 +880,10 @@ abstract class driver implements driver_interface { $operations_ary = implode(" \n $operation ", $operations_ary); } - + return $operations_ary; } - + /** * {@inheritDoc} -- cgit v1.2.1 From 46de94690453e0bd6dfc589682b7c4ede34f1d59 Mon Sep 17 00:00:00 2001 From: brunoais Date: Wed, 25 Feb 2015 07:55:24 +0000 Subject: [feature/sql-bool-builder] Added code to use this feature for the WHERE clause PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index cdff6bf9de..0fbbe5ae7c 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -774,7 +774,18 @@ abstract class driver implements driver_interface if (!empty($array['WHERE'])) { - $sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']); + $sql .= ' WHERE '; + + if (is_array($array['WHERE'])) + { + $sql_where = $this->_process_boolean_tree_first($array['WHERE']); + } + else + { + $sql_where = $array['WHERE']; + } + + $sql .= $this->_sql_custom_build('WHERE', $sql_where); } if (!empty($array['GROUP_BY'])) -- cgit v1.2.1 From 5c1850e10e0e31e4f72cc16c657e37bb009659df Mon Sep 17 00:00:00 2001 From: brunoais Date: Wed, 25 Feb 2015 09:09:48 +0000 Subject: [feature/sql-bool-builder] AS keyword must be lowercase; AS keyword must be lowercase; expected "as" but found "AS" PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index 0fbbe5ae7c..f66f10322b 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -819,7 +819,7 @@ abstract class driver implements driver_interface { $operation = array_shift($operations_ary); - foreach ($operations_ary AS &$condition) + foreach ($operations_ary as &$condition) { switch ($condition[0]) { -- cgit v1.2.1 From 51737be6162584209b483f97daa87ce9d3c039b0 Mon Sep 17 00:00:00 2001 From: brunoais Date: Sat, 28 Feb 2015 18:14:00 +0000 Subject: [feature/sql-bool-builder] Also use parenthesis for the NOT operator Be on the safe side, also use parenthesis for the NOT operator PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index f66f10322b..d2bb5f4f7c 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -831,7 +831,7 @@ abstract class driver implements driver_interface break; case 'NOT': - $condition = ' NOT ' . $this->_process_boolean_tree($condition); + $condition = ' NOT (' . $this->_process_boolean_tree($condition) . ') '; break; -- cgit v1.2.1 From 5d70f612be113336d6a85146369232c87d1b069b Mon Sep 17 00:00:00 2001 From: brunoais Date: Sun, 1 Mar 2015 11:39:20 +0000 Subject: [feature/sql-bool-builder] Explain better the code in the first Explain what that if and check is for in the first method that is called. PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 2 ++ 1 file changed, 2 insertions(+) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index d2bb5f4f7c..cda2f7f86d 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -807,6 +807,8 @@ abstract class driver implements driver_interface protected function _process_boolean_tree_first($operations_ary) { + // In cases where an array exists but there is no head condition, + // it should be because there's only 1 WHERE clause. This seems the best way to deal with it. if ($operations_ary[0] !== 'AND' && $operations_ary[0] !== 'OR') { -- cgit v1.2.1 From 298d86009ed77a4b75792a6237f987be271ac43c Mon Sep 17 00:00:00 2001 From: brunoais Date: Sun, 15 Mar 2015 11:40:02 +0000 Subject: [feature/sql-bool-builder] Added LIKE and NOT_LIKE to the comparations PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index cda2f7f86d..ce69ef6a52 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -854,6 +854,18 @@ abstract class driver implements driver_interface break; + case 'LIKE': + + $condition = $condition[0] . ' ' . $this->sql_like_expression($condition[2]) . ' '; + + break; + + case 'NOT_LIKE': + + $condition = $condition[0] . ' ' . $this->sql_not_like_expression($condition[2]) . ' '; + + break; + default: $condition = implode(' ', $condition); -- cgit v1.2.1 From 576eaa0cff7a5e051aa672034e596e90f65fc1a9 Mon Sep 17 00:00:00 2001 From: brunoais Date: Mon, 16 Mar 2015 11:31:51 +0000 Subject: [feature/sql-bool-builder] Adding the IS operator to predicted operators PHPBB3-13652 --- phpBB/phpbb/db/driver/driver.php | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'phpBB/phpbb/db/driver') diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php index ce69ef6a52..4d78c84c8a 100644 --- a/phpBB/phpbb/db/driver/driver.php +++ b/phpBB/phpbb/db/driver/driver.php @@ -866,6 +866,24 @@ abstract class driver implements driver_interface break; + case 'IS_NOT': + + $condition[1] = 'IS NOT'; + + // no break + case 'IS': + + // If the value is NULL, the string of it is the empty string ('') which is not the intended result. + // this should solve that + if ($condition[2] === null) + { + $condition[2] = 'NULL'; + } + + $condition = implode(' ', $condition); + + break; + default: $condition = implode(' ', $condition); -- cgit v1.2.1