pgsql.class.php 47 KB

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