dolibarr  18.0.0
pgsql.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2001 Fabien Seisen <seisen@linuxfr.org>
3  * Copyright (C) 2002-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
4  * Copyright (C) 2004-2014 Laurent Destailleur <eldy@users.sourceforge.net>
5  * Copyright (C) 2004 Sebastien Di Cintio <sdicintio@ressource-toi.org>
6  * Copyright (C) 2004 Benoit Mortier <benoit.mortier@opensides.be>
7  * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com>
8  * Copyright (C) 2012 Yann Droneaud <yann@droneaud.fr>
9  * Copyright (C) 2012 Florian Henry <florian.henry@open-concept.pro>
10  * Copyright (C) 2015 Marcos García <marcosgdf@gmail.com>
11  *
12  * This program is free software; you can redistribute it and/or modify
13  * it under the terms of the GNU General Public License as published by
14  * the Free Software Foundation; either version 3 of the License, or
15  * (at your option) any later version.
16  *
17  * This program is distributed in the hope that it will be useful,
18  * but WITHOUT ANY WARRANTY; without even the implied warranty of
19  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20  * GNU General Public License for more details.
21  *
22  * You should have received a copy of the GNU General Public License
23  * along with this program. If not, see <https://www.gnu.org/licenses/>.
24  */
25 
31 require_once DOL_DOCUMENT_ROOT.'/core/db/DoliDB.class.php';
32 
36 class DoliDBPgsql extends DoliDB
37 {
39  public $type = 'pgsql'; // Name of manager
40 
42  const LABEL = 'PostgreSQL'; // Label of manager
43 
45  public $forcecharset = 'UTF8'; // Can't be static as it may be forced with a dynamic value
46 
48  public $forcecollate = ''; // Can't be static as it may be forced with a dynamic value
49 
51  const VERSIONMIN = '9.0.0'; // Version min database
52 
54  private $_results;
55 
56  public $unescapeslashquot;
57  public $standard_conforming_strings;
58 
59 
71  public function __construct($type, $host, $user, $pass, $name = '', $port = 0)
72  {
73  global $conf, $langs;
74 
75  // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static
76  if (!empty($conf->db->character_set)) {
77  $this->forcecharset = $conf->db->character_set;
78  }
79  if (!empty($conf->db->dolibarr_main_db_collation)) {
80  $this->forcecollate = $conf->db->dolibarr_main_db_collation;
81  }
82 
83  $this->database_user = $user;
84  $this->database_host = $host;
85  $this->database_port = $port;
86 
87  $this->transaction_opened = 0;
88 
89  //print "Name DB: $host,$user,$pass,$name<br>";
90 
91  if (!function_exists("pg_connect")) {
92  $this->connected = false;
93  $this->ok = false;
94  $this->error = "Pgsql PHP functions are not available in this version of PHP";
95  dol_syslog(get_class($this)."::DoliDBPgsql : Pgsql PHP functions are not available in this version of PHP", LOG_ERR);
96  return $this->ok;
97  }
98 
99  if (!$host) {
100  $this->connected = false;
101  $this->ok = false;
102  $this->error = $langs->trans("ErrorWrongHostParameter");
103  dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Connect, wrong host parameters", LOG_ERR);
104  return $this->ok;
105  }
106 
107  // Essai connexion serveur
108  //print "$host, $user, $pass, $name, $port";
109  $this->db = $this->connect($host, $user, $pass, $name, $port);
110 
111  if ($this->db) {
112  $this->connected = true;
113  $this->ok = true;
114  } else {
115  // host, login ou password incorrect
116  $this->connected = false;
117  $this->ok = false;
118  $this->error = 'Host, login or password incorrect';
119  dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Connect ".$this->error.'. Failed to connect to host='.$host.' port='.$port.' user='.$user, LOG_ERR);
120  }
121 
122  // Si connexion serveur ok et si connexion base demandee, on essaie connexion base
123  if ($this->connected && $name) {
124  if ($this->select_db($name)) {
125  $this->database_selected = true;
126  $this->database_name = $name;
127  $this->ok = true;
128  } else {
129  $this->database_selected = false;
130  $this->database_name = '';
131  $this->ok = false;
132  $this->error = $this->error();
133  dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Select_db ".$this->error, LOG_ERR);
134  }
135  } else {
136  // Pas de selection de base demandee, ok ou ko
137  $this->database_selected = false;
138  }
139 
140  return $this->ok;
141  }
142 
143 
152  public static function convertSQLFromMysql($line, $type = 'auto', $unescapeslashquot = false)
153  {
154  global $conf;
155 
156  // Removed empty line if this is a comment line for SVN tagging
157  if (preg_match('/^--\s\$Id/i', $line)) {
158  return '';
159  }
160  // Return line if this is a comment
161  if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) {
162  return $line;
163  }
164  if ($line != "") {
165  // group_concat support (PgSQL >= 9.0)
166  // Replace group_concat(x) or group_concat(x SEPARATOR ',') with string_agg(x, ',')
167  $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line);
168  $line = preg_replace('/ SEPARATOR/i', ',', $line);
169  $line = preg_replace('/STRING_AGG\(([^,\)]+)\)/i', 'STRING_AGG(\\1, \',\')', $line);
170  //print $line."\n";
171 
172  if ($type == 'auto') {
173  if (preg_match('/ALTER TABLE/i', $line)) {
174  $type = 'dml';
175  } elseif (preg_match('/CREATE TABLE/i', $line)) {
176  $type = 'dml';
177  } elseif (preg_match('/DROP TABLE/i', $line)) {
178  $type = 'dml';
179  }
180  }
181 
182  $line = preg_replace('/ as signed\)/i', ' as integer)', $line);
183 
184  if ($type == 'dml') {
185  $reg = array();
186 
187  $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space
188 
189  // we are inside create table statement so lets process datatypes
190  if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence
191  $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line);
192  $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line);
193  $line = preg_replace('/,$/', '', $line);
194  }
195 
196  // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..."
197  if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) {
198  $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 SERIAL PRIMARY KEY', $line);
199  //$line = "-- ".$line." replaced by --\n".$newline;
200  $line = $newline;
201  }
202 
203  if (preg_match('/[\s\t\(]*(\w*)[\s\t]+bigint.*auto_increment/i', $line, $reg)) {
204  $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+bigint.*auto_increment[^,]*/i', '\\1 \\2 BIGSERIAL PRIMARY KEY', $line);
205  //$line = "-- ".$line." replaced by --\n".$newline;
206  $line = $newline;
207  }
208 
209  // tinyint type conversion
210  $line = preg_replace('/tinyint\(?[0-9]*\)?/', 'smallint', $line);
211  $line = preg_replace('/tinyint/i', 'smallint', $line);
212 
213  // nuke unsigned
214  $line = preg_replace('/(int\w+|smallint|bigint)\s+unsigned/i', '\\1', $line);
215 
216  // blob -> text
217  $line = preg_replace('/\w*blob/i', 'text', $line);
218 
219  // tinytext/mediumtext -> text
220  $line = preg_replace('/tinytext/i', 'text', $line);
221  $line = preg_replace('/mediumtext/i', 'text', $line);
222  $line = preg_replace('/longtext/i', 'text', $line);
223 
224  $line = preg_replace('/text\([0-9]+\)/i', 'text', $line);
225 
226  // change not null datetime field to null valid ones
227  // (to support remapping of "zero time" to null
228  $line = preg_replace('/datetime not null/i', 'datetime', $line);
229  $line = preg_replace('/datetime/i', 'timestamp', $line);
230 
231  // double -> numeric
232  $line = preg_replace('/^double/i', 'numeric', $line);
233  $line = preg_replace('/(\s*)double/i', '\\1numeric', $line);
234  // float -> numeric
235  $line = preg_replace('/^float/i', 'numeric', $line);
236  $line = preg_replace('/(\s*)float/i', '\\1numeric', $line);
237 
238  //Check tms timestamp field case (in Mysql this field is defautled to now and
239  // on update defaulted by now
240  $line = preg_replace('/(\s*)tms(\s*)timestamp/i', '\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line);
241 
242  // nuke DEFAULT CURRENT_TIMESTAMP
243  $line = preg_replace('/(\s*)DEFAULT(\s*)CURRENT_TIMESTAMP/i', '\\1', $line);
244 
245  // nuke ON UPDATE CURRENT_TIMESTAMP
246  $line = preg_replace('/(\s*)ON(\s*)UPDATE(\s*)CURRENT_TIMESTAMP/i', '\\1', $line);
247 
248  // unique index(field1,field2)
249  if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) {
250  $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line);
251  }
252 
253  // We remove end of requests "AFTER fieldxxx"
254  $line = preg_replace('/\sAFTER [a-z0-9_]+/i', '', $line);
255 
256  // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX
257  $line = preg_replace('/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i', 'DROP INDEX', $line);
258 
259  // Translate order to rename fields
260  if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) {
261  $line = "-- ".$line." replaced by --\n";
262  $line .= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3];
263  }
264 
265  // Translate order to modify field format
266  if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) {
267  $line = "-- ".$line." replaced by --\n";
268  $newreg3 = $reg[3];
269  $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3);
270  $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3);
271  $newreg3 = preg_replace('/ NULL/i', '', $newreg3);
272  $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3);
273  $newreg3 = preg_replace('/ DEFAULT \'?[0-9a-zA-Z_@]*\'?/i', '', $newreg3);
274  $line .= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3;
275  // TODO Add alter to set default value or null/not null if there is this in $reg[3]
276  }
277 
278  // alter table add primary key (field1, field2 ...) -> We remove the primary key name not accepted by PostGreSQL
279  // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity)
280  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) {
281  $line = "-- ".$line." replaced by --\n";
282  $line .= "ALTER TABLE ".$reg[1]." ADD PRIMARY KEY (".$reg[3];
283  }
284 
285  // Translate order to drop primary keys
286  // ALTER TABLE llx_dolibarr_modules DROP PRIMARY KEY pk_xxx
287  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+PRIMARY\s+KEY\s*([^;]+)$/i', $line, $reg)) {
288  $line = "-- ".$line." replaced by --\n";
289  $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2];
290  }
291 
292  // Translate order to drop foreign keys
293  // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx
294  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) {
295  $line = "-- ".$line." replaced by --\n";
296  $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2];
297  }
298 
299  // Translate order to add foreign keys
300  // ALTER TABLE llx_tablechild ADD CONSTRAINT fk_tablechild_fk_fieldparent FOREIGN KEY (fk_fieldparent) REFERENCES llx_tableparent (rowid)
301  if (preg_match('/ALTER\s+TABLE\s+(.*)\s*ADD CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) {
302  $line = preg_replace('/;$/', '', $line);
303  $line .= " DEFERRABLE INITIALLY IMMEDIATE;";
304  }
305 
306  // alter table add [unique] [index] (field1, field2 ...)
307  // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version)
308  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) {
309  $fieldlist = $reg[4];
310  $idxname = $reg[3];
311  $tablename = $reg[1];
312  $line = "-- ".$line." replaced by --\n";
313  $line .= "CREATE ".(preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")";
314  }
315  }
316 
317  // To have postgresql case sensitive
318  $count_like = 0;
319  $line = str_replace(' LIKE \'', ' ILIKE \'', $line, $count_like);
320  if (!empty($conf->global->PSQL_USE_UNACCENT) && $count_like > 0) {
321  // @see https://docs.postgresql.fr/11/unaccent.html : 'unaccent()' function must be installed before
322  $line = preg_replace('/\s+(\(+\s*)([a-zA-Z0-9\-\_\.]+) ILIKE /', ' \1unaccent(\2) ILIKE ', $line);
323  }
324 
325  $line = str_replace(' LIKE BINARY \'', ' LIKE \'', $line);
326 
327  // Replace INSERT IGNORE into INSERT
328  $line = preg_replace('/^INSERT IGNORE/', 'INSERT', $line);
329 
330  // Delete using criteria on other table must not declare twice the deleted table
331  // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable
332  if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) {
333  if ($reg[1] == $reg[2]) { // If same table, we remove second one
334  $line = preg_replace('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', 'DELETE FROM \\1 USING \\3', $line);
335  }
336  }
337 
338  // Remove () in the tables in FROM if 1 table
339  $line = preg_replace('/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i', 'FROM \\1', $line);
340  //print $line."\n";
341 
342  // Remove () in the tables in FROM if 2 table
343  $line = preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i', 'FROM \\1, \\2', $line);
344  //print $line."\n";
345 
346  // Remove () in the tables in FROM if 3 table
347  $line = preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i', 'FROM \\1, \\2, \\3', $line);
348  //print $line."\n";
349 
350  // Remove () in the tables in FROM if 4 table
351  $line = preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i', 'FROM \\1, \\2, \\3, \\4', $line);
352  //print $line."\n";
353 
354  // Remove () in the tables in FROM if 5 table
355  $line = preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i', 'FROM \\1, \\2, \\3, \\4, \\5', $line);
356  //print $line."\n";
357 
358  // Replace espacing \' by ''.
359  // By default we do not (should be already done by db->escape function if required
360  // except for sql insert in data file that are mysql escaped so we removed them to
361  // be compatible with standard_conforming_strings=on that considers \ as ordinary character).
362  if ($unescapeslashquot) {
363  $line = preg_replace("/\\\'/", "''", $line);
364  }
365 
366  //print "type=".$type." newline=".$line."<br>\n";
367  }
368 
369  return $line;
370  }
371 
372  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
381  public function select_db($database)
382  {
383  // phpcs:enable
384  if ($database == $this->database_name) {
385  return true;
386  } else {
387  return false;
388  }
389  }
390 
402  public function connect($host, $login, $passwd, $name, $port = 0)
403  {
404  // use pg_pconnect() instead of pg_connect() if you want to use persistent connection costing 1ms, instead of 30ms for non persistent
405 
406  $this->db = false;
407 
408  // connections parameters must be protected (only \ and ' according to pg_connect() manual)
409  $host = str_replace(array("\\", "'"), array("\\\\", "\\'"), $host);
410  $login = str_replace(array("\\", "'"), array("\\\\", "\\'"), $login);
411  $passwd = str_replace(array("\\", "'"), array("\\\\", "\\'"), $passwd);
412  $name = str_replace(array("\\", "'"), array("\\\\", "\\'"), $name);
413  $port = str_replace(array("\\", "'"), array("\\\\", "\\'"), $port);
414 
415  if (!$name) {
416  $name = "postgres"; // When try to connect using admin user
417  }
418 
419  // try first Unix domain socket (local)
420  if ((!empty($host) && $host == "socket") && !defined('NOLOCALSOCKETPGCONNECT')) {
421  $con_string = "dbname='".$name."' user='".$login."' password='".$passwd."'"; // $name may be empty
422  try {
423  $this->db = @pg_connect($con_string);
424  } catch (Exception $e) {
425  // No message
426  }
427  }
428 
429  // if local connection failed or not requested, use TCP/IP
430  if (empty($this->db)) {
431  if (!$host) {
432  $host = "localhost";
433  }
434  if (!$port) {
435  $port = 5432;
436  }
437 
438  $con_string = "host='".$host."' port='".$port."' dbname='".$name."' user='".$login."' password='".$passwd."'";
439  try {
440  $this->db = @pg_connect($con_string);
441  } catch (Exception $e) {
442  print $e->getMessage();
443  }
444  }
445 
446  // now we test if at least one connect method was a success
447  if ($this->db) {
448  $this->database_name = $name;
449  pg_set_error_verbosity($this->db, PGSQL_ERRORS_VERBOSE); // Set verbosity to max
450  pg_query($this->db, "set datestyle = 'ISO, YMD';");
451  }
452 
453  return $this->db;
454  }
455 
461  public function getVersion()
462  {
463  $resql = $this->query('SHOW server_version');
464  if ($resql) {
465  $liste = $this->fetch_array($resql);
466  return $liste['server_version'];
467  }
468  return '';
469  }
470 
476  public function getDriverInfo()
477  {
478  return 'pgsql php driver';
479  }
480 
487  public function close()
488  {
489  if ($this->db) {
490  if ($this->transaction_opened > 0) {
491  dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
492  }
493  $this->connected = false;
494  return pg_close($this->db);
495  }
496  return false;
497  }
498 
508  public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0)
509  {
510  global $conf, $dolibarr_main_db_readonly;
511 
512  $query = trim($query);
513 
514  // Convert MySQL syntax to PostgresSQL syntax
515  $query = $this->convertSQLFromMysql($query, $type, ($this->unescapeslashquot && $this->standard_conforming_strings));
516  //print "After convertSQLFromMysql:\n".$query."<br>\n";
517 
518  if (!empty($conf->global->MAIN_DB_AUTOFIX_BAD_SQL_REQUEST)) {
519  // Fix bad formed requests. If request contains a date without quotes, we fix this but this should not occurs.
520  $loop = true;
521  while ($loop) {
522  if (preg_match('/([^\'])([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9])/', $query)) {
523  $query = preg_replace('/([^\'])([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9])/', '\\1\'\\2\'', $query);
524  dol_syslog("Warning: Bad formed request converted into ".$query, LOG_WARNING);
525  } else {
526  $loop = false;
527  }
528  }
529  }
530 
531  if ($usesavepoint && $this->transaction_opened) {
532  @pg_query($this->db, 'SAVEPOINT mysavepoint');
533  }
534 
535  if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) {
536  $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks
537  dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
538  }
539  if (empty($query)) {
540  return false; // Return false = error if empty request
541  }
542 
543  if (!empty($dolibarr_main_db_readonly)) {
544  if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
545  $this->lasterror = 'Application in read-only mode';
546  $this->lasterrno = 'APPREADONLY';
547  $this->lastquery = $query;
548  return false;
549  }
550  }
551 
552  $ret = @pg_query($this->db, $query);
553 
554  //print $query;
555  if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) { // Si requete utilisateur, on la sauvegarde ainsi que son resultset
556  if (!$ret) {
557  if ($this->errno() != 'DB_ERROR_25P02') { // Do not overwrite errors if this is a consecutive error
558  $this->lastqueryerror = $query;
559  $this->lasterror = $this->error();
560  $this->lasterrno = $this->errno();
561 
562  if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) {
563  dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously
564  }
565  dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterror." (".$this->lasterrno.")", LOG_ERR);
566  dol_syslog(get_class($this)."::query SQL Error usesavepoint = ".$usesavepoint, LOG_ERR);
567  }
568 
569  if ($usesavepoint && $this->transaction_opened) { // Warning, after that errno will be erased
570  @pg_query($this->db, 'ROLLBACK TO SAVEPOINT mysavepoint');
571  }
572  }
573  $this->lastquery = $query;
574  $this->_results = $ret;
575  }
576 
577  return $ret;
578  }
579 
580  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
587  public function fetch_object($resultset)
588  {
589  // phpcs:enable
590  // If resultset not provided, we take the last used by connexion
591  if (!is_resource($resultset) && !is_object($resultset)) {
592  $resultset = $this->_results;
593  }
594  return pg_fetch_object($resultset);
595  }
596 
597  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
604  public function fetch_array($resultset)
605  {
606  // phpcs:enable
607  // If resultset not provided, we take the last used by connexion
608  if (!is_resource($resultset) && !is_object($resultset)) {
609  $resultset = $this->_results;
610  }
611  return pg_fetch_array($resultset);
612  }
613 
614  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
621  public function fetch_row($resultset)
622  {
623  // phpcs:enable
624  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
625  if (!is_resource($resultset) && !is_object($resultset)) {
626  $resultset = $this->_results;
627  }
628  return pg_fetch_row($resultset);
629  }
630 
631  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
639  public function num_rows($resultset)
640  {
641  // phpcs:enable
642  // If resultset not provided, we take the last used by connexion
643  if (!is_resource($resultset) && !is_object($resultset)) {
644  $resultset = $this->_results;
645  }
646  return pg_num_rows($resultset);
647  }
648 
649  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
657  public function affected_rows($resultset)
658  {
659  // phpcs:enable
660  // If resultset not provided, we take the last used by connexion
661  if (!is_resource($resultset) && !is_object($resultset)) {
662  $resultset = $this->_results;
663  }
664  // pgsql necessite un resultset pour cette fonction contrairement
665  // a mysql qui prend un link de base
666  return pg_affected_rows($resultset);
667  }
668 
669 
676  public function free($resultset = null)
677  {
678  // If resultset not provided, we take the last used by connexion
679  if (!is_resource($resultset) && !is_object($resultset)) {
680  $resultset = $this->_results;
681  }
682  // Si resultset en est un, on libere la memoire
683  if (is_resource($resultset) || is_object($resultset)) {
684  pg_free_result($resultset);
685  }
686  }
687 
688 
696  public function plimit($limit = 0, $offset = 0)
697  {
698  global $conf;
699  if (empty($limit)) {
700  return "";
701  }
702  if ($limit < 0) {
703  $limit = $conf->liste_limit;
704  }
705  if ($offset > 0) {
706  return " LIMIT ".$limit." OFFSET ".$offset." ";
707  } else {
708  return " LIMIT $limit ";
709  }
710  }
711 
712 
719  public function escape($stringtoencode)
720  {
721  return pg_escape_string($stringtoencode);
722  }
723 
730  public function escapeforlike($stringtoencode)
731  {
732  return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode);
733  }
734 
743  public function ifsql($test, $resok, $resko)
744  {
745  return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)';
746  }
747 
756  public function regexpsql($subject, $pattern, $sqlstring = false)
757  {
758  if ($sqlstring) {
759  return "(". $subject ." ~ '" . $pattern . "')";
760  }
761 
762  return "('". $subject ."' ~ '" . $pattern . "')";
763  }
764 
765 
771  public function errno()
772  {
773  if (!$this->connected) {
774  // Si il y a eu echec de connexion, $this->db n'est pas valide.
775  return 'DB_ERROR_FAILED_TO_CONNECT';
776  } else {
777  // Constants to convert error code to a generic Dolibarr error code
778  $errorcode_map = array(
779  1004 => 'DB_ERROR_CANNOT_CREATE',
780  1005 => 'DB_ERROR_CANNOT_CREATE',
781  1006 => 'DB_ERROR_CANNOT_CREATE',
782  1007 => 'DB_ERROR_ALREADY_EXISTS',
783  1008 => 'DB_ERROR_CANNOT_DROP',
784  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
785  1044 => 'DB_ERROR_ACCESSDENIED',
786  1046 => 'DB_ERROR_NODBSELECTED',
787  1048 => 'DB_ERROR_CONSTRAINT',
788  '42P07' => 'DB_ERROR_TABLE_OR_KEY_ALREADY_EXISTS',
789  '42703' => 'DB_ERROR_NOSUCHFIELD',
790  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
791  42701=> 'DB_ERROR_COLUMN_ALREADY_EXISTS',
792  '42710' => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
793  '23505' => 'DB_ERROR_RECORD_ALREADY_EXISTS',
794  '42704' => 'DB_ERROR_NO_INDEX_TO_DROP', // May also be Type xxx does not exists
795  '42601' => 'DB_ERROR_SYNTAX',
796  '42P16' => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
797  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
798  1091 => 'DB_ERROR_NOSUCHFIELD',
799  1100 => 'DB_ERROR_NOT_LOCKED',
800  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
801  '42P01' => 'DB_ERROR_NOSUCHTABLE',
802  '23503' => 'DB_ERROR_NO_PARENT',
803  1217 => 'DB_ERROR_CHILD_EXISTS',
804  1451 => 'DB_ERROR_CHILD_EXISTS',
805  '42P04' => 'DB_DATABASE_ALREADY_EXISTS'
806  );
807 
808  $errorlabel = pg_last_error($this->db);
809  $errorcode = '';
810  $reg = array();
811  if (preg_match('/: *([0-9P]+):/', $errorlabel, $reg)) {
812  $errorcode = $reg[1];
813  if (isset($errorcode_map[$errorcode])) {
814  return $errorcode_map[$errorcode];
815  }
816  }
817  $errno = $errorcode ? $errorcode : $errorlabel;
818  return ($errno ? 'DB_ERROR_'.$errno : '0');
819  }
820  // '/(Table does not exist\.|Relation [\"\'].*[\"\'] does not exist|sequence does not exist|class ".+" not found)$/' => 'DB_ERROR_NOSUCHTABLE',
821  // '/table [\"\'].*[\"\'] does not exist/' => 'DB_ERROR_NOSUCHTABLE',
822  // '/Relation [\"\'].*[\"\'] already exists|Cannot insert a duplicate key into (a )?unique index.*/' => 'DB_ERROR_RECORD_ALREADY_EXISTS',
823  // '/divide by zero$/' => 'DB_ERROR_DIVZERO',
824  // '/pg_atoi: error in .*: can\'t parse /' => 'DB_ERROR_INVALID_NUMBER',
825  // '/ttribute [\"\'].*[\"\'] not found$|Relation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => 'DB_ERROR_NOSUCHFIELD',
826  // '/parser: parse error at or near \"/' => 'DB_ERROR_SYNTAX',
827  // '/referential integrity violation/' => 'DB_ERROR_CONSTRAINT'
828  }
829 
835  public function error()
836  {
837  return pg_last_error($this->db);
838  }
839 
840  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
848  public function last_insert_id($tab, $fieldid = 'rowid')
849  {
850  // phpcs:enable
851  //$result = pg_query($this->db,"SELECT MAX(".$fieldid.") FROM ".$tab);
852  $result = pg_query($this->db, "SELECT currval('".$tab."_".$fieldid."_seq')");
853  if (!$result) {
854  print pg_last_error($this->db);
855  exit;
856  }
857  //$nbre = pg_num_rows($result);
858  $row = pg_fetch_result($result, 0, 0);
859  return $row;
860  }
861 
870  public function encrypt($fieldorvalue, $withQuotes = 1)
871  {
872  global $conf;
873 
874  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
875  //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0);
876 
877  //Encryption key
878  //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
879 
880  $return = $fieldorvalue;
881  return ($withQuotes ? "'" : "").$this->escape($return).($withQuotes ? "'" : "");
882  }
883 
884 
891  public function decrypt($value)
892  {
893  global $conf;
894 
895  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
896  //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0);
897 
898  //Encryption key
899  //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
900 
901  $return = $value;
902  return $return;
903  }
904 
905 
906  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
912  public function DDLGetConnectId()
913  {
914  // phpcs:enable
915  return '?';
916  }
917 
918 
919 
920  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
932  public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
933  {
934  // phpcs:enable
935  if (empty($charset)) {
936  $charset = $this->forcecharset;
937  }
938  if (empty($collation)) {
939  $collation = $this->forcecollate;
940  }
941 
942  // Test charset match LC_TYPE (pgsql error otherwise)
943  //print $charset.' '.setlocale(LC_CTYPE,'0'); exit;
944 
945  // NOTE: Do not use ' around the database name
946  $sql = "CREATE DATABASE ".$this->escape($database)." OWNER '".$this->escape($owner)."' ENCODING '".$this->escape($charset)."'";
947  dol_syslog($sql, LOG_DEBUG);
948  $ret = $this->query($sql);
949  return $ret;
950  }
951 
952  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
960  public function DDLListTables($database, $table = '')
961  {
962  // phpcs:enable
963  $listtables = array();
964 
965  $escapedlike = '';
966  if ($table) {
967  $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
968 
969  $escapedlike = " AND table_name LIKE '".$this->escape($tmptable)."'";
970  }
971  $result = pg_query($this->db, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike." ORDER BY table_name");
972  if ($result) {
973  while ($row = $this->fetch_row($result)) {
974  $listtables[] = $row[0];
975  }
976  }
977  return $listtables;
978  }
979 
980  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
988  public function DDLListTablesFull($database, $table = '')
989  {
990  // phpcs:enable
991  $listtables = array();
992 
993  $escapedlike = '';
994  if ($table) {
995  $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
996 
997  $escapedlike = " AND table_name LIKE '".$this->escape($tmptable)."'";
998  }
999  $result = pg_query($this->db, "SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike." ORDER BY table_name");
1000  if ($result) {
1001  while ($row = $this->fetch_row($result)) {
1002  $listtables[] = $row;
1003  }
1004  }
1005  return $listtables;
1006  }
1007 
1008  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1016  public function DDLInfoTable($table)
1017  {
1018  // phpcs:enable
1019  $infotables = array();
1020 
1021  $sql = "SELECT ";
1022  $sql .= " infcol.column_name as \"Column\",";
1023  $sql .= " CASE WHEN infcol.character_maximum_length IS NOT NULL THEN infcol.udt_name || '('||infcol.character_maximum_length||')'";
1024  $sql .= " ELSE infcol.udt_name";
1025  $sql .= " END as \"Type\",";
1026  $sql .= " infcol.collation_name as \"Collation\",";
1027  $sql .= " infcol.is_nullable as \"Null\",";
1028  $sql .= " '' as \"Key\",";
1029  $sql .= " infcol.column_default as \"Default\",";
1030  $sql .= " '' as \"Extra\",";
1031  $sql .= " '' as \"Privileges\"";
1032  $sql .= " FROM information_schema.columns infcol";
1033  $sql .= " WHERE table_schema = 'public' ";
1034  $sql .= " AND table_name = '".$this->escape($table)."'";
1035  $sql .= " ORDER BY ordinal_position;";
1036 
1037  dol_syslog($sql, LOG_DEBUG);
1038  $result = $this->query($sql);
1039  if ($result) {
1040  while ($row = $this->fetch_row($result)) {
1041  $infotables[] = $row;
1042  }
1043  }
1044  return $infotables;
1045  }
1046 
1047 
1048  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1061  public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
1062  {
1063  // phpcs:enable
1064  // FIXME: $fulltext_keys parameter is unused
1065 
1066  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
1067  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
1068  $sql = "create table ".$table."(";
1069  $i = 0;
1070  foreach ($fields as $field_name => $field_desc) {
1071  $sqlfields[$i] = $field_name." ";
1072  $sqlfields[$i] .= $field_desc['type'];
1073  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
1074  $sqlfields[$i] .= "(".$field_desc['value'].")";
1075  } elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
1076  $sqlfields[$i] .= " ".$field_desc['attribute'];
1077  } elseif (preg_match("/^[^\s]/i", $field_desc['default'])) {
1078  if (preg_match("/null/i", $field_desc['default'])) {
1079  $sqlfields[$i] .= " default ".$field_desc['default'];
1080  } else {
1081  $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'";
1082  }
1083  } elseif (preg_match("/^[^\s]/i", $field_desc['null'])) {
1084  $sqlfields[$i] .= " ".$field_desc['null'];
1085  } elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) {
1086  $sqlfields[$i] .= " ".$field_desc['extra'];
1087  }
1088  $i++;
1089  }
1090  if ($primary_key != "") {
1091  $pk = "primary key(".$primary_key.")";
1092  }
1093 
1094  if (is_array($unique_keys)) {
1095  $i = 0;
1096  foreach ($unique_keys as $key => $value) {
1097  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')";
1098  $i++;
1099  }
1100  }
1101  if (is_array($keys)) {
1102  $i = 0;
1103  foreach ($keys as $key => $value) {
1104  $sqlk[$i] = "KEY ".$key." (".$value.")";
1105  $i++;
1106  }
1107  }
1108  $sql .= implode(',', $sqlfields);
1109  if ($primary_key != "") {
1110  $sql .= ",".$pk;
1111  }
1112  if (is_array($unique_keys)) {
1113  $sql .= ",".implode(',', $sqluq);
1114  }
1115  if (is_array($keys)) {
1116  $sql .= ",".implode(',', $sqlk);
1117  }
1118  $sql .= ") type=".$type;
1119 
1120  dol_syslog($sql, LOG_DEBUG);
1121  if (!$this->query($sql)) {
1122  return -1;
1123  } else {
1124  return 1;
1125  }
1126  }
1127 
1128  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1135  public function DDLDropTable($table)
1136  {
1137  // phpcs:enable
1138  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
1139 
1140  $sql = "DROP TABLE ".$tmptable;
1141 
1142  if (!$this->query($sql)) {
1143  return -1;
1144  } else {
1145  return 1;
1146  }
1147  }
1148 
1149  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1159  public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1160  {
1161  // phpcs:enable
1162  // Note: using ' on user does not works with pgsql
1163  $sql = "CREATE USER ".$this->escape($dolibarr_main_db_user)." with password '".$this->escape($dolibarr_main_db_pass)."'";
1164 
1165  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1166  $resql = $this->query($sql);
1167  if (!$resql) {
1168  return -1;
1169  }
1170 
1171  return 1;
1172  }
1173 
1174  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1182  public function DDLDescTable($table, $field = "")
1183  {
1184  // phpcs:enable
1185  $sql = "SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$this->escape($table)."' AND attrelid = typrelid";
1186  $sql .= " AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')";
1187  if ($field) {
1188  $sql .= " AND attname = '".$this->escape($field)."'";
1189  }
1190 
1191  dol_syslog($sql, LOG_DEBUG);
1192  $this->_results = $this->query($sql);
1193  return $this->_results;
1194  }
1195 
1196  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1206  public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
1207  {
1208  // phpcs:enable
1209  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
1210  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
1211  $sql = "ALTER TABLE ".$table." ADD ".$field_name." ";
1212  $sql .= $field_desc['type'];
1213  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
1214  if (!in_array($field_desc['type'], array('smallint', 'int', 'date', 'datetime')) && $field_desc['value']) {
1215  $sql .= "(".$field_desc['value'].")";
1216  }
1217  }
1218  if (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
1219  $sql .= " ".$field_desc['attribute'];
1220  }
1221  if (preg_match("/^[^\s]/i", $field_desc['null'])) {
1222  $sql .= " ".$field_desc['null'];
1223  }
1224  if (preg_match("/^[^\s]/i", $field_desc['default'])) {
1225  if (preg_match("/null/i", $field_desc['default'])) {
1226  $sql .= " default ".$field_desc['default'];
1227  } else {
1228  $sql .= " default '".$this->escape($field_desc['default'])."'";
1229  }
1230  }
1231  if (preg_match("/^[^\s]/i", $field_desc['extra'])) {
1232  $sql .= " ".$field_desc['extra'];
1233  }
1234  $sql .= " ".$field_position;
1235 
1236  dol_syslog($sql, LOG_DEBUG);
1237  if (!$this -> query($sql)) {
1238  return -1;
1239  }
1240  return 1;
1241  }
1242 
1243  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1252  public function DDLUpdateField($table, $field_name, $field_desc)
1253  {
1254  // phpcs:enable
1255  $sql = "ALTER TABLE ".$table;
1256  $sql .= " ALTER COLUMN ".$this->escape($field_name)." TYPE ".$field_desc['type'];
1257  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
1258  if (!in_array($field_desc['type'], array('smallint', 'int', 'date', 'datetime')) && $field_desc['value']) {
1259  $sql .= "(".$field_desc['value'].")";
1260  }
1261  }
1262 
1263  if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') {
1264  // We will try to change format of column to NOT NULL. To be sure the ALTER works, we try to update fields that are NULL
1265  if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
1266  $sqlbis = "UPDATE ".$table." SET ".$this->escape($field_name)." = '".$this->escape(isset($field_desc['default']) ? $field_desc['default'] : '')."' WHERE ".$this->escape($field_name)." IS NULL";
1267  $this->query($sqlbis);
1268  } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
1269  $sqlbis = "UPDATE ".$table." SET ".$this->escape($field_name)." = ".((int) $this->escape(isset($field_desc['default']) ? $field_desc['default'] : 0))." WHERE ".$this->escape($field_name)." IS NULL";
1270  $this->query($sqlbis);
1271  }
1272  }
1273 
1274  if (isset($field_desc['default']) && $field_desc['default'] != '') {
1275  if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
1276  $sql .= " DEFAULT ".$this->escape($field_desc['default']);
1277  } elseif ($field_desc['type'] != 'text') {
1278  $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
1279  }
1280  }
1281 
1282  dol_syslog($sql, LOG_DEBUG);
1283  if (!$this->query($sql)) {
1284  return -1;
1285  }
1286  return 1;
1287  }
1288 
1289  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1297  public function DDLDropField($table, $field_name)
1298  {
1299  // phpcs:enable
1300  $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
1301 
1302  $sql = "ALTER TABLE ".$table." DROP COLUMN ".$tmp_field_name;
1303  if (!$this->query($sql)) {
1304  $this->error = $this->lasterror();
1305  return -1;
1306  }
1307  return 1;
1308  }
1309 
1316  {
1317  $resql = $this->query('SHOW SERVER_ENCODING');
1318  if ($resql) {
1319  $liste = $this->fetch_array($resql);
1320  return $liste['server_encoding'];
1321  } else {
1322  return '';
1323  }
1324  }
1325 
1331  public function getListOfCharacterSet()
1332  {
1333  $resql = $this->query('SHOW SERVER_ENCODING');
1334  $liste = array();
1335  if ($resql) {
1336  $i = 0;
1337  while ($obj = $this->fetch_object($resql)) {
1338  $liste[$i]['charset'] = $obj->server_encoding;
1339  $liste[$i]['description'] = 'Default database charset';
1340  $i++;
1341  }
1342  $this->free($resql);
1343  } else {
1344  return null;
1345  }
1346  return $liste;
1347  }
1348 
1355  {
1356  $resql = $this->query('SHOW LC_COLLATE');
1357  if ($resql) {
1358  $liste = $this->fetch_array($resql);
1359  return $liste['lc_collate'];
1360  } else {
1361  return '';
1362  }
1363  }
1364 
1370  public function getListOfCollation()
1371  {
1372  $resql = $this->query('SHOW LC_COLLATE');
1373  $liste = array();
1374  if ($resql) {
1375  $i = 0;
1376  while ($obj = $this->fetch_object($resql)) {
1377  $liste[$i]['collation'] = $obj->lc_collate;
1378  $i++;
1379  }
1380  $this->free($resql);
1381  } else {
1382  return null;
1383  }
1384  return $liste;
1385  }
1386 
1392  public function getPathOfDump()
1393  {
1394  $fullpathofdump = '/pathtopgdump/pg_dump';
1395 
1396  if (file_exists('/usr/bin/pg_dump')) {
1397  $fullpathofdump = '/usr/bin/pg_dump';
1398  } else {
1399  // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande
1400  $resql = $this->query('SHOW data_directory');
1401  if ($resql) {
1402  $liste = $this->fetch_array($resql);
1403  $basedir = $liste['data_directory'];
1404  $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/pg_dump';
1405  }
1406  }
1407 
1408  return $fullpathofdump;
1409  }
1410 
1416  public function getPathOfRestore()
1417  {
1418  //$tool='pg_restore';
1419  $tool = 'psql';
1420 
1421  $fullpathofdump = '/pathtopgrestore/'.$tool;
1422 
1423  if (file_exists('/usr/bin/'.$tool)) {
1424  $fullpathofdump = '/usr/bin/'.$tool;
1425  } else {
1426  // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande
1427  $resql = $this->query('SHOW data_directory');
1428  if ($resql) {
1429  $liste = $this->fetch_array($resql);
1430  $basedir = $liste['data_directory'];
1431  $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/'.$tool;
1432  }
1433  }
1434 
1435  return $fullpathofdump;
1436  }
1437 
1444  public function getServerParametersValues($filter = '')
1445  {
1446  $result = array();
1447 
1448  $resql = 'select name,setting from pg_settings';
1449  if ($filter) {
1450  $resql .= " WHERE name = '".$this->escape($filter)."'";
1451  }
1452  $resql = $this->query($resql);
1453  if ($resql) {
1454  while ($obj = $this->fetch_object($resql)) {
1455  $result[$obj->name] = $obj->setting;
1456  }
1457  }
1458 
1459  return $result;
1460  }
1461 
1468  public function getServerStatusValues($filter = '')
1469  {
1470  /* This is to return current running requests.
1471  $sql='SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid';
1472  if ($filter) $sql.=" LIKE '".$this->escape($filter)."'";
1473  $resql=$this->query($sql);
1474  if ($resql)
1475  {
1476  $obj=$this->fetch_object($resql);
1477  $result[$obj->Variable_name]=$obj->Value;
1478  }
1479  */
1480 
1481  return array();
1482  }
1483 }
DoliDBPgsql\escape
escape($stringtoencode)
Escape a string to insert data.
Definition: pgsql.class.php:719
DoliDBPgsql\connect
connect($host, $login, $passwd, $name, $port=0)
Connexion to server.
Definition: pgsql.class.php:402
DoliDBPgsql\DDLCreateTable
DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys=null, $fulltext_keys=null, $keys=null)
Create a table into database.
Definition: pgsql.class.php:1061
DoliDBPgsql\getListOfCollation
getListOfCollation()
Return list of available collation that can be used for database.
Definition: pgsql.class.php:1370
DoliDBPgsql\getServerStatusValues
getServerStatusValues($filter='')
Return value of server status.
Definition: pgsql.class.php:1468
DoliDBPgsql\last_insert_id
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
Definition: pgsql.class.php:848
DoliDB\lasterror
lasterror()
Return last error label.
Definition: DoliDB.class.php:329
DoliDBPgsql\select_db
select_db($database)
Select a database Ici postgresql n'a aucune fonction equivalente de mysql_select_db On compare juste ...
Definition: pgsql.class.php:381
DoliDBPgsql\query
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Convert request to PostgreSQL syntax, execute it and return the resultset.
Definition: pgsql.class.php:508
DoliDBPgsql\getDefaultCharacterSetDatabase
getDefaultCharacterSetDatabase()
Return charset used to store data in database.
Definition: pgsql.class.php:1315
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:30
DoliDBPgsql\DDLGetConnectId
DDLGetConnectId()
Return connexion ID.
Definition: pgsql.class.php:912
DoliDBPgsql\ifsql
ifsql($test, $resok, $resko)
Format a SQL IF.
Definition: pgsql.class.php:743
DoliDBPgsql\DDLCreateUser
DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
Create a user to connect to database.
Definition: pgsql.class.php:1159
DoliDBPgsql\num_rows
num_rows($resultset)
Return number of lines for result of a SELECT.
Definition: pgsql.class.php:639
DoliDBPgsql\error
error()
Renvoie le texte de l'erreur pgsql de l'operation precedente.
Definition: pgsql.class.php:835
DoliDBPgsql\free
free($resultset=null)
Libere le dernier resultset utilise sur cette connexion.
Definition: pgsql.class.php:676
DoliDBPgsql\encrypt
encrypt($fieldorvalue, $withQuotes=1)
Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple ...
Definition: pgsql.class.php:870
DoliDBPgsql\DDLDescTable
DDLDescTable($table, $field="")
Return a pointer of line with description of a table or field.
Definition: pgsql.class.php:1182
DoliDBPgsql\escapeforlike
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
Definition: pgsql.class.php:730
DoliDBPgsql\DDLListTables
DDLListTables($database, $table='')
List tables into a database.
Definition: pgsql.class.php:960
DoliDBPgsql\getDefaultCollationDatabase
getDefaultCollationDatabase()
Return collation used in database.
Definition: pgsql.class.php:1354
DoliDBPgsql\decrypt
decrypt($value)
Decrypt sensitive data in database.
Definition: pgsql.class.php:891
DoliDBPgsql\$type
$type
Database type.
Definition: pgsql.class.php:39
DoliDBPgsql\$forcecollate
$forcecollate
Collate used to force collate when creating database.
Definition: pgsql.class.php:48
DoliDB\lastquery
lastquery()
Return last request executed with query()
Definition: DoliDB.class.php:274
DoliDBPgsql\regexpsql
regexpsql($subject, $pattern, $sqlstring=false)
Format a SQL REGEXP.
Definition: pgsql.class.php:756
Exception
DoliDBPgsql
Class to drive a Postgresql database for Dolibarr.
Definition: pgsql.class.php:36
DoliDBPgsql\fetch_array
fetch_array($resultset)
Return datas as an array.
Definition: pgsql.class.php:604
DoliDBPgsql\LABEL
const LABEL
Database label.
Definition: pgsql.class.php:42
DoliDBPgsql\close
close()
Close database connexion.
Definition: pgsql.class.php:487
DoliDB\lasterrno
lasterrno()
Return last error code.
Definition: DoliDB.class.php:151
DoliDBPgsql\fetch_object
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
Definition: pgsql.class.php:587
DoliDBPgsql\getPathOfDump
getPathOfDump()
Return full path of dump program.
Definition: pgsql.class.php:1392
DoliDBPgsql\errno
errno()
Renvoie le code erreur generique de l'operation precedente.
Definition: pgsql.class.php:771
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1741
DoliDBPgsql\getVersion
getVersion()
Return version of database server.
Definition: pgsql.class.php:461
DoliDBPgsql\getServerParametersValues
getServerParametersValues($filter='')
Return value of server parameters.
Definition: pgsql.class.php:1444
DoliDBPgsql\affected_rows
affected_rows($resultset)
Return the number of lines in the result of a request INSERT, DELETE or UPDATE.
Definition: pgsql.class.php:657
DoliDBPgsql\DDLListTablesFull
DDLListTablesFull($database, $table='')
List tables into a database.
Definition: pgsql.class.php:988
DoliDBPgsql\convertSQLFromMysql
static convertSQLFromMysql($line, $type='auto', $unescapeslashquot=false)
Convert a SQL request in Mysql syntax to native syntax.
Definition: pgsql.class.php:152
$sql
if(isModEnabled('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:746
DoliDBPgsql\DDLUpdateField
DDLUpdateField($table, $field_name, $field_desc)
Update format of a field into a table.
Definition: pgsql.class.php:1252
DoliDBPgsql\DDLCreateDb
DDLCreateDb($database, $charset='', $collation='', $owner='')
Create a new database Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated We fo...
Definition: pgsql.class.php:932
DoliDBPgsql\DDLAddField
DDLAddField($table, $field_name, $field_desc, $field_position="")
Create a new field into table.
Definition: pgsql.class.php:1206
DoliDB\lastqueryerror
lastqueryerror()
Return last query in error.
Definition: DoliDB.class.php:360
DoliDBPgsql\VERSIONMIN
const VERSIONMIN
Version min database.
Definition: pgsql.class.php:51
DoliDBPgsql\getListOfCharacterSet
getListOfCharacterSet()
Return list of available charset that can be used to store data in database.
Definition: pgsql.class.php:1331
DoliDBPgsql\__construct
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
Definition: pgsql.class.php:71
DoliDBPgsql\getDriverInfo
getDriverInfo()
Return version of database client driver.
Definition: pgsql.class.php:476
DoliDBPgsql\fetch_row
fetch_row($resultset)
Return datas as an array.
Definition: pgsql.class.php:621
DoliDBPgsql\getPathOfRestore
getPathOfRestore()
Return full path of restore program.
Definition: pgsql.class.php:1416
DoliDBPgsql\DDLDropTable
DDLDropTable($table)
Drop a table into database.
Definition: pgsql.class.php:1135
DoliDBPgsql\DDLDropField
DDLDropField($table, $field_name)
Drop a field from table.
Definition: pgsql.class.php:1297
getDolGlobalInt
getDolGlobalInt($key, $default=0)
Return dolibarr global constant int value.
Definition: functions.lib.php:156
DoliDBPgsql\$forcecharset
$forcecharset
Charset.
Definition: pgsql.class.php:45
DoliDBPgsql\plimit
plimit($limit=0, $offset=0)
Define limits and offset of request.
Definition: pgsql.class.php:696
DoliDBPgsql\DDLInfoTable
DDLInfoTable($table)
List information of columns into a table.
Definition: pgsql.class.php:1016