aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorGeolim4 <contact@geolim4.com>2014-08-08 15:06:12 +0200
committerGeolim4 <contact@geolim4.com>2014-08-08 16:40:33 +0200
commit6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2 (patch)
treed378a9a0aa46968a08a0f20dcbbe4b4e2c049f3a
parentf6da2661091a9f068956a23df7e70450cec9ee74 (diff)
downloadforums-6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2.tar
forums-6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2.tar.gz
forums-6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2.tar.bz2
forums-6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2.tar.xz
forums-6b60153ab4ac036bcd4eaaa90806d9898fc1e9a2.zip
[ticket/12671] Possibility to use NOT LIKE expression
PHPBB3-12671
Diffstat
-rw-r--r--phpBB/phpbb/db/driver/driver.php11
-rw-r--r--phpBB/phpbb/db/driver/driver_interface.php10
-rw-r--r--phpBB/phpbb/db/driver/factory.php8
-rw-r--r--phpBB/phpbb/db/driver/mssql.php9
-rw-r--r--phpBB/phpbb/db/driver/mssql_base.php9
-rw-r--r--phpBB/phpbb/db/driver/mysql_base.php9
-rw-r--r--phpBB/phpbb/db/driver/oracle.php9
-rw-r--r--phpBB/phpbb/db/driver/postgres.php9
-rw-r--r--phpBB/phpbb/db/driver/sqlite.php17
-rw-r--r--tests/dbal/select_test.php42
-rw-r--r--tests/di/create_container_test.php4
11 files changed, 137 insertions, 0 deletions
diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php
index 3e9110d8bc..ed650bd6a9 100644
--- a/phpBB/phpbb/db/driver/driver.php
+++ b/phpBB/phpbb/db/driver/driver.php
@@ -372,6 +372,17 @@ abstract class driver implements driver_interface
/**
* {@inheritDoc}
*/
+ function sql_not_like_expression($expression)
+ {
+ $expression = utf8_str_replace(array('_', '%'), array("\_", "\%"), $expression);
+ $expression = utf8_str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
+
+ return $this->_sql_like_expression('NOT LIKE \'' . $this->sql_escape($expression) . '\'');
+ }
+
+ /**
+ * {@inheritDoc}
+ */
public function sql_case($condition, $action_true, $action_false = false)
{
$sql_case = 'CASE WHEN ' . $condition;
diff --git a/phpBB/phpbb/db/driver/driver_interface.php b/phpBB/phpbb/db/driver/driver_interface.php
index 6722d059a5..8b487c5d42 100644
--- a/phpBB/phpbb/db/driver/driver_interface.php
+++ b/phpBB/phpbb/db/driver/driver_interface.php
@@ -419,6 +419,16 @@ interface driver_interface
public function sql_like_expression($expression);
/**
+ * Correctly adjust NOT LIKE expression for special characters
+ * Some DBMS are handling them in a different way
+ *
+ * @param string $expression The expression to use. Every wildcard is
+ * escaped, except $this->any_char and $this->one_char
+ * @return string A SQL statement like: "NOT LIKE 'bertie_%'"
+ */
+ public function sql_not_like_expression($expression);
+
+ /**
* Explain queries
*
* @param string $mode Available modes: display, start, stop,
diff --git a/phpBB/phpbb/db/driver/factory.php b/phpBB/phpbb/db/driver/factory.php
index f0fa18051b..fb3a826254 100644
--- a/phpBB/phpbb/db/driver/factory.php
+++ b/phpBB/phpbb/db/driver/factory.php
@@ -420,6 +420,14 @@ class factory implements driver_interface
/**
* {@inheritdoc}
*/
+ public function sql_not_like_expression($expression)
+ {
+ return $this->get_driver()->sql_not_like_expression($expression);
+ }
+
+ /**
+ * {@inheritdoc}
+ */
public function sql_report($mode, $query = '')
{
return $this->get_driver()->sql_report($mode, $query);
diff --git a/phpBB/phpbb/db/driver/mssql.php b/phpBB/phpbb/db/driver/mssql.php
index 268463a151..f9ea884ce2 100644
--- a/phpBB/phpbb/db/driver/mssql.php
+++ b/phpBB/phpbb/db/driver/mssql.php
@@ -351,6 +351,15 @@ class mssql extends \phpbb\db\driver\driver
}
/**
+ * Build NOT LIKE expression
+ * @access private
+ */
+ function _sql_not_like_expression($expression)
+ {
+ return $expression . " ESCAPE '\\'";
+ }
+
+ /**
* return sql error array
* @access private
*/
diff --git a/phpBB/phpbb/db/driver/mssql_base.php b/phpBB/phpbb/db/driver/mssql_base.php
index e7101903b8..514df9eaca 100644
--- a/phpBB/phpbb/db/driver/mssql_base.php
+++ b/phpBB/phpbb/db/driver/mssql_base.php
@@ -52,6 +52,15 @@ abstract class mssql_base extends \phpbb\db\driver\driver
}
/**
+ * Build NOT LIKE expression
+ * @access private
+ */
+ function _sql_not_like_expression($expression)
+ {
+ return $expression . " ESCAPE '\\'";
+ }
+
+ /**
* Build db-specific query data
* @access private
*/
diff --git a/phpBB/phpbb/db/driver/mysql_base.php b/phpBB/phpbb/db/driver/mysql_base.php
index e7c9b63f20..5e0b359134 100644
--- a/phpBB/phpbb/db/driver/mysql_base.php
+++ b/phpBB/phpbb/db/driver/mysql_base.php
@@ -112,6 +112,15 @@ abstract class mysql_base extends \phpbb\db\driver\driver
}
/**
+ * Build NOT LIKE expression
+ * @access private
+ */
+ function _sql_not_like_expression($expression)
+ {
+ return $expression;
+ }
+
+ /**
* Build db-specific query data
* @access private
*/
diff --git a/phpBB/phpbb/db/driver/oracle.php b/phpBB/phpbb/db/driver/oracle.php
index d1a186f1ba..6dcab5dd7d 100644
--- a/phpBB/phpbb/db/driver/oracle.php
+++ b/phpBB/phpbb/db/driver/oracle.php
@@ -645,6 +645,15 @@ class oracle extends \phpbb\db\driver\driver
return $expression . " ESCAPE '\\'";
}
+ /**
+ * Build NOT LIKE expression
+ * @access private
+ */
+ function _sql_not_like_expression($expression)
+ {
+ return $expression . " ESCAPE '\\'";
+ }
+
function _sql_custom_build($stage, $data)
{
return $data;
diff --git a/phpBB/phpbb/db/driver/postgres.php b/phpBB/phpbb/db/driver/postgres.php
index 83e9fa51f6..a3b9aa4c6b 100644
--- a/phpBB/phpbb/db/driver/postgres.php
+++ b/phpBB/phpbb/db/driver/postgres.php
@@ -371,6 +371,15 @@ class postgres extends \phpbb\db\driver\driver
}
/**
+ * Build NOT LIKE expression
+ * @access private
+ */
+ function _sql_not_like_expression($expression)
+ {
+ return $expression;
+ }
+
+ /**
* {@inheritDoc}
*/
function cast_expr_to_bigint($expression)
diff --git a/phpBB/phpbb/db/driver/sqlite.php b/phpBB/phpbb/db/driver/sqlite.php
index 2112e5ba2f..841662c1ed 100644
--- a/phpBB/phpbb/db/driver/sqlite.php
+++ b/phpBB/phpbb/db/driver/sqlite.php
@@ -288,6 +288,23 @@ class sqlite extends \phpbb\db\driver\driver
}
/**
+ * {@inheritDoc}
+ *
+ * For SQLite an underscore is a not-known character... this may change with SQLite3
+ */
+ function sql_not_like_expression($expression)
+ {
+ // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it!
+ // We only catch * and ? here, not the character map possible on file globbing.
+ $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
+
+ $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
+ $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
+
+ return 'GLOB \'' . $this->sql_escape($expression) . '\'';
+ }
+
+ /**
* return sql error array
* @access private
*/
diff --git a/tests/dbal/select_test.php b/tests/dbal/select_test.php
index e480716a49..ce07d7d0fb 100644
--- a/tests/dbal/select_test.php
+++ b/tests/dbal/select_test.php
@@ -233,6 +233,48 @@ class phpbb_dbal_select_test extends phpbb_database_test_case
$db->sql_freeresult($result);
}
+ public function not_like_expression_data()
+ {
+ // * = any_char; # = one_char
+ return array(
+ array('barfoo', array(array('username_clean' => 'bertie'),
+ array('username_clean' => 'foobar'))),
+ array('bar', array(array('username_clean' => 'bertie'),)),
+ array('bar*', array(array('username_clean' => 'bertie'),
+ array('username_clean' => 'foobar'))),
+ array('*bar*', array(array('username_clean' => 'bertie'))),
+ array('b*r', array(array('username_clean' => 'barfoo'),
+ array('username_clean' => 'foobar'))),
+ array('b*e', array(array('username_clean' => 'barfoo'),
+ array('username_clean' => 'foobar'))),
+ array('#b*e', array(array('username_clean' => 'barfoo'),
+ array('username_clean' => 'foobar'))),
+ array('b####e', array(array('username_clean' => 'barfoo'),
+ array('username_clean' => 'foobar'))),
+ );
+ }
+
+ /**
+ * @dataProvider not_like_expression_data
+ */
+ public function test_not_like_expression($like_expression, $expected)
+ {
+ $db = $this->new_dbal();
+
+ $like_expression = str_replace('*', $db->get_any_char(), $like_expression);
+ $like_expression = str_replace('#', $db->get_one_char(), $like_expression);
+ $where = ($like_expression) ? 'username_clean ' . $db->sql_not_like_expression($like_expression) : '';
+
+ $result = $db->sql_query('SELECT username_clean
+ FROM phpbb_users
+ ' . (($where) ? ' WHERE ' . $where : '') . '
+ ORDER BY user_id ASC');
+
+ $this->assertEquals($expected, $db->sql_fetchrowset($result));
+
+ $db->sql_freeresult($result);
+ }
+
public function in_set_data()
{
return array(
diff --git a/tests/di/create_container_test.php b/tests/di/create_container_test.php
index 559c0b122c..4ae6017989 100644
--- a/tests/di/create_container_test.php
+++ b/tests/di/create_container_test.php
@@ -191,6 +191,10 @@ namespace phpbb\db\driver
{
}
+ function sql_not_like_expression($expression)
+ {
+ }
+
function sql_fetchrowset($query_id = false)
{
return array(
generated by cgit v1.2.1 (git 2.21.0) at 2024-09-21 22:03:29 +0000