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') {
562 if ($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);
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 $infotables = array();
994 $sql .=
" infcol.column_name as \"Column\",";
995 $sql .=
" CASE WHEN infcol.character_maximum_length IS NOT NULL THEN infcol.udt_name || '('||infcol.character_maximum_length||')'";
996 $sql .=
" ELSE infcol.udt_name";
997 $sql .=
" END as \"Type\",";
998 $sql .=
" infcol.collation_name as \"Collation\",";
999 $sql .=
" infcol.is_nullable as \"Null\",";
1000 $sql .=
" '' as \"Key\",";
1001 $sql .=
" infcol.column_default as \"Default\",";
1002 $sql .=
" '' as \"Extra\",";
1003 $sql .=
" '' as \"Privileges\"";
1004 $sql .=
" FROM information_schema.columns infcol";
1005 $sql .=
" WHERE table_schema = 'public' ";
1006 $sql .=
" AND table_name = '".$this->escape($table).
"'";
1007 $sql .=
" ORDER BY ordinal_position;";
1012 while ($row = $this->
fetch_row($result)) {
1013 $infotables[] = $row;
1033 public function DDLCreateTable($table, $fields, $primary_key,
$type, $unique_keys =
null, $fulltext_keys =
null, $keys =
null)
1040 $sql =
"create table ".$table.
"(";
1042 foreach ($fields as $field_name => $field_desc) {
1043 $sqlfields[$i] = $field_name.
" ";
1044 $sqlfields[$i] .= $field_desc[
'type'];
1045 if (preg_match(
"/^[^\s]/i", $field_desc[
'value'])) {
1046 $sqlfields[$i] .=
"(".$field_desc[
'value'].
")";
1047 } elseif (preg_match(
"/^[^\s]/i", $field_desc[
'attribute'])) {
1048 $sqlfields[$i] .=
" ".$field_desc[
'attribute'];
1049 } elseif (preg_match(
"/^[^\s]/i", $field_desc[
'default'])) {
1050 if (preg_match(
"/null/i", $field_desc[
'default'])) {
1051 $sqlfields[$i] .=
" default ".$field_desc[
'default'];
1053 $sqlfields[$i] .=
" default '".$this->escape($field_desc[
'default']).
"'";
1055 } elseif (preg_match(
"/^[^\s]/i", $field_desc[
'null'])) {
1056 $sqlfields[$i] .=
" ".$field_desc[
'null'];
1057 } elseif (preg_match(
"/^[^\s]/i", $field_desc[
'extra'])) {
1058 $sqlfields[$i] .=
" ".$field_desc[
'extra'];
1062 if ($primary_key !=
"") {
1063 $pk =
"primary key(".$primary_key.
")";
1066 if (is_array($unique_keys)) {
1068 foreach ($unique_keys as $key => $value) {
1069 $sqluq[$i] =
"UNIQUE KEY '".$key.
"' ('".$this->
escape($value).
"')";
1073 if (is_array($keys)) {
1075 foreach ($keys as $key => $value) {
1076 $sqlk[$i] =
"KEY ".$key.
" (".$value.
")";
1080 $sql .= implode(
',', $sqlfields);
1081 if ($primary_key !=
"") {
1084 if (is_array($unique_keys)) {
1085 $sql .=
",".implode(
',', $sqluq);
1087 if (is_array($keys)) {
1088 $sql .=
",".implode(
',', $sqlk);
1090 $sql .=
") type=".$type;
1110 $tmptable = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $table);
1112 $sql =
"DROP TABLE ".$tmptable;
1131 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1135 $sql =
"CREATE USER ".$this->escape($dolibarr_main_db_user).
" with password '".$this->
escape($dolibarr_main_db_pass).
"'";
1137 dol_syslog(get_class($this).
"::DDLCreateUser", LOG_DEBUG);
1157 $sql =
"SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$this->escape($table).
"' AND attrelid = typrelid";
1158 $sql .=
" AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')";
1160 $sql .=
" AND attname = '".$this->escape($field).
"'";
1165 return $this->_results;
1178 public function DDLAddField($table, $field_name, $field_desc, $field_position =
"")
1183 $sql =
"ALTER TABLE ".$table.
" ADD ".$field_name.
" ";
1184 $sql .= $field_desc[
'type'];
1185 if (preg_match(
"/^[^\s]/i", $field_desc[
'value'])) {
1186 if (!in_array($field_desc[
'type'], array(
'int',
'date',
'datetime')) && $field_desc[
'value']) {
1187 $sql .=
"(".$field_desc[
'value'].
")";
1190 if (preg_match(
"/^[^\s]/i", $field_desc[
'attribute'])) {
1191 $sql .=
" ".$field_desc[
'attribute'];
1193 if (preg_match(
"/^[^\s]/i", $field_desc[
'null'])) {
1194 $sql .=
" ".$field_desc[
'null'];
1196 if (preg_match(
"/^[^\s]/i", $field_desc[
'default'])) {
1197 if (preg_match(
"/null/i", $field_desc[
'default'])) {
1198 $sql .=
" default ".$field_desc[
'default'];
1200 $sql .=
" default '".$this->escape($field_desc[
'default']).
"'";
1203 if (preg_match(
"/^[^\s]/i", $field_desc[
'extra'])) {
1204 $sql .=
" ".$field_desc[
'extra'];
1206 $sql .=
" ".$field_position;
1227 $sql =
"ALTER TABLE ".$table;
1228 $sql .=
" MODIFY COLUMN ".$field_name.
" ".$field_desc[
'type'];
1229 if (in_array($field_desc[
'type'], array(
'double',
'varchar')) && $field_desc[
'value']) {
1230 $sql .=
"(".$field_desc[
'value'].
")";
1233 if ($field_desc[
'null'] ==
'not null' || $field_desc[
'null'] ==
'NOT NULL') {
1235 if ($field_desc[
'type'] ==
'varchar' || $field_desc[
'type'] ==
'text') {
1236 $sqlbis =
"UPDATE ".$table.
" SET ".$field_name.
" = '".$this->
escape($field_desc[
'default'] ? $field_desc[
'default'] :
'').
"' WHERE ".$field_name.
" IS NULL";
1237 $this->
query($sqlbis);
1238 } elseif ($field_desc[
'type'] ==
'tinyint' || $field_desc[
'type'] ==
'int') {
1239 $sqlbis =
"UPDATE ".$table.
" SET ".$field_name.
" = ".((int) $this->
escape($field_desc[
'default'] ? $field_desc[
'default'] : 0)).
" WHERE ".$field_name.
" IS NULL";
1240 $this->query($sqlbis);
1244 if ($field_desc[
'default'] !=
'') {
1245 if ($field_desc[
'type'] ==
'double' || $field_desc[
'type'] ==
'tinyint' || $field_desc[
'type'] ==
'int') {
1246 $sql .=
" DEFAULT ".$this->escape($field_desc[
'default']);
1247 } elseif ($field_desc[
'type'] !=
'text') {
1248 $sql .=
" DEFAULT '".$this->escape($field_desc[
'default']).
"'";
1270 $tmp_field_name = preg_replace(
'/[^a-z0-9\.\-\_]/i',
'', $field_name);
1272 $sql =
"ALTER TABLE ".$table.
" DROP COLUMN ".$tmp_field_name;
1287 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1290 return $liste[
'server_encoding'];
1303 $resql = $this->
query(
'SHOW SERVER_ENCODING');
1308 $liste[$i][
'charset'] = $obj->server_encoding;
1309 $liste[$i][
'description'] =
'Default database charset';
1312 $this->
free($resql);
1326 $resql = $this->
query(
'SHOW LC_COLLATE');
1329 return $liste[
'lc_collate'];
1342 $resql = $this->
query(
'SHOW LC_COLLATE');
1347 $liste[$i][
'collation'] = $obj->lc_collate;
1350 $this->
free($resql);
1364 $fullpathofdump =
'/pathtopgdump/pg_dump';
1366 if (file_exists(
'/usr/bin/pg_dump')) {
1367 $fullpathofdump =
'/usr/bin/pg_dump';
1370 $resql = $this->
query(
'SHOW data_directory');
1373 $basedir = $liste[
'data_directory'];
1374 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/pg_dump';
1378 return $fullpathofdump;
1391 $fullpathofdump =
'/pathtopgrestore/'.$tool;
1393 if (file_exists(
'/usr/bin/'.$tool)) {
1394 $fullpathofdump =
'/usr/bin/'.$tool;
1397 $resql = $this->
query(
'SHOW data_directory');
1400 $basedir = $liste[
'data_directory'];
1401 $fullpathofdump = preg_replace(
'/data$/',
'bin', $basedir).
'/'.$tool;
1405 return $fullpathofdump;
1418 $resql =
'select name,setting from pg_settings';
1420 $resql .=
" WHERE name = '".$this->escape($filter).
"'";
1422 $resql = $this->
query($resql);
1425 $result[$obj->name] = $obj->setting;