From cb5105a872a3d2fdea64e6c913e8bc85d1998092 Mon Sep 17 00:00:00 2001 From: skodak Date: Sun, 29 Jul 2007 15:20:51 +0000 Subject: [PATCH] MDL-9960 more support for NULLs in db values + unittests --- lib/dmllib.php | 17 ++++-- lib/simpletest/testdmllib.php | 106 +++++++++++++++++++++++++--------- 2 files changed, 91 insertions(+), 32 deletions(-) diff --git a/lib/dmllib.php b/lib/dmllib.php index ba657b8a5a..81d1271a30 100644 --- a/lib/dmllib.php +++ b/lib/dmllib.php @@ -1223,8 +1223,15 @@ function set_field_select($table, $newfield, $newvalue, $select, $localcall = fa } } +/// NULL inserts - introduced in 1.9 + if (is_null($newvalue)) { + $update = "$newfield = NULL"; + } else { + $update = "$newfield = '$newvalue'"; + } + /// Arriving here, standard update - return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $newfield .' = \''. $newvalue .'\' '. $select); + return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '.$update.' '.$select); } /** @@ -1558,7 +1565,7 @@ function update_record($table, $dataobject) { foreach ($ddd as $key => $value) { $count++; if ($value === NULL) { - $update .= $key .' = NULL'; // previosly NULLs were not updated + $update .= $key .' = NULL'; // previously NULLs were not updated } else { $update .= $key .' = \''. $value .'\''; // All incoming data is already quoted } @@ -1943,11 +1950,11 @@ function sql_primary_role_subselect() { */ function where_clause($field1='', $value1='', $field2='', $value2='', $field3='', $value3='') { if ($field1) { - $select = "WHERE $field1 = '$value1'"; + $select = is_null($value1) ? "WHERE $field1 IS NULL" : "WHERE $field1 = '$value1'"; if ($field2) { - $select .= " AND $field2 = '$value2'"; + $select .= is_null($value2) ? " AND $field2 IS NULL" : " AND $field2 = '$value2'"; if ($field3) { - $select .= " AND $field3 = '$value3'"; + $select .= is_null($value3) ? " AND $field3 IS NULL" : " AND $field3 = '$value3'"; } } } else { diff --git a/lib/simpletest/testdmllib.php b/lib/simpletest/testdmllib.php index fd71674600..dfa2f6c5f3 100644 --- a/lib/simpletest/testdmllib.php +++ b/lib/simpletest/testdmllib.php @@ -23,19 +23,20 @@ class datalib_test extends prefix_changing_test_case { array( 2, 'toad', 102), array( 3, 'tadpole', 103), array( 4, 'tadpole', 104), + array( 5, 'nothing', NULL), ); var $objects = array(); function setUp() { global $CFG, $db; parent::setUp(); - wipe_tables($CFG->prefix, $db); load_test_table($CFG->prefix . $this->table, $this->data, $db); $keys = reset($this->data); - foreach ($this->data as $datum) { - if ($datum != $keys) { - $this->objects[$datum[0]] = (object) array_combine($keys, $datum); + foreach ($this->data as $row=>$datum) { + if ($row == 0) { + continue; } + $this->objects[$datum[0]] = (object) array_combine($keys, $datum); } } @@ -49,29 +50,34 @@ class datalib_test extends prefix_changing_test_case { $this->assertEqual(where_clause('f1', 'v1'), "WHERE f1 = 'v1'"); $this->assertEqual(where_clause('f1', 'v1', 'f2', 2), "WHERE f1 = 'v1' AND f2 = '2'"); $this->assertEqual(where_clause('f1', 'v1', 'f2', 1.75, 'f3', 'v3'), "WHERE f1 = 'v1' AND f2 = '1.75' AND f3 = 'v3'"); + $this->assertEqual(where_clause('f1', NULL), "WHERE f1 IS NULL"); } - + function test_record_exists() { $this->assertTrue(record_exists($this->table, 'numberfield', 101, 'id', 1)); $this->assertFalse(record_exists($this->table, 'numberfield', 102, 'id', 1)); + $this->assertTrue(record_exists($this->table, 'numberfield', NULL)); } function test_record_exists_select() { $this->assertTrue(record_exists_select($this->table, 'numberfield = 101 AND id = 1')); $this->assertFalse(record_exists_select($this->table, 'numberfield = 102 AND id = 1')); + $this->assertTrue(record_exists_select($this->table, 'numberfield IS NULL')); } function test_record_exists_sql() { global $CFG; $this->assertTrue(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield = 101 AND id = 1")); $this->assertFalse(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield = 102 AND id = 1")); + $this->assertTrue(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield IS NULL")); } function test_get_record() { // Get particular records. - $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[1]), get_record($this->table, 'id', 1), 'id = 1'); - $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[3]), get_record($this->table, 'textfield', 'tadpole', 'numberfield', 103), 'textfield = tadpole AND numberfield = 103'); + $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[1]), get_record($this->table, 'id', 1)); + $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[3]), get_record($this->table, 'textfield', 'tadpole', 'numberfield', 103)); + $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[5]), get_record($this->table, 'numberfield', null)); // Abiguous get attempt, should return one, and print a warning in debug mode. global $CFG; @@ -186,6 +192,7 @@ class datalib_test extends prefix_changing_test_case { $this->assertEqual(get_field($this->table, 'textfield', 'numberfield', 102), 'toad'); $this->assertEqual(get_field($this->table, 'numberfield', 'textfield', 'tadpole', 'id', 4), 104); $this->assertEqual(get_field($this->table, 'numberfield + id', 'textfield', 'tadpole', 'id', 4), 108); + $this->assertNull(get_field($this->table, 'numberfield', 'id', 5)); } function test_get_field_select() { @@ -206,31 +213,65 @@ class datalib_test extends prefix_changing_test_case { set_field($this->table, 'numberfield', -1, 'textfield', 'tadpole', 'id', 4); $this->assertEqual(get_field($this->table, 'numberfield', 'textfield', 'tadpole', 'id', 4), -1); + + set_field($this->table, 'textfield', null, 'id', 5); + $this->assertNull(get_field($this->table, 'textfield', 'id', 5)); } function test_delete_records() { delete_records($this->table, 'id', 666); - $this->assertEqual(count_records($this->table), 4); + $this->assertEqual(count_records($this->table), 5); delete_records($this->table, 'id', 1); - $this->assertEqual(count_records($this->table), 3); + $this->assertEqual(count_records($this->table), 4); delete_records($this->table, 'textfield', 'tadpole'); + $this->assertEqual(count_records($this->table), 2); + delete_records($this->table, 'numberfield', NULL); $this->assertEqual(count_records($this->table), 1); } function test_delete_records2() { delete_records($this->table, 'textfield', 'tadpole', 'id', 4); - $this->assertEqual(count_records($this->table), 3); + $this->assertEqual(count_records($this->table), 4); delete_records($this->table); $this->assertEqual(count_records($this->table), 0); } function test_delete_records_select() { delete_records_select($this->table, "textfield LIKE 't%'"); - $this->assertEqual(count_records($this->table), 1); + $this->assertEqual(count_records($this->table), 2); delete_records_select($this->table, "'1' = '1'"); $this->assertEqual(count_records($this->table), 0); } + function test_update_record() { + global $CFG; + + // Simple update + $obj = new stdClass; + $obj->id = 1; + $obj->textfield = 'changed entry'; + $obj->numberfield = 123; + $this->assertTrue(update_record($this->table, $obj)); + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id)); + + // Simple incomplete update + $obj = new stdClass; + $obj->id = 2; + $obj->numberfield = 123; + $this->assertTrue(update_record($this->table, $obj)); + $obj->textfield = 'toad'; + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id)); + + // Simple incomplete update + $obj = new stdClass; + $obj->id = 3; + $obj->numberfield = 123; + $obj->textfield = null; + $this->assertTrue(update_record($this->table, $obj)); + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id)); + + } + //function insert_record($table, $dataobject, $returnid=true, $primarykey='id', $feedback=true) { function test_insert_record() { global $CFG; @@ -239,37 +280,48 @@ class datalib_test extends prefix_changing_test_case { $obj = new stdClass; $obj->textfield = 'new entry'; $obj->numberfield = 123; - $this->assertEqual(insert_record($this->table, $obj), 5); - $obj->id = 5; - $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert with returnid (%s)'), get_record($this->table, 'id', 5)); - + $this->assertEqual(insert_record($this->table, $obj), 6); + $obj->id = 6; + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert with returnid (%s)'), get_record($this->table, 'id', $obj->id)); + // Simple insert without $returnid $obj = new stdClass; $obj->textfield = 'newer entry'; $obj->numberfield = 321; $this->assertEqual(insert_record($this->table, $obj, false), true); - $obj->id = 6; - $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert without returnid (%s)'), get_record($this->table, 'id', 6)); - + $obj->id = 7; + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert without returnid (%s)'), get_record($this->table, 'id', $obj->id)); + // Insert with missing columns - should get defaults. $obj = new stdClass; $obj->textfield = 'partial entry'; - $this->assertEqual(insert_record($this->table, $obj), 7); - $obj->id = 7; + $this->assertEqual(insert_record($this->table, $obj), 8); + $obj->id = 8; $obj->numberfield = 0xDefa; - $got = get_record($this->table, 'id', 7); - $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with missing columns - should get defaults (%s)'), get_record($this->table, 'id', 7)); - + $got = get_record($this->table, 'id', 8); + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with missing columns - should get defaults (%s)'), get_record($this->table, 'id', $obj->id)); + // Insert with extra columns - should be ingnored. $obj = new stdClass; $obj->textfield = 'entry with extra'; $obj->numberfield = 747; $obj->unused = 666; - $this->assertEqual(insert_record($this->table, $obj), 8); - $obj->id = 8; + $this->assertEqual(insert_record($this->table, $obj), 9); + $obj->id = 9; unset($obj->unused); - $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with extra columns - should be ingnored (%s)'), get_record($this->table, 'id', 8)); - + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with extra columns - should be ingnored (%s)'), get_record($this->table, 'id', $obj->id)); + + // Simple insert with $returnid and NULL values + $obj = new stdClass; + $obj->textfield = null; + $obj->numberfield = null; + $this->assertEqual(insert_record($this->table, $obj), 10); + $obj->id = 10; + $new = get_record($this->table, 'id', $obj->id); + $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert with returnid (%s)'), $new); + $this->assertNull($new->textfield); + $this->assertNull($new->numberfield); + // Insert into nonexistant table - should fail. $obj = new stdClass; $obj->textfield = 'new entry'; -- 2.39.5