X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDatabase.php;h=28283b1c8175d8273d980c0ffe17b021b8074cdc;hb=c00da77c677c0b5d3d9c3391365530e41a4dad7c;hp=f8ef1c9a1e06e044851f87a21b607893b287e726;hpb=d6a1a836f1ce0613b9d17f26a1f0fe778d50aa52;p=friendica.git diff --git a/src/Database/Database.php b/src/Database/Database.php index f8ef1c9a1e..28283b1c81 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -1,6 +1,6 @@ configCache->get('database', 'persistent'); - $this->emulate_prepares = (bool)$this->configCache->get('database', 'emulate_prepares'); $this->pdo_emulate_prepares = (bool)$this->configCache->get('database', 'pdo_emulate_prepares'); if (!$this->configCache->get('database', 'disable_pdo') && class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) { @@ -386,11 +388,10 @@ class Database switch ($this->driver) { case self::PDO: $r = $this->p("SELECT 1"); - $connected = $this->isResult($r); -// if ($this->isResult($r)) { -// $row = $this->toArray($r); -// $connected = ($row[0]['1'] == '1'); -// } + if ($this->isResult($r)) { + $row = $this->toArray($r); + $connected = ($row[0]['1'] == '1'); + } break; case self::MYSQLI: $connected = $this->connection->ping(); @@ -454,7 +455,7 @@ class Database /** * Executes a prepared statement that returns data * - * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid); + * @usage 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 @@ -467,6 +468,7 @@ class Database public function p($sql) { + $this->profiler->startRecording('database'); $stamp1 = microtime(true); $params = DBA::getParam(func_get_args()); @@ -524,7 +526,7 @@ class Database switch ($this->driver) { case self::PDO: // If there are no arguments we use "query" - if ($this->emulate_prepares || count($args) == 0) { + if (count($args) == 0) { if (!$retval = $this->connection->query($this->replaceParameters($sql, $args))) { $errorInfo = $this->connection->errorInfo(); $this->error = $errorInfo[2]; @@ -574,7 +576,7 @@ class Database $can_be_prepared = in_array($command, ['select', 'update', 'insert', 'delete']); // The fallback routine is called as well when there are no arguments - if ($this->emulate_prepares || !$can_be_prepared || (count($args) == 0)) { + if (!$can_be_prepared || (count($args) == 0)) { $retval = $this->connection->query($this->replaceParameters($sql, $args)); if ($this->connection->errno) { $this->error = $this->connection->error; @@ -694,7 +696,7 @@ class Database $this->errorno = $errorno; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); if ($this->configCache->get('system', 'db_log')) { $stamp2 = microtime(true); @@ -726,7 +728,7 @@ class Database public function e($sql) { - $stamp = microtime(true); + $this->profiler->startRecording('database_write'); $params = DBA::getParam(func_get_args()); @@ -778,7 +780,7 @@ class Database $this->errorno = $errorno; } - $this->profiler->saveTimestamp($stamp, "database_write"); + $this->profiler->stopRecording(); return $retval; } @@ -907,14 +909,13 @@ class Database /** * Fetch a single row * - * @param mixed $stmt statement object + * @param PDOStatement|mysqli_stmt $stmt statement object * - * @return array current row + * @return array|false current row */ public function fetch($stmt) { - - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); $columns = []; @@ -925,10 +926,13 @@ class Database switch ($this->driver) { case self::PDO: $columns = $stmt->fetch(PDO::FETCH_ASSOC); + if (!empty($stmt->table) && is_array($columns)) { + $columns = $this->castFields($stmt->table, $columns); + } break; case self::MYSQLI: if (get_class($stmt) == 'mysqli_result') { - $columns = $stmt->fetch_assoc(); + $columns = $stmt->fetch_assoc() ?? false; break; } @@ -959,7 +963,7 @@ class Database } } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); return $columns; } @@ -967,29 +971,37 @@ class Database /** * 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 function insert($table, array $param, bool $on_duplicate_update = false) + public function insert($table, array $param, int $duplicate_mode = self::INSERT_DEFAULT) { if (empty($table) || empty($param)) { $this->logger->info('Table and fields have to be set'); return false; } + $param = $this->castFields($table, $param); + $table_string = DBA::buildTableString($table); $fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param))); $values_string = substr(str_repeat("?, ", count($param)), 0, -2); - $sql = "INSERT INTO " . $table_string . " (" . $fields_string . ") VALUES (" . $values_string . ")"; + $sql = "INSERT "; + + if ($duplicate_mode == self::INSERT_IGNORE) { + $sql .= "IGNORE "; + } + + $sql .= "INTO " . $table_string . " (" . $fields_string . ") VALUES (" . $values_string . ")"; - if ($on_duplicate_update) { + if ($duplicate_mode == self::INSERT_UPDATE) { $fields_string = implode(' = ?, ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param))); $sql .= " ON DUPLICATE KEY UPDATE " . $fields_string . " = ?"; @@ -998,7 +1010,12 @@ class Database $param = array_merge_recursive($values, $values); } - return $this->e($sql, $param); + $result = $this->e($sql, $param); + if (!$result || ($duplicate_mode != self::INSERT_IGNORE)) { + return $result; + } + + return $this->affectedRows() != 0; } /** @@ -1018,6 +1035,8 @@ class Database return false; } + $param = $this->castFields($table, $param); + $table_string = DBA::buildTableString($table); $fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param))); @@ -1199,175 +1218,29 @@ 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); } /** @@ -1394,7 +1267,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 @@ -1430,6 +1303,8 @@ class Database return true; } + $fields = $this->castFields($table, $fields); + $table_string = DBA::buildTableString($table); $condition_string = DBA::buildCondition($condition); @@ -1492,7 +1367,7 @@ class Database * * * Example: - * $table = 'item'; + * $table = 'post'; * or: * $table = ['schema' => 'table']; * @see DBA::buildTableString() @@ -1538,6 +1413,10 @@ class Database $result = $this->p($sql, $condition); + if (($this->driver == self::PDO) && !empty($result) && is_string($table)) { + $result->table = $table; + } + return $result; } @@ -1551,7 +1430,7 @@ class Database * @return int * * Example: - * $table = "item"; + * $table = "post"; * * $condition = ["uid" => 1, "network" => 'dspr']; * or: @@ -1582,18 +1461,24 @@ class Database $row = $this->fetchFirst($sql, $condition); - return $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 []; @@ -1602,6 +1487,9 @@ class Database $data = []; while ($row = $this->fetch($stmt)) { $data[] = $row; + if (($count != 0) && (count($data) == $count)) { + return $data; + } } if ($do_close) { @@ -1611,6 +1499,71 @@ class Database return $data; } + /** + * Cast field types according to the table definition + * + * @param string $table + * @param array $fields + * @return array casted fields + */ + public function castFields(string $table, array $fields) { + // When there is no data, we don't need to do something + if (empty($fields)) { + return $fields; + } + + // We only need to cast fields with PDO + if ($this->driver != self::PDO) { + return $fields; + } + + // We only need to cast when emulating the prepares + if (!$this->connection->getAttribute(PDO::ATTR_EMULATE_PREPARES)) { + return $fields; + } + + $types = []; + + $tables = DBStructure::definition('', false); + if (empty($tables[$table])) { + // When a matching table wasn't found we check if it is a view + $views = View::definition('', false); + if (empty($views[$table])) { + return $fields; + } + + foreach(array_keys($fields) as $field) { + if (!empty($views[$table]['fields'][$field])) { + $viewdef = $views[$table]['fields'][$field]; + if (!empty($tables[$viewdef[0]]['fields'][$viewdef[1]]['type'])) { + $types[$field] = $tables[$viewdef[0]]['fields'][$viewdef[1]]['type']; + } + } + } + } else { + foreach ($tables[$table]['fields'] as $field => $definition) { + $types[$field] = $definition['type']; + } + } + + foreach ($fields as $field => $content) { + if (is_null($content) || empty($types[$field])) { + continue; + } + + if ((substr($types[$field], 0, 7) == 'tinyint') || (substr($types[$field], 0, 8) == 'smallint') || + (substr($types[$field], 0, 9) == 'mediumint') || (substr($types[$field], 0, 3) == 'int') || + (substr($types[$field], 0, 6) == 'bigint') || (substr($types[$field], 0, 7) == 'boolean')) { + $fields[$field] = (int)$content; + } + if ((substr($types[$field], 0, 5) == 'float') || (substr($types[$field], 0, 6) == 'double')) { + $fields[$field] = (float)$content; + } + } + + return $fields; + } + /** * Returns the error number of the last query * @@ -1641,7 +1594,7 @@ class Database public function close($stmt) { - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); if (!is_object($stmt)) { return false; @@ -1667,7 +1620,7 @@ class Database break; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); return $ret; }