dolibarr  17.0.3
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 (empty($conf->global->SYSLOG_LEVEL) || $conf->global->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 
731  public function escapeunderscore($stringtoencode)
732  {
733  return str_replace('_', '\_', (string) $stringtoencode);
734  }
735 
742  public function escapeforlike($stringtoencode)
743  {
744  return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode);
745  }
746 
755  public function ifsql($test, $resok, $resko)
756  {
757  return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)';
758  }
759 
768  public function regexpsql($subject, $pattern, $sqlstring = false)
769  {
770  if ($sqlstring) {
771  return "(". $subject ." ~ '" . $pattern . "')";
772  }
773 
774  return "('". $subject ."' ~ '" . $pattern . "')";
775  }
776 
777 
783  public function errno()
784  {
785  if (!$this->connected) {
786  // Si il y a eu echec de connexion, $this->db n'est pas valide.
787  return 'DB_ERROR_FAILED_TO_CONNECT';
788  } else {
789  // Constants to convert error code to a generic Dolibarr error code
790  $errorcode_map = array(
791  1004 => 'DB_ERROR_CANNOT_CREATE',
792  1005 => 'DB_ERROR_CANNOT_CREATE',
793  1006 => 'DB_ERROR_CANNOT_CREATE',
794  1007 => 'DB_ERROR_ALREADY_EXISTS',
795  1008 => 'DB_ERROR_CANNOT_DROP',
796  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
797  1044 => 'DB_ERROR_ACCESSDENIED',
798  1046 => 'DB_ERROR_NODBSELECTED',
799  1048 => 'DB_ERROR_CONSTRAINT',
800  '42P07' => 'DB_ERROR_TABLE_OR_KEY_ALREADY_EXISTS',
801  '42703' => 'DB_ERROR_NOSUCHFIELD',
802  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
803  42701=> 'DB_ERROR_COLUMN_ALREADY_EXISTS',
804  '42710' => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
805  '23505' => 'DB_ERROR_RECORD_ALREADY_EXISTS',
806  '42704' => 'DB_ERROR_NO_INDEX_TO_DROP', // May also be Type xxx does not exists
807  '42601' => 'DB_ERROR_SYNTAX',
808  '42P16' => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
809  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
810  1091 => 'DB_ERROR_NOSUCHFIELD',
811  1100 => 'DB_ERROR_NOT_LOCKED',
812  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
813  '42P01' => 'DB_ERROR_NOSUCHTABLE',
814  '23503' => 'DB_ERROR_NO_PARENT',
815  1217 => 'DB_ERROR_CHILD_EXISTS',
816  1451 => 'DB_ERROR_CHILD_EXISTS',
817  '42P04' => 'DB_DATABASE_ALREADY_EXISTS'
818  );
819 
820  $errorlabel = pg_last_error($this->db);
821  $errorcode = '';
822  $reg = array();
823  if (preg_match('/: *([0-9P]+):/', $errorlabel, $reg)) {
824  $errorcode = $reg[1];
825  if (isset($errorcode_map[$errorcode])) {
826  return $errorcode_map[$errorcode];
827  }
828  }
829  $errno = $errorcode ? $errorcode : $errorlabel;
830  return ($errno ? 'DB_ERROR_'.$errno : '0');
831  }
832  // '/(Table does not exist\.|Relation [\"\'].*[\"\'] does not exist|sequence does not exist|class ".+" not found)$/' => 'DB_ERROR_NOSUCHTABLE',
833  // '/table [\"\'].*[\"\'] does not exist/' => 'DB_ERROR_NOSUCHTABLE',
834  // '/Relation [\"\'].*[\"\'] already exists|Cannot insert a duplicate key into (a )?unique index.*/' => 'DB_ERROR_RECORD_ALREADY_EXISTS',
835  // '/divide by zero$/' => 'DB_ERROR_DIVZERO',
836  // '/pg_atoi: error in .*: can\'t parse /' => 'DB_ERROR_INVALID_NUMBER',
837  // '/ttribute [\"\'].*[\"\'] not found$|Relation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => 'DB_ERROR_NOSUCHFIELD',
838  // '/parser: parse error at or near \"/' => 'DB_ERROR_SYNTAX',
839  // '/referential integrity violation/' => 'DB_ERROR_CONSTRAINT'
840  }
841 
847  public function error()
848  {
849  return pg_last_error($this->db);
850  }
851 
852  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
860  public function last_insert_id($tab, $fieldid = 'rowid')
861  {
862  // phpcs:enable
863  //$result = pg_query($this->db,"SELECT MAX(".$fieldid.") FROM ".$tab);
864  $result = pg_query($this->db, "SELECT currval('".$tab."_".$fieldid."_seq')");
865  if (!$result) {
866  print pg_last_error($this->db);
867  exit;
868  }
869  //$nbre = pg_num_rows($result);
870  $row = pg_fetch_result($result, 0, 0);
871  return $row;
872  }
873 
882  public function encrypt($fieldorvalue, $withQuotes = 1)
883  {
884  global $conf;
885 
886  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
887  //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0);
888 
889  //Encryption key
890  //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
891 
892  $return = $fieldorvalue;
893  return ($withQuotes ? "'" : "").$this->escape($return).($withQuotes ? "'" : "");
894  }
895 
896 
903  public function decrypt($value)
904  {
905  global $conf;
906 
907  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
908  //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0);
909 
910  //Encryption key
911  //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : '');
912 
913  $return = $value;
914  return $return;
915  }
916 
917 
918  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
924  public function DDLGetConnectId()
925  {
926  // phpcs:enable
927  return '?';
928  }
929 
930 
931 
932  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
944  public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '')
945  {
946  // phpcs:enable
947  if (empty($charset)) {
948  $charset = $this->forcecharset;
949  }
950  if (empty($collation)) {
951  $collation = $this->forcecollate;
952  }
953 
954  // Test charset match LC_TYPE (pgsql error otherwise)
955  //print $charset.' '.setlocale(LC_CTYPE,'0'); exit;
956 
957  // NOTE: Do not use ' around the database name
958  $sql = "CREATE DATABASE ".$this->escape($database)." OWNER '".$this->escape($owner)."' ENCODING '".$this->escape($charset)."'";
959  dol_syslog($sql, LOG_DEBUG);
960  $ret = $this->query($sql);
961  return $ret;
962  }
963 
964  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
972  public function DDLListTables($database, $table = '')
973  {
974  // phpcs:enable
975  $listtables = array();
976 
977  $escapedlike = '';
978  if ($table) {
979  $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table);
980 
981  $escapedlike = " AND table_name LIKE '".$this->escape($tmptable)."'";
982  }
983  $result = pg_query($this->db, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike." ORDER BY table_name");
984  if ($result) {
985  while ($row = $this->fetch_row($result)) {
986  $listtables[] = $row[0];
987  }
988  }
989  return $listtables;
990  }
991 
992  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1000  public function DDLInfoTable($table)
1001  {
1002  // phpcs:enable
1003  $infotables = array();
1004 
1005  $sql = "SELECT ";
1006  $sql .= " infcol.column_name as \"Column\",";
1007  $sql .= " CASE WHEN infcol.character_maximum_length IS NOT NULL THEN infcol.udt_name || '('||infcol.character_maximum_length||')'";
1008  $sql .= " ELSE infcol.udt_name";
1009  $sql .= " END as \"Type\",";
1010  $sql .= " infcol.collation_name as \"Collation\",";
1011  $sql .= " infcol.is_nullable as \"Null\",";
1012  $sql .= " '' as \"Key\",";
1013  $sql .= " infcol.column_default as \"Default\",";
1014  $sql .= " '' as \"Extra\",";
1015  $sql .= " '' as \"Privileges\"";
1016  $sql .= " FROM information_schema.columns infcol";
1017  $sql .= " WHERE table_schema = 'public' ";
1018  $sql .= " AND table_name = '".$this->escape($table)."'";
1019  $sql .= " ORDER BY ordinal_position;";
1020 
1021  dol_syslog($sql, LOG_DEBUG);
1022  $result = $this->query($sql);
1023  if ($result) {
1024  while ($row = $this->fetch_row($result)) {
1025  $infotables[] = $row;
1026  }
1027  }
1028  return $infotables;
1029  }
1030 
1031 
1032  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1045  public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
1046  {
1047  // phpcs:enable
1048  // FIXME: $fulltext_keys parameter is unused
1049 
1050  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
1051  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
1052  $sql = "create table ".$table."(";
1053  $i = 0;
1054  foreach ($fields as $field_name => $field_desc) {
1055  $sqlfields[$i] = $field_name." ";
1056  $sqlfields[$i] .= $field_desc['type'];
1057  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
1058  $sqlfields[$i] .= "(".$field_desc['value'].")";
1059  } elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
1060  $sqlfields[$i] .= " ".$field_desc['attribute'];
1061  } elseif (preg_match("/^[^\s]/i", $field_desc['default'])) {
1062  if (preg_match("/null/i", $field_desc['default'])) {
1063  $sqlfields[$i] .= " default ".$field_desc['default'];
1064  } else {
1065  $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'";
1066  }
1067  } elseif (preg_match("/^[^\s]/i", $field_desc['null'])) {
1068  $sqlfields[$i] .= " ".$field_desc['null'];
1069  } elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) {
1070  $sqlfields[$i] .= " ".$field_desc['extra'];
1071  }
1072  $i++;
1073  }
1074  if ($primary_key != "") {
1075  $pk = "primary key(".$primary_key.")";
1076  }
1077 
1078  if (is_array($unique_keys)) {
1079  $i = 0;
1080  foreach ($unique_keys as $key => $value) {
1081  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')";
1082  $i++;
1083  }
1084  }
1085  if (is_array($keys)) {
1086  $i = 0;
1087  foreach ($keys as $key => $value) {
1088  $sqlk[$i] = "KEY ".$key." (".$value.")";
1089  $i++;
1090  }
1091  }
1092  $sql .= implode(',', $sqlfields);
1093  if ($primary_key != "") {
1094  $sql .= ",".$pk;
1095  }
1096  if (is_array($unique_keys)) {
1097  $sql .= ",".implode(',', $sqluq);
1098  }
1099  if (is_array($keys)) {
1100  $sql .= ",".implode(',', $sqlk);
1101  }
1102  $sql .= ") type=".$type;
1103 
1104  dol_syslog($sql, LOG_DEBUG);
1105  if (!$this->query($sql)) {
1106  return -1;
1107  } else {
1108  return 1;
1109  }
1110  }
1111 
1112  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1119  public function DDLDropTable($table)
1120  {
1121  // phpcs:enable
1122  $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table);
1123 
1124  $sql = "DROP TABLE ".$tmptable;
1125 
1126  if (!$this->query($sql)) {
1127  return -1;
1128  } else {
1129  return 1;
1130  }
1131  }
1132 
1133  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1143  public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1144  {
1145  // phpcs:enable
1146  // Note: using ' on user does not works with pgsql
1147  $sql = "CREATE USER ".$this->escape($dolibarr_main_db_user)." with password '".$this->escape($dolibarr_main_db_pass)."'";
1148 
1149  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1150  $resql = $this->query($sql);
1151  if (!$resql) {
1152  return -1;
1153  }
1154 
1155  return 1;
1156  }
1157 
1158  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1166  public function DDLDescTable($table, $field = "")
1167  {
1168  // phpcs:enable
1169  $sql = "SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$this->escape($table)."' AND attrelid = typrelid";
1170  $sql .= " AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')";
1171  if ($field) {
1172  $sql .= " AND attname = '".$this->escape($field)."'";
1173  }
1174 
1175  dol_syslog($sql, LOG_DEBUG);
1176  $this->_results = $this->query($sql);
1177  return $this->_results;
1178  }
1179 
1180  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1190  public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
1191  {
1192  // phpcs:enable
1193  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
1194  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
1195  $sql = "ALTER TABLE ".$table." ADD ".$field_name." ";
1196  $sql .= $field_desc['type'];
1197  if (preg_match("/^[^\s]/i", $field_desc['value'])) {
1198  if (!in_array($field_desc['type'], array('int', 'date', 'datetime')) && $field_desc['value']) {
1199  $sql .= "(".$field_desc['value'].")";
1200  }
1201  }
1202  if (preg_match("/^[^\s]/i", $field_desc['attribute'])) {
1203  $sql .= " ".$field_desc['attribute'];
1204  }
1205  if (preg_match("/^[^\s]/i", $field_desc['null'])) {
1206  $sql .= " ".$field_desc['null'];
1207  }
1208  if (preg_match("/^[^\s]/i", $field_desc['default'])) {
1209  if (preg_match("/null/i", $field_desc['default'])) {
1210  $sql .= " default ".$field_desc['default'];
1211  } else {
1212  $sql .= " default '".$this->escape($field_desc['default'])."'";
1213  }
1214  }
1215  if (preg_match("/^[^\s]/i", $field_desc['extra'])) {
1216  $sql .= " ".$field_desc['extra'];
1217  }
1218  $sql .= " ".$field_position;
1219 
1220  dol_syslog($sql, LOG_DEBUG);
1221  if (!$this -> query($sql)) {
1222  return -1;
1223  }
1224  return 1;
1225  }
1226 
1227  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1236  public function DDLUpdateField($table, $field_name, $field_desc)
1237  {
1238  // phpcs:enable
1239  $sql = "ALTER TABLE ".$table;
1240  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
1241  if (in_array($field_desc['type'], array('double', 'varchar')) && $field_desc['value']) {
1242  $sql .= "(".$field_desc['value'].")";
1243  }
1244 
1245  if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') {
1246  // 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
1247  if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') {
1248  $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape($field_desc['default'] ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL";
1249  $this->query($sqlbis);
1250  } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
1251  $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape($field_desc['default'] ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL";
1252  $this->query($sqlbis);
1253  }
1254  }
1255 
1256  if ($field_desc['default'] != '') {
1257  if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') {
1258  $sql .= " DEFAULT ".$this->escape($field_desc['default']);
1259  } elseif ($field_desc['type'] != 'text') {
1260  $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields
1261  }
1262  }
1263 
1264  dol_syslog($sql, LOG_DEBUG);
1265  if (!$this->query($sql)) {
1266  return -1;
1267  }
1268  return 1;
1269  }
1270 
1271  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1279  public function DDLDropField($table, $field_name)
1280  {
1281  // phpcs:enable
1282  $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name);
1283 
1284  $sql = "ALTER TABLE ".$table." DROP COLUMN ".$tmp_field_name;
1285  if (!$this->query($sql)) {
1286  $this->error = $this->lasterror();
1287  return -1;
1288  }
1289  return 1;
1290  }
1291 
1298  {
1299  $resql = $this->query('SHOW SERVER_ENCODING');
1300  if ($resql) {
1301  $liste = $this->fetch_array($resql);
1302  return $liste['server_encoding'];
1303  } else {
1304  return '';
1305  }
1306  }
1307 
1313  public function getListOfCharacterSet()
1314  {
1315  $resql = $this->query('SHOW SERVER_ENCODING');
1316  $liste = array();
1317  if ($resql) {
1318  $i = 0;
1319  while ($obj = $this->fetch_object($resql)) {
1320  $liste[$i]['charset'] = $obj->server_encoding;
1321  $liste[$i]['description'] = 'Default database charset';
1322  $i++;
1323  }
1324  $this->free($resql);
1325  } else {
1326  return null;
1327  }
1328  return $liste;
1329  }
1330 
1337  {
1338  $resql = $this->query('SHOW LC_COLLATE');
1339  if ($resql) {
1340  $liste = $this->fetch_array($resql);
1341  return $liste['lc_collate'];
1342  } else {
1343  return '';
1344  }
1345  }
1346 
1352  public function getListOfCollation()
1353  {
1354  $resql = $this->query('SHOW LC_COLLATE');
1355  $liste = array();
1356  if ($resql) {
1357  $i = 0;
1358  while ($obj = $this->fetch_object($resql)) {
1359  $liste[$i]['collation'] = $obj->lc_collate;
1360  $i++;
1361  }
1362  $this->free($resql);
1363  } else {
1364  return null;
1365  }
1366  return $liste;
1367  }
1368 
1374  public function getPathOfDump()
1375  {
1376  $fullpathofdump = '/pathtopgdump/pg_dump';
1377 
1378  if (file_exists('/usr/bin/pg_dump')) {
1379  $fullpathofdump = '/usr/bin/pg_dump';
1380  } else {
1381  // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande
1382  $resql = $this->query('SHOW data_directory');
1383  if ($resql) {
1384  $liste = $this->fetch_array($resql);
1385  $basedir = $liste['data_directory'];
1386  $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/pg_dump';
1387  }
1388  }
1389 
1390  return $fullpathofdump;
1391  }
1392 
1398  public function getPathOfRestore()
1399  {
1400  //$tool='pg_restore';
1401  $tool = 'psql';
1402 
1403  $fullpathofdump = '/pathtopgrestore/'.$tool;
1404 
1405  if (file_exists('/usr/bin/'.$tool)) {
1406  $fullpathofdump = '/usr/bin/'.$tool;
1407  } else {
1408  // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande
1409  $resql = $this->query('SHOW data_directory');
1410  if ($resql) {
1411  $liste = $this->fetch_array($resql);
1412  $basedir = $liste['data_directory'];
1413  $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/'.$tool;
1414  }
1415  }
1416 
1417  return $fullpathofdump;
1418  }
1419 
1426  public function getServerParametersValues($filter = '')
1427  {
1428  $result = array();
1429 
1430  $resql = 'select name,setting from pg_settings';
1431  if ($filter) {
1432  $resql .= " WHERE name = '".$this->escape($filter)."'";
1433  }
1434  $resql = $this->query($resql);
1435  if ($resql) {
1436  while ($obj = $this->fetch_object($resql)) {
1437  $result[$obj->name] = $obj->setting;
1438  }
1439  }
1440 
1441  return $result;
1442  }
1443 
1450  public function getServerStatusValues($filter = '')
1451  {
1452  /* This is to return current running requests.
1453  $sql='SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid';
1454  if ($filter) $sql.=" LIKE '".$this->escape($filter)."'";
1455  $resql=$this->query($sql);
1456  if ($resql)
1457  {
1458  $obj=$this->fetch_object($resql);
1459  $result[$obj->Variable_name]=$obj->Value;
1460  }
1461  */
1462 
1463  return array();
1464  }
1465 }
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:1045
db
$conf db
API class for accounts.
Definition: inc.php:41
DoliDBPgsql\getListOfCollation
getListOfCollation()
Return list of available collation that can be used for database.
Definition: pgsql.class.php:1352
DoliDBPgsql\getServerStatusValues
getServerStatusValues($filter='')
Return value of server status.
Definition: pgsql.class.php:1450
DoliDBPgsql\last_insert_id
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
Definition: pgsql.class.php:860
DoliDB\lasterror
lasterror()
Return last error label.
Definition: DoliDB.class.php:326
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:1297
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:30
DoliDBPgsql\DDLGetConnectId
DDLGetConnectId()
Return connexion ID.
Definition: pgsql.class.php:924
DoliDBPgsql\ifsql
ifsql($test, $resok, $resko)
Format a SQL IF.
Definition: pgsql.class.php:755
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:1143
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:847
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:882
DoliDBPgsql\DDLDescTable
DDLDescTable($table, $field="")
Return a pointer of line with description of a table or field.
Definition: pgsql.class.php:1166
DoliDBPgsql\escapeforlike
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
Definition: pgsql.class.php:742
DoliDBPgsql\DDLListTables
DDLListTables($database, $table='')
List tables into a database.
Definition: pgsql.class.php:972
DoliDBPgsql\getDefaultCollationDatabase
getDefaultCollationDatabase()
Return collation used in database.
Definition: pgsql.class.php:1336
DoliDBPgsql\decrypt
decrypt($value)
Decrypt sensitive data in database.
Definition: pgsql.class.php:903
DoliDBPgsql\escapeunderscore
escapeunderscore($stringtoencode)
Escape a string to insert data.
Definition: pgsql.class.php:731
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:271
DoliDBPgsql\regexpsql
regexpsql($subject, $pattern, $sqlstring=false)
Format a SQL REGEXP.
Definition: pgsql.class.php:768
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
$resql
if(isModEnabled('facture') &&!empty($user->rights->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') &&!empty($user->rights->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)) $resql
Social contributions to pay.
Definition: index.php:745
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:148
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:1374
DoliDBPgsql\errno
errno()
Renvoie le code erreur generique de l'operation precedente.
Definition: pgsql.class.php:783
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1628
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:1426
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\convertSQLFromMysql
static convertSQLFromMysql($line, $type='auto', $unescapeslashquot=false)
Convert a SQL request in Mysql syntax to native syntax.
Definition: pgsql.class.php:152
DoliDBPgsql\DDLUpdateField
DDLUpdateField($table, $field_name, $field_desc)
Update format of a field into a table.
Definition: pgsql.class.php:1236
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:944
DoliDBPgsql\DDLAddField
DDLAddField($table, $field_name, $field_desc, $field_position="")
Create a new field into table.
Definition: pgsql.class.php:1190
DoliDB\lastqueryerror
lastqueryerror()
Return last query in error.
Definition: DoliDB.class.php:357
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:1313
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:1398
DoliDBPgsql\DDLDropTable
DDLDropTable($table)
Drop a table into database.
Definition: pgsql.class.php:1119
DoliDBPgsql\DDLDropField
DDLDropField($table, $field_name)
Drop a field from table.
Definition: pgsql.class.php:1279
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:1000