[Summary view]

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