31 require_once DOL_DOCUMENT_ROOT.
'/core/db/DoliDB.class.php';
56 public $unescapeslashquot;
57 public $standard_conforming_strings;
76 if (!empty($conf->db->character_set)) {
77 $this->forcecharset = $conf->db->character_set;
79 if (!empty($conf->db->dolibarr_main_db_collation)) {
80 $this->forcecollate = $conf->db->dolibarr_main_db_collation;
83 $this->database_user = $user;
84 $this->database_host = $host;
85 $this->database_port = $port;
87 $this->transaction_opened = 0;
91 if (!function_exists(
"pg_connect")) {
92 $this->connected =
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);
100 $this->connected =
false;
102 $this->
error = $langs->trans(
"ErrorWrongHostParameter");
103 dol_syslog(get_class($this).
"::DoliDBPgsql : Erreur Connect, wrong host parameters", LOG_ERR);
109 $this->
db = $this->
connect($host, $user, $pass, $name, $port);
112 $this->connected =
true;
116 $this->connected =
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);
123 if ($this->connected && $name) {
125 $this->database_selected =
true;
126 $this->database_name = $name;
129 $this->database_selected =
false;
130 $this->database_name =
'';
133 dol_syslog(get_class($this).
"::DoliDBPgsql : Erreur Select_db ".$this->
error, LOG_ERR);
137 $this->database_selected =
false;
157 if (preg_match(
'/^--\s\$Id/i', $line)) {
161 if (preg_match(
'/^#/i', $line) || preg_match(
'/^$/i', $line) || preg_match(
'/^--/i', $line)) {
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);
172 if (
$type ==
'auto') {
173 if (preg_match(
'/ALTER TABLE/i', $line)) {
175 } elseif (preg_match(
'/CREATE TABLE/i', $line)) {
177 } elseif (preg_match(
'/DROP TABLE/i', $line)) {
182 $line = preg_replace(
'/ as signed\)/i',
' as integer)', $line);
184 if (
$type ==
'dml') {
187 $line = preg_replace(
'/\s/',
' ', $line);
190 if (preg_match(
'/(ISAM|innodb)/i', $line)) {
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);
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);
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);
210 $line = preg_replace(
'/tinyint\(?[0-9]*\)?/',
'smallint', $line);
211 $line = preg_replace(
'/tinyint/i',
'smallint', $line);
214 $line = preg_replace(
'/(int\w+|smallint|bigint)\s+unsigned/i',
'\\1', $line);
217 $line = preg_replace(
'/\w*blob/i',
'text', $line);
220 $line = preg_replace(
'/tinytext/i',
'text', $line);
221 $line = preg_replace(
'/mediumtext/i',
'text', $line);
222 $line = preg_replace(
'/longtext/i',
'text', $line);
224 $line = preg_replace(
'/text\([0-9]+\)/i',
'text', $line);
228 $line = preg_replace(
'/datetime not null/i',
'datetime', $line);
229 $line = preg_replace(
'/datetime/i',
'timestamp', $line);
232 $line = preg_replace(
'/^double/i',
'numeric', $line);
233 $line = preg_replace(
'/(\s*)double/i',
'\\1numeric', $line);
235 $line = preg_replace(
'/^float/i',
'numeric', $line);
236 $line = preg_replace(
'/(\s*)float/i',
'\\1numeric', $line);
240 $line = preg_replace(
'/(\s*)tms(\s*)timestamp/i',
'\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line);
243 $line = preg_replace(
'/(\s*)DEFAULT(\s*)CURRENT_TIMESTAMP/i',
'\\1', $line);
246 $line = preg_replace(
'/(\s*)ON(\s*)UPDATE(\s*)CURRENT_TIMESTAMP/i',
'\\1', $line);
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);
254 $line = preg_replace(
'/\sAFTER [a-z0-9_]+/i',
'', $line);
257 $line = preg_replace(
'/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i',
'DROP INDEX', $line);
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];
266 if (preg_match(
'/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) {
267 $line =
"-- ".$line.
" replaced by --\n";
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;
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];
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];
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];
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;";
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];
311 $tablename = $reg[1];
312 $line =
"-- ".$line.
" replaced by --\n";
313 $line .=
"CREATE ".(preg_match(
'/UNIQUE/', $reg[2]) ?
'UNIQUE ' :
'').
"INDEX ".$idxname.
" ON ".$tablename.
" (".$fieldlist.
")";
319 $line = str_replace(
' LIKE \'',
' ILIKE \'', $line, $count_like);
320 if (!empty($conf->global->PSQL_USE_UNACCENT) && $count_like > 0) {
322 $line = preg_replace(
'/\s+(\(+\s*)([a-zA-Z0-9\-\_\.]+) ILIKE /',
' \1unaccent(\2) ILIKE ', $line);
325 $line = str_replace(
' LIKE BINARY \'',
' LIKE \'', $line);
328 $line = preg_replace(
'/^INSERT IGNORE/',
'INSERT', $line);
332 if (preg_match(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) {
333 if ($reg[1] == $reg[2]) {
334 $line = preg_replace(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i',
'DELETE FROM \\1 USING \\3', $line);
339 $line = preg_replace(
'/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i',
'FROM \\1', $line);
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);
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);
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);
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);
362 if ($unescapeslashquot) {
363 $line = preg_replace(
"/\\\'/",
"''", $line);
384 if ($database == $this->database_name) {
402 public function connect($host, $login, $passwd, $name, $port = 0)
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);
420 if ((!empty($host) && $host ==
"socket") && !defined(
'NOLOCALSOCKETPGCONNECT')) {
421 $con_string =
"dbname='".$name.
"' user='".$login.
"' password='".$passwd.
"'";
423 $this->
db = @pg_connect($con_string);
430 if (empty($this->
db)) {
438 $con_string =
"host='".$host.
"' port='".$port.
"' dbname='".$name.
"' user='".$login.
"' password='".$passwd.
"'";
440 $this->
db = @pg_connect($con_string);
442 print $e->getMessage();
448 $this->database_name = $name;
449 pg_set_error_verbosity($this->
db, PGSQL_ERRORS_VERBOSE);
450 pg_query($this->
db,
"set datestyle = 'ISO, YMD';");
466 return $liste[
'server_version'];
478 return 'pgsql php driver';
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);
493 $this->connected =
false;
494 return pg_close($this->
db);
508 public function query($query, $usesavepoint = 0,
$type =
'auto', $result_mode = 0)
510 global $conf, $dolibarr_main_db_readonly;
512 $query = trim($query);
515 $query = $this->
convertSQLFromMysql($query,
$type, ($this->unescapeslashquot && $this->standard_conforming_strings));
518 if (!empty($conf->global->MAIN_DB_AUTOFIX_BAD_SQL_REQUEST)) {
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);
531 if ($usesavepoint && $this->transaction_opened) {
532 @pg_query($this->
db,
'SAVEPOINT mysavepoint');
535 if (!in_array($query, array(
'BEGIN',
'COMMIT',
'ROLLBACK'))) {
536 $SYSLOG_SQL_LIMIT = 10000;
537 dol_syslog(
'sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
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';
552 $ret = @pg_query($this->
db, $query);
555 if (!preg_match(
"/^COMMIT/i", $query) && !preg_match(
"/^ROLLBACK/i", $query)) {
557 if ($this->
errno() !=
'DB_ERROR_25P02') {
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);
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);
569 if ($usesavepoint && $this->transaction_opened) {
570 @pg_query($this->
db,
'ROLLBACK TO SAVEPOINT mysavepoint');
574 $this->_results = $ret;
591 if (!is_resource($resultset) && !is_object($resultset)) {
592 $resultset = $this->_results;
594 return pg_fetch_object($resultset);
608 if (!is_resource($resultset) && !is_object($resultset)) {
609 $resultset = $this->_results;
611 return pg_fetch_array($resultset);
625 if (!is_resource($resultset) && !is_object($resultset)) {
626 $resultset = $this->_results;
628 return pg_fetch_row($resultset);
643 if (!is_resource($resultset) && !is_object($resultset)) {
644 $resultset = $this->_results;
646 return pg_num_rows($resultset);
661 if (!is_resource($resultset) && !is_object($resultset)) {
662 $resultset = $this->_results;
666 return pg_affected_rows($resultset);
676 public function free($resultset =
null)
679 if (!is_resource($resultset) && !is_object($resultset)) {
680 $resultset = $this->_results;
683 if (is_resource($resultset) || is_object($resultset)) {
684 pg_free_result($resultset);
696 public function plimit($limit = 0, $offset = 0)
703 $limit = $conf->liste_limit;
706 return " LIMIT ".$limit.
" OFFSET ".$offset.
" ";
708 return " LIMIT $limit ";
721 return pg_escape_string($stringtoencode);
733 return str_replace(
'_',
'\_', (
string) $stringtoencode);
744 return str_replace(array(
'\\',
'_',
'%'), array(
'\\\\',
'\_',
'\%'), (
string) $stringtoencode);
755 public function ifsql($test, $resok, $resko)
757 return '(CASE WHEN '.$test.
' THEN '.$resok.
' ELSE '.$resko.
' END)';
768 public function regexpsql($subject, $pattern, $sqlstring =
false)
771 return "(". $subject .
" ~ '" . $pattern .
"')";
774 return "('". $subject .
"' ~ '" . $pattern .
"')";
785 if (!$this->connected) {
787 return 'DB_ERROR_FAILED_TO_CONNECT';
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',
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'
820 $errorlabel = pg_last_error($this->
db);
823 if (preg_match(
'/: *([0-9P]+):/', $errorlabel, $reg)) {
824 $errorcode = $reg[1];
825 if (isset($errorcode_map[$errorcode])) {
826 return $errorcode_map[$errorcode];
829 $errno = $errorcode ? $errorcode : $errorlabel;
830 return ($errno ?
'DB_ERROR_'.$errno :
'0');
849 return pg_last_error($this->
db);
864 $result = pg_query($this->
db,
"SELECT currval('".$tab.
"_".$fieldid.
"_seq')");
866 print pg_last_error($this->
db);
870 $row = pg_fetch_result($result, 0, 0);
882 public function encrypt($fieldorvalue, $withQuotes = 1)
892 $return = $fieldorvalue;
893 return ($withQuotes ?
"'" :
"").$this->escape($return).($withQuotes ?
"'" :
"");
944 public function DDLCreateDb($database, $charset =
'', $collation =
'', $owner =
'')
947 if (empty($charset)) {
950 if (empty($collation)) {
958 $sql =
"CREATE DATABASE ".$this->escape($database).
" OWNER '".$this->
escape($owner).
"' ENCODING '".$this->
escape($charset).
"'";
960 $ret = $this->
query($sql);
975 $listtables = array();
979 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_%]/i',
'', $table);
981 $escapedlike =
" AND table_name LIKE '".$this->escape($tmptable).
"'";
983 $result = pg_query($this->
db,
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike.
" ORDER BY table_name");
985 while ($row = $this->
fetch_row($result)) {
986 $listtables[] = $row[0];
1003 $infotables = array();
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;";
1022 $result = $this->
query($sql);
1024 while ($row = $this->
fetch_row($result)) {
1025 $infotables[] = $row;
1045 public function DDLCreateTable($table, $fields, $primary_key,
$type, $unique_keys =
null, $fulltext_keys =
null, $keys =
null)
1052 $sql =
"create table ".$table.
"(";
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'];
1065 $sqlfields[$i] .=
" default '".$this->escape($field_desc[
'default']).
"'";
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'];
1074 if ($primary_key !=
"") {
1075 $pk =
"primary key(".$primary_key.
")";
1078 if (is_array($unique_keys)) {
1080 foreach ($unique_keys as $key => $value) {
1081 $sqluq[$i] =
"UNIQUE KEY '".$key.
"' ('".$this->
escape($value).
"')";
1085 if (is_array($keys)) {
1087 foreach ($keys as $key => $value) {
1088 $sqlk[$i] =
"KEY ".$key.
" (".$value.
")";
1092 $sql .= implode(
',', $sqlfields);
1093 if ($primary_key !=
"") {
1096 if (is_array($unique_keys)) {
1097 $sql .=
",".implode(
',', $sqluq);
1099 if (is_array($keys)) {
1100 $sql .=
",".implode(
',', $sqlk);
1102 $sql .=
") type=".$type;
1105 if (!$this->
query($sql)) {
1122 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $table);
1124 $sql =
"DROP TABLE ".$tmptable;
1126 if (!$this->
query($sql)) {
1143 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1147 $sql =
"CREATE USER ".$this->escape($dolibarr_main_db_user).
" with password '".$this->
escape($dolibarr_main_db_pass).
"'";
1149 dol_syslog(get_class($this).
"::DDLCreateUser", LOG_DEBUG);
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')";
1172 $sql .=
" AND attname = '".$this->escape($field).
"'";
1176 $this->_results = $this->
query($sql);
1177 return $this->_results;
1190 public function DDLAddField($table, $field_name, $field_desc, $field_position =
"")
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'].
")";
1202 if (preg_match(
"/^[^\s]/i", $field_desc[
'attribute'])) {
1203 $sql .=
" ".$field_desc[
'attribute'];
1205 if (preg_match(
"/^[^\s]/i", $field_desc[
'null'])) {
1206 $sql .=
" ".$field_desc[
'null'];
1208 if (preg_match(
"/^[^\s]/i", $field_desc[
'default'])) {
1209 if (preg_match(
"/null/i", $field_desc[
'default'])) {
1210 $sql .=
" default ".$field_desc[
'default'];
1212 $sql .=
" default '".$this->escape($field_desc[
'default']).
"'";
1215 if (preg_match(
"/^[^\s]/i", $field_desc[
'extra'])) {
1216 $sql .=
" ".$field_desc[
'extra'];
1218 $sql .=
" ".$field_position;
1221 if (!$this ->
query($sql)) {
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'].
")";
1245 if ($field_desc[
'null'] ==
'not null' || $field_desc[
'null'] ==
'NOT 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);
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']).
"'";
1265 if (!$this->
query($sql)) {
1282 $tmp_field_name = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $field_name);
1284 $sql =
"ALTER TABLE ".$table.
" DROP COLUMN ".$tmp_field_name;
1285 if (!$this->
query($sql)) {
1302 return $liste[
'server_encoding'];
1320 $liste[$i][
'charset'] = $obj->server_encoding;
1321 $liste[$i][
'description'] =
'Default database charset';
1341 return $liste[
'lc_collate'];
1359 $liste[$i][
'collation'] = $obj->lc_collate;
1376 $fullpathofdump =
'/pathtopgdump/pg_dump';
1378 if (file_exists(
'/usr/bin/pg_dump')) {
1379 $fullpathofdump =
'/usr/bin/pg_dump';
1385 $basedir = $liste[
'data_directory'];
1386 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/pg_dump';
1390 return $fullpathofdump;
1403 $fullpathofdump =
'/pathtopgrestore/'.$tool;
1405 if (file_exists(
'/usr/bin/'.$tool)) {
1406 $fullpathofdump =
'/usr/bin/'.$tool;
1412 $basedir = $liste[
'data_directory'];
1413 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/'.$tool;
1417 return $fullpathofdump;
1430 $resql =
'select name,setting from pg_settings';
1432 $resql .=
" WHERE name = '".$this->escape($filter).
"'";
1437 $result[$obj->name] = $obj->setting;
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.
errno()
Renvoie le code erreur generique de l'operation precedente.
DDLGetConnectId()
Return connexion ID.
num_rows($resultset)
Return number of lines for result of a SELECT.
const VERSIONMIN
Version min database.
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
static convertSQLFromMysql($line, $type='auto', $unescapeslashquot=false)
Convert a SQL request in Mysql syntax to native syntax.
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.
escapeunderscore($stringtoencode)
Escape a string to insert data.
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.
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.
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.
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.
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.
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') &&!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.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
$conf db
API class for accounts.