tests/Galette/Core/tests/units/Db.php

   1 <?php
   2
   3 /**
   4  * Copyright © 2003-2024 The Galette Team
   5  *
   6  * This file is part of Galette (https://galette.eu).
   7  *
   8  * Galette is free software: you can redistribute it and/or modify
   9  * it under the terms of the GNU General Public License as published by
  10  * the Free Software Foundation, either version 3 of the License, or
  11  * (at your option) any later version.
  12  *
  13  * Galette is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  *  GNU General Public License for more details.
  17  *
  18  * You should have received a copy of the GNU General Public License
  19  * along with Galette. If not, see <http://www.gnu.org/licenses/>.
  20  */
  21
  22 namespace Galette\Core\test\units;
  23
  24 use PHPUnit\Framework\TestCase;
  25
  26 /**
  27  * Database tests class
  28  *
  29  * @author Johan Cwiklinski <johan@x-tnd.be>
  30  */
  31 class Db extends TestCase
  32 {
  33     private \Galette\Core\Db $db;
  34     private array $have_warnings = [];
  35
  36     /**
  37      * Set up tests
  38      *
  39      * @return void
  40      */
  41     public function setUp(): void
  42     {
  43         $this->db = new \Galette\Core\Db();
  44     }
  45
  46     /**
  47      * Tear down tests
  48      *
  49      * @return void
  50      */
  51     public function tearDown(): void
  52     {
  53         if (TYPE_DB === 'mysql') {
  54             $this->assertEquals($this->db->getWarnings(), $this->have_warnings);
  55         }
  56
  57         $this->db = new \Galette\Core\Db();
  58         $delete = $this->db->delete(\Galette\Entity\Title::TABLE);
  59         $delete->where([\Galette\Entity\Title::PK => '150']);
  60         $this->db->execute($delete);
  61     }
  62
  63     /**
  64      * Test constructor
  65      *
  66      * @return void
  67      */
  68     public function testConstructor()
  69     {
  70         $db = new \Galette\Core\Db();
  71         $type = $db->type_db;
  72         $this->assertSame(TYPE_DB, $type);
  73
  74         $dsn = array(
  75             'TYPE_DB'   => TYPE_DB,
  76             'USER_DB'   => USER_DB,
  77             'PWD_DB'    => PWD_DB,
  78             'HOST_DB'   => HOST_DB,
  79             'PORT_DB'   => PORT_DB,
  80             'NAME_DB'   => NAME_DB
  81         );
  82         $db = new \Galette\Core\Db($dsn);
  83
  84         $is_pg = $db->isPostgres();
  85         $type = $db->type_db;
  86
  87         switch (TYPE_DB) {
  88             case 'pgsql':
  89                 $this->assertTrue($is_pg);
  90                 $this->assertSame(\Galette\Core\Db::PGSQL, $type);
  91                 break;
  92             case \Galette\Core\Db::MYSQL:
  93                 $this->assertFalse($is_pg);
  94                 $this->assertSame(\Galette\Core\Db::MYSQL, $type);
  95                 break;
  96         }
  97
  98         $this->expectException(\Exception::class);
  99         $dsn['TYPE_DB'] = 'DOES_NOT_EXISTS';
 100         $db = new \Galette\Core\Db($dsn);
 101     }
 102
 103     /**
 104      * Test database connectivity
 105      *
 106      * @return void
 107      */
 108     public function testConnectivity()
 109     {
 110         $res = $this->db->testConnectivity(
 111             TYPE_DB,
 112             USER_DB,
 113             PWD_DB,
 114             HOST_DB,
 115             PORT_DB,
 116             NAME_DB
 117         );
 118         $this->assertTrue($res);
 119     }
 120
 121     /**
 122      * Test database grants
 123      *
 124      * @return void
 125      */
 126     public function testGrant()
 127     {
 128         $result = $this->db->dropTestTable();
 129
 130         $expected = array(
 131             'create' => true,
 132             'insert' => true,
 133             'select' => true,
 134             'update' => true,
 135             'delete' => true,
 136             'drop'   => true
 137         );
 138         $result = $this->db->grantCheck();
 139
 140         $this->assertSame($expected, $result);
 141
 142         //in update mode, we need alter
 143         $result = $this->db->grantCheck('u');
 144
 145         $expected['alter'] = true;
 146         $this->assertSame($result, $expected);
 147     }
 148
 149     /**
 150      * Test database grants that throws an exception
 151      *
 152      * @return void
 153      */
 154     public function testGrantWException()
 155     {
 156         $atoum = $this;
 157
 158         //test insert failing
 159         $this->db = $this->getMockBuilder(\Galette\Core\Db::class)
 160             ->onlyMethods(array('execute'))
 161             ->getMock();
 162
 163         $this->db->method('execute')
 164             ->will(
 165                 $this->returnCallback(
 166                     function ($o) {
 167                         if ($o instanceof \Laminas\Db\Sql\Insert) {
 168                             throw new \LogicException('Error executing query!', 123);
 169                         }
 170                     }
 171                 )
 172             );
 173
 174         $result = $this->db->grantCheck('u');
 175
 176         $this->assertTrue($result['create']);
 177         $this->assertTrue($result['alter']);
 178         $this->assertInstanceOf(\LogicException::class, $result['insert']);
 179         $this->assertFalse($result['update']);
 180         $this->assertFalse($result['select']);
 181         $this->assertFalse($result['delete']);
 182         $this->assertTrue($result['drop']);
 183
 184         //test select failing
 185         $this->db = $this->getMockBuilder(\Galette\Core\Db::class)
 186             ->onlyMethods(array('execute'))
 187             ->getMock();
 188
 189         $this->db->method('execute')
 190             ->will(
 191                 $this->returnCallback(
 192                     function ($o) {
 193                         if ($o instanceof \Laminas\Db\Sql\Select) {
 194                             throw new \LogicException('Error executing query!', 123);
 195                         } else {
 196                             $rs = $this->getMockBuilder(\Laminas\Db\ResultSet\ResultSet::class)
 197                                 ->onlyMethods(array('count'))
 198                                 ->getMock();
 199                             $rs->method('count')
 200                                 ->willReturn(1);
 201                             return $rs;
 202                         }
 203                     }
 204                 )
 205             );
 206
 207         $result = $this->db->grantCheck('u');
 208
 209         $this->assertTrue($result['create']);
 210         $this->assertTrue($result['alter']);
 211         $this->assertTrue($result['insert']);
 212         $this->assertTrue($result['update']);
 213         $this->assertInstanceOf(\LogicException::class, $result['select']);
 214         $this->assertTrue($result['delete']);
 215         $this->assertTrue($result['drop']);
 216
 217         //test update failing
 218         $this->db = $this->getMockBuilder(\Galette\Core\Db::class)
 219             ->onlyMethods(array('execute'))
 220             ->getMock();
 221
 222         $this->db->method('execute')
 223             ->will(
 224                 $this->returnCallback(
 225                     function ($o) {
 226                         if ($o instanceof \Laminas\Db\Sql\Update) {
 227                             throw new \LogicException('Error executing query!', 123);
 228                         } else {
 229                             $rs = $this->getMockBuilder(\Laminas\Db\ResultSet\ResultSet::class)
 230                                 ->onlyMethods(array('count'))
 231                                 ->getMock();
 232                             $rs->method('count')
 233                                 ->willReturn(1);
 234                             return $rs;
 235                         }
 236                     }
 237                 )
 238             );
 239
 240         $result = $this->db->grantCheck('u');
 241
 242         $this->assertTrue($result['create']);
 243         $this->assertTrue($result['alter']);
 244         $this->assertTrue($result['insert']);
 245         $this->assertInstanceOf(\LogicException::class, $result['update']);
 246         $this->assertTrue($result['select']);
 247         $this->assertTrue($result['delete']);
 248         $this->assertTrue($result['drop']);
 249
 250         //test delete failing
 251         $this->db = $this->getMockBuilder(\Galette\Core\Db::class)
 252             ->onlyMethods(array('execute'))
 253             ->getMock();
 254
 255         $this->db->method('execute')
 256             ->will(
 257                 $this->returnCallback(
 258                     function ($o) {
 259                         if ($o instanceof \Laminas\Db\Sql\Delete) {
 260                             throw new \LogicException('Error executing query!', 123);
 261                         } else {
 262                             $rs = $this->getMockBuilder(\Laminas\Db\ResultSet\ResultSet::class)
 263                                 ->onlyMethods(array('count'))
 264                                 ->getMock();
 265                             $rs->method('count')
 266                                 ->willReturn(1);
 267                             return $rs;
 268                         }
 269                     }
 270                 )
 271             );
 272
 273         $result = $this->db->grantCheck('u');
 274
 275         $this->assertTrue($result['create']);
 276         $this->assertTrue($result['alter']);
 277         $this->assertTrue($result['insert']);
 278         $this->assertTrue($result['update']);
 279         $this->assertTrue($result['select']);
 280         $this->assertInstanceOf(\LogicException::class, $result['delete']);
 281         $this->assertTrue($result['drop']);
 282     }
 283
 284     /**
 285      * Is database Postgresql powered?
 286      *
 287      * @return void
 288      */
 289     public function testIsPostgres()
 290     {
 291         $is_pg = $this->db->isPostgres();
 292
 293         switch (TYPE_DB) {
 294             case 'pgsql':
 295                 $this->assertTrue($is_pg);
 296                 break;
 297             default:
 298                 $this->assertFalse($is_pg);
 299                 break;
 300         }
 301     }
 302
 303     /**
 304      * Test getters
 305      *
 306      * @return void
 307      */
 308     public function testGetters()
 309     {
 310         switch (TYPE_DB) {
 311             case 'pgsql':
 312                 $type = $this->db->type_db;
 313                 $this->assertSame('pgsql', $type);
 314                 break;
 315             case 'mysql':
 316                 $type = $this->db->type_db;
 317                 $this->assertSame('mysql', $type);
 318                 break;
 319         }
 320
 321         $db = $this->db->db;
 322         $this->assertInstanceOf('Laminas\Db\Adapter\Adapter', $db);
 323
 324         $sql = $this->db->sql;
 325         $this->assertInstanceOf('Laminas\Db\Sql\Sql', $sql);
 326
 327         $connection = $this->db->connection;
 328         $this->assertInstanceOf('Laminas\Db\Adapter\Driver\Pdo\Connection', $connection);
 329
 330         $driver = $this->db->driver;
 331         $this->assertInstanceOf('Laminas\Db\Adapter\Driver\Pdo\Pdo', $driver);
 332     }
 333
 334     /**
 335      * Test select
 336      *
 337      * @return void
 338      */
 339     public function testSelect()
 340     {
 341         $select = $this->db->select('preferences', 'p');
 342         $select->where(array('p.nom_pref' => 'pref_nom'));
 343
 344         $results = $this->db->execute($select);
 345
 346         $query = $this->db->query_string;
 347
 348         $expected = 'SELECT "p".* FROM "galette_preferences" AS "p" ' .
 349             'WHERE "p"."nom_pref" = \'pref_nom\'';
 350
 351         if (TYPE_DB === 'mysql') {
 352             $expected = 'SELECT `p`.* FROM `galette_preferences` AS `p` ' .
 353                 'WHERE `p`.`nom_pref` = \'pref_nom\'';
 354         }
 355
 356         $this->assertSame($expected, $query);
 357     }
 358
 359     /**
 360      * Test selectAll
 361      *
 362      * @return void
 363      */
 364     public function testSelectAll()
 365     {
 366         $all = $this->db->selectAll('preferences');
 367         $this->assertInstanceOf('Laminas\Db\ResultSet\ResultSet', $all);
 368     }
 369
 370     /**
 371      * Test insert
 372      *
 373      * @return void
 374      */
 375     public function testInsert()
 376     {
 377         $insert = $this->db->insert('titles');
 378         $data = [
 379             'id_title'      => '150',
 380             'short_label'   => 'Dr',
 381             'long_label'    => 'Doctor'
 382         ];
 383         $insert->values($data);
 384         $res = $this->db->execute($insert);
 385
 386         $select = $this->db->select('titles', 't');
 387         $select->where(['t.id_title' => $data['id_title']]);
 388
 389         $results = $this->db->execute($select);
 390         $this->assertSame(1, $results->count());
 391
 392         if (TYPE_DB === 'pgsql') {
 393             $data['id_title'] = (int)$data['id_title'];
 394         }
 395         $this->assertEquals((array)$results->current(), $data);
 396     }
 397
 398     /**
 399      * Test update
 400      *
 401      * @return void
 402      */
 403     public function testUpdate()
 404     {
 405         $insert = $this->db->insert('titles');
 406         $data = [
 407             'id_title'      => '150',
 408             'short_label'   => 'Dr',
 409             'long_label'    => 'Doctor'
 410         ];
 411         $insert->values($data);
 412         $res = $this->db->execute($insert);
 413
 414         $update = $this->db->update('titles');
 415         $data = [
 416             'long_label'    => 'DoctorS'
 417         ];
 418         $where = ['id_title' => 150];
 419
 420         $select = $this->db->select('titles', 't');
 421         $select->columns(['long_label']);
 422         $select->where($where);
 423         $results = $this->db->execute($select);
 424
 425         $long_label = $results->current()->long_label;
 426         $this->assertSame('Doctor', $long_label);
 427
 428         $update->set($data);
 429         $update->where($where);
 430         $res = $this->db->execute($update);
 431         $this->assertSame(1, $res->count());
 432
 433         $results = $this->db->execute($select);
 434         $this->assertSame(1, $results->count());
 435
 436         $long_label = $results->current()->long_label;
 437         $this->assertSame('DoctorS', $long_label);
 438     }
 439
 440     /**
 441      * Test delete
 442      *
 443      * @return void
 444      */
 445     public function testDelete()
 446     {
 447         $insert = $this->db->insert('titles');
 448         $data = [
 449             'id_title'      => '150',
 450             'short_label'   => 'Dr',
 451             'long_label'    => 'Doctor'
 452         ];
 453         $insert->values($data);
 454         $res = $this->db->execute($insert);
 455
 456         $delete = $this->db->delete('titles');
 457         $where = ['id_title' => 150];
 458
 459         $select = $this->db->select('titles', 't');
 460         $select->where($where);
 461         $results = $this->db->execute($select);
 462         $this->assertSame(1, $results->count());
 463
 464         $delete->where($where);
 465         $res = $this->db->execute($delete);
 466         $this->assertSame(1, $res->count());
 467
 468         $results = $this->db->execute($select);
 469         $this->assertSame(0, $results->count());
 470     }
 471
 472     /**
 473      * Test database version
 474      *
 475      * @return void
 476      */
 477     public function testDbVersion()
 478     {
 479         $db_version = $this->db->getDbVersion();
 480         $this->assertSame(GALETTE_DB_VERSION, $db_version);
 481
 482         $res = $this->db->checkDbVersion();
 483         $this->assertTrue($res);
 484     }
 485
 486     /**
 487      * Test database version that throws an exception
 488      *
 489      * @return void
 490      */
 491     public function testDbVersionWException()
 492     {
 493         $this->db = $this->getMockBuilder(\Galette\Core\Db::class)
 494             ->onlyMethods(array('execute'))
 495             ->getMock();
 496         $this->db->method('execute')
 497             ->will(
 498                 $this->returnCallback(
 499                     function ($table, $where) {
 500                         throw new \LogicException('Error executing query!', 123);
 501                     }
 502                 )
 503             );
 504
 505         $this->expectException('LogicException');
 506         $this->db->getDbVersion();
 507         $this->assertFalse($this->db->checkDbVersion());
 508     }
 509
 510     /**
 511      * Test get columns method
 512      *
 513      * @return void
 514      */
 515     public function testGetColumns()
 516     {
 517         $cols = $this->db->getColumns('preferences');
 518
 519         $this->assertCount(3, $cols);
 520
 521         $columns = array();
 522         foreach ($cols as $c) {
 523             $columns[] = $c->getName();
 524         }
 525
 526         $this->assertSame(
 527             array(
 528                 'id_pref',
 529                 'nom_pref',
 530                 'val_pref'
 531             ),
 532             array_values($columns)
 533         );
 534     }
 535
 536     /**
 537      * Test tables count
 538      *
 539      * FIXME: this test will fail if some plugins tables are present
 540      *
 541      * @return void
 542      */
 543     public function testTables()
 544     {
 545         $expected = array (
 546             'galette_groups_members',
 547             'galette_transactions',
 548             'galette_titles',
 549             'galette_types_cotisation',
 550             'galette_paymenttypes',
 551             'galette_database',
 552             'galette_socials',
 553             'galette_statuts',
 554             'galette_texts',
 555             'galette_logs',
 556             'galette_groups',
 557             'galette_reminders',
 558             'galette_groups_managers',
 559             'galette_dynamic_fields',
 560             'galette_fields_config',
 561             'galette_tmppasswds',
 562             'galette_pictures',
 563             'galette_adherents',
 564             'galette_l10n',
 565             'galette_import_model',
 566             'galette_cotisations',
 567             'galette_field_types',
 568             'galette_fields_categories',
 569             'galette_mailing_history',
 570             'galette_pdfmodels',
 571             'galette_preferences',
 572             'galette_searches',
 573             'galette_tmplinks'
 574         );
 575
 576         $tables = $this->db->getTables();
 577
 578         //tables created in grantCheck are sometimes
 579         //present here... :(
 580         if (in_array('galette_test', $tables)) {
 581             unset($tables[array_search('galette_test', $tables)]);
 582         }
 583
 584         sort($tables);
 585         sort($expected);
 586
 587         $this->assertSame($expected, $tables);
 588     }
 589
 590     /**
 591      * Test UTF conversion, for MySQL only
 592      *
 593      * @return void
 594      */
 595     public function testConvertToUtf()
 596     {
 597         $convert = $this->db->convertToUTF();
 598         $this->assertNull($convert);
 599     }
 600
 601     /**
 602      * Test get platform
 603      *
 604      * @return void
 605      */
 606     public function testGetPlatform()
 607     {
 608         $quoted = $this->db->platform->quoteValue('somethin\' to "quote"');
 609
 610         $expected = ($this->db->isPostgres()) ?
 611             "'somethin'' to \"quote\"'" :
 612             "'somethin\\' to \\\"quote\\\"'";
 613
 614         $this->assertSame($expected, $quoted);
 615     }
 616
 617     /**
 618      * Test execute Method
 619      *
 620      * @return void
 621      */
 622     public function testExecute()
 623     {
 624         $select = $this->db->select('preferences', 'p');
 625         $select->where(['p.nom_pref' => 'azerty']);
 626         $results = $this->db->execute($select);
 627
 628         $this->assertInstanceOf('\Laminas\Db\ResultSet\ResultSet', $results);
 629     }
 630
 631     /**
 632      * Test execute Method
 633      *
 634      * @return void
 635      */
 636     public function testExecuteWException()
 637     {
 638         $this->have_warnings = [
 639             new \ArrayObject(
 640                 [
 641                     'Level' => 'Error',
 642                     'Code' => 1054,
 643                     'Message' => "Unknown column 'p.notknown' in 'where clause'"
 644                 ]
 645             )
 646         ];
 647         $select = $this->db->select('preferences', 'p');
 648         $select->where(['p.nom_pref' => 'azerty']);
 649         $select->where(['p.notknown' => 'azerty']);
 650
 651         $this->expectException('\PDOException');
 652         $results = $this->db->execute($select);
 653     }
 654
 655     /**
 656      * Test serialization
 657      *
 658      * @return void
 659      */
 660     public function testSerialization()
 661     {
 662         $db = $this->db;
 663         $serialized = serialize($db);
 664         $this->assertNotNull($serialized);
 665
 666         $unserialized = unserialize($serialized);
 667         $this->assertInstanceOf('Galette\Core\Db', $unserialized);
 668     }
 669 }