[Summary view]

Differences Between: [Versions 311 and 401] [Versions 401 and 403]

   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.
  19   *
  20   * @package core
  21   * @category test
  22   * @copyright 2020 Ruslan Kabalin
  23   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  
  27  /**
  28   * Test specific features of the Postgres dml.
  29   *
  30   * @package core
  31   * @category test
  32   * @copyright 2020 Ruslan Kabalin
  33   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  34   */
  35  class pgsql_native_moodle_database_test extends advanced_testcase {
  36  
  37      /**
  38       * Setup before class.
  39       */
  40      public static function setUpBeforeClass(): void {
  41          global $CFG;
  42          require_once($CFG->libdir.'/dml/pgsql_native_moodle_database.php');
  43      }
  44  
  45      /**
  46       * Set up.
  47       */
  48      public function setUp(): void {
  49          global $DB;
  50          parent::setUp();
  51          // Skip tests if not using Postgres.
  52          if (!($DB instanceof pgsql_native_moodle_database)) {
  53              $this->markTestSkipped('Postgres-only test');
  54          }
  55      }
  56  
  57      /**
  58       * Get a xmldb_table object for testing, deleting any existing table
  59       * of the same name, for example if one was left over from a previous test
  60       * run that crashed.
  61       *
  62       * @param string $suffix table name suffix, use if you need more test tables
  63       * @return xmldb_table the table object.
  64       */
  65      private function get_test_table($suffix = ''): xmldb_table {
  66          $tablename = "test_table";
  67          if ($suffix !== '') {
  68              $tablename .= $suffix;
  69          }
  70  
  71          $table = new xmldb_table($tablename);
  72          $table->setComment("This is a test'n drop table. You can drop it safely");
  73          return $table;
  74      }
  75  
  76      /**
  77       * Find out the current index used for unique SQL_PARAMS_NAMED.
  78       *
  79       * @return int
  80       */
  81      private function get_current_index(): int {
  82          global $DB;
  83          $reflector = new ReflectionClass($DB);
  84          $property = $reflector->getProperty('inorequaluniqueindex');
  85          $property->setAccessible(true);
  86          return (int) $property->getValue($DB);
  87      }
  88  
  89      public function test_get_in_or_equal_below_limit(): void {
  90          global $DB;
  91          // Just less than 65535 values, expect fallback to parent method.
  92          $invalues = range(1, 65533);
  93          list($usql, $params) = $DB->get_in_or_equal($invalues);
  94          $this->assertSame('IN ('.implode(',', array_fill(0, count($invalues), '?')).')', $usql);
  95          $this->assertEquals(count($invalues), count($params));
  96          foreach ($params as $key => $value) {
  97              $this->assertSame($invalues[$key], $value);
  98          }
  99      }
 100  
 101      public function test_get_in_or_equal_single_array_value(): void {
 102          global $DB;
 103          // Single value (in an array), expect fallback to parent method.
 104          $invalues = array('value1');
 105          list($usql, $params) = $DB->get_in_or_equal($invalues);
 106          $this->assertEquals("= ?", $usql);
 107          $this->assertCount(1, $params);
 108          $this->assertEquals($invalues[0], $params[0]);
 109      }
 110  
 111      public function test_get_in_or_equal_single_scalar_value(): void {
 112          global $DB;
 113          // Single value (scalar), expect fallback to parent method.
 114          $invalue = 'value1';
 115          list($usql, $params) = $DB->get_in_or_equal($invalue);
 116          $this->assertEquals("= ?", $usql);
 117          $this->assertCount(1, $params);
 118          $this->assertEquals($invalue, $params[0]);
 119      }
 120  
 121      public function test_get_in_or_equal_multiple_int_value(): void {
 122          global $DB;
 123          // 65535 values, int.
 124          $invalues = range(1, 65535);
 125          list($usql, $params) = $DB->get_in_or_equal($invalues);
 126          $this->assertSame('IN (VALUES ('.implode('),(', array_fill(0, count($invalues), '?::bigint')).'))', $usql);
 127          $this->assertEquals($params, $invalues);
 128      }
 129  
 130      public function test_get_in_or_equal_multiple_int_value_not_equal(): void {
 131          global $DB;
 132          // 65535 values, not equal, int.
 133          $invalues = range(1, 65535);
 134          list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_QM, 'param', false);
 135          $this->assertSame('NOT IN (VALUES ('.implode('),(', array_fill(0, count($invalues), '?::bigint')).'))', $usql);
 136          $this->assertEquals($params, $invalues);
 137      }
 138  
 139      public function test_get_in_or_equal_named_int_value_default_name(): void {
 140          global $DB;
 141          // 65535 values, int, SQL_PARAMS_NAMED.
 142          $index = $this->get_current_index();
 143          $invalues = range(1, 65535);
 144          list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_NAMED);
 145          $regex = '/^'.
 146              preg_quote('IN (VALUES (:param'.$index.'::bigint),(:param'.++$index.'::bigint),(:param'.++$index.'::bigint)').'/';
 147          $this->assertMatchesRegularExpression($regex, $usql);
 148          foreach ($params as $value) {
 149              $this->assertEquals(current($invalues), $value);
 150              next($invalues);
 151          }
 152      }
 153  
 154      public function test_get_in_or_equal_named_int_value_specified_name(): void {
 155          global $DB;
 156          // 65535 values, int, SQL_PARAMS_NAMED, define param name.
 157          $index = $this->get_current_index();
 158          $invalues = range(1, 65535);
 159          list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_NAMED, 'ppp');
 160          // We are in same DBI instance, expect uniqie param indexes.
 161          $regex = '/^'.
 162              preg_quote('IN (VALUES (:ppp'.$index.'::bigint),(:ppp'.++$index.'::bigint),(:ppp'.++$index.'::bigint)').'/';
 163          $this->assertMatchesRegularExpression($regex, $usql);
 164          foreach ($params as $value) {
 165              $this->assertEquals(current($invalues), $value);
 166              next($invalues);
 167          }
 168      }
 169  
 170      public function test_get_in_or_equal_named_scalar_value_specified_name(): void {
 171          global $DB;
 172          // 65535 values, string.
 173          $invalues = array_fill(1, 65535, 'abc');
 174          list($usql, $params) = $DB->get_in_or_equal($invalues);
 175          $this->assertMatchesRegularExpression('/^' . preg_quote('IN (VALUES (?::text),(?::text),(?::text)') . '/', $usql);
 176          foreach ($params as $value) {
 177              $this->assertEquals(current($invalues), $value);
 178              next($invalues);
 179          }
 180      }
 181  
 182      public function test_get_in_or_equal_query_use(): void {
 183          global $DB;
 184          $this->resetAfterTest();
 185          $dbman = $DB->get_manager();
 186          $table = $this->get_test_table();
 187          $tablename = $table->getName();
 188  
 189          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 190          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 191          $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
 192          $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL);
 193          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 194          $dbman->create_table($table);
 195  
 196          $rec1 = ['course' => 3, 'content' => 'hello', 'name' => 'xyz'];
 197          $DB->insert_record($tablename, $rec1);
 198          $rec2 = ['course' => 3, 'content' => 'world', 'name' => 'abc'];
 199          $DB->insert_record($tablename, $rec2);
 200          $rec3 = ['course' => 5, 'content' => 'hello', 'name' => 'xyz'];
 201          $DB->insert_record($tablename, $rec3);
 202          $rec4 = ['course' => 6, 'content' => 'universe'];
 203          $DB->insert_record($tablename, $rec4);
 204  
 205          $currentcount = $DB->count_records($tablename);
 206  
 207          // Getting all 4.
 208          $values = range(1, 65535);
 209          list($insql, $inparams) = $DB->get_in_or_equal($values);
 210          $sql = "SELECT *
 211                    FROM {{$tablename}}
 212                   WHERE id $insql
 213                ORDER BY id ASC";
 214          $this->assertCount($currentcount, $DB->get_records_sql($sql, $inparams));
 215  
 216          // Getting 'hello' records (text).
 217          $values = array_fill(1, 65535, 'hello');
 218          list($insql, $inparams) = $DB->get_in_or_equal($values);
 219          $sql = "SELECT *
 220                    FROM {{$tablename}}
 221                   WHERE content $insql
 222                ORDER BY id ASC";
 223          $result = $DB->get_records_sql($sql, $inparams);
 224          $this->assertCount(2, $result);
 225          $this->assertEquals([1, 3], array_keys($result));
 226  
 227          // Getting NOT 'hello' records (text).
 228          $values = array_fill(1, 65535, 'hello');
 229          list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
 230          $sql = "SELECT *
 231                    FROM {{$tablename}}
 232                   WHERE content $insql
 233                ORDER BY id ASC";
 234          $result = $DB->get_records_sql($sql, $inparams);
 235          $this->assertCount(2, $result);
 236          $this->assertEquals([2, 4], array_keys($result));
 237  
 238          // Getting 'xyz' records (char and NULL mix).
 239          $values = array_fill(1, 65535, 'xyz');
 240          list($insql, $inparams) = $DB->get_in_or_equal($values);
 241          $sql = "SELECT *
 242                    FROM {{$tablename}}
 243                   WHERE name $insql
 244                ORDER BY id ASC";
 245          $result = $DB->get_records_sql($sql, $inparams);
 246          $this->assertCount(2, $result);
 247          $this->assertEquals([1, 3], array_keys($result));
 248  
 249          // Getting NOT 'xyz' records (char and NULL mix).
 250          $values = array_fill(1, 65535, 'xyz');
 251          list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
 252          $sql = "SELECT *
 253                    FROM {{$tablename}}
 254                   WHERE name $insql
 255                ORDER BY id ASC";
 256          $result = $DB->get_records_sql($sql, $inparams);
 257          // NULL will not be in result.
 258          $this->assertCount(1, $result);
 259          $this->assertEquals([2], array_keys($result));
 260  
 261          // Getting numbeic records.
 262          $values = array_fill(1, 65535, 3);
 263          list($insql, $inparams) = $DB->get_in_or_equal($values);
 264          $sql = "SELECT *
 265                    FROM {{$tablename}}
 266                   WHERE course $insql
 267                ORDER BY id ASC";
 268          $result = $DB->get_records_sql($sql, $inparams);
 269          $this->assertCount(2, $result);
 270          $this->assertEquals([1, 2], array_keys($result));
 271  
 272          // Getting numbeic records with NOT condition.
 273          $values = array_fill(1, 65535, 3);
 274          list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
 275          $sql = "SELECT *
 276                    FROM {{$tablename}}
 277                   WHERE course $insql
 278                ORDER BY id ASC";
 279          $result = $DB->get_records_sql($sql, $inparams);
 280          $this->assertCount(2, $result);
 281          $this->assertEquals([3, 4], array_keys($result));
 282      }
 283  
 284      public function test_get_in_or_equal_big_table_query(): void {
 285          global $DB;
 286          $this->resetAfterTest();
 287          $dbman = $DB->get_manager();
 288  
 289          $table = $this->get_test_table();
 290          $tablename = $table->getName();
 291  
 292          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 293          $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
 294          $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
 295          $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
 296          $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
 297          $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
 298          $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 299          $dbman->create_table($table);
 300  
 301          $record = new stdClass();
 302          $record->course = 1;
 303          $record->oneint = null;
 304          $record->onenum = 1.0;
 305          $record->onechar = 'a';
 306          $record->onetext = 'aaa';
 307  
 308          $records = [];
 309          for ($i = 1; $i <= 65535; $i++) {
 310              $rec = clone($record);
 311              $rec->oneint = $i;
 312              $records[$i] = $rec;
 313          }
 314          // Populate table with 65535 records.
 315          $DB->insert_records($tablename, $records);
 316          // And one more record.
 317          $record->oneint = -1;
 318          $DB->insert_record($tablename, $record);
 319  
 320          // Check we can fetch all.
 321          $values = range(1, 65535);
 322          list($insql, $inparams) = $DB->get_in_or_equal($values);
 323          $sql = "SELECT *
 324                    FROM {{$tablename}}
 325                   WHERE oneint $insql
 326                ORDER BY id ASC";
 327          $stored = $DB->get_records_sql($sql, $inparams);
 328  
 329          // Check we got correct set of records.
 330          $this->assertCount(65535, $stored);
 331          $oneint = array_column($stored, 'oneint');
 332          $this->assertEquals($values, $oneint);
 333  
 334          // Check we can fetch all, SQL_PARAMS_NAMED.
 335          $values = range(1, 65535);
 336          list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED);
 337          $sql = "SELECT *
 338                    FROM {{$tablename}}
 339                   WHERE oneint $insql
 340                ORDER BY id ASC";
 341          $stored = $DB->get_records_sql($sql, $inparams);
 342  
 343          // Check we got correct set of records.
 344          $this->assertCount(65535, $stored);
 345          $oneint = array_column($stored, 'oneint');
 346          $this->assertEquals($values, $oneint);
 347  
 348          // Check we can fetch one using NOT IN.
 349          list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
 350          $sql = "SELECT *
 351                    FROM {{$tablename}}
 352                   WHERE oneint $insql
 353                ORDER BY id ASC";
 354          $stored = $DB->get_records_sql($sql, $inparams);
 355  
 356          // Check we got correct set of records.
 357          $this->assertCount(1, $stored);
 358          $oneint = array_column($stored, 'oneint');
 359          $this->assertEquals([-1], $oneint);
 360      }
 361  }