*/
class Database
{
+ const PDO = 'pdo';
+ const MYSQLI = 'mysqli';
+
+ const INSERT_DEFAULT = 0;
+ const INSERT_UPDATE = 1;
+ const INSERT_IGNORE = 2;
+
protected $connected = false;
/**
protected $connection;
protected $driver;
protected $emulate_prepares = false;
+ protected $pdo_emulate_prepares = false;
private $error = false;
private $errorno = 0;
private $affected_rows = 0;
$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) {
}
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;
}
{
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]);
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;
}
/**
* 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 . " = ?";
$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;
}
/**
return false;
}
+ $param = $this->castFields($table, $param);
+
$table_string = DBA::buildTableString($table);
$fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($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;
}
return true;
}
+ $fields = $this->castFields($table, $fields);
+
$table_string = DBA::buildTableString($table);
$condition_string = DBA::buildCondition($condition);
$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.
projects / friendica.git / blobdiff