diff options
Diffstat (limited to 'inc/database.inc.php')
-rw-r--r-- | inc/database.inc.php | 163 |
1 files changed, 110 insertions, 53 deletions
diff --git a/inc/database.inc.php b/inc/database.inc.php index 3b2414b5..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,36 +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 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(); }); @@ -53,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(); } /** @@ -68,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(); } /** @@ -81,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) @@ -90,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) @@ -107,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; } @@ -132,27 +184,32 @@ 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 $oquery = $query; self::handleArrayArgument($query, $args); - try { - if (!isset(self::$statements[$query])) { - self::$statements[$query] = self::$dbh->prepare($query); - } else { - //self::$statements[$query]->closeCursor(); + // Now turn any bools into 0 or 1, since PDO unfortunately only does (string)<bool>, which + // results in an empty string for false + foreach ($args as &$arg) { + if ($arg === false) { + $arg = '0'; + } elseif ($arg === true) { + $arg = '1'; } + } + try { + $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; @@ -165,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() @@ -182,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; @@ -202,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; @@ -215,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; } @@ -236,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 . "|"); } @@ -254,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) { @@ -269,7 +326,7 @@ class Database continue; } $newkey = $key; - if ($newkey{0} !== ':') { + if ($newkey[0] !== ':') { $newkey = ":$newkey"; } $new = array(); @@ -296,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 @@ -324,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'; @@ -392,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; } |