X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBA.php;h=097f1b756a19367574c35352029e772d94e654ce;hb=ead0cf370aa985b895e3db46cd7e3b1dc2bdfff6;hp=1cf10ba4addd8472edf9a95359ec20acd7258ff0;hpb=388b963714895b6aee59648084fe4a66a0964efc;p=friendica.git diff --git a/src/Database/DBA.php b/src/Database/DBA.php index 1cf10ba4ad..097f1b756a 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -1,4 +1,23 @@ . + * + */ namespace Friendica\Database; @@ -10,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 @@ -56,7 +73,17 @@ class DBA } /** - * @brief Returns the MySQL server version string + * Return the database driver string + * + * @return string with either "pdo" or "mysqli" + */ + public static function getDriver() + { + return DI::dba()->getDriver(); + } + + /** + * 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 @@ -69,7 +96,7 @@ class DBA } /** - * @brief Returns the selected database name + * Returns the selected database name * * @return string * @throws \Exception @@ -79,19 +106,30 @@ class DBA return DI::dba()->databaseName(); } + /** + * Escape all SQL unsafe data + * + * @param string $str + * @return string escaped string + */ public static function escape($str) { return DI::dba()->escape($str); } + /** + * Checks if the database is connected + * + * @return boolean is the database connected? + */ public static function 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 @@ -106,7 +144,7 @@ class DBA } /** - * @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. @@ -127,7 +165,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 */ @@ -144,8 +182,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 `post` WHERE `guid` = ?", $guid); * * Please only use it with complicated queries. * For all regular queries please use DBA::select or DBA::exists @@ -162,7 +200,7 @@ class DBA } /** - * @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 * @@ -178,7 +216,7 @@ class DBA } /** - * @brief Check if data exists + * Check if data exists * * @param string|array $table Table name or array [schema => table] * @param array $condition array of fields for condition @@ -196,7 +234,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 @@ -209,7 +246,7 @@ class DBA } /** - * @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 */ @@ -219,7 +256,7 @@ class DBA } /** - * @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 @@ -229,7 +266,7 @@ class DBA 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 @@ -240,7 +277,7 @@ class DBA } /** - * @brief Fetch a single row + * Fetch a single row * * @param mixed $stmt statement object * @return array current row @@ -251,22 +288,37 @@ class DBA } /** - * @brief Insert a row into a table + * Insert a row into a table * - * @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 + * @param string|array $table Table name or array [schema => table] + * @param array $param parameter array + * @param int $duplicate_mode What to do on a duplicated entry * * @return boolean was the insert successful? * @throws \Exception */ - public static function insert($table, $param, $on_duplicate_update = false) + public static function insert($table, array $param, int $duplicate_mode = Database::INSERT_DEFAULT) { - return DI::dba()->insert($table, $param, $on_duplicate_update); + return DI::dba()->insert($table, $param, $duplicate_mode); } /** - * @brief Fetch the id of the last insert command + * Inserts a row with the provided data in the provided table. + * If the data corresponds to an existing row through a UNIQUE or PRIMARY index constraints, it updates the row instead. + * + * @param string|array $table Table name or array [schema => table] + * @param array $param parameter array + * + * @return boolean was the insert successful? + * @throws \Exception + */ + public static function replace($table, $param) + { + return DI::dba()->replace($table, $param); + } + + /** + * Fetch the id of the last insert command * * @return integer Last inserted id */ @@ -276,7 +328,7 @@ class DBA } /** - * @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. * @@ -291,7 +343,7 @@ class DBA } /** - * @brief Unlocks all locked tables + * Unlocks all locked tables * * @return boolean was the unlock successful? * @throws \Exception @@ -302,7 +354,7 @@ class DBA } /** - * @brief Starts a transaction + * Starts a transaction * * @return boolean Was the command executed successfully? */ @@ -312,7 +364,7 @@ class DBA } /** - * @brief Does a commit + * Does a commit * * @return boolean Was the command executed successfully? */ @@ -322,7 +374,7 @@ class DBA } /** - * @brief Does a rollback + * Does a rollback * * @return boolean Was the command executed successfully? */ @@ -332,13 +384,10 @@ class DBA } /** - * @brief Delete a row from a table + * Delete a row from a table * * @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) * * @return boolean was the delete successful? * @throws \Exception @@ -349,9 +398,9 @@ class DBA } /** - * @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: @@ -385,7 +434,6 @@ class DBA /** * Retrieve a single record from a table and returns it in an associative array * - * @brief Retrieve a single record from a table * @param string|array $table Table name or array [schema => table] * @param array $fields * @param array $condition @@ -400,7 +448,7 @@ class DBA } /** - * @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|array $table Table name or array [schema => table] * @param array $fields Array of selected fields, empty for all @@ -417,7 +465,7 @@ class DBA } /** - * @brief Select rows from a table + * Select rows from a table * * @param string|array $table Table name or array [schema => table] * @param array $fields Array of selected fields, empty for all @@ -427,7 +475,7 @@ class DBA * @return boolean|object * * Example: - * $table = "item"; + * $table = "post"; * $fields = array("id", "uri", "uid", "network"); * * $condition = array("uid" => 1, "network" => 'dspr'); @@ -445,7 +493,7 @@ class DBA } /** - * @brief Counts the rows from a table satisfying the provided condition + * Counts the rows from a table satisfying the provided condition * * @param string|array $table Table name or array [schema => table] * @param array $condition array of fields for condition @@ -454,7 +502,7 @@ class DBA * @return int * * Example: - * $table = "item"; + * $table = "post"; * * $condition = ["uid" => 1, "network" => 'dspr']; * or: @@ -510,10 +558,10 @@ class DBA } /** - * @brief Returns the SQL condition string built from the provided condition array + * 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 + * - Supplied with a field/value associative array, it builds simple strict * equality conditions linked by AND. * - Supplied with a flat list, the first element is the condition string and * the following arguments are the values to be interpolated @@ -529,69 +577,148 @@ 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)) { - 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; - } - } + $condition_string = " WHERE (" . array_shift($condition) . ")"; + } - if ($is_int && $is_alpha) { - foreach ($value as &$ref) { - if (is_int($ref)) { - $ref = (string)$ref; - } - } - unset($ref); //Prevent accidental re-use. - } + 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); - $new_values = array_merge($new_values, array_values($value)); - $placeholders = substr(str_repeat("?, ", count($value)), 0, -2); - $condition_string .= self::quoteIdentifier($field) . " IN (" . $placeholders . ")"; + 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 { - // Empty value array isn't supported by IN and is logically equivalent to no match - $condition_string .= "FALSE"; + $is_alpha = true; } - } elseif (is_null($value)) { - $condition_string .= self::quoteIdentifier($field) . " IS NULL"; - } else { - $new_values[$field] = $value; - $condition_string .= self::quoteIdentifier($field) . " = ?"; } + + if ($is_int && $is_alpha) { + foreach ($value as &$ref) { + if (is_int($ref)) { + $ref = (string)$ref; + } + } + 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 + * Merges the provided conditions into a single collapsed one + * + * @param array ...$conditions One or more condition arrays + * @return array A collapsed condition + * @see DBA::collapseCondition() for the condition array formats + */ + public static function mergeConditions(array ...$conditions) + { + if (count($conditions) == 1) { + return current($conditions); + } + + $conditionStrings = []; + $result = []; + + foreach ($conditions as $key => $condition) { + if (!$condition) { + continue; + } + + $condition = self::collapseCondition($condition); + + $conditionStrings[] = array_shift($condition); + // The result array holds the eventual parameter values + $result = array_merge($result, $condition); + } + + if (count($conditionStrings)) { + // We prepend the condition string at the end to form a collapsed condition array again + array_unshift($result, implode(' AND ', $conditionStrings)); + } + + return $result; + } + + /** + * 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 @@ -610,7 +737,11 @@ class DBA if ($order === 'RAND()') { $order_string .= "RAND(), "; } elseif (!is_int($fields)) { - $order_string .= self::quoteIdentifier($fields) . " " . ($order ? "DESC" : "ASC") . ", "; + if ($order !== 'DESC' && $order !== 'ASC') { + $order = $order ? 'DESC' : 'ASC'; + } + + $order_string .= self::quoteIdentifier($fields) . " " . $order . ", "; } else { $order_string .= self::quoteIdentifier($order) . ", "; } @@ -631,19 +762,33 @@ 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 Close database connection after last row + * @param int $count maximum number of rows to be fetched * - * @param object $stmt statement object - * @param bool $do_close * @return array Data array */ - public static function toArray($stmt, $do_close = true) + public static function toArray($stmt, $do_close = true, int $count = 0) { - return DI::dba()->toArray($stmt, $do_close); + return DI::dba()->toArray($stmt, $do_close, $count); } /** - * @brief Returns the error number of the last query + * Cast field types according to the table definition + * + * @param string $table + * @param array $fields + * @return array casted fields + */ + public static function castFields(string $table, array $fields) + { + return DI::dba()->castFields($table, $fields); + } + + /** + * Returns the error number of the last query * * @return string Error number (0 if no error) */ @@ -653,7 +798,7 @@ class DBA } /** - * @brief Returns the error message of the last query + * Returns the error message of the last query * * @return string Error message ('' if no error) */ @@ -663,7 +808,7 @@ class DBA } /** - * @brief Closes the current statement + * Closes the current statement * * @param object $stmt statement object * @return boolean was the close successful? @@ -674,7 +819,7 @@ class DBA } /** - * @brief Return a list of database processes + * Return a list of database processes * * @return array * 'list' => List of processes, separated in their different states @@ -686,6 +831,17 @@ class DBA 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); + } + /** * Checks if $array is a filled array with at least one entry. * @@ -699,7 +855,7 @@ class DBA } /** - * @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