summaryrefslogtreecommitdiffstats
path: root/tests/Stubs/Database.php
diff options
context:
space:
mode:
Diffstat (limited to 'tests/Stubs/Database.php')
-rw-r--r--tests/Stubs/Database.php413
1 files changed, 413 insertions, 0 deletions
diff --git a/tests/Stubs/Database.php b/tests/Stubs/Database.php
new file mode 100644
index 00000000..b0f8f30c
--- /dev/null
+++ b/tests/Stubs/Database.php
@@ -0,0 +1,413 @@
+<?php
+
+/**
+ * SQLite-backed Database implementation for tests.
+ *
+ * Provides a lightweight, in-memory RDBMS without requiring MySQL, similar in spirit to DbUnit.
+ * It implements the subset of the legacy Database API used by our tests and by the classes we test.
+ */
+class Database
+{
+ private static ?PDO $pdo = null;
+ private static int $queryCount = 0;
+ private static float $queryTime = 0.0;
+ private static bool $initialized = false;
+
+ public static function reset(): void
+ {
+ self::$pdo = null; // drop the connection; will be recreated lazily
+ self::$queryCount = 0;
+ self::$queryTime = 0.0;
+ self::$initialized = false;
+ }
+
+ private static function connect(): PDO
+ {
+ if (self::$pdo instanceof PDO)
+ return self::$pdo;
+ $pdo = new PDO('sqlite::memory:');
+ $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
+ // Enable foreign keys if needed
+ $pdo->exec('PRAGMA foreign_keys = ON');
+ // Register lightweight MySQL-compatibility functions used in queries
+ if (method_exists($pdo, 'sqliteCreateFunction')) {
+ // UNIX_TIMESTAMP() -> current epoch seconds
+ $pdo->sqliteCreateFunction('UNIX_TIMESTAMP', function (): int { return time(); }, 0);
+ }
+ self::$pdo = $pdo;
+ self::initializeSchemaAndData();
+ return $pdo;
+ }
+
+ public static function resetSchema(): void
+ {
+ // Recreate connection and schema
+ self::reset();
+ self::connect();
+ }
+
+ public static function reseed(): void
+ {
+ if (!self::$pdo)
+ self::connect();
+ self::seedData(self::$pdo);
+ }
+
+ public static function truncateAll(): void
+ {
+ $pdo = self::connect();
+ $tables = ['location',
+ 'subnet',
+ 'machine',
+ 'user',
+ 'role',
+ 'role_x_user',
+ 'role_x_location',
+ 'role_x_permission',
+ 'clientlog',
+ 'mail_queue',
+ 'mail_config',
+ 'audit'];
+ foreach ($tables as $t) {
+ $pdo->exec("DELETE FROM $t");
+ }
+ }
+
+ private static function initializeSchemaAndData(): void
+ {
+ if (self::$initialized)
+ return;
+ $pdo = self::$pdo;
+ // Create tables
+ $pdo->exec('CREATE TABLE location (locationid INTEGER PRIMARY KEY, parentlocationid INTEGER NOT NULL, locationname TEXT NOT NULL, openingtime TEXT NULL)');
+ $pdo->exec('CREATE TABLE subnet (startaddr INTEGER NOT NULL, endaddr INTEGER NOT NULL, locationid INTEGER NOT NULL)');
+ $pdo->exec("CREATE TABLE machine (machineuuid TEXT PRIMARY KEY, subnetlocationid INTEGER, locationid INTEGER, fixedlocationid INTEGER, hostname TEXT, clientip TEXT, state TEXT DEFAULT 'OFFLINE')");
+ $pdo->exec('CREATE TABLE user (userid INTEGER PRIMARY KEY, login TEXT, passwd TEXT, fullname TEXT, lasteventid INTEGER, permissions INTEGER)');
+ $pdo->exec('CREATE TABLE role (roleid INTEGER PRIMARY KEY, rolename TEXT, builtin INTEGER DEFAULT 0, roledescription TEXT)');
+ $pdo->exec('CREATE TABLE role_x_user (userid INTEGER, roleid INTEGER)');
+ $pdo->exec('CREATE TABLE role_x_location (roleid INTEGER, locationid INTEGER)');
+ $pdo->exec('CREATE TABLE role_x_permission (roleid INTEGER, permissionid TEXT)');
+ $pdo->exec('CREATE TABLE clientlog (logid INTEGER PRIMARY KEY, dateline INTEGER, logtypeid TEXT, clientip TEXT, machineuuid TEXT, description TEXT, extra TEXT)');
+ $pdo->exec('CREATE TABLE mail_queue (mailid INTEGER PRIMARY KEY, configid INTEGER, rcpt TEXT, subject TEXT, body TEXT, dateline INTEGER, nexttry INTEGER DEFAULT 0)');
+ $pdo->exec('CREATE TABLE mail_config (configid INTEGER PRIMARY KEY, host TEXT, port INTEGER, ssl TEXT, senderaddress TEXT, replyto TEXT, username TEXT, password TEXT)');
+ $pdo->exec('CREATE TABLE audit (id INTEGER PRIMARY KEY, dateline INTEGER, userid INTEGER, ipaddr TEXT, module TEXT, action TEXT, data TEXT, response INTEGER)');
+ // Rebootcontrol scheduler table
+ $pdo->exec('CREATE TABLE reboot_scheduler (locationid INTEGER PRIMARY KEY, action TEXT, nextexecution INTEGER, options TEXT)');
+ // Exams module tables
+ $pdo->exec('CREATE TABLE exams (examid INTEGER PRIMARY KEY, lectureid TEXT NULL, autologin TEXT, starttime INTEGER, endtime INTEGER, description TEXT)');
+ $pdo->exec('CREATE TABLE exams_x_location (examid INTEGER, locationid INTEGER NULL)');
+ // Simulated sat.* schema as sat_ prefixed tables for SQLite
+ $pdo->exec('CREATE TABLE sat_user (userid INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, email TEXT)');
+ $pdo->exec('CREATE TABLE sat_lecture (lectureid TEXT PRIMARY KEY, ownerid INTEGER, displayname TEXT, starttime INTEGER, endtime INTEGER, isexam INTEGER, isenabled INTEGER, islocationprivate INTEGER DEFAULT 0)');
+ $pdo->exec('CREATE TABLE sat_lecture_x_location (lectureid TEXT, locationid INTEGER)');
+ self::seedData($pdo);
+ self::$initialized = true;
+ }
+
+ private static function seedData(PDO $pdo): void
+ {
+ // Seed a small canonical dataset used by multiple tests
+ $loc = $pdo->prepare('INSERT INTO location (locationid, parentlocationid, locationname) VALUES (?, ?, ?)');
+ $rows = [
+ [1, 0, 'Campus'],
+ [2, 1, 'Building A'],
+ [3, 1, 'Building B'],
+ [4, 2, 'Room 101'],
+ [5, 0, 'Offsite'],
+ ];
+ foreach ($rows as $r) {
+ $loc->execute($r);
+ }
+ // Seed opening times for locations: Campus (1) open all week 00:00-23:59
+ $today = date('l');
+ $now = time();
+ $week = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
+ $otAllDay = json_encode([
+ ['days' => $week, 'openingtime' => '00:00', 'closingtime' => '23:59']
+ ]);
+ $pdo->prepare('UPDATE location SET openingtime = ? WHERE locationid = 1')->execute([$otAllDay]);
+ // Force a predictable day/time check by crafting a narrow window around now
+ // XXX: Will fail around midnight, since the shifted opening times will span
+ // to the next/previous day, but be attached to the current weekday.
+ // First, move opening time into future (location 5)
+ $open = date('H:i', $now + 3600); // opens in 1h
+ $close = date('H:i', $now + 7200); // closes in 2h
+ $json = json_encode([[ 'days' => [$today], 'openingtime' => $open, 'closingtime' => $close ]]);
+ Database::exec('UPDATE location SET openingtime = :ot WHERE locationid = :id', ['ot' => $json, 'id' => 5]);
+ // Now, move opening time into past (location 4)
+ $open = date('H:i', $now - 7200); // opened 2h ago
+ $close = date('H:i', $now - 3600); // closed 1h ago
+ $json = json_encode([[ 'days' => [$today], 'openingtime' => $open, 'closingtime' => $close ]]);
+ Database::exec('UPDATE location SET openingtime = :ot WHERE locationid = :id', ['ot' => $json, 'id' => 4]);
+ // Subnets
+ $sub = $pdo->prepare('INSERT INTO subnet (startaddr, endaddr, locationid) VALUES (?, ?, ?)');
+ $sub->execute([sprintf('%u', ip2long('10.0.0.0')), sprintf('%u', ip2long('10.0.0.255')), 1]);
+ $sub->execute([sprintf('%u', ip2long('10.0.0.128')), sprintf('%u', ip2long('10.0.0.191')), 2]);
+ $sub->execute([sprintf('%u', ip2long('192.168.1.0')), sprintf('%u', ip2long('192.168.1.255')), 5]);
+ // Users and roles minimal
+ $pdo->exec("INSERT INTO user (userid, login, passwd, fullname, permissions) VALUES (1,'alice','STORED','Alice Doe',0), (2,'bob','STORED','Bob',0)");
+ $pdo->exec("INSERT INTO role (roleid, rolename, builtin, roledescription) VALUES (1,'Admin',1,''),(2,'Tech',0,'')");
+ $pdo->exec("INSERT INTO role_x_user (userid, roleid) VALUES (1,1)");
+ $pdo->exec('INSERT INTO role_x_user (userid, roleid) VALUES (1,2)');
+ $pdo->exec("INSERT INTO role_x_permission (roleid, permissionid) VALUES (1,'permissionmanager.*'),(2,'permissionmanager.users.view')");
+ // Map Admin role (1) globally (NULL -> treated as 0) and Tech role (2) to Building A (2) which has child Room 101 (4)
+ $pdo->exec('INSERT INTO role_x_location (roleid, locationid) VALUES (1, NULL)');
+ $pdo->exec('INSERT INTO role_x_location (roleid, locationid) VALUES (2, 2)');
+ // Machine table minimal
+ $pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid) VALUES ('uuid-1', NULL, NULL)");
+ // Ensure some machines with fixedlocationid/subnetlocationid and details for mismatch checks
+ $pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip)
+ VALUES ('m1', 1, 1, 5, 'host-m1', '10.0.0.10')");
+ $pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip)
+ VALUES ('m2', 5, 5, 5, 'host-m2', '192.168.1.50')");
+ // Seed some client log entries within last month for syslog tests (types and listing)
+ $now = time();
+ $ins = $pdo->prepare('INSERT INTO clientlog (dateline, logtypeid, clientip, machineuuid, description, extra) VALUES (?, ?, ?, ?, ?, ?)');
+ $ins->execute([$now - 60, 'session-open', '1.2.3.4', 'm1', 'User login', '']);
+ $ins->execute([$now - 120, 'partition-temp', '2.3.4.5', 'm2', 'Temp high', '']);
+ $pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip) VALUES ('wrong2', 0, NULL, 2, 'host-w2', '10.0.0.77')");
+ }
+
+ public static function pdo(): PDO
+ {
+ return self::connect();
+ }
+
+ private static function startTimer(): float { return microtime(true); }
+
+ private static function stopTimer(float $t0): void
+ {
+ self::$queryTime += (microtime(true) - $t0);
+ self::$queryCount++;
+ }
+
+ /**
+ * Expand array parameters and special bulk VALUES :arg syntax used in legacy code
+ */
+ private static function expandParams(string $sql, array $params): array
+ {
+ $flat = [];
+ // Handle bulk insert syntax: VALUES :arg where :arg is array of associative rows
+ if (preg_match('/VALUES\s*:(\w+)/i', $sql, $m)) {
+ $key = $m[1];
+ $rows = $params[$key] ?? [];
+ if (!is_array($rows) || empty($rows)) {
+ // No rows -> create a no-op that inserts zero rows; easiest is to use SELECT with no rows
+ $sql = preg_replace('/VALUES\s*:\w+/i', "SELECT 1 WHERE 0", $sql);
+ return [$sql, []];
+ }
+ $cols = array_keys($rows[0]);
+ $groups = [];
+ $idx = 0;
+ foreach ($rows as $r) {
+ $phs = [];
+ foreach ($cols as $c) {
+ $name = ":{$key}_{$c}_{$idx}";
+ $flat[$key . '_' . $c . '_' . $idx] = $r[$c];
+ $phs[] = $name;
+ }
+ $groups[] = '(' . implode(', ', $phs) . ')';
+ $idx++;
+ }
+ $sql = preg_replace('/VALUES\s*:\w+/i', 'VALUES ' . implode(', ', $groups), $sql);
+ // Remove the original bulk key so it does not get processed again
+ unset($params[$key]);
+ }
+ // Handle IN (:list) where param value is array
+ foreach ($params as $name => $value) {
+ if (is_array($value)) {
+ $placeholders = [];
+ foreach (array_values($value) as $i => $val) {
+ $ph = ":{$name}_{$i}";
+ $placeholders[] = $ph;
+ $flat[$name . '_' . $i] = $val;
+ }
+ $sql = preg_replace('/:' . preg_quote($name, '/') . '\b/', implode(', ', $placeholders), $sql);
+ } else {
+ $flat[$name] = $value;
+ }
+ }
+ return [$sql, $flat];
+ }
+
+ public static function exec(string $sql, array $params = [], ...$rest): int
+ {
+ $pdo = self::connect();
+ [$sql2, $flat] = self::expandParams($sql, $params);
+ $sql2 = self::fixupSyntax($sql2);
+ $t0 = self::startTimer();
+ try {
+ $stmt = $pdo->prepare($sql2);
+ } catch (PDOException $e) {
+ error_log($sql2);
+ }
+ $stmt->execute($flat);
+ self::stopTimer($t0);
+ return $stmt->rowCount();
+ }
+
+ /**
+ * Translates a MySQL-query into an SQLite query.
+ * Very rudimentary, might need improvements as we go.
+ */
+ private static function fixupSyntax(string $sql): string
+ {
+ // MySQL -> SQLite tweaks
+ $sql = preg_replace('/^\s*INSERT\s+IGNORE/i', 'INSERT OR IGNORE', $sql);
+ // sat.* schema mapping to sat_* tables for tests
+ $sql = preg_replace('/\bsat\.lecture_x_location\b/i', 'sat_lecture_x_location', $sql);
+ $sql = preg_replace('/\bsat\.lecture\b/i', 'sat_lecture', $sql);
+ $sql = preg_replace('/\bsat\.user\b/i', 'sat_user', $sql);
+ // GROUP_CONCAT with SEPARATOR -> group_concat(expr, sep)
+ $sql = preg_replace("/GROUP_CONCAT\s*\(\s*([^\)]*?)\s+SEPARATOR\s+'([^']*)'\s*\)/i", "group_concat($1, '$2')", $sql);
+ // Generic GROUP_CONCAT -> group_concat
+ $sql = preg_replace('/GROUP_CONCAT\s*\(/i', 'group_concat(', $sql);
+ // duplicate key handling
+ $parts = preg_split('/\bON\s+DUPLICATE\s+KEY\s+UPDATE\b/i', $sql);
+ if (count($parts) === 2) {
+ $parts[1] = preg_replace('/\bVALUES\s*\(\s*(\w+)\s*\)/i', 'excluded.$1', $parts[1]);
+ $sql = implode(' ON CONFLICT DO UPDATE SET ', $parts);
+ }
+ return $sql;
+ }
+
+ public static function lastInsertId(): int
+ {
+ return (int)self::connect()->lastInsertId();
+ }
+
+ public static function queryFirst(string $sql, array $params = [])
+ {
+ $pdo = self::connect();
+ [$sql2, $flat] = self::expandParams($sql, $params);
+ $sql2 = self::fixupSyntax($sql2);
+ $t0 = self::startTimer();
+ $stmt = $pdo->prepare($sql2);
+ $stmt->execute($flat);
+ self::stopTimer($t0);
+ $row = $stmt->fetch(PDO::FETCH_ASSOC);
+ return $row !== false ? $row : false;
+ }
+
+ public static function queryAll(string $sql, array $params = [])
+ {
+ $pdo = self::connect();
+ [$sql2, $flat] = self::expandParams($sql, $params);
+ $sql2 = self::fixupSyntax($sql2);
+ $t0 = self::startTimer();
+ $stmt = $pdo->prepare($sql2);
+ $stmt->execute($flat);
+ self::stopTimer($t0);
+ return $stmt->fetchAll(PDO::FETCH_ASSOC);
+ }
+
+ public static function simpleQuery(string $sql, array $params = [])
+ {
+ $pdo = self::connect();
+ [$sql2, $flat] = self::expandParams($sql, $params);
+ $sql2 = self::fixupSyntax($sql2);
+ $t0 = self::startTimer();
+ $stmt = $pdo->prepare($sql2);
+ $stmt->execute($flat);
+ self::stopTimer($t0);
+ return new SqliteDbResult($stmt);
+ }
+
+ public static function queryIndexedList(string $sql)
+ {
+ $pdo = self::connect();
+ $t0 = self::startTimer();
+ $sql = self::fixupSyntax($sql);
+ $stmt = $pdo->query($sql);
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ self::stopTimer($t0);
+ $out = [];
+ foreach ($rows as $row) {
+ // Use the first column as key
+ $key = array_key_first($row);
+ $out[$row[$key]] = $row;
+ }
+ return $out;
+ }
+
+ public static function queryKeyValueList(string $sql, array $params = [])
+ {
+ $pdo = self::connect();
+ [$sql2, $flat] = self::expandParams($sql, $params);
+ $sql2 = self::fixupSyntax($sql2);
+ $t0 = self::startTimer();
+ $stmt = $pdo->prepare($sql2);
+ $stmt->execute($flat);
+ $rows = $stmt->fetchAll(PDO::FETCH_NUM);
+ self::stopTimer($t0);
+ $out = [];
+ foreach ($rows as $row) {
+ if (count($row) >= 2) {
+ $out[$row[0]] = $row[1];
+ }
+ }
+ return $out;
+ }
+
+ /**
+ * Return rows grouped by a key. If the selected result includes a column named
+ * 'keyval', it will be used as the grouping key; otherwise, the first column is used.
+ * Each group contains a single-level numeric array of rows.
+ */
+ public static function queryGroupList(string $sql)
+ {
+ $pdo = self::connect();
+ $t0 = self::startTimer();
+ $sql = self::fixupSyntax($sql);
+ $stmt = $pdo->query($sql);
+ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+ self::stopTimer($t0);
+ $out = [];
+ foreach ($rows as $row) {
+ $key = array_key_exists('keyval', $row) ? $row['keyval'] : $row[array_key_first($row)];
+ $out[$key][] = $row;
+ }
+ return array_values($out);
+ }
+
+ public static function getQueryCount(): int { return self::$queryCount; }
+
+ public static function getQueryTime(): float { return self::$queryTime; }
+}
+
+class SqliteDbResult implements IteratorAggregate
+{
+ private PDOStatement $stmt;
+ private ?array $cache = null;
+
+ public function __construct(PDOStatement $stmt)
+ {
+ $this->stmt = $stmt;
+ }
+
+ private function ensureCache(): void
+ {
+ if ($this->cache === null) {
+ $this->cache = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
+ }
+ }
+
+ public function rowCount(): int
+ {
+ $this->ensureCache();
+ return count($this->cache);
+ }
+
+ public function getIterator(): Traversable
+ {
+ $this->ensureCache();
+ return new ArrayIterator($this->cache);
+ }
+
+ public function fetchAll(): array
+ {
+ $this->ensureCache();
+ return $this->cache;
+ }
+}
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2026-03-25 14:44:45 +0100