summaryrefslogtreecommitdiffstats
path: root/inc/database.inc.php
diff options
context:
space:
mode:
Diffstat (limited to 'inc/database.inc.php')
-rw-r--r--inc/database.inc.php155
1 files changed, 101 insertions, 54 deletions
diff --git a/inc/database.inc.php b/inc/database.inc.php
index eddd4faf..83720baa 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -1,5 +1,7 @@
<?php
+declare(strict_types=1);
+
/**
* Handle communication with the database
* This is a very thin layer between you and PDO.
@@ -11,37 +13,38 @@ class Database
* @var \PDO Database handle
*/
private static $dbh = false;
- /*
- * @var \PDOStatement[]
- */
- private static $statements = array();
- private static $returnErrors;
- private static $lastError = false;
+
+ /** @var bool */
+ private static $returnErrors = false;
+ /** @var ?string */
+ private static $lastError = null;
private static $explainList = array();
private static $queryCount = 0;
+ /** @var float */
private static $queryTime = 0;
/**
* Connect to the DB if not already connected.
*/
- public static function init($returnErrors = false)
+ public static function init(bool $returnErrors = false): bool
{
if (self::$dbh !== false)
return true;
self::$returnErrors = $returnErrors;
try {
- if (CONFIG_SQL_FORCE_UTF8) {
- self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
- } else {
- self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS);
- }
+ self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS, [
+ PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
+ PDO::ATTR_EMULATE_PREPARES => true,
+ PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4', // Somehow needed, even if charset=utf8mb4 is in DSN?
+ ]);
} catch (PDOException $e) {
if (self::$returnErrors)
return false;
- Util::traceError('Connecting to the local database failed: ' . $e->getMessage());
+ ErrorHandler::traceError('Connecting to the local database failed: ' . $e->getMessage());
}
if (CONFIG_DEBUG) {
- Database::exec("SET sql_mode='STRICT_TRANS_TABLES'");
+ Database::exec("SET SESSION sql_mode='STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO'");
+ Database::exec("SET SESSION innodb_strict_mode=ON");
register_shutdown_function(function() {
self::examineLoggedQueries();
});
@@ -54,12 +57,12 @@ class Database
*
* @return array|boolean Associative array representing row, or false if no row matches the query
*/
- public static function queryFirst($query, $args = array(), $ignoreError = null)
+ public static function queryFirst(string $query, array $args = [], bool $ignoreError = null)
{
$res = self::simpleQuery($query, $args, $ignoreError);
if ($res === false)
return false;
- return $res->fetch(PDO::FETCH_ASSOC);
+ return $res->fetch();
}
/**
@@ -69,12 +72,12 @@ class Database
*
* @return array|bool List of associative arrays representing rows, or false on error
*/
- public static function queryAll($query, $args = array(), $ignoreError = null)
+ public static function queryAll(string $query, array $args = [], bool $ignoreError = null)
{
$res = self::simpleQuery($query, $args, $ignoreError);
if ($res === false)
return false;
- return $res->fetchAll(PDO::FETCH_ASSOC);
+ return $res->fetchAll();
}
/**
@@ -82,7 +85,7 @@ class Database
*
* @return array|bool List of values representing first column of query
*/
- public static function queryColumnArray($query, $args = array(), $ignoreError = null)
+ public static function queryColumnArray(string $query, array $args = [], bool $ignoreError = null)
{
$res = self::simpleQuery($query, $args, $ignoreError);
if ($res === false)
@@ -91,15 +94,63 @@ class Database
}
/**
+ * Fetch two columns as key => value list.
+ *
+ * @return array|bool Associative array, first column is key, second column is value
+ */
+ public static function queryKeyValueList(string $query, array $args = [], bool $ignoreError = null)
+ {
+ $res = self::simpleQuery($query, $args, $ignoreError);
+ if ($res === false)
+ return false;
+ return $res->fetchAll(PDO::FETCH_KEY_PAIR);
+ }
+
+ /**
+ * Fetch and group by first column. First column is key, value is a list of rows with remaining columns.
+ * [
+ * col1 => [
+ * [col2, col3],
+ * [col2, col3],
+ * ],
+ * ...,
+ * ]
+ *
+ * @return array|bool Associative array, first column is key, remaining columns are array values
+ */
+ public static function queryGroupList(string $query, array $args = [], bool $ignoreError = null)
+ {
+ $res = self::simpleQuery($query, $args, $ignoreError);
+ if ($res === false)
+ return false;
+ return $res->fetchAll(PDO::FETCH_GROUP);
+ }
+
+ /**
+ * Fetch and use first column as key of returned array.
+ * This is like queryGroup list, but it is assumed that the first column is unique, so
+ * the remaining columns won't be wrapped in another array.
+ *
+ * @return array|bool Associative array, first column is key, remaining columns are array values
+ */
+ public static function queryIndexedList(string $query, array $args = [], bool $ignoreError = null)
+ {
+ $res = self::simpleQuery($query, $args, $ignoreError);
+ if ($res === false)
+ return false;
+ return $res->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);
+ }
+
+ /**
* Execute the given query and return the number of rows affected.
* Mostly useful for UPDATEs or INSERTs
*
* @param string $query Query to run
* @param array $args Arguments to query
- * @param boolean $ignoreError Ignore query errors and just return false
+ * @param ?bool $ignoreError Ignore query errors and just return false
* @return int|boolean Number of rows affected, or false on error
*/
- public static function exec($query, $args = array(), $ignoreError = null)
+ public static function exec(string $query, array $args = [], bool $ignoreError = null)
{
$res = self::simpleQuery($query, $args, $ignoreError);
if ($res === false)
@@ -108,19 +159,19 @@ class Database
}
/**
- * Get id (promary key) of last row inserted.
+ * Get id (primary key) of last row inserted.
*
* @return int the id
*/
- public static function lastInsertId()
+ public static function lastInsertId(): int
{
- return self::$dbh->lastInsertId();
+ return (int)self::$dbh->lastInsertId();
}
/**
- * @return string|bool return last error returned by query
+ * @return ?string return last error returned by query
*/
- public static function lastError()
+ public static function lastError(): ?string
{
return self::$lastError;
}
@@ -133,10 +184,10 @@ class Database
*
* @return \PDOStatement|false The query result object
*/
- public static function simpleQuery($query, $args = array(), $ignoreError = null)
+ public static function simpleQuery(string $query, array $args = [], bool $ignoreError = null)
{
self::init();
- if (CONFIG_DEBUG && !isset(self::$explainList[$query]) && preg_match('/^\s*SELECT/is', $query)) {
+ if (CONFIG_DEBUG && !isset(self::$explainList[$query]) && preg_match('/^\s*SELECT/i', $query)) {
self::$explainList[$query] = [$args];
}
// Support passing nested arrays for IN statements, automagically refactor
@@ -152,17 +203,13 @@ class Database
}
}
try {
- if (!isset(self::$statements[$query])) {
- self::$statements[$query] = self::$dbh->prepare($query);
- } else {
- //self::$statements[$query]->closeCursor();
- }
+ $stmt = self::$dbh->prepare($query);
$start = microtime(true);
- if (self::$statements[$query]->execute($args) === false) {
- self::$lastError = implode("\n", self::$statements[$query]->errorInfo());
+ if ($stmt->execute($args) === false) {
+ self::$lastError = implode("\n", $stmt->errorInfo());
if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
return false;
- Util::traceError("Database Error: \n" . self::$lastError);
+ ErrorHandler::traceError("Database Error: \n" . self::$lastError);
}
if (CONFIG_DEBUG) {
$duration = microtime(true) - $start;
@@ -175,14 +222,13 @@ class Database
}
self::$queryCount += 1;
}
- return self::$statements[$query];
+ return $stmt;
} catch (Exception $e) {
self::$lastError = '(' . $e->getCode() . ') ' . $e->getMessage();
if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
return false;
- Util::traceError("Database Error: \n" . self::$lastError);
+ ErrorHandler::traceError("Database Error: \n" . self::$lastError);
}
- return false;
}
public static function examineLoggedQueries()
@@ -192,7 +238,7 @@ class Database
}
}
- private static function explainQuery($query, $data)
+ private static function explainQuery(string $query, array $data)
{
$args = array_shift($data);
$slow = false;
@@ -212,7 +258,7 @@ class Database
$res = self::simpleQuery('EXPLAIN ' . $query, $args, true);
if ($res === false)
return;
- $rows = $res->fetchAll(PDO::FETCH_ASSOC);
+ $rows = $res->fetchAll();
if (empty($rows))
return;
$log = $veryslow;
@@ -225,7 +271,7 @@ class Database
$log = true;
}
foreach ($row as $key => $col) {
- $l = strlen($col);
+ $l = strlen((string)($col ?? 'NULL'));
if ($l > $lens[$key]) {
$lens[$key] = $l;
}
@@ -246,7 +292,7 @@ class Database
foreach ($rows as $row) {
$line = '';
foreach ($lens as $key => $len) {
- $line .= '| '. str_pad($row[$key], $len) . ' ';
+ $line .= '| '. str_pad((string)($row[$key] ?? 'NULL'), $len) . ' ';
}
error_log($line . "|");
}
@@ -264,8 +310,9 @@ class Database
*
* @param string $query sql query string
* @param array $args query arguments
+ * @return void
*/
- private static function handleArrayArgument(&$query, &$args)
+ private static function handleArrayArgument(string &$query, array &$args)
{
$again = false;
foreach (array_keys($args) as $key) {
@@ -279,7 +326,7 @@ class Database
continue;
}
$newkey = $key;
- if ($newkey{0} !== ':') {
+ if ($newkey[0] !== ':') {
$newkey = ":$newkey";
}
$new = array();
@@ -306,7 +353,7 @@ class Database
* Simply calls PDO::prepare and returns the PDOStatement.
* You must call PDOStatement::execute manually on it.
*/
- public static function prepare($query)
+ public static function prepare(string $query)
{
self::init();
self::$queryCount += 1; // Cannot know actual count
@@ -334,17 +381,17 @@ class Database
* @param string $table table to insert into
* @param string $aiKey name of the AUTO_INCREMENT column
* @param array $uniqueValues assoc array containing columnName => value mapping
- * @param array $additionalValues assoc array containing columnName => value mapping
- * @return int[] list of AUTO_INCREMENT values matching the list of $values
+ * @param ?array $additionalValues assoc array containing columnName => value mapping
+ * @return int AUTO_INCREMENT value matching the given unique values entry
*/
- public static function insertIgnore($table, $aiKey, $uniqueValues, $additionalValues = false)
+ public static function insertIgnore(string $table, string $aiKey, array $uniqueValues, array $additionalValues = null): int
{
// Sanity checks
if (array_key_exists($aiKey, $uniqueValues)) {
- Util::traceError("$aiKey must not be in \$uniqueValues");
+ ErrorHandler::traceError("$aiKey must not be in \$uniqueValues");
}
if (is_array($additionalValues) && array_key_exists($aiKey, $additionalValues)) {
- Util::traceError("$aiKey must not be in \$additionalValues");
+ ErrorHandler::traceError("$aiKey must not be in \$additionalValues");
}
// Simple SELECT first
$selectSql = 'SELECT ' . $aiKey . ' FROM ' . $table . ' WHERE 1';
@@ -402,17 +449,17 @@ class Database
// Insert done, retrieve key again
$res = self::queryFirst($selectSql, $uniqueValues);
if ($res === false) {
- Util::traceError('Could not find value in table ' . $table . ' that was just inserted');
+ ErrorHandler::traceError('Could not find value in table ' . $table . ' that was just inserted');
}
return $res[$aiKey];
}
- public static function getQueryCount()
+ public static function getQueryCount(): int
{
return self::$queryCount;
}
- public static function getQueryTime()
+ public static function getQueryTime(): float
{
return self::$queryTime;
}
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2024-04-28 06:54:22 +0200