[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Test specific features of the Postgres dml support relating to recordsets.
  19   *
  20   * @package core
  21   * @category test
  22   * @copyright 2017 The Open University
  23   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  global $CFG;
  29  require_once($CFG->dirroot.'/lib/dml/pgsql_native_moodle_database.php');
  30  
  31  /**
  32   * Test specific features of the Postgres dml support relating to recordsets.
  33   *
  34   * @package core
  35   * @category test
  36   * @copyright 2017 The Open University
  37   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  class pgsql_native_recordset_test extends basic_testcase {
  40  
  41      /** @var pgsql_native_moodle_database Special database connection */
  42      protected $specialdb;
  43  
  44      /**
  45       * Creates a second db connection and a temp table with values in for testing.
  46       */
  47      protected function setUp(): void {
  48          global $DB;
  49  
  50          parent::setUp();
  51  
  52          // Skip tests if not using Postgres.
  53          if (!($DB instanceof pgsql_native_moodle_database)) {
  54              $this->markTestSkipped('Postgres-only test');
  55          }
  56      }
  57  
  58      /**
  59       * Initialises database connection with given fetch buffer size
  60       * @param int $fetchbuffersize Size of fetch buffer
  61       */
  62      protected function init_db($fetchbuffersize) {
  63          global $CFG, $DB;
  64  
  65          // To make testing easier, create a database with the same dboptions as the real one,
  66          // but a low number for the cursor size.
  67          $this->specialdb = \moodle_database::get_driver_instance('pgsql', 'native', true);
  68          $dboptions = $CFG->dboptions;
  69          $dboptions['fetchbuffersize'] = $fetchbuffersize;
  70          $this->specialdb->connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass, $CFG->dbname,
  71                  $DB->get_prefix(), $dboptions);
  72  
  73          // Create a temp table.
  74          $dbman = $this->specialdb->get_manager();
  75          $table = new xmldb_table('silly_test_table');
  76          $table->add_field('id', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, XMLDB_SEQUENCE);
  77          $table->add_field('msg', XMLDB_TYPE_CHAR, 255);
  78          $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
  79          $dbman->create_temp_table($table);
  80  
  81          // Add some records to the table.
  82          for ($index = 1; $index <= 7; $index++) {
  83              $this->specialdb->insert_record('silly_test_table', ['msg' => 'record' . $index]);
  84          }
  85      }
  86  
  87      /**
  88       * Gets rid of the second db connection.
  89       */
  90      protected function tearDown(): void {
  91          if ($this->specialdb) {
  92              $table = new xmldb_table('silly_test_table');
  93              $this->specialdb->get_manager()->drop_table($table);
  94              $this->specialdb->dispose();
  95              $this->specialdb = null;
  96          }
  97          parent::tearDown();
  98      }
  99  
 100      /**
 101       * Tests that get_recordset_sql works when using cursors, which it does when no limit is
 102       * specified.
 103       */
 104      public function test_recordset_cursors() {
 105          $this->init_db(3);
 106  
 107          // Query the table and check the actual queries using debug mode, also check the count.
 108          $this->specialdb->set_debug(true);
 109          $before = $this->specialdb->perf_get_queries();
 110          ob_start();
 111          $rs = $this->specialdb->get_recordset_sql('SELECT * FROM {silly_test_table} ORDER BY id');
 112          $index = 0;
 113          foreach ($rs as $rec) {
 114              $index++;
 115              $this->assertEquals('record' . $index, $rec->msg);
 116          }
 117          $this->assertEquals(7, $index);
 118          $rs->close();
 119          $debugging = ob_get_contents();
 120          ob_end_clean();
 121  
 122          // Expect 4 fetches - first three, next three, last one (with 2).
 123          $this->assert_query_regexps([
 124                  '~SELECT \* FROM~',
 125                  '~FETCH 3 FROM crs1~',
 126                  '~FETCH 3 FROM crs1~',
 127                  '~FETCH 3 FROM crs1~',
 128                  '~CLOSE crs1~'], $debugging);
 129  
 130          // There should have been 7 queries tracked for perf log.
 131          $this->assertEquals(5, $this->specialdb->perf_get_queries() - $before);
 132  
 133          // Try a second time - this time we'll request exactly 3 items so that it has to query
 134          // twice (as it can't tell if the first batch is the last).
 135          $before = $this->specialdb->perf_get_queries();
 136          ob_start();
 137          $rs = $this->specialdb->get_recordset_sql(
 138                  'SELECT * FROM {silly_test_table} WHERE id <= ? ORDER BY id', [3]);
 139          $index = 0;
 140          foreach ($rs as $rec) {
 141              $index++;
 142              $this->assertEquals('record' . $index, $rec->msg);
 143          }
 144          $this->assertEquals(3, $index);
 145          $rs->close();
 146          $debugging = ob_get_contents();
 147          ob_end_clean();
 148  
 149          $this->specialdb->set_debug(false);
 150  
 151          // Expect 2 fetches - first three, then next one (empty).
 152          $this->assert_query_regexps([
 153                  '~SELECT \* FROM~',
 154                  '~FETCH 3 FROM crs2~',
 155                  '~FETCH 3 FROM crs2~',
 156                  '~CLOSE crs2~'], $debugging);
 157  
 158          // There should have been 4 queries tracked for perf log.
 159          $this->assertEquals(4, $this->specialdb->perf_get_queries() - $before);
 160      }
 161  
 162      /**
 163       * Tests that get_recordset_sql works when using cursors and when there are two overlapping
 164       * recordsets being used.
 165       */
 166      public function test_recordset_cursors_overlapping() {
 167          $this->init_db(3);
 168  
 169          $rs1 = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 170          $rs2 = $this->specialdb->get_recordset('silly_test_table', null, 'id DESC');
 171  
 172          // Read first 3 from first recordset.
 173          $read = [];
 174          $read[] = $rs1->current()->id;
 175          $rs1->next();
 176          $read[] = $rs1->current()->id;
 177          $rs1->next();
 178          $read[] = $rs1->current()->id;
 179          $rs1->next();
 180          $this->assertEquals([1, 2, 3], $read);
 181  
 182          // Read 5 from second recordset.
 183          $read = [];
 184          $read[] = $rs2->current()->id;
 185          $rs2->next();
 186          $read[] = $rs2->current()->id;
 187          $rs2->next();
 188          $read[] = $rs2->current()->id;
 189          $rs2->next();
 190          $read[] = $rs2->current()->id;
 191          $rs2->next();
 192          $read[] = $rs2->current()->id;
 193          $rs2->next();
 194          $this->assertEquals([7, 6, 5, 4, 3], $read);
 195  
 196          // Now read remainder of first recordset and close it.
 197          $read = [];
 198          $read[] = $rs1->current()->id;
 199          $rs1->next();
 200          $read[] = $rs1->current()->id;
 201          $rs1->next();
 202          $read[] = $rs1->current()->id;
 203          $rs1->next();
 204          $read[] = $rs1->current()->id;
 205          $rs1->next();
 206          $this->assertFalse($rs1->valid());
 207          $this->assertEquals([4, 5, 6, 7], $read);
 208          $rs1->close();
 209  
 210          // And remainder of second.
 211          $read = [];
 212          $read[] = $rs2->current()->id;
 213          $rs2->next();
 214          $read[] = $rs2->current()->id;
 215          $rs2->next();
 216          $this->assertFalse($rs2->valid());
 217          $this->assertEquals([2, 1], $read);
 218          $rs2->close();
 219      }
 220  
 221      /**
 222       * Tests that get_recordset_sql works when using cursors and transactions inside.
 223       */
 224      public function test_recordset_cursors_transaction_inside() {
 225          $this->init_db(3);
 226  
 227          // Transaction inside the recordset processing.
 228          $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 229          $read = [];
 230          foreach ($rs as $rec) {
 231              $read[] = $rec->id;
 232              $transaction = $this->specialdb->start_delegated_transaction();
 233              $transaction->allow_commit();
 234          }
 235          $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 236          $rs->close();
 237      }
 238  
 239      /**
 240       * Tests that get_recordset_sql works when using cursors and a transaction outside.
 241       */
 242      public function test_recordset_cursors_transaction_outside() {
 243          $this->init_db(3);
 244  
 245          // Transaction outside the recordset processing.
 246          $transaction = $this->specialdb->start_delegated_transaction();
 247          $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 248          $read = [];
 249          foreach ($rs as $rec) {
 250              $read[] = $rec->id;
 251          }
 252          $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 253          $rs->close();
 254          $transaction->allow_commit();
 255      }
 256  
 257      /**
 258       * Tests that get_recordset_sql works when using cursors and a transaction overlapping.
 259       */
 260      public function test_recordset_cursors_transaction_overlapping_before() {
 261          $this->init_db(3);
 262  
 263          // Transaction outside the recordset processing.
 264          $transaction = $this->specialdb->start_delegated_transaction();
 265          $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 266          $transaction->allow_commit();
 267          $read = [];
 268          foreach ($rs as $rec) {
 269              $read[] = $rec->id;
 270          }
 271          $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 272          $rs->close();
 273      }
 274  
 275      /**
 276       * Tests that get_recordset_sql works when using cursors and a transaction overlapping.
 277       */
 278      public function test_recordset_cursors_transaction_overlapping_after() {
 279          $this->init_db(3);
 280  
 281          // Transaction outside the recordset processing.
 282          $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 283          $transaction = $this->specialdb->start_delegated_transaction();
 284          $read = [];
 285          foreach ($rs as $rec) {
 286              $read[] = $rec->id;
 287          }
 288          $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 289          $rs->close();
 290          $transaction->allow_commit();
 291      }
 292  
 293      /**
 294       * Tests that get_recordset_sql works when using cursors and a transaction that 'fails' and gets
 295       * rolled back.
 296       */
 297      public function test_recordset_cursors_transaction_rollback() {
 298          $this->init_db(3);
 299  
 300          try {
 301              $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 302              $transaction = $this->specialdb->start_delegated_transaction();
 303              $this->specialdb->delete_records('silly_test_table', ['id' => 5]);
 304              $transaction->rollback(new dml_transaction_exception('rollback please'));
 305              $this->fail('should not get here');
 306          } catch (dml_transaction_exception $e) {
 307              $this->assertStringContainsString('rollback please', $e->getMessage());
 308          } finally {
 309  
 310              // Rollback should not kill our recordset.
 311              $read = [];
 312              foreach ($rs as $rec) {
 313                  $read[] = $rec->id;
 314              }
 315              $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 316  
 317              // This would happen in real code (that isn't within the same function) anyway because
 318              // it would go out of scope.
 319              $rs->close();
 320          }
 321  
 322          // OK, transaction aborted, now get the recordset again and check nothing was deleted.
 323          $rs = $this->specialdb->get_recordset('silly_test_table', null, 'id');
 324          $read = [];
 325          foreach ($rs as $rec) {
 326              $read[] = $rec->id;
 327          }
 328          $this->assertEquals([1, 2, 3, 4, 5, 6, 7], $read);
 329          $rs->close();
 330      }
 331  
 332      /**
 333       * Tests that get_recordset_sql works when not using cursors, because a limit is specified.
 334       */
 335      public function test_recordset_no_cursors_limit() {
 336          $this->init_db(3);
 337  
 338          $this->specialdb->set_debug(true);
 339          $before = $this->specialdb->perf_get_queries();
 340          ob_start();
 341          $rs = $this->specialdb->get_recordset_sql(
 342                  'SELECT * FROM {silly_test_table} ORDER BY id', [], 0, 100);
 343          $index = 0;
 344          foreach ($rs as $rec) {
 345              $index++;
 346              $this->assertEquals('record' . $index, $rec->msg);
 347          }
 348          $this->assertEquals(7, $index);
 349          $rs->close();
 350          $this->specialdb->set_debug(false);
 351          $debugging = ob_get_contents();
 352          ob_end_clean();
 353  
 354          // Expect direct request without using cursors.
 355          $this->assert_query_regexps(['~SELECT \* FROM~'], $debugging);
 356  
 357          // There should have been 1 query tracked for perf log.
 358          $this->assertEquals(1, $this->specialdb->perf_get_queries() - $before);
 359      }
 360  
 361      /**
 362       * Tests that get_recordset_sql works when not using cursors, because the config setting turns
 363       * them off.
 364       */
 365      public function test_recordset_no_cursors_config() {
 366          $this->init_db(0);
 367  
 368          $this->specialdb->set_debug(true);
 369          $before = $this->specialdb->perf_get_queries();
 370          ob_start();
 371          $rs = $this->specialdb->get_recordset_sql('SELECT * FROM {silly_test_table} ORDER BY id');
 372          $index = 0;
 373          foreach ($rs as $rec) {
 374              $index++;
 375              $this->assertEquals('record' . $index, $rec->msg);
 376          }
 377          $this->assertEquals(7, $index);
 378          $rs->close();
 379          $this->specialdb->set_debug(false);
 380          $debugging = ob_get_contents();
 381          ob_end_clean();
 382  
 383          // Expect direct request without using cursors.
 384          $this->assert_query_regexps(['~SELECT \* FROM~'], $debugging);
 385  
 386          // There should have been 1 query tracked for perf log.
 387          $this->assertEquals(1, $this->specialdb->perf_get_queries() - $before);
 388      }
 389  
 390      /**
 391       * Asserts that database debugging output matches the expected list of SQL queries, specified
 392       * as an array of regular expressions.
 393       *
 394       * @param string[] $expected Expected regular expressions
 395       * @param string $debugging Debugging text from the database
 396       */
 397      protected function assert_query_regexps(array $expected, $debugging) {
 398          $lines = explode("\n", $debugging);
 399          $index = 0;
 400          $params = false;
 401          foreach ($lines as $line) {
 402              if ($params) {
 403                  if ($line === ')]') {
 404                      $params = false;
 405                  }
 406                  continue;
 407              }
 408              // Skip irrelevant lines.
 409              if (preg_match('~^---~', $line)) {
 410                  continue;
 411              }
 412              if (preg_match('~^Query took~', $line)) {
 413                  continue;
 414              }
 415              if (trim($line) === '') {
 416                  continue;
 417              }
 418              // Skip param lines.
 419              if ($line === '[array (') {
 420                  $params = true;
 421                  continue;
 422              }
 423              if (!array_key_exists($index, $expected)) {
 424                  $this->fail('More queries than expected');
 425              }
 426              $this->assertMatchesRegularExpression($expected[$index++], $line);
 427          }
 428          if (array_key_exists($index, $expected)) {
 429              $this->fail('Fewer queries than expected');
 430          }
 431      }
 432  
 433  }