From 63b3d8abb42d86360435cbbe639f1a054edaf900 Mon Sep 17 00:00:00 2001
From: Eloy Lafuente <stronk7@moodle.org>
Date: Thu, 7 Jan 2010 14:59:54 +0000
Subject: [PATCH] MDL-21264 sql_isnotempty() fixed. Thanks Tim! Added (and
 passed) a bunch of tests for that function.

---
 lib/dml/moodle_database.php              |  2 +-
 lib/dml/mssql_native_moodle_database.php |  4 +-
 lib/dml/oci_native_moodle_database.php   |  4 +-
 lib/dml/simpletest/testdml.php           | 62 ++++++++++++++++++++++--
 4 files changed, 64 insertions(+), 8 deletions(-)

diff --git a/lib/dml/moodle_database.php b/lib/dml/moodle_database.php
index 0da533897f..5c0d2158ff 100644
--- a/lib/dml/moodle_database.php
+++ b/lib/dml/moodle_database.php
@@ -1843,7 +1843,7 @@ abstract class moodle_database {
      * @return string the sql code to be added to check for empty values
      */
     public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
-        return " $fieldname = '' ";
+        return " ($fieldname = '') ";
     }
 
     /**
diff --git a/lib/dml/mssql_native_moodle_database.php b/lib/dml/mssql_native_moodle_database.php
index b7d5abfc33..ebb54c2ace 100644
--- a/lib/dml/mssql_native_moodle_database.php
+++ b/lib/dml/mssql_native_moodle_database.php
@@ -1113,9 +1113,9 @@ class mssql_native_moodle_database extends moodle_database {
 
    public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
         if ($textfield) {
-            return $this->sql_compare_text($fieldname)." = '' ";
+            return ' (' . $this->sql_compare_text($fieldname) . " = '') ";
         } else {
-            return " $fieldname = '' ";
+            return " ($fieldname = '') ";
         }
     }
 
diff --git a/lib/dml/oci_native_moodle_database.php b/lib/dml/oci_native_moodle_database.php
index e2b52d5f30..e0140dab90 100644
--- a/lib/dml/oci_native_moodle_database.php
+++ b/lib/dml/oci_native_moodle_database.php
@@ -1457,9 +1457,9 @@ class oci_native_moodle_database extends moodle_database {
 
     public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
         if ($textfield) {
-            return " ".$this->sql_compare_text($fieldname)." = '".$this->sql_empty()."' ";
+            return " (".$this->sql_compare_text($fieldname)." = '".$this->sql_empty()."') ";
         } else {
-            return " $fieldname = '".$this->sql_empty()."' ";
+            return " ($fieldname = '".$this->sql_empty()."') ";
         }
     }
 
diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php
index 2fa93b6d4b..9d26eeb957 100755
--- a/lib/dml/simpletest/testdml.php
+++ b/lib/dml/simpletest/testdml.php
@@ -1,7 +1,25 @@
 <?php
 
+// This file is part of Moodle - http://moodle.org/
+//
+// Moodle is free software: you can redistribute it and/or modify
+// it under the terms of the GNU General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// Moodle is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
+
 /**
- * Unit tests for dml
+ * @package    moodlecore
+ * @subpackage DML
+ * @copyright  2008 Petr Skoda (http://skodak.org)
+ * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  */
 
 if (!defined('MOODLE_INTERNAL')) {
@@ -2256,8 +2274,46 @@ class dml_test extends UnitTestCase {
         $this->assertEqual($record->descriptionnull, '');
     }
 
-    function sql_isnotempty() {
-        //TODO
+    function test_sql_isnotempty() {
+        $DB = $this->tdb;
+        $dbman = $DB->get_manager();
+
+        $table = $this->get_test_table();
+        $tablename = $table->getName();
+
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null, null);
+        $table->add_field('namenull', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+        $table->add_field('description', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL, null, null);
+        $table->add_field('descriptionnull', XMLDB_TYPE_TEXT, 'big', null, null, null, null);
+        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+        $dbman->create_table($table);
+        $this->tables[$tablename] = $table;
+
+        $DB->insert_record($tablename, array('name'=>'',   'namenull'=>'',   'description'=>'',   'descriptionnull'=>''));
+        $DB->insert_record($tablename, array('name'=>'??', 'namenull'=>null, 'description'=>'??', 'descriptionnull'=>null));
+        $DB->insert_record($tablename, array('name'=>'la', 'namenull'=>'la', 'description'=>'la', 'descriptionnull'=>'lalala'));
+        $DB->insert_record($tablename, array('name'=>0,    'namenull'=>0,    'description'=>0,    'descriptionnull'=>0));
+
+        $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'name', false, false));
+        $this->assertEqual(count($records), 3);
+        $record = reset($records);
+        $this->assertEqual($record->name, '??');
+
+        $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'namenull', true, false));
+        $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
+        $record = reset($records);
+        $this->assertEqual($record->namenull, 'la'); // so 'la' is the first non-empty 'namenull' record
+
+        $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'description', false, true));
+        $this->assertEqual(count($records), 3);
+        $record = reset($records);
+        $this->assertEqual($record->description, '??');
+
+        $records = $DB->get_records_sql("SELECT * FROM {".$tablename."} WHERE ".$DB->sql_isnotempty($tablename, 'descriptionnull', true, true));
+        $this->assertEqual(count($records), 2); // nulls aren't comparable (so they aren't "not empty"). SQL expected behaviour
+        $record = reset($records);
+        $this->assertEqual($record->descriptionnull, 'lalala'); // so 'lalala' is the first non-empty 'descriptionnull' record
     }
 
     function test_sql_regex() {
-- 
2.39.5