[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402]

   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 tests for pgsql_native_moodle_database
  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  namespace core;
  27  
  28  use moodle_database;
  29  use xmldb_table;
  30  
  31  defined('MOODLE_INTERNAL') || die();
  32  
  33  require_once (__DIR__.'/fixtures/read_slave_moodle_database_mock_pgsql.php');
  34  
  35  /**
  36   * DML pgsql_native_moodle_database read slave specific tests
  37   *
  38   * @package    core
  39   * @category   dml
  40   * @copyright  2018 Catalyst IT
  41   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  42   * @covers     \pgsql_native_moodle_database
  43   */
  44  class dml_pgsql_read_slave_test extends \advanced_testcase {
  45      /**
  46       * Test correct database handles are used for cursors
  47       *
  48       * @return void
  49       */
  50      public function test_cursors(): void {
  51          $DB = new read_slave_moodle_database_mock_pgsql();
  52  
  53          // Declare a cursor on a table that has not been written to.
  54          list($sql, $params, $type) = $DB->fix_sql_params("SELECT * FROM {table}");
  55          $sql = "DECLARE crs1 NO SCROLL CURSOR WITH HOLD FOR $sql";
  56          $DB->query_start($sql, null, SQL_QUERY_SELECT);
  57          $DB->query_end(null);
  58  
  59          // Declare a cursor on a table that has been written to.
  60          list($sql, $params, $type) = $DB->fix_sql_params("INSERT INTO {table2} (name) VALUES ('blah')");
  61          $DB->query_start($sql, null, SQL_QUERY_INSERT);
  62          $DB->query_end(null);
  63          list($sql, $params, $type) = $DB->fix_sql_params("SELECT * FROM {table2}");
  64          $sql = "DECLARE crs2 NO SCROLL CURSOR WITH HOLD FOR $sql";
  65          $DB->query_start($sql, null, SQL_QUERY_SELECT);
  66          $DB->query_end(null);
  67  
  68          // Read from the non-written to table cursor.
  69          $sql = 'FETCH 1 FROM crs1';
  70          $DB->query_start($sql, null, SQL_QUERY_AUX);
  71          $this->assertTrue($DB->db_handle_is_ro());
  72          $DB->query_end(null);
  73  
  74          // Read from the written to table cursor.
  75          $sql = 'FETCH 1 FROM crs2';
  76          $DB->query_start($sql, null, SQL_QUERY_AUX);
  77          $this->assertTrue($DB->db_handle_is_rw());
  78          $DB->query_end(null);
  79  
  80          // Close the non-written to table cursor.
  81          $sql = 'CLOSE crs1';
  82          $DB->query_start($sql, [], SQL_QUERY_AUX);
  83          $this->assertTrue($DB->db_handle_is_ro());
  84          $DB->query_end(null);
  85  
  86          // Close the written to table cursor.
  87          $sql = 'CLOSE crs2';
  88          $DB->query_start($sql, [], SQL_QUERY_AUX);
  89          $this->assertTrue($DB->db_handle_is_rw());
  90          $DB->query_end(null);
  91      }
  92  
  93      /**
  94       * Test readonly handle is used for reading from random pg_*() call queries.
  95       *
  96       * @return void
  97       */
  98      public function test_read_pg_table(): void {
  99          $DB = new read_slave_moodle_database_mock_pgsql();
 100  
 101          $this->assertEquals(0, $DB->perf_get_reads_slave());
 102  
 103          $DB->query_start('SELECT pg_whatever(1)', null, SQL_QUERY_SELECT);
 104          $this->assertTrue($DB->db_handle_is_ro());
 105          $DB->query_end(null);
 106          $this->assertEquals(1, $DB->perf_get_reads_slave());
 107      }
 108  
 109      /**
 110       * Test readonly handle is not used for reading from special pg_*() call queries,
 111       * pg_try_advisory_lock and pg_advisory_unlock.
 112       *
 113       * @return void
 114       */
 115      public function test_read_pg_lock_table(): void {
 116          $DB = new read_slave_moodle_database_mock_pgsql();
 117  
 118          $this->assertEquals(0, $DB->perf_get_reads_slave());
 119  
 120          foreach (['pg_try_advisory_lock', 'pg_advisory_unlock'] as $fn) {
 121              $DB->query_start("SELECT $fn(1)", null, SQL_QUERY_SELECT);
 122              $this->assertTrue($DB->db_handle_is_rw());
 123              $DB->query_end(null);
 124              $this->assertEquals(0, $DB->perf_get_reads_slave());
 125          }
 126      }
 127  
 128      /**
 129       * Test readonly handle is used for SQL_QUERY_AUX_READONLY queries.
 130       *
 131       * @return void
 132       */
 133      public function test_aux_readonly(): void {
 134          global $DB;
 135          $this->resetAfterTest();
 136  
 137          if ($DB->get_dbfamily() != 'postgres') {
 138              $this->markTestSkipped('Not postgres');
 139          }
 140  
 141          // Open second connection.
 142          $cfg = $DB->export_dbconfig();
 143          if (!isset($cfg->dboptions)) {
 144              $cfg->dboptions = [];
 145          }
 146          if (!isset($cfg->dboptions['readonly'])) {
 147              $cfg->dboptions['readonly'] = [
 148                  'instance' => [$cfg->dbhost]
 149              ];
 150          }
 151  
 152          // Get a separate disposable db connection handle with guaranteed 'readonly' config.
 153          $db2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
 154          $db2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
 155  
 156          $reads = $db2->perf_get_reads();
 157          $readsprimary = $reads - $db2->perf_get_reads_slave();
 158  
 159          // Readonly handle queries.
 160  
 161          $db2->get_server_info();
 162          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 163          $this->assertGreaterThan($readsprimary, $readsprimary = $reads - $db2->perf_get_reads_slave());
 164  
 165          $db2->setup_is_unicodedb();
 166          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 167          $this->assertEquals($readsprimary, $reads - $db2->perf_get_reads_slave());
 168  
 169          $db2->get_tables();
 170          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 171          $this->assertEquals($readsprimary, $reads - $db2->perf_get_reads_slave());
 172  
 173          $db2->get_indexes('course');
 174          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 175          $this->assertEquals($readsprimary, $reads - $db2->perf_get_reads_slave());
 176  
 177          $db2->get_columns('course');
 178          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 179          $this->assertEquals($readsprimary, $reads - $db2->perf_get_reads_slave());
 180  
 181          // Readwrite handle queries.
 182  
 183          $tablename = 'test_table';
 184          $table = new xmldb_table($tablename);
 185          $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
 186          $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
 187          $dbman = $db2->get_manager();
 188          $dbman->create_table($table);
 189          $db2->get_columns($tablename);
 190          $this->assertGreaterThan($reads, $reads = $db2->perf_get_reads());
 191          $this->assertGreaterThan($readsprimary, $reads - $db2->perf_get_reads_slave());
 192      }
 193  
 194      /**
 195       * Test readonly handle is not used for reading from temptables
 196       * and getting temptables metadata.
 197       * This test is only possible because of no pg_query error reporting.
 198       * It may need to be removed in the future if we decide to handle null
 199       * results in pgsql_native_moodle_database differently.
 200       *
 201       * @return void
 202       */
 203      public function test_temp_table(): void {
 204          global $DB;
 205  
 206          if ($DB->get_dbfamily() != 'postgres') {
 207              $this->markTestSkipped('Not postgres');
 208          }
 209  
 210          // Open second connection.
 211          $cfg = $DB->export_dbconfig();
 212          if (!isset($cfg->dboptions)) {
 213              $cfg->dboptions = [];
 214          }
 215          if (!isset($cfg->dboptions['readonly'])) {
 216              $cfg->dboptions['readonly'] = [
 217                  'instance' => [$cfg->dbhost]
 218              ];
 219          }
 220  
 221          // Get a separate disposable db connection handle with guaranteed 'readonly' config.
 222          $db2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
 223          $db2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
 224  
 225          $dbman = $db2->get_manager();
 226  
 227          $table = new xmldb_table('silly_test_table');
 228          $table->add_field('id', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, XMLDB_SEQUENCE);
 229          $table->add_field('msg', XMLDB_TYPE_CHAR, 255);
 230          $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 231          $dbman->create_temp_table($table);
 232  
 233          // We need to go through the creation proces twice.
 234          // create_temp_table() performs some reads before the temp table is created.
 235          // First time around those reads should go to ro ...
 236          $reads = $db2->perf_get_reads_slave();
 237  
 238          $db2->get_columns('silly_test_table');
 239          $db2->get_records('silly_test_table');
 240          $this->assertEquals($reads, $db2->perf_get_reads_slave());
 241  
 242          $table2 = new xmldb_table('silly_test_table2');
 243          $table2->add_field('id', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, XMLDB_SEQUENCE);
 244          $table2->add_field('msg', XMLDB_TYPE_CHAR, 255);
 245          $table2->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
 246          $dbman->create_temp_table($table2);
 247  
 248          // ... but once the first temp table is created no more ro reads should occur.
 249          $db2->get_columns('silly_test_table2');
 250          $db2->get_records('silly_test_table2');
 251          $this->assertEquals($reads, $db2->perf_get_reads_slave());
 252  
 253          // Make database driver happy.
 254          $dbman->drop_table($table2);
 255          $dbman->drop_table($table);
 256      }
 257  
 258      /**
 259       * Test readonly connection failure with real pgsql connection
 260       *
 261       * @return void
 262       */
 263      public function test_real_readslave_connect_fail(): void {
 264          global $DB;
 265  
 266          if ($DB->get_dbfamily() != 'postgres') {
 267              $this->markTestSkipped('Not postgres');
 268          }
 269  
 270          // Open second connection.
 271          $cfg = $DB->export_dbconfig();
 272          if (!isset($cfg->dboptions)) {
 273              $cfg->dboptions = array();
 274          }
 275          $cfg->dboptions['readonly'] = [
 276              'instance' => ['host.that.is.not'],
 277              'connecttimeout' => 1
 278          ];
 279  
 280          $db2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
 281          $db2->connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);
 282          $this->assertTrue(count($db2->get_records('user')) > 0);
 283      }
 284  }