X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDatabase.php;h=88d8d7d0f6ef87baa78cbc7f63dcd2cceeb65c15;hb=612e91b603a5a5817839d04d120e1cf672b8a80e;hp=ae06f00e26cd5562e425008b773a833eb777855e;hpb=8a3995a740a109b2bcac9b88fee6ecc55dd97c85;p=friendica.git diff --git a/src/Database/Database.php b/src/Database/Database.php index ae06f00e26..88d8d7d0f6 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -1,6 +1,6 @@ configCache->get('database', 'password')); $db = trim($this->configCache->get('database', 'database')); $charset = trim($this->configCache->get('database', 'charset')); + $socket = trim($this->configCache->get('database', 'socket')); if (!(strlen($server) && strlen($user))) { return false; @@ -135,9 +136,14 @@ class Database $connect .= ";charset=" . $charset; } + if ($socket) { + $connect .= ";$unix_socket=" . $socket; + } + try { $this->connection = @new PDO($connect, $user, $pass, [PDO::ATTR_PERSISTENT => $persistent]); $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares); + $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $this->connected = true; } catch (PDOException $e) { $this->connected = false; @@ -159,6 +165,11 @@ class Database if ($charset) { $this->connection->set_charset($charset); } + + if ($socket) { + $this->connection->set_socket($socket); + } + } } @@ -434,7 +445,7 @@ class Database private function replaceParameters($sql, $args) { $offset = 0; - foreach ($args AS $param => $value) { + foreach ($args as $param => $value) { if (is_int($args[$param]) || is_float($args[$param]) || is_bool($args[$param])) { $replace = intval($args[$param]); } elseif (is_null($args[$param])) { @@ -468,6 +479,7 @@ class Database public function p($sql) { + $this->profiler->startRecording('database'); $stamp1 = microtime(true); $params = DBA::getParam(func_get_args()); @@ -475,7 +487,7 @@ class Database // Renumber the array keys to be sure that they fit $i = 0; $args = []; - foreach ($params AS $param) { + foreach ($params as $param) { // Avoid problems with some MySQL servers and boolean values. See issue #3645 if (is_bool($param)) { $param = (int)$param; @@ -519,7 +531,7 @@ class Database $called_from_e = ($called_from['function'] == 'e'); if (!isset($this->connection)) { - throw new InternalServerErrorException('The Connection is empty, although connected is set true.'); + throw new ServiceUnavailableException('The Connection is empty, although connected is set true.'); } switch ($this->driver) { @@ -548,12 +560,14 @@ class Database break; } - foreach ($args AS $param => $value) { + foreach (array_keys($args) as $param) { + $data_type = PDO::PARAM_STR; if (is_int($args[$param])) { $data_type = PDO::PARAM_INT; - } else { - $data_type = PDO::PARAM_STR; + } elseif ($args[$param] !== null) { + $args[$param] = (string)$args[$param]; } + $stmt->bindParam($param, $args[$param], $data_type); } @@ -604,13 +618,16 @@ class Database $param_types = ''; $values = []; - foreach ($args AS $param => $value) { + foreach (array_keys($args) as $param) { if (is_int($args[$param])) { $param_types .= 'i'; } elseif (is_float($args[$param])) { $param_types .= 'd'; } elseif (is_string($args[$param])) { $param_types .= 's'; + } elseif (is_object($args[$param]) && method_exists($args[$param], '__toString')) { + $param_types .= 's'; + $args[$param] = (string)$args[$param]; } else { $param_types .= 'b'; } @@ -695,7 +712,7 @@ class Database $this->errorno = $errorno; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); if ($this->configCache->get('system', 'db_log')) { $stamp2 = microtime(true); @@ -727,7 +744,7 @@ class Database public function e($sql) { - $stamp = microtime(true); + $this->profiler->startRecording('database_write'); $params = DBA::getParam(func_get_args()); @@ -779,7 +796,7 @@ class Database $this->errorno = $errorno; } - $this->profiler->saveTimestamp($stamp, "database_write"); + $this->profiler->stopRecording(); return $retval; } @@ -908,13 +925,13 @@ class Database /** * Fetch a single row * - * @param PDOStatement|mysqli_stmt $stmt statement object + * @param bool|PDOStatement|mysqli_stmt $stmt statement object * * @return array|false current row */ public function fetch($stmt) { - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); $columns = []; @@ -957,18 +974,18 @@ class Database $result = $stmt->result_metadata(); $fields = $result->fetch_fields(); - foreach ($cols_num AS $param => $col) { + foreach ($cols_num as $param => $col) { $columns[$fields[$param]->name] = $col; } } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); return $columns; } /** - * Insert a row into a table + * Insert a row into a table. Field value objects will be cast as string. * * @param string|array $table Table name or array [schema => table] * @param array $param parameter array @@ -1217,181 +1234,35 @@ class Database return $ret; } - /** - * Build the array with the table relations - * - * The array is build from the database definitions in DBStructure.php - * - * This process must only be started once, since the value is cached. - */ - private function buildRelationData() - { - $definition = DBStructure::definition($this->configCache->get('system', 'basepath')); - - foreach ($definition AS $table => $structure) { - foreach ($structure['fields'] AS $field => $field_struct) { - if (isset($field_struct['relation'])) { - foreach ($field_struct['relation'] AS $rel_table => $rel_field) { - $this->relation[$rel_table][$rel_field][$table][] = $field; - } - } - } - } - } - /** * Delete a row from a table * - * Note: this methods does NOT accept schema => table arrays because of the complex relation stuff. - * * @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 array $callstack Internal use: prevent endless loops * * @return boolean was the delete successful? * @throws \Exception */ - public function delete($table, array $conditions, array $options = [], array &$callstack = []) + public function delete($table, array $conditions) { if (empty($table) || empty($conditions)) { $this->logger->info('Table and conditions have to be set'); return false; } - $commands = []; - - // Create a key for the loop prevention - $key = $table . ':' . json_encode($conditions); - - // We quit when this key already exists in the callstack. - if (isset($callstack[$key])) { - return true; - } - - $callstack[$key] = true; - - $commands[$key] = ['table' => $table, 'conditions' => $conditions]; - - // 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($this->relation) == 0) { - $this->buildRelationData(); - } - - // Is there a relation entry for the table? - if ($cascade && isset($this->relation[$table])) { - // We only allow a simple "one field" relation. - $field = array_keys($this->relation[$table])[0]; - $rel_def = array_values($this->relation[$table])[0]; - - // Create a key for preventing double queries - $qkey = $field . '-' . $table . ':' . json_encode($conditions); - - // When the search field is the relation field, we don't need to fetch the rows - // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend - if ((count($conditions) == 1) && ($field == array_keys($conditions)[0])) { - foreach ($rel_def AS $rel_table => $rel_fields) { - foreach ($rel_fields AS $rel_field) { - $this->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 = $this->select($table, [$field], $conditions); - - while ($row = $this->fetch($data)) { - $this->delete($table, [$field => $row[$field]], $options, $callstack); - } - - $this->close($data); - - // Since we had split the delete command we don't need the original command anymore - unset($commands[$key]); - } - } - - // Now we finalize the process - $do_transaction = !$this->in_transaction; - - if ($do_transaction) { - $this->transaction(); - } - - $compacted = []; - $counter = []; - - foreach ($commands AS $command) { - $conditions = $command['conditions']; - reset($conditions); - $first_key = key($conditions); - - $condition_string = DBA::buildCondition($conditions); - - if ((count($command['conditions']) > 1) || is_int($first_key)) { - $sql = "DELETE FROM " . DBA::quoteIdentifier($command['table']) . " " . $condition_string; - $this->logger->info($this->replaceParameters($sql, $conditions), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]); - - if (!$this->e($sql, $conditions)) { - if ($do_transaction) { - $this->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; - } - - $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 " . DBA::quoteIdentifier($table) . " WHERE " . DBA::quoteIdentifier($field) . " IN (" . - substr(str_repeat("?, ", count($field_values)), 0, -2) . ");"; + $table_string = DBA::buildTableString($table); - $this->logger->info($this->replaceParameters($sql, $field_values), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]); + $condition_string = DBA::buildCondition($conditions); - if (!$this->e($sql, $field_values)) { - if ($do_transaction) { - $this->rollback(); - } - return false; - } - } - } - } - if ($do_transaction) { - $this->commit(); - } - return true; + $sql = "DELETE FROM " . $table_string . " " . $condition_string; + $this->logger->debug($this->replaceParameters($sql, $conditions), ['callstack' => System::callstack(6)]); + return $this->e($sql, $conditions); } /** - * Updates rows + * Updates rows in the database. Field value objects will be cast as string. * - * 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: @@ -1412,7 +1283,7 @@ class Database * @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) + * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate, false = don't update identical fields) * * @return boolean was the update successfull? * @throws \Exception @@ -1438,7 +1309,7 @@ class Database } } - foreach ($old_fields AS $fieldname => $content) { + foreach ($old_fields as $fieldname => $content) { if (isset($fields[$fieldname]) && !is_null($content) && ($fields[$fieldname] == $content)) { unset($fields[$fieldname]); } @@ -1507,12 +1378,51 @@ class Database return $this->toArray($this->select($table, $fields, $condition, $params)); } + /** + * Escape fields, adding special treatment for "group by" handling + * + * @param array $fields + * @param array $options + * @return array + */ + private function escapeFields(array $fields, array $options) + { + // In the case of a "GROUP BY" we have to add all the ORDER fields to the fieldlist. + // This needs to done to apply the "ANY_VALUE(...)" treatment from below to them. + // Otherwise MySQL would report errors. + if (!empty($options['group_by']) && !empty($options['order'])) { + foreach ($options['order'] as $key => $field) { + if (!is_int($key)) { + if (!in_array($key, $fields)) { + $fields[] = $key; + } + } else { + if (!in_array($field, $fields)) { + $fields[] = $field; + } + } + } + } + + array_walk($fields, function(&$value, $key) use ($options) + { + $field = $value; + $value = '`' . str_replace('`', '``', $value) . '`'; + + if (!empty($options['group_by']) && !in_array($field, $options['group_by'])) { + $value = 'ANY_VALUE(' . $value . ') AS ' . $value; + } + }); + + return $fields; + } + /** * Select rows from a table * * * Example: - * $table = 'item'; + * $table = 'post'; * or: * $table = ['schema' => 'table']; * @see DBA::buildTableString() @@ -1543,7 +1453,8 @@ class Database } if (count($fields) > 0) { - $select_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], $fields)); + $fields = $this->escapeFields($fields, $params); + $select_string = implode(', ', $fields); } else { $select_string = '*'; } @@ -1575,7 +1486,7 @@ class Database * @return int * * Example: - * $table = "item"; + * $table = "post"; * * $condition = ["uid" => 1, "network" => 'dspr']; * or: @@ -1606,19 +1517,24 @@ class Database $row = $this->fetchFirst($sql, $condition); - // Ensure to always return either a "null" or a numeric value - return is_numeric($row['count']) ? (int)$row['count'] : $row['count']; + if (!isset($row['count'])) { + $this->logger->notice('Invalid count.', ['table' => $table, 'row' => $row, 'expression' => $expression, 'condition' => $condition_string, 'callstack' => System::callstack()]); + return 0; + } else { + return (int)$row['count']; + } } /** * Fills an array with data from a query * - * @param object $stmt statement object - * @param bool $do_close + * @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 * * @return array Data array */ - public function toArray($stmt, $do_close = true) + public function toArray($stmt, $do_close = true, int $count = 0) { if (is_bool($stmt)) { return []; @@ -1627,6 +1543,9 @@ class Database $data = []; while ($row = $this->fetch($stmt)) { $data[] = $row; + if (($count != 0) && (count($data) == $count)) { + return $data; + } } if ($do_close) { @@ -1698,9 +1617,9 @@ class Database } } - return $fields; + return $fields; } - + /** * Returns the error number of the last query * @@ -1731,7 +1650,7 @@ class Database public function close($stmt) { - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); if (!is_object($stmt)) { return false; @@ -1757,7 +1676,7 @@ class Database break; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); return $ret; }