namespace Friendica\Database;
-use Exception;
use Friendica\Core\Config\Cache;
use Friendica\Core\System;
-use Friendica\DI;
use Friendica\Network\HTTPException\InternalServerErrorException;
use Friendica\Util\DateTimeFormat;
use Friendica\Util\Profiler;
*/
class Database
{
+ const PDO = 'pdo';
+ const MYSQLI = 'mysqli';
+
+ const INSERT_DEFAULT = 0;
+ const INSERT_UPDATE = 1;
+ const INSERT_IGNORE = 2;
+
protected $connected = false;
/**
/** @var PDO|mysqli */
protected $connection;
protected $driver;
- private $emulate_prepares = false;
+ protected $emulate_prepares = false;
+ protected $pdo_emulate_prepares = false;
private $error = false;
private $errorno = 0;
private $affected_rows = 0;
protected $testmode = false;
private $relation = [];
- public function __construct(Cache $configCache, Profiler $profiler, LoggerInterface $logger, array $server = [])
+ public function __construct(Cache $configCache, Profiler $profiler, LoggerInterface $logger)
{
// We are storing these values for being able to perform a reconnect
$this->configCache = $configCache;
$this->profiler = $profiler;
$this->logger = $logger;
- $this->readServerVariables($server);
$this->connect();
if ($this->isConnected()) {
}
}
- 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']);
- }
- }
-
public function connect()
{
if (!is_null($this->connection) && $this->connected()) {
if (count($serverdata) > 1) {
$port = trim($serverdata[1]);
}
+
+ if (!empty(trim($this->configCache->get('database', 'port')))) {
+ $port = trim($this->configCache->get('database', 'port'));
+ }
+
$server = trim($server);
$user = trim($this->configCache->get('database', 'username'));
$pass = trim($this->configCache->get('database', 'password'));
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';
+ $this->driver = self::PDO;
$connect = "mysql:host=" . $server . ";dbname=" . $db;
if ($port > 0) {
}
try {
- $this->connection = @new PDO($connect, $user, $pass);
+ $this->connection = @new PDO($connect, $user, $pass, [PDO::ATTR_PERSISTENT => $persistent]);
$this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
$this->connected = true;
} catch (PDOException $e) {
}
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 (!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;
return $this->connection;
}
+ /**
+ * Return the database driver string
+ *
+ * @return string with either "pdo" or "mysqli"
+ */
+ public function getDriver()
+ {
+ return $this->driver;
+ }
+
/**
* Returns the MySQL server version 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;
}
$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, 2000) . "\n", FILE_APPEND);
+ substr($query, 0, 4000) . "\n", FILE_APPEND);
}
}
}
{
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 {
}
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;
}
}
switch ($this->driver) {
- case 'pdo':
+ case self::PDO:
// If there are no arguments we use "query"
if ($this->emulate_prepares || count($args) == 0) {
if (!$retval = $this->connection->query($this->replaceParameters($sql, $args))) {
$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]);
$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', [
$this->errorno = $errorno;
}
- $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
+ $this->profiler->saveTimestamp($stamp1, 'database');
if ($this->configCache->get('system', 'db_log')) {
$stamp2 = microtime(true);
@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, 2000) . "\n", FILE_APPEND);
+ substr($this->replaceParameters($sql, $args), 0, 4000) . "\n", FILE_APPEND);
}
}
return $retval;
$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', [
$this->errorno = $errorno;
}
- $this->profiler->saveTimestamp($stamp, "database_write", System::callstack());
+ $this->profiler->saveTimestamp($stamp, "database_write");
return $retval;
}
return 0;
}
switch ($this->driver) {
- case 'pdo':
+ case self::PDO:
return $stmt->columnCount();
- case 'mysqli':
+ case self::MYSQLI:
return $stmt->field_count;
}
return 0;
return 0;
}
switch ($this->driver) {
- case 'pdo':
+ case self::PDO:
return $stmt->rowCount();
- case 'mysqli':
+ case self::MYSQLI:
return $stmt->num_rows;
}
return 0;
/**
* 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);
$columns = [];
}
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;
}
}
}
- $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
+ $this->profiler->saveTimestamp($stamp1, 'database');
return $columns;
}
/**
* 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, $param, $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 . " = ?";
$param = array_merge_recursive($values, $values);
}
+ $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
+ *
+ * @return boolean was the insert successful?
+ * @throws \Exception
+ */
+ public function replace($table, array $param)
+ {
+ 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 = "REPLACE " . $table_string . " (" . $fields_string . ") VALUES (" . $values_string . ")";
+
return $this->e($sql, $param);
}
public function lastInsertId()
{
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;
}
/**
public function lock($table)
{
// 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 {
$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);
// 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 {
}
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;
}
protected function performCommit()
{
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();
}
$ret = false;
switch ($this->driver) {
- case 'pdo':
+ case self::PDO:
if (!$this->connection->inTransaction()) {
$ret = true;
break;
$ret = $this->connection->rollBack();
break;
- case 'mysqli':
+ case self::MYSQLI:
$ret = $this->connection->rollback();
break;
}
if (is_bool($old_fields)) {
if ($do_insert) {
$values = array_merge($condition, $fields);
- return $this->insert($table, $values, $do_insert);
+ return $this->replace($table, $values);
}
$old_fields = [];
}
return true;
}
+ $fields = $this->castFields($table, $fields);
+
$table_string = DBA::buildTableString($table);
$condition_string = DBA::buildCondition($condition);
/**
* Select rows from a table
*
- * @param string|array $table Table name or array [schema => table]
- * @param array $fields Array of selected fields, empty for all
- * @param array $condition Array of fields for condition
- * @param array $params Array of several parameters
- *
- * @return boolean|object
*
* Example:
- * $table = "item";
- * $fields = array("id", "uri", "uid", "network");
+ * $table = 'item';
+ * or:
+ * $table = ['schema' => 'table'];
+ * @see DBA::buildTableString()
*
- * $condition = array("uid" => 1, "network" => 'dspr');
+ * $fields = ['id', 'uri', 'uid', 'network'];
+ *
+ * $condition = ['uid' => 1, 'network' => 'dspr', 'blocked' => true];
* or:
- * $condition = array("`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr');
+ * $condition = ['`uid` = ? AND `network` IN (?, ?)', 1, 'dfrn', 'dspr'];
+ * @see DBA::buildCondition()
*
- * $params = array("order" => array("id", "received" => true), "limit" => 10);
+ * $params = ['order' => ['id', 'received' => true, 'created' => 'ASC'), 'limit' => 10];
+ * @see DBA::buildParameter()
*
* $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
+ * @return boolean|object
* @throws \Exception
*/
public function select($table, array $fields = [], array $condition = [], array $params = [])
$result = $this->p($sql, $condition);
+ if (($this->driver == self::PDO) && !empty($result) && is_string($table)) {
+ $result->table = $table;
+ }
+
return $result;
}
$row = $this->fetchFirst($sql, $condition);
- return $row['count'];
+ // Ensure to always return either a "null" or a numeric value
+ return is_numeric($row['count']) ? (int)$row['count'] : $row['count'];
}
/**
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
*
}
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.
break;
}
- $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
+ $this->profiler->saveTimestamp($stamp1, 'database');
return $ret;
}
projects / friendica.git / blobdiff