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-2023 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-2023 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 Throwable;
  40 use Analog\Analog;
  41 use Laminas\Db\Adapter\Adapter;
  42 use Laminas\Db\Adapter\Driver\DriverInterface;
  43 use Laminas\Db\Adapter\Driver\AbstractConnection;
  44 use Laminas\Db\Adapter\Platform\PlatformInterface;
  45 use Laminas\Db\Sql\Insert;
  46 use Laminas\Db\Sql\Update;
  47 use Laminas\Db\Sql\Select;
  48 use Laminas\Db\Sql\Delete;
  49 use Laminas\Db\ResultSet\ResultSet;
  50 use Laminas\Db\Sql\Sql;
  51 use Laminas\Db\Sql\SqlInterface;
  52
  53 /**
  54  * Zend Db wrapper
  55  *
  56  * @category  Core
  57  * @name      Db
  58  * @package   Galette
  59  * @author    Johan Cwiklinski <johan@x-tnd.be>
  60  * @copyright 2011-2023 The Galette Team
  61  * @license   http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
  62  * @link      http://framework.zend.com/apidoc/2.2/namespaces/Zend.Db.html
  63  * @since     Available since 0.7dev - 2011-07-27
  64  *
  65  * @property Adapter $db
  66  * @property Sql $sql
  67  * @property DriverInterface $driver
  68  * @property AbstractConnection $connection
  69  * @property PlatformInterface $platform
  70  * @property string $query_string
  71  * @property string $type_db
  72  */
  73 class Db
  74 {
  75     /** @var Adapter */
  76     private $db;
  77     /** @var string */
  78     private $type_db;
  79     /** @var Sql */
  80     private $sql;
  81     /** @var array */
  82     private $options;
  83     /** @var string */
  84     private $last_query;
  85
  86     public const MYSQL = 'mysql';
  87     public const PGSQL = 'pgsql';
  88
  89     public const MYSQL_DEFAULT_PORT = 3306;
  90     public const PGSQL_DEFAULT_PORT = 5432;
  91
  92     /**
  93      * Main constructor
  94      *
  95      * @param array $dsn Connection information
  96      *                   If not set, database constants will be used.
  97      */
  98     public function __construct($dsn = null)
  99     {
 100         $_type = null;
 101
 102         if ($dsn !== null && is_array($dsn)) {
 103             $_type_db = $dsn['TYPE_DB'];
 104             $_host_db = $dsn['HOST_DB'];
 105             $_port_db = $dsn['PORT_DB'];
 106             $_user_db = $dsn['USER_DB'];
 107             $_pwd_db = $dsn['PWD_DB'];
 108             $_name_db = $dsn['NAME_DB'];
 109         } else {
 110             $_type_db = TYPE_DB;
 111             $_host_db = HOST_DB;
 112             $_port_db = PORT_DB;
 113             $_user_db = USER_DB;
 114             $_pwd_db = PWD_DB;
 115             $_name_db = NAME_DB;
 116         }
 117
 118         try {
 119             if ($_type_db === self::MYSQL) {
 120                 $_type = 'Pdo_Mysql';
 121             } elseif ($_type_db === self::PGSQL) {
 122                 $_type = 'Pdo_Pgsql';
 123             } else {
 124                 throw new \Exception("Type $_type_db not known (dsn: $_user_db@$_host_db(:$_port_db)/$_name_db)");
 125             }
 126
 127             $this->type_db = $_type_db;
 128             $this->options = array(
 129                 'driver'   => $_type,
 130                 'hostname' => $_host_db,
 131                 'port'     => $_port_db,
 132                 'username' => $_user_db,
 133                 'password' => $_pwd_db,
 134                 'database' => $_name_db
 135             );
 136             if ($_type_db === self::MYSQL && !defined('NON_UTF_DBCONNECT')) {
 137                 $this->options['charset'] = 'utf8';
 138             }
 139
 140             $this->doConnection();
 141         } catch (Throwable $e) {
 142             // perhaps factory() failed to load the specified Adapter class
 143             Analog::log(
 144                 '[Db] Error (' . $e->getCode() . '|' .
 145                 $e->getMessage() . ')',
 146                 Analog::ALERT
 147             );
 148             throw $e;
 149         }
 150     }
 151
 152     /**
 153      * Do database connection
 154      *
 155      * @return void
 156      */
 157     private function doConnection()
 158     {
 159         $this->db = new Adapter($this->options);
 160         $this->db->getDriver()->getConnection()->connect();
 161         $this->sql = new Sql($this->db);
 162
 163         if (!$this->isPostgres()) {
 164             $this->db->query("SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
 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 string
 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 (Throwable $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 ResultSet
 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
 280      *
 281      * @throws \Exception|Throwable
 282      */
 283     public static function testConnectivity(
 284         $type,
 285         $user = null,
 286         $pass = null,
 287         $host = null,
 288         $port = null,
 289         $db = null
 290     ) {
 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('Unknown database type');
 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             return true;
 313         } catch (Throwable $e) {
 314             // perhaps failed to load the specified Adapter class
 315             Analog::log(
 316                 '[' . __METHOD__ . '] Connection error (' . $e->getCode() . '|' .
 317                 $e->getMessage() . ')',
 318                 Analog::ALERT
 319             );
 320             throw $e;
 321         }
 322     }
 323
 324     /**
 325      * Drop test table if it exists, so we can make all checks.
 326      *
 327      * @return void
 328      */
 329     public function dropTestTable()
 330     {
 331         try {
 332             $this->db->query('DROP TABLE IF EXISTS galette_test');
 333             Analog::log('Test table successfully dropped.', Analog::DEBUG);
 334         } catch (Throwable $e) {
 335             Analog::log(
 336                 'Cannot drop test table! ' . $e->getMessage(),
 337                 Analog::WARNING
 338             );
 339         }
 340     }
 341
 342     /**
 343      * Checks GRANT access for install time
 344      *
 345      * @param string $mode are we at install time (i) or update time (u) ?
 346      *
 347      * @return array containing each test. Each array entry could
 348      *           be either true or contains an exception of false if test did not
 349      *           ran.
 350      */
 351     public function grantCheck($mode = 'i')
 352     {
 353         Analog::log(
 354             'Check for database rights (mode ' . $mode . ')',
 355             Analog::DEBUG
 356         );
 357         $stop = false;
 358         $results = array(
 359             'create' => false,
 360             'insert' => false,
 361             'select' => false,
 362             'update' => false,
 363             'delete' => false,
 364             'drop'   => false
 365         );
 366         if ($mode === 'u') {
 367             $results['alter'] = false;
 368         }
 369
 370         //can Galette CREATE tables?
 371         try {
 372             $sql = 'CREATE TABLE galette_test (
 373                 test_id INTEGER NOT NULL,
 374                 test_text VARCHAR(20)
 375             )';
 376             $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 377             $results['create'] = true;
 378         } catch (Throwable $e) {
 379             Analog::log('Cannot CREATE TABLE', Analog::WARNING);
 380             //if we cannot create tables, we cannot check other permissions
 381             $stop = true;
 382             $results['create'] = $e;
 383         }
 384
 385         //all those tests need the table to exists
 386         if (!$stop) {
 387             if ($mode == 'u') {
 388                 //can Galette ALTER tables? (only for update mode)
 389                 try {
 390                     $sql = 'ALTER TABLE galette_test ALTER test_text SET DEFAULT \'nothing\'';
 391                     $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 392                     $results['alter'] = true;
 393                 } catch (Throwable $e) {
 394                     Analog::log(
 395                         'Cannot ALTER TABLE | ' . $e->getMessage(),
 396                         Analog::WARNING
 397                     );
 398                     $results['alter'] = $e;
 399                 }
 400             }
 401
 402             //can Galette INSERT records ?
 403             $values = array(
 404                 'test_id'      => 1,
 405                 'test_text'    => 'a simple text'
 406             );
 407             try {
 408                 $insert = $this->sql->insert('galette_test');
 409                 $insert->values($values);
 410
 411                 $res = $this->execute($insert);
 412
 413                 if ($res->count() === 1) {
 414                     $results['insert'] = true;
 415                 } else {
 416                     throw new \Exception('No row inserted!');
 417                 }
 418             } catch (Throwable $e) {
 419                 Analog::log(
 420                     'Cannot INSERT records | ' . $e->getMessage(),
 421                     Analog::WARNING
 422                 );
 423                 //if we cannot insert records, some others tests cannot be done
 424                 $stop = true;
 425                 $results['insert'] = $e;
 426             }
 427
 428             //all those tests need that the first record exists
 429             if (!$stop) {
 430                 //can Galette UPDATE records ?
 431                 $values = array(
 432                     'test_text' => 'another simple text'
 433                 );
 434                 try {
 435                     $update = $this->sql->update('galette_test');
 436                     $update->set($values)->where(
 437                         array('test_id' => 1)
 438                     );
 439                     $res = $this->execute($update);
 440                     if ($res->count() === 1) {
 441                         $results['update'] = true;
 442                     } else {
 443                         throw new \Exception('No row updated!');
 444                     }
 445                 } catch (Throwable $e) {
 446                     Analog::log(
 447                         'Cannot UPDATE records | ' . $e->getMessage(),
 448                         Analog::WARNING
 449                     );
 450                     $results['update'] = $e;
 451                 }
 452
 453                 //can Galette SELECT records ?
 454                 try {
 455                     $select = $this->sql->select('galette_test');
 456                     $select->where(['test_id' => 1]);
 457                     $res = $this->execute($select);
 458                     $pass = $res->count() === 1;
 459
 460                     if ($pass) {
 461                         $results['select'] = true;
 462                     } else {
 463                         throw new \Exception('Select is empty!');
 464                     }
 465                 } catch (Throwable $e) {
 466                     Analog::log(
 467                         'Cannot SELECT records | ' . $e->getMessage(),
 468                         Analog::WARNING
 469                     );
 470                     $results['select'] = $e;
 471                 }
 472
 473                 //can Galette DELETE records ?
 474                 try {
 475                     $delete = $this->sql->delete('galette_test');
 476                     $delete->where(array('test_id' => 1));
 477                     $this->execute($delete);
 478                     $results['delete'] = true;
 479                 } catch (Throwable $e) {
 480                     Analog::log(
 481                         'Cannot DELETE records | ' . $e->getMessage(),
 482                         Analog::WARNING
 483                     );
 484                     $results['delete'] = $e;
 485                 }
 486             }
 487
 488             //can Galette DROP tables ?
 489             try {
 490                 $sql = 'DROP TABLE galette_test';
 491                 $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 492                 $results['drop'] = true;
 493             } catch (Throwable $e) {
 494                 Analog::log(
 495                     'Cannot DROP TABLE | ' . $e->getMessage(),
 496                     Analog::WARNING
 497                 );
 498                 $results['drop'] = $e;
 499             }
 500         }
 501
 502         return $results;
 503     }
 504
 505     /**
 506      * Get a list of Galette's tables
 507      *
 508      * @param string $prefix Specified table prefix, PREFIX_DB if null
 509      *
 510      * @return array
 511      */
 512     public function getTables($prefix = null)
 513     {
 514         $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 515         $tmp_tables_list = $metadata->getTableNames();
 516
 517         if ($prefix === null) {
 518             $prefix = PREFIX_DB;
 519         }
 520
 521         $tables_list = array();
 522         //filter table_list: we only want PREFIX_DB tables
 523         foreach ($tmp_tables_list as $t) {
 524             if (preg_match('/^' . $prefix . '/', $t)) {
 525                 $tables_list[] = $t;
 526             }
 527         }
 528         return $tables_list;
 529     }
 530
 531     /**
 532      * Get columns for a specified table
 533      *
 534      * @param string $table Table name
 535      *
 536      * @return array
 537      */
 538     public function getColumns($table)
 539     {
 540         $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 541         $table = $metadata->getTable(PREFIX_DB . $table);
 542         return $table->getColumns();
 543     }
 544
 545     /**
 546      * Converts recursively database to UTF-8
 547      *
 548      * @param string  $prefix       Specified table prefix
 549      * @param boolean $content_only Proceed only content (no table conversion)
 550      *
 551      * @return void
 552      */
 553     public function convertToUTF($prefix = null, $content_only = false)
 554     {
 555         if ($this->isPostgres()) {
 556             Analog::log(
 557                 'Cannot change encoding on PostgreSQL database',
 558                 Analog::INFO
 559             );
 560             return;
 561         }
 562         if ($prefix === null) {
 563             $prefix = PREFIX_DB;
 564         }
 565
 566         $table = '';
 567         try {
 568             $tables = $this->getTables($prefix);
 569
 570             foreach ($tables as $table) {
 571                 if ($content_only === false) {
 572                     //Change whole table charset
 573                     //CONVERT TO instruction will take care of each fields,
 574                     //but converting data stay our problem.
 575                     $query = 'ALTER TABLE ' . $table .
 576                         ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci';
 577
 578                     $this->db->query(
 579                         $query,
 580                         Adapter::QUERY_MODE_EXECUTE
 581                     );
 582
 583                     Analog::log(
 584                         'Charset successfully changed for table `' . $table . '`',
 585                         Analog::DEBUG
 586                     );
 587                 }
 588
 589                 //Data conversion
 590                 if ($table != $prefix . 'pictures') {
 591                     $this->convertContentToUTF($prefix, $table);
 592                 }
 593             }
 594         } catch (Throwable $e) {
 595             Analog::log(
 596                 'An error occurred while converting to utf table ' .
 597                 $table . ' (' . $e->getMessage() . ')',
 598                 Analog::ERROR
 599             );
 600             throw $e;
 601         }
 602     }
 603
 604     /**
 605      * Converts dtabase content to UTF-8
 606      *
 607      * @param string $prefix Specified table prefix
 608      * @param string $table  the table we want to convert datas from
 609      *
 610      * @return void
 611      */
 612     private function convertContentToUTF($prefix, $table)
 613     {
 614
 615         try {
 616             $query = 'SET NAMES latin1';
 617             $this->db->query(
 618                 $query,
 619                 Adapter::QUERY_MODE_EXECUTE
 620             );
 621         } catch (Throwable $e) {
 622             Analog::log(
 623                 'Cannot SET NAMES on table `' . $table . '`. ' .
 624                 $e->getMessage(),
 625                 Analog::ERROR
 626             );
 627         }
 628
 629         try {
 630             $metadata = \Laminas\Db\Metadata\Source\Factory::createSourceFromAdapter($this->db);
 631             $tbl = $metadata->getTable($table);
 632             $constraints = $tbl->getConstraints();
 633             $pkeys = array();
 634
 635             foreach ($constraints as $constraint) {
 636                 if ($constraint->getType() === 'PRIMARY KEY') {
 637                     $pkeys = $constraint->getColumns();
 638                 }
 639             }
 640
 641             if (count($pkeys) == 0) {
 642                 //no primary key! How to do an update without that?
 643                 //Prior to 0.7, l10n and dynamic_fields tables does not
 644                 //contains any primary key. Since encoding conversion is done
 645                 //_before_ the SQL upgrade, we'll have to manually
 646                 //check these ones
 647                 if (preg_match('/' . $prefix . 'dynamic_fields/', $table) !== 0) {
 648                     $pkeys = array(
 649                         'item_id',
 650                         'field_id',
 651                         'field_form',
 652                         'val_index'
 653                     );
 654                 } elseif (preg_match('/' . $prefix . 'l10n/', $table) !== 0) {
 655                     $pkeys = array(
 656                         'text_orig',
 657                         'text_locale'
 658                     );
 659                 } else {
 660                     //not a know case, we do not perform any update.
 661                     throw new \Exception(
 662                         'Cannot define primary key for table `' . $table .
 663                         '`, aborting'
 664                     );
 665                 }
 666             }
 667
 668             $select = $this->sql->select($table);
 669             $results = $this->execute($select);
 670
 671             foreach ($results as $row) {
 672                 $data = array();
 673                 $where = array();
 674
 675                 //build where
 676                 foreach ($pkeys as $k) {
 677                     $where[$k] = $row->$k;
 678                 }
 679
 680                 //build data
 681                 foreach ($row as $key => $value) {
 682                     $data[$key] = $value;
 683                 }
 684
 685                 //finally, update data!
 686                 $update = $this->sql->update($table);
 687                 $update->set($data)->where($where);
 688                 $this->execute($update);
 689             }
 690         } catch (Throwable $e) {
 691             Analog::log(
 692                 'An error occurred while converting contents to UTF-8 for table ' .
 693                 $table . ' (' . $e->getMessage() . ')',
 694                 Analog::ERROR
 695             );
 696         }
 697     }
 698
 699     /**
 700      * Is current database using Postgresql?
 701      *
 702      * @return boolean
 703      */
 704     public function isPostgres()
 705     {
 706         return $this->type_db === self::PGSQL;
 707     }
 708
 709     /**
 710      * Instanciate a select query
 711      *
 712      * @param string $table Table name, without prefix
 713      * @param string $alias Tables alias, optionnal
 714      *
 715      * @return Select
 716      */
 717     public function select($table, $alias = null)
 718     {
 719         if ($alias === null) {
 720             return $this->sql->select(
 721                 PREFIX_DB . $table
 722             );
 723         } else {
 724             return $this->sql->select(
 725                 array(
 726                     $alias => PREFIX_DB . $table
 727                 )
 728             );
 729         }
 730     }
 731
 732     /**
 733      * Instanciate an insert query
 734      *
 735      * @param string $table Table name, without prefix
 736      *
 737      * @return Insert
 738      */
 739     public function insert($table)
 740     {
 741         return $this->sql->insert(
 742             PREFIX_DB . $table
 743         );
 744     }
 745
 746     /**
 747      * Instanciate an update query
 748      *
 749      * @param string $table Table name, without prefix
 750      *
 751      * @return Update
 752      */
 753     public function update($table)
 754     {
 755         return $this->sql->update(
 756             PREFIX_DB . $table
 757         );
 758     }
 759
 760     /**
 761      * Instanciate a delete query
 762      *
 763      * @param string $table Table name, without prefix
 764      *
 765      * @return Delete
 766      */
 767     public function delete($table)
 768     {
 769         return $this->sql->delete(
 770             PREFIX_DB . $table
 771         );
 772     }
 773
 774     /**
 775      * Execute query string
 776      *
 777      * @param SqlInterface $sql SQL object
 778      *
 779      * @return ResultSet
 780      */
 781     public function execute($sql)
 782     {
 783         try {
 784             $query_string = $this->sql->buildSqlString($sql);
 785             $this->last_query = $query_string;
 786             $this->log($query_string);
 787             return $this->db->query(
 788                 $query_string,
 789                 Adapter::QUERY_MODE_EXECUTE
 790             );
 791         } catch (Throwable $e) {
 792             $msg = 'Query error: ';
 793             if (isset($query_string)) {
 794                 $msg .= $query_string;
 795             }
 796             Analog::log(
 797                 $msg . ' ' . $e->__toString(),
 798                 Analog::ERROR
 799             );
 800             if ($sql instanceof Insert && $this->isDuplicateException($e)) {
 801                 throw new \OverflowException('Duplicate entry', 0, $e);
 802             }
 803             throw $e;
 804         }
 805     }
 806
 807     /**
 808      * Global getter method
 809      *
 810      * @param string $name name of the variable we want to retrieve
 811      *
 812      * @return mixed
 813      */
 814     public function __get($name)
 815     {
 816         switch ($name) {
 817             case 'db':
 818                 return $this->db;
 819             case 'sql':
 820                 return $this->sql;
 821             case 'driver':
 822                 return $this->db->getDriver();
 823             case 'connection':
 824                 return $this->db->getDriver()->getConnection();
 825             case 'platform':
 826                 return $this->db->getPlatform();
 827             case 'query_string':
 828                 return $this->last_query;
 829             case 'type_db':
 830                 return $this->type_db;
 831         }
 832     }
 833
 834     /**
 835      * Global isset method
 836      * Required for twig to access properties via __get
 837      *
 838      * @param string $name name of the variable we want to retrieve
 839      *
 840      * @return bool
 841      */
 842     public function __isset($name)
 843     {
 844         switch ($name) {
 845             case 'db':
 846             case 'sql':
 847             case 'driver':
 848             case 'connection':
 849             case 'platform':
 850             case 'query_string':
 851             case 'type_db':
 852                 return true;
 853         }
 854         return property_exists($this, $name);
 855     }
 856
 857     /**
 858      * Get database information
 859      *
 860      * @return array
 861      */
 862     public function getInfos()
 863     {
 864         $infos = [
 865             'engine'    => null,
 866             'version'   => null,
 867             'size'      => null,
 868             'log_size'  => null,
 869             'sql_mode'  => ''
 870         ];
 871
 872         if ($this->isPostgres()) {
 873             $infos['engine'] = 'PostgreSQL';
 874             $sql = 'SHOW server_version';
 875             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 876                 ->current();
 877             $infos['version'] = $result['server_version'];
 878
 879             $sql = 'SELECT pg_database_size(\'' . NAME_DB . '\')';
 880             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 881                 ->current();
 882             $infos['size'] = (string)round($result['pg_database_size'] / 1024 / 1024);
 883         } else {
 884             $sql = 'SELECT @@sql_mode as mode, @@version AS version, @@version_comment AS version_comment';
 885             $result = $this->db->query($sql, Adapter::QUERY_MODE_EXECUTE)
 886                 ->current();
 887
 888             $infos['engine']    = $result['version_comment'];
 889             $infos['version']   = $result['version'];
 890             $infos['sql_mode']  = $result['mode'];
 891
 892             $size_sql = 'SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS dbsize' .
 893                 ' FROM information_schema.tables WHERE table_schema="' . NAME_DB . '"';
 894             $result = $this->db->query($size_sql, Adapter::QUERY_MODE_EXECUTE)
 895                 ->current();
 896
 897             $infos['size'] = $result['dbsize'];
 898         }
 899
 900         return $infos;
 901     }
 902
 903     /**
 904      * Handle sequence on PostgreSQL
 905      *
 906      * When inserting a value on a field with a sequence,
 907      * this one is not incremented.
 908      * This happens when installing system values (for status, titles, ...)
 909      *
 910      * @see https://bugs.galette.eu/issues/1158
 911      * @see https://bugs.galette.eu/issues/1374
 912      *
 913      * @param string  $table    Table name
 914      * @param integer $expected Expected value
 915      *
 916      * @return void
 917      */
 918     public function handleSequence($table, $expected)
 919     {
 920         if ($this->isPostgres()) {
 921             //check for Postgres sequence
 922             //see https://bugs.galette.eu/issues/1158
 923             //see https://bugs.galette.eu/issues/1374
 924             $seq = $table . '_id_seq';
 925
 926             $select = $this->select($seq);
 927             $select->columns(['last_value']);
 928             $results = $this->execute($select);
 929             $result = $results->current();
 930             if ($result->last_value < $expected) {
 931                 $this->db->query(
 932                     'SELECT setval(\'' . PREFIX_DB . $seq . '\', ' . $expected . ')',
 933                     Adapter::QUERY_MODE_EXECUTE
 934                 );
 935             }
 936         }
 937     }
 938
 939     /**
 940      * Check if current exception is on a duplicate key
 941      *
 942      * @param Throwable $exception Exception to check
 943      *
 944      * @return boolean
 945      */
 946     public function isDuplicateException($exception)
 947     {
 948         return $exception instanceof \PDOException
 949             && (
 950                 (!$this->isPostgres() && $exception->getCode() == 23000)
 951                 || ($this->isPostgres() && $exception->getCode() == 23505)
 952             )
 953         ;
 954     }
 955
 956     /**
 957      * Drops a table
 958      *
 959      * @param string  $table   Table name, without prefix
 960      * @param boolean $maymiss Whether the table can be missing, defaults to false
 961      *
 962      * @return void
 963      */
 964     public function drop($table, $maymiss = false)
 965     {
 966         $sql = 'DROP TABLE ';
 967         if ($maymiss === true) {
 968             $sql .= 'IF EXISTS ';
 969         }
 970         $sql .= PREFIX_DB . $table;
 971         $this->db->query(
 972             $sql,
 973             \Laminas\Db\Adapter\Adapter::QUERY_MODE_EXECUTE
 974         );
 975     }
 976
 977     /**
 978      * Log queries in specific file
 979      *
 980      * @param string $query Query to add in logs
 981      *
 982      * @return void
 983      */
 984     protected function log($query)
 985     {
 986         if (GALETTE_MODE == 'DEV' || defined('GALETTE_SQL_DEBUG')) {
 987             $logfile = GALETTE_LOGS_PATH . 'galette_sql.log';
 988             file_put_contents($logfile, $query . "\n", FILE_APPEND);
 989         }
 990     }
 991
 992     /**
 993      * Get last generated value
 994      *
 995      * @param object $entity Entity instance
 996      *
 997      * @return integer
 998      */
 999     public function getLastGeneratedValue($entity): int
1000     {
1001         /** @phpstan-ignore-next-line */
1002         return (int)$this->driver->getLastGeneratedValue(
1003             $this->isPostgres() ?
1004                 PREFIX_DB . $entity::TABLE . '_id_seq'
1005                 : null
1006         );
1007     }
1008
1009     /**
1010      * Get MySQL warnings
1011      *
1012      * @return array
1013      */
1014     public function getWarnings(): array
1015     {
1016         $results = $this->db->query('SHOW WARNINGS', Adapter::QUERY_MODE_EXECUTE);
1017
1018         $warnings = [];
1019         foreach ($results as $result) {
1020             $warnings[] = $result;
1021         }
1022
1023         return $warnings;
1024     }
1025 }