--- /tmp/dsg/dolibarr/htdocs/core/db/github_19.0.3_Database.interface.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_Database.interface.php @@ -67,4 +67,3 @@ - * @param string $textinlog Add a small text into log. '' by default. - * @return int 1 if transaction successfuly opened or already opened, 0 if error - */ - public function begin($textinlog = ''); + * @return int 1 if transaction successfuly opened or already opened, 0 if error + */ + public function begin(); @@ -101 +100 @@ - public function convertSQLFromMysql($line, $type = 'ddl'); + public static function convertSQLFromMysql($line, $type = 'ddl'); @@ -126 +125 @@ - * @param string $table Name of table filter ('xxx%') + * @param string $table Nmae of table filter ('xxx%') @@ -130,11 +128,0 @@ - // phpcs:enable - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List tables into a database with table type - * - * @param string $database Name of database - * @param string $table Name of table filter ('xxx%') - * @return array List of tables in an array - */ - public function DDLListTablesFull($database, $table = ''); @@ -157 +145 @@ - public function order($sortfield = '', $sortorder = ''); + public function order($sortfield = null, $sortorder = null); @@ -187,2 +175,2 @@ - * @param string $stringtoencode String to escape - * @return string String escaped + * @param string $stringtoencode String to escape + * @return string String escaped @@ -192,17 +179,0 @@ - /** - * Escape a string to insert data into a like. - * Can be used this way: LIKE '%".dbhandler->escape(dbhandler->escapeforlike(...))."%' - * - * @param string $stringtoencode String to escape - * @return string String escaped - */ - public function escapeforlike($stringtoencode); - - /** - * Sanitize a string for SQL forging - * - * @param string $stringtosanitize String to escape - * @return string String escaped - */ - public function sanitize($stringtosanitize); - @@ -213 +184 @@ - * @param string $tab Table name concerned by insert. Not used under MySql but required for compatibility with Postgresql + * @param string $tab Table name concerned by insert. Ne sert pas sous MySql mais requis pour compatibilite avec Postgresql @@ -238,8 +209,7 @@ - * @param string $query SQL query string - * @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). - * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @param int $result_mode Result mode - * @return bool|resource Resultset of answer or false - */ - public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0); + * @param string $query SQL query string + * @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). + * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return resource Resultset of answer + */ + public function query($query, $usesavepoint = 0, $type = 'auto'); @@ -335 +305 @@ - * @return int Return integer <0 if KO, >=0 if OK + * @return int <0 if KO, >=0 if OK @@ -345 +315 @@ - * @return int Return integer <0 if KO, >=0 if OK + * @return int <0 if KO, >=0 if OK @@ -365 +335 @@ - * @return int Return integer <0 if KO, >0 if OK + * @return int <0 if KO, >0 if OK @@ -376 +346 @@ - * @return int Return integer <0 if KO, >0 if OK + * @return int <0 if KO, >0 if OK @@ -388 +358 @@ - * @return int Return integer <0 if KO, >0 if OK + * @return int <0 if KO, >0 if OK @@ -433 +403 @@ - * @return int Return integer <0 if KO, >=0 if OK + * @return int <0 if KO, >=0 if OK @@ -456,7 +426,7 @@ - * Warning: This function includes the escape and add the SQL simple quotes on strings. - * - * @param string $fieldorvalue Field name or value to encrypt - * @param int $withQuotes Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated). - * @return string XXX(field) or XXX('value') or field or 'value' - */ - public function encrypt($fieldorvalue, $withQuotes = 1); + * Warning: This function includes the escape, so it must use direct value + * + * @param string $fieldorvalue Field name or value to encrypt + * @param int $withQuotes Return string with quotes + * @return string XXX(field) or XXX('value') or field or 'value' + */ + public function encrypt($fieldorvalue, $withQuotes = 0); @@ -518,2 +488,2 @@ - * @param resource|PgSql\Connection $resultset Handler of the desired request - * @return Object|false Object result line or false if KO or end of cursor + * @param resource $resultset Cursor of the desired request + * @return Object Object result line or false if KO or end of cursor --- /tmp/dsg/dolibarr/htdocs/core/db/github_19.0.3_DoliDB.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_DoliDB.class.php @@ -32 +32 @@ - /** @var bool|resource|mysqli|SQLite3|PgSql\Connection Database handler */ + /** @var bool|resource|SQLite3 Database handler */ @@ -40 +39,0 @@ - @@ -43 +41,0 @@ - @@ -67,3 +64,0 @@ - /** @var string If we need to set a prefix specific to the database so it can be reused (when defined instead of MAIN_DB_PREFIX) to forge requests */ - public $prefix_db; - @@ -75,13 +69,0 @@ - - - /** - * Return the DB prefix found into prefix_db (if it was set manually by doing $dbhandler->prefix_db=...). - * Otherwise return MAIN_DB_PREFIX (common use). - * - * @return string The DB prefix - */ - public function prefix() - { - return (empty($this->prefix_db) ? MAIN_DB_PREFIX : $this->prefix_db); - } - @@ -96,35 +78,4 @@ - public function ifsql($test, $resok, $resko) - { - //return 'IF('.$test.','.$resok.','.$resko.')'; // Not sql standard - return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)'; - } - - /** - * Return SQL string to force an index - * - * @param string $nameofindex Name of index - * @return string SQL string - */ - public function hintindex($nameofindex) - { - return ''; - } - - - /** - * Format a SQL REGEXP - * - * @param string $subject string tested - * @param string $pattern SQL pattern to match - * @param int $sqlstring whether or not the string being tested is an SQL expression - * @return string SQL string - */ - public function regexpsql($subject, $pattern, $sqlstring = 0) - { - if ($sqlstring) { - return "(". $subject ." REGEXP '" . $pattern . "')"; - } - - return "('". $subject ."' REGEXP '" . $pattern . "')"; - } - + public function ifsql($test, $resok, $resko) + { + return 'IF('.$test.','.$resok.','.$resko.')'; + } @@ -136,8 +87,7 @@ - * @param int $param Date TMS to convert - * @param mixed $gm 'gmt'=Input informations are GMT values, 'tzserver'=Local to server TZ - * @return string Date in a string YYYY-MM-DD HH:MM:SS - */ - public function idate($param, $gm = 'tzserver') - { - // TODO $param should be gmt, so we should have default $gm to 'gmt' instead of default 'tzserver' - return dol_print_date($param, "%Y-%m-%d %H:%M:%S", $gm); + * @param int $param Date TMS to convert + * @return string Date in a string YYYY-MM-DD HH:MM:SS + */ + public function idate($param) + { + // TODO GMT $param should be gmt, so we should add tzouptut to 'gmt' + return dol_print_date($param, "%Y-%m-%d %H:%M:%S"); @@ -151 +101 @@ - public function lasterrno() + public function lasterrno() @@ -157,14 +106,0 @@ - * Sanitize a string for SQL forging - * - * @param string $stringtosanitize String to escape - * @param int $allowsimplequote 1=Allow simple quotes in string. When string is used as a list of SQL string ('aa', 'bb', ...) - * @param int $allowsequals 1=Allow equals sign - * @param int $allowsspace 1=Allow space char - * @return string String escaped - */ - public function sanitize($stringtosanitize, $allowsimplequote = 0, $allowsequals = 0, $allowsspace = 0) - { - return preg_replace('/[^a-z0-9_\-\.,'.($allowsequals ? '=' : '').($allowsimplequote ? "\'" : '').($allowsspace ? ' ' : '').']/i', '', $stringtosanitize); - } - - /** @@ -173,6 +109,6 @@ - * @param string $textinlog Add a small text into log. '' by default. - * @return int 1 if transaction successfuly opened or already opened, 0 if error - */ - public function begin($textinlog = '') - { - if (!$this->transaction_opened) { + * @return int 1 if transaction successfuly opened or already opened, 0 if error + */ + public function begin() + { + if (!$this->transaction_opened) + { @@ -180 +116,2 @@ - if ($ret) { + if ($ret) + { @@ -182 +119 @@ - dol_syslog("BEGIN Transaction".($textinlog ? ' '.$textinlog : ''), LOG_DEBUG); + dol_syslog("BEGIN Transaction", LOG_DEBUG); @@ -184,5 +121,5 @@ - return 1; - } else { - return 0; - } - } else { + } + return $ret; + } + else + { @@ -201 +138 @@ - public function commit($log = '') + public function commit($log = '') @@ -204 +141,2 @@ - if ($this->transaction_opened <= 1) { + if ($this->transaction_opened <= 1) + { @@ -206 +144,2 @@ - if ($ret) { + if ($ret) + { @@ -210 +149,3 @@ - } else { + } + else + { @@ -213 +154,3 @@ - } else { + } + else + { @@ -225 +168 @@ - public function rollback($log = '') + public function rollback($log = '') @@ -228 +171,2 @@ - if ($this->transaction_opened <= 1) { + if ($this->transaction_opened <= 1) + { @@ -233 +177,3 @@ - } else { + } + else + { @@ -246 +192 @@ - public function plimit($limit = 0, $offset = 0) + public function plimit($limit = 0, $offset = 0) @@ -249,11 +195,4 @@ - if (empty($limit)) { - return ""; - } - if ($limit < 0) { - $limit = $conf->liste_limit; - } - if ($offset > 0) { - return " LIMIT ".((int) $offset).",".((int) $limit)." "; - } else { - return " LIMIT ".((int) $limit)." "; - } + if (empty($limit)) return ""; + if ($limit < 0) $limit = $conf->liste_limit; + if ($offset > 0) return " LIMIT $offset,$limit "; + else return " LIMIT $limit "; @@ -267 +206 @@ - public function getVersionArray() + public function getVersionArray() @@ -277 +216 @@ - public function lastquery() + public function lastquery() @@ -286 +225 @@ - * @param string $sortorder Sort order, separated by comma. Example: 'ASC,DESC'. Note: If the quantity fo sortorder values is lower than sortfield, we used the last value for missing values. + * @param string $sortorder Sort order, separated by comma. Example: 'ASC,DESC'; @@ -289,4 +228,4 @@ - public function order($sortfield = '', $sortorder = '') - { - if (!empty($sortfield)) { - $oldsortorder = ''; + public function order($sortfield = null, $sortorder = null) + { + if (!empty($sortfield)) + { @@ -297,10 +236,8 @@ - foreach ($fields as $val) { - if (!$return) { - $return .= ' ORDER BY '; - } else { - $return .= ', '; - } - - $return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); // Add field - - $tmpsortorder = (empty($orders[$i]) ? '' : trim($orders[$i])); + foreach ($fields as $val) + { + if (!$return) $return .= ' ORDER BY '; + else $return .= ', '; + + $return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); + + $tmpsortorder = trim($orders[$i]); @@ -310 +246,0 @@ - $oldsortorder = 'ASC'; @@ -313 +248,0 @@ - $oldsortorder = 'DESC'; @@ -315,2 +249,0 @@ - } else { - $return .= ' '.($oldsortorder ? $oldsortorder : 'ASC'); @@ -322 +255,3 @@ - } else { + } + else + { @@ -332 +267 @@ - public function lasterror() + public function lasterror() @@ -339,2 +274,2 @@ - * 19700101020000 -> 3600 with server TZ = +1 and $gm='tzserver' - * 19700101020000 -> 7200 whaterver is server TZ if $gm='gmt' + * 19700101020000 -> 3600 with TZ+1 and gmt=0 + * 19700101020000 -> 7200 whaterver is TZ if gmt=1 @@ -343 +278 @@ - * @param mixed $gm 'gmt'=Input informations are GMT values, 'tzserver'=Local to server TZ + * @param bool $gm 1=Input informations are GMT values, otherwise local to server TZ @@ -346,6 +281,4 @@ - public function jdate($string, $gm = 'tzserver') - { - // TODO $string should be converted into a GMT timestamp, so param gm should be set to true by default instead of false - if ($string == 0 || $string == "0000-00-00 00:00:00") { - return ''; - } + public function jdate($string, $gm = false) + { + // TODO GMT must set param gm to true by default + if ($string == 0 || $string == "0000-00-00 00:00:00") return ''; @@ -363 +296 @@ - public function lastqueryerror() + public function lastqueryerror() @@ -372,3 +305,2 @@ - * - * @param string $sql The sql query string - * @return bool|int|object False on failure, 0 on empty, object on success + * @param string $sql the sql query string + * @return bool| object @@ -378 +310 @@ - $sql .= ' LIMIT 1'; + $sql .= ' LIMIT 1;'; @@ -381,7 +313,3 @@ - if ($res) { - $obj = $this->fetch_object($res); - if ($obj) { - return $obj; - } else { - return 0; - } + if ($res) + { + return $this->fetch_object($res); @@ -394 +322 @@ - * Return all results from query as an array of objects + * return all results from query as an array of objects @@ -396,5 +324,3 @@ - * be carefull with this method use it only with some limit of results to avoid performences loss. - * - * @param string $sql The sql query string - * @return bool|array Result - * @deprecated + * be carefull with this method use it only with some limit of results to avoid performences loss + * @param string $sql the sql query string + * @return bool| array @@ -405 +331,2 @@ - if ($res) { + if ($res) + { @@ -407,2 +334,2 @@ - if ($this->num_rows($res) > 0) { - while ($obj = $this->fetch_object($res)) { + if($this->num_rows($res) > 0){ + while ($obj = $this->fetch_object($res)){ --- /tmp/dsg/dolibarr/htdocs/core/db/github_19.0.3_mysqli.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_mysqli.class.php @@ -37,8 +37,6 @@ - //! Database type - public $type = 'mysqli'; - - //! Database label - const LABEL = 'MySQL or MariaDB'; - //! Version min database - const VERSIONMIN = '5.0.3'; - + //! Database type + public $type = 'mysqli'; + //! Database label + const LABEL = 'MySQL or MariaDB'; + //! Version min database + const VERSIONMIN = '5.0.3'; @@ -48 +46 @@ - /** + /** @@ -52 +50 @@ - * @param string $type Type of database (mysql, pgsql...). Not used. + * @param string $type Type of database (mysql, pgsql...) @@ -54,3 +52,3 @@ - * @param string $user Name of database user - * @param string $pass Password of database user - * @param string $name Name of database + * @param string $user Nom de l'utilisateur autorise + * @param string $pass Mot de passe + * @param string $name Nom de la database @@ -58,34 +56,32 @@ - */ - public function __construct($type, $host, $user, $pass, $name = '', $port = 0) - { - global $conf, $langs; - - // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static - if (!empty($conf->db->character_set)) { - $this->forcecharset = $conf->db->character_set; - } - if (!empty($conf->db->dolibarr_main_db_collation)) { - $this->forcecollate = $conf->db->dolibarr_main_db_collation; - } - - $this->database_user = $user; - $this->database_host = $host; - $this->database_port = $port; - - $this->transaction_opened = 0; - - //print "Name DB: $host,$user,$pass,$name
"; - - if (!class_exists('mysqli')) { - $this->connected = false; - $this->ok = false; - $this->error = "Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver."; - dol_syslog(get_class($this)."::DoliDBMysqli : Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.", LOG_ERR); - } - - if (!$host) { - $this->connected = false; - $this->ok = false; - $this->error = $langs->trans("ErrorWrongHostParameter"); - dol_syslog(get_class($this)."::DoliDBMysqli : Connect error, wrong host parameters", LOG_ERR); - } + */ + public function __construct($type, $host, $user, $pass, $name = '', $port = 0) + { + global $conf, $langs; + + // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static + if (!empty($conf->db->character_set)) $this->forcecharset = $conf->db->character_set; + if (!empty($conf->db->dolibarr_main_db_collation)) $this->forcecollate = $conf->db->dolibarr_main_db_collation; + + $this->database_user = $user; + $this->database_host = $host; + $this->database_port = $port; + + $this->transaction_opened = 0; + + //print "Name DB: $host,$user,$pass,$name
"; + + if (!class_exists('mysqli')) + { + $this->connected = false; + $this->ok = false; + $this->error = "Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver."; + dol_syslog(get_class($this)."::DoliDBMysqli : Mysqli PHP functions for using Mysqli driver are not available in this version of PHP. Try to use another driver.", LOG_ERR); + } + + if (!$host) + { + $this->connected = false; + $this->ok = false; + $this->error = $langs->trans("ErrorWrongHostParameter"); + dol_syslog(get_class($this)."::DoliDBMysqli : Connect error, wrong host parameters", LOG_ERR); + } @@ -97 +93,6 @@ - if ($this->db && empty($this->db->connect_errno)) { + if ($this->db->connect_errno) { + $this->connected = false; + $this->ok = false; + $this->error = $this->db->connect_error; + dol_syslog(get_class($this)."::DoliDBMysqli Connect error: ".$this->error, LOG_ERR); + } else { @@ -100,5 +100,0 @@ - } else { - $this->connected = false; - $this->ok = false; - $this->error = empty($this->db) ? 'Failed to connect' : $this->db->connect_error; - dol_syslog(get_class($this)."::DoliDBMysqli Connect error: ".$this->error, LOG_ERR); @@ -108,10 +104,19 @@ - if ($this->connected && $name) { - if ($this->select_db($name)) { - $this->database_selected = true; - $this->database_name = $name; - $this->ok = true; - - // If client is old latin, we force utf8 - $clientmustbe = empty($conf->db->character_set) ? 'utf8' : $conf->db->character_set; - if (preg_match('/latin1/', $clientmustbe)) { - $clientmustbe = 'utf8'; + if ($this->connected && $name) + { + if ($this->select_db($name)) + { + $this->database_selected = true; + $this->database_name = $name; + $this->ok = true; + + // If client is old latin, we force utf8 + $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set; + if (preg_match('/latin1/', $clientmustbe)) $clientmustbe = 'utf8'; + + if ($this->db->character_set_name() != $clientmustbe) { + $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation + + $collation = $conf->db->dolibarr_main_db_collation; + if (preg_match('/latin1/', $collation)) $collation = 'utf8_unicode_ci'; + + if (!preg_match('/general/', $collation)) $this->db->query("SET collation_connection = ".$collation); @@ -119,61 +124,30 @@ - - $disableforcecharset = 0; // Set to 1 to test without charset forcing - if (empty($disableforcecharset) && $this->db->character_set_name() != $clientmustbe) { - try { - //print "You should set the \$dolibarr_main_db_character_set and \$dolibarr_main_db_collation for the PHP to the one of the database ".$this->db->character_set_name(); - dol_syslog(get_class($this)."::DoliDBMysqli You should set the \$dolibarr_main_db_character_set and \$dolibarr_main_db_collation for the PHP to the one of the database ".$this->db->character_set_name(), LOG_WARNING); - $this->db->set_charset($clientmustbe); // This set charset, but with a bad collation - } catch (Exception $e) { - print 'Failed to force character set to '.$clientmustbe." according to setup to match the one of the server database.
\n"; - print $e->getMessage(); - print "
\n"; - if ($clientmustbe != 'utf8') { - print 'Edit conf/conf.php file to set a charset "utf8" instead of "'.$clientmustbe.'".'."\n"; - } - exit; - } - - $collation = (empty($conf) ? 'utf8_unicode_ci' : $conf->db->dolibarr_main_db_collation); - if (preg_match('/latin1/', $collation)) { - $collation = 'utf8_unicode_ci'; - } - - if (!preg_match('/general/', $collation)) { - $this->db->query("SET collation_connection = ".$collation); - } - } - } else { - $this->database_selected = false; - $this->database_name = ''; - $this->ok = false; - $this->error = $this->error(); - dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR); - } - } else { - // No selection of database done. We may only be connected or not (ok or ko) to the server. - $this->database_selected = false; - - if ($this->connected) { - // If client is old latin, we force utf8 - $clientmustbe = empty($conf->db->character_set) ? 'utf8' : $conf->db->character_set; - if (preg_match('/latin1/', $clientmustbe)) { - $clientmustbe = 'utf8'; - } - if (preg_match('/utf8mb4/', $clientmustbe)) { - $clientmustbe = 'utf8'; - } - - if ($this->db->character_set_name() != $clientmustbe) { - $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci - - $collation = $conf->db->dolibarr_main_db_collation; - if (preg_match('/latin1/', $collation)) { - $collation = 'utf8_unicode_ci'; - } - if (preg_match('/utf8mb4/', $collation)) { - $collation = 'utf8_unicode_ci'; - } - - if (!preg_match('/general/', $collation)) { - $this->db->query("SET collation_connection = ".$collation); - } + } + else + { + $this->database_selected = false; + $this->database_name = ''; + $this->ok = false; + $this->error = $this->error(); + dol_syslog(get_class($this)."::DoliDBMysqli : Select_db error ".$this->error, LOG_ERR); + } + } + else + { + // Pas de selection de base demandee, ok ou ko + $this->database_selected = false; + + if ($this->connected) + { + // If client is old latin, we force utf8 + $clientmustbe = empty($conf->db->dolibarr_main_db_character_set) ? 'utf8' : $conf->db->dolibarr_main_db_character_set; + if (preg_match('/latin1/', $clientmustbe)) $clientmustbe = 'utf8'; + if (preg_match('/utf8mb4/', $clientmustbe)) $clientmustbe = 'utf8'; + + if ($this->db->character_set_name() != $clientmustbe) { + $this->db->set_charset($clientmustbe); // This set utf8_unicode_ci + + $collation = $conf->db->dolibarr_main_db_collation; + if (preg_match('/latin1/', $collation)) $collation = 'utf8_unicode_ci'; + if (preg_match('/utf8mb4/', $collation)) $collation = 'utf8_unicode_ci'; + + if (!preg_match('/general/', $collation)) $this->db->query("SET collation_connection = ".$collation); @@ -182,30 +156,17 @@ - } - } - - - /** - * Return SQL string to force an index - * - * @param string $nameofindex Name of index - * @return string SQL string - */ - public function hintindex($nameofindex) - { - return " FORCE INDEX(".preg_replace('/[^a-z0-9_]/', '', $nameofindex).")"; - } - - - /** - * Convert a SQL request in Mysql syntax to native syntax - * - * @param string $line SQL request line to convert - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @return string SQL request line converted - */ - public function convertSQLFromMysql($line, $type = 'ddl') - { - return $line; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + } + } + + + /** + * Convert a SQL request in Mysql syntax to native syntax + * + * @param string $line SQL request line to convert + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return string SQL request line converted + */ + public static function convertSQLFromMysql($line, $type = 'ddl') + { + return $line; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -218,12 +179,6 @@ - public function select_db($database) - { - // phpcs:enable - dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); - $result = false; - try { - $result = $this->db->select_db($database); - } catch (Exception $e) { - // Nothing done on error - } - return $result; - } + public function select_db($database) + { + // phpcs:enable + dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); + return $this->db->select_db($database); + } @@ -240 +195 @@ - * @return mysqli|null Database access object + * @return mysqli Database access object @@ -243,2 +198,2 @@ - public function connect($host, $login, $passwd, $name, $port = 0) - { + public function connect($host, $login, $passwd, $name, $port = 0) + { @@ -247,19 +202,7 @@ - //mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); - - $tmp = false; - try { - if (!class_exists('mysqli')) { - dol_print_error('', 'Driver mysqli for PHP not available'); - } - if (strpos($host, 'ssl://') === 0) { - $tmp = new mysqliDoli($host, $login, $passwd, $name, $port); - } else { - $tmp = new mysqli($host, $login, $passwd, $name, $port); - } - } catch (Exception $e) { - dol_syslog(get_class($this)."::connect failed", LOG_DEBUG); - } - return $tmp; - } - - /** + // Can also be + // mysqli::init(); mysql::options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0'); mysqli::options(MYSQLI_OPT_CONNECT_TIMEOUT, 5); + // return mysqli::real_connect($host, $user, $pass, $db, $port); + return new mysqli($host, $login, $passwd, $name, $port); + } + + /** @@ -269,116 +212,98 @@ - */ - public function getVersion() - { - return $this->db->server_info; - } - - /** - * Return version of database client driver - * - * @return string Version string - */ - public function getDriverInfo() - { - return $this->db->client_info; - } - - - /** - * Close database connexion - * - * @return bool True if disconnect successfull, false otherwise - * @see connect() - */ - public function close() - { - if ($this->db) { - if ($this->transaction_opened > 0) { - dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); - } - $this->connected = false; - return $this->db->close(); - } - return false; - } - - - - /** - * Execute a SQL request and return the resultset - * - * @param string $query SQL query string - * @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). - * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @param int $result_mode Result mode (Using 1=MYSQLI_USE_RESULT instead of 0=MYSQLI_STORE_RESULT will not buffer the result and save memory) - * @return bool|mysqli_result Resultset of answer - */ - public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0) - { - global $conf, $dolibarr_main_db_readonly; - - $query = trim($query); - - if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) { - $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks - dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG); - } - if (empty($query)) { - return false; // Return false = error if empty request - } - - if (!empty($dolibarr_main_db_readonly)) { - if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) { - $this->lasterror = 'Application in read-only mode'; - $this->lasterrno = 'APPREADONLY'; - $this->lastquery = $query; - return false; - } - } - - try { - if (!$this->database_name) { - // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE) - $ret = $this->db->query($query, $result_mode); - } else { - $ret = $this->db->query($query, $result_mode); - } - } catch (Exception $e) { - dol_syslog(get_class($this)."::query Exception in query instead of returning an error: ".$e->getMessage(), LOG_ERR); - $ret = false; - } - - if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) { - // Si requete utilisateur, on la sauvegarde ainsi que son resultset - if (!$ret) { - $this->lastqueryerror = $query; - $this->lasterror = $this->error(); - $this->lasterrno = $this->errno(); - - if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) { - dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously - } - dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror, LOG_ERR); - //var_dump(debug_print_backtrace()); - } - $this->lastquery = $query; - $this->_results = $ret; - } - - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Returns the current line (as an object) for the resultset cursor - * - * @param mysqli_result $resultset Curseur de la requete voulue - * @return object|null Object result line or null if KO or end of cursor - */ - public function fetch_object($resultset) - { - // phpcs:enable - // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } + */ + public function getVersion() + { + return $this->db->server_info; + } + + /** + * Return version of database client driver + * + * @return string Version string + */ + public function getDriverInfo() + { + return $this->db->client_info; + } + + + /** + * Close database connexion + * + * @return bool True if disconnect successfull, false otherwise + * @see connect() + */ + public function close() + { + if ($this->db) + { + if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); + $this->connected = false; + return $this->db->close(); + } + return false; + } + + /** + * Execute a SQL request and return the resultset + * + * @param string $query SQL query string + * @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). + * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return bool|mysqli_result Resultset of answer + */ + public function query($query, $usesavepoint = 0, $type = 'auto') + { + global $conf; + + $query = trim($query); + + if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) + { + $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks + dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG); + } + if (empty($query)) return false; // Return false = error if empty request + + if (!$this->database_name) + { + // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE) + $ret = $this->db->query($query); + } + else + { + $ret = $this->db->query($query); + } + + if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) + { + // Si requete utilisateur, on la sauvegarde ainsi que son resultset + if (!$ret) + { + $this->lastqueryerror = $query; + $this->lasterror = $this->error(); + $this->lasterrno = $this->errno(); + + if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously + dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterrno." ".$this->lasterror, LOG_ERR); + //var_dump(debug_print_backtrace()); + } + $this->lastquery = $query; + $this->_results = $ret; + } + + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Renvoie la ligne courante (comme un objet) pour le curseur resultset + * + * @param mysqli_result $resultset Curseur de la requete voulue + * @return object|null Object result line or null if KO or end of cursor + */ + public function fetch_object($resultset) + { + // phpcs:enable + // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion + if (!is_object($resultset)) { $resultset = $this->_results; } @@ -386,174 +311,153 @@ - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param mysqli_result $resultset Resultset of request - * @return array|null Array or null if KO or end of cursor - */ - public function fetch_array($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - return $resultset->fetch_array(); - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param mysqli_result $resultset Resultset of request - * @return array|null|int Array or null if KO or end of cursor or 0 if resultset is bool - */ - public function fetch_row($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_bool($resultset)) { - if (!is_object($resultset)) { - $resultset = $this->_results; - } - return $resultset->fetch_row(); - } else { - // si le curseur est un booleen on retourne la valeur 0 - return 0; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return number of lines for result of a SELECT - * - * @param mysqli_result $resultset Resulset of requests - * @return int Nb of lines - * @see affected_rows() - */ - public function num_rows($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - return isset($resultset->num_rows) ? $resultset->num_rows : 0; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return the number of lines in the result of a request INSERT, DELETE or UPDATE - * - * @param mysqli_result $resultset Curseur de la requete voulue - * @return int Number of lines - * @see num_rows() - */ - public function affected_rows($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - // mysql necessite un link de base pour cette fonction contrairement - // a pqsql qui prend un resultset - return $this->db->affected_rows; - } - - - /** - * Libere le dernier resultset utilise sur cette connexion - * - * @param mysqli_result $resultset Curseur de la requete voulue - * @return void - */ - public function free($resultset = null) - { - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - // Si resultset en est un, on libere la memoire - if (is_object($resultset)) { - $resultset->free_result(); - } - } - - /** - * Escape a string to insert data - * - * @param string $stringtoencode String to escape - * @return string String escaped - */ - public function escape($stringtoencode) - { - return $this->db->real_escape_string((string) $stringtoencode); - } - - /** - * Escape a string to insert data into a like - * - * @param string $stringtoencode String to escape - * @return string String escaped - */ - public function escapeforlike($stringtoencode) - { - return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode); - } - - /** - * Return generic error code of last operation. - * - * @return string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) - */ - public function errno() - { - if (!$this->connected) { - // Si il y a eu echec de connexion, $this->db n'est pas valide. - return 'DB_ERROR_FAILED_TO_CONNECT'; - } else { - // Constants to convert a MySql error code to a generic Dolibarr error code - $errorcode_map = array( - 1004 => 'DB_ERROR_CANNOT_CREATE', - 1005 => 'DB_ERROR_CANNOT_CREATE', - 1006 => 'DB_ERROR_CANNOT_CREATE', - 1007 => 'DB_ERROR_ALREADY_EXISTS', - 1008 => 'DB_ERROR_CANNOT_DROP', - 1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', - 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP', - 1044 => 'DB_ERROR_ACCESSDENIED', - 1046 => 'DB_ERROR_NODBSELECTED', - 1048 => 'DB_ERROR_CONSTRAINT', - 1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS', - 1051 => 'DB_ERROR_NOSUCHTABLE', - 1054 => 'DB_ERROR_NOSUCHFIELD', - 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', - 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', - 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS', - 1064 => 'DB_ERROR_SYNTAX', - 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS', - 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY', - 1091 => 'DB_ERROR_NOSUCHFIELD', - 1100 => 'DB_ERROR_NOT_LOCKED', - 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW', - 1146 => 'DB_ERROR_NOSUCHTABLE', - 1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT', - 1216 => 'DB_ERROR_NO_PARENT', - 1217 => 'DB_ERROR_CHILD_EXISTS', - 1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating a user that already existing - 1451 => 'DB_ERROR_CHILD_EXISTS', - 1826 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS' - ); - - if (isset($errorcode_map[$this->db->errno])) { - return $errorcode_map[$this->db->errno]; - } - $errno = $this->db->errno; - return ($errno ? 'DB_ERROR_'.$errno : '0'); - } - } - - /** + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param mysqli_result $resultset Resultset of request + * @return array|null Array or null if KO or end of cursor + */ + public function fetch_array($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + return $resultset->fetch_array(); + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param mysqli_result $resultset Resultset of request + * @return array|null|int Array or null if KO or end of cursor or 0 if resultset is bool + */ + public function fetch_row($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_bool($resultset)) + { + if (!is_object($resultset)) { $resultset = $this->_results; } + return $resultset->fetch_row(); + } + else + { + // si le curseur est un booleen on retourne la valeur 0 + return 0; + } + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return number of lines for result of a SELECT + * + * @param mysqli_result $resultset Resulset of requests + * @return int Nb of lines + * @see affected_rows() + */ + public function num_rows($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + return $resultset->num_rows; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return the number of lines in the result of a request INSERT, DELETE or UPDATE + * + * @param mysqli_result $resultset Curseur de la requete voulue + * @return int Number of lines + * @see num_rows() + */ + public function affected_rows($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + // mysql necessite un link de base pour cette fonction contrairement + // a pqsql qui prend un resultset + return $this->db->affected_rows; + } + + + /** + * Libere le dernier resultset utilise sur cette connexion + * + * @param mysqli_result $resultset Curseur de la requete voulue + * @return void + */ + public function free($resultset = null) + { + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + // Si resultset en est un, on libere la memoire + if (is_object($resultset)) $resultset->free_result(); + } + + /** + * Escape a string to insert data + * + * @param string $stringtoencode String to escape + * @return string String escaped + */ + public function escape($stringtoencode) + { + return $this->db->real_escape_string($stringtoencode); + } + + /** + * Return generic error code of last operation. + * + * @return string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) + */ + public function errno() + { + if (!$this->connected) { + // Si il y a eu echec de connexion, $this->db n'est pas valide. + return 'DB_ERROR_FAILED_TO_CONNECT'; + } else { + // Constants to convert a MySql error code to a generic Dolibarr error code + $errorcode_map = array( + 1004 => 'DB_ERROR_CANNOT_CREATE', + 1005 => 'DB_ERROR_CANNOT_CREATE', + 1006 => 'DB_ERROR_CANNOT_CREATE', + 1007 => 'DB_ERROR_ALREADY_EXISTS', + 1008 => 'DB_ERROR_CANNOT_DROP', + 1022 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', + 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP', + 1044 => 'DB_ERROR_ACCESSDENIED', + 1046 => 'DB_ERROR_NODBSELECTED', + 1048 => 'DB_ERROR_CONSTRAINT', + 1050 => 'DB_ERROR_TABLE_ALREADY_EXISTS', + 1051 => 'DB_ERROR_NOSUCHTABLE', + 1054 => 'DB_ERROR_NOSUCHFIELD', + 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', + 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', + 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS', + 1064 => 'DB_ERROR_SYNTAX', + 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS', + 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY', + 1091 => 'DB_ERROR_NOSUCHFIELD', + 1100 => 'DB_ERROR_NOT_LOCKED', + 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW', + 1146 => 'DB_ERROR_NOSUCHTABLE', + 1215 => 'DB_ERROR_CANNOT_ADD_FOREIGN_KEY_CONSTRAINT', + 1216 => 'DB_ERROR_NO_PARENT', + 1217 => 'DB_ERROR_CHILD_EXISTS', + 1396 => 'DB_ERROR_USER_ALREADY_EXISTS', // When creating user already existing + 1451 => 'DB_ERROR_CHILD_EXISTS' + ); + + if (isset($errorcode_map[$this->db->errno])) { + return $errorcode_map[$this->db->errno]; + } + $errno = $this->db->errno; + return ($errno ? 'DB_ERROR_'.$errno : '0'); + } + } + + /** @@ -563,13 +467,14 @@ - */ - public function error() - { - if (!$this->connected) { - // Si il y a eu echec de connexion, $this->db n'est pas valide pour mysqli_error. - return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions'; - } else { - return $this->db->error; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function error() + { + if (!$this->connected) { + // Si il y a eu echec de connexion, $this->db n'est pas valide pour mysqli_error. + return 'Not connected. Check setup parameters in conf/conf.php file and your mysql client and server versions'; + } + else { + return $this->db->error; + } + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -581,70 +486,79 @@ - */ - public function last_insert_id($tab, $fieldid = 'rowid') - { - // phpcs:enable - return $this->db->insert_id; - } - - /** - * Encrypt sensitive data in database - * Warning: This function includes the escape and add the SQL simple quotes on strings. - * - * @param string $fieldorvalue Field name or value to encrypt - * @param int $withQuotes Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated). - * @return string XXX(field) or XXX('value') or field or 'value' - */ - public function encrypt($fieldorvalue, $withQuotes = 1) - { - global $conf; - - // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) - $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0); - - //Encryption key - $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); - - $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : ""); - - if ($cryptType && !empty($cryptKey)) { - if ($cryptType == 2) { - $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')"; - } elseif ($cryptType == 1) { - $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')"; - } - } - - return $escapedstringwithquotes; - } - - /** - * Decrypt sensitive data in database - * - * @param string $value Value to decrypt - * @return string Decrypted value if used - */ - public function decrypt($value) - { - global $conf; - - // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) - $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0); - - //Encryption key - $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); - - $return = $value; - - if ($cryptType && !empty($cryptKey)) { - if ($cryptType == 2) { - $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')'; - } elseif ($cryptType == 1) { - $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')'; - } - } - - return $return; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function last_insert_id($tab, $fieldid = 'rowid') + { + // phpcs:enable + return $this->db->insert_id; + } + + /** + * Encrypt sensitive data in database + * Warning: This function includes the escape, so it must use direct value + * + * @param string $fieldorvalue Field name or value to encrypt + * @param int $withQuotes Return string with quotes + * @return string XXX(field) or XXX('value') or field or 'value' + * + */ + public function encrypt($fieldorvalue, $withQuotes = 0) + { + global $conf; + + // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) + $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0); + + //Encryption key + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + + $return = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : ""); + + if ($cryptType && !empty($cryptKey)) + { + if ($cryptType == 2) + { + $return = 'AES_ENCRYPT('.$return.',\''.$cryptKey.'\')'; + } + elseif ($cryptType == 1) + { + $return = 'DES_ENCRYPT('.$return.',\''.$cryptKey.'\')'; + } + } + + return $return; + } + + /** + * Decrypt sensitive data in database + * + * @param string $value Value to decrypt + * @return string Decrypted value if used + */ + public function decrypt($value) + { + global $conf; + + // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) + $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0); + + //Encryption key + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + + $return = $value; + + if ($cryptType && !empty($cryptKey)) + { + if ($cryptType == 2) + { + $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')'; + } + elseif ($cryptType == 1) + { + $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')'; + } + } + + return $return; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -654,16 +568,16 @@ - */ - public function DDLGetConnectId() - { - // phpcs:enable - $resql = $this->query('SELECT CONNECTION_ID()'); - if ($resql) { - $row = $this->fetch_row($resql); - return $row[0]; - } else { - return '?'; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Create a new database + */ + public function DDLGetConnectId() + { + // phpcs:enable + $resql = $this->query('SELECT CONNECTION_ID()'); + if ($resql) + { + $row = $this->fetch_row($resql); + return $row[0]; + } + else return '?'; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Create a new database @@ -678,12 +592,8 @@ - */ - public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') - { - // phpcs:enable - if (empty($charset)) { - $charset = $this->forcecharset; - } - if (empty($collation)) { - $collation = $this->forcecollate; - } - - // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci + */ + public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') + { + // phpcs:enable + if (empty($charset)) $charset = $this->forcecharset; + if (empty($collation)) $collation = $this->forcecollate; + + // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci @@ -693,13 +603,14 @@ - dol_syslog($sql, LOG_DEBUG); - $ret = $this->query($sql); - if (!$ret) { - // We try again for compatibility with Mysql < 4.1.1 - $sql = "CREATE DATABASE `".$this->escape($database)."`"; - dol_syslog($sql, LOG_DEBUG); - $ret = $this->query($sql); - } - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + dol_syslog($sql, LOG_DEBUG); + $ret = $this->query($sql); + if (!$ret) + { + // We try again for compatibility with Mysql < 4.1.1 + $sql = "CREATE DATABASE `".$this->escape($database)."`"; + dol_syslog($sql, LOG_DEBUG); + $ret = $this->query($sql); + } + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -711,59 +622,23 @@ - */ - public function DDLListTables($database, $table = '') - { - // phpcs:enable - $listtables = array(); - - $like = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $like = "LIKE '".$this->escape($tmptable)."'"; - } - $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database); - - $sql = "SHOW TABLES FROM `".$tmpdatabase."` ".$like.";"; - //print $sql; - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row[0]; - } - } - return $listtables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List tables into a database - * - * @param string $database Name of database - * @param string $table Nmae of table filter ('xxx%') - * @return array List of tables in an array - */ - public function DDLListTablesFull($database, $table = '') - { - // phpcs:enable - $listtables = array(); - - $like = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $like = "LIKE '".$this->escape($tmptable)."'"; - } - $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database); - - $sql = "SHOW FULL TABLES FROM `".$tmpdatabase."` ".$like.";"; - - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row; - } - } - return $listtables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLListTables($database, $table = '') + { + // phpcs:enable + $listtables = array(); + + $like = ''; + if ($table) $like = "LIKE '".$table."'"; + $sql = "SHOW TABLES FROM ".$database." ".$like.";"; + //print $sql; + $result = $this->query($sql); + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $listtables[] = $row[0]; + } + } + return $listtables; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -774,22 +649,22 @@ - */ - public function DDLInfoTable($table) - { - // phpcs:enable - $infotables = array(); - - $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); - - $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";"; - - dol_syslog($sql, LOG_DEBUG); - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $infotables[] = $row; - } - } - return $infotables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLInfoTable($table) + { + // phpcs:enable + $infotables = array(); + + $sql = "SHOW FULL COLUMNS FROM ".$table.";"; + + dol_syslog($sql, LOG_DEBUG); + $result = $this->query($sql); + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $infotables[] = $row; + } + } + return $infotables; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -805,17 +680,15 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) - { - // phpcs:enable - // FIXME: $fulltext_keys parameter is unused - - $pk = ''; - $sqluq = $sqlk = array(); - - // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra - // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); - $sql = "CREATE TABLE ".$table."("; - $i = 0; - $sqlfields = array(); - foreach ($fields as $field_name => $field_desc) { - $sqlfields[$i] = $field_name." "; + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) + { + // phpcs:enable + // FIXME: $fulltext_keys parameter is unused + + // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra + // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); + $sql = "CREATE TABLE ".$table."("; + $i = 0; + $sqlfields = array(); + foreach ($fields as $field_name => $field_desc) + { + $sqlfields[$i] = $field_name." "; @@ -829 +702,2 @@ - if (preg_match("/^[^\s]/i", $field_desc['default'])) { + if (preg_match("/^[^\s]/i", $field_desc['default'])) + { @@ -832,2 +706,3 @@ - } else { - $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'"; + } + else { + $sqlfields[$i] .= " default '".$field_desc['default']."'"; @@ -842,62 +717,57 @@ - $i++; - } - if ($primary_key != "") { - $pk = "primary key(".$primary_key.")"; - } - - if (is_array($unique_keys)) { - $i = 0; - foreach ($unique_keys as $key => $value) { - $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')"; - $i++; - } - } - if (is_array($keys)) { - $i = 0; - foreach ($keys as $key => $value) { - $sqlk[$i] = "KEY ".$key." (".$value.")"; - $i++; - } - } - $sql .= implode(',', $sqlfields); - if ($primary_key != "") { - $sql .= ",".$pk; - } - if ($unique_keys != "") { - $sql .= ",".implode(',', $sqluq); - } - if (is_array($keys)) { - $sql .= ",".implode(',', $sqlk); - } - $sql .= ") engine=".$type; - - if (!$this->query($sql)) { - return -1; - } else { - return 1; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Drop a table into database - * - * @param string $table Name of table - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLDropTable($table) - { - // phpcs:enable - $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); - - $sql = "DROP TABLE ".$tmptable; - - if (!$this->query($sql)) { - return -1; - } else { - return 1; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + $i++; + } + if ($primary_key != "") + $pk = "primary key(".$primary_key.")"; + + if (is_array($unique_keys)) { + $i = 0; + foreach ($unique_keys as $key => $value) + { + $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')"; + $i++; + } + } + if (is_array($keys)) + { + $i = 0; + foreach ($keys as $key => $value) + { + $sqlk[$i] = "KEY ".$key." (".$value.")"; + $i++; + } + } + $sql .= implode(',', $sqlfields); + if ($primary_key != "") + $sql .= ",".$pk; + if ($unique_keys != "") + $sql .= ",".implode(',', $sqluq); + if (is_array($keys)) + $sql .= ",".implode(',', $sqlk); + $sql .= ") engine=".$type; + + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Drop a table into database + * + * @param string $table Name of table + * @return int <0 if KO, >=0 if OK + */ + public function DDLDropTable($table) + { + // phpcs:enable + $sql = "DROP TABLE ".$table; + + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -909,13 +779,13 @@ - */ - public function DDLDescTable($table, $field = "") - { - // phpcs:enable - $sql = "DESC ".$table." ".$field; - - dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG); - $this->_results = $this->query($sql); - return $this->_results; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLDescTable($table, $field = "") + { + // phpcs:enable + $sql = "DESC ".$table." ".$field; + + dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG); + $this->_results = $this->query($sql); + return $this->_results; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -928,41 +798,46 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLAddField($table, $field_name, $field_desc, $field_position = "") - { - // phpcs:enable - // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra - // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); - $sql = "ALTER TABLE ".$table." ADD ".$field_name." "; - $sql .= $field_desc['type']; - if (preg_match("/^[^\s]/i", $field_desc['value'])) { - if (!in_array($field_desc['type'], array('date', 'datetime')) && $field_desc['value']) { - $sql .= "(".$field_desc['value'].")"; - } - } - if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) { - $sql .= " ".$field_desc['attribute']; - } - if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) { - $sql .= " ".$field_desc['null']; - } - if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) { - if (preg_match("/null/i", $field_desc['default'])) { - $sql .= " default ".$field_desc['default']; - } else { - $sql .= " default '".$this->escape($field_desc['default'])."'"; - } - } - if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) { - $sql .= " ".$field_desc['extra']; - } - $sql .= " ".$field_position; - - dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG); - if ($this->query($sql)) { - return 1; - } - return -1; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >0 if OK + */ + public function DDLAddField($table, $field_name, $field_desc, $field_position = "") + { + // phpcs:enable + // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra + // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); + $sql = "ALTER TABLE ".$table." ADD ".$field_name." "; + $sql .= $field_desc['type']; + if (preg_match("/^[^\s]/i", $field_desc['value'])) + { + if (!in_array($field_desc['type'], array('date', 'datetime')) && $field_desc['value']) + { + $sql .= "(".$field_desc['value'].")"; + } + } + if (isset($field_desc['attribute']) && preg_match("/^[^\s]/i", $field_desc['attribute'])) + { + $sql .= " ".$field_desc['attribute']; + } + if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) + { + $sql .= " ".$field_desc['null']; + } + if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) + { + if (preg_match("/null/i", $field_desc['default'])) + $sql .= " default ".$field_desc['default']; + else + $sql .= " default '".$field_desc['default']."'"; + } + if (isset($field_desc['extra']) && preg_match("/^[^\s]/i", $field_desc['extra'])) + { + $sql .= " ".$field_desc['extra']; + } + $sql .= " ".$field_position; + + dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG); + if ($this->query($sql)) { + return 1; + } + return -1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -974,41 +849,42 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLUpdateField($table, $field_name, $field_desc) - { - // phpcs:enable - $sql = "ALTER TABLE ".$table; - $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type']; - if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) { - $sql .= "(".$field_desc['value'].")"; - } - if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') { - // 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 - if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') { - $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape(isset($field_desc['default']) ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL"; - $this->query($sqlbis); - } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') { - $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape(isset($field_desc['default']) ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL"; - $this->query($sqlbis); - } - - $sql .= " NOT NULL"; - } - - if (isset($field_desc['default']) && $field_desc['default'] != '') { - if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') { - $sql .= " DEFAULT ".$this->escape($field_desc['default']); - } elseif ($field_desc['type'] != 'text') { - $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields - } - } - - dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG); - if (!$this->query($sql)) { - return -1; - } else { - return 1; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >0 if OK + */ + public function DDLUpdateField($table, $field_name, $field_desc) + { + // phpcs:enable + $sql = "ALTER TABLE ".$table; + $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type']; + if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) { + $sql .= "(".$field_desc['value'].")"; + } + if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') + { + // 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 + if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') + { + $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape($field_desc['default'] ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL"; + $this->query($sqlbis); + } + elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') + { + $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape($field_desc['default'] ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL"; + $this->query($sqlbis); + } + + $sql .= " NOT NULL"; + } + + if ($field_desc['default'] != '') + { + if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') $sql .= " DEFAULT ".$this->escape($field_desc['default']); + elseif ($field_desc['type'] != 'text') $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields + } + + dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG); + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1019,18 +895,17 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLDropField($table, $field_name) - { - // phpcs:enable - $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name); - - $sql = "ALTER TABLE ".$table." DROP COLUMN `".$tmp_field_name."`"; - if ($this->query($sql)) { - return 1; - } - $this->error = $this->lasterror(); - return -1; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >0 if OK + */ + public function DDLDropField($table, $field_name) + { + // phpcs:enable + $sql = "ALTER TABLE ".$table." DROP COLUMN `".$field_name."`"; + dol_syslog(get_class($this)."::DDLDropField ".$sql, LOG_DEBUG); + if ($this->query($sql)) { + return 1; + } + $this->error = $this->lasterror(); + return -1; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1043,127 +918,139 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) - { - // phpcs:enable - $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'"; - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log - $resql = $this->query($sql); - if (!$resql) { - if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') { - return -1; - } else { - // If user already exists, we continue to set permissions - dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING); - } - } - - // Redo with localhost forced (sometimes user is created on %) - $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'"; - $resql = $this->query($sql); - - $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'"; - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log - $resql = $this->query($sql); - if (!$resql) { - $this->error = "Connected user not allowed to GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."'"; - return -1; - } - - $sql = "FLUSH Privileges"; - - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); - $resql = $this->query($sql); - if (!$resql) { - return -1; - } - - return 1; - } - - /** - * Return charset used to store data in current database - * Note: if we are connected to databasename, it is same result than using SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "databasename";) - * - * @return string Charset - * @see getDefaultCollationDatabase() - */ - public function getDefaultCharacterSetDatabase() - { - $resql = $this->query("SHOW VARIABLES LIKE 'character_set_database'"); - if (!$resql) { - // version Mysql < 4.1.1 - return $this->forcecharset; - } - $liste = $this->fetch_array($resql); - $tmpval = $liste['Value']; - - return $tmpval; - } - - /** - * Return list of available charset that can be used to store data in database - * - * @return array|null List of Charset - */ - public function getListOfCharacterSet() - { - $resql = $this->query('SHOW CHARSET'); - $liste = array(); - if ($resql) { - $i = 0; - while ($obj = $this->fetch_object($resql)) { - $liste[$i]['charset'] = $obj->Charset; - $liste[$i]['description'] = $obj->Description; - $i++; - } - $this->free($resql); - } else { - // version Mysql < 4.1.1 - return null; - } - return $liste; - } - - /** - * Return collation used in current database - * - * @return string Collation value - * @see getDefaultCharacterSetDatabase() - */ - public function getDefaultCollationDatabase() - { - $resql = $this->query("SHOW VARIABLES LIKE 'collation_database'"); - if (!$resql) { - // version Mysql < 4.1.1 - return $this->forcecollate; - } - $liste = $this->fetch_array($resql); - $tmpval = $liste['Value']; - - return $tmpval; - } - - /** - * Return list of available collation that can be used for database - * - * @return array|null Liste of Collation - */ - public function getListOfCollation() - { - $resql = $this->query('SHOW COLLATION'); - $liste = array(); - if ($resql) { - $i = 0; - while ($obj = $this->fetch_object($resql)) { - $liste[$i]['collation'] = $obj->Collation; - $i++; - } - $this->free($resql); - } else { - // version Mysql < 4.1.1 - return null; - } - return $liste; - } - - /** + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) + { + // phpcs:enable + $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'"; + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log + $resql = $this->query($sql); + if (!$resql) + { + if ($this->lasterrno != 'DB_ERROR_USER_ALREADY_EXISTS') + { + return -1; + } + else + { + // If user already exists, we continue to set permissions + dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING); + } + } + + // Redo with localhost forced (sometimes user is created on %) + $sql = "CREATE USER '".$this->escape($dolibarr_main_db_user)."'@'localhost'"; + $resql = $this->query($sql); + + $sql = "GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."' IDENTIFIED BY '".$this->escape($dolibarr_main_db_pass)."'"; + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log + $resql = $this->query($sql); + if (!$resql) + { + $this->error = "Connected user not allowed to GRANT ALL PRIVILEGES ON ".$this->escape($dolibarr_main_db_name).".* TO '".$this->escape($dolibarr_main_db_user)."'@'".$this->escape($dolibarr_main_db_host)."' IDENTIFIED BY '*****'"; + return -1; + } + + $sql = "FLUSH Privileges"; + + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); + $resql = $this->query($sql); + if (!$resql) + { + return -1; + } + + return 1; + } + + /** + * Return charset used to store data in current database + * Note: if we are connected to databasename, it is same result than using SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "databasename";) + * + * @return string Charset + * @see getDefaultCollationDatabase() + */ + public function getDefaultCharacterSetDatabase() + { + $resql = $this->query('SHOW VARIABLES LIKE \'character_set_database\''); + if (!$resql) + { + // version Mysql < 4.1.1 + return $this->forcecharset; + } + $liste = $this->fetch_array($resql); + $tmpval = $liste['Value']; + + return $tmpval; + } + + /** + * Return list of available charset that can be used to store data in database + * + * @return array|null List of Charset + */ + public function getListOfCharacterSet() + { + $resql = $this->query('SHOW CHARSET'); + $liste = array(); + if ($resql) + { + $i = 0; + while ($obj = $this->fetch_object($resql)) + { + $liste[$i]['charset'] = $obj->Charset; + $liste[$i]['description'] = $obj->Description; + $i++; + } + $this->free($resql); + } else { + // version Mysql < 4.1.1 + return null; + } + return $liste; + } + + /** + * Return collation used in current database + * + * @return string Collation value + * @see getDefaultCharacterSetDatabase() + */ + public function getDefaultCollationDatabase() + { + $resql = $this->query('SHOW VARIABLES LIKE \'collation_database\''); + if (!$resql) + { + // version Mysql < 4.1.1 + return $this->forcecollate; + } + $liste = $this->fetch_array($resql); + $tmpval = $liste['Value']; + + return $tmpval; + } + + /** + * Return list of available collation that can be used for database + * + * @return array|null Liste of Collation + */ + public function getListOfCollation() + { + $resql = $this->query('SHOW COLLATION'); + $liste = array(); + if ($resql) + { + $i = 0; + while ($obj = $this->fetch_object($resql)) + { + $liste[$i]['collation'] = $obj->Collation; + $i++; + } + $this->free($resql); + } else { + // version Mysql < 4.1.1 + return null; + } + return $liste; + } + + /** @@ -1173,36 +1060,38 @@ - */ - public function getPathOfDump() - { - $fullpathofdump = '/pathtomysqldump/mysqldump'; - - $resql = $this->query("SHOW VARIABLES LIKE 'basedir'"); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['Value']; - $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump'; - } - return $fullpathofdump; - } - - /** - * Return full path of restore program - * - * @return string Full path of restore program - */ - public function getPathOfRestore() - { - $fullpathofimport = '/pathtomysql/mysql'; - - $resql = $this->query("SHOW VARIABLES LIKE 'basedir'"); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['Value']; - $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql'; - } - return $fullpathofimport; - } - - /** - * Return value of server parameters - * - * @param string $filter Filter list on a particular value + */ + public function getPathOfDump() + { + $fullpathofdump = '/pathtomysqldump/mysqldump'; + + $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['Value']; + $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump'; + } + return $fullpathofdump; + } + + /** + * Return full path of restore program + * + * @return string Full path of restore program + */ + public function getPathOfRestore() + { + $fullpathofimport = '/pathtomysql/mysql'; + + $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['Value']; + $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql'; + } + return $fullpathofimport; + } + + /** + * Return value of server parameters + * + * @param string $filter Filter list on a particular value @@ -1210,23 +1099,20 @@ - */ - public function getServerParametersValues($filter = '') - { - $result = array(); - - $sql = 'SHOW VARIABLES'; - if ($filter) { - $sql .= " LIKE '".$this->escape($filter)."'"; - } - $resql = $this->query($sql); - if ($resql) { - while ($obj = $this->fetch_object($resql)) { - $result[$obj->Variable_name] = $obj->Value; - } - } - - return $result; - } - - /** - * Return value of server status (current indicators on memory, cache...) - * - * @param string $filter Filter list on a particular value + */ + public function getServerParametersValues($filter = '') + { + $result = array(); + + $sql = 'SHOW VARIABLES'; + if ($filter) $sql .= " LIKE '".$this->escape($filter)."'"; + $resql = $this->query($sql); + if ($resql) + { + while ($obj = $this->fetch_object($resql)) $result[$obj->Variable_name] = $obj->Value; + } + + return $result; + } + + /** + * Return value of server status (current indicators on memory, cache...) + * + * @param string $filter Filter list on a particular value @@ -1234,18 +1120,15 @@ - */ - public function getServerStatusValues($filter = '') - { - $result = array(); - - $sql = 'SHOW STATUS'; - if ($filter) { - $sql .= " LIKE '".$this->escape($filter)."'"; - } - $resql = $this->query($sql); - if ($resql) { - while ($obj = $this->fetch_object($resql)) { - $result[$obj->Variable_name] = $obj->Value; - } - } - - return $result; - } + */ + public function getServerStatusValues($filter = '') + { + $result = array(); + + $sql = 'SHOW STATUS'; + if ($filter) $sql .= " LIKE '".$this->escape($filter)."'"; + $resql = $this->query($sql); + if ($resql) + { + while ($obj = $this->fetch_object($resql)) $result[$obj->Variable_name] = $obj->Value; + } + + return $result; + } @@ -1253,30 +1135,0 @@ - -/** - * Class to make SSL connection - */ -class mysqliDoli extends mysqli -{ - /** - * Constructor. - * This create an opened connexion to a database server and eventually to a database - * - * @param string $host Address of database server - * @param string $user Name of database user - * @param string $pass Password of database user - * @param string $name Name of database - * @param int $port Port of database server - * @param string $socket Socket - */ - public function __construct($host, $user, $pass, $name, $port = 0, $socket = "") - { - $flags = 0; - parent::init(); - if (strpos($host, 'ssl://') === 0) { - $host = substr($host, 6); - parent::options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false); - parent::ssl_set(null, null, "", null, null); - $flags = MYSQLI_CLIENT_SSL; - } - parent::real_connect($host, $user, $pass, $name, $port, $socket, $flags); - } -} --- /tmp/dsg/dolibarr/htdocs/core/db/github_19.0.3_pgsql.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_pgsql.class.php @@ -38,4 +38,3 @@ - //! Database type - public $type = 'pgsql'; // Name of manager - - //! Database label + //! Database type + public $type = 'pgsql'; // Name of manager + //! Database label @@ -43 +41,0 @@ - @@ -45,5 +43,3 @@ - public $forcecharset = 'UTF8'; // Can't be static as it may be forced with a dynamic value - - //! Collate used to force collate when creating database - public $forcecollate = ''; // Can't be static as it may be forced with a dynamic value - + public $forcecharset = 'UTF8'; // Can't be static as it may be forced with a dynamic value + //! Collate used to force collate when creating database + public $forcecollate = ''; // Can't be static as it may be forced with a dynamic value @@ -52,12 +48 @@ - - /** - * @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 - */ - public $unescapeslashquot = false; - /** - * @var boolean $standard_conforming_string Set this to true if postgres accept only standard encoding of sting using '' and not \' - */ - public $standard_conforming_strings = false; - - - /** @var resource|boolean Resultset of last query */ + /** @var resource Resultset of last query */ @@ -66 +51,2 @@ - + public $unescapeslashquot; + public $standard_conforming_strings; @@ -72 +58 @@ - * @param string $type Type of database (mysql, pgsql...). Not used. + * @param string $type Type of database (mysql, pgsql...) @@ -79 +65 @@ - public function __construct($type, $host, $user, $pass, $name = '', $port = 0) + public function __construct($type, $host, $user, $pass, $name = '', $port = 0) @@ -83,7 +69,3 @@ - // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static - if (!empty($conf->db->character_set)) { - $this->forcecharset = $conf->db->character_set; - } - if (!empty($conf->db->dolibarr_main_db_collation)) { - $this->forcecollate = $conf->db->dolibarr_main_db_collation; - } + // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static + if (!empty($conf->db->character_set)) $this->forcecharset = $conf->db->character_set; + if (!empty($conf->db->dolibarr_main_db_collation)) $this->forcecollate = $conf->db->dolibarr_main_db_collation; @@ -92,2 +74,2 @@ - $this->database_host = $host; - $this->database_port = $port; + $this->database_host = $host; + $this->database_port = $port; @@ -99 +81,2 @@ - if (!function_exists("pg_connect")) { + if (!function_exists("pg_connect")) + { @@ -104,4 +87,5 @@ - return; - } - - if (!$host) { + return $this->ok; + } + + if (!$host) + { @@ -112 +96 @@ - return; + return $this->ok; @@ -119 +103,2 @@ - if ($this->db) { + if ($this->db) + { @@ -122 +107,3 @@ - } else { + } + else + { @@ -127 +114 @@ - dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Connect ".$this->error.'. Failed to connect to host='.$host.' port='.$port.' user='.$user, LOG_ERR); + dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Connect ".$this->error, LOG_ERR); @@ -131,2 +118,4 @@ - if ($this->connected && $name) { - if ($this->select_db($name)) { + if ($this->connected && $name) + { + if ($this->select_db($name)) + { @@ -136 +125,3 @@ - } else { + } + else + { @@ -143 +134,3 @@ - } else { + } + else + { @@ -147,12 +140,14 @@ - } - - - /** - * Convert a SQL request in Mysql syntax to native syntax - * - * @param string $line SQL request line to convert - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @param bool $unescapeslashquot Unescape "slash quote" with "quote quote" - * @return string SQL request line converted - */ - public function convertSQLFromMysql($line, $type = 'auto', $unescapeslashquot = false) + + return $this->ok; + } + + + /** + * Convert a SQL request in Mysql syntax to native syntax + * + * @param string $line SQL request line to convert + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @param bool $unescapeslashquot Unescape slash quote with quote quote + * @return string SQL request line converted + */ + public static function convertSQLFromMysql($line, $type = 'auto', $unescapeslashquot = false) @@ -167 +162,2 @@ - if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) { + if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) + { @@ -170 +166,2 @@ - if ($line != "") { + if ($line != "") + { @@ -173 +170 @@ - $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line); + $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line); @@ -178,146 +175,147 @@ - if ($type == 'auto') { - if (preg_match('/ALTER TABLE/i', $line)) { - $type = 'dml'; - } elseif (preg_match('/CREATE TABLE/i', $line)) { - $type = 'dml'; - } elseif (preg_match('/DROP TABLE/i', $line)) { - $type = 'dml'; - } - } - - $line = preg_replace('/ as signed\)/i', ' as integer)', $line); - - if ($type == 'dml') { - $reg = array(); - - $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space - - // we are inside create table statement so lets process datatypes - if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence - $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); - $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); - $line = preg_replace('/,$/', '', $line); - } - - // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..." - if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) { - $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 SERIAL PRIMARY KEY', $line); - //$line = "-- ".$line." replaced by --\n".$newline; - $line = $newline; - } - - if (preg_match('/[\s\t\(]*(\w*)[\s\t]+bigint.*auto_increment/i', $line, $reg)) { - $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+bigint.*auto_increment[^,]*/i', '\\1 \\2 BIGSERIAL PRIMARY KEY', $line); - //$line = "-- ".$line." replaced by --\n".$newline; - $line = $newline; - } - - // tinyint type conversion - $line = preg_replace('/tinyint\(?[0-9]*\)?/', 'smallint', $line); - $line = preg_replace('/tinyint/i', 'smallint', $line); - - // nuke unsigned - $line = preg_replace('/(int\w+|smallint|bigint)\s+unsigned/i', '\\1', $line); - - // blob -> text - $line = preg_replace('/\w*blob/i', 'text', $line); - - // tinytext/mediumtext -> text - $line = preg_replace('/tinytext/i', 'text', $line); - $line = preg_replace('/mediumtext/i', 'text', $line); - $line = preg_replace('/longtext/i', 'text', $line); - - $line = preg_replace('/text\([0-9]+\)/i', 'text', $line); - - // change not null datetime field to null valid ones - // (to support remapping of "zero time" to null - $line = preg_replace('/datetime not null/i', 'datetime', $line); - $line = preg_replace('/datetime/i', 'timestamp', $line); - - // double -> numeric - $line = preg_replace('/^double/i', 'numeric', $line); - $line = preg_replace('/(\s*)double/i', '\\1numeric', $line); - // float -> numeric - $line = preg_replace('/^float/i', 'numeric', $line); - $line = preg_replace('/(\s*)float/i', '\\1numeric', $line); - - //Check tms timestamp field case (in Mysql this field is defautled to now and - // on update defaulted by now - $line = preg_replace('/(\s*)tms(\s*)timestamp/i', '\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line); - - // nuke DEFAULT CURRENT_TIMESTAMP - $line = preg_replace('/(\s*)DEFAULT(\s*)CURRENT_TIMESTAMP/i', '\\1', $line); - - // nuke ON UPDATE CURRENT_TIMESTAMP - $line = preg_replace('/(\s*)ON(\s*)UPDATE(\s*)CURRENT_TIMESTAMP/i', '\\1', $line); - - // unique index(field1,field2) - if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) { - $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line); - } - - // We remove end of requests "AFTER fieldxxx" - $line = preg_replace('/\sAFTER [a-z0-9_]+/i', '', $line); - - // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX - $line = preg_replace('/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i', 'DROP INDEX', $line); - - // Translate order to rename fields - if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3]; - } - - // Translate order to modify field format - if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $newreg3 = $reg[3]; - $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3); - $newreg3 = preg_replace('/ DEFAULT \'?[0-9a-zA-Z_@]*\'?/i', '', $newreg3); - $line .= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3; - // TODO Add alter to set default value or null/not null if there is this in $reg[3] - } - - // alter table add primary key (field1, field2 ...) -> We remove the primary key name not accepted by PostGreSQL - // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity) - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." ADD PRIMARY KEY (".$reg[3]; - } - - // Translate order to drop primary keys - // ALTER TABLE llx_dolibarr_modules DROP PRIMARY KEY pk_xxx - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+PRIMARY\s+KEY\s*([^;]+)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; - } - - // Translate order to drop foreign keys - // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; - } - - // Translate order to add foreign keys - // ALTER TABLE llx_tablechild ADD CONSTRAINT fk_tablechild_fk_fieldparent FOREIGN KEY (fk_fieldparent) REFERENCES llx_tableparent (rowid) - if (preg_match('/ALTER\s+TABLE\s+(.*)\s*ADD CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) { - $line = preg_replace('/;$/', '', $line); - $line .= " DEFERRABLE INITIALLY IMMEDIATE;"; - } - - // alter table add [unique] [index] (field1, field2 ...) - // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version) - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) { - $fieldlist = $reg[4]; - $idxname = $reg[3]; - $tablename = $reg[1]; - $line = "-- ".$line." replaced by --\n"; - $line .= "CREATE ".(preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")"; - } - } - - // To have postgresql case sensitive + if ($type == 'auto') + { + if (preg_match('/ALTER TABLE/i', $line)) $type = 'dml'; + elseif (preg_match('/CREATE TABLE/i', $line)) $type = 'dml'; + elseif (preg_match('/DROP TABLE/i', $line)) $type = 'dml'; + } + + $line = preg_replace('/ as signed\)/i', ' as integer)', $line); + + if ($type == 'dml') + { + $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space + + // we are inside create table statement so lets process datatypes + if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence + $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); + $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb).*;/i', ');', $line); + $line = preg_replace('/,$/', '', $line); + } + + // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..." + if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) { + $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 SERIAL PRIMARY KEY', $line); + //$line = "-- ".$line." replaced by --\n".$newline; + $line = $newline; + } + + if (preg_match('/[\s\t\(]*(\w*)[\s\t]+bigint.*auto_increment/i', $line, $reg)) { + $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+bigint.*auto_increment[^,]*/i', '\\1 \\2 BIGSERIAL PRIMARY KEY', $line); + //$line = "-- ".$line." replaced by --\n".$newline; + $line = $newline; + } + + // tinyint type conversion + $line = preg_replace('/tinyint\(?[0-9]*\)?/', 'smallint', $line); + $line = preg_replace('/tinyint/i', 'smallint', $line); + + // nuke unsigned + $line = preg_replace('/(int\w+|smallint)\s+unsigned/i', '\\1', $line); + + // blob -> text + $line = preg_replace('/\w*blob/i', 'text', $line); + + // tinytext/mediumtext -> text + $line = preg_replace('/tinytext/i', 'text', $line); + $line = preg_replace('/mediumtext/i', 'text', $line); + $line = preg_replace('/longtext/i', 'text', $line); + + $line = preg_replace('/text\([0-9]+\)/i', 'text', $line); + + // change not null datetime field to null valid ones + // (to support remapping of "zero time" to null + $line = preg_replace('/datetime not null/i', 'datetime', $line); + $line = preg_replace('/datetime/i', 'timestamp', $line); + + // double -> numeric + $line = preg_replace('/^double/i', 'numeric', $line); + $line = preg_replace('/(\s*)double/i', '\\1numeric', $line); + // float -> numeric + $line = preg_replace('/^float/i', 'numeric', $line); + $line = preg_replace('/(\s*)float/i', '\\1numeric', $line); + + //Check tms timestamp field case (in Mysql this field is defautled to now and + // on update defaulted by now + $line = preg_replace('/(\s*)tms(\s*)timestamp/i', '\\1tms timestamp without time zone DEFAULT now() NOT NULL', $line); + + // nuke ON UPDATE CURRENT_TIMESTAMP + $line = preg_replace('/(\s*)on(\s*)update(\s*)CURRENT_TIMESTAMP/i', '\\1', $line); + + // unique index(field1,field2) + if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) + { + $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line); + } + + // We remove end of requests "AFTER fieldxxx" + $line = preg_replace('/\sAFTER [a-z0-9_]+/i', '', $line); + + // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX + $line = preg_replace('/ALTER TABLE [a-z0-9_]+\s+DROP INDEX/i', 'DROP INDEX', $line); + + // Translate order to rename fields + if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3]; + } + + // Translate order to modify field format + if (preg_match('/ALTER TABLE ([a-z0-9_]+)\s+MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $newreg3 = $reg[3]; + $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3); + $newreg3 = preg_replace('/ DEFAULT \'?[0-9a-zA-Z_@]*\'?/i', '', $newreg3); + $line .= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3; + // TODO Add alter to set default value or null/not null if there is this in $reg[3] + } + + // alter table add primary key (field1, field2 ...) -> We remove the primary key name not accepted by PostGreSQL + // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity) + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." ADD PRIMARY KEY (".$reg[3]; + } + + // Translate order to drop primary keys + // ALTER TABLE llx_dolibarr_modules DROP PRIMARY KEY pk_xxx + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+PRIMARY\s+KEY\s*([^;]+)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; + } + + // Translate order to drop foreign keys + // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; + } + + // Translate order to add foreign keys + // ALTER TABLE llx_tablechild ADD CONSTRAINT fk_tablechild_fk_fieldparent FOREIGN KEY (fk_fieldparent) REFERENCES llx_tableparent (rowid) + if (preg_match('/ALTER\s+TABLE\s+(.*)\s*ADD CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) + { + $line = preg_replace('/;$/', '', $line); + $line .= " DEFERRABLE INITIALLY IMMEDIATE;"; + } + + // alter table add [unique] [index] (field1, field2 ...) + // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version) + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) + { + $fieldlist = $reg[4]; + $idxname = $reg[3]; + $tablename = $reg[1]; + $line = "-- ".$line." replaced by --\n"; + $line .= "CREATE ".(preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")"; + } + } + + // To have postgresql case sensitive @@ -326 +324,2 @@ - if (getDolGlobalString('PSQL_USE_UNACCENT') && $count_like > 0) { + if (!empty($conf->global->PSQL_USE_UNACCENT) && $count_like > 0) + { @@ -331,4 +330,4 @@ - $line = str_replace(' LIKE BINARY \'', ' LIKE \'', $line); - - // Replace INSERT IGNORE into INSERT - $line = preg_replace('/^INSERT IGNORE/', 'INSERT', $line); + $line = str_replace(' LIKE BINARY \'', ' LIKE \'', $line); + + // Replace INSERT IGNORE into INSERT + $line = preg_replace('/^INSERT IGNORE/', 'INSERT', $line); @@ -338,2 +337,4 @@ - if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) { - if ($reg[1] == $reg[2]) { // If same table, we remove second one + if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) + { + if ($reg[1] == $reg[2]) // If same table, we remove second one + { @@ -368,3 +369 @@ - if ($unescapeslashquot) { - $line = preg_replace("/\\\'/", "''", $line); - } + if ($unescapeslashquot) $line = preg_replace("/\\\'/", "''", $line); @@ -378,5 +377,5 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Select a database - * Ici postgresql n'a aucune fonction equivalente de mysql_select_db - * On compare juste manuellement si la database choisie est bien celle activee par la connexion + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Select a database + * Ici postgresql n'a aucune fonction equivalente de mysql_select_db + * On compare juste manuellement si la database choisie est bien celle activee par la connexion @@ -387,9 +386,9 @@ - public function select_db($database) - { - // phpcs:enable - if ($database == $this->database_name) { - return true; - } else { - return false; - } - } + public function select_db($database) + { + // phpcs:enable + if ($database == $this->database_name) { + return true; + } else { + return false; + } + } @@ -408 +407 @@ - public function connect($host, $login, $passwd, $name, $port = 0) + public function connect($host, $login, $passwd, $name, $port = 0) @@ -421,3 +420 @@ - if (!$name) { - $name = "postgres"; // When try to connect using admin user - } + if (!$name) $name = "postgres"; // When try to connect using admin user @@ -426 +423,2 @@ - if ((!empty($host) && $host == "socket") && !defined('NOLOCALSOCKETPGCONNECT')) { + if ((!empty($host) && $host == "socket") && !defined('NOLOCALSOCKETPGCONNECT')) + { @@ -428,5 +426 @@ - try { - $this->db = @pg_connect($con_string); - } catch (Exception $e) { - // No message - } + $this->db = @pg_connect($con_string); @@ -436,7 +430,4 @@ - if (empty($this->db)) { - if (!$host) { - $host = "localhost"; - } - if (!$port) { - $port = 5432; - } + if (!$this->db) + { + if (!$host) $host = "localhost"; + if (!$port) $port = 5432; @@ -445,5 +436 @@ - try { - $this->db = @pg_connect($con_string); - } catch (Exception $e) { - print $e->getMessage(); - } + $this->db = @pg_connect($con_string); @@ -453 +440,2 @@ - if ($this->db) { + if ($this->db) + { @@ -467 +455 @@ - public function getVersion() + public function getVersion() @@ -470,3 +458,4 @@ - if ($resql) { - $liste = $this->fetch_array($resql); - return $liste['server_version']; + if ($resql) + { + $liste = $this->fetch_array($resql); + return $liste['server_version']; @@ -482 +471 @@ - public function getDriverInfo() + public function getDriverInfo() @@ -487,17 +476,16 @@ - /** - * Close database connexion - * - * @return boolean True if disconnect successfull, false otherwise - * @see connect() - */ - public function close() - { - if ($this->db) { - if ($this->transaction_opened > 0) { - dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); - } - $this->connected = false; - return pg_close($this->db); - } - return false; - } + /** + * Close database connexion + * + * @return boolean True if disconnect successfull, false otherwise + * @see connect() + */ + public function close() + { + if ($this->db) + { + if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); + $this->connected = false; + return pg_close($this->db); + } + return false; + } @@ -510,7 +498,6 @@ - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @param int $result_mode Result mode (not used with pgsql) - * @return bool|resource Resultset of answer - */ - public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0) - { - global $conf, $dolibarr_main_db_readonly; + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return false|resource Resultset of answer + */ + public function query($query, $usesavepoint = 0, $type = 'auto') + { + global $conf; @@ -524 +511,2 @@ - if (getDolGlobalString('MAIN_DB_AUTOFIX_BAD_SQL_REQUEST')) { + if (!empty($conf->global->MAIN_DB_AUTOFIX_BAD_SQL_REQUEST)) + { @@ -527,2 +515,4 @@ - while ($loop) { - 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)) { + while ($loop) + { + 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)) + { @@ -531,2 +520,0 @@ - } else { - $loop = false; @@ -533,0 +522 @@ + else $loop = false; @@ -537 +526,2 @@ - if ($usesavepoint && $this->transaction_opened) { + if ($usesavepoint && $this->transaction_opened) + { @@ -541 +531,2 @@ - if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) { + if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) + { @@ -545,12 +535,0 @@ - if (empty($query)) { - return false; // Return false = error if empty request - } - - if (!empty($dolibarr_main_db_readonly)) { - if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) { - $this->lasterror = 'Application in read-only mode'; - $this->lasterrno = 'APPREADONLY'; - $this->lastquery = $query; - return false; - } - } @@ -561,10 +540,11 @@ - if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) { // Si requete utilisateur, on la sauvegarde ainsi que son resultset - if (!$ret) { - if ($this->errno() != 'DB_ERROR_25P02') { // Do not overwrite errors if this is a consecutive error - $this->lastqueryerror = $query; - $this->lasterror = $this->error(); - $this->lasterrno = $this->errno(); - - if (getDolGlobalInt('SYSLOG_LEVEL') < LOG_DEBUG) { - dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously - } + if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) // Si requete utilisateur, on la sauvegarde ainsi que son resultset + { + if (!$ret) + { + if ($this->errno() != 'DB_ERROR_25P02') // Do not overwrite errors if this is a consecutive error + { + $this->lastqueryerror = $query; + $this->lasterror = $this->error(); + $this->lasterrno = $this->errno(); + + if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously @@ -573,3 +553,4 @@ - } - - if ($usesavepoint && $this->transaction_opened) { // Warning, after that errno will be erased + } + + if ($usesavepoint && $this->transaction_opened) // Warning, after that errno will be erased + { @@ -586,3 +567,3 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Returns the current line (as an object) for the resultset cursor + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Renvoie la ligne courante (comme un objet) pour le curseur resultset @@ -593,7 +574,5 @@ - public function fetch_object($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + public function fetch_object($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -603,14 +582,12 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param resource $resultset Resultset of request - * @return false|array Array - */ - public function fetch_array($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param resource $resultset Resultset of request + * @return false|array Array + */ + public function fetch_array($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -620,10 +597,10 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param resource $resultset Resultset of request - * @return false|array Array - */ - public function fetch_row($resultset) - { - // phpcs:enable + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param resource $resultset Resultset of request + * @return false|array Array + */ + public function fetch_row($resultset) + { + // phpcs:enable @@ -631,3 +608 @@ - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -637,15 +612,13 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return number of lines for result of a SELECT - * - * @param resource $resultset Resulset of requests - * @return int Nb of lines, -1 on error - * @see affected_rows() - */ - public function num_rows($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return number of lines for result of a SELECT + * + * @param resource $resultset Resulset of requests + * @return int Nb of lines, -1 on error + * @see affected_rows() + */ + public function num_rows($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -655 +628 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -663,7 +636,5 @@ - public function affected_rows($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + public function affected_rows($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -682,6 +653,4 @@ - public function free($resultset = null) - { - // If resultset not provided, we take the last used by connexion - if (!is_resource($resultset) && !is_object($resultset)) { - $resultset = $this->_results; - } + public function free($resultset = null) + { + // If resultset not provided, we take the last used by connexion + if (!is_resource($resultset)) { $resultset = $this->_results; } @@ -689,14 +658,12 @@ - if (is_resource($resultset) || is_object($resultset)) { - pg_free_result($resultset); - } - } - - - /** - * Define limits and offset of request - * - * @param int $limit Maximum number of lines returned (-1=conf->liste_limit, 0=no limit) - * @param int $offset Numero of line from where starting fetch - * @return string String with SQL syntax to add a limit and offset - */ - public function plimit($limit = 0, $offset = 0) + if (is_resource($resultset)) pg_free_result($resultset); + } + + + /** + * Define limits and offset of request + * + * @param int $limit Maximum number of lines returned (-1=conf->liste_limit, 0=no limit) + * @param int $offset Numero of line from where starting fetch + * @return string String with SQL syntax to add a limit and offset + */ + public function plimit($limit = 0, $offset = 0) @@ -705,11 +672,4 @@ - if (empty($limit)) { - return ""; - } - if ($limit < 0) { - $limit = $conf->liste_limit; - } - if ($offset > 0) { - return " LIMIT ".$limit." OFFSET ".$offset." "; - } else { - return " LIMIT $limit "; - } + if (empty($limit)) return ""; + if ($limit < 0) $limit = $conf->liste_limit; + if ($offset > 0) return " LIMIT ".$limit." OFFSET ".$offset." "; + else return " LIMIT $limit "; @@ -725 +685 @@ - public function escape($stringtoencode) + public function escape($stringtoencode) @@ -730,14 +690,15 @@ - /** - * Escape a string to insert data into a like - * - * @param string $stringtoencode String to escape - * @return string String escaped - */ - public function escapeforlike($stringtoencode) - { - return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode); - } - - /** - * Format a SQL IF - * + /** + * Convert (by PHP) a GM Timestamp date into a GM string date to insert into a date field. + * Function to use to build INSERT, UPDATE or WHERE predica + * + * @param string $param Date TMS to convert + * @return string Date in a string YYYY-MM-DD HH:MM:SS + */ + public function idate($param) + { + return dol_print_date($param, "%Y-%m-%d %H:%M:%S"); + } + + /** + * Format a SQL IF + * @@ -749 +710 @@ - public function ifsql($test, $resok, $resko) + public function ifsql($test, $resok, $resko) @@ -755,18 +715,0 @@ - * Format a SQL REGEXP - * - * @param string $subject string tested - * @param string $pattern SQL pattern to match - * @param int $sqlstring whether or not the string being tested is an SQL expression - * @return string SQL string - */ - public function regexpsql($subject, $pattern, $sqlstring = 0) - { - if ($sqlstring) { - return "(". $subject ." ~ '" . $pattern . "')"; - } - - return "('". $subject ."' ~ '" . $pattern . "')"; - } - - - /** @@ -777 +720 @@ - public function errno() + public function errno() @@ -782 +725,2 @@ - } else { + } + else { @@ -816,2 +760,2 @@ - $reg = array(); - if (preg_match('/: *([0-9P]+):/', $errorlabel, $reg)) { + if (preg_match('/: *([0-9P]+):/', $errorlabel, $reg)) + { @@ -819 +763,2 @@ - if (isset($errorcode_map[$errorcode])) { + if (isset($errorcode_map[$errorcode])) + { @@ -841 +786 @@ - public function error() + public function error() @@ -846 +791 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -854,3 +799,3 @@ - public function last_insert_id($tab, $fieldid = 'rowid') - { - // phpcs:enable + public function last_insert_id($tab, $fieldid = 'rowid') + { + // phpcs:enable @@ -859 +804,2 @@ - if (!$result) { + if (!$result) + { @@ -869,8 +815,8 @@ - * Encrypt sensitive data in database - * Warning: This function includes the escape and add the SQL simple quotes on strings. - * - * @param string $fieldorvalue Field name or value to encrypt - * @param int $withQuotes Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated). - * @return string XXX(field) or XXX('value') or field or 'value' - */ - public function encrypt($fieldorvalue, $withQuotes = 1) + * Encrypt sensitive data in database + * Warning: This function includes the escape, so it must use direct value + * + * @param string $fieldorvalue Field name or value to encrypt + * @param int $withQuotes Return string with quotes + * @return string XXX(field) or XXX('value') or field or 'value' + */ + public function encrypt($fieldorvalue, $withQuotes = 0) @@ -881 +827 @@ - //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); + $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); @@ -884 +830 @@ - //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); @@ -897 +843 @@ - public function decrypt($value) + public function decrypt($value) @@ -902 +848 @@ - //$cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); + $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); @@ -905 +851 @@ - //$cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); @@ -912 +858 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -918,3 +864,3 @@ - public function DDLGetConnectId() - { - // phpcs:enable + public function DDLGetConnectId() + { + // phpcs:enable @@ -926 +872 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -938,9 +884,5 @@ - public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') - { - // phpcs:enable - if (empty($charset)) { - $charset = $this->forcecharset; - } - if (empty($collation)) { - $collation = $this->forcecollate; - } + public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') + { + // phpcs:enable + if (empty($charset)) $charset = $this->forcecharset; + if (empty($collation)) $collation = $this->forcecollate; @@ -951,2 +893 @@ - // NOTE: Do not use ' around the database name - $sql = "CREATE DATABASE ".$this->escape($database)." OWNER '".$this->escape($owner)."' ENCODING '".$this->escape($charset)."'"; + $sql = 'CREATE DATABASE "'.$database.'" OWNER "'.$owner.'" ENCODING \''.$charset.'\''; @@ -958 +899 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -966,3 +907,3 @@ - public function DDLListTables($database, $table = '') - { - // phpcs:enable + public function DDLListTables($database, $table = '') + { + // phpcs:enable @@ -971,12 +912,10 @@ - $escapedlike = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $escapedlike = " AND table_name LIKE '".$this->escape($tmptable)."'"; - } - $result = pg_query($this->db, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike." ORDER BY table_name"); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row[0]; - } - } + $like = ''; + if ($table) $like = " AND table_name LIKE '".$table."'"; + $result = pg_query($this->db, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'".$like." ORDER BY table_name"); + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $listtables[] = $row[0]; + } + } @@ -986,29 +925 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List tables into a database - * - * @param string $database Name of database - * @param string $table Name of table filter ('xxx%') - * @return array List of tables in an array - */ - public function DDLListTablesFull($database, $table = '') - { - // phpcs:enable - $listtables = array(); - - $escapedlike = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $escapedlike = " AND table_name LIKE '".$this->escape($tmptable)."'"; - } - $result = pg_query($this->db, "SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public'".$escapedlike." ORDER BY table_name"); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row; - } - } - return $listtables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1022,3 +933,3 @@ - public function DDLInfoTable($table) - { - // phpcs:enable + public function DDLInfoTable($table) + { + // phpcs:enable @@ -1039,2 +950,2 @@ - $sql .= " WHERE table_schema = 'public' "; - $sql .= " AND table_name = '".$this->escape($table)."'"; + $sql .= " WHERE table_schema='public' "; + $sql .= " AND table_name='".$table."'"; @@ -1045,10 +956,12 @@ - if ($result) { - while ($row = $this->fetch_row($result)) { - $infotables[] = $row; - } - } - return $infotables; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $infotables[] = $row; + } + } + return $infotables; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1065,5 +978,5 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) - { - // phpcs:enable + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) + { + // phpcs:enable @@ -1076 +989,2 @@ - foreach ($fields as $field_name => $field_desc) { + foreach ($fields as $field_name => $field_desc) + { @@ -1079,14 +993,10 @@ - if (preg_match("/^[^\s]/i", $field_desc['value'])) { - $sqlfields[$i] .= "(".$field_desc['value'].")"; - } elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) { - $sqlfields[$i] .= " ".$field_desc['attribute']; - } elseif (preg_match("/^[^\s]/i", $field_desc['default'])) { - if (preg_match("/null/i", $field_desc['default'])) { - $sqlfields[$i] .= " default ".$field_desc['default']; - } else { - $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'"; - } - } elseif (preg_match("/^[^\s]/i", $field_desc['null'])) { - $sqlfields[$i] .= " ".$field_desc['null']; - } elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) { - $sqlfields[$i] .= " ".$field_desc['extra']; + if (preg_match("/^[^\s]/i", $field_desc['value'])) + $sqlfields[$i] .= "(".$field_desc['value'].")"; + elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) + $sqlfields[$i] .= " ".$field_desc['attribute']; + elseif (preg_match("/^[^\s]/i", $field_desc['default'])) + { + if (preg_match("/null/i", $field_desc['default'])) + $sqlfields[$i] .= " default ".$field_desc['default']; + else + $sqlfields[$i] .= " default '".$field_desc['default']."'"; @@ -1093,0 +1004,5 @@ + elseif (preg_match("/^[^\s]/i", $field_desc['null'])) + $sqlfields[$i] .= " ".$field_desc['null']; + + elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) + $sqlfields[$i] .= " ".$field_desc['extra']; @@ -1096,5 +1011,5 @@ - if ($primary_key != "") { - $pk = "primary key(".$primary_key.")"; - } - - if (is_array($unique_keys)) { + if ($primary_key != "") + $pk = "primary key(".$primary_key.")"; + + if (is_array($unique_keys)) + { @@ -1102,2 +1017,3 @@ - foreach ($unique_keys as $key => $value) { - $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')"; + foreach ($unique_keys as $key => $value) + { + $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')"; @@ -1107 +1023,2 @@ - if (is_array($keys)) { + if (is_array($keys)) + { @@ -1109 +1026,2 @@ - foreach ($keys as $key => $value) { + foreach ($keys as $key => $value) + { @@ -1115,9 +1033,6 @@ - if ($primary_key != "") { - $sql .= ",".$pk; - } - if (is_array($unique_keys)) { - $sql .= ",".implode(',', $sqluq); - } - if (is_array($keys)) { - $sql .= ",".implode(',', $sqlk); - } + if ($primary_key != "") + $sql .= ",".$pk; + if (is_array($unique_keys)) + $sql .= ",".implode(',', $sqluq); + if (is_array($keys)) + $sql .= ",".implode(',', $sqlk); @@ -1127 +1042,19 @@ - if (!$this->query($sql)) { + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Drop a table into database + * + * @param string $table Name of table + * @return int <0 if KO, >=0 if OK + */ + public function DDLDropTable($table) + { + // phpcs:enable + $sql = "DROP TABLE ".$table; + + if (!$this->query($sql)) @@ -1129 +1062 @@ - } else { + else @@ -1131,25 +1064,3 @@ - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Drop a table into database - * - * @param string $table Name of table - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLDropTable($table) - { - // phpcs:enable - $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); - - $sql = "DROP TABLE ".$tmptable; - - if (!$this->query($sql)) { - return -1; - } else { - return 1; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1163,5 +1074,5 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) - { - // phpcs:enable + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) + { + // phpcs:enable @@ -1173 +1084,2 @@ - if (!$resql) { + if (!$resql) + { @@ -1180 +1092 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1188,4 +1100,4 @@ - public function DDLDescTable($table, $field = "") - { - // phpcs:enable - $sql = "SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$this->escape($table)."' AND attrelid = typrelid"; + public function DDLDescTable($table, $field = "") + { + // phpcs:enable + $sql = "SELECT attname FROM pg_attribute, pg_type WHERE typname = '".$table."' AND attrelid = typrelid"; @@ -1193,3 +1105 @@ - if ($field) { - $sql .= " AND attname = '".$this->escape($field)."'"; - } + if ($field) $sql .= " AND attname = '".$field."'"; @@ -1202 +1112 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1210,5 +1120,5 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLAddField($table, $field_name, $field_desc, $field_position = "") - { - // phpcs:enable + * @return int <0 if KO, >0 if OK + */ + public function DDLAddField($table, $field_name, $field_desc, $field_position = "") + { + // phpcs:enable @@ -1220,10 +1130,9 @@ - if (!in_array($field_desc['type'], array('smallint', 'int', 'date', 'datetime')) && $field_desc['value']) { - $sql .= "(".$field_desc['value'].")"; - } - } - if (preg_match("/^[^\s]/i", $field_desc['attribute'])) { - $sql .= " ".$field_desc['attribute']; - } - if (preg_match("/^[^\s]/i", $field_desc['null'])) { - $sql .= " ".$field_desc['null']; - } + if (!in_array($field_desc['type'], array('int', 'date', 'datetime')) && $field_desc['value']) + { + $sql .= "(".$field_desc['value'].")"; + } + } + if (preg_match("/^[^\s]/i", $field_desc['attribute'])) + $sql .= " ".$field_desc['attribute']; + if (preg_match("/^[^\s]/i", $field_desc['null'])) + $sql .= " ".$field_desc['null']; @@ -1231,2 +1140,2 @@ - if (preg_match("/null/i", $field_desc['default'])) { - $sql .= " default ".$field_desc['default']; + if (preg_match("/null/i", $field_desc['default'])) { + $sql .= " default ".$field_desc['default']; @@ -1234 +1143 @@ - $sql .= " default '".$this->escape($field_desc['default'])."'"; + $sql .= " default '".$field_desc['default']."'"; @@ -1243 +1152 @@ - if (!$this -> query($sql)) { + if (!$this -> query($sql)) @@ -1245 +1153,0 @@ - } @@ -1249 +1157 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1256,5 +1164,5 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLUpdateField($table, $field_name, $field_desc) - { - // phpcs:enable + * @return int <0 if KO, >0 if OK + */ + public function DDLUpdateField($table, $field_name, $field_desc) + { + // phpcs:enable @@ -1262,24 +1170,24 @@ - $sql .= " ALTER COLUMN ".$this->escape($field_name)." TYPE ".$field_desc['type']; - if (preg_match("/^[^\s]/i", $field_desc['value'])) { - if (!in_array($field_desc['type'], array('smallint', 'int', 'date', 'datetime')) && $field_desc['value']) { - $sql .= "(".$field_desc['value'].")"; - } - } - - if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') { - // 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 - if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') { - $sqlbis = "UPDATE ".$table." SET ".$this->escape($field_name)." = '".$this->escape(isset($field_desc['default']) ? $field_desc['default'] : '')."' WHERE ".$this->escape($field_name)." IS NULL"; - $this->query($sqlbis); - } elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') { - $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"; - $this->query($sqlbis); - } - } - - if (isset($field_desc['default']) && $field_desc['default'] != '') { - if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') { - $sql .= ", ALTER COLUMN ".$this->escape($field_name)." SET DEFAULT ".((float) $field_desc['default']); - } elseif ($field_desc['type'] != 'text') { // Default not supported on text fields ? - $sql .= ", ALTER COLUMN ".$this->escape($field_name)." SET DEFAULT '".$this->escape($field_desc['default'])."'"; - } + $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type']; + if (in_array($field_desc['type'], array('double', 'tinyint', 'int', 'varchar')) && $field_desc['value']) { + $sql .= "(".$field_desc['value'].")"; + } + + if ($field_desc['null'] == 'not null' || $field_desc['null'] == 'NOT NULL') + { + // 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 + if ($field_desc['type'] == 'varchar' || $field_desc['type'] == 'text') + { + $sqlbis = "UPDATE ".$table." SET ".$field_name." = '".$this->escape($field_desc['default'] ? $field_desc['default'] : '')."' WHERE ".$field_name." IS NULL"; + $this->query($sqlbis); + } + elseif ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') + { + $sqlbis = "UPDATE ".$table." SET ".$field_name." = ".((int) $this->escape($field_desc['default'] ? $field_desc['default'] : 0))." WHERE ".$field_name." IS NULL"; + $this->query($sqlbis); + } + } + + if ($field_desc['default'] != '') + { + if ($field_desc['type'] == 'double' || $field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int') $sql .= " DEFAULT ".$this->escape($field_desc['default']); + elseif ($field_desc['type'] != 'text') $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields @@ -1289 +1197 @@ - if (!$this->query($sql)) { + if (!$this->query($sql)) @@ -1291 +1198,0 @@ - } @@ -1295 +1202 @@ - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1301,9 +1208,9 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLDropField($table, $field_name) - { - // phpcs:enable - $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name); - - $sql = "ALTER TABLE ".$table." DROP COLUMN ".$tmp_field_name; - if (!$this->query($sql)) { + * @return int <0 if KO, >0 if OK + */ + public function DDLDropField($table, $field_name) + { + // phpcs:enable + $sql = "ALTER TABLE ".$table." DROP COLUMN ".$field_name; + dol_syslog($sql, LOG_DEBUG); + if (!$this->query($sql)) + { @@ -1321 +1228 @@ - public function getDefaultCharacterSetDatabase() + public function getDefaultCharacterSetDatabase() @@ -1324,6 +1231,6 @@ - if ($resql) { - $liste = $this->fetch_array($resql); - return $liste['server_encoding']; - } else { - return ''; - } + if ($resql) + { + $liste = $this->fetch_array($resql); + return $liste['server_encoding']; + } + else return ''; @@ -1337 +1244 @@ - public function getListOfCharacterSet() + public function getListOfCharacterSet() @@ -1341 +1248,2 @@ - if ($resql) { + if ($resql) + { @@ -1343 +1251,2 @@ - while ($obj = $this->fetch_object($resql)) { + while ($obj = $this->fetch_object($resql)) + { @@ -1360 +1269 @@ - public function getDefaultCollationDatabase() + public function getDefaultCollationDatabase() @@ -1363,2 +1272,3 @@ - if ($resql) { - $liste = $this->fetch_array($resql); + if ($resql) + { + $liste = $this->fetch_array($resql); @@ -1366,3 +1276,2 @@ - } else { - return ''; - } + } + else return ''; @@ -1376 +1285 @@ - public function getListOfCollation() + public function getListOfCollation() @@ -1380 +1289,2 @@ - if ($resql) { + if ($resql) + { @@ -1382 +1292,2 @@ - while ($obj = $this->fetch_object($resql)) { + while ($obj = $this->fetch_object($resql)) + { @@ -1398 +1309 @@ - public function getPathOfDump() + public function getPathOfDump() @@ -1402,10 +1313,14 @@ - if (file_exists('/usr/bin/pg_dump')) { - $fullpathofdump = '/usr/bin/pg_dump'; - } else { - // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande - $resql = $this->query('SHOW data_directory'); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['data_directory']; - $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/pg_dump'; - } + if (file_exists('/usr/bin/pg_dump')) + { + $fullpathofdump = '/usr/bin/pg_dump'; + } + else + { + // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande + $resql = $this->query('SHOW data_directory'); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['data_directory']; + $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/pg_dump'; + } @@ -1417,6 +1332,6 @@ - /** - * Return full path of restore program - * - * @return string Full path of restore program - */ - public function getPathOfRestore() + /** + * Return full path of restore program + * + * @return string Full path of restore program + */ + public function getPathOfRestore() @@ -1429,11 +1344,15 @@ - if (file_exists('/usr/bin/'.$tool)) { - $fullpathofdump = '/usr/bin/'.$tool; - } else { - // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande - $resql = $this->query('SHOW data_directory'); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['data_directory']; - $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/'.$tool; - } - } + if (file_exists('/usr/bin/'.$tool)) + { + $fullpathofdump = '/usr/bin/'.$tool; + } + else + { + // TODO L'utilisateur de la base doit etre un superadmin pour lancer cette commande + $resql = $this->query('SHOW data_directory'); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['data_directory']; + $fullpathofdump = preg_replace('/data$/', 'bin', $basedir).'/'.$tool; + } + } @@ -1450 +1369 @@ - public function getServerParametersValues($filter = '') + public function getServerParametersValues($filter = '') @@ -1455,3 +1374 @@ - if ($filter) { - $resql .= " WHERE name = '".$this->escape($filter)."'"; - } + if ($filter) $resql .= " WHERE name = '".$this->escape($filter)."'"; @@ -1459,4 +1376,3 @@ - if ($resql) { - while ($obj = $this->fetch_object($resql)) { - $result[$obj->name] = $obj->setting; - } + if ($resql) + { + while ($obj = $this->fetch_object($resql)) $result[$obj->name] = $obj->setting; @@ -1474 +1390 @@ - public function getServerStatusValues($filter = '') + public function getServerStatusValues($filter = '') @@ -1478,7 +1394,7 @@ - if ($filter) $sql.=" LIKE '".$this->escape($filter)."'"; - $resql=$this->query($sql); - if ($resql) - { - $obj=$this->fetch_object($resql); - $result[$obj->Variable_name]=$obj->Value; - } + if ($filter) $sql.=" LIKE '".$this->escape($filter)."'"; + $resql=$this->query($sql); + if ($resql) + { + $obj=$this->fetch_object($resql); + $result[$obj->Variable_name]=$obj->Value; + } --- /tmp/dsg/dolibarr/htdocs/core/db/github_19.0.3_sqlite3.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_sqlite3.class.php @@ -35,259 +35,265 @@ - //! Database type - public $type = 'sqlite3'; - //! Database label - const LABEL = 'Sqlite3'; - //! Version min database - const VERSIONMIN = '3.0.0'; - - /** - * @var SQLite3Result|boolean Resultset of last query - */ - private $_results; - - const WEEK_MONDAY_FIRST = 1; - const WEEK_YEAR = 2; - const WEEK_FIRST_WEEKDAY = 4; - - - /** - * Constructor. - * This create an opened connexion to a database server and eventually to a database - * - * @param string $type Type of database (mysql, pgsql...). Not used. - * @param string $host Address of database server - * @param string $user Nom de l'utilisateur autorise - * @param string $pass Mot de passe - * @param string $name Nom de la database - * @param int $port Port of database server - */ - public function __construct($type, $host, $user, $pass, $name = '', $port = 0) - { - global $conf; - - // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static - if (!empty($conf->db->character_set)) { - $this->forcecharset = $conf->db->character_set; - } - if (!empty($conf->db->dolibarr_main_db_collation)) { - $this->forcecollate = $conf->db->dolibarr_main_db_collation; - } - - $this->database_user = $user; - $this->database_host = $host; - $this->database_port = $port; - - $this->transaction_opened = 0; - - //print "Name DB: $host,$user,$pass,$name
"; - - /*if (! function_exists("sqlite_query")) - { - $this->connected = false; - $this->ok = false; - $this->error="Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver."; - dol_syslog(get_class($this)."::DoliDBSqlite3 : Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver.",LOG_ERR); - return; - }*/ - - /*if (! $host) - { - $this->connected = false; - $this->ok = false; - $this->error=$langs->trans("ErrorWrongHostParameter"); - dol_syslog(get_class($this)."::DoliDBSqlite3 : Erreur Connect, wrong host parameters",LOG_ERR); - return; - }*/ - - // Essai connexion serveur - // We do not try to connect to database, only to server. Connect to database is done later in constrcutor - $this->db = $this->connect($host, $user, $pass, $name, $port); - - if ($this->db) { - $this->connected = true; - $this->ok = true; - $this->database_selected = true; - $this->database_name = $name; - - $this->addCustomFunction('IF'); - $this->addCustomFunction('MONTH'); - $this->addCustomFunction('CURTIME'); - $this->addCustomFunction('CURDATE'); - $this->addCustomFunction('WEEK', 1); - $this->addCustomFunction('WEEK', 2); - $this->addCustomFunction('WEEKDAY'); - $this->addCustomFunction('date_format'); - //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); - } else { - // host, login ou password incorrect - $this->connected = false; - $this->ok = false; - $this->database_selected = false; - $this->database_name = ''; - //$this->error=sqlite_connect_error(); - dol_syslog(get_class($this)."::DoliDBSqlite3 : Error Connect ".$this->error, LOG_ERR); - } - } - - - /** - * Convert a SQL request in Mysql syntax to native syntax - * - * @param string $line SQL request line to convert - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @return string SQL request line converted - */ - public function convertSQLFromMysql($line, $type = 'ddl') - { - // Removed empty line if this is a comment line for SVN tagging - if (preg_match('/^--\s\$Id/i', $line)) { - return ''; - } - // Return line if this is a comment - if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) { - return $line; - } - if ($line != "") { - if ($type == 'auto') { - if (preg_match('/ALTER TABLE/i', $line)) { - $type = 'dml'; - } elseif (preg_match('/CREATE TABLE/i', $line)) { - $type = 'dml'; - } elseif (preg_match('/DROP TABLE/i', $line)) { - $type = 'dml'; - } - } - - if ($type == 'dml') { - $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space - - // we are inside create table statement so lets process datatypes - if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence - $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb);/i', ');', $line); - $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb);/i', ');', $line); - $line = preg_replace('/,$/', '', $line); - } - - // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..." - if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) { - $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 integer PRIMARY KEY AUTOINCREMENT', $line); - //$line = "-- ".$line." replaced by --\n".$newline; - $line = $newline; - } - - // tinyint type conversion - $line = str_replace('tinyint', 'smallint', $line); - - // nuke unsigned - $line = preg_replace('/(int\w+|smallint)\s+unsigned/i', '\\1', $line); - - // blob -> text - $line = preg_replace('/\w*blob/i', 'text', $line); - - // tinytext/mediumtext -> text - $line = preg_replace('/tinytext/i', 'text', $line); - $line = preg_replace('/mediumtext/i', 'text', $line); - - // change not null datetime field to null valid ones - // (to support remapping of "zero time" to null - $line = preg_replace('/datetime not null/i', 'datetime', $line); - $line = preg_replace('/datetime/i', 'timestamp', $line); - - // double -> numeric - $line = preg_replace('/^double/i', 'numeric', $line); - $line = preg_replace('/(\s*)double/i', '\\1numeric', $line); - // float -> numeric - $line = preg_replace('/^float/i', 'numeric', $line); - $line = preg_replace('/(\s*)float/i', '\\1numeric', $line); - - // unique index(field1,field2) - if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) { - $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line); - } - - // We remove end of requests "AFTER fieldxxx" - $line = preg_replace('/AFTER [a-z0-9_]+/i', '', $line); - - // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX - $line = preg_replace('/ALTER TABLE [a-z0-9_]+ DROP INDEX/i', 'DROP INDEX', $line); - - // Translate order to rename fields - if (preg_match('/ALTER TABLE ([a-z0-9_]+) CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3]; - } - - // Translate order to modify field format - if (preg_match('/ALTER TABLE ([a-z0-9_]+) MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $newreg3 = $reg[3]; - $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ NULL/i', '', $newreg3); - $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3); - $newreg3 = preg_replace('/ DEFAULT \'[0-9a-zA-Z_@]*\'/i', '', $newreg3); - $line .= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3; - // TODO Add alter to set default value or null/not null if there is this in $reg[3] - } - - // alter table add primary key (field1, field2 ...) -> We create a unique index instead as dynamic creation of primary key is not supported - // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity); - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "CREATE UNIQUE INDEX ".$reg[2]." ON ".$reg[1]."(".$reg[3]; - } - - // Translate order to drop foreign keys - // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx; - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) { - $line = "-- ".$line." replaced by --\n"; - $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; - } - - // alter table add [unique] [index] (field1, field2 ...) - // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version) - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) { - $fieldlist = $reg[4]; - $idxname = $reg[3]; - $tablename = $reg[1]; - $line = "-- ".$line." replaced by --\n"; - $line .= "CREATE ".(preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")"; - } - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i', $line, $reg)) { - // Pour l'instant les contraintes ne sont pas créées - dol_syslog(get_class().'::query line emptied'); - $line = 'SELECT 0;'; - } - - //if (preg_match('/rowid\s+.*\s+PRIMARY\s+KEY,/i', $line)) { - //preg_replace('/(rowid\s+.*\s+PRIMARY\s+KEY\s*,)/i', '/* \\1 */', $line); - //} - } - - // Delete using criteria on other table must not declare twice the deleted table - // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable - if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) { - if ($reg[1] == $reg[2]) { // If same table, we remove second one - $line = preg_replace('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', 'DELETE FROM \\1 USING \\3', $line); - } - } - - // Remove () in the tables in FROM if one table - $line = preg_replace('/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i', 'FROM \\1', $line); - //print $line."\n"; - - // Remove () in the tables in FROM if two table - $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); - //print $line."\n"; - - // Remove () in the tables in FROM if two table - $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); - //print $line."\n"; - - //print "type=".$type." newline=".$line."
\n"; - } - - return $line; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + //! Database type + public $type = 'sqlite3'; + //! Database label + const LABEL = 'Sqlite3'; + //! Version min database + const VERSIONMIN = '3.0.0'; + /** @var SQLite3Result Resultset of last query */ + private $_results; + + const WEEK_MONDAY_FIRST = 1; + const WEEK_YEAR = 2; + const WEEK_FIRST_WEEKDAY = 4; + + + /** + * Constructor. + * This create an opened connexion to a database server and eventually to a database + * + * @param string $type Type of database (mysql, pgsql...) + * @param string $host Address of database server + * @param string $user Nom de l'utilisateur autorise + * @param string $pass Mot de passe + * @param string $name Nom de la database + * @param int $port Port of database server + */ + public function __construct($type, $host, $user, $pass, $name = '', $port = 0) + { + global $conf; + + // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static + if (!empty($conf->db->character_set)) $this->forcecharset = $conf->db->character_set; + if (!empty($conf->db->dolibarr_main_db_collation)) $this->forcecollate = $conf->db->dolibarr_main_db_collation; + + $this->database_user = $user; + $this->database_host = $host; + $this->database_port = $port; + + $this->transaction_opened = 0; + + //print "Name DB: $host,$user,$pass,$name
"; + + /*if (! function_exists("sqlite_query")) + { + $this->connected = false; + $this->ok = false; + $this->error="Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver."; + dol_syslog(get_class($this)."::DoliDBSqlite3 : Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver.",LOG_ERR); + return $this->ok; + }*/ + + /*if (! $host) + { + $this->connected = false; + $this->ok = false; + $this->error=$langs->trans("ErrorWrongHostParameter"); + dol_syslog(get_class($this)."::DoliDBSqlite3 : Erreur Connect, wrong host parameters",LOG_ERR); + return $this->ok; + }*/ + + // Essai connexion serveur + // We do not try to connect to database, only to server. Connect to database is done later in constrcutor + $this->db = $this->connect($host, $user, $pass, $name, $port); + + if ($this->db) + { + $this->connected = true; + $this->ok = true; + $this->database_selected = true; + $this->database_name = $name; + + $this->addCustomFunction('IF'); + $this->addCustomFunction('MONTH'); + $this->addCustomFunction('CURTIME'); + $this->addCustomFunction('CURDATE'); + $this->addCustomFunction('WEEK', 1); + $this->addCustomFunction('WEEK', 2); + $this->addCustomFunction('WEEKDAY'); + $this->addCustomFunction('date_format'); + //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + } + else + { + // host, login ou password incorrect + $this->connected = false; + $this->ok = false; + $this->database_selected = false; + $this->database_name = ''; + //$this->error=sqlite_connect_error(); + dol_syslog(get_class($this)."::DoliDBSqlite3 : Error Connect ".$this->error, LOG_ERR); + } + + return $this->ok; + } + + + /** + * Convert a SQL request in Mysql syntax to native syntax + * + * @param string $line SQL request line to convert + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return string SQL request line converted + */ + public static function convertSQLFromMysql($line, $type = 'ddl') + { + // Removed empty line if this is a comment line for SVN tagging + if (preg_match('/^--\s\$Id/i', $line)) { + return ''; + } + // Return line if this is a comment + if (preg_match('/^#/i', $line) || preg_match('/^$/i', $line) || preg_match('/^--/i', $line)) + { + return $line; + } + if ($line != "") + { + if ($type == 'auto') + { + if (preg_match('/ALTER TABLE/i', $line)) $type = 'dml'; + elseif (preg_match('/CREATE TABLE/i', $line)) $type = 'dml'; + elseif (preg_match('/DROP TABLE/i', $line)) $type = 'dml'; + } + + if ($type == 'dml') + { + $line = preg_replace('/\s/', ' ', $line); // Replace tabulation with space + + // we are inside create table statement so lets process datatypes + if (preg_match('/(ISAM|innodb)/i', $line)) { // end of create table sequence + $line = preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb);/i', ');', $line); + $line = preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb);/i', ');', $line); + $line = preg_replace('/,$/', '', $line); + } + + // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..." + if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) { + $newline = preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i', '\\1 \\2 integer PRIMARY KEY AUTOINCREMENT', $line); + //$line = "-- ".$line." replaced by --\n".$newline; + $line = $newline; + } + + // tinyint type conversion + $line = str_replace('tinyint', 'smallint', $line); + + // nuke unsigned + $line = preg_replace('/(int\w+|smallint)\s+unsigned/i', '\\1', $line); + + // blob -> text + $line = preg_replace('/\w*blob/i', 'text', $line); + + // tinytext/mediumtext -> text + $line = preg_replace('/tinytext/i', 'text', $line); + $line = preg_replace('/mediumtext/i', 'text', $line); + + // change not null datetime field to null valid ones + // (to support remapping of "zero time" to null + $line = preg_replace('/datetime not null/i', 'datetime', $line); + $line = preg_replace('/datetime/i', 'timestamp', $line); + + // double -> numeric + $line = preg_replace('/^double/i', 'numeric', $line); + $line = preg_replace('/(\s*)double/i', '\\1numeric', $line); + // float -> numeric + $line = preg_replace('/^float/i', 'numeric', $line); + $line = preg_replace('/(\s*)float/i', '\\1numeric', $line); + + // unique index(field1,field2) + if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) + { + $line = preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i', 'UNIQUE\(\\1\)', $line); + } + + // We remove end of requests "AFTER fieldxxx" + $line = preg_replace('/AFTER [a-z0-9_]+/i', '', $line); + + // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX + $line = preg_replace('/ALTER TABLE [a-z0-9_]+ DROP INDEX/i', 'DROP INDEX', $line); + + // Translate order to rename fields + if (preg_match('/ALTER TABLE ([a-z0-9_]+) CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3]; + } + + // Translate order to modify field format + if (preg_match('/ALTER TABLE ([a-z0-9_]+) MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $newreg3 = $reg[3]; + $newreg3 = preg_replace('/ DEFAULT NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ NOT NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ NULL/i', '', $newreg3); + $newreg3 = preg_replace('/ DEFAULT 0/i', '', $newreg3); + $newreg3 = preg_replace('/ DEFAULT \'[0-9a-zA-Z_@]*\'/i', '', $newreg3); + $line .= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3; + // TODO Add alter to set default value or null/not null if there is this in $reg[3] + } + + // alter table add primary key (field1, field2 ...) -> We create a unique index instead as dynamic creation of primary key is not supported + // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity); + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "CREATE UNIQUE INDEX ".$reg[2]." ON ".$reg[1]."(".$reg[3]; + } + + // Translate order to drop foreign keys + // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx; + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) + { + $line = "-- ".$line." replaced by --\n"; + $line .= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2]; + } + + // alter table add [unique] [index] (field1, field2 ...) + // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version) + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) + { + $fieldlist = $reg[4]; + $idxname = $reg[3]; + $tablename = $reg[1]; + $line = "-- ".$line." replaced by --\n"; + $line .= "CREATE ".(preg_match('/UNIQUE/', $reg[2]) ? 'UNIQUE ' : '')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")"; + } + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i', $line, $reg)) { + // Pour l'instant les contraintes ne sont pas créées + dol_syslog(get_class().'::query line emptied'); + $line = 'SELECT 0;'; + } + + //if (preg_match('/rowid\s+.*\s+PRIMARY\s+KEY,/i', $line)) { + //preg_replace('/(rowid\s+.*\s+PRIMARY\s+KEY\s*,)/i', '/* \\1 */', $line); + //} + } + + // Delete using criteria on other table must not declare twice the deleted table + // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable + if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) + { + if ($reg[1] == $reg[2]) // If same table, we remove second one + { + $line = preg_replace('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', 'DELETE FROM \\1 USING \\3', $line); + } + } + + // Remove () in the tables in FROM if one table + $line = preg_replace('/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i', 'FROM \\1', $line); + //print $line."\n"; + + // Remove () in the tables in FROM if two table + $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); + //print $line."\n"; + + // Remove () in the tables in FROM if two table + $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); + //print $line."\n"; + + //print "type=".$type." newline=".$line."
\n"; + } + + return $line; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -295 +301 @@ - * + * @@ -298,12 +304,12 @@ - */ - public function select_db($database) - { - // phpcs:enable - dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); - // sqlite_select_db() does not exist - //return sqlite_select_db($this->db,$database); - return true; - } - - - /** + */ + public function select_db($database) + { + // phpcs:enable + dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); + // sqlite_select_db() does not exist + //return sqlite_select_db($this->db,$database); + return true; + } + + + /** @@ -311 +317 @@ - * + * @@ -317 +323 @@ - * @return SQLite3|string Database access handler + * @return SQLite3 Database access handler @@ -319,17 +325,15 @@ - */ - public function connect($host, $login, $passwd, $name, $port = 0) - { - global $main_data_dir; - - dol_syslog(get_class($this)."::connect name=".$name, LOG_DEBUG); - - $dir = $main_data_dir; - if (empty($dir)) { - $dir = DOL_DATA_ROOT; - } - // With sqlite, port must be in connect parameters - //if (! $newport) $newport=3306; - $database_name = $dir.'/database_'.$name.'.sdb'; - try { - /*** connect to SQLite database ***/ - //$this->db = new PDO("sqlite:".$dir.'/database_'.$name.'.sdb'); + */ + public function connect($host, $login, $passwd, $name, $port = 0) + { + global $main_data_dir; + + dol_syslog(get_class($this)."::connect name=".$name, LOG_DEBUG); + + $dir = $main_data_dir; + if (empty($dir)) $dir = DOL_DATA_ROOT; + // With sqlite, port must be in connect parameters + //if (! $newport) $newport=3306; + $database_name = $dir.'/database_'.$name.'.sdb'; + try { + /*** connect to SQLite database ***/ + //$this->db = new PDO("sqlite:".$dir.'/database_'.$name.'.sdb'); @@ -337,12 +341,14 @@ - //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); - } catch (Exception $e) { - $this->error = self::LABEL.' '.$e->getMessage().' current dir='.$database_name; - return ''; - } - - //print "Resultat fonction connect: ".$this->db; - return $this->db; - } - - - /** + //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + } + catch (Exception $e) + { + $this->error = self::LABEL.' '.$e->getMessage().' current dir='.$database_name; + return ''; + } + + //print "Resultat fonction connect: ".$this->db; + return $this->db; + } + + + /** @@ -350 +356 @@ - * + * @@ -352,35 +358,162 @@ - */ - public function getVersion() - { - $tmp = $this->db->version(); - return $tmp['versionString']; - } - - /** - * Return version of database client driver - * - * @return string Version string - */ - public function getDriverInfo() - { - return 'sqlite3 php driver'; - } - - - /** - * Close database connexion - * - * @return bool True if disconnect successfull, false otherwise - * @see connect() - */ - public function close() - { - if ($this->db) { - if ($this->transaction_opened > 0) { - dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); - } - $this->connected = false; - $this->db->close(); - unset($this->db); // Clean this->db - return true; - } + */ + public function getVersion() + { + $tmp = $this->db->version(); + return $tmp['versionString']; + } + + /** + * Return version of database client driver + * + * @return string Version string + */ + public function getDriverInfo() + { + return 'sqlite3 php driver'; + } + + + /** + * Close database connexion + * + * @return bool True if disconnect successfull, false otherwise + * @see connect() + */ + public function close() + { + if ($this->db) + { + if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR); + $this->connected = false; + $this->db->close(); + unset($this->db); // Clean this->db + return true; + } + return false; + } + + /** + * Execute a SQL request and return the resultset + * + * @param string $query SQL query string + * @param int $usesavepoint 0=Default mode, 1=Run a savepoint before and a rollbock to savepoint if error (this allow to have some request with errors inside global transactions). + * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. + * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @return SQLite3Result Resultset of answer + */ + public function query($query, $usesavepoint = 0, $type = 'auto') + { + global $conf; + + $ret = null; + + $query = trim($query); + + $this->error = ''; + + // Convert MySQL syntax to SQLite syntax + if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i', $query, $reg)) { + // Ajout d'une clef étrangère à la table + // procédure de remplacement de la table pour ajouter la contrainte + // Exemple : ALTER TABLE llx_adherent ADD CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid) + // -> CREATE TABLE ( ... ,CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid)) + $foreignFields = $reg[5]; + $foreignTable = $reg[4]; + $localfields = $reg[3]; + $constraintname = trim($reg[2]); + $tablename = trim($reg[1]); + + $descTable = $this->db->querySingle("SELECT sql FROM sqlite_master WHERE name='".$tablename."'"); + + // 1- Renommer la table avec un nom temporaire + $this->query('ALTER TABLE '.$tablename.' RENAME TO tmp_'.$tablename); + + // 2- Recréer la table avec la contrainte ajoutée + + // on bricole la requete pour ajouter la contrainte + $descTable = substr($descTable, 0, strlen($descTable) - 1); + $descTable .= ", CONSTRAINT ".$constraintname." FOREIGN KEY (".$localfields.") REFERENCES ".$foreignTable."(".$foreignFields.")"; + + // fermeture de l'instruction + $descTable .= ')'; + + // Création proprement dite de la table + $this->query($descTable); + + // 3- Transférer les données + $this->query('INSERT INTO '.$tablename.' SELECT * FROM tmp_'.$tablename); + + // 4- Supprimer la table temporaire + $this->query('DROP TABLE tmp_'.$tablename); + + // dummy statement + $query = "SELECT 0"; + } else { + $query = $this->convertSQLFromMysql($query, $type); + } + //print "After convertSQLFromMysql:\n".$query."
\n"; + + if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) + { + $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks + dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG); + } + if (empty($query)) return false; // Return false = error if empty request + + // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE) + try { + //$ret = $this->db->exec($query); + $ret = $this->db->query($query); // $ret is a Sqlite3Result + if ($ret) { + $ret->queryString = $query; + } + } + catch (Exception $e) + { + $this->error = $this->db->lastErrorMsg(); + } + + if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) + { + // Si requete utilisateur, on la sauvegarde ainsi que son resultset + if (!is_object($ret) || $this->error) + { + $this->lastqueryerror = $query; + $this->lasterror = $this->error(); + $this->lasterrno = $this->errno(); + + dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); + + $errormsg = get_class($this)."::query SQL Error message: ".$this->lasterror; + + if (preg_match('/[0-9]/', $this->lasterrno)) { + $errormsg .= ' ('.$this->lasterrno.')'; + } + + if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously + dol_syslog(get_class($this)."::query SQL Error message: ".$errormsg, LOG_ERR); + } + $this->lastquery = $query; + $this->_results = $ret; + } + + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Renvoie la ligne courante (comme un objet) pour le curseur resultset + * + * @param SQLite3Result $resultset Curseur de la requete voulue + * @return false|object Object result line or false if KO or end of cursor + */ + public function fetch_object($resultset) + { + // phpcs:enable + // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + //return $resultset->fetch(PDO::FETCH_OBJ); + $ret = $resultset->fetchArray(SQLITE3_ASSOC); + if ($ret) { + return (object) $ret; + } @@ -388,234 +521,89 @@ - } - - /** - * Execute a SQL request and return the resultset - * - * @param string $query SQL query string - * @param int $usesavepoint 0=Default mode, 1=Run a savepoint before and a rollbock to savepoint if error (this allow to have some request with errors inside global transactions). - * Note that with Mysql, this parameter is not used as Myssql can already commit a transaction even if one request is in error, without using savepoints. - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) - * @param int $result_mode Result mode (not used with sqlite) - * @return bool|SQLite3Result Resultset of answer - */ - public function query($query, $usesavepoint = 0, $type = 'auto', $result_mode = 0) - { - global $conf, $dolibarr_main_db_readonly; - - $ret = null; - - $query = trim($query); - - $this->error = ''; - - // Convert MySQL syntax to SQLite syntax - $reg = array(); - if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i', $query, $reg)) { - // Ajout d'une clef étrangère à la table - // procédure de remplacement de la table pour ajouter la contrainte - // Exemple : ALTER TABLE llx_adherent ADD CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid) - // -> CREATE TABLE ( ... ,CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid)) - $foreignFields = $reg[5]; - $foreignTable = $reg[4]; - $localfields = $reg[3]; - $constraintname = trim($reg[2]); - $tablename = trim($reg[1]); - - $descTable = $this->db->querySingle("SELECT sql FROM sqlite_master WHERE name='".$this->escape($tablename)."'"); - - // 1- Renommer la table avec un nom temporaire - $this->query("ALTER TABLE ".$tablename." RENAME TO tmp_".$tablename); - - // 2- Recréer la table avec la contrainte ajoutée - - // on bricole la requete pour ajouter la contrainte - $descTable = substr($descTable, 0, strlen($descTable) - 1); - $descTable .= ", CONSTRAINT ".$constraintname." FOREIGN KEY (".$localfields.") REFERENCES ".$foreignTable."(".$foreignFields.")"; - - // fermeture de l'instruction - $descTable .= ')'; - - // Création proprement dite de la table - $this->query($descTable); - - // 3- Transférer les données - $this->query("INSERT INTO ".$tablename." SELECT * FROM tmp_".$tablename); - - // 4- Supprimer la table temporaire - $this->query("DROP TABLE tmp_".$tablename); - - // dummy statement - $query = "SELECT 0"; - } else { - $query = $this->convertSQLFromMysql($query, $type); - } - //print "After convertSQLFromMysql:\n".$query."
\n"; - - if (!in_array($query, array('BEGIN', 'COMMIT', 'ROLLBACK'))) { - $SYSLOG_SQL_LIMIT = 10000; // limit log to 10kb per line to limit DOS attacks - dol_syslog('sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG); - } - if (empty($query)) { - return false; // Return false = error if empty request - } - - if (!empty($dolibarr_main_db_readonly)) { - if (preg_match('/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) { - $this->lasterror = 'Application in read-only mode'; - $this->lasterrno = 'APPREADONLY'; - $this->lastquery = $query; - return false; - } - } - - // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE) - try { - //$ret = $this->db->exec($query); - $ret = $this->db->query($query); // $ret is a Sqlite3Result - if ($ret) { - $ret->queryString = $query; - } - } catch (Exception $e) { - $this->error = $this->db->lastErrorMsg(); - } - - if (!preg_match("/^COMMIT/i", $query) && !preg_match("/^ROLLBACK/i", $query)) { - // Si requete utilisateur, on la sauvegarde ainsi que son resultset - if (!is_object($ret) || $this->error) { - $this->lastqueryerror = $query; - $this->lasterror = $this->error(); - $this->lasterrno = $this->errno(); - - dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); - - $errormsg = get_class($this)."::query SQL Error message: ".$this->lasterror; - - if (preg_match('/[0-9]/', $this->lasterrno)) { - $errormsg .= ' ('.$this->lasterrno.')'; - } - - if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) { - dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR); // Log of request was not yet done previously - } - dol_syslog(get_class($this)."::query SQL Error message: ".$errormsg, LOG_ERR); - } - $this->lastquery = $query; - $this->_results = $ret; - } - - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Returns the current line (as an object) for the resultset cursor - * - * @param SQLite3Result $resultset Curseur de la requete voulue - * @return false|object Object result line or false if KO or end of cursor - */ - public function fetch_object($resultset) - { - // phpcs:enable - // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - //return $resultset->fetch(PDO::FETCH_OBJ); - $ret = $resultset->fetchArray(SQLITE3_ASSOC); - if ($ret) { - return (object) $ret; - } - return false; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param SQLite3Result $resultset Resultset of request - * @return false|array Array or false if KO or end of cursor - */ - public function fetch_array($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - //return $resultset->fetch(PDO::FETCH_ASSOC); - $ret = $resultset->fetchArray(SQLITE3_ASSOC); - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return datas as an array - * - * @param SQLite3Result $resultset Resultset of request - * @return false|array Array or false if KO or end of cursor - */ - public function fetch_row($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion - if (!is_bool($resultset)) { - if (!is_object($resultset)) { - $resultset = $this->_results; - } - return $resultset->fetchArray(SQLITE3_NUM); - } else { - // si le curseur est un booleen on retourne la valeur 0 - return false; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return number of lines for result of a SELECT - * - * @param SQLite3Result $resultset Resulset of requests - * @return int Nb of lines - * @see affected_rows() - */ - public function num_rows($resultset) - { - // phpcs:enable - // FIXME: SQLite3Result does not have a queryString member - - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - if (preg_match("/^SELECT/i", $resultset->queryString)) { - return $this->db->querySingle("SELECT count(*) FROM (".$resultset->queryString.") q"); - } - return 0; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return number of lines for result of a SELECT - * - * @param SQLite3Result $resultset Resulset of requests - * @return int Nb of lines - * @see affected_rows() - */ - public function affected_rows($resultset) - { - // phpcs:enable - // FIXME: SQLite3Result does not have a queryString member - - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - if (preg_match("/^SELECT/i", $resultset->queryString)) { - return $this->num_rows($resultset); - } - // mysql necessite un link de base pour cette fonction contrairement - // a pqsql qui prend un resultset - return $this->db->changes(); - } - - - /** + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param SQLite3Result $resultset Resultset of request + * @return false|array Array or false if KO or end of cursor + */ + public function fetch_array($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + //return $resultset->fetch(PDO::FETCH_ASSOC); + $ret = $resultset->fetchArray(SQLITE3_ASSOC); + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return datas as an array + * + * @param SQLite3Result $resultset Resultset of request + * @return false|array Array or false if KO or end of cursor + */ + public function fetch_row($resultset) + { + // phpcs:enable + // If resultset not provided, we take the last used by connexion + if (!is_bool($resultset)) + { + if (!is_object($resultset)) { $resultset = $this->_results; } + return $resultset->fetchArray(SQLITE3_NUM); + } + else + { + // si le curseur est un booleen on retourne la valeur 0 + return false; + } + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return number of lines for result of a SELECT + * + * @param SQLite3Result $resultset Resulset of requests + * @return int Nb of lines + * @see affected_rows() + */ + public function num_rows($resultset) + { + // phpcs:enable + // FIXME: SQLite3Result does not have a queryString member + + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + if (preg_match("/^SELECT/i", $resultset->queryString)) { + return $this->db->querySingle("SELECT count(*) FROM (".$resultset->queryString.") q"); + } + return 0; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return number of lines for result of a SELECT + * + * @param SQLite3Result $resultset Resulset of requests + * @return int Nb of lines + * @see affected_rows() + */ + public function affected_rows($resultset) + { + // phpcs:enable + // FIXME: SQLite3Result does not have a queryString member + + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + if (preg_match("/^SELECT/i", $resultset->queryString)) { + return $this->num_rows($resultset); + } + // mysql necessite un link de base pour cette fonction contrairement + // a pqsql qui prend un resultset + return $this->db->changes(); + } + + + /** @@ -623 +611 @@ - * + * @@ -626,14 +614,10 @@ - */ - public function free($resultset = null) - { - // If resultset not provided, we take the last used by connexion - if (!is_object($resultset)) { - $resultset = $this->_results; - } - // Si resultset en est un, on libere la memoire - if ($resultset && is_object($resultset)) { - $resultset->finalize(); - } - } - - /** + */ + public function free($resultset = null) + { + // If resultset not provided, we take the last used by connexion + if (!is_object($resultset)) { $resultset = $this->_results; } + // Si resultset en est un, on libere la memoire + if ($resultset && is_object($resultset)) $resultset->finalize(); + } + + /** @@ -641 +625 @@ - * + * @@ -644,105 +628,91 @@ - */ - public function escape($stringtoencode) - { - return Sqlite3::escapeString($stringtoencode); - } - - /** - * Escape a string to insert data into a like - * - * @param string $stringtoencode String to escape - * @return string String escaped - */ - public function escapeforlike($stringtoencode) - { - return str_replace(array('\\', '_', '%'), array('\\\\', '\_', '\%'), (string) $stringtoencode); - } - - /** - * Renvoie le code erreur generique de l'operation precedente. - * - * @return string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) - */ - public function errno() - { - if (!$this->connected) { - // Si il y a eu echec de connexion, $this->db n'est pas valide. - return 'DB_ERROR_FAILED_TO_CONNECT'; - } else { - // Constants to convert error code to a generic Dolibarr error code - /*$errorcode_map = array( - 1004 => 'DB_ERROR_CANNOT_CREATE', - 1005 => 'DB_ERROR_CANNOT_CREATE', - 1006 => 'DB_ERROR_CANNOT_CREATE', - 1007 => 'DB_ERROR_ALREADY_EXISTS', - 1008 => 'DB_ERROR_CANNOT_DROP', - 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP', - 1044 => 'DB_ERROR_ACCESSDENIED', - 1046 => 'DB_ERROR_NODBSELECTED', - 1048 => 'DB_ERROR_CONSTRAINT', - 'HY000' => 'DB_ERROR_TABLE_ALREADY_EXISTS', - 1051 => 'DB_ERROR_NOSUCHTABLE', - 1054 => 'DB_ERROR_NOSUCHFIELD', - 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', - 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', - 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS', - 1064 => 'DB_ERROR_SYNTAX', - 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS', - 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY', - 1091 => 'DB_ERROR_NOSUCHFIELD', - 1100 => 'DB_ERROR_NOT_LOCKED', - 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW', - 1146 => 'DB_ERROR_NOSUCHTABLE', - 1216 => 'DB_ERROR_NO_PARENT', - 1217 => 'DB_ERROR_CHILD_EXISTS', - 1451 => 'DB_ERROR_CHILD_EXISTS' - ); - - if (isset($errorcode_map[$this->db->errorCode()])) - { - return $errorcode_map[$this->db->errorCode()]; - }*/ - $errno = $this->db->lastErrorCode(); - if ($errno == 'HY000' || $errno == 0) { - if (preg_match('/table.*already exists/i', $this->error)) { - return 'DB_ERROR_TABLE_ALREADY_EXISTS'; - } elseif (preg_match('/index.*already exists/i', $this->error)) { - return 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'; - } elseif (preg_match('/syntax error/i', $this->error)) { - return 'DB_ERROR_SYNTAX'; - } - } - if ($errno == '23000') { - if (preg_match('/column.* not unique/i', $this->error)) { - return 'DB_ERROR_RECORD_ALREADY_EXISTS'; - } elseif (preg_match('/PRIMARY KEY must be unique/i', $this->error)) { - return 'DB_ERROR_RECORD_ALREADY_EXISTS'; - } - } - if ($errno > 1) { - // TODO Voir la liste des messages d'erreur - } - - return ($errno ? 'DB_ERROR_'.$errno : '0'); - } - } - - /** - * Renvoie le texte de l'erreur mysql de l'operation precedente. - * - * @return string Error text - */ - public function error() - { - if (!$this->connected) { - // Si il y a eu echec de connexion, $this->db n'est pas valide pour sqlite_error. - return 'Not connected. Check setup parameters in conf/conf.php file and your sqlite version'; - } else { - return $this->error; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Get last ID after an insert INSERT - * + */ + public function escape($stringtoencode) + { + return Sqlite3::escapeString($stringtoencode); + } + + /** + * Renvoie le code erreur generique de l'operation precedente. + * + * @return string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) + */ + public function errno() + { + if (!$this->connected) { + // Si il y a eu echec de connexion, $this->db n'est pas valide. + return 'DB_ERROR_FAILED_TO_CONNECT'; + } + else { + // Constants to convert error code to a generic Dolibarr error code + /*$errorcode_map = array( + 1004 => 'DB_ERROR_CANNOT_CREATE', + 1005 => 'DB_ERROR_CANNOT_CREATE', + 1006 => 'DB_ERROR_CANNOT_CREATE', + 1007 => 'DB_ERROR_ALREADY_EXISTS', + 1008 => 'DB_ERROR_CANNOT_DROP', + 1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP', + 1044 => 'DB_ERROR_ACCESSDENIED', + 1046 => 'DB_ERROR_NODBSELECTED', + 1048 => 'DB_ERROR_CONSTRAINT', + 'HY000' => 'DB_ERROR_TABLE_ALREADY_EXISTS', + 1051 => 'DB_ERROR_NOSUCHTABLE', + 1054 => 'DB_ERROR_NOSUCHFIELD', + 1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS', + 1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS', + 1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS', + 1064 => 'DB_ERROR_SYNTAX', + 1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS', + 1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY', + 1091 => 'DB_ERROR_NOSUCHFIELD', + 1100 => 'DB_ERROR_NOT_LOCKED', + 1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW', + 1146 => 'DB_ERROR_NOSUCHTABLE', + 1216 => 'DB_ERROR_NO_PARENT', + 1217 => 'DB_ERROR_CHILD_EXISTS', + 1451 => 'DB_ERROR_CHILD_EXISTS' + ); + + if (isset($errorcode_map[$this->db->errorCode()])) + { + return $errorcode_map[$this->db->errorCode()]; + }*/ + $errno = $this->db->lastErrorCode(); + if ($errno == 'HY000' || $errno == 0) + { + if (preg_match('/table.*already exists/i', $this->error)) return 'DB_ERROR_TABLE_ALREADY_EXISTS'; + elseif (preg_match('/index.*already exists/i', $this->error)) return 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'; + elseif (preg_match('/syntax error/i', $this->error)) return 'DB_ERROR_SYNTAX'; + } + if ($errno == '23000') + { + if (preg_match('/column.* not unique/i', $this->error)) return 'DB_ERROR_RECORD_ALREADY_EXISTS'; + elseif (preg_match('/PRIMARY KEY must be unique/i', $this->error)) return 'DB_ERROR_RECORD_ALREADY_EXISTS'; + } + if ($errno > 1) { + // TODO Voir la liste des messages d'erreur + } + + return ($errno ? 'DB_ERROR_'.$errno : '0'); + } + } + + /** + * Renvoie le texte de l'erreur mysql de l'operation precedente. + * + * @return string Error text + */ + public function error() + { + if (!$this->connected) { + // Si il y a eu echec de connexion, $this->db n'est pas valide pour sqlite_error. + return 'Not connected. Check setup parameters in conf/conf.php file and your sqlite version'; + } + else { + return $this->error; + } + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Get last ID after an insert INSERT + * @@ -752,72 +722,80 @@ - */ - public function last_insert_id($tab, $fieldid = 'rowid') - { - // phpcs:enable - return $this->db->lastInsertRowId(); - } - - /** - * Encrypt sensitive data in database - * Warning: This function includes the escape and add the SQL simple quotes on strings. - * - * @param string $fieldorvalue Field name or value to encrypt - * @param int $withQuotes Return string including the SQL simple quotes. This param must always be 1 (Value 0 is bugged and deprecated). - * @return string XXX(field) or XXX('value') or field or 'value' - */ - public function encrypt($fieldorvalue, $withQuotes = 1) - { - global $conf; - - // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) - $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0); - - //Encryption key - $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); - - $escapedstringwithquotes = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : ""); - - if ($cryptType && !empty($cryptKey)) { - if ($cryptType == 2) { - $escapedstringwithquotes = "AES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')"; - } elseif ($cryptType == 1) { - $escapedstringwithquotes = "DES_ENCRYPT(".$escapedstringwithquotes.", '".$this->escape($cryptKey)."')"; - } - } - - return $escapedstringwithquotes; - } - - /** - * Decrypt sensitive data in database - * - * @param string $value Value to decrypt - * @return string Decrypted value if used - */ - public function decrypt($value) - { - global $conf; - - // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) - $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); - - //Encryption key - $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); - - $return = $value; - - if ($cryptType && !empty($cryptKey)) { - if ($cryptType == 2) { - $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')'; - } elseif ($cryptType == 1) { - $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')'; - } - } - - return $return; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Return connexion ID - * + */ + public function last_insert_id($tab, $fieldid = 'rowid') + { + // phpcs:enable + return $this->db->lastInsertRowId(); + } + + /** + * Encrypt sensitive data in database + * Warning: This function includes the escape, so it must use direct value + * + * @param string $fieldorvalue Field name or value to encrypt + * @param int $withQuotes Return string with quotes + * @return string XXX(field) or XXX('value') or field or 'value' + */ + public function encrypt($fieldorvalue, $withQuotes = 0) + { + global $conf; + + // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) + $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); + + //Encryption key + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + + $return = ($withQuotes ? "'" : "").$this->escape($fieldorvalue).($withQuotes ? "'" : ""); + + if ($cryptType && !empty($cryptKey)) + { + if ($cryptType == 2) + { + $return = 'AES_ENCRYPT('.$return.',\''.$cryptKey.'\')'; + } + elseif ($cryptType == 1) + { + $return = 'DES_ENCRYPT('.$return.',\''.$cryptKey.'\')'; + } + } + + return $return; + } + + /** + * Decrypt sensitive data in database + * + * @param string $value Value to decrypt + * @return string Decrypted value if used + */ + public function decrypt($value) + { + global $conf; + + // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption) + $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0); + + //Encryption key + $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey : ''); + + $return = $value; + + if ($cryptType && !empty($cryptKey)) + { + if ($cryptType == 2) + { + $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')'; + } + elseif ($cryptType == 1) + { + $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')'; + } + } + + return $return; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Return connexion ID + * @@ -825,10 +803,10 @@ - */ - public function DDLGetConnectId() - { - // phpcs:enable - return '?'; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLGetConnectId() + { + // phpcs:enable + return '?'; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -838 +816 @@ - * + * @@ -844,25 +822,27 @@ - */ - public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') - { - // phpcs:enable - if (empty($charset)) { - $charset = $this->forcecharset; - } - if (empty($collation)) { - $collation = $this->forcecollate; - } - - // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci - $sql = "CREATE DATABASE ".$this->escape($database); - $sql .= " DEFAULT CHARACTER SET ".$this->escape($charset)." DEFAULT COLLATE ".$this->escape($collation); - - dol_syslog($sql, LOG_DEBUG); - $ret = $this->query($sql); - - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List tables into a database - * + */ + public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') + { + // phpcs:enable + if (empty($charset)) $charset = $this->forcecharset; + if (empty($collation)) $collation = $this->forcecollate; + + // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci + $sql = 'CREATE DATABASE '.$database; + $sql .= ' DEFAULT CHARACTER SET '.$charset.' DEFAULT COLLATE '.$collation; + + dol_syslog($sql, LOG_DEBUG); + $ret = $this->query($sql); + if (!$ret) + { + // We try again for compatibility with Mysql < 4.1.1 + $sql = 'CREATE DATABASE '.$database; + $ret = $this->query($sql); + dol_syslog($sql, LOG_DEBUG); + } + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * List tables into a database + * @@ -872,61 +852,25 @@ - */ - public function DDLListTables($database, $table = '') - { - // phpcs:enable - $listtables = array(); - - $like = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $like = "LIKE '".$this->escape($tmptable)."'"; - } - $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database); - - $sql = "SHOW TABLES FROM ".$tmpdatabase." ".$like.";"; - //print $sql; - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row[0]; - } - } - return $listtables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List tables into a database with table type - * - * @param string $database Name of database - * @param string $table Name of table filter ('xxx%') - * @return array List of tables in an array - */ - public function DDLListTablesFull($database, $table = '') - { - // phpcs:enable - $listtables = array(); - - $like = ''; - if ($table) { - $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i', '', $table); - - $like = "LIKE '".$this->escape($tmptable)."'"; - } - $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i', '', $database); - - $sql = "SHOW FULL TABLES FROM ".$tmpdatabase." ".$like.";"; - //print $sql; - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $listtables[] = $row; - } - } - return $listtables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * List information of columns into a table. - * + */ + public function DDLListTables($database, $table = '') + { + // phpcs:enable + $listtables = array(); + + $like = ''; + if ($table) $like = "LIKE '".$table."'"; + $sql = "SHOW TABLES FROM ".$database." ".$like.";"; + //print $sql; + $result = $this->query($sql); + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $listtables[] = $row[0]; + } + } + return $listtables; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * List information of columns into a table. + * @@ -936,22 +880,22 @@ - */ - public function DDLInfoTable($table) - { - // phpcs:enable - $infotables = array(); - - $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); - - $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";"; - - dol_syslog($sql, LOG_DEBUG); - $result = $this->query($sql); - if ($result) { - while ($row = $this->fetch_row($result)) { - $infotables[] = $row; - } - } - return $infotables; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLInfoTable($table) + { + // phpcs:enable + $infotables = array(); + + $sql = "SHOW FULL COLUMNS FROM ".$table.";"; + + dol_syslog($sql, LOG_DEBUG); + $result = $this->query($sql); + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $infotables[] = $row; + } + } + return $infotables; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -959 +903 @@ - * + * @@ -967,91 +911,89 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) - { - // phpcs:enable - // FIXME: $fulltext_keys parameter is unused - - // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra - // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); - $sql = "create table ".$table."("; - $i = 0; - foreach ($fields as $field_name => $field_desc) { - $sqlfields[$i] = $field_name." "; - $sqlfields[$i] .= $field_desc['type']; - if (preg_match("/^[^\s]/i", $field_desc['value'])) { - $sqlfields[$i] .= "(".$field_desc['value'].")"; - } elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) { - $sqlfields[$i] .= " ".$field_desc['attribute']; - } elseif (preg_match("/^[^\s]/i", $field_desc['default'])) { - if (preg_match("/null/i", $field_desc['default'])) { - $sqlfields[$i] .= " default ".$field_desc['default']; - } else { - $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'"; - } - } elseif (preg_match("/^[^\s]/i", $field_desc['null'])) { - $sqlfields[$i] .= " ".$field_desc['null']; - } elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) { - $sqlfields[$i] .= " ".$field_desc['extra']; - } - $i++; - } - if ($primary_key != "") { - $pk = "primary key(".$primary_key.")"; - } - - if (is_array($unique_keys)) { - $i = 0; - foreach ($unique_keys as $key => $value) { - $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')"; - $i++; - } - } - if (is_array($keys)) { - $i = 0; - foreach ($keys as $key => $value) { - $sqlk[$i] = "KEY ".$key." (".$value.")"; - $i++; - } - } - $sql .= implode(',', $sqlfields); - if ($primary_key != "") { - $sql .= ",".$pk; - } - if (is_array($unique_keys)) { - $sql .= ",".implode(',', $sqluq); - } - if (is_array($keys)) { - $sql .= ",".implode(',', $sqlk); - } - $sql .= ") type=".$type; - - dol_syslog($sql, LOG_DEBUG); - if (!$this -> query($sql)) { - return -1; - } - return 1; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Drop a table into database - * - * @param string $table Name of table - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLDropTable($table) - { - // phpcs:enable - $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i', '', $table); - - $sql = "DROP TABLE ".$tmptable; - - if (!$this->query($sql)) { - return -1; - } else { - return 1; - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null) + { + // phpcs:enable + // FIXME: $fulltext_keys parameter is unused + + // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra + // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); + $sql = "create table ".$table."("; + $i = 0; + foreach ($fields as $field_name => $field_desc) + { + $sqlfields[$i] = $field_name." "; + $sqlfields[$i] .= $field_desc['type']; + if (preg_match("/^[^\s]/i", $field_desc['value'])) + $sqlfields[$i] .= "(".$field_desc['value'].")"; + elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) + $sqlfields[$i] .= " ".$field_desc['attribute']; + elseif (preg_match("/^[^\s]/i", $field_desc['default'])) + { + if (preg_match("/null/i", $field_desc['default'])) + $sqlfields[$i] .= " default ".$field_desc['default']; + else + $sqlfields[$i] .= " default '".$field_desc['default']."'"; + } + elseif (preg_match("/^[^\s]/i", $field_desc['null'])) + $sqlfields[$i] .= " ".$field_desc['null']; + + elseif (preg_match("/^[^\s]/i", $field_desc['extra'])) + $sqlfields[$i] .= " ".$field_desc['extra']; + $i++; + } + if ($primary_key != "") + $pk = "primary key(".$primary_key.")"; + + if (is_array($unique_keys)) + { + $i = 0; + foreach ($unique_keys as $key => $value) + { + $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')"; + $i++; + } + } + if (is_array($keys)) + { + $i = 0; + foreach ($keys as $key => $value) + { + $sqlk[$i] = "KEY ".$key." (".$value.")"; + $i++; + } + } + $sql .= implode(',', $sqlfields); + if ($primary_key != "") + $sql .= ",".$pk; + if (is_array($unique_keys)) + $sql .= ",".implode(',', $sqluq); + if (is_array($keys)) + $sql .= ",".implode(',', $sqlk); + $sql .= ") type=".$type; + + dol_syslog($sql, LOG_DEBUG); + if (!$this -> query($sql)) + return -1; + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Drop a table into database + * + * @param string $table Name of table + * @return int <0 if KO, >=0 if OK + */ + public function DDLDropTable($table) + { + // phpcs:enable + $sql = "DROP TABLE ".$table; + + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1059 +1001 @@ - * + * @@ -1062,14 +1004,14 @@ - * @return bool|SQLite3Result Resource - */ - public function DDLDescTable($table, $field = "") - { - // phpcs:enable - $sql = "DESC ".$table." ".$field; - - dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG); - $this->_results = $this->query($sql); - return $this->_results; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return SQLite3Result Resource + */ + public function DDLDescTable($table, $field = "") + { + // phpcs:enable + $sql = "DESC ".$table." ".$field; + + dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG); + $this->_results = $this->query($sql); + return $this->_results; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1077 +1019 @@ - * + * @@ -1082,41 +1024,39 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLAddField($table, $field_name, $field_desc, $field_position = "") - { - // phpcs:enable - // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra - // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); - $sql = "ALTER TABLE ".$table." ADD ".$field_name." "; - $sql .= $field_desc['type']; - if (preg_match("/^[^\s]/i", $field_desc['value'])) { - if (!in_array($field_desc['type'], array('date', 'datetime'))) { - $sql .= "(".$field_desc['value'].")"; - } - } - if (preg_match("/^[^\s]/i", $field_desc['attribute'])) { - $sql .= " ".$field_desc['attribute']; - } - if (preg_match("/^[^\s]/i", $field_desc['null'])) { - $sql .= " ".$field_desc['null']; - } - if (preg_match("/^[^\s]/i", $field_desc['default'])) { - if (preg_match("/null/i", $field_desc['default'])) { - $sql .= " default ".$field_desc['default']; - } else { - $sql .= " default '".$this->escape($field_desc['default'])."'"; - } - } - if (preg_match("/^[^\s]/i", $field_desc['extra'])) { - $sql .= " ".$field_desc['extra']; - } - $sql .= " ".$field_position; - - dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG); - if (!$this->query($sql)) { - return -1; - } - return 1; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >0 if OK + */ + public function DDLAddField($table, $field_name, $field_desc, $field_position = "") + { + // phpcs:enable + // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra + // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment'); + $sql = "ALTER TABLE ".$table." ADD ".$field_name." "; + $sql .= $field_desc['type']; + if (preg_match("/^[^\s]/i", $field_desc['value'])) + if (!in_array($field_desc['type'], array('date', 'datetime'))) + { + $sql .= "(".$field_desc['value'].")"; + } + if (preg_match("/^[^\s]/i", $field_desc['attribute'])) + $sql .= " ".$field_desc['attribute']; + if (preg_match("/^[^\s]/i", $field_desc['null'])) + $sql .= " ".$field_desc['null']; + if (preg_match("/^[^\s]/i", $field_desc['default'])) + { + if (preg_match("/null/i", $field_desc['default'])) + $sql .= " default ".$field_desc['default']; + else + $sql .= " default '".$field_desc['default']."'"; + } + if (preg_match("/^[^\s]/i", $field_desc['extra'])) + $sql .= " ".$field_desc['extra']; + $sql .= " ".$field_position; + + dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG); + if (!$this->query($sql)) + { + return -1; + } + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1124 +1064 @@ - * + * @@ -1128,20 +1068,19 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLUpdateField($table, $field_name, $field_desc) - { - // phpcs:enable - $sql = "ALTER TABLE ".$table; - $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type']; - if ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar') { - $sql .= "(".$field_desc['value'].")"; - } - - dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG); - if (!$this->query($sql)) { - return -1; - } - return 1; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + * @return int <0 if KO, >0 if OK + */ + public function DDLUpdateField($table, $field_name, $field_desc) + { + // phpcs:enable + $sql = "ALTER TABLE ".$table; + $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type']; + if ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar') { + $sql .= "(".$field_desc['value'].")"; + } + + dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG); + if (!$this->query($sql)) + return -1; + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** @@ -1149 +1088 @@ - * + * @@ -1152,20 +1091,20 @@ - * @return int Return integer <0 if KO, >0 if OK - */ - public function DDLDropField($table, $field_name) - { - // phpcs:enable - $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i', '', $field_name); - - $sql = "ALTER TABLE ".$table." DROP COLUMN `".$tmp_field_name."`"; - if (!$this->query($sql)) { - $this->error = $this->lasterror(); - return -1; - } - return 1; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * Create a user and privileges to connect to database (even if database does not exists yet) - * + * @return int <0 if KO, >0 if OK + */ + public function DDLDropField($table, $field_name) + { + // phpcs:enable + $sql = "ALTER TABLE ".$table." DROP COLUMN `".$field_name."`"; + dol_syslog(get_class($this)."::DDLDropField ".$sql, LOG_DEBUG); + if (!$this->query($sql)) + { + $this->error = $this->lasterror(); + return -1; + } + return 1; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * Create a user and privileges to connect to database (even if database does not exists yet) + * @@ -1176,38 +1115,41 @@ - * @return int Return integer <0 if KO, >=0 if OK - */ - public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) - { - // phpcs:enable - $sql = "INSERT INTO user "; - $sql .= "(Host,User,password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)"; - $sql .= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_user)."',password('".addslashes($dolibarr_main_db_pass)."')"; - $sql .= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')"; - - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log - $resql = $this->query($sql); - if (!$resql) { - return -1; - } - - $sql = "INSERT INTO db "; - $sql .= "(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)"; - $sql .= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_name)."','".addslashes($dolibarr_main_db_user)."'"; - $sql .= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')"; - - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); - $resql = $this->query($sql); - if (!$resql) { - return -1; - } - - $sql = "FLUSH Privileges"; - - dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); - $resql = $this->query($sql); - if (!$resql) { - return -1; - } - return 1; - } - - /** + * @return int <0 if KO, >=0 if OK + */ + public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) + { + // phpcs:enable + $sql = "INSERT INTO user "; + $sql .= "(Host,User,password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)"; + $sql .= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_user)."',password('".addslashes($dolibarr_main_db_pass)."')"; + $sql .= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')"; + + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log + $resql = $this->query($sql); + if (!$resql) + { + return -1; + } + + $sql = "INSERT INTO db "; + $sql .= "(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)"; + $sql .= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_name)."','".addslashes($dolibarr_main_db_user)."'"; + $sql .= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')"; + + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); + $resql = $this->query($sql); + if (!$resql) + { + return -1; + } + + $sql = "FLUSH Privileges"; + + dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); + $resql = $this->query($sql); + if (!$resql) + { + return -1; + } + return 1; + } + + /** @@ -1215 +1157 @@ - * + * @@ -1217,7 +1159,7 @@ - */ - public function getDefaultCharacterSetDatabase() - { - return 'UTF-8'; - } - - /** + */ + public function getDefaultCharacterSetDatabase() + { + return 'UTF-8'; + } + + /** @@ -1225 +1167 @@ - * + * @@ -1227,11 +1169,11 @@ - */ - public function getListOfCharacterSet() - { - $liste = array(); - $i = 0; - $liste[$i]['charset'] = 'UTF-8'; - $liste[$i]['description'] = 'UTF-8'; - return $liste; - } - - /** + */ + public function getListOfCharacterSet() + { + $liste = array(); + $i = 0; + $liste[$i]['charset'] = 'UTF-8'; + $liste[$i]['description'] = 'UTF-8'; + return $liste; + } + + /** @@ -1239 +1181 @@ - * + * @@ -1241,7 +1183,7 @@ - */ - public function getDefaultCollationDatabase() - { - return 'UTF-8'; - } - - /** + */ + public function getDefaultCollationDatabase() + { + return 'UTF-8'; + } + + /** @@ -1249 +1191 @@ - * + * @@ -1251,11 +1193,11 @@ - */ - public function getListOfCollation() - { - $liste = array(); - $i = 0; - $liste[$i]['charset'] = 'UTF-8'; - $liste[$i]['description'] = 'UTF-8'; - return $liste; - } - - /** + */ + public function getListOfCollation() + { + $liste = array(); + $i = 0; + $liste[$i]['charset'] = 'UTF-8'; + $liste[$i]['description'] = 'UTF-8'; + return $liste; + } + + /** @@ -1263 +1205 @@ - * + * @@ -1265,16 +1207,17 @@ - */ - public function getPathOfDump() - { - // FIXME: not for SQLite - $fullpathofdump = '/pathtomysqldump/mysqldump'; - - $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['Value']; - $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump'; - } - return $fullpathofdump; - } - - /** + */ + public function getPathOfDump() + { + // FIXME: not for SQLite + $fullpathofdump = '/pathtomysqldump/mysqldump'; + + $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['Value']; + $fullpathofdump = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysqldump'; + } + return $fullpathofdump; + } + + /** @@ -1282 +1225 @@ - * + * @@ -1284,18 +1227,19 @@ - */ - public function getPathOfRestore() - { - // FIXME: not for SQLite - $fullpathofimport = '/pathtomysql/mysql'; - - $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); - if ($resql) { - $liste = $this->fetch_array($resql); - $basedir = $liste['Value']; - $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql'; - } - return $fullpathofimport; - } - - /** - * Return value of server parameters - * + */ + public function getPathOfRestore() + { + // FIXME: not for SQLite + $fullpathofimport = '/pathtomysql/mysql'; + + $resql = $this->query('SHOW VARIABLES LIKE \'basedir\''); + if ($resql) + { + $liste = $this->fetch_array($resql); + $basedir = $liste['Value']; + $fullpathofimport = $basedir.(preg_match('/\/$/', $basedir) ? '' : '/').'bin/mysql'; + } + return $fullpathofimport; + } + + /** + * Return value of server parameters + * @@ -1304,45 +1248,47 @@ - */ - public function getServerParametersValues($filter = '') - { - $result = array(); - static $pragmas; - if (!isset($pragmas)) { - // Définition de la liste des pragmas utilisés qui ne retournent qu'une seule valeur - // indépendante de la base de données. - // cf. http://www.sqlite.org/pragma.html - $pragmas = array( - 'application_id', 'auto_vacuum', 'automatic_index', 'busy_timeout', 'cache_size', - 'cache_spill', 'case_sensitive_like', 'checkpoint_fullsync', 'collation_list', - 'compile_options', 'data_version', /*'database_list',*/ - 'defer_foreign_keys', 'encoding', 'foreign_key_check', 'freelist_count', - 'full_column_names', 'fullsync', 'ingore_check_constraints', 'integrity_check', - 'journal_mode', 'journal_size_limit', 'legacy_file_format', 'locking_mode', - 'max_page_count', 'page_count', 'page_size', 'parser_trace', - 'query_only', 'quick_check', 'read_uncommitted', 'recursive_triggers', - 'reverse_unordered_selects', 'schema_version', 'user_version', - 'secure_delete', 'short_column_names', 'shrink_memory', 'soft_heap_limit', - 'synchronous', 'temp_store', /*'temp_store_directory',*/ 'threads', - 'vdbe_addoptrace', 'vdbe_debug', 'vdbe_listing', 'vdbe_trace', - 'wal_autocheckpoint', - ); - } - - // TODO prendre en compte le filtre - foreach ($pragmas as $var) { - $sql = "PRAGMA $var"; - $resql = $this->query($sql); - if ($resql) { - $obj = $this->fetch_row($resql); - //dol_syslog(get_class($this)."::select_db getServerParametersValues $var=". print_r($obj, true), LOG_DEBUG); - $result[$var] = $obj[0]; - } else { - // TODO Récupérer le message - $result[$var] = 'FAIL'; - } - } - return $result; - } - - /** - * Return value of server status - * + */ + public function getServerParametersValues($filter = '') + { + $result = array(); + static $pragmas; + if (!isset($pragmas)) { + // Définition de la liste des pragmas utilisés qui ne retournent qu'une seule valeur + // indépendante de la base de données. + // cf. http://www.sqlite.org/pragma.html + $pragmas = array( + 'application_id', 'auto_vacuum', 'automatic_index', 'busy_timeout', 'cache_size', + 'cache_spill', 'case_sensitive_like', 'checkpoint_fullsync', 'collation_list', + 'compile_options', 'data_version', /*'database_list',*/ + 'defer_foreign_keys', 'encoding', 'foreign_key_check', 'freelist_count', + 'full_column_names', 'fullsync', 'ingore_check_constraints', 'integrity_check', + 'journal_mode', 'journal_size_limit', 'legacy_file_format', 'locking_mode', + 'max_page_count', 'page_count', 'page_size', 'parser_trace', + 'query_only', 'quick_check', 'read_uncommitted', 'recursive_triggers', + 'reverse_unordered_selects', 'schema_version', 'user_version', + 'secure_delete', 'short_column_names', 'shrink_memory', 'soft_heap_limit', + 'synchronous', 'temp_store', /*'temp_store_directory',*/ 'threads', + 'vdbe_addoptrace', 'vdbe_debug', 'vdbe_listing', 'vdbe_trace', + 'wal_autocheckpoint', + ); + } + + // TODO prendre en compte le filtre + foreach ($pragmas as $var) { + $sql = "PRAGMA $var"; + $resql = $this->query($sql); + if ($resql) + { + $obj = $this->fetch_row($resql); + //dol_syslog(get_class($this)."::select_db getServerParametersValues $var=". print_r($obj, true), LOG_DEBUG); + $result[$var] = $obj[0]; + } + else { + // TODO Récupérer le message + $result[$var] = 'FAIL'; + } + } + return $result; + } + + /** + * Return value of server status + * @@ -1351,97 +1297,97 @@ - */ - public function getServerStatusValues($filter = '') - { - $result = array(); - /* - $sql='SHOW STATUS'; - if ($filter) $sql.=" LIKE '".$this->escape($filter)."'"; - $resql=$this->query($sql); - if ($resql) - { - while ($obj=$this->fetch_object($resql)) $result[$obj->Variable_name]=$obj->Value; - } - */ - - return $result; - } - - /** - * Permet le chargement d'une fonction personnalisee dans le moteur de base de donnees. - * Note: le nom de la fonction personnalisee est prefixee par 'db'. La fonction doit être - * statique et publique. Le nombre de parametres est determine automatiquement. - * - * @param string $name Le nom de la fonction a definir dans Sqlite - * @param int $arg_count Arg count - * @return void - */ - private function addCustomFunction($name, $arg_count = -1) - { - if ($this->db) { - $newname = preg_replace('/_/', '', $name); - $localname = __CLASS__.'::db'.$newname; - $reflectClass = new ReflectionClass(__CLASS__); - $reflectFunction = $reflectClass->getMethod('db'.$newname); - if ($arg_count < 0) { - $arg_count = $reflectFunction->getNumberOfParameters(); - } - if (!$this->db->createFunction($name, $localname, $arg_count)) { - $this->error = "unable to create custom function '$name'"; - } - } - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * calc_daynr - * - * @param int $year Year - * @param int $month Month - * @param int $day Day - * @return int Formatted date - */ - private static function calc_daynr($year, $month, $day) - { - // phpcs:enable - $y = $year; - if ($y == 0 && $month == 0) { - return 0; - } - $num = (365 * $y + 31 * ($month - 1) + $day); - if ($month <= 2) { - $y--; - } else { - $num -= floor(($month * 4 + 23) / 10); - } - $temp = floor(($y / 100 + 1) * 3 / 4); - return $num + floor($y / 4) - $temp; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * calc_weekday - * - * @param int $daynr ??? - * @param bool $sunday_first_day_of_week ??? - * @return int - */ - private static function calc_weekday($daynr, $sunday_first_day_of_week) - { - // phpcs:enable - $ret = floor(($daynr + 5 + ($sunday_first_day_of_week ? 1 : 0)) % 7); - return $ret; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** - * calc_days_in_year - * - * @param string $year Year - * @return int Nb of days in year - */ - private static function calc_days_in_year($year) - { - // phpcs:enable - return (($year & 3) == 0 && ($year % 100 || ($year % 400 == 0 && $year)) ? 366 : 365); - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + */ + public function getServerStatusValues($filter = '') + { + $result = array(); + /* + $sql='SHOW STATUS'; + if ($filter) $sql.=" LIKE '".$this->escape($filter)."'"; + $resql=$this->query($sql); + if ($resql) + { + while ($obj=$this->fetch_object($resql)) $result[$obj->Variable_name]=$obj->Value; + } + */ + + return $result; + } + + /** + * Permet le chargement d'une fonction personnalisee dans le moteur de base de donnees. + * Note: le nom de la fonction personnalisee est prefixee par 'db'. La fonction doit être + * statique et publique. Le nombre de parametres est determine automatiquement. + * + * @param string $name Le nom de la fonction a definir dans Sqlite + * @param int $arg_count Arg count + * @return void + */ + private function addCustomFunction($name, $arg_count = -1) + { + if ($this->db) + { + $newname = preg_replace('/_/', '', $name); + $localname = __CLASS__.'::db'.$newname; + $reflectClass = new ReflectionClass(__CLASS__); + $reflectFunction = $reflectClass->getMethod('db'.$newname); + if ($arg_count < 0) { + $arg_count = $reflectFunction->getNumberOfParameters(); + } + if (!$this->db->createFunction($name, $localname, $arg_count)) + { + $this->error = "unable to create custom function '$name'"; + } + } + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * calc_daynr + * + * @param int $year Year + * @param int $month Month + * @param int $day Day + * @return int Formatted date + */ + private static function calc_daynr($year, $month, $day) + { + // phpcs:enable + $y = $year; + if ($y == 0 && $month == 0) return 0; + $num = (365 * $y + 31 * ($month - 1) + $day); + if ($month <= 2) { + $y--; } + else { + $num -= floor(($month * 4 + 23) / 10); + } + $temp = floor(($y / 100 + 1) * 3 / 4); + return $num + floor($y / 4) - $temp; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * calc_weekday + * + * @param int $daynr ??? + * @param bool $sunday_first_day_of_week ??? + * @return int + */ + private static function calc_weekday($daynr, $sunday_first_day_of_week) + { + // phpcs:enable + $ret = floor(($daynr + 5 + ($sunday_first_day_of_week ? 1 : 0)) % 7); + return $ret; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** + * calc_days_in_year + * + * @param string $year Year + * @return int Nb of days in year + */ + private static function calc_days_in_year($year) + { + // phpcs:enable + return (($year & 3) == 0 && ($year % 100 || ($year % 400 == 0 && $year)) ? 366 : 365); + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps @@ -1458,37 +1404,36 @@ - private static function calc_week($year, $month, $day, $week_behaviour, &$calc_year) - { - // phpcs:enable - $daynr = self::calc_daynr($year, $month, $day); - $first_daynr = self::calc_daynr($year, 1, 1); - $monday_first = ($week_behaviour & self::WEEK_MONDAY_FIRST) ? 1 : 0; - $week_year = ($week_behaviour & self::WEEK_YEAR) ? 1 : 0; - $first_weekday = ($week_behaviour & self::WEEK_FIRST_WEEKDAY) ? 1 : 0; - - $weekday = self::calc_weekday($first_daynr, !$monday_first); - $calc_year = $year; - - if ($month == 1 && $day <= 7 - $weekday) { - if (!$week_year && (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4))) { - return 0; - } - $week_year = 1; - $calc_year--; - $first_daynr -= ($days = self::calc_days_in_year($calc_year)); - $weekday = ($weekday + 53 * 7 - $days) % 7; - } - - if (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4)) { - $days = $daynr - ($first_daynr + (7 - $weekday)); - } else { - $days = $daynr - ($first_daynr - $weekday); - } - - if ($week_year && $days >= 52 * 7) { - $weekday = ($weekday + self::calc_days_in_year($calc_year)) % 7; - if ((!$first_weekday && $weekday < 4) || ($first_weekday && $weekday == 0)) { - $calc_year++; - return 1; - } - } - return floor($days / 7 + 1); - } + private static function calc_week($year, $month, $day, $week_behaviour, &$calc_year) + { + // phpcs:enable + $daynr = self::calc_daynr($year, $month, $day); + $first_daynr = self::calc_daynr($year, 1, 1); + $monday_first = ($week_behaviour & self::WEEK_MONDAY_FIRST) ? 1 : 0; + $week_year = ($week_behaviour & self::WEEK_YEAR) ? 1 : 0; + $first_weekday = ($week_behaviour & self::WEEK_FIRST_WEEKDAY) ? 1 : 0; + + $weekday = self::calc_weekday($first_daynr, !$monday_first); + $calc_year = $year; + + if ($month == 1 && $day <= 7 - $weekday) { + if (!$week_year && (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4))) + return 0; + $week_year = 1; + $calc_year--; + $first_daynr -= ($days = self::calc_days_in_year($calc_year)); + $weekday = ($weekday + 53 * 7 - $days) % 7; + } + + if (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4)) { + $days = $daynr - ($first_daynr + (7 - $weekday)); + } else { + $days = $daynr - ($first_daynr - $weekday); + } + + if ($week_year && $days >= 52 * 7) { + $weekday = ($weekday + self::calc_days_in_year($calc_year)) % 7; + if ((!$first_weekday && $weekday < 4) || ($first_weekday && $weekday == 0)) { + $calc_year++; + return 1; + } + } + return floor($days / 7 + 1); + }