X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBA.php;h=832f0a444f994684354267123a0f9ff6c8c39fed;hb=67aa1888309e83648be62988b10e757c14a2e2a9;hp=8e5a621b3689819f93ec021506716debf7f6561e;hpb=aa26943f92dd08fa204dab71833fe9d37880ca76;p=friendica.git diff --git a/src/Database/DBA.php b/src/Database/DBA.php index 8e5a621b36..832f0a444f 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -2,12 +2,11 @@ namespace Friendica\Database; -// Do not use Core\Config in this class at risk of infinite loop. -// Please use App->getConfigVariable() instead. -//use Friendica\Core\Config; - +use Friendica\Core\Config\Cache\IConfigCache; +use Friendica\Core\Logger; use Friendica\Core\System; use Friendica\Util\DateTimeFormat; +use Friendica\Util\Profiler; use mysqli; use mysqli_result; use mysqli_stmt; @@ -15,8 +14,6 @@ use PDO; use PDOException; use PDOStatement; -require_once 'include/dba.php'; - /** * @class MySQL database class * @@ -24,8 +21,29 @@ require_once 'include/dba.php'; */ class DBA { + /** + * Lowest possible date value + */ + const NULL_DATE = '0001-01-01'; + /** + * Lowest possible datetime value + */ + const NULL_DATETIME = '0001-01-01 00:00:00'; + public static $connected = false; + /** + * @var IConfigCache + */ + private static $configCache; + /** + * @var Profiler + */ + private static $profiler; + /** + * @var string + */ + private static $basePath; private static $server_info = ''; private static $connection; private static $driver; @@ -41,13 +59,16 @@ class DBA private static $db_name = ''; private static $db_charset = ''; - public static function connect($serveraddr, $user, $pass, $db, $charset = null) + public static function connect($basePath, IConfigCache $configCache, Profiler $profiler, $serveraddr, $user, $pass, $db, $charset = null) { if (!is_null(self::$connection) && self::connected()) { return true; } // We are storing these values for being able to perform a reconnect + self::$basePath = $basePath; + self::$configCache = $configCache; + self::$profiler = $profiler; self::$db_serveraddr = $serveraddr; self::$db_user = $user; self::$db_pass = $pass; @@ -91,6 +112,7 @@ class DBA self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); self::$connected = true; } catch (PDOException $e) { + /// @TODO At least log exception, don't ignore it! } } @@ -147,7 +169,7 @@ class DBA public static function reconnect() { self::disconnect(); - $ret = self::connect(self::$db_serveraddr, self::$db_user, self::$db_pass, self::$db_name, self::$db_charset); + $ret = self::connect(self::$basePath, self::$configCache, self::$profiler, self::$db_serveraddr, self::$db_user, self::$db_pass, self::$db_name, self::$db_charset); return $ret; } @@ -186,6 +208,7 @@ class DBA * @brief Returns the selected database name * * @return string + * @throws \Exception */ public static function databaseName() { $ret = self::p("SELECT DATABASE() AS `db`"); @@ -197,11 +220,11 @@ class DBA * @brief Analyze a database query and log this if some conditions are met. * * @param string $query The database query that will be analyzed + * @throws \Exception */ private static function logIndex($query) { - $a = get_app(); - if (!$a->getConfigVariable('system', 'db_log_index')) { + if (!self::$configCache->get('system', 'db_log_index')) { return; } @@ -220,18 +243,18 @@ class DBA return; } - $watchlist = explode(',', $a->getConfigVariable('system', 'db_log_index_watch')); - $blacklist = explode(',', $a->getConfigVariable('system', 'db_log_index_blacklist')); + $watchlist = explode(',', self::$configCache->get('system', 'db_log_index_watch')); + $blacklist = explode(',', self::$configCache->get('system', 'db_log_index_blacklist')); while ($row = self::fetch($r)) { - if ((intval($a->getConfigVariable('system', 'db_loglimit_index')) > 0)) { + if ((intval(self::$configCache->get('system', 'db_loglimit_index')) > 0)) { $log = (in_array($row['key'], $watchlist) && - ($row['rows'] >= intval($a->getConfigVariable('system', 'db_loglimit_index')))); + ($row['rows'] >= intval(self::$configCache->get('system', 'db_loglimit_index')))); } else { $log = false; } - if ((intval($a->getConfigVariable('system', 'db_loglimit_index_high')) > 0) && ($row['rows'] >= intval($a->getConfigVariable('system', 'db_loglimit_index_high')))) { + if ((intval(self::$configCache->get('system', 'db_loglimit_index_high')) > 0) && ($row['rows'] >= intval(self::$configCache->get('system', 'db_loglimit_index_high')))) { $log = true; } @@ -241,7 +264,7 @@ class DBA if ($log) { $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($a->getConfigVariable('system', 'db_log_index'), DateTimeFormat::utcNow()."\t". + @file_put_contents(self::$configCache->get('system', 'db_log_index'), DateTimeFormat::utcNow()."\t". $row['key']."\t".$row['rows']."\t".$row['Extra']."\t". basename($backtrace[1]["file"])."\t". $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t". @@ -251,11 +274,16 @@ class DBA } public static function escape($str) { - switch (self::$driver) { - case 'pdo': - return substr(@self::$connection->quote($str, PDO::PARAM_STR), 1, -1); - case 'mysqli': - return @self::$connection->real_escape_string($str); + if (self::$connected) { + switch (self::$driver) { + case 'pdo': + return substr(@self::$connection->quote($str, PDO::PARAM_STR), 1, -1); + + case 'mysqli': + return @self::$connection->real_escape_string($str); + } + } else { + return str_replace("'", "\\'", $str); } } @@ -368,13 +396,13 @@ class DBA * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid); * * Please only use it with complicated queries. - * For all regular queries please use dba::select or dba::exists + * For all regular queries please use DBA::select or DBA::exists * * @param string $sql SQL statement * @return bool|object statement object or result object + * @throws \Exception */ public static function p($sql) { - $a = get_app(); $stamp1 = microtime(true); @@ -397,7 +425,7 @@ class DBA if ((substr_count($sql, '?') != count($args)) && (count($args) > 0)) { // Question: Should we continue or stop the query here? - logger('Parameter mismatch. Query "'.$sql.'" - Parameters '.print_r($args, true), LOGGER_DEBUG); + Logger::warning('Query parameters mismatch.', ['query' => $sql, 'args' => $args, 'callstack' => System::callstack()]); } $sql = self::cleanQuery($sql); @@ -405,7 +433,7 @@ class DBA $orig_sql = $sql; - if ($a->getConfigValue('system', 'db_callstack')) { + if (self::$configCache->get('system', 'db_callstack') !== null) { $sql = "/*".System::callstack()." */ ".$sql; } @@ -537,7 +565,7 @@ class DBA $error = self::$error; $errorno = self::$errorno; - logger('DB Error '.self::$errorno.': '.self::$error."\n". + Logger::log('DB Error '.self::$errorno.': '.self::$error."\n". System::callstack(8)."\n".self::replaceParameters($sql, $args)); // On a lost connection we try to reconnect - but only once. @@ -545,14 +573,14 @@ class DBA if (self::$in_retrial || !self::reconnect()) { // It doesn't make sense to continue when the database connection was lost if (self::$in_retrial) { - logger('Giving up retrial because of database error '.$errorno.': '.$error); + Logger::log('Giving up retrial because of database error '.$errorno.': '.$error); } else { - logger("Couldn't reconnect after database error ".$errorno.': '.$error); + Logger::log("Couldn't reconnect after database error ".$errorno.': '.$error); } exit(1); } else { // We try it again - logger('Reconnected after database error '.$errorno.': '.$error); + Logger::log('Reconnected after database error '.$errorno.': '.$error); self::$in_retrial = true; $ret = self::p($sql, $args); self::$in_retrial = false; @@ -564,17 +592,17 @@ class DBA self::$errorno = $errorno; } - $a->save_timestamp($stamp1, 'database'); + self::$profiler->saveTimestamp($stamp1, 'database', System::callstack()); - if ($a->getConfigValue('system', 'db_log')) { + if (self::$configCache->get('system', 'db_log')) { $stamp2 = microtime(true); $duration = (float)($stamp2 - $stamp1); - if (($duration > $a->getConfigValue('system', 'db_loglimit'))) { + if (($duration > self::$configCache->get('system', 'db_loglimit'))) { $duration = round($duration, 3); $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($a->getConfigValue('system', 'db_log'), DateTimeFormat::utcNow()."\t".$duration."\t". + @file_put_contents(self::$configCache->get('system', 'db_log'), DateTimeFormat::utcNow()."\t".$duration."\t". basename($backtrace[1]["file"])."\t". $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t". substr(self::replaceParameters($sql, $args), 0, 2000)."\n", FILE_APPEND); @@ -586,13 +614,13 @@ class DBA /** * @brief Executes a prepared statement like UPDATE or INSERT that doesn't return data * - * Please use dba::delete, dba::insert, dba::update, ... instead + * Please use DBA::delete, DBA::insert, DBA::update, ... instead * * @param string $sql SQL statement * @return boolean Was the query successfull? False is returned only if an error occurred + * @throws \Exception */ public static function e($sql) { - $a = get_app(); $stamp = microtime(true); @@ -621,13 +649,13 @@ class DBA $error = self::$error; $errorno = self::$errorno; - logger('DB Error '.self::$errorno.': '.self::$error."\n". + Logger::log('DB Error '.self::$errorno.': '.self::$error."\n". System::callstack(8)."\n".self::replaceParameters($sql, $params)); // On a lost connection we simply quit. // A reconnect like in self::p could be dangerous with modifications if ($errorno == 2006) { - logger('Giving up because of database error '.$errorno.': '.$error); + Logger::log('Giving up because of database error '.$errorno.': '.$error); exit(1); } @@ -635,7 +663,7 @@ class DBA self::$errorno = $errorno; } - $a->save_timestamp($stamp, "database_write"); + self::$profiler->saveTimestamp($stamp, "database_write", System::callstack()); return $retval; } @@ -643,10 +671,11 @@ class DBA /** * @brief Check if data exists * - * @param string $table Table name - * @param array $condition array of fields for condition + * @param string $table Table name + * @param array $condition array of fields for condition * * @return boolean Are there rows for that condition? + * @throws \Exception */ public static function exists($table, $condition) { if (empty($table)) { @@ -681,11 +710,12 @@ class DBA /** * Fetches the first row * - * Please use dba::selectFirst or dba::exists whenever this is possible. + * Please use DBA::selectFirst or DBA::exists whenever this is possible. * * @brief Fetches the first row * @param string $sql SQL statement * @return array first row of query + * @throws \Exception */ public static function fetchFirst($sql) { $params = self::getParam(func_get_args()); @@ -756,7 +786,6 @@ class DBA * @return array current row */ public static function fetch($stmt) { - $a = get_app(); $stamp1 = microtime(true); @@ -803,7 +832,7 @@ class DBA } } - $a->save_timestamp($stamp1, 'database'); + self::$profiler->saveTimestamp($stamp1, 'database', System::callstack()); return $columns; } @@ -811,16 +840,17 @@ class DBA /** * @brief Insert a row into a table * - * @param string $table Table name - * @param array $param parameter array - * @param bool $on_duplicate_update Do an update on a duplicate entry + * @param string $table Table name + * @param array $param parameter array + * @param bool $on_duplicate_update Do an update on a duplicate entry * - * @return boolean was the insert successfull? + * @return boolean was the insert successful? + * @throws \Exception */ public static function insert($table, $param, $on_duplicate_update = false) { if (empty($table) || empty($param)) { - logger('Table and fields have to be set'); + Logger::log('Table and fields have to be set'); return false; } @@ -862,6 +892,7 @@ class DBA * @param string $table Table name * * @return boolean was the lock successful? + * @throws \Exception */ public static function lock($table) { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html @@ -894,6 +925,7 @@ class DBA * @brief Unlocks all locked tables * * @return boolean was the unlock successful? + * @throws \Exception */ public static function unlock() { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html @@ -928,13 +960,11 @@ class DBA switch (self::$driver) { case 'pdo': - if (self::$connection->inTransaction()) { - break; - } - if (!self::$connection->beginTransaction()) { + if (!self::$connection->inTransaction() && !self::$connection->beginTransaction()) { return false; } break; + case 'mysqli': if (!self::$connection->begin_transaction()) { return false; @@ -953,10 +983,13 @@ class DBA if (!self::$connection->inTransaction()) { return true; } + return self::$connection->commit(); + case 'mysqli': return self::$connection->commit(); } + return true; } @@ -989,6 +1022,7 @@ class DBA } $ret = self::$connection->rollBack(); break; + case 'mysqli': $ret = self::$connection->rollback(); break; @@ -1005,7 +1039,7 @@ class DBA * This process must only be started once, since the value is cached. */ private static function buildRelationData() { - $definition = DBStructure::definition(); + $definition = DBStructure::definition(self::$basePath); foreach ($definition AS $table => $structure) { foreach ($structure['fields'] AS $field => $field_struct) { @@ -1021,20 +1055,20 @@ class DBA /** * @brief Delete a row from a table * - * @param string $table Table name - * @param array $conditions Field condition(s) - * @param array $options - * - cascade: If true we delete records in other tables that depend on the one we're deleting through + * @param string $table Table name + * @param array $conditions Field condition(s) + * @param array $options + * - cascade: If true we delete records in other tables that depend on the one we're deleting through * relations (default: true) - * @param boolean $in_process Internal use: Only do a commit after the last delete - * @param array $callstack Internal use: prevent endless loops + * @param array $callstack Internal use: prevent endless loops * - * @return boolean|array was the delete successful? When $in_process is set: deletion data + * @return boolean was the delete successful? + * @throws \Exception */ - public static function delete($table, array $conditions, array $options = [], $in_process = false, array &$callstack = []) + public static function delete($table, array $conditions, array $options = [], array &$callstack = []) { if (empty($table) || empty($conditions)) { - logger('Table and conditions have to be set'); + Logger::log('Table and conditions have to be set'); return false; } @@ -1054,7 +1088,12 @@ class DBA $commands[$key] = ['table' => $table, 'conditions' => $conditions]; - $cascade = defaults($options, 'cascade', true); + // Don't use "defaults" here, since it would set "false" to "true" + if (isset($options['cascade'])) { + $cascade = $options['cascade']; + } else { + $cascade = true; + } // To speed up the whole process we cache the table relations if ($cascade && count(self::$relation) == 0) { @@ -1075,22 +1114,18 @@ class DBA if ((count($conditions) == 1) && ($field == array_keys($conditions)[0])) { foreach ($rel_def AS $rel_table => $rel_fields) { foreach ($rel_fields AS $rel_field) { - $retval = self::delete($rel_table, [$rel_field => array_values($conditions)[0]], $options, true, $callstack); - $commands = array_merge($commands, $retval); + self::delete($rel_table, [$rel_field => array_values($conditions)[0]], $options, $callstack); } } // We quit when this key already exists in the callstack. } elseif (!isset($callstack[$qkey])) { - $callstack[$qkey] = true; // Fetch all rows that are to be deleted $data = self::select($table, [$field], $conditions); while ($row = self::fetch($data)) { - // Now we accumulate the delete commands - $retval = self::delete($table, [$field => $row[$field]], $options, true, $callstack); - $commands = array_merge($commands, $retval); + self::delete($table, [$field => $row[$field]], $options, $callstack); } self::close($data); @@ -1100,74 +1135,70 @@ class DBA } } - if (!$in_process) { - // Now we finalize the process - $do_transaction = !self::$in_transaction; + // Now we finalize the process + $do_transaction = !self::$in_transaction; - if ($do_transaction) { - self::transaction(); - } + if ($do_transaction) { + self::transaction(); + } - $compacted = []; - $counter = []; + $compacted = []; + $counter = []; - foreach ($commands AS $command) { - $conditions = $command['conditions']; - reset($conditions); - $first_key = key($conditions); + foreach ($commands AS $command) { + $conditions = $command['conditions']; + reset($conditions); + $first_key = key($conditions); - $condition_string = self::buildCondition($conditions); + $condition_string = self::buildCondition($conditions); - if ((count($command['conditions']) > 1) || is_int($first_key)) { - $sql = "DELETE FROM `" . $command['table'] . "`" . $condition_string; - logger(self::replaceParameters($sql, $conditions), LOGGER_DATA); + if ((count($command['conditions']) > 1) || is_int($first_key)) { + $sql = "DELETE FROM `" . $command['table'] . "`" . $condition_string; + Logger::log(self::replaceParameters($sql, $conditions), Logger::DATA); - if (!self::e($sql, $conditions)) { - if ($do_transaction) { - self::rollback(); - } - return false; - } - } else { - $key_table = $command['table']; - $key_condition = array_keys($command['conditions'])[0]; - $value = array_values($command['conditions'])[0]; - - // Split the SQL queries in chunks of 100 values - // We do the $i stuff here to make the code better readable - $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0; - if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) { - ++$i; + if (!self::e($sql, $conditions)) { + if ($do_transaction) { + self::rollback(); } - - $compacted[$key_table][$key_condition][$i][$value] = $value; - $counter[$key_table][$key_condition] = $i; + return false; + } + } else { + $key_table = $command['table']; + $key_condition = array_keys($command['conditions'])[0]; + $value = array_values($command['conditions'])[0]; + + // Split the SQL queries in chunks of 100 values + // We do the $i stuff here to make the code better readable + $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0; + if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) { + ++$i; } + + $compacted[$key_table][$key_condition][$i][$value] = $value; + $counter[$key_table][$key_condition] = $i; } - foreach ($compacted AS $table => $values) { - foreach ($values AS $field => $field_value_list) { - foreach ($field_value_list AS $field_values) { - $sql = "DELETE FROM `" . $table . "` WHERE `" . $field . "` IN (" . - substr(str_repeat("?, ", count($field_values)), 0, -2) . ");"; + } + foreach ($compacted AS $table => $values) { + foreach ($values AS $field => $field_value_list) { + foreach ($field_value_list AS $field_values) { + $sql = "DELETE FROM `" . $table . "` WHERE `" . $field . "` IN (" . + substr(str_repeat("?, ", count($field_values)), 0, -2) . ");"; - logger(self::replaceParameters($sql, $field_values), LOGGER_DATA); + Logger::log(self::replaceParameters($sql, $field_values), Logger::DATA); - if (!self::e($sql, $field_values)) { - if ($do_transaction) { - self::rollback(); - } - return false; + if (!self::e($sql, $field_values)) { + if ($do_transaction) { + self::rollback(); } + return false; } } } - if ($do_transaction) { - self::commit(); - } - return true; } - - return $commands; + if ($do_transaction) { + self::commit(); + } + return true; } /** @@ -1191,17 +1222,18 @@ class DBA * Only set $old_fields to a boolean value when you are sure that you will update a single row. * When you set $old_fields to "true" then $fields must contain all relevant fields! * - * @param string $table Table name - * @param array $fields contains the fields that are updated - * @param array $condition condition array with the key values + * @param string $table Table name + * @param array $fields contains the fields that are updated + * @param array $condition condition array with the key values * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate) * * @return boolean was the update successfull? + * @throws \Exception */ public static function update($table, $fields, $condition, $old_fields = []) { if (empty($table) || empty($fields) || empty($condition)) { - logger('Table, fields and condition have to be set'); + Logger::log('Table, fields and condition have to be set'); return false; } @@ -1227,7 +1259,7 @@ class DBA foreach ($old_fields AS $fieldname => $content) { if (isset($fields[$fieldname])) { - if ($fields[$fieldname] == $content) { + if (($fields[$fieldname] == $content) && !is_null($content)) { unset($fields[$fieldname]); } else { $do_update = true; @@ -1258,7 +1290,8 @@ class DBA * @param array $condition * @param array $params * @return bool|array - * @see self::select + * @throws \Exception + * @see self::select */ public static function selectFirst($table, array $fields = [], array $condition = [], $params = []) { @@ -1294,7 +1327,8 @@ class DBA * * $params = array("order" => array("id", "received" => true), "limit" => 10); * - * $data = dba::select($table, $fields, $condition, $params); + * $data = DBA::select($table, $fields, $condition, $params); + * @throws \Exception */ public static function select($table, array $fields = [], array $condition = [], array $params = []) { @@ -1324,8 +1358,8 @@ class DBA /** * @brief Counts the rows from a table satisfying the provided condition * - * @param string $table Table name - * @param array $condition array of fields for condition + * @param string $table Table name + * @param array $condition array of fields for condition * * @return int * @@ -1336,7 +1370,8 @@ class DBA * or: * $condition = ["`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr']; * - * $count = dba::count($table, $condition); + * $count = DBA::count($table, $condition); + * @throws \Exception */ public static function count($table, array $condition = []) { @@ -1390,7 +1425,7 @@ class DBA /* Workaround for MySQL Bug #64791. * Never mix data types inside any IN() condition. * In case of mixed types, cast all as string. - * Logic needs to be consistent with dba::p() data types. + * Logic needs to be consistent with DBA::p() data types. */ $is_int = false; $is_alpha = false; @@ -1450,7 +1485,7 @@ class DBA $limit_string = ''; if (isset($params['limit']) && is_int($params['limit'])) { - $limit_string = " LIMIT " . $params['limit']; + $limit_string = " LIMIT " . intval($params['limit']); } if (isset($params['limit']) && is_array($params['limit'])) { @@ -1464,6 +1499,7 @@ class DBA * @brief Fills an array with data from a query * * @param object $stmt statement object + * @param bool $do_close * @return array Data array */ public static function toArray($stmt, $do_close = true) { @@ -1506,7 +1542,6 @@ class DBA * @return boolean was the close successful? */ public static function close($stmt) { - $a = get_app(); $stamp1 = microtime(true); @@ -1521,7 +1556,7 @@ class DBA case 'mysqli': // MySQLi offers both a mysqli_stmt and a mysqli_result class. // We should be careful not to assume the object type of $stmt - // because dba::p() has been able to return both types. + // because DBA::p() has been able to return both types. if ($stmt instanceof mysqli_stmt) { $stmt->free_result(); $ret = $stmt->close(); @@ -1534,7 +1569,7 @@ class DBA break; } - $a->save_timestamp($stamp1, 'database'); + self::$profiler->saveTimestamp($stamp1, 'database', System::callstack()); return $ret; } @@ -1545,14 +1580,13 @@ class DBA * @return array * 'list' => List of processes, separated in their different states * 'amount' => Number of concurrent database processes + * @throws \Exception */ public static function processlist() { $ret = self::p("SHOW PROCESSLIST"); $data = self::toArray($ret); - $s = []; - $processes = 0; $states = []; foreach ($data as $process) { @@ -1610,7 +1644,7 @@ class DBA if (is_bool($value)) { $value = ($value ? '1' : '0'); } else { - $value = dbesc($value); + $value = self::escape($value); } return; } @@ -1620,7 +1654,7 @@ class DBA } elseif (is_float($value) || is_integer($value)) { $value = (string) $value; } else { - $value = "'" . dbesc($value) . "'"; + $value = "'" . self::escape($value) . "'"; } }