X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDatabase.php;h=036e6ec2cbf6d8ad52d2cce7a4d058f42af60684;hb=29ee2473d7adbd40e3b7612b2a5c7ef3cf337047;hp=80fd02dc0d5f230c26cd91057c5ae5c85fe685f2;hpb=ee8689cc899beecaf0943ac175550a7fb49cf199;p=friendica.git diff --git a/src/Database/Database.php b/src/Database/Database.php index 80fd02dc0d..036e6ec2cb 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -1,6 +1,6 @@ configCache = $configCache; - $this->profiler = $profiler; - $this->logger = $logger; + $this->configCache = $configCache; + $this->profiler = $profiler; + $this->dbaDefinition = $dbaDefinition; + $this->viewDefinition = $viewDefinition; - $this->readServerVariables($server); - $this->connect(); - - if ($this->isConnected()) { - // Loads DB_UPDATE_VERSION constant - DBStructure::definition($configCache->get('system', 'basepath'), false); - } - } + // Temporary NullLogger until we can fetch the logger class from the config + $this->logger = new NullLogger(); - private function readServerVariables(array $server) - { - // Use environment variables for mysql if they are set beforehand - if (!empty($server['MYSQL_HOST']) - && (!empty($server['MYSQL_USERNAME']) || !empty($server['MYSQL_USER'])) - && $server['MYSQL_PASSWORD'] !== false - && !empty($server['MYSQL_DATABASE'])) - { - $db_host = $server['MYSQL_HOST']; - if (!empty($server['MYSQL_PORT'])) { - $db_host .= ':' . $server['MYSQL_PORT']; - } - $this->configCache->set('database', 'hostname', $db_host); - unset($db_host); - if (!empty($server['MYSQL_USERNAME'])) { - $this->configCache->set('database', 'username', $server['MYSQL_USERNAME']); - } else { - $this->configCache->set('database', 'username', $server['MYSQL_USER']); - } - $this->configCache->set('database', 'password', (string) $server['MYSQL_PASSWORD']); - $this->configCache->set('database', 'database', $server['MYSQL_DATABASE']); - } + $this->connect(); } - public function connect() + /** + * Tries to connect to database + * + * @return bool Success + */ + public function connect(): bool { if (!is_null($this->connection) && $this->connected()) { return $this->connected; @@ -118,42 +110,52 @@ class Database $this->connected = false; $port = 0; - $serveraddr = trim($this->configCache->get('database', 'hostname')); + $serveraddr = trim($this->configCache->get('database', 'hostname') ?? ''); $serverdata = explode(':', $serveraddr); - $server = $serverdata[0]; + $host = trim($serverdata[0]); if (count($serverdata) > 1) { $port = trim($serverdata[1]); } - $server = trim($server); - $user = trim($this->configCache->get('database', 'username')); - $pass = trim($this->configCache->get('database', 'password')); - $db = trim($this->configCache->get('database', 'database')); - $charset = trim($this->configCache->get('database', 'charset')); - if (!(strlen($server) && strlen($user))) { + if (trim($this->configCache->get('database', 'port') ?? 0)) { + $port = trim($this->configCache->get('database', 'port') ?? 0); + } + + $user = trim($this->configCache->get('database', 'username')); + $pass = trim($this->configCache->get('database', 'password')); + $database = trim($this->configCache->get('database', 'database')); + $charset = trim($this->configCache->get('database', 'charset')); + $socket = trim($this->configCache->get('database', 'socket')); + + if (!$host && !$socket || !$user) { return false; } $persistent = (bool)$this->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())) { - $this->driver = 'pdo'; - $connect = "mysql:host=" . $server . ";dbname=" . $db; - - if ($port > 0) { - $connect .= ";port=" . $port; + $this->driver = self::PDO; + if ($socket) { + $connect = 'mysql:unix_socket=' . $socket; + } else { + $connect = 'mysql:host=' . $host; + if ($port > 0) { + $connect .= ';port=' . $port; + } } if ($charset) { - $connect .= ";charset=" . $charset; + $connect .= ';charset=' . $charset; } + $connect .= ';dbname=' . $database; + 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; @@ -161,12 +163,14 @@ class Database } if (!$this->connected && class_exists('\mysqli')) { - $this->driver = 'mysqli'; + $this->driver = self::MYSQLI; - if ($port > 0) { - $this->connection = @new mysqli($server, $user, $pass, $db, $port); + if ($socket) { + $this->connection = @new mysqli(null, $user, $pass, $database, null, $socket); + } elseif ($port > 0) { + $this->connection = @new mysqli($host, $user, $pass, $database, $port); } else { - $this->connection = @new mysqli($server, $user, $pass, $db); + $this->connection = @new mysqli($host, $user, $pass, $database); } if (!mysqli_connect_errno()) { @@ -180,7 +184,7 @@ class Database // No suitable SQL driver was found. if (!$this->connected) { - $this->driver = null; + $this->driver = ''; $this->connection = null; } @@ -191,6 +195,7 @@ class Database { $this->testmode = $test; } + /** * Sets the logger for DBA * @@ -215,6 +220,7 @@ class Database { $this->profiler = $profiler; } + /** * Disconnects the current database connection */ @@ -222,17 +228,17 @@ class Database { if (!is_null($this->connection)) { switch ($this->driver) { - case 'pdo': + case self::PDO: $this->connection = null; break; - case 'mysqli': + case self::MYSQLI: $this->connection->close(); $this->connection = null; break; } } - $this->driver = null; + $this->driver = ''; $this->connected = false; } @@ -255,22 +261,32 @@ class Database return $this->connection; } + /** + * Return the database driver string + * + * @return string with either "pdo" or "mysqli" + */ + public function getDriver(): string + { + return $this->driver; + } + /** * 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 * - * @return string + * @return string Database server information */ - public function serverInfo() + public function serverInfo(): string { if ($this->server_info == '') { switch ($this->driver) { - case 'pdo': + case self::PDO: $this->server_info = $this->connection->getAttribute(PDO::ATTR_SERVER_VERSION); break; - case 'mysqli': + case self::MYSQLI: $this->server_info = $this->connection->server_info; break; } @@ -281,10 +297,10 @@ class Database /** * Returns the selected database name * - * @return string + * @return string Database name * @throws \Exception */ - public function databaseName() + public function databaseName(): string { $ret = $this->p("SELECT DATABASE() AS `db`"); $data = $this->toArray($ret); @@ -295,10 +311,10 @@ class Database * Analyze a database query and log this if some conditions are met. * * @param string $query The database query that will be analyzed - * + * @return void * @throws \Exception */ - private function logIndex($query) + private function logIndex(string $query) { if (!$this->configCache->get('system', 'db_log_index')) { @@ -321,12 +337,12 @@ class Database } $watchlist = explode(',', $this->configCache->get('system', 'db_log_index_watch')); - $denylist = explode(',', $this->configCache->get('system', 'db_log_index_denylist')); + $denylist = explode(',', $this->configCache->get('system', 'db_log_index_denylist')); while ($row = $this->fetch($r)) { if ((intval($this->configCache->get('system', 'db_loglimit_index')) > 0)) { $log = (in_array($row['key'], $watchlist) && - ($row['rows'] >= intval($this->configCache->get('system', 'db_loglimit_index')))); + ($row['rows'] >= intval($this->configCache->get('system', 'db_loglimit_index')))); } else { $log = false; } @@ -341,11 +357,15 @@ class Database if ($log) { $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($this->configCache->get('system', 'db_log_index'), DateTimeFormat::utcNow() . "\t" . - $row['key'] . "\t" . $row['rows'] . "\t" . $row['Extra'] . "\t" . - basename($backtrace[1]["file"]) . "\t" . - $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" . - substr($query, 0, 4000) . "\n", FILE_APPEND); + @file_put_contents( + $this->configCache->get('system', 'db_log_index'), + DateTimeFormat::utcNow() . "\t" . + $row['key'] . "\t" . $row['rows'] . "\t" . $row['Extra'] . "\t" . + basename($backtrace[1]["file"]) . "\t" . + $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" . + substr($query, 0, 4000) . "\n", + FILE_APPEND + ); } } } @@ -354,11 +374,10 @@ class Database * Removes every not allowlisted character from the identifier string * * @param string $identifier - * * @return string sanitized identifier * @throws \Exception */ - private function sanitizeIdentifier($identifier) + private function sanitizeIdentifier(string $identifier): string { return preg_replace('/[^A-Za-z0-9_\-]+/', '', $identifier); } @@ -367,10 +386,10 @@ class Database { if ($this->connected) { switch ($this->driver) { - case 'pdo': + case self::PDO: return substr(@$this->connection->quote($str, PDO::PARAM_STR), 1, -1); - case 'mysqli': + case self::MYSQLI: return @$this->connection->real_escape_string($str); } } else { @@ -378,11 +397,21 @@ class Database } } - public function isConnected() + /** + * Returns connected flag + * + * @return bool Whether connection to database was success + */ + public function isConnected(): bool { return $this->connected; } + /** + * Checks connection status + * + * @return bool Whether connection to database was success + */ public function connected() { $connected = false; @@ -392,14 +421,14 @@ class Database } switch ($this->driver) { - case 'pdo': + case self::PDO: $r = $this->p("SELECT 1"); if ($this->isResult($r)) { $row = $this->toArray($r); $connected = ($row[0]['1'] == '1'); } break; - case 'mysqli': + case self::MYSQLI: $connected = $this->connection->ping(); break; } @@ -419,11 +448,11 @@ class Database * * @return string The input SQL string modified if necessary. */ - public function anyValueFallback($sql) + public function anyValueFallback(string $sql): string { $server_info = $this->serverInfo(); if (version_compare($server_info, '5.7.5', '<') || - (stripos($server_info, 'MariaDB') !== false)) { + (stripos($server_info, 'MariaDB') !== false)) { $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql); } return $sql; @@ -437,10 +466,10 @@ class Database * * @return string The replaced SQL query */ - private function replaceParameters($sql, $args) + private function replaceParameters(string $sql, array $args): string { $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])) { @@ -461,7 +490,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 @@ -471,9 +500,10 @@ class Database * @return bool|object statement object or result object * @throws \Exception */ - public function p($sql) + public function p(string $sql) { + $this->profiler->startRecording('database'); $stamp1 = microtime(true); $params = DBA::getParam(func_get_args()); @@ -481,7 +511,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; @@ -525,17 +555,17 @@ 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) { - case 'pdo': + 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]; - $this->errorno = $errorInfo[1]; + $this->error = (string)$errorInfo[2]; + $this->errorno = (int)$errorInfo[1]; $retval = false; $is_error = true; break; @@ -547,26 +577,28 @@ class Database /** @var $stmt mysqli_stmt|PDOStatement */ if (!$stmt = $this->connection->prepare($sql)) { $errorInfo = $this->connection->errorInfo(); - $this->error = $errorInfo[2]; - $this->errorno = $errorInfo[1]; + $this->error = (string)$errorInfo[2]; + $this->errorno = (int)$errorInfo[1]; $retval = false; $is_error = true; 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); } if (!$stmt->execute()) { $errorInfo = $stmt->errorInfo(); - $this->error = $errorInfo[2]; - $this->errorno = $errorInfo[1]; + $this->error = (string)$errorInfo[2]; + $this->errorno = (int)$errorInfo[1]; $retval = false; $is_error = true; } else { @@ -574,18 +606,18 @@ class Database $this->affected_rows = $retval->rowCount(); } break; - case 'mysqli': + case self::MYSQLI: // There are SQL statements that cannot be executed with a prepared statement $parts = explode(' ', $orig_sql); $command = strtolower($parts[0]); $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; - $this->errorno = $this->connection->errno; + $this->error = (string)$this->connection->error; + $this->errorno = (int)$this->connection->errno; $retval = false; $is_error = true; } else { @@ -601,8 +633,8 @@ class Database $stmt = $this->connection->stmt_init(); if (!$stmt->prepare($sql)) { - $this->error = $stmt->error; - $this->errorno = $stmt->errno; + $this->error = (string)$stmt->error; + $this->errorno = (int)$stmt->errno; $retval = false; $is_error = true; break; @@ -610,13 +642,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'; } @@ -629,8 +664,8 @@ class Database } if (!$stmt->execute()) { - $this->error = $this->connection->error; - $this->errorno = $this->connection->errno; + $this->error = (string)$this->connection->error; + $this->errorno = (int)$this->connection->errno; $retval = false; $is_error = true; } else { @@ -658,7 +693,7 @@ class Database $errorno = $this->errorno; if ($this->testmode) { - throw new Exception(DI::l10n()->t('Database error %d "%s" at "%s"', $errorno, $error, $this->replaceParameters($sql, $args))); + throw new DatabaseException($error, $errorno, $this->replaceParameters($sql, $args)); } $this->logger->error('DB Error', [ @@ -697,11 +732,11 @@ class Database } } - $this->error = $error; - $this->errorno = $errorno; + $this->error = (string)$error; + $this->errorno = (int)$errorno; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); if ($this->configCache->get('system', 'db_log')) { $stamp2 = microtime(true); @@ -711,10 +746,14 @@ class Database $duration = round($duration, 3); $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($this->configCache->get('system', 'db_log'), DateTimeFormat::utcNow() . "\t" . $duration . "\t" . - basename($backtrace[1]["file"]) . "\t" . - $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" . - substr($this->replaceParameters($sql, $args), 0, 4000) . "\n", FILE_APPEND); + @file_put_contents( + $this->configCache->get('system', 'db_log'), + DateTimeFormat::utcNow() . "\t" . $duration . "\t" . + basename($backtrace[1]["file"]) . "\t" . + $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" . + substr($this->replaceParameters($sql, $args), 0, 4000) . "\n", + FILE_APPEND + ); } } return $retval; @@ -730,10 +769,11 @@ class Database * @return boolean Was the query successfull? False is returned only if an error occurred * @throws \Exception */ - public function e($sql) + public function e(string $sql): bool { + $retval = false; - $stamp = microtime(true); + $this->profiler->startRecording('database_write'); $params = DBA::getParam(func_get_args()); @@ -761,7 +801,7 @@ class Database $errorno = $this->errorno; if ($this->testmode) { - throw new Exception(DI::l10n()->t('Database error %d "%s" at "%s"', $errorno, $error, $this->replaceParameters($sql, $params))); + throw new DatabaseException($error, $errorno, $this->replaceParameters($sql, $params)); } $this->logger->error('DB Error', [ @@ -785,7 +825,7 @@ class Database $this->errorno = $errorno; } - $this->profiler->saveTimestamp($stamp, "database_write"); + $this->profiler->stopRecording(); return $retval; } @@ -793,13 +833,14 @@ class Database /** * Check if data exists * - * @param string|array $table Table name or array [schema => table] - * @param array $condition array of fields for condition + * @param string $table Table name in format schema.table (while scheme is optiona) + * @param array $condition Array of fields for condition * * @return boolean Are there rows for that condition? * @throws \Exception + * @todo Please unwrap the DBStructure::existsTable() call so this method has one behavior only: checking existence on records */ - public function exists($table, $condition) + public function exists(string $table, array $condition): bool { if (empty($table)) { return false; @@ -839,10 +880,10 @@ class Database * * @param string $sql SQL statement * - * @return array first row of query + * @return array|bool first row of query or false on failure * @throws \Exception */ - public function fetchFirst($sql) + public function fetchFirst(string $sql) { $params = DBA::getParam(func_get_args()); @@ -864,7 +905,7 @@ class Database * * @return int Number of rows */ - public function affectedRows() + public function affectedRows(): int { return $this->affected_rows; } @@ -876,15 +917,15 @@ class Database * * @return int Number of columns */ - public function columnCount($stmt) + public function columnCount($stmt): int { if (!is_object($stmt)) { return 0; } switch ($this->driver) { - case 'pdo': + case self::PDO: return $stmt->columnCount(); - case 'mysqli': + case self::MYSQLI: return $stmt->field_count; } return 0; @@ -897,15 +938,15 @@ class Database * * @return int Number of rows */ - public function numRows($stmt) + public function numRows($stmt): int { if (!is_object($stmt)) { return 0; } switch ($this->driver) { - case 'pdo': + case self::PDO: return $stmt->rowCount(); - case 'mysqli': + case self::MYSQLI: return $stmt->num_rows; } return 0; @@ -914,14 +955,13 @@ class Database /** * Fetch a single row * - * @param mixed $stmt statement object + * @param bool|PDOStatement|mysqli_stmt $stmt statement object * - * @return array current row + * @return array|bool Current row or false on failure */ public function fetch($stmt) { - - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); $columns = []; @@ -930,12 +970,15 @@ class Database } switch ($this->driver) { - case 'pdo': + case self::PDO: $columns = $stmt->fetch(PDO::FETCH_ASSOC); + if (!empty($stmt->table) && is_array($columns)) { + $columns = $this->castFields($stmt->table, $columns); + } break; - case 'mysqli': + case self::MYSQLI: if (get_class($stmt) == 'mysqli_result') { - $columns = $stmt->fetch_assoc(); + $columns = $stmt->fetch_assoc() ?? false; break; } @@ -961,42 +1004,50 @@ 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 - * @param bool $on_duplicate_update Do an update on a duplicate entry + * @param string $table Table name in format schema.table (while scheme is optiona) + * @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(string $table, array $param, int $duplicate_mode = self::INSERT_DEFAULT): bool { if (empty($table) || empty($param)) { $this->logger->info('Table and fields have to be set'); return false; } - $table_string = DBA::buildTableString($table); + $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 . " = ?"; @@ -1005,27 +1056,33 @@ 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; } /** * 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 - * + * @param string $table Table name in format schema.table (while scheme is optiona) + * @param array $param parameter array * @return boolean was the insert successful? * @throws \Exception */ - public function replace($table, array $param) + public function replace(string $table, array $param): bool { if (empty($table) || empty($param)) { $this->logger->info('Table and fields have to be set'); return false; } - $table_string = DBA::buildTableString($table); + $param = $this->castFields($table, $param); + + $table_string = DBA::buildTableString([$table]); $fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param))); @@ -1041,17 +1098,17 @@ class Database * * @return integer Last inserted id */ - public function lastInsertId() + public function lastInsertId(): int { switch ($this->driver) { - case 'pdo': + case self::PDO: $id = $this->connection->lastInsertId(); break; - case 'mysqli': + case self::MYSQLI: $id = $this->connection->insert_id; break; } - return $id; + return (int)$id; } /** @@ -1059,29 +1116,28 @@ class Database * * This function can be extended in the future to accept a table array as well. * - * @param string|array $table Table name or array [schema => table] - * + * @param string $table Table name in format schema.table (while scheme is optiona) * @return boolean was the lock successful? * @throws \Exception */ - public function lock($table) + public function lock(string $table): bool { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html - if ($this->driver == 'pdo') { + if ($this->driver == self::PDO) { $this->e("SET autocommit=0"); $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); } else { $this->connection->autocommit(false); } - $success = $this->e("LOCK TABLES " . DBA::buildTableString($table) . " WRITE"); + $success = $this->e("LOCK TABLES " . DBA::buildTableString([$table]) . " WRITE"); - if ($this->driver == 'pdo') { + if ($this->driver == self::PDO) { $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares); } if (!$success) { - if ($this->driver == 'pdo') { + if ($this->driver == self::PDO) { $this->e("SET autocommit=1"); } else { $this->connection->autocommit(true); @@ -1098,18 +1154,18 @@ class Database * @return boolean was the unlock successful? * @throws \Exception */ - public function unlock() + public function unlock(): bool { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html $this->performCommit(); - if ($this->driver == 'pdo') { + if ($this->driver == self::PDO) { $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); } $success = $this->e("UNLOCK TABLES"); - if ($this->driver == 'pdo') { + if ($this->driver == self::PDO) { $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares); $this->e("SET autocommit=1"); } else { @@ -1125,20 +1181,20 @@ class Database * * @return boolean Was the command executed successfully? */ - public function transaction() + public function transaction(): bool { if (!$this->performCommit()) { return false; } switch ($this->driver) { - case 'pdo': + case self::PDO: if (!$this->connection->inTransaction() && !$this->connection->beginTransaction()) { return false; } break; - case 'mysqli': + case self::MYSQLI: if (!$this->connection->begin_transaction()) { return false; } @@ -1149,17 +1205,22 @@ class Database return true; } - protected function performCommit() + /** + * Performs the commit + * + * @return boolean Was the command executed successfully? + */ + protected function performCommit(): bool { switch ($this->driver) { - case 'pdo': + case self::PDO: if (!$this->connection->inTransaction()) { return true; } return $this->connection->commit(); - case 'mysqli': + case self::MYSQLI: return $this->connection->commit(); } @@ -1171,7 +1232,7 @@ class Database * * @return boolean Was the command executed successfully? */ - public function commit() + public function commit(): bool { if (!$this->performCommit()) { return false; @@ -1185,12 +1246,12 @@ class Database * * @return boolean Was the command executed successfully? */ - public function rollback() + public function rollback(): bool { $ret = false; switch ($this->driver) { - case 'pdo': + case self::PDO: if (!$this->connection->inTransaction()) { $ret = true; break; @@ -1198,189 +1259,44 @@ class Database $ret = $this->connection->rollBack(); break; - case 'mysqli': + case self::MYSQLI: $ret = $this->connection->rollback(); break; } + $this->in_transaction = false; 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(string $table, array $conditions): bool { 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]; + $table_string = DBA::buildTableString([$table]); - // Create a key for preventing double queries - $qkey = $field . '-' . $table . ':' . json_encode($conditions); + $condition_string = DBA::buildCondition($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) . ");"; - - $this->logger->info($this->replaceParameters($sql, $field_values), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]); - - 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: @@ -1398,15 +1314,17 @@ class Database * 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|array $table Table name or array [schema => table] + * @param string $table Table name in format schema.table (while scheme is optiona) * @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) + * @param array $params Parameters: "ignore" If set to "true" then the update is done with the ignore parameter * * @return boolean was the update successfull? * @throws \Exception + * @todo Implement "bool $update_on_duplicate" to avoid mixed type for $old_fields */ - public function update($table, $fields, $condition, $old_fields = []) + public function update(string $table, array $fields, array $condition, $old_fields = [], array $params = []) { if (empty($table) || empty($fields) || empty($condition)) { $this->logger->info('Table, fields and condition have to be set'); @@ -1427,7 +1345,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]); } @@ -1437,16 +1355,24 @@ class Database return true; } - $table_string = DBA::buildTableString($table); + $fields = $this->castFields($table, $fields); + + $table_string = DBA::buildTableString([$table]); $condition_string = DBA::buildCondition($condition); - $sql = "UPDATE " . $table_string . " SET " + if (!empty($params['ignore'])) { + $ignore = 'IGNORE '; + } else { + $ignore = ''; + } + + $sql = "UPDATE " . $ignore . $table_string . " SET " . implode(" = ?, ", array_map([DBA::class, 'quoteIdentifier'], array_keys($fields))) . " = ?" . $condition_string; // Combines the updated fields parameter values with the condition parameter values - $params = array_merge(array_values($fields), $condition); + $params = array_merge(array_values($fields), $condition); return $this->e($sql, $params); } @@ -1454,16 +1380,16 @@ class Database /** * Retrieve a single record from a table and returns it in an associative array * - * @param string|array $table - * @param array $fields - * @param array $condition - * @param array $params + * @param string $table Table name in format schema.table (while scheme is optiona) + * @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 bool|array * @throws \Exception * @see $this->select */ - public function selectFirst($table, array $fields = [], array $condition = [], $params = []) + public function selectFirst(string $table, array $fields = [], array $condition = [], array $params = []) { $params['limit'] = 1; $result = $this->select($table, $fields, $condition, $params); @@ -1480,26 +1406,63 @@ class Database /** * 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 - * + * @param string $table Table name in format schema.table (while scheme is optiona) + * @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 function selectToArray($table, array $fields = [], array $condition = [], array $params = []) + public function selectToArray(string $table, array $fields = [], array $condition = [], array $params = []) { 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 Escaped fields + */ + private function escapeFields(array $fields, array $options): array + { + // 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 = DBA::quoteIdentifier($field); + + 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() @@ -1516,26 +1479,27 @@ class Database * * $data = DBA::select($table, $fields, $condition, $params); * - * @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 + * @param string $table Table name in format schema.table (while scheme is optiona) + * @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 * @throws \Exception */ - public function select($table, array $fields = [], array $condition = [], array $params = []) + public function select(string $table, array $fields = [], array $condition = [], array $params = []) { if (empty($table)) { return false; } 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 = '*'; } - $table_string = DBA::buildTableString($table); + $table_string = DBA::buildTableString([$table]); $condition_string = DBA::buildCondition($condition); @@ -1545,20 +1509,24 @@ class Database $result = $this->p($sql, $condition); + if (($this->driver == self::PDO) && !empty($result) && is_string($table)) { + $result->table = $table; + } + return $result; } /** * 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 - * @param array $params Array of several parameters + * @param string $table Table name in format schema.table (while scheme is optiona) + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * - * @return int + * @return int Count of rows * * Example: - * $table = "item"; + * $table = "post"; * * $condition = ["uid" => 1, "network" => 'dspr']; * or: @@ -1567,13 +1535,13 @@ class Database * $count = DBA::count($table, $condition); * @throws \Exception */ - public function count($table, array $condition = [], array $params = []) + public function count(string $table, array $condition = [], array $params = []): int { if (empty($table)) { - return false; + throw new InvalidArgumentException('Parameter "table" cannot be empty.'); } - $table_string = DBA::buildTableString($table); + $table_string = DBA::buildTableString([$table]); $condition_string = DBA::buildCondition($condition); @@ -1589,18 +1557,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, bool $do_close = true, int $count = 0): array { if (is_bool($stmt)) { return []; @@ -1609,6 +1583,9 @@ class Database $data = []; while ($row = $this->fetch($stmt)) { $data[] = $row; + if (($count != 0) && (count($data) == $count)) { + return $data; + } } if ($do_close) { @@ -1618,12 +1595,78 @@ 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): array + { + // 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 = $this->dbaDefinition->getAll(); + if (empty($tables[$table])) { + // When a matching table wasn't found we check if it is a view + $views = $this->viewDefinition->getAll(); + 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 * * @return string Error number (0 if no error) */ - public function errorNo() + public function errorNo(): int { return $this->errorno; } @@ -1633,7 +1676,7 @@ class Database * * @return string Error message ('' if no error) */ - public function errorMessage() + public function errorMessage(): string { return $this->error; } @@ -1645,20 +1688,20 @@ class Database * * @return boolean was the close successful? */ - public function close($stmt) + public function close($stmt): bool { - $stamp1 = microtime(true); + $this->profiler->startRecording('database'); if (!is_object($stmt)) { return false; } switch ($this->driver) { - case 'pdo': + case self::PDO: $ret = $stmt->closeCursor(); break; - case 'mysqli': + case self::MYSQLI: // MySQLi offers both a mysqli_stmt and a mysqli_result class. // We should be careful not to assume the object type of $stmt // because DBA::p() has been able to return both types. @@ -1674,7 +1717,7 @@ class Database break; } - $this->profiler->saveTimestamp($stamp1, 'database'); + $this->profiler->stopRecording(); return $ret; } @@ -1687,38 +1730,39 @@ class Database * 'amount' => Number of concurrent database processes * @throws \Exception */ - public function processlist() + public function processlist(): array { - $ret = $this->p("SHOW PROCESSLIST"); + $ret = $this->p('SHOW PROCESSLIST'); $data = $this->toArray($ret); $processes = 0; $states = []; foreach ($data as $process) { - $state = trim($process["State"]); + $state = trim($process['State']); // Filter out all non blocking processes - if (!in_array($state, ["", "init", "statistics", "updating"])) { + if (!in_array($state, ['', 'init', 'statistics', 'updating'])) { ++$states[$state]; ++$processes; } } - $statelist = ""; + $statelist = ''; foreach ($states as $state => $usage) { - if ($statelist != "") { - $statelist .= ", "; + if ($statelist != '') { + $statelist .= ', '; } - $statelist .= $state . ": " . $usage; + $statelist .= $state . ': ' . $usage; } - return (["list" => $statelist, "amount" => $processes]); + return (['list' => $statelist, 'amount' => $processes]); } /** * Fetch a database variable * * @param string $name - * @return string content + * @return string|null content or null if inexistent + * @throws \Exception */ public function getVariable(string $name) { @@ -1730,10 +1774,9 @@ class Database * Checks if $array is a filled array with at least one entry. * * @param mixed $array A filled array with at least one entry - * * @return boolean Whether $array is a filled array or an object with rows */ - public function isResult($array) + public function isResult($array): bool { // It could be a return value from an update statement if (is_bool($array)) { @@ -1753,10 +1796,9 @@ class Database * @param mixed $value Array value * @param string $key Array key * @param boolean $add_quotation add quotation marks for string values - * * @return void */ - private function escapeArrayCallback(&$value, $key, $add_quotation) + private function escapeArrayCallback(&$value, string $key, bool $add_quotation) { if (!$add_quotation) { if (is_bool($value)) { @@ -1781,11 +1823,40 @@ class Database * * @param mixed $arr Array with values to be escaped * @param boolean $add_quotation add quotation marks for string values - * * @return void */ - public function escapeArray(&$arr, $add_quotation = false) + public function escapeArray(&$arr, bool $add_quotation = false) { array_walk($arr, [$this, 'escapeArrayCallback'], $add_quotation); } + + /** + * Replaces a string in the provided fields of the provided table + * + * @param string $table Table name + * @param array $fields List of field names in the provided table + * @param string $search String to search for + * @param string $replace String to replace with + * @return void + * @throws \Exception + */ + public function replaceInTableFields(string $table, array $fields, string $search, string $replace) + { + $search = $this->escape($search); + $replace = $this->escape($replace); + + $upd = []; + foreach ($fields as $field) { + $field = DBA::quoteIdentifier($field); + $upd[] = "$field = REPLACE($field, '$search', '$replace')"; + } + + $upds = implode(', ', $upd); + + $r = $this->e(sprintf("UPDATE %s SET %s;", DBA::quoteIdentifier($table), $upds)); + + if (!$this->isResult($r)) { + throw new \RuntimeException("Failed updating `$table`: " . $this->errorMessage()); + } + } }