[Summary view]

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