[Summary view]

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

   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 sqlsrv dml support.
  19   *
  20   * @package    core
  21   * @category   dml
  22   * @copyright  2017 John Okely
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  global $CFG;
  29  require_once($CFG->dirroot.'/lib/dml/sqlsrv_native_moodle_database.php');
  30  
  31  /**
  32   * Test case for sqlsrv dml support.
  33   *
  34   * @package    core
  35   * @category   dml
  36   * @copyright  2017 John Okely
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  class sqlsrv_native_moodle_database_testcase extends advanced_testcase {
  40  
  41      public function setUp(): void {
  42          parent::setUp();
  43          $this->resetAfterTest();
  44      }
  45  
  46      /**
  47       * Dataprovider for test_add_no_lock_to_temp_tables
  48       * @return array Data for test_add_no_lock_to_temp_tables
  49       */
  50      public function add_no_lock_to_temp_tables_provider() {
  51          return [
  52              "Basic temp table, nothing following" => [
  53                  'input' => 'SELECT * FROM {table_temp}',
  54                  'expected' => 'SELECT * FROM {table_temp} WITH (NOLOCK)'
  55              ],
  56              "Basic temp table, with capitalised alias" => [
  57                  'input' => 'SELECT * FROM {table_temp} MYTABLE',
  58                  'expected' => 'SELECT * FROM {table_temp} MYTABLE WITH (NOLOCK)'
  59              ],
  60              "Temp table with alias, and another non-temp table" => [
  61                  'input' => 'SELECT * FROM {table_temp} x WHERE y in (SELECT y from {table2})',
  62                  'expected' => 'SELECT * FROM {table_temp} x WITH (NOLOCK) WHERE y in (SELECT y from {table2})'
  63              ],
  64              "Temp table with reserve word following, no alias" => [
  65                  'input' => 'SELECT DISTINCT * FROM {table_temp} WHERE y in (SELECT y from {table2} nottemp)',
  66                  'expected' => 'SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK) WHERE y in (SELECT y from {table2} nottemp)'
  67              ],
  68              "Temp table with reserve word, lower case" => [
  69                  'input' => 'SELECT DISTINCT * FROM {table_temp} where y in (SELECT y from {table2} nottemp)',
  70                  'expected' => 'SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK) where y in (SELECT y from {table2} nottemp)'
  71              ],
  72              "Another reserve word test" => [
  73                  'input' => 'SELECT DISTINCT * FROM {table_temp} PIVOT y in (SELECT y from {table2} nottemp)',
  74                  'expected' => 'SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK) PIVOT y in (SELECT y from {table2} nottemp)'
  75              ],
  76              "Another reserve word test should fail" => [
  77                  'input' => 'SELECT DISTINCT * FROM {table_temp} PIVOT y in (SELECT y from {table2} nottemp)',
  78                  'expected' => 'SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK) PIVOT y in (SELECT y from {table2} nottemp)'
  79              ],
  80              "Temp table with an alias starting with a keyword" => [
  81                  'input' => 'SELECT * FROM {table_temp} asx',
  82                  'expected' => 'SELECT * FROM {table_temp} asx WITH (NOLOCK)'
  83              ],
  84              "Keep alias with underscore" => [
  85                  'input' => 'SELECT * FROM {table_temp} alias_for_table',
  86                  'expected' => 'SELECT * FROM {table_temp} alias_for_table WITH (NOLOCK)'
  87              ],
  88              "Alias with number" => [
  89                  'input' => 'SELECT * FROM {table_temp} a5 WHERE y',
  90                  'expected' => 'SELECT * FROM {table_temp} a5 WITH (NOLOCK) WHERE y'
  91              ],
  92              "Alias with number and underscore" => [
  93                  'input' => 'SELECT * FROM {table_temp} a_5 WHERE y',
  94                  'expected' => 'SELECT * FROM {table_temp} a_5 WITH (NOLOCK) WHERE y'
  95              ],
  96              "Temp table in subquery" => [
  97                  'input' => 'select * FROM (SELECT DISTINCT * FROM {table_temp})',
  98                  'expected' => 'select * FROM (SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK))'
  99              ],
 100              "Temp table in subquery, with following commands" => [
 101                  'input' => 'select * FROM (SELECT DISTINCT * FROM {table_temp} ) WHERE y',
 102                  'expected' => 'select * FROM (SELECT DISTINCT * FROM {table_temp} WITH (NOLOCK) ) WHERE y'
 103              ],
 104              "Temp table in subquery, with alias" => [
 105                  'input' => 'select * FROM (SELECT DISTINCT * FROM {table_temp} x) WHERE y',
 106                  'expected' => 'select * FROM (SELECT DISTINCT * FROM {table_temp} x WITH (NOLOCK)) WHERE y'
 107              ],
 108          ];
 109      }
 110  
 111      /**
 112       * Test add_no_lock_to_temp_tables
 113       *
 114       * @param string $input The input SQL query
 115       * @param string $expected The expected resultant query
 116       * @dataProvider add_no_lock_to_temp_tables_provider
 117       */
 118      public function test_add_no_lock_to_temp_tables($input, $expected) {
 119          $sqlsrv = new sqlsrv_native_moodle_database();
 120  
 121          $reflector = new ReflectionObject($sqlsrv);
 122  
 123          $method = $reflector->getMethod('add_no_lock_to_temp_tables');
 124          $method->setAccessible(true);
 125  
 126          $temptablesproperty = $reflector->getProperty('temptables');
 127          $temptablesproperty->setAccessible(true);
 128          $temptables = new temptables_tester();
 129  
 130          $temptablesproperty->setValue($sqlsrv, $temptables);
 131  
 132          $result = $method->invoke($sqlsrv, $input);
 133  
 134          $temptablesproperty->setValue($sqlsrv, null);
 135          $this->assertEquals($expected, $result);
 136      }
 137  
 138      /**
 139       * Data provider for test_has_query_order_by
 140       *
 141       * @return array data for test_has_query_order_by
 142       */
 143      public function has_query_order_by_provider() {
 144          // Fixtures taken from https://docs.moodle.org/en/ad-hoc_contributed_reports.
 145  
 146          return [
 147              'User with language => FALSE' => [
 148                  'sql' => <<<EOT
 149  SELECT username, lang
 150    FROM prefix_user
 151  EOT
 152                  ,
 153                  'expectedmainquery' => <<<EOT
 154  SELECT username, lang
 155    FROM prefix_user
 156  EOT
 157                  ,
 158                  'expectedresult' => false
 159              ],
 160              'List Users with extra info (email) in current course => FALSE' => [
 161                  'sql' => <<<EOT
 162  SELECT u.firstname, u.lastname, u.email
 163    FROM prefix_role_assignments AS ra
 164    JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
 165    JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
 166    JOIN prefix_user AS u ON u.id = ra.userid
 167  EOT
 168                  ,
 169                  'expectedmainquery' => <<<EOT
 170  SELECT u.firstname, u.lastname, u.email
 171    FROM prefix_role_assignments AS ra
 172    JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
 173    JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
 174    JOIN prefix_user AS u ON u.id = ra.userid
 175  EOT
 176                  ,
 177                  'expectedresult' => false
 178              ],
 179              'ROW_NUMBER() OVER (ORDER BY ...) => FALSE (https://github.com/jleyva/moodle-block_configurablereports/issues/120)' => [
 180                  'sql' => <<<EOT
 181  SELECT COUNT(*) AS 'Users who have logged in today'
 182    FROM (
 183           SELECT ROW_NUMBER() OVER(ORDER BY lastaccess DESC) AS Row
 184             FROM mdl_user
 185            WHERE lastaccess > DATEDIFF(s, '1970-01-01 02:00:00', (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))))
 186         ) AS Logins
 187  EOT
 188                  ,
 189                  'expectedmainquery' => <<<EOT
 190  SELECT COUNT() AS 'Users who have logged in today'
 191    FROM () AS Logins
 192  EOT
 193                  ,
 194                  'expectedresult' => false
 195              ],
 196              'CONTRIB-7725 workaround) => TRUE' => [
 197                  'sql' => <<<EOT
 198  SELECT COUNT(*) AS 'Users who have logged in today'
 199    FROM (
 200           SELECT ROW_NUMBER() OVER(ORDER BY lastaccess DESC) AS Row
 201             FROM mdl_user
 202            WHERE lastaccess > DATEDIFF(s, '1970-01-01 02:00:00', (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))))
 203         ) AS Logins ORDER BY 1
 204  EOT
 205                  ,
 206                  'expectedmainquery' => <<<EOT
 207  SELECT COUNT() AS 'Users who have logged in today'
 208    FROM () AS Logins ORDER BY 1
 209  EOT
 210                  ,
 211                  'expectedresult' => true
 212              ],
 213              'Enrolment count in each Course => TRUE' => [
 214                  'sql' => <<<EOT
 215    SELECT c.fullname, COUNT(ue.id) AS Enroled
 216      FROM prefix_course AS c
 217      JOIN prefix_enrol AS en ON en.courseid = c.id
 218      JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 219  GROUP BY c.id
 220  ORDER BY c.fullname
 221  EOT
 222                  ,
 223                  'expectedmainquery' => <<<EOT
 224    SELECT c.fullname, COUNT() AS Enroled
 225      FROM prefix_course AS c
 226      JOIN prefix_enrol AS en ON en.courseid = c.id
 227      JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 228  GROUP BY c.id
 229  ORDER BY c.fullname
 230  EOT
 231                  ,
 232                  'expectedresult' => true
 233              ],
 234          ];
 235      }
 236  
 237      /**
 238       * Test has_query_order_by
 239       *
 240       * @dataProvider has_query_order_by_provider
 241       * @param string $sql the query
 242       * @param string $expectedmainquery the expected main query
 243       * @param bool $expectedresult the expected result
 244       */
 245      public function test_has_query_order_by(string $sql, string $expectedmainquery, bool $expectedresult) {
 246          $mainquery = preg_replace('/\(((?>[^()]+)|(?R))*\)/', '()', $sql);
 247          $this->assertSame($expectedmainquery, $mainquery);
 248  
 249          // The has_query_order_by static method is protected. Use Reflection to call the method.
 250          $method = new ReflectionMethod('sqlsrv_native_moodle_database', 'has_query_order_by');
 251          $method->setAccessible(true);
 252          $result = $method->invoke(null, $sql);
 253          $this->assertSame($expectedresult, $result);
 254      }
 255  }
 256  
 257  /**
 258   * Test class for testing temptables
 259   *
 260   * @copyright  2017 John Okely
 261   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 262   */
 263  class temptables_tester {
 264      /**
 265       * Returns if one table, based in the information present in the store, is a temp table
 266       *
 267       * For easy testing, anything with the word 'temp' in it is considered temporary.
 268       *
 269       * @param string $tablename name without prefix of the table we are asking about
 270       * @return bool true if the table is a temp table (based in the store info), false if not
 271       */
 272      public function is_temptable($tablename) {
 273          if (strpos($tablename, 'temp') === false) {
 274              return false;
 275          } else {
 276              return true;
 277          }
 278      }
 279      /**
 280       * Dispose the temptables
 281       *
 282       * @return void
 283       */
 284      public function dispose() {
 285      }
 286  }