[Summary view]

Differences Between: [Versions 400 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  declare(strict_types=1);
  18  
  19  namespace core_reportbuilder\local\helpers;
  20  
  21  use advanced_testcase;
  22  use coding_exception;
  23  use core_user;
  24  
  25  /**
  26   * Unit tests for the database helper class
  27   *
  28   * @package     core_reportbuilder
  29   * @covers      \core_reportbuilder\local\helpers\database
  30   * @copyright   2020 Paul Holden <paulh@moodle.com>
  31   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  32   */
  33  class database_test extends advanced_testcase {
  34  
  35      /**
  36       * Test generating alias
  37       */
  38      public function test_generate_alias(): void {
  39          $this->assertMatchesRegularExpression('/^rbalias(\d+)$/', database::generate_alias());
  40      }
  41  
  42      /**
  43       * Test generating multiple aliases
  44       */
  45      public function test_generate_aliases(): void {
  46          $aliases = database::generate_aliases(3);
  47  
  48          $this->assertCount(3, $aliases);
  49          [$aliasone, $aliastwo, $aliasthree] = $aliases;
  50  
  51          // Ensure they are different.
  52          $this->assertNotEquals($aliasone, $aliastwo);
  53          $this->assertNotEquals($aliasone, $aliasthree);
  54          $this->assertNotEquals($aliastwo, $aliasthree);
  55      }
  56  
  57      /**
  58       * Test generating parameter name
  59       */
  60      public function test_generate_param_name(): void {
  61          $this->assertMatchesRegularExpression('/^rbparam(\d+)$/', database::generate_param_name());
  62      }
  63  
  64      /**
  65       * Test generating multiple parameter names
  66       */
  67      public function test_generate_param_names(): void {
  68          $params = database::generate_param_names(3);
  69  
  70          $this->assertCount(3, $params);
  71          [$paramone, $paramtwo, $paramthree] = $params;
  72  
  73          // Ensure they are different.
  74          $this->assertNotEquals($paramone, $paramtwo);
  75          $this->assertNotEquals($paramone, $paramthree);
  76          $this->assertNotEquals($paramtwo, $paramthree);
  77      }
  78  
  79      /**
  80       * Test parameter validation
  81       */
  82      public function test_validate_params(): void {
  83          [$paramone, $paramtwo] = database::generate_param_names(2);
  84  
  85          $params = [
  86              $paramone => 1,
  87              $paramtwo => 2,
  88          ];
  89  
  90          $this->assertTrue(database::validate_params($params));
  91      }
  92  
  93      /**
  94       * Test parameter validation for invalid parameters
  95       */
  96      public function test_validate_params_invalid(): void {
  97          $params = [
  98              database::generate_param_name() => 1,
  99              'invalidfoo' => 2,
 100              'invalidbar' => 4,
 101          ];
 102  
 103          $this->expectException(coding_exception::class);
 104          $this->expectExceptionMessage('Invalid parameter names (invalidfoo, invalidbar)');
 105          database::validate_params($params);
 106      }
 107  
 108      /**
 109       * Generate aliases and parameters and confirm they can be used within a query
 110       */
 111      public function test_generated_data_in_query(): void {
 112          global $DB;
 113  
 114          // Unique aliases.
 115          [
 116              $usertablealias,
 117              $userfieldalias,
 118          ] = database::generate_aliases(2);
 119  
 120          // Unique parameters.
 121          [
 122              $paramuserid,
 123              $paramuserdeleted,
 124          ] = database::generate_param_names(2);
 125  
 126          // Simple query to retrieve the admin user.
 127          $sql = "SELECT {$usertablealias}.id AS {$userfieldalias}
 128                    FROM {user} {$usertablealias}
 129                   WHERE {$usertablealias}.id = :{$paramuserid}
 130                     AND {$usertablealias}.deleted = :{$paramuserdeleted}";
 131  
 132          $admin = core_user::get_user_by_username('admin');
 133  
 134          $params = [
 135              $paramuserid => $admin->id,
 136              $paramuserdeleted => 0,
 137          ];
 138  
 139          $record = $DB->get_record_sql($sql, $params);
 140          $this->assertEquals($admin->id, $record->{$userfieldalias});
 141      }
 142  
 143      /**
 144       * Test replacement of parameter names within SQL statements
 145       */
 146      public function test_sql_replace_parameter_names(): void {
 147          global $DB;
 148  
 149          // Predefine parameter names, to ensure they don't overwrite each other.
 150          [$param0, $param1, $param10] = ['rbparam0', 'rbparam1', 'rbparam10'];
 151  
 152          $sql = "SELECT :{$param0} AS field0, :{$param1} AS field1, :{$param10} AS field10" . $DB->sql_null_from_clause();
 153          $sql = database::sql_replace_parameter_names($sql, [$param0, $param1, $param10], static function(string $param): string {
 154              return "prefix_{$param}";
 155          });
 156  
 157          $record = $DB->get_record_sql($sql, [
 158              "prefix_{$param0}" => 'Zero',
 159              "prefix_{$param1}" => 'One',
 160              "prefix_{$param10}" => 'Ten',
 161          ]);
 162  
 163          $this->assertEquals((object) [
 164              'field0' => 'Zero',
 165              'field1' => 'One',
 166              'field10' => 'Ten',
 167          ], $record);
 168      }
 169  }