galette/lib/Galette/Core/Db.php

   1 <?php
   2
   3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
   4
   5 /**
   6  * Zend Db wrapper
   7  *
   8  * PHP version 5
   9  *
  10  * Copyright © 2011-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   Galette
  29  *
  30  * @author    Johan Cwiklinski <johan@x-tnd.be>
  31  * @copyright 2011-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  * @link      http://galette.tuxfamily.org
  34  * @since     Available since 0.7dev - 2011-07-27
  35  */
  36
  37 namespace Galette\Core;
  38
  39 use Analog\Analog;
  40 use Laminas\Db\Adapter\Adapter;
  41 use Laminas\Db\Adapter\Driver\DriverInterface;
  42 use Laminas\Db\Adapter\Driver\ConnectionInterface;
  43 use Laminas\Db\Adapter\Platform\PlatformInterface;
  44 use Laminas\Db\Sql\Insert;
  45 use Laminas\Db\Sql\Select;
  46 use Laminas\Db\Sql\Sql;
  47
  48 /**
  49  * Zend Db wrapper
  50  *
  51  * @category  Core
  52  * @name      Db
  53  * @package   Galette
  54  * @author    Johan Cwiklinski <johan@x-tnd.be>
  55  * @copyright 2011-2014 The Galette Team
  56  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  57  * @link      http://framework.zend.com/apidoc/2.2/namespaces/Zend.Db.html
  58  * @since     Available since 0.7dev - 2011-07-27
  59  *
  60  * @property Adapter $db
  61  * @property Sql $sql
  62  * @property DriverInterface $driver
  63  * @property ConnectionInterface $connection
  64  * @property PlatformInterface $platform
  65  * @property string $query_string
  66  * @property string $type_db
  67  */
  68 class Db
  69 {
  70     /** @var Adapter */
  71     private $db;
  72     /** @var string */
  73     private $type_db;
  74     /** @var Sql */
  75     private $sql;
  76     /** @var array */
  77     private $options;
  78     /** @var string */
  79     private $last_query;
  80
  81     public const MYSQL = 'mysql';
  82     public const PGSQL = 'pgsql';
  83
  84     public const MYSQL_DEFAULT_PORT = 3306;
  85     public const PGSQL_DEFAULT_PORT = 5432;
  86
  87     /**
  88      * Main constructor
  89      *
  90      * @param array $dsn Connection information
  91      *                   If not set, database constants will be used.
  92      */
  93     public function __construct($dsn = null)
  94     {
  95         $_type = null;
  96
  97         if ($dsn !== null && is_array($dsn)) {
  98             $_type_db = $dsn['TYPE_DB'];
  99             $_host_db = $dsn['HOST_DB'];
 100             $_port_db = $dsn['PORT_DB'];
 101             $_user_db = $dsn['USER_DB'];
 102             $_pwd_db = $dsn['PWD_DB'];
 103             $_name_db = $dsn['NAME_DB'];
 104         } else {
 105             $_type_db = TYPE_DB;
 106             $_host_db = HOST_DB;
 107             $_port_db = PORT_DB;
 108             $_user_db = USER_DB;
 109             $_pwd_db = PWD_DB;
 110             $_name_db = NAME_DB;
 111         }
 112
 113         try {
 114             if ($_type_db === self::MYSQL) {
 115                 $_type = 'Pdo_Mysql';
 116             } elseif ($_type_db === self::PGSQL) {
 117                 $_type = 'Pdo_Pgsql';
 118             } else {
 119                 throw new \Exception("Type $_type_db not known (dsn: $_user_db@$_host_db(:$_port_db)/$_name_db)");
 120             }
 121
 122             $this->type_db = $_type_db;
 123             $this->options = array(
 124                 'driver'   => $_type,
 125                 'hostname' => $_host_db,
 126                 'port'     => $_port_db,
 127                 'username' => $_user_db,
 128                 'password' => $_pwd_db,
 129                 'database' => $_name_db
 130             );
 131             if ($_type_db === self::MYSQL && !defined('NON_UTF_DBCONNECT')) {
 132                 $this->options['charset'] = 'utf8';
 133             }
 134
 135             $this->doConnection();
 136         } catch (\Exception $e) {
 137             // perhaps factory() failed to load the specified Adapter class
 138             Analog::log(
 139                 '[Db] Error (' . $e->getCode() . '|' .
 140                 $e->getMessage() . ')',
 141                 Analog::ALERT
 142             );
 143             throw $e;
 144         }
 145     }
 146
 147     /**
 148      * Do database connection
 149      *
 150      * @return void
 151      */
 152     private function doConnection()
 153     {
 154         $this->db = new Adapter($this->options);
 155         $this->db->getDriver()->getConnection()->connect();
 156         $this->sql = new Sql($this->db);
 157
 158         if (!$this->isPostgres()) {
 159             $this->db->query("SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
 160         }
 161
 162         Analog::log(
 163             '[Db] Database connection was successfull!',
 164             Analog::DEBUG
 165         );
 166     }
 167
 168     /**
 169      * To store Db in session
 170      *
 171      * @return array
 172      */
 173     public function __sleep()
 174     {
 175         return ['type_db', 'options'];
 176     }
 177
 178     /**
 179      * Connect again to the database on wakeup
 180      *
 181      * @return void
 182      */
 183     public function __wakeup()
 184     {
 185         $this->doConnection();
 186     }
 187
 188     /**
 189      * Retrieve current database version
 190      *
 191      * @param boolean $check_table Check if table exists, defaults to false
 192      *
 193      * @return float
 194      *
 195      * @throw LogicException
 196      */
 197     public function getDbVersion($check_table = false)
 198     {
 199         try {
 200             if ($check_table === true) {
 201                 $exists = count($this->getTables(PREFIX_DB . 'database')) === 1;
 202             } else {
 203                 $exists = true;
 204             }
 205
 206             if ($exists === true) {
 207                 $select = $this->select('database');
 208                 $select->columns(
 209                     array('version')
 210                 )->limit(1);
 211
 212                 $results = $this->execute($select);
 213                 $result = $results->current();
 214                 return number_format(
 215                     $result->version,
 216                     3,
 217                     '.',
 218                     ''
 219                 );
 220             } else {
 221                 return 0.63;
 222             }
 223         } catch (\Exception $e) {
 224             Analog::log(
 225                 'Cannot check database version: ' . $e->getMessage(),
 226                 Analog::ERROR
 227             );
 228             throw new \LogicException('Cannot check database version');
 229         }
 230     }
 231
 232     /**
 233      * Check if database version suits our needs
 234      *
 235      * @return boolean
 236      */
 237     public function checkDbVersion()
 238     {
 239         if (GALETTE_MODE === 'DEV') {
 240             Analog::log(
 241                 'Database version not checked in DEV mode.',
 242                 Analog::INFO
 243             );
 244             return true;
 245         }
 246
 247         try {
 248             return $this->getDbVersion() === GALETTE_DB_VERSION;
 249         } catch (\LogicException $e) {
 250             return false;
 251         }
 252     }
 253
 254     /**
 255      * Peform a select query on the whole table
 256      *
 257      * @param string $table Table name
 258      *
 259      * @return array
 260      */
 261     public function selectAll($table)
 262     {
 263         return $this->db->query(
 264             'SELECT * FROM ' . PREFIX_DB . $table,
 265             Adapter::QUERY_MODE_EXECUTE
 266         );
 267     }
 268
 269     /**
 270      * Test if database can be contacted. Mostly used for installation
 271      *
 272      * @param string $type db type
 273      * @param string $user database's user
 274      * @param string $pass password for the user
 275      * @param string $host which host we want to connect to
 276      * @param string $port which tcp port we want to connect to
 277      * @param string $db   database name
 278      *
 279      * @return true|array true if connection was successfull,
 280      *                    an array with some infos otherwise
 281      */
 282     public static function testConnectivity(
 283         $type,
 284         $user = null,
 285         $pass = null,
 286         $host = null,
 287         $port = null,
 288         $db = null
 289     ) {
 290         $_type = null;
 291         try {
 292             if ($type === self::MYSQL) {
 293                 $_type = 'Pdo_Mysql';
 294             } elseif ($type === self::PGSQL) {
 295                 $_type = 'Pdo_Pgsql';
 296             } else {
 297                 throw new \Exception();
 298             }
 299
 300             $_options = array(
 301                 'driver'   => $_type,
 302                 'hostname' => $host,
 303                 'port'     => $port,
 304                 'username' => $user,
 305                 'password' => $pass,
 306                 'database' => $db
 307             );
 308
 309             $_db = new Adapter($_options);
 310             $_db->getDriver()->getConnection()->connect();
 311
 312             Analog::log(
 313                 '[' . __METHOD__ . '] Database connection was successfull!',
 314                 Analog::DEBUG
 315             );
 316             return true;
 317         } catch (\Exception $e) {
 318             // perhaps failed to load the specified Adapter class
 319             Analog::log(
 320                 '[' . __METHOD__ . '] Connection error (' . $e->getCode() . '|' .
 321                 $e->getMessage() . ')',
 322                 Analog::ALERT
 323             );
 324             return $e;
 325         }
 326     }
 327
 328     /**
 329      * Drop test table if it exists, so we can make all checks.
 330      *
 331      * @return void
 332      */
 333     public function dropTestTable()
 334     {
 335         try {
 336             $this->db->query('DROP TABLE IF EXISTS galette_test');
 337             Analog::log('Test table successfully dropped.', Analog::DEBUG);
 338         } catch (\Exception $e) {
 339             Analog::log(
 340                 'Cannot drop test table! ' . $e->getMessage(),
 341                 Analog::WARNING
 342             );
 343         }
 344     }
 345
 346     /**
 347      * Checks GRANT access for install time
 348      *
 349      * @param string $mode are we at install time (i) or update time (u) ?
 350      *
 351      * @return array containing each test. Each array entry could
 352      *           be either true or contains an exception of false if test did not
 353      *           ran.
 354      */
 355     public function grantCheck($mode = 'i')
 356     {
 357         Analog::log(
 358             'Check for database rights (mode ' . $mode . ')',
 359             Analog::DEBUG
 360         );
 361         $stop = false;
 362         $results = array(
 363             'create' => false,
 364             'insert' => false,
 365             'select' => false,
 366             'update' => false,
 367             'delete' => false,
 368             'drop'   => false
 369         );
 370         if ($mode === 'u') {
 371             $results['alter'] = false;
 372         }
 373
 374         //can Galette CREATE tables?
 375         try {
 376             $sql = 'CREATE TABLE galette_test (
 377                 test_id INTEGER NOT NULL,
 378                 test_text VARCHAR(20)
 379             )';
 380             $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 381             $results['create'] = true;
 382         } catch (\Exception $e) {
 383             Analog::log('Cannot CREATE TABLE', Analog::WARNING);
 384             //if we cannot create tables, we cannot check other permissions
 385             $stop = true;
 386             $results['create'] = $e;
 387         }
 388
 389         //all those tests need the table to exists
 390         if (!$stop) {
 391             if ($mode == 'u') {
 392                 //can Galette ALTER tables? (only for update mode)
 393                 try {
 394                     $sql = 'ALTER TABLE galette_test ALTER test_text SET DEFAULT \'nothing\'';
 395                     $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 396                     $results['alter'] = true;
 397                 } catch (\Exception $e) {
 398                     Analog::log(
 399                         'Cannot ALTER TABLE | ' . $e->getMessage(),
 400                         Analog::WARNING
 401                     );
 402                     $results['alter'] = $e;
 403                 }
 404             }
 405
 406             //can Galette INSERT records ?
 407             $values = array(
 408                 'test_id'      => 1,
 409                 'test_text'    => 'a simple text'
 410             );
 411             try {
 412                 $insert = $this->sql->insert('galette_test');
 413                 $insert->values($values);
 414
 415                 $res = $this->execute($insert);
 416
 417                 if ($res->count() === 1) {
 418                     $results['insert'] = true;
 419                 } else {
 420                     throw new \Exception('No row inserted!');
 421                 }
 422             } catch (\Exception $e) {
 423                 Analog::log(
 424                     'Cannot INSERT records | ' . $e->getMessage(),
 425                     Analog::WARNING
 426                 );
 427                 //if we cannot insert records, some others tests cannot be done
 428                 $stop = true;
 429                 $results['insert'] = $e;
 430             }
 431
 432             //all those tests need that the first record exists
 433             if (!$stop) {
 434                 //can Galette UPDATE records ?
 435                 $values = array(
 436                     'test_text' => 'another simple text'
 437                 );
 438                 try {
 439                     $update = $this->sql->update('galette_test');
 440                     $update->set($values)->where(
 441                         array('test_id' => 1)
 442                     );
 443                     $res = $this->execute($update);
 444                     if ($res->count() === 1) {
 445                         $results['update'] = true;
 446                     } else {
 447                         throw new \Exception('No row updated!');
 448                     }
 449                 } catch (\Exception $e) {
 450                     Analog::log(
 451                         'Cannot UPDATE records | ' . $e->getMessage(),
 452                         Analog::WARNING
 453                     );
 454                     $results['update'] = $e;
 455                 }
 456
 457                 //can Galette SELECT records ?
 458                 try {
 459                     $pass = false;
 460                     $select = $this->sql->select('galette_test');
 461                     $select->where('test_id = 1');
 462                     $res = $this->execute($select);
 463                     $pass = $res->count() === 1;
 464
 465                     if ($pass) {
 466                         $results['select'] = true;
 467                     } else {
 468                         throw new \Exception('Select is empty!');
 469                     }
 470                 } catch (\Exception $e) {
 471                     Analog::log(
 472                         'Cannot SELECT records | ' . $e->getMessage(),
 473                         Analog::WARNING
 474                     );
 475                     $results['select'] = $e;
 476                 }
 477
 478                 //can Galette DELETE records ?
 479                 try {
 480                     $delete = $this->sql->delete('galette_test');
 481                     $delete->where(array('test_id' => 1));
 482                     $this->execute($delete);
 483                     $results['delete'] = true;
 484                 } catch (\Exception $e) {
 485                     Analog::log(
 486                         'Cannot DELETE records | ' . $e->getMessage(),
 487                         Analog::WARNING
 488                     );
 489                     $results['delete'] = $e;
 490                 }
 491             }
 492
 493             //can Galette DROP tables ?
 494             try {
 495                 $sql = 'DROP TABLE galette_test';
 496                 $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 497                 $results['drop'] = true;
 498             } catch (\Exception $e) {
 499                 Analog::log(
 500                     'Cannot DROP TABLE | ' . $e->getMessage(),
 501                     Analog::WARNING
 502                 );
 503                 $results['drop'] = $e;
 504             }
 505         }
 506
 507         return $results;
 508     }
 509
 510     /**
 511      * Get a list of Galette's tables
 512      *
 513      * @param string $prefix Specified table prefix, PREFIX_DB if null
 514      *
 515      * @return array
 516      */
 517     public function getTables($prefix = null)
 518     {
 519         $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 520         $tmp_tables_list = $metadata->getTableNames();
 521
 522         if ($prefix === null) {
 523             $prefix = PREFIX_DB;
 524         }
 525
 526         $tables_list = array();
 527         //filter table_list: we only want PREFIX_DB tables
 528         foreach ($tmp_tables_list as $t) {
 529             if (preg_match('/^' . $prefix . '/', $t)) {
 530                 $tables_list[] = $t;
 531             }
 532         }
 533         return $tables_list;
 534     }
 535
 536     /**
 537      * Get columns for a specified table
 538      *
 539      * @param string $table Table name
 540      *
 541      * @return array
 542      */
 543     public function getColumns($table)
 544     {
 545         $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 546         $table = $metadata->getTable(PREFIX_DB . $table);
 547         return $table->getColumns();
 548     }
 549
 550     /**
 551      * Converts recursively database to UTF-8
 552      *
 553      * @param string  $prefix       Specified table prefix
 554      * @param boolean $content_only Proceed only content (no table conversion)
 555      *
 556      * @return void
 557      */
 558     public function convertToUTF($prefix = null, $content_only = false)
 559     {
 560         if ($this->isPostgres()) {
 561             Analog::log(
 562                 'Cannot change encoding on PostgreSQL database',
 563                 Analog::INFO
 564             );
 565             return;
 566         }
 567         if ($prefix === null) {
 568             $prefix = PREFIX_DB;
 569         }
 570
 571         try {
 572             $this->connection->beginTransaction();
 573
 574             $tables = $this->getTables($prefix);
 575
 576             foreach ($tables as $table) {
 577                 if ($content_only === false) {
 578                     //Change whole table charset
 579                     //CONVERT TO instruction will take care of each fields,
 580                     //but converting data stay our problem.
 581                     $query = 'ALTER TABLE ' . $table .
 582                         ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci';
 583
 584                     $this->db->query(
 585                         $query,
 586                         Adapter::QUERY_MODE_EXECUTE
 587                     );
 588
 589                     Analog::log(
 590                         'Charset successfully changed for table `' . $table . '`',
 591                         Analog::DEBUG
 592                     );
 593                 }
 594
 595                 //Data conversion
 596                 if ($table != $prefix . 'pictures') {
 597                     $this->convertContentToUTF($prefix, $table);
 598                 }
 599             }
 600             $this->connection->commit();
 601         } catch (\Exception $e) {
 602             $this->connection->rollBack();
 603             Analog::log(
 604                 'An error occurred while converting to utf table ' .
 605                 $table . ' (' . $e->getMessage() . ')',
 606                 Analog::ERROR
 607             );
 608         }
 609     }
 610
 611     /**
 612      * Converts dtabase content to UTF-8
 613      *
 614      * @param string $prefix Specified table prefix
 615      * @param string $table  the table we want to convert datas from
 616      *
 617      * @return void
 618      */
 619     private function convertContentToUTF($prefix, $table)
 620     {
 621
 622         try {
 623             $query = 'SET NAMES latin1';
 624             $this->db->query(
 625                 $query,
 626                 Adapter::QUERY_MODE_EXECUTE
 627             );
 628         } catch (\Exception $e) {
 629             Analog::log(
 630                 'Cannot SET NAMES on table `' . $table . '`. ' .
 631                 $e->getMessage(),
 632                 Analog::ERROR
 633             );
 634         }
 635
 636         try {
 637             $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 638             $tbl = $metadata->getTable($table);
 639             $columns = $tbl->getColumns();
 640             $constraints = $tbl->getConstraints();
 641             $pkeys = array();
 642
 643             foreach ($constraints as $constraint) {
 644                 if ($constraint->getType() === 'PRIMARY KEY') {
 645                     $pkeys = $constraint->getColumns();
 646                 }
 647             }
 648
 649             if (count($pkeys) == 0) {
 650                 //no primary key! How to do an update without that?
 651                 //Prior to 0.7, l10n and dynamic_fields tables does not
 652                 //contains any primary key. Since encoding conversion is done
 653                 //_before_ the SQL upgrade, we'll have to manually
 654                 //check these ones
 655                 if (preg_match('/' . $prefix . 'dynamic_fields/', $table) !== 0) {
 656                     $pkeys = array(
 657                         'item_id',
 658                         'field_id',
 659                         'field_form',
 660                         'val_index'
 661                     );
 662                 } elseif (preg_match('/' . $prefix . 'l10n/', $table) !== 0) {
 663                     $pkeys = array(
 664                         'text_orig',
 665                         'text_locale'
 666                     );
 667                 } else {
 668                     //not a know case, we do not perform any update.
 669                     throw new \Exception(
 670                         'Cannot define primary key for table `' . $table .
 671                         '`, aborting'
 672                     );
 673                 }
 674             }
 675
 676             $select = $this->sql->select($table);
 677             $results = $this->execute($select);
 678
 679             foreach ($results as $row) {
 680                 $data = array();
 681                 $where = array();
 682
 683                 //build where
 684                 foreach ($pkeys as $k) {
 685                     $where[] = $k . ' = "' . $row->$k . '"';
 686                 }
 687
 688                 //build data
 689                 foreach ($row as $key => $value) {
 690                     $data[$key] = $value;
 691                 }
 692
 693                 //finally, update data!
 694                 $update = $this->sql->update($table);
 695                 $update->set($data)->where($where);
 696                 $this->execute($update);
 697             }
 698         } catch (\Exception $e) {
 699             Analog::log(
 700                 'An error occurred while converting contents to UTF-8 for table ' .
 701                 $table . ' (' . $e->getMessage() . ')',
 702                 Analog::ERROR
 703             );
 704         }
 705     }
 706
 707     /**
 708      * Is current database using Postgresql?
 709      *
 710      * @return boolean
 711      */
 712     public function isPostgres()
 713     {
 714         return $this->type_db === self::PGSQL;
 715     }
 716
 717     /**
 718      * Instanciate a select query
 719      *
 720      * @param string $table Table name, without prefix
 721      * @param string $alias Tables alias, optionnal
 722      *
 723      * @return Select
 724      */
 725     public function select($table, $alias = null)
 726     {
 727         if ($alias === null) {
 728             return $this->sql->select(
 729                 PREFIX_DB . $table
 730             );
 731         } else {
 732             return $this->sql->select(
 733                 array(
 734                     $alias => PREFIX_DB . $table
 735                 )
 736             );
 737         }
 738     }
 739
 740     /**
 741      * Instanciate an insert query
 742      *
 743      * @param string $table Table name, without prefix
 744      *
 745      * @return Insert
 746      */
 747     public function insert($table)
 748     {
 749         return $this->sql->insert(
 750             PREFIX_DB . $table
 751         );
 752     }
 753
 754     /**
 755      * Instanciate an update query
 756      *
 757      * @param string $table Table name, without prefix
 758      *
 759      * @return Update
 760      */
 761     public function update($table)
 762     {
 763         return $this->sql->update(
 764             PREFIX_DB . $table
 765         );
 766     }
 767
 768     /**
 769      * Instanciate a delete query
 770      *
 771      * @param string $table Table name, without prefix
 772      *
 773      * @return Delete
 774      */
 775     public function delete($table)
 776     {
 777         return $this->sql->delete(
 778             PREFIX_DB . $table
 779         );
 780     }
 781
 782     /**
 783      * Execute query string
 784      *
 785      * @param SqlInterface $sql SQL object
 786      *
 787      * @return Stmt
 788      */
 789     public function execute($sql)
 790     {
 791         try {
 792             $query_string = $this->sql->buildSqlString($sql);
 793             $this->last_query = $query_string;
 794             Analog::log(
 795                 'Executing query: ' . $query_string,
 796                 Analog::DEBUG
 797             );
 798             return $this->db->query(
 799                 $query_string,
 800                 Adapter::QUERY_MODE_EXECUTE
 801             );
 802         } catch (\Exception $e) {
 803             $msg = 'Query error: ';
 804             if (isset($query_string)) {
 805                 $msg .= $query_string;
 806             }
 807             Analog::log(
 808                 $msg . ' ' . $e->__toString(),
 809                 Analog::ERROR
 810             );
 811             if ($sql instanceof Insert && $this->isDuplicateException($e)) {
 812                 throw new \OverflowException('Duplicate entry', 0, $e);
 813             }
 814             throw $e;
 815         }
 816     }
 817
 818     /**
 819      * Global getter method
 820      *
 821      * @param string $name name of the variable we want to retrieve
 822      *
 823      * @return mixed
 824      */
 825     public function __get($name)
 826     {
 827         switch ($name) {
 828             case 'db':
 829                 return $this->db;
 830                 break;
 831             case 'sql':
 832                 return $this->sql;
 833                 break;
 834             case 'driver':
 835                 return $this->db->getDriver();
 836                 break;
 837             case 'connection':
 838                 return $this->db->getDriver()->getConnection();
 839                 break;
 840             case 'platform':
 841                 return $this->db->getPlatform();
 842                 break;
 843             case 'query_string':
 844                 return $this->last_query;
 845                 break;
 846             case 'type_db':
 847                 return $this->type_db;
 848                 break;
 849         }
 850     }
 851
 852     /**
 853      * Get database information
 854      *
 855      * @return array
 856      */
 857     public function getInfos()
 858     {
 859         $infos = [
 860             'engine'    => null,
 861             'version'   => null,
 862             'size'      => null,
 863             'log_size'  => null,
 864             'sql_mode'  => ''
 865         ];
 866
 867         if ($this->isPostgres()) {
 868             $infos['engine'] = 'PostgreSQL';
 869             $sql = 'SHOW server_version';
 870             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 871                 ->current();
 872             $infos['version'] = $result['server_version'];
 873
 874             $sql = 'SELECT pg_database_size(\'' . NAME_DB . '\')';
 875             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 876                 ->current();
 877             $infos['size'] = (string)round($result['pg_database_size'] / 1024 / 1024);
 878         } else {
 879             $sql = 'SELECT @@sql_mode as mode, @@version AS version, @@version_comment AS version_comment';
 880             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 881                 ->current();
 882
 883             $infos['engine']    = $result['version_comment'];
 884             $infos['version']   = $result['version'];
 885             $infos['sql_mode']  = $result['mode'];
 886
 887             $size_sql = 'SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS dbsize' .
 888                 ' FROM information_schema.tables WHERE table_schema="' . NAME_DB . '"';
 889             $result = $this->db->query($size_sql, Adapter::QUERY_MODE_EXECUTE)
 890                 ->current();
 891
 892             $infos['size'] = $result['dbsize'];
 893         }
 894
 895         return $infos;
 896     }
 897
 898     /**
 899      * Handle sequence on PostgreSQL
 900      *
 901      * When inserting a value on a field with a sequence,
 902      * this one is not incremented.
 903      * This happens when installing system values (for status, titles, ...)
 904      *
 905      * @see https://bugs.galette.eu/issues/1158
 906      * @see https://bugs.galette.eu/issues/1374
 907      *
 908      * @param string  $table    Table name
 909      * @param integer $expected Expected value
 910      *
 911      * @return void
 912      */
 913     public function handleSequence($table, $expected)
 914     {
 915         if ($this->isPostgres()) {
 916             //check for Postgres sequence
 917             //see https://bugs.galette.eu/issues/1158
 918             //see https://bugs.galette.eu/issues/1374
 919             $seq = $table . '_id_seq';
 920
 921             $select = $this->select($seq);
 922             $select->columns(['last_value']);
 923             $results = $this->execute($select);
 924             $result = $results->current();
 925             if ($result->last_value < $expected) {
 926                 $this->db->query(
 927                     'SELECT setval(\'' . PREFIX_DB . $seq . '\', ' . $expected . ')',
 928                     Adapter::QUERY_MODE_EXECUTE
 929                 );
 930             }
 931         }
 932     }
 933
 934     /**
 935      * Check if current exception is on a duplicate key
 936      *
 937      * @param \Exception $exception Exception to check
 938      *
 939      * @return boolean
 940      */
 941     public function isDuplicateException($exception)
 942     {
 943         return $exception instanceof \PDOException
 944             && (
 945                 (!$this->isPostgres() && $exception->getCode() == 23000)
 946                 || ($this->isPostgres() && $exception->getCode() == 23505)
 947             )
 948         ;
 949     }
 950 }