dolibarr  19.0.0-dev
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 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 }
Class to manage Dolibarr database access.
lastqueryerror()
Return last query in error.
lasterror()
Return last error label.
lasterrno()
Return last error code.
lastquery()
Return last request executed with query()
Class to drive a Postgresql database for Dolibarr.
Definition: pgsql.class.php:37
errno()
Renvoie le code erreur generique de l'operation precedente.
DDLListTablesFull($database, $table='')
List tables into a database.
DDLGetConnectId()
Return connexion ID.
num_rows($resultset)
Return number of lines for result of a SELECT.
const VERSIONMIN
Version min database.
Definition: pgsql.class.php:51
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
Definition: pgsql.class.php:71
DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
Create a user to connect to database.
DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys=null, $fulltext_keys=null, $keys=null)
Create a table into database.
DDLDropTable($table)
Drop a table into database.
DDLUpdateField($table, $field_name, $field_desc)
Update format of a field into a table.
getPathOfDump()
Return full path of dump program.
select_db($database)
Select a database Ici postgresql n'a aucune fonction equivalente de mysql_select_db On compare juste ...
getServerStatusValues($filter='')
Return value of server status.
plimit($limit=0, $offset=0)
Define limits and offset of request.
decrypt($value)
Decrypt sensitive data in database.
error()
Renvoie le texte de l'erreur pgsql de l'operation precedente.
escape($stringtoencode)
Escape a string to insert data.
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
close()
Close database connexion.
encrypt($fieldorvalue, $withQuotes=1)
Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple ...
getListOfCharacterSet()
Return list of available charset that can be used to store data in database.
fetch_array($resultset)
Return datas as an array.
regexpsql($subject, $pattern, $sqlstring=false)
Format a SQL REGEXP.
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
getPathOfRestore()
Return full path of restore program.
DDLAddField($table, $field_name, $field_desc, $field_position="")
Create a new field into table.
$type
Database type.
Definition: pgsql.class.php:39
DDLInfoTable($table)
List information of columns into a table.
connect($host, $login, $passwd, $name, $port=0)
Connexion to server.
getVersion()
Return version of database server.
$forcecharset
Charset.
Definition: pgsql.class.php:45
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
affected_rows($resultset)
Return the number of lines in the result of a request INSERT, DELETE or UPDATE.
DDLDropField($table, $field_name)
Drop a field from table.
DDLCreateDb($database, $charset='', $collation='', $owner='')
Create a new database Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated We fo...
const LABEL
Database label.
Definition: pgsql.class.php:42
getListOfCollation()
Return list of available collation that can be used for database.
getDriverInfo()
Return version of database client driver.
free($resultset=null)
Libere le dernier resultset utilise sur cette connexion.
DDLDescTable($table, $field="")
Return a pointer of line with description of a table or field.
ifsql($test, $resok, $resko)
Format a SQL IF.
getDefaultCollationDatabase()
Return collation used in database.
convertSQLFromMysql($line, $type='auto', $unescapeslashquot=false)
Convert a SQL request in Mysql syntax to native syntax.
getDefaultCharacterSetDatabase()
Return charset used to store data in database.
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Convert request to PostgreSQL syntax, execute it and return the resultset.
$forcecollate
Collate used to force collate when creating database.
Definition: pgsql.class.php:48
fetch_row($resultset)
Return datas as an array.
getServerParametersValues($filter='')
Return value of server parameters.
DDLListTables($database, $table='')
List tables into a database.
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
getDolGlobalInt($key, $default=0)
Return dolibarr global constant int value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.