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.php418
1 files changed, 383 insertions, 35 deletions
diff --git a/inc/database.inc.php b/inc/database.inc.php
index f76c9e7..c1f9454 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -8,28 +8,43 @@ class Database
{
/**
- *
* @var \PDO Database handle
*/
private static $dbh = false;
- private static $statements = array();
-
-
- /**
+
+ private static $returnErrors;
+ private static $lastError = false;
+ private static $explainList = array();
+ private static $queryCount = 0;
+ private static $queryTime = 0;
+
+ /**
* Connect to the DB if not already connected.
*/
- private static function init()
+ public static function init(bool $returnErrors = false): bool
{
if (self::$dbh !== false)
- return;
+ 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());
}
+ 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();
+ });
+ }
+ return true;
}
/**
@@ -37,25 +52,100 @@ 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 = false)
+ 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();
+ }
+
+ /**
+ * If you need all rows for a query as plain array you can use this.
+ * Don't use this if you want to do further processing of the data, to save some
+ * memory.
+ *
+ * @return array|bool List of associative arrays representing rows, or false on error
+ */
+ 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();
+ }
+
+ /**
+ * Fetch the first column of the query as a plain list-of-values array.
+ *
+ * @return array|bool List of values representing first column of query
+ */
+ public static function queryColumnArray(string $query, array $args = [], bool $ignoreError = null)
+ {
+ $res = self::simpleQuery($query, $args, $ignoreError);
+ if ($res === false)
+ return false;
+ return $res->fetchAll(PDO::FETCH_COLUMN, 0);
+ }
+
+ /**
+ * 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->fetch(PDO::FETCH_ASSOC);
+ 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 = false)
+ public static function exec(string $query, array $args = [], bool $ignoreError = null)
{
$res = self::simpleQuery($query, $args, $ignoreError);
if ($res === false)
@@ -64,41 +154,194 @@ 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 string|false return last error returned by query
+ */
+ public static function lastError()
+ {
+ return self::$lastError;
+ }
+
+ /**
* Execute the given query and return the corresponding PDOStatement object
* Note that this will re-use PDOStatements, so if you run the same
* query again with different params, do not rely on the first PDOStatement
* still being valid. If you need to do something fancy, use Database::prepare
- * @return \PDOStatement The query result object
+ *
+ * @return \PDOStatement|false The query result object
*/
- public static function simpleQuery($query, $args = array(), $ignoreError = false)
+ public static function simpleQuery(string $query, array $args = [], bool $ignoreError = null)
{
self::init();
- try {
- if (!isset(self::$statements[$query])) {
- self::$statements[$query] = self::$dbh->prepare($query);
- } else {
- self::$statements[$query]->closeCursor();
+ if (CONFIG_DEBUG && !isset(self::$explainList[$query]) && preg_match('/^\s*SELECT/is', $query)) {
+ self::$explainList[$query] = [$args];
+ }
+ // Support passing nested arrays for IN statements, automagically refactor
+ $oquery = $query;
+ self::handleArrayArgument($query, $args);
+ // 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';
}
- if (self::$statements[$query]->execute($args) === false) {
- if ($ignoreError)
+ }
+ try {
+ $stmt = self::$dbh->prepare($query);
+ $start = microtime(true);
+ 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" . implode("\n", self::$statements[$query]->errorInfo()));
+ Util::traceError("Database Error: \n" . self::$lastError);
}
- return self::$statements[$query];
+ if (CONFIG_DEBUG) {
+ $duration = microtime(true) - $start;
+ self::$queryTime += $duration;
+ $duration = round($duration, 3);
+ if (isset(self::$explainList[$oquery])) {
+ self::$explainList[$oquery][] = $duration;
+ } elseif ($duration > 0.1) {
+ error_log('SLOW ****** ' . $duration . "s *********\n" . $query);
+ }
+ self::$queryCount += 1;
+ }
+ return $stmt;
} catch (Exception $e) {
- if ($ignoreError)
- return false;
- Util::traceError("Database Error: \n" . $e->getMessage());
+ self::$lastError = '(' . $e->getCode() . ') ' . $e->getMessage();
+ if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
+ return false;
+ Util::traceError("Database Error: \n" . self::$lastError);
+ }
+ return false;
+ }
+
+ public static function examineLoggedQueries()
+ {
+ foreach (self::$explainList as $q => $a) {
+ self::explainQuery($q, $a);
+ }
+ }
+
+ private static function explainQuery(string $query, array $data)
+ {
+ $args = array_shift($data);
+ $slow = false;
+ $veryslow = false;
+ foreach ($data as &$ts) {
+ if ($ts > 0.004) {
+ $slow = true;
+ if ($ts > 0.015) {
+ $ts = "[$ts]";
+ $veryslow = true;
+ }
+ }
+ }
+ if (!$slow)
+ return;
+ unset($ts);
+ $res = self::simpleQuery('EXPLAIN ' . $query, $args, true);
+ if ($res === false)
+ return;
+ $rows = $res->fetchAll();
+ if (empty($rows))
+ return;
+ $log = $veryslow;
+ $lens = array();
+ foreach (array_keys($rows[0]) as $key) {
+ $lens[$key] = strlen($key);
+ }
+ foreach ($rows as $row) {
+ if (!$log && $row['rows'] > 20 && preg_match('/filesort|temporary/i', $row['Extra'])) {
+ $log = true;
+ }
+ foreach ($row as $key => $col) {
+ $l = strlen($col);
+ if ($l > $lens[$key]) {
+ $lens[$key] = $l;
+ }
+ }
+ }
+ if (!$log)
+ return;
+ error_log('Possible slow query: ' . $query);
+ error_log('Times: ' . implode(', ', $data));
+ $border = $head = '';
+ foreach ($lens as $key => $len) {
+ $border .= '+' . str_repeat('-', $len + 2);
+ $head .= '| ' . str_pad($key, $len) . ' ';
+ }
+ $border .= '+';
+ $head .= '|';
+ error_log("\n" . $border . "\n" . $head . "\n" . $border);
+ foreach ($rows as $row) {
+ $line = '';
+ foreach ($lens as $key => $len) {
+ $line .= '| '. str_pad($row[$key], $len) . ' ';
+ }
+ error_log($line . "|");
+ }
+ error_log($border);
+ }
+
+ /**
+ * Convert nested array argument to multiple arguments.
+ * If you have:
+ * $query = 'SELECT * FROM tbl WHERE bcol = :bool AND col IN (:list)
+ * $args = ( 'bool' => 1, 'list' => ('foo', 'bar') )
+ * it results in:
+ * $query = '...WHERE bcol = :bool AND col IN (:list_0, :list_1)
+ * $args = ( 'bool' => 1, 'list_0' => 'foo', 'list_1' => 'bar' )
+ *
+ * @param string $query sql query string
+ * @param array $args query arguments
+ * @return void
+ */
+ private static function handleArrayArgument(string &$query, array &$args)
+ {
+ $again = false;
+ foreach (array_keys($args) as $key) {
+ if (is_numeric($key) || $key === '?')
+ continue;
+ if (is_array($args[$key])) {
+ if (empty($args[$key])) {
+ // Empty list - what to do? We try to generate a query string that will not yield any result
+ $args[$key] = 'asdf' . mt_rand(0,PHP_INT_MAX) . mt_rand(0,PHP_INT_MAX)
+ . mt_rand(0,PHP_INT_MAX) . '@' . microtime(true);
+ continue;
+ }
+ $newkey = $key;
+ if ($newkey[0] !== ':') {
+ $newkey = ":$newkey";
+ }
+ $new = array();
+ foreach ($args[$key] as $subIndex => $sub) {
+ if (is_array($sub)) {
+ $new[] = '(' . $newkey . '_' . $subIndex . ')';
+ $again = true;
+ } else {
+ $new[] = $newkey . '_' . $subIndex;
+ }
+ $args[$newkey . '_' . $subIndex] = $sub;
+ }
+ unset($args[$key]);
+ $new = implode(',', $new);
+ $query = preg_replace('/' . $newkey . '\b/', $new, $query);
+ }
+ }
+ if ($again) {
+ self::handleArrayArgument($query, $args);
}
}
@@ -106,10 +349,115 @@ 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::init();
+ self::$queryCount += 1; // Cannot know actual count
return self::$dbh->prepare($query);
}
+ /**
+ * Insert row into table, returning the generated key.
+ * This requires the table to have an AUTO_INCREMENT column and
+ * usually requires the given $uniqueValues to span across a UNIQUE index.
+ * The code first tries to SELECT the key for the given values without
+ * inserting first. This means this function is best used for cases
+ * where you expect that the entry already exists in the table, so
+ * only one SELECT will run. For all the entries that do not exist,
+ * an INSERT or INSERT IGNORE is run, depending on whether $additionalValues
+ * is empty or not. Another reason we don't run the INSERT (IGNORE) first
+ * is that it will increase the AUTO_INCREMENT value on InnoDB, even when
+ * no INSERT took place. So if you expect a lot of collisions you might
+ * use this function to prevent your A_I value from counting up too
+ * quickly.
+ * Other than that, this is just a dumb version of running INSERT and then
+ * getting the LAST_INSERT_ID(), or doing a query for the existing ID in
+ * case of a key collision.
+ *
+ * @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 AUTO_INCREMENT value matching the given unique values entry
+ */
+ 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");
+ }
+ if (is_array($additionalValues) && array_key_exists($aiKey, $additionalValues)) {
+ Util::traceError("$aiKey must not be in \$additionalValues");
+ }
+ // Simple SELECT first
+ $selectSql = 'SELECT ' . $aiKey . ' FROM ' . $table . ' WHERE 1';
+ foreach ($uniqueValues as $key => $value) {
+ $selectSql .= ' AND ' . $key . ' = :' . $key;
+ }
+ $selectSql .= ' LIMIT 1';
+ $res = self::queryFirst($selectSql, $uniqueValues);
+ if ($res !== false) {
+ // Exists
+ if (!empty($additionalValues)) {
+ // Simulate ON DUPLICATE KEY UPDATE ...
+ $updateSql = 'UPDATE ' . $table . ' SET ';
+ $first = true;
+ foreach ($additionalValues as $key => $value) {
+ if ($first) {
+ $first = false;
+ } else {
+ $updateSql .= ', ';
+ }
+ $updateSql .= $key . ' = :' . $key;
+ }
+ $updateSql .= ' WHERE ' . $aiKey . ' = :' . $aiKey;
+ $additionalValues[$aiKey] = $res[$aiKey];
+ Database::exec($updateSql, $additionalValues);
+ }
+ return $res[$aiKey];
+ }
+ // Does not exist:
+ if (empty($additionalValues)) {
+ $combined =& $uniqueValues;
+ } else {
+ $combined = $uniqueValues + $additionalValues;
+ }
+ // Aight, try INSERT or INSERT IGNORE
+ $insertSql = 'INTO ' . $table . ' (' . implode(', ', array_keys($combined))
+ . ') VALUES (:' . implode(', :', array_keys($combined)) . ')';
+ if (empty($additionalValues)) {
+ // Simple INSERT IGNORE
+ $insertSql = 'INSERT IGNORE ' . $insertSql;
+ } else {
+ // INSERT ... ON DUPLICATE (in case we have a race)
+ $insertSql = 'INSERT ' . $insertSql . ' ON DUPLICATE KEY UPDATE ';
+ $first = true;
+ foreach ($additionalValues as $key => $value) {
+ if ($first) {
+ $first = false;
+ } else {
+ $insertSql .= ', ';
+ }
+ $insertSql .= $key . ' = VALUES(' . $key . ')';
+ }
+ }
+ self::exec($insertSql, $combined);
+ // 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');
+ }
+ return $res[$aiKey];
+ }
+
+ public static function getQueryCount(): int
+ {
+ return self::$queryCount;
+ }
+
+ public static function getQueryTime(): int
+ {
+ return self::$queryTime;
+ }
+
}
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2024-11-21 13:33:47 +0100