tim
c8030b759d
A page of 50 stories read 50 images from the database only to see which stories had one.
208 lines
10 KiB
PHP
208 lines
10 KiB
PHP
<?php
|
|
namespace Xxx\Tests\Support;
|
|
|
|
/*
|
|
* In-memory copies of the site tables (xxx, queue, editors and
|
|
* persistent_sessions), with FakeDb handlers for the queries that the
|
|
* code sends. It knows only those queries, not SQL in general.
|
|
*
|
|
* Story rows have the columns id, title, author, email, date, story,
|
|
* hidden, img, mime, comments and editor.
|
|
*/
|
|
class SiteData {
|
|
public static array $tables = array();
|
|
public static int $lastInsertId = 0;
|
|
// The size of editors.password; NULL means that the column keeps any hash
|
|
public static ?int $passwordLength = NULL;
|
|
|
|
public static function install(array $tables = array()): void {
|
|
self::$tables = $tables + array('xxx' => array(), 'queue' => array(), 'editors' => array(), 'persistent_sessions' => array());
|
|
foreach (array('xxx', 'queue') as $table)
|
|
self::$tables[$table] = array_map(array(self::class, 'story'), self::$tables[$table]);
|
|
self::$lastInsertId = 0;
|
|
|
|
$stories = '(xxx|queue)';
|
|
FakeDb::on("/^SELECT id, title, author, email, date, story, hidden, mime, comments, editor, IF\\(LENGTH\\(img\\) > 0, 1, NULL\\) AS img FROM $stories\\b/",
|
|
function ($sql, $params) {
|
|
return array_map(function ($row) {
|
|
$row['img'] = strlen((string)$row['img']) > 0 ? 1 : NULL;
|
|
return $row;
|
|
}, self::selectStories($sql, $params));
|
|
});
|
|
FakeDb::on("/^SELECT COUNT\\(\\*\\) FROM $stories\\b/", function ($sql, $params) {
|
|
return array(array('COUNT(*)' => (string)count(self::filterStories($sql, $params))));
|
|
});
|
|
FakeDb::on('/^SELECT MAX\(`date`\) FROM xxx/', function () {
|
|
$dates = array_column(self::$tables['xxx'], 'date');
|
|
return array(array('MAX(`date`)' => $dates ? max($dates) : NULL));
|
|
});
|
|
FakeDb::on("/^SELECT img, mime(, hidden)? FROM $stories\\b/i", function ($sql, $params) {
|
|
$rows = self::filterStories($sql, $params);
|
|
$columns = str_starts_with($sql, 'SELECT img, mime, hidden') ? array('img', 'mime', 'hidden') : array('img', 'mime');
|
|
return array_map(function ($row) use ($columns) { return array_intersect_key($row, array_flip($columns)); }, array_slice($rows, 0, 1));
|
|
});
|
|
FakeDb::on("/^INSERT INTO $stories SET /", array(self::class, 'insertStory'));
|
|
FakeDb::on("/^UPDATE $stories SET /", array(self::class, 'updateStory'));
|
|
FakeDb::on("/^DELETE FROM $stories WHERE `id` = \\? LIMIT 1/", function ($sql, $params) {
|
|
$table = self::tableOf($sql);
|
|
self::$tables[$table] = array_values(array_filter(self::$tables[$table], function ($row) use ($params) {
|
|
return (int)$row['id'] !== (int)$params[0];
|
|
}));
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^SELECT LAST_INSERT_ID\(\)/', function () {
|
|
return array(array('LAST_INSERT_ID()' => (string)self::$lastInsertId));
|
|
});
|
|
|
|
FakeDb::on('/^SELECT (\*|username|password) FROM editors WHERE username = \?/', function ($sql, $params) {
|
|
return array_slice(array_values(array_filter(self::$tables['editors'], function ($row) use ($params) {
|
|
return $row['username'] === $params[0];
|
|
})), 0, 1);
|
|
});
|
|
FakeDb::on('/^UPDATE editors SET password = \? WHERE username = \?/', function ($sql, $params) {
|
|
foreach (self::$tables['editors'] as &$row) {
|
|
if ($row['username'] === $params[1])
|
|
$row['password'] = self::cut($params[0]);
|
|
}
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^INSERT INTO editors SET `username` = \?, `password` = \?, `email` = \?/', function ($sql, $params) {
|
|
self::$tables['editors'][] = array('username' => $params[0], 'password' => self::cut($params[1]), 'email' => $params[2]);
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^DELETE FROM editors WHERE username = \?/', function ($sql, $params) {
|
|
self::$tables['editors'] = array_values(array_filter(self::$tables['editors'], function ($row) use ($params) {
|
|
return $row['username'] !== $params[0];
|
|
}));
|
|
return TRUE;
|
|
});
|
|
|
|
FakeDb::on('/^SELECT \* FROM persistent_sessions WHERE username = \? AND session = \?/', function ($sql, $params) {
|
|
return array_slice(array_values(array_filter(self::$tables['persistent_sessions'], function ($row) use ($params) {
|
|
return $row['username'] === $params[0] && $row['session'] === $params[1];
|
|
})), 0, 1);
|
|
});
|
|
FakeDb::on('/^SELECT p\.username, p\.session FROM persistent_sessions p JOIN editors e ON e\.username = p\.username\s+WHERE p\.username = \? AND p\.session = \? AND p\.created > DATE_SUB\(now\(\), INTERVAL 1 YEAR\)/', function ($sql, $params) {
|
|
$editors = array_column(self::$tables['editors'], 'username');
|
|
$limit = gmdate('Y-m-d H:i:s', strtotime('-1 year'));
|
|
return array_slice(array_values(array_map(function ($row) {
|
|
return array('username' => $row['username'], 'session' => $row['session']);
|
|
}, array_filter(self::$tables['persistent_sessions'], function ($row) use ($params, $editors, $limit) {
|
|
return $row['username'] === $params[0] && $row['session'] === $params[1]
|
|
&& in_array($row['username'], $editors, TRUE) && ($row['created'] ?? $limit) > $limit;
|
|
}))), 0, 1);
|
|
});
|
|
FakeDb::on('/^DELETE FROM persistent_sessions WHERE username = \?$/', function ($sql, $params) {
|
|
self::$tables['persistent_sessions'] = array_values(array_filter(self::$tables['persistent_sessions'], function ($row) use ($params) {
|
|
return $row['username'] !== $params[0];
|
|
}));
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^INSERT INTO persistent_sessions \(username, session\) VALUES \(\?, \?\)/', function ($sql, $params) {
|
|
self::$tables['persistent_sessions'][] = array('username' => $params[0], 'session' => $params[1], 'created' => gmdate('Y-m-d H:i:s'));
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^DELETE FROM persistent_sessions WHERE username = \? AND session = \?/', function ($sql, $params) {
|
|
self::$tables['persistent_sessions'] = array_values(array_filter(self::$tables['persistent_sessions'], function ($row) use ($params) {
|
|
return !($row['username'] === $params[0] && $row['session'] === $params[1]);
|
|
}));
|
|
return TRUE;
|
|
});
|
|
FakeDb::on('/^DELETE FROM persistent_sessions\s+WHERE created < DATE_SUB/', function () {
|
|
$limit = gmdate('Y-m-d H:i:s', strtotime('-1 year'));
|
|
self::$tables['persistent_sessions'] = array_values(array_filter(self::$tables['persistent_sessions'], function ($row) use ($limit) {
|
|
return strlen($row['session']) === 64 && ($row['created'] ?? $limit) >= $limit;
|
|
}));
|
|
return TRUE;
|
|
});
|
|
}
|
|
|
|
// A story row with every column, from the columns given
|
|
public static function story(array $row): array {
|
|
static $next = 1;
|
|
$row += array('id' => $next, 'title' => 'Title', 'author' => 'Author', 'email' => '',
|
|
'date' => '2020-01-01 10:00:00', 'story' => '<p>Story.</p>', 'hidden' => '0',
|
|
'img' => NULL, 'mime' => NULL, 'comments' => '', 'editor' => 'original author');
|
|
$next = max($next, (int)$row['id']) + 1;
|
|
$row['id'] = (string)$row['id'];
|
|
return $row;
|
|
}
|
|
|
|
public static function tableOf(string $sql): string {
|
|
preg_match('/\b(FROM|INTO|UPDATE)\s+`?(xxx|queue)\b/', $sql, $match);
|
|
return $match[2];
|
|
}
|
|
|
|
// The rows of the story table in $sql that its WHERE clause selects
|
|
public static function filterStories(string $sql, array $params): array {
|
|
$rows = self::$tables[self::tableOf($sql)];
|
|
if (stripos($sql, 'hidden IS NOT TRUE') !== FALSE)
|
|
$rows = array_filter($rows, function ($row) { return !$row['hidden']; });
|
|
if (preg_match('/\bid = (\d+)/i', $sql, $match))
|
|
$rows = array_filter($rows, function ($row) use ($match) { return (int)$row['id'] === (int)$match[1]; });
|
|
else if (preg_match('/\bid = \?/i', $sql))
|
|
$rows = array_filter($rows, function ($row) use ($params) { return (int)$row['id'] === (int)$params[0]; });
|
|
if (preg_match('/title LIKE \?/', $sql)) {
|
|
$term = strtolower(trim($params[0], '%'));
|
|
$rows = array_filter($rows, function ($row) use ($term) {
|
|
foreach (array('title', 'author', 'date', 'story') as $column) {
|
|
if (str_contains(strtolower((string)$row[$column]), $term))
|
|
return TRUE;
|
|
}
|
|
return FALSE;
|
|
});
|
|
}
|
|
return array_values($rows);
|
|
}
|
|
|
|
public static function selectStories(string $sql, array $params): array {
|
|
$rows = self::filterStories($sql, $params);
|
|
if (preg_match('/ORDER BY (\w+) (asc|desc)/i', $sql, $match)) {
|
|
$column = $match[1];
|
|
usort($rows, function ($a, $b) use ($column) { return strcmp((string)$a[$column], (string)$b[$column]); });
|
|
if (strtolower($match[2]) === 'desc')
|
|
$rows = array_reverse($rows);
|
|
}
|
|
if (preg_match('/LIMIT (\d+)\s*,\s*(\d+)/', $sql, $match))
|
|
$rows = array_slice($rows, (int)$match[1], (int)$match[2]);
|
|
return $rows;
|
|
}
|
|
|
|
// The column => value pairs of "SET `a` = ?, `b` = ?"
|
|
private static function assignments(string $sql, array $params): array {
|
|
preg_match_all('/`(\w+)` = \?/', explode(' WHERE ', $sql)[0], $matches);
|
|
return array_combine($matches[1], array_slice($params, 0, count($matches[1])));
|
|
}
|
|
|
|
public static function insertStory(string $sql, array $params) {
|
|
$ids = array_merge(array_column(self::$tables['xxx'], 'id'), array_column(self::$tables['queue'], 'id'), array(0));
|
|
$row = self::story(array('id' => max(array_map('intval', $ids)) + 1) + self::assignments($sql, $params));
|
|
self::$tables[self::tableOf($sql)][] = $row;
|
|
self::$lastInsertId = (int)$row['id'];
|
|
return TRUE;
|
|
}
|
|
|
|
public static function updateStory(string $sql, array $params) {
|
|
$id = (int)end($params);
|
|
$table = self::tableOf($sql);
|
|
foreach (self::$tables[$table] as &$row) {
|
|
if ((int)$row['id'] === $id)
|
|
$row = array_merge($row, self::assignments($sql, $params));
|
|
}
|
|
return TRUE;
|
|
}
|
|
|
|
private static function cut(string $value): string {
|
|
return self::$passwordLength === NULL ? $value : substr($value, 0, self::$passwordLength);
|
|
}
|
|
|
|
// The rows of a story table, found by id
|
|
public static function find(string $table, $id): ?array {
|
|
foreach (self::$tables[$table] as $row) {
|
|
if ((int)$row['id'] === (int)$id)
|
|
return $row;
|
|
}
|
|
return NULL;
|
|
}
|
|
}
|