--- /tmp/dsg/dolibarr/htdocs/core/db/github_Database.interface.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_Database.interface.php @@ -177,14 +177,6 @@ */ public function escape($stringtoencode); - /** - * Sanitize a string for SQL forging - * - * @param string $stringtosanitize String to escape - * @return string String escaped - */ - public function sanitize($stringtosanitize); - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Get last ID after an insert INSERT --- /tmp/dsg/dolibarr/htdocs/core/db/github_DoliDB.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_DoliDB.class.php @@ -75,7 +75,7 @@ * @param string $resko resultat si test non egal * @return string SQL string */ - public function ifsql($test, $resok, $resko) + public function ifsql($test, $resok, $resko) { return 'IF('.$test.','.$resok.','.$resko.')'; } @@ -84,14 +84,13 @@ * Convert (by PHP) a GM Timestamp date into a string date with PHP server TZ to insert into a date field. * Function to use to build INSERT, UPDATE or WHERE predica * - * @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 add $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"); } /** @@ -99,33 +98,17 @@ * * @return string lasterrno */ - public function lasterrno() + public function lasterrno() { return $this->lasterrno; } /** - * Sanitize a string for SQL forging - * - * @param string $stringtosanitize String to escape - * @param int $allowsimplequote Allow simple quote - * @return string String escaped - */ - public function sanitize($stringtosanitize, $allowsimplequote = 0) - { - if ($allowsimplequote) { - return preg_replace('/[^a-z0-9_\-\.,\']/i', '', $stringtosanitize); - } else { - return preg_replace('/[^a-z0-9_\-\.,]/i', '', $stringtosanitize); - } - } - - /** * Start transaction * * @return int 1 if transaction successfuly opened or already opened, 0 if error */ - public function begin() + public function begin() { if (!$this->transaction_opened) { @@ -137,7 +120,9 @@ dol_syslog('', 0, 1); } return $ret; - } else { + } + else + { $this->transaction_opened++; dol_syslog('', 0, 1); return 1; @@ -150,7 +135,7 @@ * @param string $log Add more log to default log line * @return int 1 if validation is OK or transaction level no started, 0 if ERROR */ - public function commit($log = '') + public function commit($log = '') { dol_syslog('', 0, -1); if ($this->transaction_opened <= 1) @@ -161,10 +146,14 @@ $this->transaction_opened = 0; dol_syslog("COMMIT Transaction".($log ? ' '.$log : ''), LOG_DEBUG); return 1; - } else { + } + else + { return 0; } - } else { + } + else + { $this->transaction_opened--; return 1; } @@ -176,7 +165,7 @@ * @param string $log Add more log to default log line * @return resource|int 1 if cancelation is ok or transaction not open, 0 if error */ - public function rollback($log = '') + public function rollback($log = '') { dol_syslog('', 0, -1); if ($this->transaction_opened <= 1) @@ -185,7 +174,9 @@ $this->transaction_opened = 0; dol_syslog("ROLLBACK Transaction".($log ? ' '.$log : ''), LOG_DEBUG); return $ret; - } else { + } + else + { $this->transaction_opened--; return 1; } @@ -198,7 +189,7 @@ * @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) + public function plimit($limit = 0, $offset = 0) { global $conf; if (empty($limit)) return ""; @@ -212,7 +203,7 @@ * * @return array Version array */ - public function getVersionArray() + public function getVersionArray() { return preg_split("/[\.,-]/", $this->getVersion()); } @@ -222,7 +213,7 @@ * * @return string Last query */ - public function lastquery() + public function lastquery() { return $this->lastquery; } @@ -234,38 +225,36 @@ * @param string $sortorder Sort order, separated by comma. Example: 'ASC,DESC'; * @return string String to provide syntax of a sort sql string */ - public function order($sortfield = null, $sortorder = null) + public function order($sortfield = null, $sortorder = null) { if (!empty($sortfield)) { - $oldsortorder = ''; $return = ''; $fields = explode(',', $sortfield); $orders = explode(',', $sortorder); $i = 0; - foreach ($fields as $val) { + 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])); + $return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); + + $tmpsortorder = trim($orders[$i]); // Only ASC and DESC values are valid SQL if (strtoupper($tmpsortorder) === 'ASC') { - $oldsortorder = 'ASC'; $return .= ' ASC'; } elseif (strtoupper($tmpsortorder) === 'DESC') { - $oldsortorder = 'DESC'; $return .= ' DESC'; - } else { - $return .= ' '.($oldsortorder ? $oldsortorder : 'ASC'); } $i++; } return $return; - } else { + } + else + { return ''; } } @@ -275,7 +264,7 @@ * * @return string Last error */ - public function lasterror() + public function lasterror() { return $this->lasterror; } @@ -286,12 +275,12 @@ * 19700101020000 -> 7200 whaterver is TZ if gmt=1 * * @param string $string Date in a string (YYYYMMDDHHMMSS, YYYYMMDD, YYYY-MM-DD HH:MM:SS) - * @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 * @return int|string Date TMS or '' */ - 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 + 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 ''; $string = preg_replace('/([^0-9])/i', '', $string); $tmp = $string.'000000'; @@ -304,7 +293,7 @@ * * @return string lastqueryerror */ - public function lastqueryerror() + public function lastqueryerror() { return $this->lastqueryerror; } @@ -342,8 +331,8 @@ if ($res) { $results = array(); - if ($this->num_rows($res) > 0) { - while ($obj = $this->fetch_object($res)) { + if($this->num_rows($res) > 0){ + while ($obj = $this->fetch_object($res)){ $results[] = $obj; } } --- /tmp/dsg/dolibarr/htdocs/core/db/github_mysqli.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_mysqli.class.php @@ -34,16 +34,16 @@ { /** @var mysqli Database object */ public $db; - //! 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'; /** @var bool|mysqli_result Resultset of last query */ private $_results; - /** + /** * Constructor. * This create an opened connexion to a database server and eventually to a database * @@ -53,38 +53,38 @@ * @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, $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); + } // Try server connection // We do not try to connect to database, only to server. Connect to database is done later in constrcutor @@ -101,17 +101,17 @@ } // If server connection is ok, we try to connect to the database - 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->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 @@ -121,63 +121,67 @@ 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'; + } + 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); } } - } - } - - - /** - * 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 + } + } + + + /** + * 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 /** * Select a database * * @param string $database Name of database * @return boolean true if OK, false if KO */ - public function select_db($database) - { - // phpcs:enable - dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); - return $this->db->select_db($database); - } + public function select_db($database) + { + // phpcs:enable + dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG); + return $this->db->select_db($database); + } /** @@ -191,385 +195,392 @@ * @return mysqli Database access object * @see close() */ - public function connect($host, $login, $passwd, $name, $port = 0) - { + public function connect($host, $login, $passwd, $name, $port = 0) + { dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name", LOG_DEBUG); // 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); - } - - /** + } + + /** * Return version of database server * * @return string Version string - */ - 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(); + */ + 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; } + 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; } return $resultset->fetch_object(); - } - - - // 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 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'); + } + } + + /** * Return description of last error * * @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 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 + /** * Get last ID after an insert INSERT * * @param string $tab Table name concerned by insert. Ne sert pas sous MySql mais requis pour compatibilite avec Postgresql * @param string $fieldid Field name * @return int|string Id of row - */ - 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 - /** + */ + 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 + /** * Return connexion ID * * @return string Id connexion - */ - 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 * Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated * We force to create database with charset this->forcecharset and collate this->forcecollate * @@ -578,85 +589,85 @@ * @param string $collation Charset used to sort data * @param string $owner Username of database owner * @return bool|mysqli_result resource defined if OK, null if KO - */ - 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 $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); - 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 + /** * 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 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 - /** + */ + 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. * * @param string $table Name of table * @return array Tableau des informations des champs de la table - */ - 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 - /** + */ + 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 + /** * Create a table into database * * @param string $table Name of table @@ -667,20 +678,20 @@ * @param array $fulltext_keys Tableau des Nom de champs qui seront indexes en fulltext * @param array $keys Tableau des champs cles noms => valeur * @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." "; + */ + 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." "; $sqlfields[$i] .= $field_desc['type']; if (preg_match("/^[^\s]/i", $field_desc['value'])) { $sqlfields[$i] .= "(".$field_desc['value'].")"; @@ -692,8 +703,9 @@ { if ((preg_match("/null/i", $field_desc['default'])) || (preg_match("/CURRENT_TIMESTAMP/i", $field_desc['default']))) { $sqlfields[$i] .= " default ".$field_desc['default']; - } else { - $sqlfields[$i] .= " default '".$this->escape($field_desc['default'])."'"; + } + else { + $sqlfields[$i] .= " default '".$field_desc['default']."'"; } } if (preg_match("/^[^\s]/i", $field_desc['null'])) { @@ -702,79 +714,81 @@ if (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 ($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; + $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 - /** + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** * Return a pointer of line with description of a table or field * * @param string $table Name of table * @param string $field Optionnel : Name of field if we want description of field * @return bool|mysqli_result Resultset x (x->Field, x->Type, ...) - */ - 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 + /** * Create a new field into table * * @param string $table Name of table @@ -782,116 +796,119 @@ * @param string $field_desc Tableau associatif de description du champ a inserer[nom du parametre][valeur du parametre] * @param string $field_position Optionnel ex.: "after champtruc" * @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 '".$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 - /** + */ + 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 + /** * Update format of a field into a table * * @param string $table Name of table * @param string $field_name Name of field to modify * @param string $field_desc Array with description of field format * @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'] != '') - { + */ + 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 - /** + } + + dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG); + if (!$this->query($sql)) + return -1; + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** * Drop a field from table * * @param string $table Name of table * @param string $field_name Name of field to drop * @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 - /** + */ + 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 + /** * Create a user and privileges to connect to database (even if database does not exists yet) * * @param string $dolibarr_main_db_host Ip server or '%' @@ -899,218 +916,220 @@ * @param string $dolibarr_main_db_pass Mot de passe user a creer * @param string $dolibarr_main_db_name Database name where user must be granted * @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') + */ + 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 { - 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; - } - - /** + // 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; + } + + /** * Return full path of dump program * * @return string Full path of dump program - */ - 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 * @return array Array of key-values (key=>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 + */ + 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 * @return array Array of key-values (key=>value) - */ - 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; + } } --- /tmp/dsg/dolibarr/htdocs/core/db/github_pgsql.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_pgsql.class.php @@ -35,14 +35,14 @@ */ class DoliDBPgsql extends DoliDB { - //! Database type - public $type = 'pgsql'; // Name of manager - //! Database label + //! Database type + public $type = 'pgsql'; // Name of manager + //! Database label const LABEL = 'PostgreSQL'; // Label of manager //! Charset - 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 //! Version min database const VERSIONMIN = '9.0.0'; // Version min database /** @var resource Resultset of last query */ @@ -62,17 +62,17 @@ * @param string $name Nom de la database * @param int $port Port of database server */ - public function __construct($type, $host, $user, $pass, $name = '', $port = 0) + 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 + // 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->database_host = $host; + $this->database_port = $port; $this->transaction_opened = 0; @@ -104,7 +104,9 @@ { $this->connected = true; $this->ok = true; - } else { + } + else + { // host, login ou password incorrect $this->connected = false; $this->ok = false; @@ -120,14 +122,18 @@ $this->database_selected = true; $this->database_name = $name; $this->ok = true; - } else { + } + else + { $this->database_selected = false; $this->database_name = ''; $this->ok = false; $this->error = $this->error(); dol_syslog(get_class($this)."::DoliDBPgsql : Erreur Select_db ".$this->error, LOG_ERR); } - } else { + } + else + { // Pas de selection de base demandee, ok ou ko $this->database_selected = false; } @@ -136,14 +142,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 - */ + /** + * 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) { global $conf; @@ -161,163 +167,158 @@ { // group_concat support (PgSQL >= 9.0) // Replace group_concat(x) or group_concat(x SEPARATOR ',') with string_agg(x, ',') - $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line); + $line = preg_replace('/GROUP_CONCAT/i', 'STRING_AGG', $line); $line = preg_replace('/ SEPARATOR/i', ',', $line); $line = preg_replace('/STRING_AGG\(([^,\)]+)\)/i', 'STRING_AGG(\\1, \',\')', $line); //print $line."\n"; - 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)\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 $count_like = 0; $line = str_replace(' LIKE \'', ' ILIKE \'', $line, $count_like); if (!empty($conf->global->PSQL_USE_UNACCENT) && $count_like > 0) @@ -326,10 +327,10 @@ $line = preg_replace('/\s+(\(+\s*)([a-zA-Z0-9\-\_\.]+) ILIKE /', ' \1unaccent(\2) ILIKE ', $line); } - $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); // Delete using criteria on other table must not declare twice the deleted table // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable @@ -373,24 +374,24 @@ return $line; } - // 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 * * @param string $database Name of database * @return bool true if OK, false if KO */ - 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; + } + } /** * Connexion to server @@ -403,7 +404,7 @@ * @return bool|resource Database access handler * @see close() */ - public function connect($host, $login, $passwd, $name, $port = 0) + public function connect($host, $login, $passwd, $name, $port = 0) { // use pg_pconnect() instead of pg_connect() if you want to use persistent connection costing 1ms, instead of 30ms for non persistent @@ -428,7 +429,7 @@ // if local connection failed or not requested, use TCP/IP if (!$this->db) { - if (!$host) $host = "localhost"; + if (!$host) $host = "localhost"; if (!$port) $port = 5432; $con_string = "host='".$host."' port='".$port."' dbname='".$name."' user='".$login."' password='".$passwd."'"; @@ -451,13 +452,13 @@ * * @return string Version string */ - public function getVersion() + public function getVersion() { $resql = $this->query('SHOW server_version'); if ($resql) { - $liste = $this->fetch_array($resql); - return $liste['server_version']; + $liste = $this->fetch_array($resql); + return $liste['server_version']; } return ''; } @@ -467,37 +468,37 @@ * * @return string Version string */ - public function getDriverInfo() + public function getDriverInfo() { return 'pgsql php driver'; } - /** - * 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; + } /** * Convert request to PostgreSQL syntax, execute it 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). - * @param string $type Type of SQL order ('ddl' for insert, update, select, delete or 'dml' for create, alter...) + * @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') + public function query($query, $usesavepoint = 0, $type = 'auto') { global $conf; @@ -517,7 +518,8 @@ { $query = preg_replace('/([^\'])([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9])/', '\\1\'\\2\'', $query); dol_syslog("Warning: Bad formed request converted into ".$query, LOG_WARNING); - } else $loop = false; + } + else $loop = false; } } @@ -539,16 +541,16 @@ { 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 + 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 dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterror." (".$this->lasterrno.")", LOG_ERR); dol_syslog(get_class($this)."::query SQL Error usesavepoint = ".$usesavepoint, LOG_ERR); - } + } if ($usesavepoint && $this->transaction_opened) // Warning, after that errno will be erased { @@ -562,68 +564,68 @@ return $ret; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Renvoie la ligne courante (comme un objet) pour le curseur resultset * * @param resource $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 - // If resultset not provided, we take the last used by connexion + 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; } return pg_fetch_object($resultset); } - // 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 + // 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; } return pg_fetch_array($resultset); } - // 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 // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion if (!is_resource($resultset)) { $resultset = $this->_results; } return pg_fetch_row($resultset); } - // 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 + // 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; } return pg_num_rows($resultset); } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Return the number of lines in the result of a request INSERT, DELETE or UPDATE * @@ -631,10 +633,10 @@ * @return int Nb of lines * @see num_rows() */ - public function affected_rows($resultset) - { - // phpcs:enable - // If resultset not provided, we take the last used by connexion + 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; } // pgsql necessite un resultset pour cette fonction contrairement // a mysql qui prend un link de base @@ -648,9 +650,9 @@ * @param resource $resultset Result set of request * @return void */ - public function free($resultset = null) - { - // If resultset not provided, we take the last used by connexion + public function free($resultset = null) + { + // If resultset not provided, we take the last used by connexion if (!is_resource($resultset)) { $resultset = $this->_results; } // Si resultset en est un, on libere la memoire if (is_resource($resultset)) pg_free_result($resultset); @@ -658,16 +660,16 @@ /** - * 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) + * 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) { global $conf; - if (empty($limit)) return ""; + if (empty($limit)) return ""; if ($limit < 0) $limit = $conf->liste_limit; if ($offset > 0) return " LIMIT ".$limit." OFFSET ".$offset." "; else return " LIMIT $limit "; @@ -680,20 +682,32 @@ * @param string $stringtoencode String to escape * @return string String escaped */ - public function escape($stringtoencode) + public function escape($stringtoencode) { return pg_escape_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 + * * @param string $test Test string (example: 'cd.statut=0', 'field IS NULL') * @param string $resok resultat si test egal * @param string $resko resultat si test non egal * @return string chaine formate SQL */ - public function ifsql($test, $resok, $resko) + public function ifsql($test, $resok, $resko) { return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)'; } @@ -703,12 +717,13 @@ * * @return string Error code (Exemples: DB_ERROR_TABLE_ALREADY_EXISTS, DB_ERROR_RECORD_ALREADY_EXISTS...) */ - public function errno() + 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 { + } + else { // Constants to convert error code to a generic Dolibarr error code $errorcode_map = array( 1004 => 'DB_ERROR_CANNOT_CREATE', @@ -768,12 +783,12 @@ * * @return string Error text */ - public function error() + public function error() { return pg_last_error($this->db); } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Get last ID after an insert INSERT * @@ -781,9 +796,9 @@ * @param string $fieldid Field name * @return string Id of row */ - public function last_insert_id($tab, $fieldid = 'rowid') - { - // phpcs:enable + public function last_insert_id($tab, $fieldid = 'rowid') + { + // phpcs:enable //$result = pg_query($this->db,"SELECT MAX(".$fieldid.") FROM ".$tab); $result = pg_query($this->db, "SELECT currval('".$tab."_".$fieldid."_seq')"); if (!$result) @@ -797,14 +812,14 @@ } /** - * 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) + * 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; @@ -825,7 +840,7 @@ * @param int $value Value to decrypt * @return string Decrypted value if used */ - public function decrypt($value) + public function decrypt($value) { global $conf; @@ -840,21 +855,21 @@ } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Return connexion ID * * @return string Id connexion */ - public function DDLGetConnectId() - { - // phpcs:enable + public function DDLGetConnectId() + { + // phpcs:enable return '?'; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Create a new database * Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated @@ -866,10 +881,10 @@ * @param string $owner Username of database owner * @return false|resource resource defined if OK, null if KO */ - public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') - { - // phpcs:enable - if (empty($charset)) $charset = $this->forcecharset; + public function DDLCreateDb($database, $charset = '', $collation = '', $owner = '') + { + // phpcs:enable + if (empty($charset)) $charset = $this->forcecharset; if (empty($collation)) $collation = $this->forcecollate; // Test charset match LC_TYPE (pgsql error otherwise) @@ -881,7 +896,7 @@ return $ret; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * List tables into a database * @@ -889,25 +904,25 @@ * @param string $table Name of table filter ('xxx%') * @return array List of tables in an array */ - public function DDLListTables($database, $table = '') - { - // phpcs:enable + public function DDLListTables($database, $table = '') + { + // phpcs:enable $listtables = array(); $like = ''; - if ($table) $like = " AND table_name LIKE '".$this->escape($table)."'"; + 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]; - } - } + if ($result) + { + while ($row = $this->fetch_row($result)) + { + $listtables[] = $row[0]; + } + } return $listtables; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * List information of columns into a table. * @@ -915,9 +930,9 @@ * @return array Tableau des informations des champs de la table * */ - public function DDLInfoTable($table) - { - // phpcs:enable + public function DDLInfoTable($table) + { + // phpcs:enable $infotables = array(); $sql = "SELECT "; @@ -933,23 +948,23 @@ $sql .= " '' as \"Privileges\""; $sql .= " FROM information_schema.columns infcol"; $sql .= " WHERE table_schema='public' "; - $sql .= " AND table_name='".$this->escape($table)."'"; + $sql .= " AND table_name='".$table."'"; $sql .= " ORDER BY ordinal_position;"; 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 + while ($row = $this->fetch_row($result)) + { + $infotables[] = $row; + } + } + return $infotables; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Create a table into database * @@ -962,9 +977,9 @@ * @param array $keys Tableau des champs cles noms => valeur * @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 + 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 @@ -976,19 +991,21 @@ $sqlfields[$i] = $field_name." "; $sqlfields[$i] .= $field_desc['type']; if (preg_match("/^[^\s]/i", $field_desc['value'])) - $sqlfields[$i] .= "(".$field_desc['value'].")"; + $sqlfields[$i] .= "(".$field_desc['value'].")"; elseif (preg_match("/^[^\s]/i", $field_desc['attribute'])) - $sqlfields[$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']; + $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']; + $sqlfields[$i] .= " ".$field_desc['extra']; $i++; } if ($primary_key != "") @@ -999,7 +1016,7 @@ $i = 0; foreach ($unique_keys as $key => $value) { - $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$this->escape($value)."')"; + $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')"; $i++; } } @@ -1024,27 +1041,29 @@ dol_syslog($sql, LOG_DEBUG); if (!$this->query($sql)) return -1; - else return 1; - } - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + 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 + 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 + else + return 1; + } + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Create a user to connect to database * @@ -1054,9 +1073,9 @@ * @param string $dolibarr_main_db_name Database name where user must be granted * @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 + public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name) + { + // phpcs:enable // Note: using ' on user does not works with pgsql $sql = "CREATE USER ".$this->escape($dolibarr_main_db_user)." with password '".$this->escape($dolibarr_main_db_pass)."'"; @@ -1070,7 +1089,7 @@ return 1; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Return a pointer of line with description of a table or field * @@ -1078,19 +1097,19 @@ * @param string $field Optionnel : Name of field if we want description of field * @return false|resource Resultset x (x->attname) */ - 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"; $sql .= " AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax')"; - if ($field) $sql .= " AND attname = '".$this->escape($field)."'"; + if ($field) $sql .= " AND attname = '".$field."'"; dol_syslog($sql, LOG_DEBUG); $this->_results = $this->query($sql); return $this->_results; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Create a new field into table * @@ -1100,28 +1119,28 @@ * @param string $field_position Optionnel ex.: "after champtruc" * @return int <0 if KO, >0 if OK */ - public function DDLAddField($table, $field_name, $field_desc, $field_position = "") - { - // phpcs:enable + 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('int', 'date', 'datetime')) && $field_desc['value']) - { - $sql .= "(".$field_desc['value'].")"; - } + { + $sql .= "(".$field_desc['value'].")"; + } } if (preg_match("/^[^\s]/i", $field_desc['attribute'])) - $sql .= " ".$field_desc['attribute']; + $sql .= " ".$field_desc['attribute']; if (preg_match("/^[^\s]/i", $field_desc['null'])) - $sql .= " ".$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']; + if (preg_match("/null/i", $field_desc['default'])) { + $sql .= " default ".$field_desc['default']; } else { - $sql .= " default '".$this->escape($field_desc['default'])."'"; + $sql .= " default '".$field_desc['default']."'"; } } if (preg_match("/^[^\s]/i", $field_desc['extra'])) { @@ -1135,7 +1154,7 @@ return 1; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Update format of a field into a table * @@ -1144,9 +1163,9 @@ * @param string $field_desc Array with description of field format * @return int <0 if KO, >0 if OK */ - public function DDLUpdateField($table, $field_name, $field_desc) - { - // phpcs:enable + 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']) { @@ -1155,22 +1174,23 @@ 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); - } + // 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 + elseif ($field_desc['type'] != 'text') $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'"; // Default not supported on text fields } dol_syslog($sql, LOG_DEBUG); @@ -1179,7 +1199,7 @@ return 1; } - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps /** * Drop a field from table * @@ -1187,9 +1207,9 @@ * @param string $field_name Name of field to drop * @return int <0 if KO, >0 if OK */ - public function DDLDropField($table, $field_name) - { - // phpcs:enable + 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)) @@ -1205,14 +1225,15 @@ * * @return string Charset */ - public function getDefaultCharacterSetDatabase() + public function getDefaultCharacterSetDatabase() { $resql = $this->query('SHOW SERVER_ENCODING'); if ($resql) { - $liste = $this->fetch_array($resql); - return $liste['server_encoding']; - } else return ''; + $liste = $this->fetch_array($resql); + return $liste['server_encoding']; + } + else return ''; } /** @@ -1220,7 +1241,7 @@ * * @return array List of Charset */ - public function getListOfCharacterSet() + public function getListOfCharacterSet() { $resql = $this->query('SHOW SERVER_ENCODING'); $liste = array(); @@ -1245,14 +1266,15 @@ * * @return string Collation value */ - public function getDefaultCollationDatabase() + public function getDefaultCollationDatabase() { $resql = $this->query('SHOW LC_COLLATE'); if ($resql) { - $liste = $this->fetch_array($resql); + $liste = $this->fetch_array($resql); return $liste['lc_collate']; - } else return ''; + } + else return ''; } /** @@ -1260,7 +1282,7 @@ * * @return array Liste of Collation */ - public function getListOfCollation() + public function getListOfCollation() { $resql = $this->query('SHOW LC_COLLATE'); $liste = array(); @@ -1284,52 +1306,56 @@ * * @return string Full path of dump program */ - public function getPathOfDump() + public function getPathOfDump() { $fullpathofdump = '/pathtopgdump/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'; - } + $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'; + } } return $fullpathofdump; } - /** - * 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() { //$tool='pg_restore'; $tool = 'psql'; $fullpathofdump = '/pathtopgrestore/'.$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; - } - } + 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; + } + } return $fullpathofdump; } @@ -1340,7 +1366,7 @@ * @param string $filter Filter list on a particular value * @return array Array of key-values (key=>value) */ - public function getServerParametersValues($filter = '') + public function getServerParametersValues($filter = '') { $result = array(); @@ -1361,7 +1387,7 @@ * @param string $filter Filter list on a particular value * @return array Array of key-values (key=>value) */ - public function getServerStatusValues($filter = '') + public function getServerStatusValues($filter = '') { /* This is to return current running requests. $sql='SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid'; --- /tmp/dsg/dolibarr/htdocs/core/db/github_sqlite3.class.php +++ /tmp/dsg/dolibarr/htdocs/core/db/client_sqlite3.class.php @@ -32,48 +32,48 @@ */ class DoliDBSqlite3 extends DoliDB { - //! 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")) + //! 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; @@ -82,7 +82,7 @@ return $this->ok; }*/ - /*if (! $host) + /*if (! $host) { $this->connected = false; $this->ok = false; @@ -91,228 +91,230 @@ 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)) - { + // 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 - /** + { + $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 + /** * Select a database - * + * * @param string $database Name of database * @return boolean true if OK, false if KO - */ - 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; + } + + + /** * Connexion to server - * + * * @param string $host database server host * @param string $login login * @param string $passwd password @@ -320,324 +322,329 @@ * @param integer $port Port of database server * @return SQLite3 Database access handler * @see close() - */ - 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'); $this->db = new SQLite3($database_name); - //$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; + } + + + /** * Return version of database server - * + * * @return string Version string - */ - 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; + } 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='".$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 - - // 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; - } - 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(); + } + + + /** * Free last resultset used. - * + * * @param SQLite3Result $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 ($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(); + } + + /** * Escape a string to insert data - * + * * @param string $stringtoencode String to escape * @return string String escaped - */ - 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( + */ + 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', @@ -669,228 +676,231 @@ { return $errorcode_map[$this->db->errorCode()]; }*/ - $errno = $this->db->lastErrorCode(); + $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 - * + { + 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 + * * @param string $tab Table name concerned by insert. Ne sert pas sous MySql mais requis pour compatibilite avec Postgresql * @param string $fieldid Field name * @return int Id of row - */ - 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 - * + */ + 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 + * * @return string Id connexion - */ - public function DDLGetConnectId() - { - // phpcs:enable - return '?'; - } - - - // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps - /** + */ + public function DDLGetConnectId() + { + // phpcs:enable + return '?'; + } + + + // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps + /** * Create a new database * Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated * We force to create database with charset this->forcecharset and collate this->forcecollate - * + * * @param string $database Database name to create * @param string $charset Charset used to store data * @param string $collation Charset used to sort data * @param string $owner Username of database owner * @return SQLite3Result resource defined if OK, null if KO - */ - 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 - * + */ + 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 + * * @param string $database Name of database * @param string $table Name of table filter ('xxx%') * @return array List of tables in an array - */ - 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. - * + */ + 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. + * * @param string $table Name of table * @return array Tableau des informations des champs de la table * TODO modify for sqlite - */ - 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 - /** + */ + 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 + /** * Create a table into database - * + * * @param string $table Nom de la table * @param array $fields Tableau associatif [nom champ][tableau des descriptions] * @param string $primary_key Nom du champ qui sera la clef primaire @@ -899,391 +909,396 @@ * @param array $fulltext_keys Tableau des Nom de champs qui seront indexes en fulltext * @param array $keys Tableau des champs cles noms => valeur * @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 '".$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 <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 - /** + */ + 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 + /** * Return a pointer of line with description of a table or field - * + * * @param string $table Name of table * @param string $field Optionnel : Name of field if we want description of field * @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 - /** + */ + 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 + /** * Create a new field into table - * + * * @param string $table Name of table * @param string $field_name Name of field to add * @param string $field_desc Tableau associatif de description du champ a inserer[nom du parametre][valeur du parametre] * @param string $field_position Optionnel ex.: "after champtruc" * @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 '".$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 - /** + */ + 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 + /** * Update format of a field into a table - * + * * @param string $table Name of table * @param string $field_name Name of field to modify * @param string $field_desc Array with description of field format * @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 - /** + */ + 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 + /** * Drop a field from table - * + * * @param string $table Name of table * @param string $field_name Name of field to drop * @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) - * + */ + 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) + * * @param string $dolibarr_main_db_host Ip serveur * @param string $dolibarr_main_db_user Nom user a creer * @param string $dolibarr_main_db_pass Mot de passe user a creer * @param string $dolibarr_main_db_name Database name where user must be granted * @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; - } - - /** + */ + 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 charset used to store data in database - * + * * @return string Charset - */ - public function getDefaultCharacterSetDatabase() - { - return 'UTF-8'; - } - - /** + */ + public function getDefaultCharacterSetDatabase() + { + return 'UTF-8'; + } + + /** * Return list of available charset that can be used to store data in database - * + * * @return array List of Charset - */ - 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; + } + + /** * Return collation used in database - * + * * @return string Collation value - */ - public function getDefaultCollationDatabase() - { - return 'UTF-8'; - } - - /** + */ + public function getDefaultCollationDatabase() + { + return 'UTF-8'; + } + + /** * Return list of available collation that can be used for database - * + * * @return array List of Collation - */ - 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; + } + + /** * Return full path of dump program - * + * * @return string Full path of dump program - */ - 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; + } + + /** * Return full path of restore program - * + * * @return string Full path of restore program - */ - 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 + * * @param string $filter Filter list on a particular value * @return array Array of key-values (key=>value) - */ - 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 + * * @param string $filter Filter list on a particular value * @return array Array of key-values (key=>value) - */ - public function getServerStatusValues($filter = '') - { - $result = array(); - /* + */ + public function getServerStatusValues($filter = '') + { + $result = array(); + /* $sql='SHOW STATUS'; if ($filter) $sql.=" LIKE '".$this->escape($filter)."'"; $resql=$this->query($sql); @@ -1293,88 +1308,89 @@ } */ - 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 + 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 /** * calc_week * @@ -1385,40 +1401,40 @@ * @param string $calc_year ??? * @return string ??? */ - 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); + } }