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';");
463 $resql = $this->
query(
'SHOW server_version');
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') {
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);
732 return str_replace(array(
'\\',
'_',
'%'), array(
'\\\\',
'\_',
'\%'), (
string) $stringtoencode);
743 public function ifsql($test, $resok, $resko)
745 return '(CASE WHEN '.$test.
' THEN '.$resok.
' ELSE '.$resko.
' END)';
756 public function regexpsql($subject, $pattern, $sqlstring =
false)
759 return "(". $subject .
" ~ '" . $pattern .
"')";
762 return "('". $subject .
"' ~ '" . $pattern .
"')";
773 if (!$this->connected) {
775 return 'DB_ERROR_FAILED_TO_CONNECT';
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',
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'
808 $errorlabel = pg_last_error($this->db);
811 if (preg_match(
'/: *([0-9P]+):/', $errorlabel, $reg)) {
812 $errorcode = $reg[1];
813 if (isset($errorcode_map[$errorcode])) {
814 return $errorcode_map[$errorcode];
817 $errno = $errorcode ? $errorcode : $errorlabel;
818 return ($errno ?
'DB_ERROR_'.$errno :
'0');
837 return pg_last_error($this->db);
852 $result = pg_query($this->db,
"SELECT currval('".$tab.
"_".$fieldid.
"_seq')");
854 print pg_last_error($this->db);
858 $row = pg_fetch_result($result, 0, 0);
870 public function encrypt($fieldorvalue, $withQuotes = 1)
880 $return = $fieldorvalue;
881 return ($withQuotes ?
"'" :
"").$this->escape($return).($withQuotes ?
"'" :
"");
932 public function DDLCreateDb($database, $charset =
'', $collation =
'', $owner =
'')
935 if (empty($charset)) {
938 if (empty($collation)) {
946 $sql =
"CREATE DATABASE ".$this->escape($database).
" OWNER '".$this->
escape($owner).
"' ENCODING '".$this->
escape($charset).
"'";
963 $listtables = array();
967 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_%]/i',
'', $table);
969 $escapedlike =
" AND table_name LIKE '".$this->escape($tmptable).
"'";
971 $result = pg_query($this->db,
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike.
" ORDER BY table_name");
973 while ($row = $this->
fetch_row($result)) {
974 $listtables[] = $row[0];
991 $listtables = array();
995 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_%]/i',
'', $table);
997 $escapedlike =
" AND table_name LIKE '".$this->escape($tmptable).
"'";
999 $result = pg_query($this->db,
"SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike.
" ORDER BY table_name");
1001 while ($row = $this->
fetch_row($result)) {
1002 $listtables[] = $row;
1019 $infotables = array();
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;";
1040 while ($row = $this->
fetch_row($result)) {
1041 $infotables[] = $row;
1061 public function DDLCreateTable($table, $fields, $primary_key,
$type, $unique_keys =
null, $fulltext_keys =
null, $keys =
null)
1068 $sql =
"create table ".$table.
"(";
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'];
1081 $sqlfields[$i] .=
" default '".$this->escape($field_desc[
'default']).
"'";
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'];
1090 if ($primary_key !=
"") {
1091 $pk =
"primary key(".$primary_key.
")";
1094 if (is_array($unique_keys)) {
1096 foreach ($unique_keys as $key => $value) {
1097 $sqluq[$i] =
"UNIQUE KEY '".$key.
"' ('".$this->
escape($value).
"')";
1101 if (is_array($keys)) {
1103 foreach ($keys as $key => $value) {
1104 $sqlk[$i] =
"KEY ".$key.
" (".$value.
")";
1108 $sql .= implode(
',', $sqlfields);
1109 if ($primary_key !=
"") {
1112 if (is_array($unique_keys)) {
1113 $sql .=
",".implode(
',', $sqluq);
1115 if (is_array($keys)) {
1116 $sql .=
",".implode(
',', $sqlk);
1118 $sql .=
") type=".$type;
1138 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $table);
1140 $sql =
"DROP TABLE ".$tmptable;
1159 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1163 $sql =
"CREATE USER ".$this->escape($dolibarr_main_db_user).
" with password '".$this->
escape($dolibarr_main_db_pass).
"'";
1165 dol_syslog(get_class($this).
"::DDLCreateUser", LOG_DEBUG);
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')";
1188 $sql .=
" AND attname = '".$this->escape($field).
"'";
1193 return $this->_results;
1206 public function DDLAddField($table, $field_name, $field_desc, $field_position =
"")
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'].
")";
1218 if (preg_match(
"/^[^\s]/i", $field_desc[
'attribute'])) {
1219 $sql .=
" ".$field_desc[
'attribute'];
1221 if (preg_match(
"/^[^\s]/i", $field_desc[
'null'])) {
1222 $sql .=
" ".$field_desc[
'null'];
1224 if (preg_match(
"/^[^\s]/i", $field_desc[
'default'])) {
1225 if (preg_match(
"/null/i", $field_desc[
'default'])) {
1226 $sql .=
" default ".$field_desc[
'default'];
1228 $sql .=
" default '".$this->escape($field_desc[
'default']).
"'";
1231 if (preg_match(
"/^[^\s]/i", $field_desc[
'extra'])) {
1232 $sql .=
" ".$field_desc[
'extra'];
1234 $sql .=
" ".$field_position;
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'].
")";
1263 if ($field_desc[
'null'] ==
'not null' || $field_desc[
'null'] ==
'NOT 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);
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']).
"'";
1300 $tmp_field_name = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $field_name);
1302 $sql =
"ALTER TABLE ".$table.
" DROP COLUMN ".$tmp_field_name;
1317 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1320 return $liste[
'server_encoding'];
1333 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1338 $liste[$i][
'charset'] = $obj->server_encoding;
1339 $liste[$i][
'description'] =
'Default database charset';
1342 $this->
free($resql);
1356 $resql = $this->
query(
'SHOW LC_COLLATE');
1359 return $liste[
'lc_collate'];
1372 $resql = $this->
query(
'SHOW LC_COLLATE');
1377 $liste[$i][
'collation'] = $obj->lc_collate;
1380 $this->
free($resql);
1394 $fullpathofdump =
'/pathtopgdump/pg_dump';
1396 if (file_exists(
'/usr/bin/pg_dump')) {
1397 $fullpathofdump =
'/usr/bin/pg_dump';
1400 $resql = $this->
query(
'SHOW data_directory');
1403 $basedir = $liste[
'data_directory'];
1404 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/pg_dump';
1408 return $fullpathofdump;
1421 $fullpathofdump =
'/pathtopgrestore/'.$tool;
1423 if (file_exists(
'/usr/bin/'.$tool)) {
1424 $fullpathofdump =
'/usr/bin/'.$tool;
1427 $resql = $this->
query(
'SHOW data_directory');
1430 $basedir = $liste[
'data_directory'];
1431 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/'.$tool;
1435 return $fullpathofdump;
1448 $resql =
'select name,setting from pg_settings';
1450 $resql .=
" WHERE name = '".$this->escape($filter).
"'";
1452 $resql = $this->
query($resql);
1455 $result[$obj->name] = $obj->setting;