2 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
9 * Copyright © 2011-2014 The Galette Team
11 * This file is part of Galette (http://galette.tuxfamily.org).
13 * Galette is free software: you can redistribute it and/or modify
14 * it under the terms of the GNU General Public License as published by
15 * the Free Software Foundation, either version 3 of the License, or
16 * (at your option) any later version.
18 * Galette is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License
24 * along with Galette. If not, see <http://www.gnu.org/licenses/>.
29 * @author Johan Cwiklinski <johan@x-tnd.be>
30 * @copyright 2011-2014 The Galette Team
31 * @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\Sql\Sql
;
49 * @author Johan Cwiklinski <johan@x-tnd.be>
50 * @copyright 2011-2014 The Galette Team
51 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL License 3.0 or (at your option) any later version
52 * @link http://framework.zend.com/apidoc/2.2/namespaces/Zend.Db.html
53 * @since Available since 0.7dev - 2011-07-27
62 const MYSQL
= 'mysql';
63 const PGSQL
= 'pgsql';
65 const MYSQL_DEFAULT_PORT
= 3306;
66 const PGSQL_DEFAULT_PORT
= 5432;
71 * @param array $dsn Connection information
72 * If not set, database constants will be used.
74 public function __construct($dsn = null)
78 if ($dsn !== null && is_array($dsn)) {
79 $_type_db = $dsn['TYPE_DB'];
80 $_host_db = $dsn['HOST_DB'];
81 $_port_db = $dsn['PORT_DB'];
82 $_user_db = $dsn['USER_DB'];
83 $_pwd_db = $dsn['PWD_DB'];
84 $_name_db = $dsn['NAME_DB'];
95 if ($_type_db === self
::MYSQL
) {
97 } elseif ($_type_db === self
::PGSQL
) {
100 throw new \
Exception("Type $_type_db not known (dsn: $_user_db@$_host_db(:$_port_db)/$_name_db)");
103 $this->type_db
= $_type_db;
104 $this->options
= array(
106 'hostname' => $_host_db,
108 'username' => $_user_db,
109 'password' => $_pwd_db,
110 'database' => $_name_db
112 if ($_type_db === self
::MYSQL
&& !defined('NON_UTF_DBCONNECT')) {
113 $this->options
['charset'] = 'utf8';
116 $this->doConnection();
117 } catch (\Exception
$e) {
118 // perhaps factory() failed to load the specified Adapter class
120 '[Db] Error (' . $e->getCode() . '|' .
121 $e->getMessage() . ')',
129 * Do database connection
133 private function doConnection()
135 $this->db
= new Adapter($this->options
);
136 $this->db
->getDriver()->getConnection()->connect();
137 $this->sql
= new Sql($this->db
);
139 if (!$this->isPostgres()) {
140 $this->db
->query("SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
144 '[Db] Database connection was successfull!',
150 * To store Db in session
154 public function __sleep()
156 return ['type_db', 'options'];
160 * Connect again to the database on wakeup
164 public function __wakeup()
166 $this->doConnection();
170 * Retrieve current database version
172 * @param boolean $check_table Check if table exists, defaults to false
176 * @throw LogicException
178 public function getDbVersion($check_table = false)
181 if ($check_table === true) {
182 $exists = count($this->getTables(PREFIX_DB
. 'database')) === 1;
187 if ($exists === true) {
188 $select = $this->select('database');
193 $results = $this->execute($select);
194 $result = $results->current();
195 return number_format(
204 } catch (\Exception
$e) {
206 'Cannot check database version: ' . $e->getMessage(),
209 throw new \
LogicException('Cannot check database version');
214 * Check if database version suits our needs
218 public function checkDbVersion()
220 if (GALETTE_MODE
=== 'DEV') {
222 'Database version not checked in DEV mode.',
229 return $this->getDbVersion() === GALETTE_DB_VERSION
;
230 } catch (\LogicException
$e) {
236 * Peform a select query on the whole table
238 * @param string $table Table name
242 public function selectAll($table)
244 return $this->db
->query(
245 'SELECT * FROM ' . PREFIX_DB
. $table,
246 Adapter
::QUERY_MODE_EXECUTE
251 * Test if database can be contacted. Mostly used for installation
253 * @param string $type db type
254 * @param string $user database's user
255 * @param string $pass password for the user
256 * @param string $host which host we want to connect to
257 * @param string $port which tcp port we want to connect to
258 * @param string $db database name
260 * @return true|array true if connection was successfull,
261 * an array with some infos otherwise
263 public static function testConnectivity(
273 if ($type === self
::MYSQL
) {
274 $_type = 'Pdo_Mysql';
275 } elseif ($type === self
::PGSQL
) {
276 $_type = 'Pdo_Pgsql';
278 throw new \Exception
;
290 $_db = new Adapter($_options);
291 $_db->getDriver()->getConnection()->connect();
294 '[' . __METHOD__
. '] Database connection was successfull!',
298 } catch (\Exception
$e) {
299 // perhaps failed to load the specified Adapter class
301 '[' . __METHOD__
. '] Connection error (' . $e->getCode() . '|' .
302 $e->getMessage() . ')',
310 * Drop test table if it exists, so we can make all checks.
314 public function dropTestTable()
317 $this->db
->query('DROP TABLE IF EXISTS galette_test');
318 Analog
::log('Test table successfully dropped.', Analog
::DEBUG
);
319 } catch (\Exception
$e) {
321 'Cannot drop test table! ' . $e->getMessage(),
328 * Checks GRANT access for install time
330 * @param char $mode are we at install time (i) or update time (u) ?
332 * @return array containing each test. Each array entry could
333 * be either true or contains an exception of false if test did not
336 public function grantCheck($mode = 'i')
339 'Check for database rights (mode ' . $mode . ')',
352 $results['alter'] = false;
355 //can Galette CREATE tables?
357 $sql = 'CREATE TABLE galette_test (
358 test_id INTEGER NOT NULL,
359 test_text VARCHAR(20)
361 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
362 $results['create'] = true;
363 } catch (\Exception
$e) {
364 Analog
::log('Cannot CREATE TABLE', Analog
::WARNING
);
365 //if we cannot create tables, we cannot check other permissions
367 $results['create'] = $e;
370 //all those tests need the table to exists
373 //can Galette ALTER tables? (only for update mode)
375 $sql = 'ALTER TABLE galette_test ALTER test_text SET DEFAULT \'nothing\'';
376 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
377 $results['alter'] = true;
378 } catch (\Exception
$e) {
380 'Cannot ALTER TABLE | ' . $e->getMessage(),
383 $results['alter'] = $e;
387 //can Galette INSERT records ?
390 'test_text' => 'a simple text'
393 $insert = $this->sql
->insert('galette_test');
394 $insert->values($values);
396 $res = $this->execute($insert);
398 if ($res->count() === 1) {
399 $results['insert'] = true;
401 throw new \
Exception('No row inserted!');
403 } catch (\Exception
$e) {
405 'Cannot INSERT records | ' . $e->getMessage(),
408 //if we cannot insert records, some others tests cannot be done
410 $results['insert'] = $e;
413 //all those tests need that the first record exists
415 //can Galette UPDATE records ?
417 'test_text' => 'another simple text'
420 $update = $this->sql
->update('galette_test');
421 $update->set($values)->where(
422 array('test_id' => 1)
424 $res = $this->execute($update);
425 if ($res->count() === 1) {
426 $results['update'] = true;
428 throw new \
Exception('No row updated!');
430 } catch (\Exception
$e) {
432 'Cannot UPDATE records | ' . $e->getMessage(),
435 $results['update'] = $e;
438 //can Galette SELECT records ?
441 $select = $this->sql
->select('galette_test');
442 $select->where('test_id = 1');
443 $res = $this->execute($select);
444 $pass = $res->count() === 1;
447 $results['select'] = true;
449 throw new \
Exception('Select is empty!');
451 } catch (\Exception
$e) {
453 'Cannot SELECT records | ' . $e->getMessage(),
456 $results['select'] = $e;
459 //can Galette DELETE records ?
461 $delete = $this->sql
->delete('galette_test');
462 $delete->where(array('test_id' => 1));
463 $this->execute($delete);
464 $results['delete'] = true;
465 } catch (\Exception
$e) {
467 'Cannot DELETE records | ' . $e->getMessage(),
470 $results['delete'] = $e;
474 //can Galette DROP tables ?
476 $sql = 'DROP TABLE galette_test';
477 $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
478 $results['drop'] = true;
479 } catch (\Exception
$e) {
481 'Cannot DROP TABLE | ' . $e->getMessage(),
484 $results['drop'] = $e;
492 * Get a list of Galette's tables
494 * @param string $prefix Specified table prefix, PREFIX_DB if null
498 public function getTables($prefix = null)
500 $metadata = new \Laminas\Db\Metadata\
Metadata($this->db
);
501 $tmp_tables_list = $metadata->getTableNames();
503 if ($prefix === null) {
507 $tables_list = array();
508 //filter table_list: we only want PREFIX_DB tables
509 foreach ($tmp_tables_list as $t) {
510 if (preg_match('/^' . $prefix . '/', $t)) {
518 * Get columns for a specified table
520 * @param string $table Table name
524 public function getColumns($table)
526 $metadata = new \Laminas\Db\Metadata\
Metadata($this->db
);
527 $table = $metadata->getTable(PREFIX_DB
. $table);
528 return $table->getColumns();
532 * Converts recursively database to UTF-8
534 * @param string $prefix Specified table prefix
535 * @param boolean $content_only Proceed only content (no table conversion)
539 public function convertToUTF($prefix = null, $content_only = false)
541 if ($this->isPostgres()) {
543 'Cannot change encoding on PostgreSQL database',
548 if ($prefix === null) {
553 $this->connection
->beginTransaction();
555 $tables = $this->getTables($prefix);
557 foreach ($tables as $table) {
558 if ($content_only === false) {
559 //Change whole table charset
560 //CONVERT TO instruction will take care of each fields,
561 //but converting data stay our problem.
562 $query = 'ALTER TABLE ' . $table .
563 ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci';
567 Adapter
::QUERY_MODE_EXECUTE
571 'Charset successfully changed for table `' . $table . '`',
577 if ($table != $prefix . 'pictures') {
578 $this->convertContentToUTF($prefix, $table);
581 $this->connection
->commit();
582 } catch (\Exception
$e) {
583 $this->connection
->rollBack();
585 'An error occurred while converting to utf table ' .
586 $table . ' (' . $e->getMessage() . ')',
593 * Converts dtabase content to UTF-8
595 * @param string $prefix Specified table prefix
596 * @param string $table the table we want to convert datas from
600 private function convertContentToUTF($prefix, $table)
604 $query = 'SET NAMES latin1';
607 Adapter
::QUERY_MODE_EXECUTE
609 } catch (\Exception
$e) {
611 'Cannot SET NAMES on table `' . $table . '`. ' .
618 $metadata = new \Laminas\Db\Metadata\
Metadata($this->db
);
619 $tbl = $metadata->getTable($table);
620 $columns = $tbl->getColumns();
621 $constraints = $tbl->getConstraints();
624 foreach ($constraints as $constraint) {
625 if ($constraint->getType() === 'PRIMARY KEY') {
626 $pkeys = $constraint->getColumns();
630 if (count($pkeys) == 0) {
631 //no primary key! How to do an update without that?
632 //Prior to 0.7, l10n and dynamic_fields tables does not
633 //contains any primary key. Since encoding conversion is done
634 //_before_ the SQL upgrade, we'll have to manually
636 if (preg_match('/' . $prefix . 'dynamic_fields/', $table) !== 0) {
643 } elseif (preg_match('/' . $prefix . 'l10n/', $table) !== 0) {
649 //not a know case, we do not perform any update.
650 throw new \
Exception(
651 'Cannot define primary key for table `' . $table .
657 $select = $this->sql
->select($table);
658 $results = $this->execute($select);
660 foreach ($results as $row) {
665 foreach ($pkeys as $k) {
666 $where[] = $k . ' = "' . $row->$k . '"';
670 foreach ($row as $key => $value) {
671 $data[$key] = $value;
674 //finally, update data!
675 $update = $this->sql
->update($table);
676 $update->set($data)->where($where);
677 $this->execute($update);
679 } catch (\Exception
$e) {
681 'An error occurred while converting contents to UTF-8 for table ' .
682 $table . ' (' . $e->getMessage() . ')',
689 * Is current database using Postgresql?
693 public function isPostgres()
695 return $this->type_db
=== self
::PGSQL
;
699 * Instanciate a select query
701 * @param string $table Table name, without prefix
702 * @param string $alias Tables alias, optionnal
706 public function select($table, $alias = null)
708 if ($alias === null) {
709 return $this->sql
->select(
713 return $this->sql
->select(
715 $alias => PREFIX_DB
. $table
722 * Instanciate an insert query
724 * @param string $table Table name, without prefix
728 public function insert($table)
730 return $this->sql
->insert(
736 * Instanciate an update query
738 * @param string $table Table name, without prefix
742 public function update($table)
744 return $this->sql
->update(
750 * Instanciate a delete query
752 * @param string $table Table name, without prefix
756 public function delete($table)
758 return $this->sql
->delete(
764 * Execute query string
766 * @param SqlInterface $sql SQL object
770 public function execute($sql)
773 $query_string = $this->sql
->getSqlStringForSqlObject($sql);
774 $this->_last_query
= $query_string;
776 'Executing query: ' . $query_string,
779 return $this->db
->query(
781 Adapter
::QUERY_MODE_EXECUTE
783 } catch (\Exception
$e) {
784 $msg = 'Query error: ';
785 if (isset($query_string)) {
786 $msg .= $query_string;
789 $msg . ' ' . $e->__toString(),
792 if ($this->isDuplicateException($e)) {
793 throw new \
OverflowException('Duplicate entry', 0, $e);
800 * Global getter method
802 * @param string $name name of the variable we want to retrieve
806 public function __get($name)
816 return $this->db
->getDriver();
819 return $this->db
->getDriver()->getConnection();
822 return $this->db
->getPlatform();
825 return $this->_last_query
;
828 return $this->type_db
;
834 * Get database information
838 public function getInfos()
848 if ($this->isPostgres()) {
849 $infos['engine'] = 'PostgreSQL';
850 $sql = 'SHOW server_version';
851 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
853 $infos['version'] = $result['server_version'];
855 $sql = 'SELECT pg_database_size(\'' . NAME_DB
. '\')';
856 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
858 $infos['size'] = (string)round($result['pg_database_size']/1024/1024);
860 $sql = 'SELECT @@sql_mode as mode, @@version AS version, @@version_comment AS version_comment';
861 $result = $this->db
->query($sql, Adapter
::QUERY_MODE_EXECUTE
)
864 $infos['engine'] = $result['version_comment'];
865 $infos['version'] = $result['version'];
866 $infos['sql_mode'] = $result['mode'];
868 $size_sql = 'SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS dbsize' .
869 ' FROM information_schema.tables WHERE table_schema="' . NAME_DB
. '"';
870 $result = $this->db
->query($size_sql, Adapter
::QUERY_MODE_EXECUTE
)
873 $infos['size'] = $result['dbsize'];
880 * Handle sequence on PostgreSQL
882 * When inserting a value on a field with a sequence,
883 * this one is not incremented.
884 * This happens when installing system values (for status, titles, ...)
886 * @see https://bugs.galette.eu/issues/1158
887 * @see https://bugs.galette.eu/issues/1374
889 * @param sting $table Table name
890 * @param intger $expected Expected value
894 public function handleSequence($table, $expected)
896 if ($this->isPostgres()) {
897 //check for Postgres sequence
898 //see https://bugs.galette.eu/issues/1158
899 //see https://bugs.galette.eu/issues/1374
900 $seq = $table . '_id_seq';
902 $select = $this->select($seq);
903 $select->columns(['last_value']);
904 $results = $this->execute($select);
905 $result = $results->current();
906 if ($result->last_value
< $expected) {
908 'SELECT setval(\'' . PREFIX_DB
. $seq . '\', ' . $expected . ')',
909 Adapter
::QUERY_MODE_EXECUTE
916 * Check if current exception is on a duplicate key
918 * @param Exception $exception Exception to check
922 public function isDuplicateException($exception)
924 return $exception instanceof \PDOException
926 (!$this->isPostgres() && $exception->getCode() == 23000)
927 ||
($this->isPostgres() && $exception->getCode() == 23505)