cacti/tests/Helpers/FakeMySQLPDO.php

<?php
/*
 +-------------------------------------------------------------------------+
 | Copyright (C) 2004-2026 The Cacti Group                                 |
 |                                                                         |
 | This program is free software; you can redistribute it and/or           |
 | modify it under the terms of the GNU General Public License             |
 | as published by the Free Software Foundation; either version 2          |
 | of the License, or (at your option) any later version.                  |
 +-------------------------------------------------------------------------+
 | Cacti: The Complete RRDtool-based Graphing Solution                     |
 +-------------------------------------------------------------------------+
*/

declare(strict_types = 1);

/**
 * Test-only PDO decorator that translates MySQL-specific syntax in
 * lib/database.php queries to a form sqlite::memory: can execute.
 * Only the surface used by the DB unit tests is implemented.
 *
 * The handle uses PDO::ERRMODE_EXCEPTION, so any SQL that the translate()
 * pass does not rewrite into valid SQLite syntax will raise PDOException
 * from prepare()/query()/exec(). Tests that exercise unsupported syntax
 * must catch or expect-throws explicitly.
 */
class FakeMySQLPDO extends PDO {
	public function __construct() {
		parent::__construct('sqlite::memory:');
		$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	}

	public function prepare(string $query, array $options = []): PDOStatement|false {
		return parent::prepare($this->translate($query), $options);
	}

	public function query(string $statement, ?int $fetchMode = null, mixed ...$fetchModeArgs): PDOStatement|false {
		$translated = $this->translate($statement);

		return $fetchMode === null
			? parent::query($translated)
			: parent::query($translated, $fetchMode, ...$fetchModeArgs);
	}

	public function exec(string $statement): int|false {
		return parent::exec($this->translate($statement));
	}

	private function translate(string $sql): string {
		$trim = ltrim($sql);

		// SQL_NO_CACHE is a MySQL SELECT modifier with no SQLite equivalent.
		$trim = preg_replace('/^SELECT\s+SQL_NO_CACHE\s+/i', 'SELECT ', $trim);

		// SHOW TABLES LIKE '...'
		if (preg_match('/^SHOW\s+TABLES\s+LIKE\s+(.+)$/is', $trim, $m)) {
			return "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE " . trim($m[1], '; ');
		}

		// SHOW TABLES (no LIKE)
		if (preg_match('/^SHOW\s+TABLES\b/i', $trim)) {
			return "SELECT name FROM sqlite_master WHERE type='table'";
		}

		// SHOW COLUMNS FROM table  (and "SHOW columns FROM table LIKE 'col'")
		if (preg_match('/^SHOW\s+COLUMNS\s+FROM\s+`?([A-Za-z0-9_]+)`?(?:\s+LIKE\s+(.+))?\s*;?\s*$/is', $trim, $m)) {
			$table = $m[1];
			$like  = isset($m[2]) ? trim($m[2], "'\" ;") : null;
			// sqlite pragma returns rows with: cid, name, type, notnull, dflt_value, pk
			// db_column_exists / db_get_table_column_types only need: Field (name), Type
			// Use a SELECT over pragma_table_info to project MySQL-shaped column names.
			$where = $like !== null ? ' WHERE name = ' . $this->quote($like) : '';

			return "SELECT name AS Field, type AS Type, CASE WHEN \"notnull\" = 0 THEN 'YES' ELSE 'NO' END AS \"Null\", '' AS Collation, '' AS Key, dflt_value AS \"Default\", '' AS Extra FROM pragma_table_info('$table')$where";
		}

		// INSERT ... ON DUPLICATE KEY UPDATE ...  -> INSERT OR REPLACE INTO ...
		if (preg_match('/^INSERT\s+INTO\s+`?([A-Za-z0-9_]+)`?\s*\(([^)]+)\)\s*VALUES\s*(.+?)\s+ON\s+DUPLICATE\s+KEY\s+UPDATE\b.*$/is', $trim, $m)) {
			return "INSERT OR REPLACE INTO `{$m[1]}` ({$m[2]}) VALUES {$m[3]}";
		}

		// INSERT IGNORE INTO ...  -> INSERT OR IGNORE INTO ...
		if (preg_match('/^INSERT\s+IGNORE\s+INTO\b/is', $trim)) {
			return preg_replace('/^INSERT\s+IGNORE\s+INTO\b/is', 'INSERT OR IGNORE INTO', $trim);
		}

		// SHOW INDEX FROM table - project the columns lib/database.php reads
		// (Key_name, Seq_in_index, Column_name, Non_unique) from sqlite's index
		// pragmas. Only explicitly created secondary indexes (origin 'c') are
		// reported, matching the prior sqlite_master projection so PRIMARY KEY
		// and UNIQUE-constraint autoindexes stay invisible as before.
		if (preg_match('/^SHOW\s+(?:INDEX|INDEXES|KEYS)\s+FROM\s+`?([A-Za-z0-9_]+)`?\s*;?\s*$/i', $trim, $m)) {
			$table = $m[1];

			return "SELECT il.name AS Key_name, ii.seqno + 1 AS Seq_in_index, ii.name AS Column_name, "
				. "CASE WHEN il.\"unique\" = 1 THEN 0 ELSE 1 END AS Non_unique "
				. "FROM pragma_index_list('$table') AS il "
				. "JOIN pragma_index_info(il.name) AS ii "
				. "WHERE il.origin = 'c' "
				. "ORDER BY il.name, ii.seqno";
		}

		// SHOW VARIABLES LIKE '...'  -> a synthetic single-row result so callers
		// that read $row['Value'] (e.g. max_allowed_packet lookups) get a value
		// instead of an unhandled syntax error against sqlite.
		if (preg_match('/^SHOW\s+VARIABLES\s+LIKE\s+/is', $trim)) {
			return "SELECT 'max_allowed_packet' AS Variable_name, '4194304' AS Value";
		}
		// UNIX_TIMESTAMP(col) and UNIX_TIMESTAMP() become integer-cast
		// strftime() expressions wherever they appear in the statement, so
		// queries mixing them with other columns still translate faithfully.
		//
		// The CAST is load-bearing rather than tidy: strftime() returns TEXT,
		// and sqlite orders every integer before every string, so an in-SQL
		// comparison of two of these is decided by type and not by value.
		// "UNIX_TIMESTAMP(started) + timeout < UNIX_TIMESTAMP()" then holds
		// whatever the clock says, which reads as a passing test rather than
		// as a broken one. MySQL returns a number here, so the cast is what
		// makes the translation faithful.
		$trim = preg_replace('/UNIX_TIMESTAMP\s*\(\s*\)/i', "CAST(strftime('%s','now') AS INTEGER)", $trim);
		$trim = preg_replace('/UNIX_TIMESTAMP\s*\(([^)]+)\)/i', "CAST(strftime('%s', $1) AS INTEGER)", $trim);

		return $trim;
	}
}