3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
10 * Copyright © 2011-2014 The Galette Team
12 * This file is part of Galette (http://galette.tuxfamily.org).
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.
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.
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/>.
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
37 namespace Galette\Core
;
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\Sql
;
52 * @author Johan Cwiklinski <johan@x-tnd.be>
53 * @copyright 2011-2014 The Galette Team
54 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
55 * @link http://framework.zend.com/apidoc/2.2/namespaces/Zend.Db.html
56 * @since Available since 0.7dev - 2011-07-27
58 * @property Adapter $db
60 * @property DriverInterface $driver
61 * @property ConnectionInterface $connection
62 * @property PlatformInterface $platform
63 * @property string $query_string
64 * @property string $type_db
79 public const MYSQL
= 'mysql';
80 public const PGSQL
= 'pgsql';
82 public const MYSQL_DEFAULT_PORT
= 3306;
83 public const PGSQL_DEFAULT_PORT
= 5432;
88 * @param array $dsn Connection information
89 * If not set, database constants will be used.
91 public function __construct($dsn = null)
95 if ($dsn !== null && is_array($dsn)) {
96 $_type_db = $dsn['TYPE_DB'];
97 $_host_db = $dsn['HOST_DB'];
98 $_port_db = $dsn['PORT_DB'];
99 $_user_db = $dsn['USER_DB'];
100 $_pwd_db = $dsn['PWD_DB'];
101 $_name_db = $dsn['NAME_DB'];
112 if ($_type_db === self
::MYSQL
) {
113 $_type = 'Pdo_Mysql';
114 } elseif ($_type_db === self
::PGSQL
) {
115 $_type = 'Pdo_Pgsql';
117 throw new \
Exception("Type $_type_db not known (dsn: $_user_db@$_host_db(:$_port_db)/$_name_db)");
120 $this->type_db
= $_type_db;
121 $this->options
= array(
123 'hostname' => $_host_db,
125 'username' => $_user_db,
126 'password' => $_pwd_db,
127 'database' => $_name_db
129 if ($_type_db === self
::MYSQL
&& !defined('NON_UTF_DBCONNECT')) {
130 $this->options
['charset'] = 'utf8';
133 $this->doConnection();
134 } catch (\Exception
$e) {
135 // perhaps factory() failed to load the specified Adapter class
137 '[Db] Error (' . $e->getCode() . '|' .
138 $e->getMessage() . ')',
146 * Do database connection
150 private function doConnection()
152 $this->db
= new Adapter($this->options
);
153 $this->db
->getDriver()->getConnection()->connect();
154 $this->sql
= new Sql($this->db
);
156 if (!$this->isPostgres()) {
157 $this->db
->query("SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
161 '[Db] Database connection was successfull!',
167 * To store Db in session
171 public function __sleep()
173 return ['type_db', 'options'];
177 * Connect again to the database on wakeup
181 public function __wakeup()
183 $this->doConnection();
187 * Retrieve current database version
189 * @param boolean $check_table Check if table exists, defaults to false
193 * @throw LogicException
195 public function getDbVersion($check_table = false)
198 if ($check_table === true) {
199 $exists = count($this->getTables(PREFIX_DB
. 'database')) === 1;
204 if ($exists === true) {
205 $select = $this->select('database');
210 $results = $this->execute($select);
211 $result = $results->current();
212 return number_format(
221 } catch (\Exception
$e) {
223 'Cannot check database version: ' . $e->getMessage(),
226 throw new \
LogicException('Cannot check database version');
231 * Check if database version suits our needs
235 public function checkDbVersion()
237 if (GALETTE_MODE
=== 'DEV') {
239 'Database version not checked in DEV mode.',
246 return $this->getDbVersion() === GALETTE_DB_VERSION
;
247 } catch (\LogicException
$e) {
253 * Peform a select query on the whole table
255 * @param string $table Table name
259 public function selectAll($table)
261 return $this->db
->query(
262 'SELECT * FROM ' . PREFIX_DB
. $table,
263 Adapter
::QUERY_MODE_EXECUTE
268 * Test if database can be contacted. Mostly used for installation
270 * @param string $type db type
271 * @param string $user database's user
272 * @param string $pass password for the user
273 * @param string $host which host we want to connect to
274 * @param string $port which tcp port we want to connect to
275 * @param string $db database name
277 * @return true|array true if connection was successfull,
278 * an array with some infos otherwise
280 public static function testConnectivity(
290 if ($type === self
::MYSQL
) {
291 $_type = 'Pdo_Mysql';
292 } elseif ($type === self
::PGSQL
) {
293 $_type = 'Pdo_Pgsql';
295 throw new \
Exception();
307 $_db = new Adapter($_options);
308 $_db->getDriver()->getConnection()->connect();
311 '[' . __METHOD__
. '] Database connection was successfull!',
315 } catch (\Exception
$e) {
316 // perhaps failed to load the specified Adapter class
318 '[' . __METHOD__
. '] Connection error (' . $e->getCode() . '|' .
319 $e->getMessage() . ')',
327 * Drop test table if it exists, so we can make all checks.
331 public function dropTestTable()
334 $this->db
->query('DROP TABLE IF EXISTS galette_test');
335 Analog
::log('Test table successfully dropped.', Analog
::DEBUG
);
336 } catch (\Exception
$e) {
338 'Cannot drop test table! ' . $e->getMessage(),
345 * Checks GRANT access for install time
347 * @param string $mode are we at install time (i) or update time (u) ?
349 * @return array containing each test. Each array entry could
350 * be either true or contains an exception of false if test did not
353 public function grantCheck($mode = 'i')
356 'Check for database rights (mode ' . $mode . ')',
369 $results['alter'] = false;
372 //can Galette CREATE tables?
374 $sql = 'CREATE TABLE galette_test (
375 test_id INTEGER NOT NULL,
376 test_text VARCHAR(20)
378 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
379 $results['create'] = true;
380 } catch (\Exception
$e) {
381 Analog
::log('Cannot CREATE TABLE', Analog
::WARNING
);
382 //if we cannot create tables, we cannot check other permissions
384 $results['create'] = $e;
387 //all those tests need the table to exists
390 //can Galette ALTER tables? (only for update mode)
392 $sql = 'ALTER TABLE galette_test ALTER test_text SET DEFAULT \'nothing\'';
393 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
394 $results['alter'] = true;
395 } catch (\Exception
$e) {
397 'Cannot ALTER TABLE | ' . $e->getMessage(),
400 $results['alter'] = $e;
404 //can Galette INSERT records ?
407 'test_text' => 'a simple text'
410 $insert = $this->sql
->insert('galette_test');
411 $insert->values($values);
413 $res = $this->execute($insert);
415 if ($res->count() === 1) {
416 $results['insert'] = true;
418 throw new \
Exception('No row inserted!');
420 } catch (\Exception
$e) {
422 'Cannot INSERT records | ' . $e->getMessage(),
425 //if we cannot insert records, some others tests cannot be done
427 $results['insert'] = $e;
430 //all those tests need that the first record exists
432 //can Galette UPDATE records ?
434 'test_text' => 'another simple text'
437 $update = $this->sql
->update('galette_test');
438 $update->set($values)->where(
439 array('test_id' => 1)
441 $res = $this->execute($update);
442 if ($res->count() === 1) {
443 $results['update'] = true;
445 throw new \
Exception('No row updated!');
447 } catch (\Exception
$e) {
449 'Cannot UPDATE records | ' . $e->getMessage(),
452 $results['update'] = $e;
455 //can Galette SELECT records ?
458 $select = $this->sql
->select('galette_test');
459 $select->where('test_id = 1');
460 $res = $this->execute($select);
461 $pass = $res->count() === 1;
464 $results['select'] = true;
466 throw new \
Exception('Select is empty!');
468 } catch (\Exception
$e) {
470 'Cannot SELECT records | ' . $e->getMessage(),
473 $results['select'] = $e;
476 //can Galette DELETE records ?
478 $delete = $this->sql
->delete('galette_test');
479 $delete->where(array('test_id' => 1));
480 $this->execute($delete);
481 $results['delete'] = true;
482 } catch (\Exception
$e) {
484 'Cannot DELETE records | ' . $e->getMessage(),
487 $results['delete'] = $e;
491 //can Galette DROP tables ?
493 $sql = 'DROP TABLE galette_test';
494 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
495 $results['drop'] = true;
496 } catch (\Exception
$e) {
498 'Cannot DROP TABLE | ' . $e->getMessage(),
501 $results['drop'] = $e;
509 * Get a list of Galette's tables
511 * @param string $prefix Specified table prefix, PREFIX_DB if null
515 public function getTables($prefix = null)
517 $metadata = \Laminas\Db\Metadata\Source\Factory
::createSourceFromAdapter($this->db
);
518 $tmp_tables_list = $metadata->getTableNames();
520 if ($prefix === null) {
524 $tables_list = array();
525 //filter table_list: we only want PREFIX_DB tables
526 foreach ($tmp_tables_list as $t) {
527 if (preg_match('/^' . $prefix . '/', $t)) {
535 * Get columns for a specified table
537 * @param string $table Table name
541 public function getColumns($table)
543 $metadata = \Laminas\Db\Metadata\Source\Factory
::createSourceFromAdapter($this->db
);
544 $table = $metadata->getTable(PREFIX_DB
. $table);
545 return $table->getColumns();
549 * Converts recursively database to UTF-8
551 * @param string $prefix Specified table prefix
552 * @param boolean $content_only Proceed only content (no table conversion)
556 public function convertToUTF($prefix = null, $content_only = false)
558 if ($this->isPostgres()) {
560 'Cannot change encoding on PostgreSQL database',
565 if ($prefix === null) {
570 $this->connection
->beginTransaction();
572 $tables = $this->getTables($prefix);
574 foreach ($tables as $table) {
575 if ($content_only === false) {
576 //Change whole table charset
577 //CONVERT TO instruction will take care of each fields,
578 //but converting data stay our problem.
579 $query = 'ALTER TABLE ' . $table .
580 ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci';
584 Adapter
::QUERY_MODE_EXECUTE
588 'Charset successfully changed for table `' . $table . '`',
594 if ($table != $prefix . 'pictures') {
595 $this->convertContentToUTF($prefix, $table);
598 $this->connection
->commit();
599 } catch (\Exception
$e) {
600 $this->connection
->rollBack();
602 'An error occurred while converting to utf table ' .
603 $table . ' (' . $e->getMessage() . ')',
610 * Converts dtabase content to UTF-8
612 * @param string $prefix Specified table prefix
613 * @param string $table the table we want to convert datas from
617 private function convertContentToUTF($prefix, $table)
621 $query = 'SET NAMES latin1';
624 Adapter
::QUERY_MODE_EXECUTE
626 } catch (\Exception
$e) {
628 'Cannot SET NAMES on table `' . $table . '`. ' .
635 $metadata = \Laminas\Db\Metadata\Source\Factory
::createSourceFromAdapter($this->db
);
636 $tbl = $metadata->getTable($table);
637 $columns = $tbl->getColumns();
638 $constraints = $tbl->getConstraints();
641 foreach ($constraints as $constraint) {
642 if ($constraint->getType() === 'PRIMARY KEY') {
643 $pkeys = $constraint->getColumns();
647 if (count($pkeys) == 0) {
648 //no primary key! How to do an update without that?
649 //Prior to 0.7, l10n and dynamic_fields tables does not
650 //contains any primary key. Since encoding conversion is done
651 //_before_ the SQL upgrade, we'll have to manually
653 if (preg_match('/' . $prefix . 'dynamic_fields/', $table) !== 0) {
660 } elseif (preg_match('/' . $prefix . 'l10n/', $table) !== 0) {
666 //not a know case, we do not perform any update.
667 throw new \
Exception(
668 'Cannot define primary key for table `' . $table .
674 $select = $this->sql
->select($table);
675 $results = $this->execute($select);
677 foreach ($results as $row) {
682 foreach ($pkeys as $k) {
683 $where[] = $k . ' = "' . $row->$k . '"';
687 foreach ($row as $key => $value) {
688 $data[$key] = $value;
691 //finally, update data!
692 $update = $this->sql
->update($table);
693 $update->set($data)->where($where);
694 $this->execute($update);
696 } catch (\Exception
$e) {
698 'An error occurred while converting contents to UTF-8 for table ' .
699 $table . ' (' . $e->getMessage() . ')',
706 * Is current database using Postgresql?
710 public function isPostgres()
712 return $this->type_db
=== self
::PGSQL
;
716 * Instanciate a select query
718 * @param string $table Table name, without prefix
719 * @param string $alias Tables alias, optionnal
723 public function select($table, $alias = null)
725 if ($alias === null) {
726 return $this->sql
->select(
730 return $this->sql
->select(
732 $alias => PREFIX_DB
. $table
739 * Instanciate an insert query
741 * @param string $table Table name, without prefix
745 public function insert($table)
747 return $this->sql
->insert(
753 * Instanciate an update query
755 * @param string $table Table name, without prefix
759 public function update($table)
761 return $this->sql
->update(
767 * Instanciate a delete query
769 * @param string $table Table name, without prefix
773 public function delete($table)
775 return $this->sql
->delete(
781 * Execute query string
783 * @param SqlInterface $sql SQL object
787 public function execute($sql)
790 $query_string = $this->sql
->getSqlStringForSqlObject($sql);
791 $this->last_query
= $query_string;
793 'Executing query: ' . $query_string,
796 return $this->db
->query(
798 Adapter
::QUERY_MODE_EXECUTE
800 } catch (\Exception
$e) {
801 $msg = 'Query error: ';
802 if (isset($query_string)) {
803 $msg .= $query_string;
806 $msg . ' ' . $e->__toString(),
809 if ($this->isDuplicateException($e)) {
810 throw new \
OverflowException('Duplicate entry', 0, $e);
817 * Global getter method
819 * @param string $name name of the variable we want to retrieve
823 public function __get($name)
833 return $this->db
->getDriver();
836 return $this->db
->getDriver()->getConnection();
839 return $this->db
->getPlatform();
842 return $this->last_query
;
845 return $this->type_db
;
851 * Get database information
855 public function getInfos()
865 if ($this->isPostgres()) {
866 $infos['engine'] = 'PostgreSQL';
867 $sql = 'SHOW server_version';
868 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
870 $infos['version'] = $result['server_version'];
872 $sql = 'SELECT pg_database_size(\'' . NAME_DB
. '\')';
873 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
875 $infos['size'] = (string)round($result['pg_database_size'] / 1024 / 1024);
877 $sql = 'SELECT @@sql_mode as mode, @@version AS version, @@version_comment AS version_comment';
878 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
881 $infos['engine'] = $result['version_comment'];
882 $infos['version'] = $result['version'];
883 $infos['sql_mode'] = $result['mode'];
885 $size_sql = 'SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS dbsize' .
886 ' FROM information_schema.tables WHERE table_schema="' . NAME_DB
. '"';
887 $result = $this->db
->query($size_sql, Adapter
::QUERY_MODE_EXECUTE
)
890 $infos['size'] = $result['dbsize'];
897 * Handle sequence on PostgreSQL
899 * When inserting a value on a field with a sequence,
900 * this one is not incremented.
901 * This happens when installing system values (for status, titles, ...)
903 * @see https://bugs.galette.eu/issues/1158
904 * @see https://bugs.galette.eu/issues/1374
906 * @param string $table Table name
907 * @param integer $expected Expected value
911 public function handleSequence($table, $expected)
913 if ($this->isPostgres()) {
914 //check for Postgres sequence
915 //see https://bugs.galette.eu/issues/1158
916 //see https://bugs.galette.eu/issues/1374
917 $seq = $table . '_id_seq';
919 $select = $this->select($seq);
920 $select->columns(['last_value']);
921 $results = $this->execute($select);
922 $result = $results->current();
923 if ($result->last_value
< $expected) {
925 'SELECT setval(\'' . PREFIX_DB
. $seq . '\', ' . $expected . ')',
926 Adapter
::QUERY_MODE_EXECUTE
933 * Check if current exception is on a duplicate key
935 * @param \Exception $exception Exception to check
939 public function isDuplicateException($exception)
941 return $exception instanceof \PDOException
943 (!$this->isPostgres() && $exception->getCode() == 23000)
944 ||
($this->isPostgres() && $exception->getCode() == 23505)