[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 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  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 table alias and parameter names
  37       */
  38      public function test_generate_alias_params(): void {
  39          global $DB;
  40  
  41          $admin = core_user::get_user_by_username('admin');
  42  
  43          $usertablealias = database::generate_alias();
  44          $usertablealiasjoin = database::generate_alias();
  45          $useridalias = database::generate_alias();
  46  
  47          $paramuserid = database::generate_param_name();
  48          $paramuserdeleted = database::generate_param_name();
  49  
  50          // Ensure they are different.
  51          $this->assertNotEquals($usertablealias, $usertablealiasjoin);
  52          $this->assertNotEquals($paramuserid, $paramuserdeleted);
  53  
  54          $sql = "SELECT {$usertablealias}.id AS {$useridalias}
  55                    FROM {user} {$usertablealias}
  56                    JOIN {user} {$usertablealiasjoin} ON {$usertablealiasjoin}.id = {$usertablealias}.id
  57                   WHERE {$usertablealias}.id = :{$paramuserid} AND {$usertablealias}.deleted = :{$paramuserdeleted}";
  58          $params = [$paramuserid => $admin->id, $paramuserdeleted => 0];
  59  
  60          $validated = database::validate_params($params);
  61          $this->assertTrue($validated);
  62  
  63          $record = $DB->get_record_sql($sql, $params);
  64          $this->assertEquals($admin->id, $record->{$useridalias});
  65      }
  66  
  67      /**
  68       * Test parameter validation
  69       */
  70      public function test_validate_params(): void {
  71          $params = [
  72              database::generate_param_name() => 1,
  73              'invalidfoo' => 2,
  74              'invalidbar' => 4,
  75          ];
  76  
  77          $this->expectException(coding_exception::class);
  78          $this->expectExceptionMessage('Invalid parameter names (invalidfoo, invalidbar)');
  79          database::validate_params($params);
  80      }
  81  
  82      /**
  83       * Test replacement of parameter names within SQL statements
  84       */
  85      public function test_sql_replace_parameter_names(): void {
  86          global $DB;
  87  
  88          // Predefine parameter names, to ensure they don't overwrite each other.
  89          [$param0, $param1, $param10] = ['rbparam0', 'rbparam1', 'rbparam10'];
  90  
  91          $sql = "SELECT :{$param0} AS field0, :{$param1} AS field1, :{$param10} AS field10" . $DB->sql_null_from_clause();
  92          $sql = database::sql_replace_parameter_names($sql, [$param0, $param1, $param10], static function(string $param): string {
  93              return "prefix_{$param}";
  94          });
  95  
  96          $record = $DB->get_record_sql($sql, [
  97              "prefix_{$param0}" => 'Zero',
  98              "prefix_{$param1}" => 'One',
  99              "prefix_{$param10}" => 'Ten',
 100          ]);
 101  
 102          $this->assertEquals((object) [
 103              'field0' => 'Zero',
 104              'field1' => 'One',
 105              'field10' => 'Ten',
 106          ], $record);
 107      }
 108  }