X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBA.php;h=9c607638b9c6050b8e069f591881ad3b20b49791;hb=6b8db5ad1333e2b430da3a771d9a962d44d4b6fc;hp=6e9bc89be1fca65e1e737f038fd2b1b086a2cdc7;hpb=9bf065c9d8343ff2d2b6b5a7514a78471a0bedd4;p=friendica.git diff --git a/src/Database/DBA.php b/src/Database/DBA.php index 6e9bc89be1..9c607638b9 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -2,6 +2,7 @@ namespace Friendica\Database; +use Friendica\DI; use mysqli; use mysqli_result; use mysqli_stmt; @@ -24,19 +25,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 +35,7 @@ class DBA */ public static function disconnect() { - self::$database->disconnect(); + DI::dba()->disconnect(); } /** @@ -52,7 +43,7 @@ class DBA */ public static function reconnect() { - return self::$database->reconnect(); + return DI::dba()->reconnect(); } /** @@ -61,7 +52,7 @@ class DBA */ public static function getConnection() { - return self::$database->getConnection(); + return DI::dba()->getConnection(); } /** @@ -74,7 +65,7 @@ class DBA */ public static function serverInfo() { - return self::$database->serverInfo(); + return DI::dba()->serverInfo(); } /** @@ -85,17 +76,17 @@ class DBA */ public static function databaseName() { - return self::$database->databaseName(); + return DI::dba()->databaseName(); } public static function escape($str) { - return self::$database->escape($str); + return DI::dba()->escape($str); } public static function connected() { - return self::$database->connected(); + return DI::dba()->connected(); } /** @@ -111,7 +102,7 @@ class DBA */ public static function anyValueFallback($sql) { - return self::$database->anyValueFallback($sql); + return DI::dba()->anyValueFallback($sql); } /** @@ -167,7 +158,7 @@ class DBA { $params = self::getParam(func_get_args()); - return self::$database->p($sql, $params); + return DI::dba()->p($sql, $params); } /** @@ -183,21 +174,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 * - * @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); } /** @@ -214,7 +205,7 @@ class DBA { $params = self::getParam(func_get_args()); - return self::$database->fetchFirst($sql, $params); + return DI::dba()->fetchFirst($sql, $params); } /** @@ -224,7 +215,7 @@ class DBA */ public static function affectedRows() { - return self::$database->affectedRows(); + return DI::dba()->affectedRows(); } /** @@ -235,7 +226,7 @@ class DBA */ public static function columnCount($stmt) { - return self::$database->columnCount($stmt); + return DI::dba()->columnCount($stmt); } /** * @brief Returns the number of rows of a statement @@ -245,7 +236,7 @@ class DBA */ public static function numRows($stmt) { - return self::$database->numRows($stmt); + return DI::dba()->numRows($stmt); } /** @@ -256,22 +247,22 @@ class DBA */ public static function fetch($stmt) { - return self::$database->fetch($stmt); + return DI::dba()->fetch($stmt); } /** * @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|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); } /** @@ -281,7 +272,7 @@ class DBA */ public static function lastInsertId() { - return self::$database->lastInsertId(); + return DI::dba()->lastInsertId(); } /** @@ -289,14 +280,14 @@ class DBA * * 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); } /** @@ -307,7 +298,7 @@ class DBA */ public static function unlock() { - return self::$database->unlock(); + return DI::dba()->unlock(); } /** @@ -317,7 +308,7 @@ class DBA */ public static function transaction() { - return self::$database->transaction(); + return DI::dba()->transaction(); } /** @@ -327,7 +318,7 @@ class DBA */ public static function commit() { - return self::$database->commit(); + return DI::dba()->commit(); } /** @@ -337,15 +328,15 @@ class DBA */ public static function rollback() { - return self::$database->rollback(); + return DI::dba()->rollback(); } /** * @brief 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,7 +345,7 @@ 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); } /** @@ -378,7 +369,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 +379,50 @@ 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 + * + * @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 + * @see self::select + */ + public static function selectToArray($table, array $fields = [], array $condition = [], array $params = []) + { + return DI::dba()->selectToArray($table, $fields, $condition, $params); } /** * @brief 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 * @@ -433,14 +441,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 * - * @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 * @@ -454,9 +463,50 @@ 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 DI::dba()->count($table, $condition, $params); + } + + /** + * 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 self::$database->count($table, $condition); + return '`' . str_replace('`', '``', $identifier) . '`'; } /** @@ -479,60 +529,94 @@ 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; } /** @@ -544,12 +628,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 = ''; @@ -559,9 +639,9 @@ class DBA if ($order === 'RAND()') { $order_string .= "RAND(), "; } elseif (!is_int($fields)) { - $order_string .= "`" . $fields . "` " . ($order ? "DESC" : "ASC") . ", "; + $order_string .= self::quoteIdentifier($fields) . " " . ($order ? "DESC" : "ASC") . ", "; } else { - $order_string .= "`" . $order . "`, "; + $order_string .= self::quoteIdentifier($order) . ", "; } } $order_string = substr($order_string, 0, -2); @@ -588,7 +668,7 @@ class DBA */ public static function toArray($stmt, $do_close = true) { - return self::$database->toArray($stmt, $do_close); + return DI::dba()->toArray($stmt, $do_close); } /** @@ -598,7 +678,7 @@ class DBA */ public static function errorNo() { - return self::$database->errorNo(); + return DI::dba()->errorNo(); } /** @@ -608,7 +688,7 @@ class DBA */ public static function errorMessage() { - return self::$database->errorMessage(); + return DI::dba()->errorMessage(); } /** @@ -619,7 +699,7 @@ class DBA */ public static function close($stmt) { - return self::$database->close($stmt); + return DI::dba()->close($stmt); } /** @@ -632,7 +712,7 @@ class DBA */ public static function processlist() { - return self::$database->processlist(); + return DI::dba()->processlist(); } /** @@ -644,7 +724,7 @@ class DBA */ public static function isResult($array) { - return self::$database->isResult($array); + return DI::dba()->isResult($array); } /** @@ -656,6 +736,6 @@ class DBA */ public static function escapeArray(&$arr, $add_quotation = false) { - return self::$database->escapeArray($arr, $add_quotation); + DI::dba()->escapeArray($arr, $add_quotation); } }