X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBA.php;h=f3edf52be5f1e973f940402db42ff6cc90b5d16b;hb=f4e1acedfcd86c81403c14747ecfd3b11583506e;hp=4619bc38b821323877d4f0919c45a78d21d29982;hpb=555c444b4e283261d2efe376b5803e0adbf5e214;p=friendica.git diff --git a/src/Database/DBA.php b/src/Database/DBA.php index 4619bc38b8..f3edf52be5 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -1,7 +1,27 @@ . + * + */ namespace Friendica\Database; +use Friendica\DI; use mysqli; use mysqli_result; use mysqli_stmt; @@ -9,8 +29,6 @@ use PDO; use PDOStatement; /** - * @class MySQL database class - * * This class is for the low level database stuff that does driver specific things. */ class DBA @@ -24,19 +42,9 @@ class DBA */ const NULL_DATETIME = '0001-01-01 00:00:00'; - /** - * @var Database - */ - private static $database; - - public static function init(Database $database) - { - self::$database = $database; - } - public static function connect() { - return self::$database->connect(); + return DI::dba()->connect(); } /** @@ -44,7 +52,7 @@ class DBA */ public static function disconnect() { - self::$database->disconnect(); + DI::dba()->disconnect(); } /** @@ -52,7 +60,7 @@ class DBA */ public static function reconnect() { - return self::$database->reconnect(); + return DI::dba()->reconnect(); } /** @@ -61,11 +69,11 @@ class DBA */ public static function getConnection() { - return self::$database->getConnection(); + return DI::dba()->getConnection(); } /** - * @brief Returns the MySQL server version string + * Returns the MySQL server version string * * This function discriminate between the deprecated mysql API and the current * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1 @@ -74,33 +82,44 @@ class DBA */ public static function serverInfo() { - return self::$database->serverInfo(); + return DI::dba()->serverInfo(); } /** - * @brief Returns the selected database name + * Returns the selected database name * * @return string * @throws \Exception */ public static function databaseName() { - return self::$database->databaseName(); + return DI::dba()->databaseName(); } + /** + * Escape all SQL unsafe data + * + * @param string $str + * @return string escaped string + */ public static function escape($str) { - return self::$database->escape($str); + return DI::dba()->escape($str); } + /** + * Checks if the database is connected + * + * @return boolean is the database connected? + */ public static function connected() { - return self::$database->connected(); + return DI::dba()->connected(); } /** - * @brief Replaces ANY_VALUE() function by MIN() function, - * if the database server does not support ANY_VALUE(). + * Replaces ANY_VALUE() function by MIN() function, + * if the database server does not support ANY_VALUE(). * * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5). * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html @@ -111,11 +130,11 @@ class DBA */ public static function anyValueFallback($sql) { - return self::$database->anyValueFallback($sql); + return DI::dba()->anyValueFallback($sql); } /** - * @brief beautifies the query - useful for "SHOW PROCESSLIST" + * beautifies the query - useful for "SHOW PROCESSLIST" * * This is safe when we bind the parameters later. * The parameter values aren't part of the SQL. @@ -136,7 +155,7 @@ class DBA } /** - * @brief Convert parameter array to an universal form + * Convert parameter array to an universal form * @param array $args Parameter array * @return array universalized parameter array */ @@ -153,8 +172,8 @@ class DBA } /** - * @brief Executes a prepared statement that returns data - * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid); + * Executes a prepared statement that returns data + * 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 @@ -167,11 +186,11 @@ class DBA { $params = self::getParam(func_get_args()); - return self::$database->p($sql, $params); + return DI::dba()->p($sql, $params); } /** - * @brief Executes a prepared statement like UPDATE or INSERT that doesn't return data + * Executes a prepared statement like UPDATE or INSERT that doesn't return data * * Please use DBA::delete, DBA::insert, DBA::update, ... instead * @@ -183,21 +202,21 @@ class DBA $params = self::getParam(func_get_args()); - return self::$database->e($sql, $params); + return DI::dba()->e($sql, $params); } /** - * @brief Check if data exists + * Check if data exists * - * @param string $table Table name - * @param array $condition array of fields for condition + * @param string|array $table Table name or array [schema => table] + * @param array $condition array of fields for condition * * @return boolean Are there rows for that condition? * @throws \Exception */ public static function exists($table, $condition) { - return self::$database->exists($table, $condition); + return DI::dba()->exists($table, $condition); } /** @@ -205,7 +224,6 @@ class DBA * * 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 @@ -214,138 +232,138 @@ class DBA { $params = self::getParam(func_get_args()); - return self::$database->fetchFirst($sql, $params); + return DI::dba()->fetchFirst($sql, $params); } /** - * @brief Returns the number of affected rows of the last statement + * Returns the number of affected rows of the last statement * * @return int Number of rows */ public static function affectedRows() { - return self::$database->affectedRows(); + return DI::dba()->affectedRows(); } /** - * @brief Returns the number of columns of a statement + * Returns the number of columns of a statement * * @param object Statement object * @return int Number of columns */ public static function columnCount($stmt) { - return self::$database->columnCount($stmt); + return DI::dba()->columnCount($stmt); } /** - * @brief Returns the number of rows of a statement + * Returns the number of rows of a statement * * @param PDOStatement|mysqli_result|mysqli_stmt Statement object * @return int Number of rows */ public static function numRows($stmt) { - return self::$database->numRows($stmt); + return DI::dba()->numRows($stmt); } /** - * @brief Fetch a single row + * Fetch a single row * * @param mixed $stmt statement object * @return array current row */ public static function fetch($stmt) { - return self::$database->fetch($stmt); + return DI::dba()->fetch($stmt); } /** - * @brief Insert a row into a table + * 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|array $table Table name or array [schema => table] + * @param array $param parameter array + * @param bool $on_duplicate_update Do an update on a duplicate entry * * @return boolean was the insert successful? * @throws \Exception */ public static function insert($table, $param, $on_duplicate_update = false) { - return self::$database->insert($table, $param, $on_duplicate_update); + return DI::dba()->insert($table, $param, $on_duplicate_update); } /** - * @brief Fetch the id of the last insert command + * Fetch the id of the last insert command * * @return integer Last inserted id */ public static function lastInsertId() { - return self::$database->lastInsertId(); + return DI::dba()->lastInsertId(); } /** - * @brief Locks a table for exclusive write access + * Locks a table for exclusive write access * * This function can be extended in the future to accept a table array as well. * - * @param string $table Table name + * @param string|array $table Table name or array [schema => table] * * @return boolean was the lock successful? * @throws \Exception */ public static function lock($table) { - return self::$database->lock($table); + return DI::dba()->lock($table); } /** - * @brief Unlocks all locked tables + * Unlocks all locked tables * * @return boolean was the unlock successful? * @throws \Exception */ public static function unlock() { - return self::$database->unlock(); + return DI::dba()->unlock(); } /** - * @brief Starts a transaction + * Starts a transaction * * @return boolean Was the command executed successfully? */ public static function transaction() { - return self::$database->transaction(); + return DI::dba()->transaction(); } /** - * @brief Does a commit + * Does a commit * * @return boolean Was the command executed successfully? */ public static function commit() { - return self::$database->commit(); + return DI::dba()->commit(); } /** - * @brief Does a rollback + * Does a rollback * * @return boolean Was the command executed successfully? */ public static function rollback() { - return self::$database->rollback(); + return DI::dba()->rollback(); } /** - * @brief Delete a row from a table + * Delete a row from a table * - * @param string $table Table name - * @param array $conditions Field condition(s) - * @param array $options + * @param string|array $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) * @@ -354,13 +372,13 @@ class DBA */ public static function delete($table, array $conditions, array $options = []) { - return self::$database->delete($table, $conditions, $options); + return DI::dba()->delete($table, $conditions, $options); } /** - * @brief Updates rows + * Updates rows in the database. * - * Updates rows in the database. When $old_fields is set to an array, + * When $old_fields is set to an array, * the system will only do an update if the fields in that array changed. * * Attention: @@ -378,7 +396,7 @@ 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 string|array $table Table name or array [schema => table] * @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) @@ -388,33 +406,32 @@ class DBA */ public static function update($table, $fields, $condition, $old_fields = []) { - return self::$database->update($table, $fields, $condition, $old_fields); + return DI::dba()->update($table, $fields, $condition, $old_fields); } /** * Retrieve a single record from a table and returns it in an associative array * - * @brief Retrieve a single record from a table - * @param string $table - * @param array $fields - * @param array $condition - * @param array $params + * @param string|array $table Table name or array [schema => table] + * @param array $fields + * @param array $condition + * @param array $params * @return bool|array * @throws \Exception * @see self::select */ public static function selectFirst($table, array $fields = [], array $condition = [], $params = []) { - return self::$database->selectFirst($table, $fields, $condition, $params); + return DI::dba()->selectFirst($table, $fields, $condition, $params); } /** - * @brief Select rows from a table and fills an array with the data + * Select rows from a table and fills an array with the data * - * @param string $table Table name - * @param array $fields Array of selected fields, empty for all - * @param array $condition Array of fields for condition - * @param array $params Array of several parameters + * @param string|array $table Table name or array [schema => table] + * @param array $fields Array of selected fields, empty for all + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * * @return array Data array * @throws \Exception @@ -422,16 +439,16 @@ class DBA */ public static function selectToArray($table, array $fields = [], array $condition = [], array $params = []) { - return self::$database->selectToArray($table, $fields, $condition, $params); + return DI::dba()->selectToArray($table, $fields, $condition, $params); } /** - * @brief Select rows from a table + * Select rows from a table * - * @param string $table Table name - * @param array $fields Array of selected fields, empty for all - * @param array $condition Array of fields for condition - * @param array $params Array of several parameters + * @param string|array $table Table name or array [schema => table] + * @param array $fields Array of selected fields, empty for all + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * * @return boolean|object * @@ -450,14 +467,15 @@ class DBA */ public static function select($table, array $fields = [], array $condition = [], array $params = []) { - return self::$database->select($table, $fields, $condition, $params); + return DI::dba()->select($table, $fields, $condition, $params); } /** - * @brief Counts the rows from a table satisfying the provided condition + * 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|array $table Table name or array [schema => table] + * @param array $condition array of fields for condition + * @param array $params Array of several parameters * * @return int * @@ -471,13 +489,54 @@ class DBA * $count = DBA::count($table, $condition); * @throws \Exception */ - public static function count($table, array $condition = []) + public static function count($table, array $condition = [], array $params = []) { - return self::$database->count($table, $condition); + return DI::dba()->count($table, $condition, $params); } /** - * @brief Returns the SQL condition string built from the provided condition array + * Build the table query substring from one or more tables, with or without a schema. + * + * Expected formats: + * - table + * - [table1, table2, ...] + * - [schema1 => table1, schema2 => table2, table3, ...] + * + * @param string|array $tables + * @return string + */ + public static function buildTableString($tables) + { + if (is_string($tables)) { + $tables = [$tables]; + } + + $quotedTables = []; + + foreach ($tables as $schema => $table) { + if (is_numeric($schema)) { + $quotedTables[] = self::quoteIdentifier($table); + } else { + $quotedTables[] = self::quoteIdentifier($schema) . '.' . self::quoteIdentifier($table); + } + } + + return implode(', ', $quotedTables); + } + + /** + * Escape an identifier (table or field name) + * + * @param $identifier + * @return string + */ + public static function quoteIdentifier($identifier) + { + return '`' . str_replace('`', '``', $identifier) . '`'; + } + + /** + * Returns the SQL condition string built from the provided condition array * * This function operates with two modes. * - Supplied with a filed/value associative array, it builds simple strict @@ -496,64 +555,112 @@ class DBA */ public static function buildCondition(array &$condition = []) { + $condition = self::collapseCondition($condition); + $condition_string = ''; if (count($condition) > 0) { - reset($condition); - $first_key = key($condition); - if (is_int($first_key)) { - $condition_string = " WHERE (" . array_shift($condition) . ")"; - } else { - $new_values = []; - $condition_string = ""; - foreach ($condition as $field => $value) { - if ($condition_string != "") { - $condition_string .= " AND "; - } - if (is_array($value)) { - /* 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. - */ - $is_int = false; - $is_alpha = false; - foreach ($value as $single_value) { - if (is_int($single_value)) { - $is_int = true; - } else { - $is_alpha = true; - } + $condition_string = " WHERE (" . array_shift($condition) . ")"; + } + + return $condition_string; + } + + /** + * Collapse an associative array condition into a SQL string + parameters condition array. + * + * ['uid' => 1, 'network' => ['dspr', 'apub']] + * + * gets transformed into + * + * ["`uid` = ? AND `network` IN (?, ?)", 1, 'dspr', 'apub'] + * + * @param array $condition + * @return array + */ + public static function collapseCondition(array $condition) + { + // Ensures an always true condition is returned + if (count($condition) < 1) { + return ['1']; + } + + reset($condition); + $first_key = key($condition); + + if (is_int($first_key)) { + // Already collapsed + return $condition; + } + + $values = []; + $condition_string = ""; + foreach ($condition as $field => $value) { + if ($condition_string != "") { + $condition_string .= " AND "; + } + + if (is_array($value)) { + if (count($value)) { + /* 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. + */ + $is_int = false; + $is_alpha = false; + foreach ($value as $single_value) { + if (is_int($single_value)) { + $is_int = true; + } else { + $is_alpha = true; } + } - if ($is_int && $is_alpha) { - foreach ($value as &$ref) { - if (is_int($ref)) { - $ref = (string)$ref; - } + if ($is_int && $is_alpha) { + foreach ($value as &$ref) { + if (is_int($ref)) { + $ref = (string)$ref; } - unset($ref); //Prevent accidental re-use. } - - $new_values = array_merge($new_values, array_values($value)); - $placeholders = substr(str_repeat("?, ", count($value)), 0, -2); - $condition_string .= "`" . $field . "` IN (" . $placeholders . ")"; - } elseif (is_null($value)) { - $condition_string .= "`" . $field . "` IS NULL"; - } else { - $new_values[$field] = $value; - $condition_string .= "`" . $field . "` = ?"; + unset($ref); //Prevent accidental re-use. } + + $values = array_merge($values, array_values($value)); + $placeholders = substr(str_repeat("?, ", count($value)), 0, -2); + $condition_string .= self::quoteIdentifier($field) . " IN (" . $placeholders . ")"; + } else { + // Empty value array isn't supported by IN and is logically equivalent to no match + $condition_string .= "FALSE"; } - $condition_string = " WHERE (" . $condition_string . ")"; - $condition = $new_values; + } elseif (is_null($value)) { + $condition_string .= self::quoteIdentifier($field) . " IS NULL"; + } else { + $values[$field] = $value; + $condition_string .= self::quoteIdentifier($field) . " = ?"; } } - return $condition_string; + $condition = array_merge([$condition_string], array_values($values)); + + return $condition; } /** - * @brief Returns the SQL parameter string built from the provided parameter array + * Returns the SQL parameter string built from the provided parameter array + * + * Expected format for each key: + * + * group_by: + * - list of column names + * + * order: + * - numeric keyed column name => ASC + * - associative element with boolean value => DESC (true), ASC (false) + * - associative element with string value => 'ASC' or 'DESC' literally + * + * limit: + * - single numeric value => count + * - list with two numeric values => offset, count * * @param array $params * @return string @@ -561,12 +668,8 @@ class DBA public static function buildParameter(array $params = []) { $groupby_string = ''; - if (isset($params['group_by'])) { - $groupby_string = " GROUP BY "; - foreach ($params['group_by'] as $fields) { - $groupby_string .= "`" . $fields . "`, "; - } - $groupby_string = substr($groupby_string, 0, -2); + if (!empty($params['group_by'])) { + $groupby_string = " GROUP BY " . implode(', ', array_map(['self', 'quoteIdentifier'], $params['group_by'])); } $order_string = ''; @@ -576,9 +679,13 @@ class DBA if ($order === 'RAND()') { $order_string .= "RAND(), "; } elseif (!is_int($fields)) { - $order_string .= "`" . $fields . "` " . ($order ? "DESC" : "ASC") . ", "; + if ($order !== 'DESC' && $order !== 'ASC') { + $order = $order ? 'DESC' : 'ASC'; + } + + $order_string .= self::quoteIdentifier($fields) . " " . $order . ", "; } else { - $order_string .= "`" . $order . "`, "; + $order_string .= self::quoteIdentifier($order) . ", "; } } $order_string = substr($order_string, 0, -2); @@ -597,7 +704,7 @@ class DBA } /** - * @brief Fills an array with data from a query + * Fills an array with data from a query * * @param object $stmt statement object * @param bool $do_close @@ -605,42 +712,42 @@ class DBA */ public static function toArray($stmt, $do_close = true) { - return self::$database->toArray($stmt, $do_close); + return DI::dba()->toArray($stmt, $do_close); } /** - * @brief Returns the error number of the last query + * Returns the error number of the last query * * @return string Error number (0 if no error) */ public static function errorNo() { - return self::$database->errorNo(); + return DI::dba()->errorNo(); } /** - * @brief Returns the error message of the last query + * Returns the error message of the last query * * @return string Error message ('' if no error) */ public static function errorMessage() { - return self::$database->errorMessage(); + return DI::dba()->errorMessage(); } /** - * @brief Closes the current statement + * Closes the current statement * * @param object $stmt statement object * @return boolean was the close successful? */ public static function close($stmt) { - return self::$database->close($stmt); + return DI::dba()->close($stmt); } /** - * @brief Return a list of database processes + * Return a list of database processes * * @return array * 'list' => List of processes, separated in their different states @@ -649,7 +756,18 @@ class DBA */ public static function processlist() { - return self::$database->processlist(); + return DI::dba()->processlist(); + } + + /** + * Fetch a database variable + * + * @param string $name + * @return string content + */ + public static function getVariable(string $name) + { + return DI::dba()->getVariable($name); } /** @@ -661,11 +779,11 @@ class DBA */ public static function isResult($array) { - return self::$database->isResult($array); + return DI::dba()->isResult($array); } /** - * @brief Escapes a whole array + * Escapes a whole array * * @param mixed $arr Array with values to be escaped * @param boolean $add_quotation add quotation marks for string values @@ -673,6 +791,6 @@ class DBA */ public static function escapeArray(&$arr, $add_quotation = false) { - return self::$database->escapeArray($arr, $add_quotation); + DI::dba()->escapeArray($arr, $add_quotation); } }