alxarafe /
alixar
| Total Complexity | 219 |
| Total Lines | 1484 |
| Duplicated Lines | 0 % |
| Changes | 0 | ||
Complex classes like DoliDBPgsql often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use DoliDBPgsql, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 41 | class DoliDBPgsql extends DoliDB |
||
| 42 | { |
||
| 43 | //! Database type |
||
| 44 | public $type = 'pgsql'; // Name of manager |
||
| 45 | |||
| 46 | //! Database label |
||
| 47 | const LABEL = 'PostgreSQL'; // Label of manager |
||
| 48 | |||
| 49 | //! Charset |
||
| 50 | public $forcecharset = 'UTF8'; // Can't be static as it may be forced with a dynamic value |
||
| 51 | |||
| 52 | //! Collate used to force collate when creating database |
||
| 53 | public $forcecollate = ''; // Can't be static as it may be forced with a dynamic value |
||
| 54 | |||
| 55 | //! Version min database |
||
| 56 | const VERSIONMIN = '9.0.0'; // Version min database |
||
| 57 | |||
| 58 | /** |
||
| 59 | * @var boolean $unescapeslashquot Set this to 1 when calling SQL queries, to say that SQL is not standard but already escaped for Mysql. Used by PostgreSQL driver |
||
| 60 | */ |
||
| 61 | public $unescapeslashquot = false; |
||
| 62 | /** |
||
| 63 | * @var boolean $standard_conforming_string Set this to true if postgres accept only standard encoding of string using '' and not \' |
||
| 64 | */ |
||
| 65 | public $standard_conforming_strings = false; |
||
| 66 | |||
| 67 | |||
| 68 | /** @var resource|boolean Resultset of last query */ |
||
| 69 | private $_results; |
||
| 70 | |||
| 71 | |||
| 72 | /** |
||
| 73 | * Constructor. |
||
| 74 | * This create an opened connection to a database server and eventually to a database |
||
| 75 | * |
||
| 76 | * @param string $type Type of database (mysql, pgsql...). Not used. |
||
| 77 | * @param string $host Address of database server |
||
| 78 | * @param string $user Nom de l'utilisateur autorise |
||
| 79 | * @param string $pass Password |
||
| 80 | * @param string $name Nom de la database |
||
| 81 | * @param int $port Port of database server |
||
| 82 | */ |
||
| 83 | public function __construct($type, $host, $user, $pass, $name = '', $port = 0) |
||
| 84 | { |
||
| 85 | global $conf, $langs; |
||
| 86 | |||
| 87 | // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static |
||
| 88 | if (!empty($conf->db->character_set)) { |
||
| 89 | $this->forcecharset = $conf->db->character_set; |
||
| 90 | } |
||
| 91 | if (!empty($conf->db->dolibarr_main_db_collation)) { |
||
| 92 | $this->forcecollate = $conf->db->dolibarr_main_db_collation; |
||
| 93 | } |
||
| 94 | |||
| 95 | $this->database_user = $user; |
||
| 96 | $this->database_host = $host; |
||
| 97 | $this->database_port = $port; |
||
| 98 | |||
| 99 | $this->transaction_opened = 0; |
||
| 100 | |||
| 101 | //print "Name DB: $host,$user,$pass,$name<br>"; |
||
| 102 | |||
| 103 | if (!function_exists("pg_connect")) { |
||
| 104 | $this->connected = false; |
||
| 105 | $this->ok = false; |
||
| 106 | $this->error = "Pgsql PHP functions are not available in this version of PHP"; |
||
| 107 | dol_syslog(get_only_class($this) . "::DoliDBPgsql : Pgsql PHP functions are not available in this version of PHP", LOG_ERR); |
||
| 108 | return; |
||
| 109 | } |
||
| 110 | |||
| 111 | if (!$host) { |
||
| 112 | $this->connected = false; |
||
| 113 | $this->ok = false; |
||
| 114 | $this->error = $langs->trans("ErrorWrongHostParameter"); |
||
| 115 | dol_syslog(get_only_class($this) . "::DoliDBPgsql : Erreur Connect, wrong host parameters", LOG_ERR); |
||
| 116 | return; |
||
| 117 | } |
||
| 118 | |||
| 119 | // Essai connection serveur |
||
| 120 | //print "$host, $user, $pass, $name, $port"; |
||
| 121 | $this->db = $this->connect($host, $user, $pass, $name, $port); |
||
| 122 | |||
| 123 | if ($this->db) { |
||
| 124 | $this->connected = true; |
||
| 125 | $this->ok = true; |
||
| 126 | } else { |
||
| 127 | // host, login ou password incorrect |
||
| 128 | $this->connected = false; |
||
| 129 | $this->ok = false; |
||
| 130 | $this->error = 'Host, login or password incorrect'; |
||
| 131 | dol_syslog(get_only_class($this) . "::DoliDBPgsql : Erreur Connect " . $this->error . '. Failed to connect to host=' . $host . ' port=' . $port . ' user=' . $user, LOG_ERR); |
||
| 132 | } |
||
| 133 | |||
| 134 | // If server connection serveur ok and DB connection is requested, try to connect to DB |
||
| 135 | if ($this->connected && $name) { |
||
| 136 | if ($this->select_db($name)) { |
||
| 137 | $this->database_selected = true; |
||
| 138 | $this->database_name = $name; |
||
| 139 | $this->ok = true; |
||
| 140 | } else { |
||
| 141 | $this->database_selected = false; |
||
| 142 | $this->database_name = ''; |
||
| 143 | $this->ok = false; |
||
| 144 | $this->error = $this->error(); |
||
| 145 | dol_syslog(get_only_class($this) . "::DoliDBPgsql : Erreur Select_db " . $this->error, LOG_ERR); |
||
| 146 | } |
||
| 147 | } else { |
||
| 148 | // Pas de selection de base demandee, ok ou ko |
||
| 149 | $this->database_selected = false; |
||
| 150 | } |
||
| 151 | } |
||
| 152 | |||
| 153 | |||
| 154 | /** |
||
| 155 | * Convert a SQL request in Mysql syntax to native syntax |
||
| 156 | * |
||
| 157 | * @param string $line SQL request line to convert |
||
| 158 | * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) |
||
| 159 | * @param bool $unescapeslashquot Unescape "slash quote" with "quote quote" |
||
| 160 | * @return string SQL request line converted |
||
| 161 | */ |
||
| 162 | public function convertSQLFromMysql($line, $type = 'auto', $unescapeslashquot = false) |
||
| 163 | { |
||
| 164 | global $conf; |
||
| 165 | |||
| 166 | // Removed empty line if this is a comment line for SVN tagging |
||
| 167 | if (preg_match('/^--\s\$Id/i', $line)) { |
||
| 168 | return ''; |
||
| 169 | } |
||
| 170 | // Return line if this is a comment |
||
| 171 | if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) { |
||
| 172 | return $line; |
||
| 173 | } |
||
| 174 | if ($line != "") { |
||
| 175 | // group_concat support (PgSQL >= 9.0) |
||
| 176 | // Replace group_concat(x) or group_concat(x SEPARATOR ',') with string_agg(x, ',') |
||
| 177 | $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line); |
||
| 178 | $line = preg_replace('/ SEPARATOR/i', ',', $line); |
||
| 179 | $line = preg_replace('/STRING_AGG\(([^,\)]+)\)/i', 'STRING_AGG(\\1, \',\')', $line); |
||
| 180 | //print $line."\n"; |
||
| 181 | |||
| 182 | if ($type == 'auto') { |
||
| 183 | if (preg_match('/ALTER TABLE/i', $line)) { |
||
| 184 | $type = 'dml'; |
||
| 185 | } elseif (preg_match('/CREATE TABLE/i', $line)) { |
||
| 186 | $type = 'dml'; |
||
| 187 | } elseif (preg_match('/DROP TABLE/i', $line)) { |
||
| 188 | $type = 'dml'; |
||
| 189 | } |
||
| 190 | } |
||
| 191 | |||
| 192 | $line = preg_replace('/ as signed\)/i', ' as integer)', $line); |
||
| 193 | |||
| 194 | if ($type == 'dml') { |
||
| 195 | $reg = array(); |
||
| 196 | |||
| 197 | $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space |
||
| 198 | |||
| 199 | // we are inside create table statement so lets process datatypes |
||
| 200 | if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence |
||
| 201 | $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); |
||
| 202 | $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); |
||
| 203 | $line = preg_replace('/,$/', '', $line); |
||
| 204 | } |
||
| 205 | |||
| 206 | // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..." |
||
| 207 | if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) { |
||
| 208 | $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 SERIAL PRIMARY KEY', $line); |
||
| 209 | //$line = "-- ".$line." replaced by --\n".$newline; |
||
| 210 | $line = $newline; |
||
| 211 | } |
||
| 212 | |||
| 213 | if (preg_match('/[\s\t\(]*(\w*)[\s\t]+bigint.*auto_increment/i', $line, $reg)) { |
||
| 214 | $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+bigint.*auto_increment[^,]*/i', '\\1 \\2 BIGSERIAL PRIMARY KEY', $line); |
||
| 215 | //$line = "-- ".$line." replaced by --\n".$newline; |
||
| 216 | $line = $newline; |
||
| 217 | } |
||
| 218 | |||
| 219 | // tinyint type conversion |
||
| 220 | $line = preg_replace('/tinyint\(?[0-9]*\)?/', 'smallint', $line); |
||
| 221 | $line = preg_replace('/tinyint/i', 'smallint', $line); |
||
| 222 | |||
| 223 | // nuke unsigned |
||
| 224 | $line = preg_replace('/(int\w+|smallint|bigint)\s+unsigned/i', '\\1', $line); |
||
| 225 | |||
| 226 | // blob -> text |
||
| 227 | $line = preg_replace('/\w*blob/i', 'text', $line); |
||
| 228 | |||
| 229 | // tinytext/mediumtext -> text |
||
| 230 | $line = preg_replace('/tinytext/i', 'text', $line); |
||
| 231 | $line = preg_replace('/mediumtext/i', 'text', $line); |
||
| 232 | $line = preg_replace('/longtext/i', 'text', $line); |
||
| 233 | |||
| 234 | $line = preg_replace('/text\([0-9]+\)/i', 'text', $line); |
||
| 235 | |||
| 236 | // change not null datetime field to null valid ones |
||
| 237 | // (to support remapping of "zero time" to null |
||
| 238 | $line = preg_replace('/datetime not null/i', 'datetime', $line); |
||
| 239 | $line = preg_replace('/datetime/i', 'timestamp', $line); |
||
| 240 | |||
| 241 | // double -> numeric |
||
| 242 | $line = preg_replace('/^double/i', 'numeric', $line); |
||
| 243 | $line = preg_replace('/(\s*)double/i', '\\1numeric', $line); |
||
| 244 | // float -> numeric |
||
| 245 | $line = preg_replace('/^float/i', 'numeric', $line); |
||
| 246 | $line = preg_replace('/(\s*)float/i', '\\1numeric', $line); |
||
| 247 | |||
| 248 | //Check tms timestamp field case (in Mysql this field is defaulted to now and |
||
| 249 | // on update defaulted by now |
||
| 250 | $line = preg_replace('/(\s*)tms(\s*)timestamp/i', '\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line); |
||
| 251 | |||
| 252 | // nuke DEFAULT CURRENT_TIMESTAMP |
||
| 253 | $line = preg_replace('/(\s*)DEFAULT(\s*)CURRENT_TIMESTAMP/i', '\\1', $line); |
||
| 254 | |||
| 255 | // nuke ON UPDATE CURRENT_TIMESTAMP |
||
| 256 | $line = preg_replace('/(\s*)ON(\s*)UPDATE(\s*)CURRENT_TIMESTAMP/i', '\\1', $line); |
||
| 257 | |||
| 258 | // unique index(field1,field2) |
||
| 259 | if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) { |
||
| 260 | $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line); |
||
| 261 | } |
||
| 262 | |||
| 263 | // We remove end of requests "AFTER fieldxxx" |
||
| 264 | $line = preg_replace('/\sAFTER [a-z0-9_]+/i', '', $line); |
||
| 265 | |||
| 266 | // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX |
||
| 267 | $line = preg_replace('/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i', 'DROP INDEX', $line); |
||
| 268 | |||
| 269 | // Translate order to rename fields |
||
| 270 | if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) { |
||
| 271 | $line = "-- " . $line . " replaced by --\n"; |
||
| 272 | $line .= "ALTER TABLE " . $reg[1] . " RENAME COLUMN " . $reg[2] . " TO " . $reg[3]; |
||
| 273 | } |
||
| 274 | |||
| 275 | // Translate order to modify field format |
||
| 276 | if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) { |
||
| 277 | $line = "-- " . $line . " replaced by --\n"; |
||
| 278 | $newreg3 = $reg[3]; |
||
| 279 | $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3); |
||
| 280 | $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3); |
||
| 281 | $newreg3 = preg_replace('/ NULL/i', '', $newreg3); |
||
| 282 | $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3); |
||
| 283 | $newreg3 = preg_replace('/ DEFAULT \'?[0-9a-zA-Z_@]*\'?/i', '', $newreg3); |
||
| 284 | $line .= "ALTER TABLE " . $reg[1] . " ALTER COLUMN " . $reg[2] . " TYPE " . $newreg3; |
||
| 285 | // TODO Add alter to set default value or null/not null if there is this in $reg[3] |
||
| 286 | } |
||
| 287 | |||
| 288 | // alter table add primary key (field1, field2 ...) -> We remove the primary key name not accepted by PostGreSQL |
||
| 289 | // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity) |
||
| 290 | if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) { |
||
| 291 | $line = "-- " . $line . " replaced by --\n"; |
||
| 292 | $line .= "ALTER TABLE " . $reg[1] . " ADD PRIMARY KEY (" . $reg[3]; |
||
| 293 | } |
||
| 294 | |||
| 295 | // Translate order to drop primary keys |
||
| 296 | // ALTER TABLE llx_dolibarr_modules DROP PRIMARY KEY pk_xxx |
||
| 297 | if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+PRIMARY\s+KEY\s*([^;]+)$/i', $line, $reg)) { |
||
| 298 | $line = "-- " . $line . " replaced by --\n"; |
||
| 299 | $line .= "ALTER TABLE " . $reg[1] . " DROP CONSTRAINT " . $reg[2]; |
||
| 300 | } |
||
| 301 | |||
| 302 | // Translate order to drop foreign keys |
||
| 303 | // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx |
||
| 304 | if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) { |
||
| 305 | $line = "-- " . $line . " replaced by --\n"; |
||
| 306 | $line .= "ALTER TABLE " . $reg[1] . " DROP CONSTRAINT " . $reg[2]; |
||
| 307 | } |
||
| 308 | |||
| 309 | // Translate order to add foreign keys |
||
| 310 | // ALTER TABLE llx_tablechild ADD CONSTRAINT fk_tablechild_fk_fieldparent FOREIGN KEY (fk_fieldparent) REFERENCES llx_tableparent (rowid) |
||
| 311 | if (preg_match('/ALTER\s+TABLE\s+(.*)\s*ADD CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) { |
||
| 312 | $line = preg_replace('/;$/', '', $line); |
||
| 313 | $line .= " DEFERRABLE INITIALLY IMMEDIATE;"; |
||
| 314 | } |
||
| 315 | |||
| 316 | // alter table add [unique] [index] (field1, field2 ...) |
||
| 317 | // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version) |
||
| 318 | if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) { |
||
| 319 | $fieldlist = $reg[4]; |
||
| 320 | $idxname = $reg[3]; |
||
| 321 | $tablename = $reg[1]; |
||
| 322 | $line = "-- " . $line . " replaced by --\n"; |
||
| 323 | $line .= "CREATE " . (preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '') . "INDEX " . $idxname . " ON " . $tablename . " (" . $fieldlist . ")"; |
||
| 324 | } |
||
| 325 | } |
||
| 326 | |||
| 327 | // To have PostgreSQL case sensitive |
||
| 328 | $count_like = 0; |
||
| 329 | $line = str_replace(' LIKE \'', ' ILIKE \'', $line, $count_like); |
||
| 330 | if (getDolGlobalString('PSQL_USE_UNACCENT') && $count_like > 0) { |
||
| 331 | // @see https://docs.PostgreSQL.fr/11/unaccent.html : 'unaccent()' function must be installed before |
||
| 332 | $line = preg_replace('/\s+(\(+\s*)([a-zA-Z0-9\-\_\.]+) ILIKE /', ' \1unaccent(\2) ILIKE ', $line); |
||
| 333 | } |
||
| 334 | |||
| 335 | $line = str_replace(' LIKE BINARY \'', ' LIKE \'', $line); |
||
| 336 | |||
| 337 | // Replace INSERT IGNORE into INSERT |
||
| 338 | $line = preg_replace('/^INSERT IGNORE/', 'INSERT', $line); |
||
| 339 | |||
| 340 | // Delete using criteria on other table must not declare twice the deleted table |
||
| 341 | // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable |
||
| 342 | if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) { |
||
| 343 | if ($reg[1] == $reg[2]) { // If same table, we remove second one |
||
| 344 | $line = preg_replace('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', 'DELETE FROM \\1 USING \\3', $line); |
||
| 345 | } |
||
| 346 | } |
||
| 347 | |||
| 348 | // Remove () in the tables in FROM if 1 table |
||
| 349 | $line = preg_replace('/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i', 'FROM \\1', $line); |
||
| 350 | //print $line."\n"; |
||
| 351 | |||
| 352 | // Remove () in the tables in FROM if 2 table |
||
| 353 | $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 | //print $line."\n"; |
||
| 355 | |||
| 356 | // Remove () in the tables in FROM if 3 table |
||
| 357 | $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 | //print $line."\n"; |
||
| 359 | |||
| 360 | // Remove () in the tables in FROM if 4 table |
||
| 361 | $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 | //print $line."\n"; |
||
| 363 | |||
| 364 | // Remove () in the tables in FROM if 5 table |
||
| 365 | $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); |
||
| 366 | //print $line."\n"; |
||
| 367 | |||
| 368 | // Replace spacing ' with ''. |
||
| 369 | // By default we do not (should be already done by db->escape function if required |
||
| 370 | // except for sql insert in data file that are mysql escaped so we removed them to |
||
| 371 | // be compatible with standard_conforming_strings=on that considers \ as ordinary character). |
||
| 372 | if ($unescapeslashquot) { |
||
| 373 | $line = preg_replace("/\\\'/", "''", $line); |
||
| 374 | } |
||
| 375 | |||
| 376 | //print "type=".$type." newline=".$line."<br>\n"; |
||
| 377 | } |
||
| 378 | |||
| 379 | return $line; |
||
| 380 | } |
||
| 381 | |||
| 382 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 383 | |||
| 384 | /** |
||
| 385 | * Select a database |
||
| 386 | * PostgreSQL does not have an equivalent for `mysql_select_db` |
||
| 387 | * Only compare if the chosen DB is the one active on the connection |
||
| 388 | * |
||
| 389 | * @param string $database Name of database |
||
| 390 | * @return bool true if OK, false if KO |
||
| 391 | */ |
||
| 392 | public function select_db($database) |
||
| 393 | { |
||
| 394 | // phpcs:enable |
||
| 395 | if ($database == $this->database_name) { |
||
| 396 | return true; |
||
| 397 | } else { |
||
| 398 | return false; |
||
| 399 | } |
||
| 400 | } |
||
| 401 | |||
| 402 | /** |
||
| 403 | * Connection to server |
||
| 404 | * |
||
| 405 | * @param string $host Database server host |
||
| 406 | * @param string $login Login |
||
| 407 | * @param string $passwd Password |
||
| 408 | * @param string $name Name of database (not used for mysql, used for pgsql) |
||
| 409 | * @param integer $port Port of database server |
||
| 410 | * @return false|resource Database access handler |
||
| 411 | * @see close() |
||
| 412 | */ |
||
| 413 | public function connect($host, $login, $passwd, $name, $port = 0) |
||
| 414 | { |
||
| 415 | // use pg_pconnect() instead of pg_connect() if you want to use persistent connection costing 1ms, instead of 30ms for non persistent |
||
| 416 | |||
| 417 | $this->db = false; |
||
| 418 | |||
| 419 | // connections parameters must be protected (only \ and ' according to pg_connect() manual) |
||
| 420 | $host = str_replace(array("\\", "'"), array("\\\\", "\\'"), $host); |
||
| 421 | $login = str_replace(array("\\", "'"), array("\\\\", "\\'"), $login); |
||
| 422 | $passwd = str_replace(array("\\", "'"), array("\\\\", "\\'"), $passwd); |
||
| 423 | $name = str_replace(array("\\", "'"), array("\\\\", "\\'"), $name); |
||
| 424 | $port = str_replace(array("\\", "'"), array("\\\\", "\\'"), (string)$port); |
||
| 425 | |||
| 426 | if (!$name) { |
||
| 427 | $name = "postgres"; // When try to connect using admin user |
||
| 428 | } |
||
| 429 | |||
| 430 | // try first Unix domain socket (local) |
||
| 431 | if ((!empty($host) && $host == "socket") && !defined('NOLOCALSOCKETPGCONNECT')) { |
||
| 432 | $con_string = "dbname='" . $name . "' user='" . $login . "' password='" . $passwd . "'"; // $name may be empty |
||
| 433 | try { |
||
| 434 | $this->db = @pg_connect($con_string); |
||
| 435 | } catch (Exception $e) { |
||
| 436 | // No message |
||
| 437 | } |
||
| 438 | } |
||
| 439 | |||
| 440 | // if local connection failed or not requested, use TCP/IP |
||
| 441 | if (empty($this->db)) { |
||
| 442 | if (!$host) { |
||
| 443 | $host = "localhost"; |
||
| 444 | } |
||
| 445 | if (!$port) { |
||
| 446 | $port = 5432; |
||
| 447 | } |
||
| 448 | |||
| 449 | $con_string = "host='" . $host . "' port='" . $port . "' dbname='" . $name . "' user='" . $login . "' password='" . $passwd . "'"; |
||
| 450 | try { |
||
| 451 | $this->db = @pg_connect($con_string); |
||
| 452 | } catch (Exception $e) { |
||
| 453 | print $e->getMessage(); |
||
| 454 | } |
||
| 455 | } |
||
| 456 | |||
| 457 | // now we test if at least one connect method was a success |
||
| 458 | if ($this->db) { |
||
| 459 | $this->database_name = $name; |
||
| 460 | pg_set_error_verbosity($this->db, PGSQL_ERRORS_VERBOSE); // Set verbosity to max |
||
| 461 | pg_query($this->db, "set datestyle = 'ISO, YMD';"); |
||
| 462 | } |
||
| 463 | |||
| 464 | return $this->db; |
||
| 465 | } |
||
| 466 | |||
| 467 | /** |
||
| 468 | * Return version of database server |
||
| 469 | * |
||
| 470 | * @return string Version string |
||
| 471 | */ |
||
| 472 | public function getVersion() |
||
| 473 | { |
||
| 474 | $resql = $this->query('SHOW server_version'); |
||
| 475 | if ($resql) { |
||
| 476 | $liste = $this->fetch_array($resql); |
||
| 477 | return $liste['server_version']; |
||
| 478 | } |
||
| 479 | return ''; |
||
| 480 | } |
||
| 481 | |||
| 482 | /** |
||
| 483 | * Return version of database client driver |
||
| 484 | * |
||
| 485 | * @return string Version string |
||
| 486 | */ |
||
| 487 | public function getDriverInfo() |
||
| 488 | { |
||
| 489 | return 'pgsql php driver'; |
||
| 490 | } |
||
| 491 | |||
| 492 | /** |
||
| 493 | * Close database connection |
||
| 494 | * |
||
| 495 | * @return boolean True if disconnect successful, false otherwise |
||
| 496 | * @see connect() |
||
| 497 | */ |
||
| 498 | public function close() |
||
| 499 | { |
||
| 500 | if ($this->db) { |
||
| 501 | if ($this->transaction_opened > 0) { |
||
| 502 | dol_syslog(get_only_class($this) . "::close Closing a connection with an opened transaction depth=" . $this->transaction_opened, LOG_ERR); |
||
| 503 | } |
||
| 504 | $this->connected = false; |
||
| 505 | return pg_close($this->db); |
||
| 506 | } |
||
| 507 | return false; |
||
| 508 | } |
||
| 509 | |||
| 510 | /** |
||
| 511 | * Convert request to PostgreSQL syntax, execute it and return the resultset |
||
| 512 | * |
||
| 513 | * @param string $query SQL query string |
||
| 514 | * @param int $usesavepoint 0=Default mode, 1=Run a savepoint before and a rollback to savepoint if error (this allow to have some request with errors inside global transactions). |
||
| 515 | * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) |
||
| 516 | * @param int $result_mode Result mode (not used with pgsql) |
||
| 517 | * @return bool|resource Resultset of answer |
||
| 518 | */ |
||
| 519 | public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0) |
||
| 520 | { |
||
| 521 | global $dolibarr_main_db_readonly; |
||
| 522 | |||
| 523 | $query = trim($query); |
||
| 524 | |||
| 525 | // Convert MySQL syntax to PostgreSQL syntax |
||
| 526 | $query = $this->convertSQLFromMysql($query, $type, ($this->unescapeslashquot && $this->standard_conforming_strings)); |
||
| 527 | //print "After convertSQLFromMysql:\n".$query."<br>\n"; |
||
| 528 | |||
| 529 | if (getDolGlobalString('MAIN_DB_AUTOFIX_BAD_SQL_REQUEST')) { |
||
| 530 | // Fix bad formed requests. If request contains a date without quotes, we fix this but this should not occurs. |
||
| 531 | $loop = true; |
||
| 532 | while ($loop) { |
||
| 533 | 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)) { |
||
| 534 | $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); |
||
| 535 | dol_syslog("Warning: Bad formed request converted into " . $query, LOG_WARNING); |
||
| 536 | } else { |
||
| 537 | $loop = false; |
||
| 538 | } |
||
| 539 | } |
||
| 540 | } |
||
| 541 | |||
| 542 | if ($usesavepoint && $this->transaction_opened) { |
||
| 543 | @pg_query($this->db, 'SAVEPOINT mysavepoint'); |
||
|
|
|||
| 544 | } |
||
| 545 | |||
| 546 | if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) { |
||
| 547 | $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks |
||
| 548 | dol_syslog('sql=' . substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG); |
||
| 549 | } |
||
| 550 | if (empty($query)) { |
||
| 551 | return false; // Return false = error if empty request |
||
| 552 | } |
||
| 553 | |||
| 554 | if (!empty($dolibarr_main_db_readonly)) { |
||
| 555 | if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) { |
||
| 556 | $this->lasterror = 'Application in read-only mode'; |
||
| 557 | $this->lasterrno = 'APPREADONLY'; |
||
| 558 | $this->lastquery = $query; |
||
| 559 | return false; |
||
| 560 | } |
||
| 561 | } |
||
| 562 | |||
| 563 | $ret = @pg_query($this->db, $query); |
||
| 564 | |||
| 565 | //print $query; |
||
| 566 | if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) { // Si requete utilisateur, on la sauvegarde ainsi que son resultset |
||
| 567 | if (!$ret) { |
||
| 568 | if ($this->errno() != 'DB_ERROR_25P02') { // Do not overwrite errors if this is a consecutive error |
||
| 569 | $this->lastqueryerror = $query; |
||
| 570 | $this->lasterror = $this->error(); |
||
| 571 | $this->lasterrno = $this->errno(); |
||
| 572 | |||
| 573 | if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) { |
||
| 574 | dol_syslog(get_only_class($this) . "::query SQL Error query: " . $query, LOG_ERR); // Log of request was not yet done previously |
||
| 575 | } |
||
| 576 | dol_syslog(get_only_class($this) . "::query SQL Error message: " . $this->lasterror . " (" . $this->lasterrno . ")", LOG_ERR); |
||
| 577 | dol_syslog(get_only_class($this) . "::query SQL Error usesavepoint = " . $usesavepoint, LOG_ERR); |
||
| 578 | } |
||
| 579 | |||
| 580 | if ($usesavepoint && $this->transaction_opened) { // Warning, after that errno will be erased |
||
| 581 | @pg_query($this->db, 'ROLLBACK TO SAVEPOINT mysavepoint'); |
||
| 582 | } |
||
| 583 | } |
||
| 584 | $this->lastquery = $query; |
||
| 585 | $this->_results = $ret; |
||
| 586 | } |
||
| 587 | |||
| 588 | return $ret; |
||
| 589 | } |
||
| 590 | |||
| 591 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 592 | |||
| 593 | /** |
||
| 594 | * Returns the current line (as an object) for the resultset cursor |
||
| 595 | * |
||
| 596 | * @param resource $resultset Curseur de la requete voulue |
||
| 597 | * @return false|object Object result line or false if KO or end of cursor |
||
| 598 | */ |
||
| 599 | public function fetch_object($resultset) |
||
| 600 | { |
||
| 601 | // phpcs:enable |
||
| 602 | // If resultset not provided, we take the last used by connection |
||
| 603 | if (!is_resource($resultset) && !is_object($resultset)) { |
||
| 604 | $resultset = $this->_results; |
||
| 605 | } |
||
| 606 | return pg_fetch_object($resultset); |
||
| 607 | } |
||
| 608 | |||
| 609 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 610 | |||
| 611 | /** |
||
| 612 | * Return datas as an array |
||
| 613 | * |
||
| 614 | * @param resource $resultset Resultset of request |
||
| 615 | * @return false|array Array |
||
| 616 | */ |
||
| 617 | public function fetch_array($resultset) |
||
| 618 | { |
||
| 619 | // phpcs:enable |
||
| 620 | // If resultset not provided, we take the last used by connection |
||
| 621 | if (!is_resource($resultset) && !is_object($resultset)) { |
||
| 622 | $resultset = $this->_results; |
||
| 623 | } |
||
| 624 | return pg_fetch_array($resultset); |
||
| 625 | } |
||
| 626 | |||
| 627 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 628 | |||
| 629 | /** |
||
| 630 | * Return datas as an array |
||
| 631 | * |
||
| 632 | * @param resource $resultset Resultset of request |
||
| 633 | * @return false|array Array |
||
| 634 | */ |
||
| 635 | public function fetch_row($resultset) |
||
| 643 | } |
||
| 644 | |||
| 645 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 646 | |||
| 647 | /** |
||
| 648 | * Return number of lines for result of a SELECT |
||
| 649 | * |
||
| 650 | * @param resource $resultset Resulset of requests |
||
| 651 | * @return int Nb of lines, -1 on error |
||
| 652 | * @see affected_rows() |
||
| 653 | */ |
||
| 654 | public function num_rows($resultset) |
||
| 655 | { |
||
| 656 | // phpcs:enable |
||
| 657 | // If resultset not provided, we take the last used by connection |
||
| 658 | if (!is_resource($resultset) && !is_object($resultset)) { |
||
| 659 | $resultset = $this->_results; |
||
| 660 | } |
||
| 661 | return pg_num_rows($resultset); |
||
| 662 | } |
||
| 663 | |||
| 664 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 665 | |||
| 666 | /** |
||
| 667 | * Return the number of lines in the result of a request INSERT, DELETE or UPDATE |
||
| 668 | * |
||
| 669 | * @param resource $resultset Result set of request |
||
| 670 | * @return int Nb of lines |
||
| 671 | * @see num_rows() |
||
| 672 | */ |
||
| 673 | public function affected_rows($resultset) |
||
| 683 | } |
||
| 684 | |||
| 685 | |||
| 686 | /** |
||
| 687 | * Libere le dernier resultset utilise sur cette connection |
||
| 688 | * |
||
| 689 | * @param resource $resultset Result set of request |
||
| 690 | * @return void |
||
| 691 | */ |
||
| 692 | public function free($resultset = null) |
||
| 701 | } |
||
| 702 | } |
||
| 703 | |||
| 704 | |||
| 705 | /** |
||
| 706 | * Define limits and offset of request |
||
| 707 | * |
||
| 708 | * @param int $limit Maximum number of lines returned (-1=conf->liste_limit, 0=no limit) |
||
| 709 | * @param int $offset Numero of line from where starting fetch |
||
| 710 | * @return string String with SQL syntax to add a limit and offset |
||
| 711 | */ |
||
| 712 | public function plimit($limit = 0, $offset = 0) |
||
| 713 | { |
||
| 714 | global $conf; |
||
| 715 | if (empty($limit)) { |
||
| 716 | return ""; |
||
| 717 | } |
||
| 718 | if ($limit < 0) { |
||
| 719 | $limit = $conf->liste_limit; |
||
| 720 | } |
||
| 721 | if ($offset > 0) { |
||
| 722 | return " LIMIT " . $limit . " OFFSET " . $offset . " "; |
||
| 723 | } else { |
||
| 724 | return " LIMIT $limit "; |
||
| 725 | } |
||
| 726 | } |
||
| 727 | |||
| 728 | |||
| 729 | /** |
||
| 730 | * Escape a string to insert data |
||
| 731 | * |
||
| 732 | * @param string $stringtoencode String to escape |
||
| 733 | * @return string String escaped |
||
| 734 | */ |
||
| 735 | public function escape($stringtoencode) |
||
| 736 | { |
||
| 737 | return pg_escape_string($this->db, $stringtoencode); |
||
| 738 | } |
||
| 739 | |||
| 740 | /** |
||
| 741 | * Escape a string to insert data into a like |
||
| 742 | * |
||
| 743 | * @param string $stringtoencode String to escape |
||
| 744 | * @return string String escaped |
||
| 745 | */ |
||
| 746 | public function escapeforlike($stringtoencode) |
||
| 747 | { |
||
| 748 | return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string)$stringtoencode); |
||
| 749 | } |
||
| 750 | |||
| 751 | /** |
||
| 752 | * Format a SQL IF |
||
| 753 | * |
||
| 754 | * @param string $test Test expression (example: 'cd.statut=0', 'field IS NULL') |
||
| 755 | * @param string $resok Result to generate when test is True |
||
| 756 | * @param string $resko Result to generate when test is False |
||
| 757 | * @return string chaine format SQL |
||
| 758 | */ |
||
| 759 | public function ifsql($test, $resok, $resko) |
||
| 760 | { |
||
| 761 | return '(CASE WHEN ' . $test . ' THEN ' . $resok . ' ELSE ' . $resko . ' END)'; |
||
| 762 | } |
||
| 763 | |||
| 764 | /** |
||
| 765 | * Format a SQL REGEXP |
||
| 766 | * |
||
| 767 | * @param string $subject Field name to test |
||
| 768 | * @param string $pattern SQL pattern to match |
||
| 769 | * @param int $sqlstring 0=the string being tested is a hard coded string, 1=the string is a field |
||
| 770 | * @return string SQL string |
||
| 771 | */ |
||
| 772 | public function regexpsql($subject, $pattern, $sqlstring = 0) |
||
| 773 | { |
||
| 774 | if ($sqlstring) { |
||
| 775 | return "(" . $subject . " ~ '" . $this->escape($pattern) . "')"; |
||
| 776 | } |
||
| 777 | |||
| 778 | return "('" . $this->escape($subject) . "' ~ '" . $this->escape($pattern) . "')"; |
||
| 779 | } |
||
| 780 | |||
| 781 | |||
| 782 | /** |
||
| 783 | * Renvoie le code erreur generique de l'operation precedente. |
||
| 784 | * |
||
| 785 | * @return string Error code (Examples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) |
||
| 786 | */ |
||
| 787 | public function errno() |
||
| 788 | { |
||
| 789 | if (!$this->connected) { |
||
| 790 | // Si il y a eu echec de connection, $this->db n'est pas valide. |
||
| 791 | return 'DB_ERROR_FAILED_TO_CONNECT'; |
||
| 792 | } else { |
||
| 793 | // Constants to convert error code to a generic Dolibarr error code |
||
| 794 | $errorcode_map = array( |
||
| 795 | 1004 => 'DB_ERROR_CANNOT_CREATE', |
||
| 796 | 1005 => 'DB_ERROR_CANNOT_CREATE', |
||
| 797 | 1006 => 'DB_ERROR_CANNOT_CREATE', |
||
| 798 | 1007 => 'DB_ERROR_ALREADY_EXISTS', |
||
| 799 | 1008 => 'DB_ERROR_CANNOT_DROP', |
||
| 800 | 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP', |
||
| 801 | 1044 => 'DB_ERROR_ACCESSDENIED', |
||
| 802 | 1046 => 'DB_ERROR_NODBSELECTED', |
||
| 803 | 1048 => 'DB_ERROR_CONSTRAINT', |
||
| 804 | '42P07' => 'DB_ERROR_TABLE_OR_KEY_ALREADY_EXISTS', |
||
| 805 | '42703' => 'DB_ERROR_NOSUCHFIELD', |
||
| 806 | 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', |
||
| 807 | 42701 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', |
||
| 808 | '42710' => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', |
||
| 809 | '23505' => 'DB_ERROR_RECORD_ALREADY_EXISTS', |
||
| 810 | '42704' => 'DB_ERROR_NO_INDEX_TO_DROP', // May also be Type xxx does not exists |
||
| 811 | '42601' => 'DB_ERROR_SYNTAX', |
||
| 812 | '42P16' => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS', |
||
| 813 | 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY', |
||
| 814 | 1091 => 'DB_ERROR_NOSUCHFIELD', |
||
| 815 | 1100 => 'DB_ERROR_NOT_LOCKED', |
||
| 816 | 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW', |
||
| 817 | '42P01' => 'DB_ERROR_NOSUCHTABLE', |
||
| 818 | '23503' => 'DB_ERROR_NO_PARENT', |
||
| 819 | 1217 => 'DB_ERROR_CHILD_EXISTS', |
||
| 820 | 1451 => 'DB_ERROR_CHILD_EXISTS', |
||
| 821 | '42P04' => 'DB_DATABASE_ALREADY_EXISTS' |
||
| 822 | ); |
||
| 823 | |||
| 824 | $errorlabel = pg_last_error($this->db); |
||
| 825 | $errorcode = ''; |
||
| 826 | $reg = array(); |
||
| 827 | if (preg_match('/: *([0-9P]+):/', $errorlabel, $reg)) { |
||
| 828 | $errorcode = $reg[1]; |
||
| 829 | if (isset($errorcode_map[$errorcode])) { |
||
| 830 | return $errorcode_map[$errorcode]; |
||
| 831 | } |
||
| 832 | } |
||
| 833 | $errno = $errorcode ? $errorcode : $errorlabel; |
||
| 834 | return ($errno ? 'DB_ERROR_' . $errno : '0'); |
||
| 835 | } |
||
| 836 | // '/(Table does not exist\.|Relation [\"\'].*[\"\'] does not exist|sequence does not exist|class ".+" not found)$/' => 'DB_ERROR_NOSUCHTABLE', |
||
| 837 | // '/table [\"\'].*[\"\'] does not exist/' => 'DB_ERROR_NOSUCHTABLE', |
||
| 838 | // '/Relation [\"\'].*[\"\'] already exists|Cannot insert a duplicate key into (a )?unique index.*/' => 'DB_ERROR_RECORD_ALREADY_EXISTS', |
||
| 839 | // '/divide by zero$/' => 'DB_ERROR_DIVZERO', |
||
| 840 | // '/pg_atoi: error in .*: can\'t parse /' => 'DB_ERROR_INVALID_NUMBER', |
||
| 841 | // '/ttribute [\"\'].*[\"\'] not found$|Relation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => 'DB_ERROR_NOSUCHFIELD', |
||
| 842 | // '/parser: parse error at or near \"/' => 'DB_ERROR_SYNTAX', |
||
| 843 | // '/referential integrity violation/' => 'DB_ERROR_CONSTRAINT' |
||
| 844 | } |
||
| 845 | |||
| 846 | /** |
||
| 847 | * Renvoie le texte de l'erreur pgsql de l'operation precedente |
||
| 848 | * |
||
| 849 | * @return string Error text |
||
| 850 | */ |
||
| 851 | public function error() |
||
| 852 | { |
||
| 853 | return pg_last_error($this->db); |
||
| 854 | } |
||
| 855 | |||
| 856 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 857 | |||
| 858 | /** |
||
| 859 | * Get last ID after an insert INSERT |
||
| 860 | * |
||
| 861 | * @param string $tab Table name concerned by insert. Ne sert pas sous MySql mais requis pour compatibilite avec PostgreSQL |
||
| 862 | * @param string $fieldid Field name |
||
| 863 | * @return int Id of row |
||
| 864 | */ |
||
| 865 | public function last_insert_id($tab, $fieldid = 'rowid') |
||
| 866 | { |
||
| 867 | // phpcs:enable |
||
| 868 | //$result = pg_query($this->db,"SELECT MAX(".$fieldid.") FROM ".$tab); |
||
| 869 | $result = pg_query($this->db, "SELECT currval('" . $tab . "_" . $fieldid . "_seq')"); |
||
| 870 | if (!$result) { |
||
| 871 | print pg_last_error($this->db); |
||
| 872 | exit; |
||
| 873 | } |
||
| 874 | //$nbre = pg_num_rows($result); |
||
| 875 | $row = pg_fetch_result($result, 0, 0); |
||
| 876 | return (int)$row; |
||
| 877 | } |
||
| 878 | |||
| 879 | /** |
||
| 880 | * Encrypt sensitive data in database |
||
| 881 | * Warning: This function includes the escape and add the SQL simple quotes on strings. |
||
| 882 | * |
||
| 883 | * @param string $fieldorvalue Field name or value to encrypt |
||
| 884 | * @param int $withQuotes Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated). |
||
| 885 | * @return string XXX(field) or XXX('value') or field or 'value' |
||
| 886 | */ |
||
| 887 | public function encrypt($fieldorvalue, $withQuotes = 1) |
||
| 888 | { |
||
| 889 | //global $conf; |
||
| 890 | |||
| 891 | // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) |
||
| 892 | //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); |
||
| 893 | |||
| 894 | //Encryption key |
||
| 895 | //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); |
||
| 896 | |||
| 897 | $return = $fieldorvalue; |
||
| 898 | return ($withQuotes ? "'" : "") . $this->escape($return) . ($withQuotes ? "'" : ""); |
||
| 899 | } |
||
| 900 | |||
| 901 | |||
| 902 | /** |
||
| 903 | * Decrypt sensitive data in database |
||
| 904 | * |
||
| 905 | * @param string $value Value to decrypt |
||
| 906 | * @return string Decrypted value if used |
||
| 907 | */ |
||
| 908 | public function decrypt($value) |
||
| 909 | { |
||
| 910 | //global $conf; |
||
| 911 | |||
| 912 | // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) |
||
| 913 | //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); |
||
| 914 | |||
| 915 | //Encryption key |
||
| 916 | //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); |
||
| 917 | |||
| 918 | $return = $value; |
||
| 919 | return $return; |
||
| 920 | } |
||
| 921 | |||
| 922 | |||
| 923 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 924 | |||
| 925 | /** |
||
| 926 | * Return connection ID |
||
| 927 | * |
||
| 928 | * @return string Id connection |
||
| 929 | */ |
||
| 930 | public function DDLGetConnectId() |
||
| 931 | { |
||
| 932 | // phpcs:enable |
||
| 933 | return '?'; |
||
| 934 | } |
||
| 935 | |||
| 936 | |||
| 937 | |||
| 938 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 939 | |||
| 940 | /** |
||
| 941 | * Create a new database |
||
| 942 | * Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated |
||
| 943 | * We force to create database with charset this->forcecharset and collate this->forcecollate |
||
| 944 | * |
||
| 945 | * @param string $database Database name to create |
||
| 946 | * @param string $charset Charset used to store data |
||
| 947 | * @param string $collation Charset used to sort data |
||
| 948 | * @param string $owner Username of database owner |
||
| 949 | * @return false|resource Resource defined if OK, null if KO |
||
| 950 | */ |
||
| 951 | public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') |
||
| 952 | { |
||
| 953 | // phpcs:enable |
||
| 954 | if (empty($charset)) { |
||
| 955 | $charset = $this->forcecharset; |
||
| 956 | } |
||
| 957 | if (empty($collation)) { |
||
| 958 | $collation = $this->forcecollate; |
||
| 959 | } |
||
| 960 | |||
| 961 | // Test charset match LC_TYPE (pgsql error otherwise) |
||
| 962 | //print $charset.' '.setlocale(LC_CTYPE,'0'); exit; |
||
| 963 | |||
| 964 | // NOTE: Do not use ' around the database name |
||
| 965 | $sql = "CREATE DATABASE " . $this->escape($database) . " OWNER '" . $this->escape($owner) . "' ENCODING '" . $this->escape($charset) . "'"; |
||
| 966 | |||
| 967 | dol_syslog($sql, LOG_DEBUG); |
||
| 968 | $ret = $this->query($sql); |
||
| 969 | |||
| 970 | return $ret; |
||
| 971 | } |
||
| 972 | |||
| 973 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 974 | |||
| 975 | /** |
||
| 976 | * List tables into a database |
||
| 977 | * |
||
| 978 | * @param string $database Name of database |
||
| 979 | * @param string $table Name of table filter ('xxx%') |
||
| 980 | * @return array List of tables in an array |
||
| 981 | */ |
||
| 982 | public function DDLListTables($database, $table = '') |
||
| 983 | { |
||
| 984 | // phpcs:enable |
||
| 985 | $listtables = array(); |
||
| 986 | |||
| 987 | $escapedlike = ''; |
||
| 988 | if ($table) { |
||
| 989 | $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); |
||
| 990 | |||
| 991 | $escapedlike = " AND table_name LIKE '" . $this->escape($tmptable) . "'"; |
||
| 992 | } |
||
| 993 | $result = pg_query($this->db, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" . $escapedlike . " ORDER BY table_name"); |
||
| 994 | if ($result) { |
||
| 995 | while ($row = $this->fetch_row($result)) { |
||
| 996 | $listtables[] = $row[0]; |
||
| 997 | } |
||
| 998 | } |
||
| 999 | return $listtables; |
||
| 1000 | } |
||
| 1001 | |||
| 1002 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1003 | |||
| 1004 | /** |
||
| 1005 | * List tables into a database |
||
| 1006 | * |
||
| 1007 | * @param string $database Name of database |
||
| 1008 | * @param string $table Name of table filter ('xxx%') |
||
| 1009 | * @return array List of tables in an array |
||
| 1010 | */ |
||
| 1011 | public function DDLListTablesFull($database, $table = '') |
||
| 1012 | { |
||
| 1013 | // phpcs:enable |
||
| 1014 | $listtables = array(); |
||
| 1015 | |||
| 1016 | $escapedlike = ''; |
||
| 1017 | if ($table) { |
||
| 1018 | $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); |
||
| 1019 | |||
| 1020 | $escapedlike = " AND table_name LIKE '" . $this->escape($tmptable) . "'"; |
||
| 1021 | } |
||
| 1022 | $result = pg_query($this->db, "SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'" . $escapedlike . " ORDER BY table_name"); |
||
| 1023 | if ($result) { |
||
| 1024 | while ($row = $this->fetch_row($result)) { |
||
| 1025 | $listtables[] = $row; |
||
| 1026 | } |
||
| 1027 | } |
||
| 1028 | return $listtables; |
||
| 1029 | } |
||
| 1030 | |||
| 1031 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1032 | |||
| 1033 | /** |
||
| 1034 | * List information of columns into a table. |
||
| 1035 | * |
||
| 1036 | * @param string $table Name of table |
||
| 1037 | * @return array Array with information on table |
||
| 1038 | */ |
||
| 1039 | public function DDLInfoTable($table) |
||
| 1040 | { |
||
| 1041 | // phpcs:enable |
||
| 1042 | $infotables = array(); |
||
| 1043 | |||
| 1044 | $sql = "SELECT "; |
||
| 1045 | $sql .= " infcol.column_name as 'Column',"; |
||
| 1046 | $sql .= " CASE WHEN infcol.character_maximum_length IS NOT NULL THEN infcol.udt_name || '('||infcol.character_maximum_length||')'"; |
||
| 1047 | $sql .= " ELSE infcol.udt_name"; |
||
| 1048 | $sql .= " END as 'Type',"; |
||
| 1049 | $sql .= " infcol.collation_name as 'Collation',"; |
||
| 1050 | $sql .= " infcol.is_nullable as 'Null',"; |
||
| 1051 | $sql .= " '' as 'Key',"; |
||
| 1052 | $sql .= " infcol.column_default as 'Default',"; |
||
| 1053 | $sql .= " '' as 'Extra',"; |
||
| 1054 | $sql .= " '' as 'Privileges'"; |
||
| 1055 | $sql .= " FROM information_schema.columns infcol"; |
||
| 1056 | $sql .= " WHERE table_schema = 'public' "; |
||
| 1057 | $sql .= " AND table_name = '" . $this->escape($table) . "'"; |
||
| 1058 | $sql .= " ORDER BY ordinal_position;"; |
||
| 1059 | |||
| 1060 | $result = $this->query($sql); |
||
| 1061 | if ($result) { |
||
| 1062 | while ($row = $this->fetch_row($result)) { |
||
| 1063 | $infotables[] = $row; |
||
| 1064 | } |
||
| 1065 | } |
||
| 1066 | return $infotables; |
||
| 1067 | } |
||
| 1068 | |||
| 1069 | |||
| 1070 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1071 | |||
| 1072 | /** |
||
| 1073 | * Create a table into database |
||
| 1074 | * |
||
| 1075 | * @param string $table Nom de la table |
||
| 1076 | * @param array<string,array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string}> $fields Tableau associatif [nom champ][tableau des descriptions] |
||
| 1077 | * @param string $primary_key Nom du champ qui sera la clef primaire |
||
| 1078 | * @param string $type Type de la table |
||
| 1079 | * @param array $unique_keys Tableau associatifs Nom de champs qui seront clef unique => valeur |
||
| 1080 | * @param array $fulltext_keys Tableau des Nom de champs qui seront indexes en fulltext |
||
| 1081 | * @param array $keys Tableau des champs cles noms => valeur |
||
| 1082 | * @return int Return integer <0 if KO, >=0 if OK |
||
| 1083 | */ |
||
| 1084 | public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) |
||
| 1085 | { |
||
| 1086 | // phpcs:enable |
||
| 1087 | // @TODO: $fulltext_keys parameter is unused |
||
| 1088 | |||
| 1089 | $sqlk = array(); |
||
| 1090 | $sqluq = array(); |
||
| 1091 | |||
| 1092 | // Keys found into the array $fields: type,value,attribute,null,default,extra |
||
| 1093 | // ex. : $fields['rowid'] = array( |
||
| 1094 | // 'type'=>'int' or 'integer', |
||
| 1095 | // 'value'=>'11', |
||
| 1096 | // 'null'=>'not null', |
||
| 1097 | // 'extra'=> 'auto_increment' |
||
| 1098 | // ); |
||
| 1099 | $sql = "CREATE TABLE " . $this->sanitize($table) . "("; |
||
| 1100 | $i = 0; |
||
| 1101 | $sqlfields = array(); |
||
| 1102 | foreach ($fields as $field_name => $field_desc) { |
||
| 1103 | $sqlfields[$i] = $this->sanitize($field_name) . " "; |
||
| 1104 | $sqlfields[$i] .= $this->sanitize($field_desc['type']); |
||
| 1105 | if (isset($field_desc['value']) && $field_desc['value'] !== '') { |
||
| 1106 | $sqlfields[$i] .= "(" . $this->sanitize($field_desc['value']) . ")"; |
||
| 1107 | } |
||
| 1108 | if (isset($field_desc['attribute']) && $field_desc['attribute'] !== '') { |
||
| 1109 | $sqlfields[$i] .= " " . $this->sanitize($field_desc['attribute']); |
||
| 1110 | } |
||
| 1111 | if (isset($field_desc['default']) && $field_desc['default'] !== '') { |
||
| 1112 | if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) { |
||
| 1113 | $sqlfields[$i] .= " DEFAULT " . ((float)$field_desc['default']); |
||
| 1114 | } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') { |
||
| 1115 | $sqlfields[$i] .= " DEFAULT " . $this->sanitize($field_desc['default']); |
||
| 1116 | } else { |
||
| 1117 | $sqlfields[$i] .= " DEFAULT '" . $this->escape($field_desc['default']) . "'"; |
||
| 1118 | } |
||
| 1119 | } |
||
| 1120 | if (isset($field_desc['null']) && $field_desc['null'] !== '') { |
||
| 1121 | $sqlfields[$i] .= " " . $this->sanitize($field_desc['null'], 0, 0, 1); |
||
| 1122 | } |
||
| 1123 | if (isset($field_desc['extra']) && $field_desc['extra'] !== '') { |
||
| 1124 | $sqlfields[$i] .= " " . $this->sanitize($field_desc['extra'], 0, 0, 1); |
||
| 1125 | } |
||
| 1126 | if (!empty($primary_key) && $primary_key == $field_name) { |
||
| 1127 | $sqlfields[$i] .= " AUTO_INCREMENT PRIMARY KEY"; // mysql instruction that will be converted by driver late |
||
| 1128 | } |
||
| 1129 | $i++; |
||
| 1130 | } |
||
| 1131 | |||
| 1132 | if (is_array($unique_keys)) { |
||
| 1133 | $i = 0; |
||
| 1134 | foreach ($unique_keys as $key => $value) { |
||
| 1135 | $sqluq[$i] = "UNIQUE KEY '" . $this->sanitize($key) . "' ('" . $this->escape($value) . "')"; |
||
| 1136 | $i++; |
||
| 1137 | } |
||
| 1138 | } |
||
| 1139 | if (is_array($keys)) { |
||
| 1140 | $i = 0; |
||
| 1141 | foreach ($keys as $key => $value) { |
||
| 1142 | $sqlk[$i] = "KEY " . $this->sanitize($key) . " (" . $value . ")"; |
||
| 1143 | $i++; |
||
| 1144 | } |
||
| 1145 | } |
||
| 1146 | $sql .= implode(', ', $sqlfields); |
||
| 1147 | if ($unique_keys != "") { |
||
| 1148 | $sql .= "," . implode(',', $sqluq); |
||
| 1149 | } |
||
| 1150 | if (is_array($keys)) { |
||
| 1151 | $sql .= "," . implode(',', $sqlk); |
||
| 1152 | } |
||
| 1153 | $sql .= ")"; |
||
| 1154 | //$sql .= " engine=".$this->sanitize($type); |
||
| 1155 | |||
| 1156 | if (!$this->query($sql, 1)) { |
||
| 1157 | return -1; |
||
| 1158 | } else { |
||
| 1159 | return 1; |
||
| 1160 | } |
||
| 1161 | } |
||
| 1162 | |||
| 1163 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1164 | |||
| 1165 | /** |
||
| 1166 | * Drop a table into database |
||
| 1167 | * |
||
| 1168 | * @param string $table Name of table |
||
| 1169 | * @return int Return integer <0 if KO, >=0 if OK |
||
| 1170 | */ |
||
| 1171 | public function DDLDropTable($table) |
||
| 1172 | { |
||
| 1173 | // phpcs:enable |
||
| 1174 | $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); |
||
| 1175 | |||
| 1176 | $sql = "DROP TABLE " . $this->sanitize($tmptable); |
||
| 1177 | |||
| 1178 | if (!$this->query($sql, 1)) { |
||
| 1179 | return -1; |
||
| 1180 | } else { |
||
| 1181 | return 1; |
||
| 1182 | } |
||
| 1183 | } |
||
| 1184 | |||
| 1185 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1186 | |||
| 1187 | /** |
||
| 1188 | * Return a pointer of line with description of a table or field |
||
| 1189 | * |
||
| 1190 | * @param string $table Name of table |
||
| 1191 | * @param string $field Optionnel : Name of field if we want description of field |
||
| 1192 | * @return false|resource Resultset x (x->attname) |
||
| 1193 | */ |
||
| 1194 | public function DDLDescTable($table, $field = "") |
||
| 1195 | { |
||
| 1196 | // phpcs:enable |
||
| 1197 | $sql = "SELECT attname FROM pg_attribute, pg_type WHERE typname = '" . $this->escape($table) . "' AND attrelid = typrelid"; |
||
| 1198 | $sql .= " AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')"; |
||
| 1199 | if ($field) { |
||
| 1200 | $sql .= " AND attname = '" . $this->escape($field) . "'"; |
||
| 1201 | } |
||
| 1202 | |||
| 1203 | dol_syslog($sql, LOG_DEBUG); |
||
| 1204 | $this->_results = $this->query($sql); |
||
| 1205 | return $this->_results; |
||
| 1206 | } |
||
| 1207 | |||
| 1208 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1209 | |||
| 1210 | /** |
||
| 1211 | * Create a new field into table |
||
| 1212 | * |
||
| 1213 | * @param string $table Name of table |
||
| 1214 | * @param string $field_name Name of field to add |
||
| 1215 | * @param array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string} $field_desc Associative array of description of the field to insert [parameter name][parameter value] |
||
| 1216 | * @param string $field_position Optionnel ex.: "after champtruc" |
||
| 1217 | * @return int Return integer <0 if KO, >0 if OK |
||
| 1218 | */ |
||
| 1219 | public function DDLAddField($table, $field_name, $field_desc, $field_position = "") |
||
| 1220 | { |
||
| 1221 | // phpcs:enable |
||
| 1222 | // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra |
||
| 1223 | // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); |
||
| 1224 | $sql = "ALTER TABLE " . $this->sanitize($table) . " ADD " . $this->sanitize($field_name) . " "; |
||
| 1225 | $sql .= $this->sanitize($field_desc['type']); |
||
| 1226 | if (isset($field_desc['value']) && preg_match("/^[^\s]/i", $field_desc['value'])) { |
||
| 1227 | if (!in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'date', 'datetime')) && $field_desc['value']) { |
||
| 1228 | $sql .= "(" . $this->sanitize($field_desc['value']) . ")"; |
||
| 1229 | } |
||
| 1230 | } |
||
| 1231 | if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) { |
||
| 1232 | $sql .= " " . $this->sanitize($field_desc['attribute']); |
||
| 1233 | } |
||
| 1234 | if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) { |
||
| 1235 | $sql .= " " . $field_desc['null']; |
||
| 1236 | } |
||
| 1237 | if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) { |
||
| 1238 | if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) { |
||
| 1239 | $sql .= " DEFAULT " . ((float)$field_desc['default']); |
||
| 1240 | } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP') { |
||
| 1241 | $sql .= " DEFAULT " . $this->sanitize($field_desc['default']); |
||
| 1242 | } else { |
||
| 1243 | $sql .= " DEFAULT '" . $this->escape($field_desc['default']) . "'"; |
||
| 1244 | } |
||
| 1245 | } |
||
| 1246 | if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) { |
||
| 1247 | $sql .= " " . $this->sanitize($field_desc['extra'], 0, 0, 1); |
||
| 1248 | } |
||
| 1249 | $sql .= " " . $this->sanitize($field_position, 0, 0, 1); |
||
| 1250 | |||
| 1251 | dol_syslog($sql, LOG_DEBUG); |
||
| 1252 | if (!$this->query($sql)) { |
||
| 1253 | return -1; |
||
| 1254 | } |
||
| 1255 | return 1; |
||
| 1256 | } |
||
| 1257 | |||
| 1258 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1259 | |||
| 1260 | /** |
||
| 1261 | * Update format of a field into a table |
||
| 1262 | * |
||
| 1263 | * @param string $table Name of table |
||
| 1264 | * @param string $field_name Name of field to modify |
||
| 1265 | * @param array{type:string,label:string,enabled:int<0,2>|string,position:int,notnull?:int,visible:int,noteditable?:int,default?:string,index?:int,foreignkey?:string,searchall?:int,isameasure?:int,css?:string,csslist?:string,help?:string,showoncombobox?:int,disabled?:int,arrayofkeyval?:array<int,string>,comment?:string} $field_desc Array with description of field format |
||
| 1266 | * @return int Return integer <0 if KO, >0 if OK |
||
| 1267 | */ |
||
| 1268 | public function DDLUpdateField($table, $field_name, $field_desc) |
||
| 1269 | { |
||
| 1270 | // phpcs:enable |
||
| 1271 | $sql = "ALTER TABLE " . $this->sanitize($table); |
||
| 1272 | $sql .= " ALTER COLUMN " . $this->sanitize($field_name) . " TYPE " . $this->sanitize($field_desc['type']); |
||
| 1273 | if (isset($field_desc['value']) && preg_match("/^[^\s]/i", $field_desc['value'])) { |
||
| 1274 | if (!in_array($field_desc['type'], array('smallint', 'int', 'date', 'datetime')) && $field_desc['value']) { |
||
| 1275 | $sql .= "(" . $this->sanitize($field_desc['value']) . ")"; |
||
| 1276 | } |
||
| 1277 | } |
||
| 1278 | |||
| 1279 | if (isset($field_desc['value']) && ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL')) { |
||
| 1280 | // We will try to change format of column to NOT NULL. To be sure the ALTER works, we try to update fields that are NULL |
||
| 1281 | if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') { |
||
| 1282 | $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"; |
||
| 1283 | $this->query($sqlbis); |
||
| 1284 | } elseif (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) { |
||
| 1285 | $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"; |
||
| 1286 | $this->query($sqlbis); |
||
| 1287 | } |
||
| 1288 | } |
||
| 1289 | |||
| 1290 | if (isset($field_desc['default']) && $field_desc['default'] != '') { |
||
| 1291 | if (in_array($field_desc['type'], array('tinyint', 'smallint', 'int', 'double'))) { |
||
| 1292 | $sql .= ", ALTER COLUMN " . $this->sanitize($field_name) . " SET DEFAULT " . ((float)$field_desc['default']); |
||
| 1293 | } elseif ($field_desc['type'] != 'text') { // Default not supported on text fields ? |
||
| 1294 | $sql .= ", ALTER COLUMN " . $this->sanitize($field_name) . " SET DEFAULT '" . $this->escape($field_desc['default']) . "'"; |
||
| 1295 | } |
||
| 1296 | } |
||
| 1297 | |||
| 1298 | dol_syslog($sql, LOG_DEBUG); |
||
| 1299 | if (!$this->query($sql)) { |
||
| 1300 | return -1; |
||
| 1301 | } |
||
| 1302 | return 1; |
||
| 1303 | } |
||
| 1304 | |||
| 1305 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1306 | |||
| 1307 | /** |
||
| 1308 | * Drop a field from table |
||
| 1309 | * |
||
| 1310 | * @param string $table Name of table |
||
| 1311 | * @param string $field_name Name of field to drop |
||
| 1312 | * @return int Return integer <0 if KO, >0 if OK |
||
| 1313 | */ |
||
| 1314 | public function DDLDropField($table, $field_name) |
||
| 1325 | } |
||
| 1326 | |||
| 1327 | // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps |
||
| 1328 | |||
| 1329 | /** |
||
| 1330 | * Create a user to connect to database |
||
| 1331 | * |
||
| 1332 | * @param string $dolibarr_main_db_host Ip server |
||
| 1333 | * @param string $dolibarr_main_db_user Name of user to create |
||
| 1334 | * @param string $dolibarr_main_db_pass Password of user to create |
||
| 1335 | * @param string $dolibarr_main_db_name Database name where user must be granted |
||
| 1336 | * @return int Return integer <0 if KO, >=0 if OK |
||
| 1337 | */ |
||
| 1338 | public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) |
||
| 1339 | { |
||
| 1340 | // phpcs:enable |
||
| 1341 | // Note: using ' on user does not works with pgsql |
||
| 1342 | $sql = "CREATE USER " . $this->sanitize($dolibarr_main_db_user) . " with password '" . $this->escape($dolibarr_main_db_pass) . "'"; |
||
| 1343 | |||
| 1344 | dol_syslog(get_only_class($this) . "::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log |
||
| 1345 | $resql = $this->query($sql); |
||
| 1346 | if (!$resql) { |
||
| 1347 | return -1; |
||
| 1348 | } |
||
| 1349 | |||
| 1350 | return 1; |
||
| 1351 | } |
||
| 1352 | |||
| 1353 | /** |
||
| 1354 | * Return charset used to store data in database |
||
| 1355 | * |
||
| 1356 | * @return string Charset |
||
| 1357 | */ |
||
| 1358 | public function getDefaultCharacterSetDatabase() |
||
| 1359 | { |
||
| 1360 | $resql = $this->query('SHOW SERVER_ENCODING'); |
||
| 1361 | if ($resql) { |
||
| 1362 | $liste = $this->fetch_array($resql); |
||
| 1363 | return $liste['server_encoding']; |
||
| 1364 | } else { |
||
| 1365 | return ''; |
||
| 1366 | } |
||
| 1367 | } |
||
| 1368 | |||
| 1369 | /** |
||
| 1370 | * Return list of available charset that can be used to store data in database |
||
| 1371 | * |
||
| 1372 | * @return array|null List of Charset |
||
| 1373 | */ |
||
| 1374 | public function getListOfCharacterSet() |
||
| 1375 | { |
||
| 1376 | $resql = $this->query('SHOW SERVER_ENCODING'); |
||
| 1377 | $liste = array(); |
||
| 1378 | if ($resql) { |
||
| 1379 | $i = 0; |
||
| 1380 | while ($obj = $this->fetch_object($resql)) { |
||
| 1381 | $liste[$i]['charset'] = $obj->server_encoding; |
||
| 1382 | $liste[$i]['description'] = 'Default database charset'; |
||
| 1383 | $i++; |
||
| 1384 | } |
||
| 1385 | $this->free($resql); |
||
| 1386 | } else { |
||
| 1387 | return null; |
||
| 1388 | } |
||
| 1389 | return $liste; |
||
| 1390 | } |
||
| 1391 | |||
| 1392 | /** |
||
| 1393 | * Return collation used in database |
||
| 1394 | * |
||
| 1395 | * @return string Collation value |
||
| 1396 | */ |
||
| 1397 | public function getDefaultCollationDatabase() |
||
| 1398 | { |
||
| 1399 | $resql = $this->query('SHOW LC_COLLATE'); |
||
| 1400 | if ($resql) { |
||
| 1401 | $liste = $this->fetch_array($resql); |
||
| 1402 | return $liste['lc_collate']; |
||
| 1403 | } else { |
||
| 1404 | return ''; |
||
| 1405 | } |
||
| 1406 | } |
||
| 1407 | |||
| 1408 | /** |
||
| 1409 | * Return list of available collation that can be used for database |
||
| 1410 | * |
||
| 1411 | * @return array|null Liste of Collation |
||
| 1412 | */ |
||
| 1413 | public function getListOfCollation() |
||
| 1414 | { |
||
| 1415 | $resql = $this->query('SHOW LC_COLLATE'); |
||
| 1416 | $liste = array(); |
||
| 1417 | if ($resql) { |
||
| 1418 | $i = 0; |
||
| 1419 | while ($obj = $this->fetch_object($resql)) { |
||
| 1420 | $liste[$i]['collation'] = $obj->lc_collate; |
||
| 1421 | $i++; |
||
| 1422 | } |
||
| 1423 | $this->free($resql); |
||
| 1424 | } else { |
||
| 1425 | return null; |
||
| 1426 | } |
||
| 1427 | return $liste; |
||
| 1428 | } |
||
| 1429 | |||
| 1430 | /** |
||
| 1431 | * Return full path of dump program |
||
| 1432 | * |
||
| 1433 | * @return string Full path of dump program |
||
| 1434 | */ |
||
| 1435 | public function getPathOfDump() |
||
| 1436 | { |
||
| 1437 | $fullpathofdump = '/pathtopgdump/pg_dump'; |
||
| 1438 | |||
| 1439 | if (file_exists('/usr/bin/pg_dump')) { |
||
| 1440 | $fullpathofdump = '/usr/bin/pg_dump'; |
||
| 1441 | } else { |
||
| 1442 | // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande |
||
| 1443 | $resql = $this->query('SHOW data_directory'); |
||
| 1444 | if ($resql) { |
||
| 1445 | $liste = $this->fetch_array($resql); |
||
| 1446 | $basedir = $liste['data_directory']; |
||
| 1447 | $fullpathofdump = preg_replace('/data$/', 'bin', $basedir) . '/pg_dump'; |
||
| 1448 | } |
||
| 1449 | } |
||
| 1450 | |||
| 1451 | return $fullpathofdump; |
||
| 1452 | } |
||
| 1453 | |||
| 1454 | /** |
||
| 1455 | * Return full path of restore program |
||
| 1456 | * |
||
| 1457 | * @return string Full path of restore program |
||
| 1458 | */ |
||
| 1459 | public function getPathOfRestore() |
||
| 1460 | { |
||
| 1461 | //$tool='pg_restore'; |
||
| 1462 | $tool = 'psql'; |
||
| 1463 | |||
| 1464 | $fullpathofdump = '/pathtopgrestore/' . $tool; |
||
| 1465 | |||
| 1466 | if (file_exists('/usr/bin/' . $tool)) { |
||
| 1467 | $fullpathofdump = '/usr/bin/' . $tool; |
||
| 1468 | } else { |
||
| 1469 | // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande |
||
| 1470 | $resql = $this->query('SHOW data_directory'); |
||
| 1471 | if ($resql) { |
||
| 1472 | $liste = $this->fetch_array($resql); |
||
| 1473 | $basedir = $liste['data_directory']; |
||
| 1474 | $fullpathofdump = preg_replace('/data$/', 'bin', $basedir) . '/' . $tool; |
||
| 1475 | } |
||
| 1476 | } |
||
| 1477 | |||
| 1478 | return $fullpathofdump; |
||
| 1479 | } |
||
| 1480 | |||
| 1481 | /** |
||
| 1482 | * Return value of server parameters |
||
| 1483 | * |
||
| 1484 | * @param string $filter Filter list on a particular value |
||
| 1485 | * @return array Array of key-values (key=>value) |
||
| 1486 | */ |
||
| 1487 | public function getServerParametersValues($filter = '') |
||
| 1503 | } |
||
| 1504 | |||
| 1505 | /** |
||
| 1506 | * Return value of server status |
||
| 1507 | * |
||
| 1508 | * @param string $filter Filter list on a particular value |
||
| 1509 | * @return array Array of key-values (key=>value) |
||
| 1510 | */ |
||
| 1511 | public function getServerStatusValues($filter = '') |
||
| 1512 | { |
||
| 1513 | /* This is to return current running requests. |
||
| 1525 | } |
||
| 1526 | } |
||
| 1527 |
Loading history...
If you suppress an error, we recommend checking for the error condition explicitly: