test/classes/ImportTest.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Test for PhpMyAdmin\Import
   5  *
   6  * @package PhpMyAdmin-test
   7  */
   8 declare(strict_types=1);
   9
  10 namespace PhpMyAdmin\Tests;
  11
  12 use PhpMyAdmin\Import;
  13 use PhpMyAdmin\SqlParser\Parser;
  14 use PhpMyAdmin\Url;
  15 use PhpMyAdmin\Util;
  16 use PHPUnit\Framework\TestCase;
  17
  18 /**
  19  * Tests for import functions
  20  *
  21  * @package PhpMyAdmin-test
  22  */
  23 class ImportTest extends TestCase
  24 {
  25     /**
  26      * @var Import $import
  27      */
  28     private $import;
  29
  30     /**
  31      * Prepares environment for the test.
  32      *
  33      * @return void
  34      */
  35     protected function setUp(): void
  36     {
  37         $GLOBALS['server'] = 0;
  38         $GLOBALS['cfg']['ServerDefault'] = '';
  39         $this->import = new Import();
  40     }
  41
  42     /**
  43      * Test for checkTimeout
  44      *
  45      * @return void
  46      */
  47     public function testCheckTimeout()
  48     {
  49         global $timestamp, $maximum_time, $timeout_passed;
  50
  51         //Reinit values.
  52         $timestamp = time();
  53         $maximum_time = 0;
  54         $timeout_passed = false;
  55
  56         $this->assertFalse($this->import->checkTimeout());
  57
  58         //Reinit values.
  59         $timestamp = time();
  60         $maximum_time = 0;
  61         $timeout_passed = true;
  62
  63         $this->assertFalse($this->import->checkTimeout());
  64
  65         //Reinit values.
  66         $timestamp = time();
  67         $maximum_time = 30;
  68         $timeout_passed = true;
  69
  70         $this->assertTrue($this->import->checkTimeout());
  71
  72         //Reinit values.
  73         $timestamp = time() - 15;
  74         $maximum_time = 30;
  75         $timeout_passed = false;
  76
  77         $this->assertFalse($this->import->checkTimeout());
  78
  79         //Reinit values.
  80         $timestamp = time() - 60;
  81         $maximum_time = 30;
  82         $timeout_passed = false;
  83
  84         $this->assertTrue($this->import->checkTimeout());
  85     }
  86
  87     /**
  88      * Test for lookForUse
  89      *
  90      * @return void
  91      */
  92     public function testLookForUse()
  93     {
  94         $this->assertEquals(
  95             [
  96                 null,
  97                 null,
  98             ],
  99             $this->import->lookForUse(null, null, null)
 100         );
 101
 102         $this->assertEquals(
 103             [
 104                 'myDb',
 105                 null,
 106             ],
 107             $this->import->lookForUse(null, 'myDb', null)
 108         );
 109
 110         $this->assertEquals(
 111             [
 112                 'myDb',
 113                 true,
 114             ],
 115             $this->import->lookForUse(null, 'myDb', true)
 116         );
 117
 118         $this->assertEquals(
 119             [
 120                 'myDb',
 121                 true,
 122             ],
 123             $this->import->lookForUse('select 1 from myTable', 'myDb', true)
 124         );
 125
 126         $this->assertEquals(
 127             [
 128                 'anotherDb',
 129                 true,
 130             ],
 131             $this->import->lookForUse('use anotherDb', 'myDb', false)
 132         );
 133
 134         $this->assertEquals(
 135             [
 136                 'anotherDb',
 137                 true,
 138             ],
 139             $this->import->lookForUse('use anotherDb', 'myDb', true)
 140         );
 141
 142         $this->assertEquals(
 143             [
 144                 'anotherDb',
 145                 true,
 146             ],
 147             $this->import->lookForUse('use `anotherDb`;', 'myDb', true)
 148         );
 149     }
 150
 151     /**
 152      * Test for getColumnAlphaName
 153      *
 154      * @param string $expected Expected result of the function
 155      * @param int    $num      The column number
 156      *
 157      * @return void
 158      *
 159      * @dataProvider provGetColumnAlphaName
 160      */
 161     public function testGetColumnAlphaName($expected, $num): void
 162     {
 163         $this->assertEquals($expected, $this->import->getColumnAlphaName($num));
 164     }
 165
 166     /**
 167      * Data provider for testGetColumnAlphaName
 168      *
 169      * @return array
 170      */
 171     public function provGetColumnAlphaName()
 172     {
 173         return [
 174             [
 175                 'A',
 176                 1,
 177             ],
 178             [
 179                 'Z',
 180                 0,
 181             ],
 182             [
 183                 'AA',
 184                 27,
 185             ],
 186             [
 187                 'AZ',
 188                 52,
 189             ],
 190             [
 191                 'BA',
 192                 53,
 193             ],
 194             [
 195                 'BB',
 196                 54,
 197             ],
 198         ];
 199     }
 200
 201     /**
 202      * Test for getColumnNumberFromName
 203      *
 204      * @param int         $expected Expected result of the function
 205      * @param string|null $name     column name(i.e. "A", or "BC", etc.)
 206      *
 207      * @return void
 208      *
 209      * @dataProvider provGetColumnNumberFromName
 210      */
 211     public function testGetColumnNumberFromName($expected, $name): void
 212     {
 213         $this->assertEquals($expected, $this->import->getColumnNumberFromName($name));
 214     }
 215
 216     /**
 217      * Data provider for testGetColumnNumberFromName
 218      *
 219      * @return array
 220      */
 221     public function provGetColumnNumberFromName()
 222     {
 223         return [
 224             [
 225                 1,
 226                 'A',
 227             ],
 228             [
 229                 26,
 230                 'Z',
 231             ],
 232             [
 233                 27,
 234                 'AA',
 235             ],
 236             [
 237                 52,
 238                 'AZ',
 239             ],
 240             [
 241                 53,
 242                 'BA',
 243             ],
 244             [
 245                 54,
 246                 'BB',
 247             ],
 248         ];
 249     }
 250
 251     /**
 252      * Test for getDecimalPrecision
 253      *
 254      * @param int         $expected Expected result of the function
 255      * @param string|null $size     Size of field
 256      *
 257      * @return void
 258      *
 259      * @dataProvider provGetDecimalPrecision
 260      */
 261     public function testGetDecimalPrecision($expected, $size): void
 262     {
 263         $this->assertEquals($expected, $this->import->getDecimalPrecision($size));
 264     }
 265
 266     /**
 267      * Data provider for testGetDecimalPrecision
 268      *
 269      * @return array
 270      */
 271     public function provGetDecimalPrecision()
 272     {
 273         return [
 274             [
 275                 2,
 276                 '2,1',
 277             ],
 278             [
 279                 6,
 280                 '6,2',
 281             ],
 282             [
 283                 6,
 284                 '6,0',
 285             ],
 286             [
 287                 16,
 288                 '16,2',
 289             ],
 290         ];
 291     }
 292
 293     /**
 294      * Test for getDecimalScale
 295      *
 296      * @param int         $expected Expected result of the function
 297      * @param string|null $size     Size of field
 298      *
 299      * @return void
 300      *
 301      * @dataProvider provGetDecimalScale
 302      */
 303     public function testGetDecimalScale($expected, $size): void
 304     {
 305         $this->assertEquals($expected, $this->import->getDecimalScale($size));
 306     }
 307
 308     /**
 309      * Data provider for testGetDecimalScale
 310      *
 311      * @return array
 312      */
 313     public function provGetDecimalScale()
 314     {
 315         return [
 316             [
 317                 1,
 318                 '2,1',
 319             ],
 320             [
 321                 2,
 322                 '6,2',
 323             ],
 324             [
 325                 0,
 326                 '6,0',
 327             ],
 328             [
 329                 20,
 330                 '30,20',
 331             ],
 332         ];
 333     }
 334
 335     /**
 336      * Test for getDecimalSize
 337      *
 338      * @param array       $expected Expected result of the function
 339      * @param string|null $cell     Cell content
 340      *
 341      * @return void
 342      *
 343      * @dataProvider provGetDecimalSize
 344      */
 345     public function testGetDecimalSize($expected, $cell): void
 346     {
 347         $this->assertEquals($expected, $this->import->getDecimalSize($cell));
 348     }
 349
 350     /**
 351      * Data provider for testGetDecimalSize
 352      *
 353      * @return array
 354      */
 355     public function provGetDecimalSize()
 356     {
 357         return [
 358             [
 359                 [
 360                     2,
 361                     1,
 362                     '2,1',
 363                 ], '2.1',
 364             ],
 365             [
 366                 [
 367                     2,
 368                     1,
 369                     '2,1',
 370                 ], '6.2',
 371             ],
 372             [
 373                 [
 374                     3,
 375                     1,
 376                     '3,1',
 377                 ], '10.0',
 378             ],
 379             [
 380                 [
 381                     4,
 382                     2,
 383                     '4,2',
 384                 ], '30.20',
 385             ],
 386         ];
 387     }
 388
 389     /**
 390      * Test for detectType
 391      *
 392      * @param int         $expected Expected result of the function
 393      * @param int|null    $type     Last cumulative column type (VARCHAR or INT or
 394      *                              BIGINT or DECIMAL or NONE)
 395      * @param string|null $cell     String representation of the cell for which a
 396      *                              best-fit type is to be determined
 397      *
 398      * @return void
 399      *
 400      * @dataProvider provDetectType
 401      */
 402     public function testDetectType($expected, $type, $cell): void
 403     {
 404         $this->assertEquals($expected, $this->import->detectType($type, $cell));
 405     }
 406
 407     /**
 408      * Data provider for testDetectType
 409      *
 410      * @return array
 411      */
 412     public function provDetectType()
 413     {
 414         $data = [
 415             [
 416                 Import::NONE,
 417                 null,
 418                 'NULL',
 419             ],
 420             [
 421                 Import::NONE,
 422                 Import::NONE,
 423                 'NULL',
 424             ],
 425             [
 426                 Import::INT,
 427                 Import::INT,
 428                 'NULL',
 429             ],
 430             [
 431                 Import::VARCHAR,
 432                 Import::VARCHAR,
 433                 'NULL',
 434             ],
 435             [
 436                 Import::VARCHAR,
 437                 null,
 438                 null,
 439             ],
 440             [
 441                 Import::VARCHAR,
 442                 Import::INT,
 443                 null,
 444             ],
 445             [
 446                 Import::INT,
 447                 Import::INT,
 448                 '10',
 449             ],
 450             [
 451                 Import::DECIMAL,
 452                 Import::DECIMAL,
 453                 '10.2',
 454             ],
 455             [
 456                 Import::DECIMAL,
 457                 Import::INT,
 458                 '10.2',
 459             ],
 460             [
 461                 Import::VARCHAR,
 462                 Import::VARCHAR,
 463                 'test',
 464             ],
 465             [
 466                 Import::VARCHAR,
 467                 Import::INT,
 468                 'test',
 469             ],
 470         ];
 471
 472         if (PHP_INT_MAX > 2147483647) {
 473             $data[] = [
 474                 Import::BIGINT,
 475                 Import::BIGINT,
 476                 '2147483648',
 477             ];
 478             $data[] = [
 479                 Import::BIGINT,
 480                 Import::INT,
 481                 '2147483648',
 482             ];
 483         } else {
 484             // To be fixed ?
 485             // Can not detect a BIGINT since the value is over PHP_INT_MAX
 486             $data[] = [
 487                 Import::VARCHAR,
 488                 Import::BIGINT,
 489                 '2147483648',
 490             ];
 491             $data[] = [
 492                 Import::VARCHAR,
 493                 Import::INT,
 494                 '2147483648',
 495             ];
 496         }
 497
 498         return $data;
 499     }
 500
 501     /**
 502      * Test for getMatchedRows.
 503      *
 504      * @return void
 505      */
 506     public function testPMAGetMatchedRows()
 507     {
 508         $GLOBALS['db'] = 'PMA';
 509         //mock DBI
 510         $dbi = $this->getMockBuilder('PhpMyAdmin\DatabaseInterface')
 511             ->disableOriginalConstructor()
 512             ->getMock();
 513
 514         $update_query = 'UPDATE `table_1` '
 515             . 'SET `id` = 20 '
 516             . 'WHERE `id` > 10';
 517         $simulated_update_query = 'SELECT `id` FROM `table_1` WHERE `id` > 10 AND (`id` <> 20)';
 518
 519         $delete_query = 'DELETE FROM `table_1` '
 520             . 'WHERE `id` > 10';
 521         $simulated_delete_query = 'SELECT * FROM `table_1` WHERE `id` > 10';
 522
 523         $dbi->expects($this->any())
 524             ->method('numRows')
 525             ->with([])
 526             ->will($this->returnValue(2));
 527
 528         $dbi->expects($this->any())
 529             ->method('selectDb')
 530             ->with('PMA')
 531             ->will($this->returnValue(true));
 532
 533         $dbi->expects($this->at(1))
 534             ->method('tryQuery')
 535             ->with($simulated_update_query)
 536             ->will($this->returnValue([]));
 537
 538         $dbi->expects($this->at(4))
 539             ->method('tryQuery')
 540             ->with($simulated_delete_query)
 541             ->will($this->returnValue([]));
 542
 543         $GLOBALS['dbi'] = $dbi;
 544
 545         $this->simulatedQueryTest($update_query, $simulated_update_query);
 546         $this->simulatedQueryTest($delete_query, $simulated_delete_query);
 547     }
 548
 549     /**
 550      * Tests simulated UPDATE/DELETE query.
 551      *
 552      * @param string $sql_query       SQL query
 553      * @param string $simulated_query Simulated query
 554      *
 555      * @return void
 556      */
 557     public function simulatedQueryTest($sql_query, $simulated_query)
 558     {
 559         $parser = new Parser($sql_query);
 560         $analyzed_sql_results = [
 561             'query' => $sql_query,
 562             'parser' => $parser,
 563             'statement' => $parser->statements[0],
 564         ];
 565
 566         $simulated_data = $this->import->getMatchedRows($analyzed_sql_results);
 567
 568         // URL to matched rows.
 569         $_url_params = [
 570             'db'        => 'PMA',
 571             'sql_query' => $simulated_query,
 572         ];
 573         $matched_rows_url  = 'sql.php' . Url::getCommon($_url_params);
 574
 575         $this->assertEquals(
 576             [
 577                 'sql_query' => Util::formatSql(
 578                     $analyzed_sql_results['query']
 579                 ),
 580                 'matched_rows' => 2,
 581                 'matched_rows_url' => $matched_rows_url,
 582             ],
 583             $simulated_data
 584         );
 585     }
 586
 587     /**
 588      * Test for checkIfRollbackPossible
 589      *
 590      * @return void
 591      */
 592     public function testPMACheckIfRollbackPossible()
 593     {
 594         $GLOBALS['db'] = 'PMA';
 595         //mock DBI
 596         $dbi = $this->getMockBuilder('PhpMyAdmin\DatabaseInterface')
 597             ->disableOriginalConstructor()
 598             ->getMock();
 599
 600         // List of Transactional Engines.
 601         $transactional_engines = [
 602             'INNODB',
 603             'FALCON',
 604             'NDB',
 605             'INFINIDB',
 606             'TOKUDB',
 607             'XTRADB',
 608             'SEQUENCE',
 609             'BDB',
 610         ];
 611
 612         $check_query = 'SELECT `ENGINE` FROM `information_schema`.`tables` '
 613             . 'WHERE `table_name` = "%s" '
 614             . 'AND `table_schema` = "%s" '
 615             . 'AND UPPER(`engine`) IN ("'
 616             . implode('", "', $transactional_engines)
 617             . '")';
 618
 619         $check_table_query = 'SELECT * FROM `%s`.`%s` '
 620             . 'LIMIT 1';
 621
 622         $dbi->expects($this->at(0))
 623             ->method('tryQuery')
 624             ->with(sprintf($check_table_query, 'PMA', 'table_1'))
 625             ->will($this->returnValue(['table']));
 626
 627         $dbi->expects($this->at(1))
 628             ->method('tryQuery')
 629             ->with(sprintf($check_query, 'table_1', 'PMA'))
 630             ->will($this->returnValue(true));
 631
 632         $dbi->expects($this->at(2))
 633             ->method('numRows')
 634             ->will($this->returnValue(1));
 635
 636         $dbi->expects($this->at(3))
 637             ->method('tryQuery')
 638             ->with(sprintf($check_table_query, 'PMA', 'table_2'))
 639             ->will($this->returnValue(['table']));
 640
 641         $dbi->expects($this->at(4))
 642             ->method('tryQuery')
 643             ->with(sprintf($check_query, 'table_2', 'PMA'))
 644             ->will($this->returnValue(true));
 645
 646         $dbi->expects($this->at(5))
 647             ->method('numRows')
 648             ->will($this->returnValue(1));
 649
 650         $GLOBALS['dbi'] = $dbi;
 651
 652         $sql_query = 'UPDATE `table_1` AS t1, `table_2` t2 '
 653             . 'SET `table_1`.`id` = `table_2`.`id` '
 654             . 'WHERE 1';
 655
 656         $this->assertEquals(true, $this->import->checkIfRollbackPossible($sql_query));
 657     }
 658 }