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

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