pgsql.class.php 50 KB

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