32require_once DOL_DOCUMENT_ROOT.
'/core/db/DoliDB.class.php';
57 public $unescapeslashquot =
false;
61 public $standard_conforming_strings =
false;
85 if (!empty($conf->db->character_set)) {
86 $this->forcecharset = $conf->db->character_set;
88 if (!empty($conf->db->dolibarr_main_db_collation)) {
89 $this->forcecollate = $conf->db->dolibarr_main_db_collation;
92 $this->database_user = $user;
93 $this->database_host = $host;
94 $this->database_port = $port;
96 $this->transaction_opened = 0;
100 if (!function_exists(
"pg_connect")) {
101 $this->connected =
false;
103 $this->
error =
"Pgsql PHP functions are not available in this version of PHP";
104 dol_syslog(get_class($this).
"::DoliDBPgsql : Pgsql PHP functions are not available in this version of PHP", LOG_ERR);
109 $this->connected =
false;
111 $this->
error = $langs->trans(
"ErrorWrongHostParameter");
112 dol_syslog(get_class($this).
"::DoliDBPgsql : Erreur Connect, wrong host parameters", LOG_ERR);
118 $this->db = $this->
connect($host, $user, $pass, $name, $port);
121 $this->connected =
true;
125 $this->connected =
false;
127 $this->
error =
'Host, login or password incorrect';
128 dol_syslog(get_class($this).
"::DoliDBPgsql : Erreur Connect ".$this->
error.
'. Failed to connect to host='.$host.
' port='.$port.
' user='.$user, LOG_ERR);
132 if ($this->connected && $name) {
134 $this->database_selected =
true;
135 $this->database_name = $name;
138 $this->database_selected =
false;
139 $this->database_name =
'';
142 dol_syslog(get_class($this).
"::DoliDBPgsql : Erreur Select_db ".$this->
error, LOG_ERR);
146 $this->database_selected =
false;
164 if (preg_match(
'/^--\s\$Id/i', $line)) {
168 if (preg_match(
'/^#/i', $line) || preg_match(
'/^$/i', $line) || preg_match(
'/^--/i', $line)) {
174 $line = preg_replace(
'/GROUP_CONCAT/i',
'STRING_AGG', $line);
175 $line = preg_replace(
'/ SEPARATOR/i',
',', $line);
176 $line = preg_replace(
'/STRING_AGG\(([^,\)]+)\)/i',
'STRING_AGG(\\1, \',\')', $line);
179 if (
$type ==
'auto') {
180 if (preg_match(
'/ALTER TABLE/i', $line)) {
182 } elseif (preg_match(
'/CREATE TABLE/i', $line)) {
184 } elseif (preg_match(
'/DROP TABLE/i', $line)) {
189 $line = preg_replace(
'/ as signed\)/i',
' as integer)', $line);
191 if (
$type ==
'dml') {
194 $line = preg_replace(
'/\s/',
' ', $line);
197 if (preg_match(
'/(ISAM|innodb)/i', $line)) {
198 $line = preg_replace(
'/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i',
');', $line);
199 $line = preg_replace(
'/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i',
');', $line);
200 $line = preg_replace(
'/,$/',
'', $line);
204 if (preg_match(
'/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) {
205 $newline = preg_replace(
'/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i',
'\\1 \\2 SERIAL PRIMARY KEY', $line);
210 if (preg_match(
'/[\s\t\(]*(\w*)[\s\t]+bigint.*auto_increment/i', $line, $reg)) {
211 $newline = preg_replace(
'/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+bigint.*auto_increment[^,]*/i',
'\\1 \\2 BIGSERIAL PRIMARY KEY', $line);
217 $line = preg_replace(
'/tinyint\(?[0-9]*\)?/',
'smallint', $line);
218 $line = preg_replace(
'/tinyint/i',
'smallint', $line);
221 $line = preg_replace(
'/(int\w+|smallint|bigint)\s+unsigned/i',
'\\1', $line);
224 $line = preg_replace(
'/\w*blob/i',
'text', $line);
227 $line = preg_replace(
'/tinytext/i',
'text', $line);
228 $line = preg_replace(
'/mediumtext/i',
'text', $line);
229 $line = preg_replace(
'/longtext/i',
'text', $line);
231 $line = preg_replace(
'/text\([0-9]+\)/i',
'text', $line);
235 $line = preg_replace(
'/datetime not null/i',
'datetime', $line);
236 $line = preg_replace(
'/datetime/i',
'timestamp', $line);
239 $line = preg_replace(
'/^double/i',
'numeric', $line);
240 $line = preg_replace(
'/(\s*)double/i',
'\\1numeric', $line);
242 $line = preg_replace(
'/^float/i',
'numeric', $line);
243 $line = preg_replace(
'/(\s*)float/i',
'\\1numeric', $line);
247 $line = preg_replace(
'/(\s*)tms(\s*)timestamp/i',
'\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line);
250 $line = preg_replace(
'/(\s*)DEFAULT(\s*)CURRENT_TIMESTAMP/i',
'\\1', $line);
253 $line = preg_replace(
'/(\s*)ON(\s*)UPDATE(\s*)CURRENT_TIMESTAMP/i',
'\\1', $line);
256 if (preg_match(
'/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) {
257 $line = preg_replace(
'/unique index\s*\((\w+\s*,\s*\w+)\)/i',
'UNIQUE\(\\1\)', $line);
261 $line = preg_replace(
'/\sAFTER [a-z0-9_]+/i',
'', $line);
264 $line = preg_replace(
'/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i',
'DROP INDEX', $line);
267 if (preg_match(
'/ALTER TABLE ([a-z0-9_]+)\s+CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) {
268 $line =
"-- ".$line.
" replaced by --\n";
269 $line .=
"ALTER TABLE ".$reg[1].
" RENAME COLUMN ".$reg[2].
" TO ".$reg[3];
273 if (preg_match(
'/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) {
274 $line =
"-- ".$line.
" replaced by --\n";
276 $newreg3 = preg_replace(
'/ DEFAULT NULL/i',
'', $newreg3);
277 $newreg3 = preg_replace(
'/ NOT NULL/i',
'', $newreg3);
278 $newreg3 = preg_replace(
'/ NULL/i',
'', $newreg3);
279 $newreg3 = preg_replace(
'/ DEFAULT 0/i',
'', $newreg3);
280 $newreg3 = preg_replace(
'/ DEFAULT \'?[0-9a-zA-Z_@]*\'?/i',
'', $newreg3);
281 $line .=
"ALTER TABLE ".$reg[1].
" ALTER COLUMN ".$reg[2].
" TYPE ".$newreg3;
287 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) {
288 $line =
"-- ".$line.
" replaced by --\n";
289 $line .=
"ALTER TABLE ".$reg[1].
" ADD PRIMARY KEY (".$reg[3];
294 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*DROP\s+PRIMARY\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*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) {
302 $line =
"-- ".$line.
" replaced by --\n";
303 $line .=
"ALTER TABLE ".$reg[1].
" DROP CONSTRAINT ".$reg[2];
308 if (preg_match(
'/ALTER\s+TABLE\s+(.*)\s*ADD CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) {
309 $line = preg_replace(
'/;$/',
'', $line);
310 $line .=
" DEFERRABLE INITIALLY IMMEDIATE;";
315 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) {
316 $fieldlist = $reg[4];
318 $tablename = $reg[1];
319 $line =
"-- ".$line.
" replaced by --\n";
320 $line .=
"CREATE ".(preg_match(
'/UNIQUE/', $reg[2]) ?
'UNIQUE ' :
'').
"INDEX ".$idxname.
" ON ".$tablename.
" (".$fieldlist.
")";
326 $line = str_replace(
' LIKE \'',
' ILIKE \'', $line, $count_like);
329 $line = preg_replace(
'/\s+(\(+\s*)([a-zA-Z0-9\-\_\.]+) ILIKE /',
' \1unaccent(\2) ILIKE ', $line);
332 $line = str_replace(
' LIKE BINARY \'',
' LIKE \'', $line);
335 $line = preg_replace(
'/^INSERT IGNORE/',
'INSERT', $line);
339 if (preg_match(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) {
340 if ($reg[1] == $reg[2]) {
341 $line = preg_replace(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i',
'DELETE FROM \\1 USING \\3', $line);
346 $line = preg_replace(
'/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i',
'FROM \\1', $line);
350 $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);
354 $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);
358 $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);
362 $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);
369 if ($unescapeslashquot) {
370 $line = preg_replace(
"/\\\'/",
"''", $line);
391 if ($database == $this->database_name) {
409 public function connect($host, $login, $passwd, $name, $port = 0)
416 $host = str_replace(array(
"\\",
"'"), array(
"\\\\",
"\\'"), $host);
417 $login = str_replace(array(
"\\",
"'"), array(
"\\\\",
"\\'"), $login);
418 $passwd = str_replace(array(
"\\",
"'"), array(
"\\\\",
"\\'"), $passwd);
419 $name = str_replace(array(
"\\",
"'"), array(
"\\\\",
"\\'"), $name);
420 $port = str_replace(array(
"\\",
"'"), array(
"\\\\",
"\\'"), (
string) $port);
427 if ((!empty($host) && $host ==
"socket") && !defined(
'NOLOCALSOCKETPGCONNECT')) {
428 $con_string =
"dbname='".$name.
"' user='".$login.
"' password='".$passwd.
"'";
430 $this->db = @pg_connect($con_string);
437 if (empty($this->db)) {
445 $con_string =
"host='".$host.
"' port='".$port.
"' dbname='".$name.
"' user='".$login.
"' password='".$passwd.
"'";
447 $this->db = @pg_connect($con_string);
449 print $e->getMessage();
455 $this->database_name = $name;
456 pg_set_error_verbosity($this->db, PGSQL_ERRORS_VERBOSE);
457 pg_query($this->db,
"set datestyle = 'ISO, YMD';");
470 $resql = $this->
query(
'SHOW server_version');
473 return $liste[
'server_version'];
485 return 'pgsql php driver';
497 if ($this->transaction_opened > 0) {
498 dol_syslog(get_class($this).
"::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
500 $this->connected =
false;
501 return pg_close($this->db);
515 public function query($query, $usesavepoint = 0,
$type =
'auto', $result_mode = 0)
517 global $dolibarr_main_db_readonly;
519 $query = trim($query);
522 $query = $this->
convertSQLFromMysql($query,
$type, ($this->unescapeslashquot && $this->standard_conforming_strings));
529 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)) {
530 $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);
531 dol_syslog(
"Warning: Bad formed request converted into ".$query, LOG_WARNING);
538 if ($usesavepoint && $this->transaction_opened) {
539 @pg_query($this->db,
'SAVEPOINT mysavepoint');
542 if (!in_array($query, array(
'BEGIN',
'COMMIT',
'ROLLBACK'))) {
543 $SYSLOG_SQL_LIMIT = 10000;
544 dol_syslog(
'sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
550 if (!empty($dolibarr_main_db_readonly)) {
551 if (preg_match(
'/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
552 $this->
lasterror =
'Application in read-only mode';
559 $ret = @pg_query($this->db, $query);
562 if (!preg_match(
"/^COMMIT/i", $query) && !preg_match(
"/^ROLLBACK/i", $query)) {
564 if ($this->
errno() !=
'DB_ERROR_25P02') {
570 dol_syslog(get_class($this).
"::query SQL Error query: ".$query, LOG_ERR);
572 dol_syslog(get_class($this).
"::query SQL Error message: ".$this->
lasterror.
" (".$this->lasterrno.
")", LOG_ERR);
573 dol_syslog(get_class($this).
"::query SQL Error usesavepoint = ".$usesavepoint, LOG_ERR);
576 if ($usesavepoint && $this->transaction_opened) {
577 @pg_query($this->db,
'ROLLBACK TO SAVEPOINT mysavepoint');
581 $this->_results = $ret;
598 if (!is_resource($resultset) && !is_object($resultset)) {
599 $resultset = $this->_results;
601 return pg_fetch_object($resultset);
615 if (!is_resource($resultset) && !is_object($resultset)) {
616 $resultset = $this->_results;
618 return pg_fetch_array($resultset);
632 if (!is_resource($resultset) && !is_object($resultset)) {
633 $resultset = $this->_results;
635 return pg_fetch_row($resultset);
650 if (!is_resource($resultset) && !is_object($resultset)) {
651 $resultset = $this->_results;
653 return pg_num_rows($resultset);
668 if (!is_resource($resultset) && !is_object($resultset)) {
669 $resultset = $this->_results;
673 return pg_affected_rows($resultset);
683 public function free($resultset =
null)
686 if (!is_resource($resultset) && !is_object($resultset)) {
687 $resultset = $this->_results;
690 if (is_resource($resultset) || is_object($resultset)) {
691 pg_free_result($resultset);
703 public function plimit($limit = 0, $offset = 0)
710 $limit = $conf->liste_limit;
713 return " LIMIT ".$limit.
" OFFSET ".$offset.
" ";
715 return " LIMIT $limit ";
728 return pg_escape_string($this->db, $stringtoencode);
739 return str_replace(array(
'\\',
'_',
'%'), array(
'\\\\',
'\_',
'\%'), (
string) $stringtoencode);
750 public function ifsql($test, $resok, $resko)
752 return '(CASE WHEN '.$test.
' THEN '.$resok.
' ELSE '.$resko.
' END)';
763 public function regexpsql($subject, $pattern, $sqlstring = 0)
766 return "(". $subject .
" ~ '" . $this->
escape($pattern) .
"')";
769 return "('". $this->
escape($subject) .
"' ~ '" . $this->
escape($pattern) .
"')";
780 if (!$this->connected) {
782 return 'DB_ERROR_FAILED_TO_CONNECT';
785 $errorcode_map = array(
786 1004 =>
'DB_ERROR_CANNOT_CREATE',
787 1005 =>
'DB_ERROR_CANNOT_CREATE',
788 1006 =>
'DB_ERROR_CANNOT_CREATE',
789 1007 =>
'DB_ERROR_ALREADY_EXISTS',
790 1008 =>
'DB_ERROR_CANNOT_DROP',
791 1025 =>
'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
792 1044 =>
'DB_ERROR_ACCESSDENIED',
793 1046 =>
'DB_ERROR_NODBSELECTED',
794 1048 =>
'DB_ERROR_CONSTRAINT',
795 '42P07' =>
'DB_ERROR_TABLE_OR_KEY_ALREADY_EXISTS',
796 '42703' =>
'DB_ERROR_NOSUCHFIELD',
797 1060 =>
'DB_ERROR_COLUMN_ALREADY_EXISTS',
798 42701 =>
'DB_ERROR_COLUMN_ALREADY_EXISTS',
799 '42710' =>
'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
800 '23505' =>
'DB_ERROR_RECORD_ALREADY_EXISTS',
801 '42704' =>
'DB_ERROR_NO_INDEX_TO_DROP',
802 '42601' =>
'DB_ERROR_SYNTAX',
803 '42P16' =>
'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
804 1075 =>
'DB_ERROR_CANT_DROP_PRIMARY_KEY',
805 1091 =>
'DB_ERROR_NOSUCHFIELD',
806 1100 =>
'DB_ERROR_NOT_LOCKED',
807 1136 =>
'DB_ERROR_VALUE_COUNT_ON_ROW',
808 '42P01' =>
'DB_ERROR_NOSUCHTABLE',
809 '23503' =>
'DB_ERROR_NO_PARENT',
810 1217 =>
'DB_ERROR_CHILD_EXISTS',
811 1451 =>
'DB_ERROR_CHILD_EXISTS',
812 '42P04' =>
'DB_DATABASE_ALREADY_EXISTS'
815 $errorlabel = pg_last_error($this->db);
818 if (preg_match(
'/: *([0-9P]+):/', $errorlabel, $reg)) {
819 $errorcode = $reg[1];
820 if (isset($errorcode_map[$errorcode])) {
821 return $errorcode_map[$errorcode];
824 $errno = $errorcode ? $errorcode : $errorlabel;
825 return ($errno ?
'DB_ERROR_'.$errno :
'0');
844 return pg_last_error($this->db);
859 $result = pg_query($this->db,
"SELECT currval('".$tab.
"_".$fieldid.
"_seq')");
861 print pg_last_error($this->db);
865 $row = pg_fetch_result($result, 0, 0);
877 public function encrypt($fieldorvalue, $withQuotes = 1)
887 $return = $fieldorvalue;
888 return ($withQuotes ?
"'" :
"").$this->
escape($return).($withQuotes ?
"'" :
"");
939 public function DDLCreateDb($database, $charset =
'', $collation =
'', $owner =
'')
942 if (empty($charset)) {
945 if (empty($collation)) {
953 $sql =
"CREATE DATABASE ".$this->escape($database).
" OWNER '".$this->
escape($owner).
"' ENCODING '".$this->
escape($charset).
"'";
956 $ret = $this->
query($sql);
972 $listtables = array();
976 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_%]/i',
'', $table);
978 $escapedlike =
" AND table_name LIKE '".$this->escape($tmptable).
"'";
980 $result = pg_query($this->db,
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike.
" ORDER BY table_name");
982 while ($row = $this->
fetch_row($result)) {
983 $listtables[] = $row[0];
1000 $listtables = array();
1004 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_%]/i',
'', $table);
1006 $escapedlike =
" AND table_name LIKE '".$this->escape($tmptable).
"'";
1008 $result = pg_query($this->db,
"SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike.
" ORDER BY table_name");
1010 while ($row = $this->
fetch_row($result)) {
1011 $listtables[] = $row;
1027 $infotables = array();
1030 $sql .=
" infcol.column_name as 'Column',";
1031 $sql .=
" CASE WHEN infcol.character_maximum_length IS NOT NULL THEN infcol.udt_name || '('||infcol.character_maximum_length||')'";
1032 $sql .=
" ELSE infcol.udt_name";
1033 $sql .=
" END as 'Type',";
1034 $sql .=
" infcol.collation_name as 'Collation',";
1035 $sql .=
" infcol.is_nullable as 'Null',";
1036 $sql .=
" '' as 'Key',";
1037 $sql .=
" infcol.column_default as 'Default',";
1038 $sql .=
" '' as 'Extra',";
1039 $sql .=
" '' as 'Privileges'";
1040 $sql .=
" FROM information_schema.columns infcol";
1041 $sql .=
" WHERE table_schema = 'public' ";
1042 $sql .=
" AND table_name = '".$this->escape($table).
"'";
1043 $sql .=
" ORDER BY ordinal_position;";
1045 $result = $this->
query($sql);
1047 while ($row = $this->
fetch_row($result)) {
1048 $infotables[] = $row;
1068 public function DDLCreateTable($table, $fields, $primary_key,
$type, $unique_keys =
null, $fulltext_keys =
null, $keys =
null)
1083 $sql =
"CREATE TABLE ".$this->sanitize($table).
"(";
1085 $sqlfields = array();
1086 foreach ($fields as $field_name => $field_desc) {
1087 $sqlfields[$i] = $this->
sanitize($field_name).
" ";
1088 $sqlfields[$i] .= $this->
sanitize($field_desc[
'type']);
1089 if (isset($field_desc[
'value']) && $field_desc[
'value'] !==
'') {
1090 $sqlfields[$i] .=
"(".$this->sanitize($field_desc[
'value']).
")";
1092 if (isset($field_desc[
'attribute']) && $field_desc[
'attribute'] !==
'') {
1093 $sqlfields[$i] .=
" ".$this->sanitize($field_desc[
'attribute']);
1095 if (isset($field_desc[
'default']) && $field_desc[
'default'] !==
'') {
1096 if (in_array($field_desc[
'type'], array(
'tinyint',
'smallint',
'int',
'double'))) {
1097 $sqlfields[$i] .=
" DEFAULT ".((float) $field_desc[
'default']);
1098 } elseif ($field_desc[
'default'] ==
'null' || $field_desc[
'default'] ==
'CURRENT_TIMESTAMP') {
1099 $sqlfields[$i] .=
" DEFAULT ".$this->sanitize($field_desc[
'default']);
1101 $sqlfields[$i] .=
" DEFAULT '".$this->escape($field_desc[
'default']).
"'";
1104 if (isset($field_desc[
'null']) && $field_desc[
'null'] !==
'') {
1105 $sqlfields[$i] .=
" ".$this->sanitize($field_desc[
'null'], 0, 0, 1);
1107 if (isset($field_desc[
'extra']) && $field_desc[
'extra'] !==
'') {
1108 $sqlfields[$i] .=
" ".$this->sanitize($field_desc[
'extra'], 0, 0, 1);
1110 if (!empty($primary_key) && $primary_key == $field_name) {
1111 $sqlfields[$i] .=
" AUTO_INCREMENT PRIMARY KEY";
1116 if (is_array($unique_keys)) {
1118 foreach ($unique_keys as $key => $value) {
1119 $sqluq[$i] =
"UNIQUE KEY '".$this->sanitize($key).
"' ('".$this->
escape($value).
"')";
1123 if (is_array($keys)) {
1125 foreach ($keys as $key => $value) {
1126 $sqlk[$i] =
"KEY ".$this->sanitize($key).
" (".$value.
")";
1130 $sql .= implode(
', ', $sqlfields);
1131 if ($unique_keys !=
"") {
1132 $sql .=
",".implode(
',', $sqluq);
1134 if (is_array($keys)) {
1135 $sql .=
",".implode(
',', $sqlk);
1140 if (!$this->
query($sql, 1)) {
1157 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $table);
1159 $sql =
"DROP TABLE ".$this->sanitize($tmptable);
1161 if (!$this->
query($sql, 1)) {
1179 $sql =
"SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$this->escape($table).
"' AND attrelid = typrelid";
1180 $sql .=
" AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')";
1182 $sql .=
" AND attname = '".$this->escape($field).
"'";
1186 $this->_results = $this->
query($sql);
1187 return $this->_results;
1200 public function DDLAddField($table, $field_name, $field_desc, $field_position =
"")
1205 $sql =
"ALTER TABLE ".$this->sanitize($table).
" ADD ".$this->
sanitize($field_name).
" ";
1206 $sql .= $this->
sanitize($field_desc[
'type']);
1207 if (isset($field_desc[
'value']) && preg_match(
"/^[^\s]/i", $field_desc[
'value'])) {
1208 if (!in_array($field_desc[
'type'], array(
'tinyint',
'smallint',
'int',
'date',
'datetime')) && $field_desc[
'value']) {
1209 $sql .=
"(".$this->sanitize($field_desc[
'value']).
")";
1212 if (isset($field_desc[
'attribute']) && preg_match(
"/^[^\s]/i", $field_desc[
'attribute'])) {
1213 $sql .=
" ".$this->sanitize($field_desc[
'attribute']);
1215 if (isset($field_desc[
'null']) && preg_match(
"/^[^\s]/i", $field_desc[
'null'])) {
1216 $sql .=
" ".$field_desc[
'null'];
1218 if (isset($field_desc[
'default']) && preg_match(
"/^[^\s]/i", $field_desc[
'default'])) {
1219 if (in_array($field_desc[
'type'], array(
'tinyint',
'smallint',
'int',
'double'))) {
1220 $sql .=
" DEFAULT ".((float) $field_desc[
'default']);
1221 } elseif ($field_desc[
'default'] ==
'null' || $field_desc[
'default'] ==
'CURRENT_TIMESTAMP') {
1222 $sql .=
" DEFAULT ".$this->sanitize($field_desc[
'default']);
1224 $sql .=
" DEFAULT '".$this->escape($field_desc[
'default']).
"'";
1227 if (isset($field_desc[
'extra']) && preg_match(
"/^[^\s]/i", $field_desc[
'extra'])) {
1228 $sql .=
" ".$this->sanitize($field_desc[
'extra'], 0, 0, 1);
1230 $sql .=
" ".$this->sanitize($field_position, 0, 0, 1);
1233 if (!$this ->
query($sql)) {
1251 $sql =
"ALTER TABLE ".$this->sanitize($table);
1252 $sql .=
" ALTER COLUMN ".$this->sanitize($field_name).
" TYPE ".$this->
sanitize($field_desc[
'type']);
1253 if (isset($field_desc[
'value']) && preg_match(
"/^[^\s]/i", $field_desc[
'value'])) {
1254 if (!in_array($field_desc[
'type'], array(
'smallint',
'int',
'date',
'datetime')) && $field_desc[
'value']) {
1255 $sql .=
"(".$this->sanitize($field_desc[
'value']).
")";
1259 if (isset($field_desc[
'value']) && ($field_desc[
'null'] ==
'not null' || $field_desc[
'null'] ==
'NOT NULL')) {
1261 if ($field_desc[
'type'] ==
'varchar' || $field_desc[
'type'] ==
'text') {
1262 $sqlbis =
"UPDATE ".$this->sanitize($table).
" SET ".$this->
escape($field_name).
" = '".$this->
escape(isset($field_desc[
'default']) ? $field_desc[
'default'] :
'').
"' WHERE ".$this->
escape($field_name).
" IS NULL";
1263 $this->
query($sqlbis);
1264 } elseif (in_array($field_desc[
'type'], array(
'tinyint',
'smallint',
'int',
'double'))) {
1265 $sqlbis =
"UPDATE ".$this->sanitize($table).
" SET ".$this->
escape($field_name).
" = ".((float) $this->
escape(isset($field_desc[
'default']) ? $field_desc[
'default'] : 0)).
" WHERE ".$this->
escape($field_name).
" IS NULL";
1266 $this->
query($sqlbis);
1270 if (isset($field_desc[
'default']) && $field_desc[
'default'] !=
'') {
1271 if (in_array($field_desc[
'type'], array(
'tinyint',
'smallint',
'int',
'double'))) {
1272 $sql .=
", ALTER COLUMN ".$this->sanitize($field_name).
" SET DEFAULT ".((float) $field_desc[
'default']);
1273 } elseif ($field_desc[
'type'] !=
'text') {
1274 $sql .=
", ALTER COLUMN ".$this->sanitize($field_name).
" SET DEFAULT '".$this->
escape($field_desc[
'default']).
"'";
1279 if (!$this->
query($sql)) {
1296 $tmp_field_name = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $field_name);
1298 $sql =
"ALTER TABLE ".$this->sanitize($table).
" DROP COLUMN ".$this->
sanitize($tmp_field_name);
1299 if (!$this->
query($sql)) {
1316 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1320 $sql =
"CREATE USER ".$this->sanitize($dolibarr_main_db_user).
" with password '".$this->
escape($dolibarr_main_db_pass).
"'";
1322 dol_syslog(get_class($this).
"::DDLCreateUser", LOG_DEBUG);
1323 $resql = $this->
query($sql);
1338 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1341 return $liste[
'server_encoding'];
1354 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1359 $liste[$i][
'charset'] = $obj->server_encoding;
1360 $liste[$i][
'description'] =
'Default database charset';
1363 $this->
free($resql);
1377 $resql = $this->
query(
'SHOW LC_COLLATE');
1380 return $liste[
'lc_collate'];
1393 $resql = $this->
query(
'SHOW LC_COLLATE');
1398 $liste[$i][
'collation'] = $obj->lc_collate;
1401 $this->
free($resql);
1415 $fullpathofdump =
'/pathtopgdump/pg_dump';
1417 if (file_exists(
'/usr/bin/pg_dump')) {
1418 $fullpathofdump =
'/usr/bin/pg_dump';
1421 $resql = $this->
query(
'SHOW data_directory');
1424 $basedir = $liste[
'data_directory'];
1425 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/pg_dump';
1429 return $fullpathofdump;
1442 $fullpathofdump =
'/pathtopgrestore/'.$tool;
1444 if (file_exists(
'/usr/bin/'.$tool)) {
1445 $fullpathofdump =
'/usr/bin/'.$tool;
1448 $resql = $this->
query(
'SHOW data_directory');
1451 $basedir = $liste[
'data_directory'];
1452 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/'.$tool;
1456 return $fullpathofdump;
1469 $resql =
'select name,setting from pg_settings';
1471 $resql .=
" WHERE name = '".$this->escape($filter).
"'";
1473 $resql = $this->
query($resql);
1476 $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()
sanitize($stringtosanitize, $allowsimplequote=0, $allowsequals=0, $allowsspace=0, $allowschars=1)
Sanitize a string for SQL forging.
Class to drive a PostgreSQL database for Dolibarr.
errno()
Renvoie le code erreur generique de l'operation precedente.
DDLListTablesFull($database, $table='')
List tables into a database.
DDLGetConnectId()
Return connection 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.
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 PostgreSQL does not have an equivalent for mysql_select_db Only compare if the chos...
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.
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Convert request to PostgreSQL syntax, execute it and return the resultset.
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
close()
Close database connection.
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.
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)
Connection 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.
regexpsql($subject, $pattern, $sqlstring=0)
Format a SQL REGEXP.
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 connection.
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.
$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.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.