From ba564eeb7c73c409e3cbd1063c2ac350d6a7e360 Mon Sep 17 00:00:00 2001
From: Antoine Musso <hashar@users.mediawiki.org>
Date: Fri, 13 May 2011 15:39:45 +0000
Subject: [PATCH] Support abstraction for 'NOT IN' SQL structure

Following a live discussion with Catrope.

When using Database::makeList() in LIST_AND or LIST_OR modes, you can now
suffix the field name with an exclamation mark.  It will negate the logical
boolean.

Example:
  $db->makeList( array( 'field!' => array( 1,2,3 ) );
  outputs:
    'field' NOT IN ('1', '2', '3' );

 $db->makeList( array( 'foo!' => array( 777 ) ) );
 outputs:
   'foo' =! 777

(note: tests not ran, please run them and ammend them)
---
 includes/db/Database.php                   | 21 ++++++++++++++--
 tests/phpunit/includes/db/DatabaseTest.php | 28 ++++++++++++++++++++++
 2 files changed, 47 insertions(+), 2 deletions(-)

diff --git a/includes/db/Database.php b/includes/db/Database.php
index fd79cabe0f..42b649bf07 100644
--- a/includes/db/Database.php
+++ b/includes/db/Database.php
@@ -1382,6 +1382,13 @@ abstract class DatabaseBase implements DatabaseType {
 	 *        LIST_SET           - comma separated with field names, like a SET clause
 	 *        LIST_NAMES         - comma separated field names
 	 *
+	 * In LIST_AND or LIST_OR modes, you can suffix a field with an exclamation
+	 * mark to generate a 'NOT IN' structure.
+	 * Example:
+	 *  $db->makeList( array( 'field!' => array( 1,2,3 ) );
+	 *  outputs:
+	 *    'field' NOT IN ('1', '2', '3' );
+
 	 * @return string
 	 */
 	function makeList( $a, $mode = LIST_COMMA ) {
@@ -1405,6 +1412,13 @@ abstract class DatabaseBase implements DatabaseType {
 				$first = false;
 			}
 
+			// Support 'NOT IN' by suffixing fieldname with an exclamation mark
+			$not = false; 
+			if( substr($field,-1) == '!' ) {
+				$not = true;
+				$field = substr($field, 0, -1 );
+			}
+
 			if ( ( $mode == LIST_AND || $mode == LIST_OR ) && is_numeric( $field ) ) {
 				$list .= "($value)";
 			} elseif ( ( $mode == LIST_SET ) && is_numeric( $field ) ) {
@@ -1417,9 +1431,12 @@ abstract class DatabaseBase implements DatabaseType {
 					// Don't necessarily assume the single key is 0; we don't
 					// enforce linear numeric ordering on other arrays here.
 					$value = array_values( $value );
-					$list .= $field . " = " . $this->addQuotes( $value[0] );
+
+					$operator = $not ? ' != ' : ' = ';
+					$list .= $field . $operator . $this->addQuotes( $value[0] );
 				} else {
-					$list .= $field . " IN (" . $this->makeList( $value ) . ") ";
+					$operator = $not ? ' NOT IN ' : ' IN ';
+					$list .= $field . $operator . " (" . $this->makeList( $value ) . ") ";
 				}
 			} elseif ( $value === null ) {
 				if ( $mode == LIST_AND || $mode == LIST_OR ) {
diff --git a/tests/phpunit/includes/db/DatabaseTest.php b/tests/phpunit/includes/db/DatabaseTest.php
index 7357524f28..5fac8dceef 100644
--- a/tests/phpunit/includes/db/DatabaseTest.php
+++ b/tests/phpunit/includes/db/DatabaseTest.php
@@ -90,6 +90,34 @@ class DatabaseTest extends MediaWikiTestCase {
 			$sql );
 	}
 
+	function testMakeNotInList() {
+		$this->assertEquals(
+			$this->db->makeList( array(
+				'field' => array( 0, 1 )
+			) ),
+			"field IN ('0','1')"
+		);
+		$this->assertEquals(
+			$this->db->makeList( array(
+				'field!' => array( 0, 1 )
+			) ),
+			"field NOT IN ('0','1')"
+		);
+
+		// make sure an array with only one value use = or !=
+		$this->assertEquals(
+			$this->db->makeList( array(
+				'field' => array( 777 )
+			) ),
+			"field = 777"
+		);
+		$this->assertEquals(
+			$this->db->makeList( array(
+				'field!' => array( 888 )
+			) ),
+			"field != 888"
+		);
+	}
 }
 
 
-- 
2.20.1