[Summary view]

Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 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   * DML read/read-write database handle use tests
  19   *
  20   * @package    core
  21   * @category   dml
  22   * @copyright  2018 Srdjan Janković, Catalyst IT
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  require_once (__DIR__.'/fixtures/read_slave_moodle_database_table_names.php');
  29  require_once (__DIR__.'/fixtures/read_slave_moodle_database_special.php');
  30  
  31  /**
  32   * DML read/read-write database handle use tests
  33   *
  34   * @package    core
  35   * @category   dml
  36   * @copyright  2018 Catalyst IT
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  class core_dml_read_slave_testcase extends base_testcase {
  40  
  41      /** @var float */
  42      static private $dbreadonlylatency = 0.8;
  43  
  44      /**
  45       * Instantiates a test database interface object.
  46       *
  47       * @param bool $wantlatency
  48       * @param mixed $readonly
  49       * @param mixed $dbclass
  50       * @return read_slave_moodle_database $db
  51       */
  52      public function new_db(
  53          $wantlatency = false,
  54          $readonly = [
  55              ['dbhost' => 'test_ro1', 'dbport' => 1, 'dbuser' => 'test1', 'dbpass' => 'test1'],
  56              ['dbhost' => 'test_ro2', 'dbport' => 2, 'dbuser' => 'test2', 'dbpass' => 'test2'],
  57              ['dbhost' => 'test_ro3', 'dbport' => 3, 'dbuser' => 'test3', 'dbpass' => 'test3'],
  58          ],
  59          $dbclass = read_slave_moodle_database::class
  60      ) : read_slave_moodle_database {
  61          $dbhost = 'test_rw';
  62          $dbname = 'test';
  63          $dbuser = 'test';
  64          $dbpass = 'test';
  65          $prefix = 'test_';
  66          $dboptions = ['readonly' => ['instance' => $readonly, 'exclude_tables' => ['exclude']]];
  67          if ($wantlatency) {
  68              $dboptions['readonly']['latency'] = self::$dbreadonlylatency;
  69          }
  70  
  71          $db = new $dbclass();
  72          $db->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
  73          return $db;
  74      }
  75  
  76      /**
  77       * Asert that the mock handle returned from read_slave_moodle_database methods
  78       * is a readonly slave handle.
  79       *
  80       * @param string $handle
  81       * @return void
  82       */
  83      private function assert_readonly_handle($handle) : void {
  84          $this->assertRegExp('/^test_ro\d:\d:test\d:test\d$/', $handle);
  85      }
  86  
  87      /**
  88       * moodle_read_slave_trait::table_names() test data provider
  89       *
  90       * @return array
  91       * @dataProvider table_names_provider
  92       */
  93      public function table_names_provider() : array {
  94          return [
  95              [
  96                  "SELECT *
  97                   FROM {user} u
  98                   JOIN (
  99                       SELECT DISTINCT u.id FROM {user} u
 100                       JOIN {user_enrolments} ue1 ON ue1.userid = u.id
 101                       JOIN {enrol} e ON e.id = ue1.enrolid
 102                       WHERE u.id NOT IN (
 103                           SELECT DISTINCT ue.userid FROM {user_enrolments} ue
 104                           JOIN {enrol} e ON (e.id = ue.enrolid AND e.courseid = 1)
 105                           WHERE ue.status = 'active'
 106                             AND e.status = 'enabled'
 107                             AND ue.timestart < now()
 108                             AND (ue.timeend = 0 OR ue.timeend > now())
 109                       )
 110                   ) je ON je.id = u.id
 111                   JOIN (
 112                       SELECT DISTINCT ra.userid
 113                         FROM {role_assignments} ra
 114                        WHERE ra.roleid IN (1, 2, 3)
 115                          AND ra.contextid = 'ctx'
 116                    ) rainner ON rainner.userid = u.id
 117                    WHERE u.deleted = 0",
 118                  [
 119                      'user',
 120                      'user',
 121                      'user_enrolments',
 122                      'enrol',
 123                      'user_enrolments',
 124                      'enrol',
 125                      'role_assignments',
 126                  ]
 127              ],
 128          ];
 129      }
 130  
 131      /**
 132       * Test moodle_read_slave_trait::table_names() query parser.
 133       *
 134       * @param string $sql
 135       * @param array $tables
 136       * @return void
 137       * @dataProvider table_names_provider
 138       */
 139      public function test_table_names($sql, $tables) : void {
 140          $db = new read_slave_moodle_database_table_names();
 141  
 142          $this->assertEquals($tables, $db->table_names($db->fix_sql_params($sql)[0]));
 143      }
 144  
 145      /**
 146       * Test correct database handles are used in a read-read-write-read scenario.
 147       * Test lazy creation of the write handle.
 148       *
 149       * @return void
 150       */
 151      public function test_read_read_write_read() : void {
 152          $DB = $this->new_db(true);
 153  
 154          $this->assertEquals(0, $DB->perf_get_reads_slave());
 155          $this->assertNull($DB->get_dbhwrite());
 156  
 157          $handle = $DB->get_records('table');
 158          $this->assert_readonly_handle($handle);
 159          $readsslave = $DB->perf_get_reads_slave();
 160          $this->assertGreaterThan(0, $readsslave);
 161          $this->assertNull($DB->get_dbhwrite());
 162  
 163          $handle = $DB->get_records('table2');
 164          $this->assert_readonly_handle($handle);
 165          $readsslave = $DB->perf_get_reads_slave();
 166          $this->assertGreaterThan(1, $readsslave);
 167          $this->assertNull($DB->get_dbhwrite());
 168  
 169          $now = microtime(true);
 170          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 171          $this->assertEquals('test_rw::test:test', $handle);
 172  
 173          if (microtime(true) - $now < self::$dbreadonlylatency) {
 174              $handle = $DB->get_records('table');
 175              $this->assertEquals('test_rw::test:test', $handle);
 176              $this->assertEquals($readsslave, $DB->perf_get_reads_slave());
 177  
 178              sleep(1);
 179          }
 180  
 181          $handle = $DB->get_records('table');
 182          $this->assert_readonly_handle($handle);
 183          $this->assertEquals($readsslave + 1, $DB->perf_get_reads_slave());
 184      }
 185  
 186      /**
 187       * Test correct database handles are used in a read-write-write scenario.
 188       *
 189       * @return void
 190       */
 191      public function test_read_write_write() : void {
 192          $DB = $this->new_db();
 193  
 194          $this->assertEquals(0, $DB->perf_get_reads_slave());
 195          $this->assertNull($DB->get_dbhwrite());
 196  
 197          $handle = $DB->get_records('table');
 198          $this->assert_readonly_handle($handle);
 199          $readsslave = $DB->perf_get_reads_slave();
 200          $this->assertGreaterThan(0, $readsslave);
 201          $this->assertNull($DB->get_dbhwrite());
 202  
 203          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 204          $this->assertEquals('test_rw::test:test', $handle);
 205  
 206          $handle = $DB->update_record_raw('table', array('id' => 1, 'name' => 'blah2'));
 207          $this->assertEquals('test_rw::test:test', $handle);
 208          $this->assertEquals($readsslave, $DB->perf_get_reads_slave());
 209      }
 210  
 211      /**
 212       * Test correct database handles are used in a write-read-read scenario.
 213       *
 214       * @return void
 215       */
 216      public function test_write_read_read() : void {
 217          $DB = $this->new_db();
 218  
 219          $this->assertEquals(0, $DB->perf_get_reads_slave());
 220          $this->assertNull($DB->get_dbhwrite());
 221  
 222          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 223          $this->assertEquals('test_rw::test:test', $handle);
 224          $this->assertEquals(0, $DB->perf_get_reads_slave());
 225  
 226          sleep(1);
 227          $handle = $DB->get_records('table');
 228          $this->assertEquals('test_rw::test:test', $handle);
 229          $this->assertEquals(0, $DB->perf_get_reads_slave());
 230  
 231          $handle = $DB->get_records('table2');
 232          $this->assert_readonly_handle($handle);
 233          $this->assertEquals(1, $DB->perf_get_reads_slave());
 234  
 235          $handle = $DB->get_records_sql("SELECT * FROM {table2} JOIN {table}");
 236          $this->assertEquals('test_rw::test:test', $handle);
 237          $this->assertEquals(1, $DB->perf_get_reads_slave());
 238      }
 239  
 240      /**
 241       * Test readonly handle is not used for reading from temptables.
 242       *
 243       * @return void
 244       */
 245      public function test_read_temptable() : void {
 246          $DB = $this->new_db();
 247          $DB->add_temptable('temptable1');
 248  
 249          $this->assertEquals(0, $DB->perf_get_reads_slave());
 250          $this->assertNull($DB->get_dbhwrite());
 251  
 252          $handle = $DB->get_records('temptable1');
 253          $this->assertEquals('test_rw::test:test', $handle);
 254          $this->assertEquals(0, $DB->perf_get_reads_slave());
 255  
 256          $DB->delete_temptable('temptable1');
 257      }
 258  
 259      /**
 260       * Test readonly handle is not used for reading from excluded tables.
 261       *
 262       * @return void
 263       */
 264      public function test_read_excluded_tables() : void {
 265          $DB = $this->new_db();
 266  
 267          $this->assertEquals(0, $DB->perf_get_reads_slave());
 268          $this->assertNull($DB->get_dbhwrite());
 269  
 270          $handle = $DB->get_records('exclude');
 271          $this->assertEquals('test_rw::test:test', $handle);
 272          $this->assertEquals(0, $DB->perf_get_reads_slave());
 273      }
 274  
 275      /**
 276       * Test readonly handle is not used during transactions.
 277       * Test last written time is adjusted post-transaction,
 278       * so the latency parameter is applied properly.
 279       *
 280       * @return void
 281       */
 282      public function test_transaction() : void {
 283          $DB = $this->new_db(true);
 284  
 285          $this->assertNull($DB->get_dbhwrite());
 286  
 287          $transaction = $DB->start_delegated_transaction();
 288          $now = microtime(true);
 289          $handle = $DB->get_records_sql("SELECT * FROM {table}");
 290          // Use rw handle during transaction.
 291          $this->assertEquals('test_rw::test:test', $handle);
 292  
 293          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 294          // Introduce delay so we can check that table write timestamps
 295          // are adjusted properly.
 296          sleep(1);
 297          $transaction->allow_commit();
 298          // This condition should always evaluate true, however we need to
 299          // safeguard from an unaccounted delay that can break this test.
 300          if (microtime(true) - $now < 1 + self::$dbreadonlylatency) {
 301              // Not enough time passed, use rw handle.
 302              $handle = $DB->get_records_sql("SELECT * FROM {table}");
 303              $this->assertEquals('test_rw::test:test', $handle);
 304  
 305              // Make sure enough time passes.
 306              sleep(1);
 307          }
 308  
 309          // Exceeded latency time, use ro handle.
 310          $handle = $DB->get_records_sql("SELECT * FROM {table}");
 311          $this->assert_readonly_handle($handle);
 312      }
 313  
 314      /**
 315       * Test failed readonly connection falls back to write connection.
 316       *
 317       * @return void
 318       */
 319      public function test_read_only_conn_fail() : void {
 320          $DB = $this->new_db(false, 'test_ro_fail');
 321  
 322          $this->assertEquals(0, $DB->perf_get_reads_slave());
 323          $this->assertNotNull($DB->get_dbhwrite());
 324  
 325          $handle = $DB->get_records('table');
 326          $this->assertEquals('test_rw::test:test', $handle);
 327          $readsslave = $DB->perf_get_reads_slave();
 328          $this->assertEquals(0, $readsslave);
 329      }
 330  
 331      /**
 332       * In multiple slaves scenario, test failed readonly connection falls back to
 333       * another readonly connection.
 334       *
 335       * @return void
 336       */
 337      public function test_read_only_conn_first_fail() : void {
 338          $DB = $this->new_db(false, ['test_ro_fail', 'test_ro_ok']);
 339  
 340          $this->assertEquals(0, $DB->perf_get_reads_slave());
 341          $this->assertNull($DB->get_dbhwrite());
 342  
 343          $handle = $DB->get_records('table');
 344          $this->assertEquals('test_ro_ok::test:test', $handle);
 345          $readsslave = $DB->perf_get_reads_slave();
 346          $this->assertEquals(1, $readsslave);
 347      }
 348  
 349      /**
 350       * Helper to restore global $DB
 351       *
 352       * @param callable $test
 353       * @return void
 354       */
 355      private function with_global_db($test) {
 356          global $DB;
 357  
 358          $dbsave = $DB;
 359          try {
 360              $test();
 361          }
 362          finally {
 363              $DB = $dbsave;
 364          }
 365      }
 366  
 367      /**
 368       * Test lock_db table exclusion
 369       *
 370       * @return void
 371       */
 372      public function test_lock_db() : void {
 373          $this->with_global_db(function () {
 374              global $DB;
 375  
 376              $DB = $this->new_db(true, ['test_ro'], read_slave_moodle_database_special::class);
 377              $DB->set_tables([
 378                  'lock_db' => [
 379                      'columns' => [
 380                          'resourcekey' => (object)['meta_type' => ''],
 381                          'owner' => (object)['meta_type' => ''],
 382                      ]
 383                  ]
 384              ]);
 385  
 386              $this->assertEquals(0, $DB->perf_get_reads_slave());
 387              $this->assertNull($DB->get_dbhwrite());
 388  
 389              $lockfactory = new \core\lock\db_record_lock_factory('default');
 390              if (!$lockfactory->is_available()) {
 391                  $this->markTestSkipped("db_record_lock_factory not available");
 392              }
 393  
 394              $lock = $lockfactory->get_lock('abc', 2);
 395              $lock->release();
 396              $this->assertEquals(0, $DB->perf_get_reads_slave());
 397              $this->assertTrue($DB->perf_get_reads() > 0);
 398          });
 399      }
 400  
 401      /**
 402       * Test sessions table exclusion
 403       *
 404       * @return void
 405       */
 406      public function test_sessions() : void {
 407          $this->with_global_db(function () {
 408              global $DB, $CFG;
 409  
 410              $CFG->dbsessions = true;
 411              $DB = $this->new_db(true, ['test_ro'], read_slave_moodle_database_special::class);
 412              $DB->set_tables([
 413                  'sessions' => [
 414                      'columns' => [
 415                          'sid' => (object)['meta_type' => ''],
 416                      ]
 417                  ]
 418              ]);
 419  
 420              $this->assertEquals(0, $DB->perf_get_reads_slave());
 421              $this->assertNull($DB->get_dbhwrite());
 422  
 423              $session = new \core\session\database();
 424              $session->handler_read('dummy');
 425  
 426              $this->assertEquals(0, $DB->perf_get_reads_slave());
 427              $this->assertTrue($DB->perf_get_reads() > 0);
 428          });
 429  
 430          \core\session\manager::restart_with_write_lock();
 431      }
 432  }