lib/simpletest/testdmllib.php

   1 <?php
   2 /**
   3  * Unit tests for (some of) ../datalib.php.
   4  *
   5  * @copyright &copy; 2006 The Open University
   6  * @author T.J.Hunt@open.ac.uk
   7  * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
   8  * @package moodlecore
   9  */
  10
  11 if (!defined('MOODLE_INTERNAL')) {
  12     die('Direct access to this script is forbidden.');    ///  It must be included from a Moodle page
  13 }
  14
  15 require_once($CFG->libdir . '/simpletestlib/web_tester.php');
  16 require_once($CFG->libdir . '/dmllib.php');
  17
  18 class datalib_test extends prefix_changing_test_case {
  19     var $table = 'table';
  20     var $data = array(
  21             array('id',   'textfield', 'numberfield'),
  22             array(  1,    'frog',     101),
  23             array(  2,    'toad',     102),
  24             array(  3, 'tadpole',     103),
  25             array(  4, 'tadpole',     104),
  26             array(  5, 'nothing',     NULL),
  27         );
  28     var $objects = array();
  29
  30     function setUp() {
  31         global $CFG, $db;
  32         parent::setUp();
  33         load_test_table($CFG->prefix . $this->table, $this->data, $db);
  34         $keys = reset($this->data);
  35         foreach ($this->data as $row=>$datum) {
  36             if ($row == 0) {
  37                 continue;
  38             }
  39             $this->objects[$datum[0]] = (object) array_combine($keys, $datum);
  40         }
  41     }
  42
  43     function tearDown() {
  44         global $CFG, $db;
  45         remove_test_table($CFG->prefix . $this->table, $db);
  46         parent::tearDown();
  47     }
  48
  49     function test_where_clause() {
  50         $this->assertEqual(where_clause('f1', 'v1'), "WHERE f1 = 'v1'");
  51         $this->assertEqual(where_clause('f1', 'v1', 'f2', 2), "WHERE f1 = 'v1' AND f2 = '2'");
  52         $this->assertEqual(where_clause('f1', 'v1', 'f2', 1.75, 'f3', 'v3'), "WHERE f1 = 'v1' AND f2 = '1.75' AND f3 = 'v3'");
  53         $this->assertEqual(where_clause('f1', NULL), "WHERE f1 IS NULL");
  54     }
  55
  56     function test_record_exists() {
  57         $this->assertTrue(record_exists($this->table, 'numberfield', 101, 'id', 1));
  58         $this->assertFalse(record_exists($this->table, 'numberfield', 102, 'id', 1));
  59         $this->assertTrue(record_exists($this->table, 'numberfield', NULL));
  60     }
  61
  62     function test_record_exists_select() {
  63         $this->assertTrue(record_exists_select($this->table, 'numberfield = 101 AND id = 1'));
  64         $this->assertFalse(record_exists_select($this->table, 'numberfield = 102 AND id = 1'));
  65         $this->assertTrue(record_exists_select($this->table, 'numberfield IS NULL'));
  66     }
  67
  68     function test_record_exists_sql() {
  69         global $CFG;
  70         $this->assertTrue(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield = 101 AND id = 1"));
  71         $this->assertFalse(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield = 102 AND id = 1"));
  72         $this->assertTrue(record_exists_sql("SELECT * FROM {$CFG->prefix}$this->table WHERE numberfield IS NULL"));
  73     }
  74
  75
  76     function test_get_record() {
  77         // Get particular records.
  78         $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[1]), get_record($this->table, 'id', 1));
  79         $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[3]), get_record($this->table, 'textfield', 'tadpole', 'numberfield', 103));
  80         $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[5]), get_record($this->table, 'numberfield', null));
  81
  82         // Abiguous get attempt, should return one, and print a warning in debug mode.
  83         global $CFG;
  84         $old_debug = $CFG->debug;
  85         $CFG->debug = 0;
  86
  87         ob_start();
  88         $record = get_record($this->table, 'textfield', 'tadpole');
  89         $result = ob_get_contents();
  90         ob_end_clean();
  91         $this->assertEqual('', $result, '%s (No error ouside debug mode).');
  92
  93         $CFG->debug = E_ALL;
  94         ob_start();
  95         $record = get_record($this->table, 'textfield', 'tadpole');
  96         $result = ob_get_contents();
  97         ob_end_clean();
  98         $this->assert(new TextExpectation('Error:'), $result, 'Error in debug mode.');
  99
 100         $CFG->debug = $old_debug;
 101
 102         // Return only specified fields
 103         $expected = new stdClass;
 104         $expected->id = 3;
 105         $expected->textfield = 'tadpole';
 106         $result = get_record($this->table, 'id', '3', '', '', '', '', 'id,textfield');
 107         $this->assert(new CheckSpecifiedFieldsExpectation($expected), $result);
 108         $this->assertFalse(isset($result->numberfield));
 109         $expected = new stdClass;
 110         $expected->textfield = 'tadpole';
 111         $expected->numberfield = 103;
 112         $result = get_record($this->table, 'id', '3', '', '', '', '', 'textfield,numberfield');
 113         $this->assert(new CheckSpecifiedFieldsExpectation($expected), $result);
 114         $this->assertFalse(isset($result->id));
 115
 116         // Attempting to get a non-existant records should return false.
 117         $this->assertFalse(get_record($this->table, 'textfield', 'not there'), 'attempt to get non-existant record');
 118     }
 119
 120     function test_get_record_sql() {
 121         global $CFG;
 122         // Get particular records.
 123         $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[1]), get_record_sql("SELECT * FROM {$CFG->prefix}" . $this->table . " WHERE id = '1'", 'id = 1'));
 124
 125         // Abiguous get attempt, should return one, and print a warning in debug mode, unless $expectmultiple is used.
 126         $old_debug = $CFG->debug;
 127         $CFG->debug = 0;
 128
 129         ob_start();
 130         $record = get_record_sql("SELECT * FROM {$CFG->prefix}" . $this->table . " WHERE textfield = 'tadpole'");
 131         $result = ob_get_contents();
 132         ob_end_clean();
 133         $this->assertEqual('', $result, '%s (No error ouside debug mode).');
 134
 135         $CFG->debug = E_ALL;
 136         ob_start();
 137         $record = get_record_sql("SELECT * FROM {$CFG->prefix}" . $this->table . " WHERE textfield = 'tadpole'");
 138         $result = ob_get_contents();
 139         ob_end_clean();
 140         $this->assert(new TextExpectation('Error:'), $result, 'Error in debug mode.');
 141
 142         ob_start();
 143         $record = get_record_sql("SELECT * FROM {$CFG->prefix}" . $this->table . " WHERE textfield = 'tadpole'", true);
 144         $result = ob_get_contents();
 145         ob_end_clean();
 146         $this->assertEqual('', $result, '%s (No error ouside debug mode).');
 147
 148         $CFG->debug = $old_debug;
 149
 150         // Attempting to get a non-existant records should return false.
 151         $this->assertFalse(get_record_sql("SELECT * FROM {$CFG->prefix}" . $this->table . " WHERE textfield = 'not there'"), 'attempt to get non-existant record');
 152     }
 153
 154     function test_get_record_select() {
 155         // Get particular records.
 156         $this->assert(new CheckSpecifiedFieldsExpectation($this->objects[2]), get_record_select($this->table, 'id > 1 AND id < 3'), 'id > 1 AND id < 3');
 157
 158         // Abiguous get attempt, should return one, and print a warning in debug mode.
 159         global $CFG;
 160         $old_debug = $CFG->debug;
 161         $CFG->debug = 0;
 162
 163         ob_start();
 164         $record = get_record_select($this->table, "textfield = 'tadpole'");
 165         $result = ob_get_contents();
 166         ob_end_clean();
 167         $this->assertEqual('', $result, '%s (No error ouside debug mode).');
 168
 169         $CFG->debug = E_ALL;
 170         ob_start();
 171         $record = get_record_select($this->table, "textfield = 'tadpole'");
 172         $result = ob_get_contents();
 173         ob_end_clean();
 174         $this->assert(new TextExpectation('Error:'), $result, 'Error in debug mode.');
 175
 176         $CFG->debug = $old_debug;
 177
 178         // Return only specified fields
 179         $expected = new stdClass;
 180         $expected->id = 1;
 181         $expected->textfield = 'frog';
 182         $result = get_record_select($this->table, "textfield = 'frog'", 'id,textfield');
 183         $this->assert(new CheckSpecifiedFieldsExpectation($expected), $result);
 184         $this->assertFalse(isset($result->numberfield));
 185
 186         // Attempting to get a non-existant records should return false.
 187         $this->assertFalse(get_record_select($this->table, 'id > 666'), 'attempt to get non-existant record');
 188     }
 189
 190     function test_get_field() {
 191         $this->assertEqual(get_field($this->table, 'numberfield', 'id', 1), 101);
 192         $this->assertEqual(get_field($this->table, 'textfield', 'numberfield', 102), 'toad');
 193         $this->assertEqual(get_field($this->table, 'numberfield', 'textfield', 'tadpole', 'id', 4), 104);
 194         $this->assertEqual(get_field($this->table, 'numberfield + id', 'textfield', 'tadpole', 'id', 4), 108);
 195         $this->assertNull(get_field($this->table, 'numberfield', 'id', 5));
 196     }
 197
 198     function test_get_field_select() {
 199         $this->assertEqual(get_field_select($this->table, 'numberfield',  'id = 1'), 101);
 200     }
 201
 202     function test_get_field_sql() {
 203         global $CFG;
 204         $this->assertEqual(get_field_sql("SELECT numberfield FROM {$CFG->prefix}$this->table WHERE id = 1"), 101);
 205     }
 206
 207     function test_set_field() {
 208         set_field($this->table, 'numberfield', 12345, 'id', 1);
 209         $this->assertEqual(get_field($this->table, 'numberfield', 'id', 1), 12345);
 210
 211         set_field($this->table, 'textfield', 'newvalue', 'numberfield', 102);
 212         $this->assertEqual(get_field($this->table, 'textfield', 'numberfield', 102), 'newvalue');
 213
 214         set_field($this->table, 'numberfield', -1, 'textfield', 'tadpole', 'id', 4);
 215         $this->assertEqual(get_field($this->table, 'numberfield', 'textfield', 'tadpole', 'id', 4), -1);
 216
 217         set_field($this->table, 'textfield', null, 'id', 5);
 218         $this->assertNull(get_field($this->table, 'textfield', 'id', 5));
 219     }
 220
 221     function test_delete_records() {
 222         delete_records($this->table, 'id', 666);
 223         $this->assertEqual(count_records($this->table), 5);
 224         delete_records($this->table, 'id', 1);
 225         $this->assertEqual(count_records($this->table), 4);
 226         delete_records($this->table, 'textfield', 'tadpole');
 227         $this->assertEqual(count_records($this->table), 2);
 228         delete_records($this->table, 'numberfield', NULL);
 229         $this->assertEqual(count_records($this->table), 1);
 230     }
 231
 232     function test_delete_records2() {
 233         delete_records($this->table, 'textfield', 'tadpole', 'id', 4);
 234         $this->assertEqual(count_records($this->table), 4);
 235         delete_records($this->table);
 236         $this->assertEqual(count_records($this->table), 0);
 237     }
 238
 239     function test_delete_records_select() {
 240         delete_records_select($this->table, "textfield LIKE 't%'");
 241         $this->assertEqual(count_records($this->table), 2);
 242         delete_records_select($this->table, "'1' = '1'");
 243         $this->assertEqual(count_records($this->table), 0);
 244     }
 245
 246     function test_update_record() {
 247         global $CFG;
 248
 249         // Simple update
 250         $obj = new stdClass;
 251         $obj->id = 1;
 252         $obj->textfield = 'changed entry';
 253         $obj->numberfield = 123;
 254         $this->assertTrue(update_record($this->table, $obj));
 255         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id));
 256
 257         // Simple incomplete update
 258         $obj = new stdClass;
 259         $obj->id = 2;
 260         $obj->numberfield = 123;
 261         $this->assertTrue(update_record($this->table, $obj));
 262         $obj->textfield = 'toad';
 263         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id));
 264
 265         // Simple incomplete update
 266         $obj = new stdClass;
 267         $obj->id = 3;
 268         $obj->numberfield = 123;
 269         $obj->textfield = null;
 270         $this->assertTrue(update_record($this->table, $obj));
 271         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple update (%s)'), get_record($this->table, 'id', $obj->id));
 272
 273     }
 274
 275 //function insert_record($table, $dataobject, $returnid=true, $primarykey='id', $feedback=true) {
 276     function test_insert_record() {
 277         global $CFG;
 278
 279         // Simple insert with $returnid
 280         $obj = new stdClass;
 281         $obj->textfield = 'new entry';
 282         $obj->numberfield = 123;
 283         $this->assertEqual(insert_record($this->table, $obj), 6);
 284         $obj->id = 6;
 285         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert with returnid (%s)'), get_record($this->table, 'id', $obj->id));
 286
 287         // Simple insert without $returnid
 288         $obj = new stdClass;
 289         $obj->textfield = 'newer entry';
 290         $obj->numberfield = 321;
 291         $this->assertEqual(insert_record($this->table, $obj, false), true);
 292         $obj->id = 7;
 293         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert without returnid (%s)'), get_record($this->table, 'id', $obj->id));
 294
 295         // Insert with missing columns - should get defaults.
 296         $obj = new stdClass;
 297         $obj->textfield = 'partial entry';
 298         $this->assertEqual(insert_record($this->table, $obj), 8);
 299         $obj->id = 8;
 300         $obj->numberfield = 0xDefa;
 301         $got = get_record($this->table, 'id', 8);
 302         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with missing columns - should get defaults (%s)'), get_record($this->table, 'id', $obj->id));
 303
 304         // Insert with extra columns - should be ingnored.
 305         $obj = new stdClass;
 306         $obj->textfield = 'entry with extra';
 307         $obj->numberfield = 747;
 308         $obj->unused = 666;
 309         $this->assertEqual(insert_record($this->table, $obj), 9);
 310         $obj->id = 9;
 311         unset($obj->unused);
 312         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Insert with extra columns - should be ingnored (%s)'), get_record($this->table, 'id', $obj->id));
 313
 314         // Simple insert with $returnid and NULL values
 315         $obj = new stdClass;
 316         $obj->textfield = null;
 317         $obj->numberfield = null;
 318         $this->assertEqual(insert_record($this->table, $obj), 10);
 319         $obj->id = 10;
 320         $new = get_record($this->table, 'id', $obj->id);
 321         $this->assert(new CheckSpecifiedFieldsExpectation($obj, 'Simple insert with returnid (%s)'), $new);
 322         $this->assertNull($new->textfield);
 323         $this->assertNull($new->numberfield);
 324
 325         // Insert into nonexistant table - should fail.
 326         $obj = new stdClass;
 327         $obj->textfield = 'new entry';
 328         $obj->numberfield = 123;
 329         $this->assertFalse(insert_record('nonexistant_table', $obj), 'Insert into nonexistant table');
 330
 331         // Insert bad data - error should be printed - mysql not tested
 332         if ($CFG->dbfamily != 'mysql') {
 333             $obj = new stdClass;
 334             $obj->textfield = 'new entry';
 335             $obj->numberfield = 'not a number';
 336             ob_start();
 337             $this->assertFalse(insert_record($this->table, $obj), 'Insert bad data - should fail.');
 338             $result = ob_get_contents();
 339             ob_end_clean();
 340             $this->assert(new TextExpectation('ERROR:'), $result, 'Insert bad data - error should have been printed. This is known not to work on MySQL.');
 341         }
 342     }
 343 }
 344
 345 ?>