Skip to content

armgit / Vvveb

Public Imported from GitHub
0

Powerful and easy to use cms to build websites, blogs or ecommerce stores.

12.9 KB
Raw Blame
<?php

/**
 * Vvveb
 *
 * Copyright (C) 2022  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\System\Db;

use Vvveb\System\Event;

class Mysqli extends DBDriver {
	/** @var ?\MySQLi The shared database connection (singleton per process). */
	private static ?\mysqli $link = null;

	/** @var int Number of rows affected by the last query. */
	public int $affected_rows = 0;

	/** @var ?int The auto-increment ID generated by the last INSERT. */
	public ?int $insert_id = null;

	/** @var string Identifier quote character for MySQL. */
	public string $quote  = '`';

	/** @var string Table name prefix. */
	public string $prefix = ''; //'vv_';

	/**
	 * Get the MySQL server version as an integer.
	 *
	 * @return int|null The server version (e.g. 80031) or null if not connected.
	 */
	public static function version(): ?int {
		if (self :: $link) {
			return mysqli_get_server_version(self :: $link);
		}

		return null;
	}

	/**
	 * Get the MySQL server version string.
	 *
	 * @return string|null The version string (e.g. '8.0.31') or null if not connected.
	 */
	public static function info(): ?string {
		if (self :: $link) {
			return mysqli_get_server_info(self :: $link);
		}

		return null;
	}

	/**
	 * Get the error message from the last MySQL operation.
	 *
	 * @return string|null The error message, or null if not connected.
	 */
	public function error(): ?string {
		if (self :: $link) {
			return self :: $link->error;
		}

		return null;
	}

	/**
	 * Get the error code from the last MySQL operation.
	 *
	 * @return int|null The error code, or null if not connected.
	 */
	public function errorCode(): ?int {
		if (self :: $link) {
			return self :: $link->errno;
		}

		return null;
	}

	public function __construct() {}

	/**
	 * Connect to the MySQL database.
	 *
	 * Uses a singleton pattern — returns the existing connection if already open.
	 * Throws an exception with a sanitised error message on failure.
	 *
	 * @param string   $host   Database host.
	 * @param string   $dbname Database name.
	 * @param string   $user   Database user.
	 * @param string   $pass   Database password.
	 * @param int|null $port   Database port.
	 * @param string   $prefix Table name prefix.
	 *
	 * @throws \Exception On connection failure.
	 *
	 * @return ?\MySQLi The database connection, or null on failure.
	 */
	public function connect(string $host = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?int $port = DB_PORT, string $prefix = DB_PREFIX): ?\mysqli {
		//connect to database
		if (self :: $link) {
			return self :: $link;
		}
		$this->prefix = $prefix;

		\mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

		try {
			self :: $link = new \mysqli($host, $user, $pass, $dbname, $port);
		} catch (\mysqli_sql_exception $e) {
			$errorMessage = str_replace($pass, '*****', $e->getMessage());

			throw new \Exception($errorMessage, $e->getCode());
		}

		// check if a connection established
		if (\mysqli_connect_errno()) {
			$error        = mysqli_connect_error();
			$errorMessage = str_replace($pass, '*****', $error);

			throw new \Exception($errorMessage, mysqli_connect_errno());
		}

		//sync database time
		//$this->execute('SET `time_zone` = :zone', ['zone' => $this->escape(date('P'))]);

		return self :: $link;
	}

	/**
	 * Get all columns for a table used for sanitizing input.
	 *
	 * @param string $tableName The table name to introspect.
	 * @param bool   $comment   Whether to include the column comment field.
	 *
	 * @return array<string, array{name: string, d: mixed, n: string, t: string, e: string, l: int|null, c?: string}>|false
	 *                                                                                                                      Associative array keyed by column name, or false on failure.
	 */
	public function getColumnsMeta(string $tableName, bool $comment = false): array|false {
		$sql =
		'SELECT COLUMN_NAME as name, COLUMN_DEFAULT as d, IS_NULLABLE  as n, DATA_TYPE as t, EXTRA as e, CHARACTER_MAXIMUM_LENGTH as l'
		. ($comment ? ', COLUMN_COMMENT as c' : '') .
		' FROM `INFORMATION_SCHEMA`.`COLUMNS`
		WHERE `TABLE_SCHEMA`= "' . DB_NAME . '"
			AND `TABLE_NAME`="' . $tableName . '"';

		if ($result = $this->query($sql)) {
			//$columns = $result->fetch_all(MYSQLI_ASSOC);
			$columns = [];

			while ($row = $result->fetch_assoc()) {
				$columns[$row['name']] = $row;
			}

			// free result set
			$result->close();

			return $columns;
		}

		return false;
	}

	/**
	 * Get all table names in the database.
	 *
	 * @param string $db The database name.
	 *
	 * @return array<int, string>|false List of table names, or false on failure.
	 */
	public function getTableNames(string $db = DB_NAME): array|false {
		$sql = "SELECT table_name as name
			FROM information_schema.tables
			WHERE table_schema = '$db' ORDER BY table_name";

		if ($result = $this->query($sql)) {
			//$names = $result->fetch_all(MYSQLI_ASSOC);
			$names = [];

			while ($row = $result->fetch_assoc()) {
				$names[] = $row['name'];
			}

			// free result set
			$result->close();

			return $names;
		}

		return false;
	}

	/**
	 * Select a different database on the current connection.
	 *
	 * @param string $db The database name.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function select_db(string $db): bool {
		return self :: $link->select_db($db);
	}

	/**
	 * Execute a raw SQL query.
	 *
	 * Automatically connects if not connected. Updates affected_rows and insert_id.
	 *
	 * @param string $sql The SQL query to execute.
	 *
	 * @throws \Exception On query failure.
	 *
	 * @return bool|\mysqli_result Query result or true for success, false on failure.
	 */
	public function query(string $sql): bool|\mysqli_result {
		if (!self :: $link) {
			$this->connect();
		}

		if (LOG_SQL_QUERIES) {
			error_log($sql);
		}

		try {
			$result = self :: $link->query($sql);
		} catch (\mysqli_sql_exception $e) {
			$message = $e->getMessage() . "\n" . $sql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

			throw new \Exception($message, $e->getCode());
		}

		if ($result) {
			$this->affected_rows = self :: $link->affected_rows;
			$this->insert_id     = self :: $link->insert_id;
		}

		return $result;
	}

	/**
	 * Execute multiple SQL statements in a single call.
	 *
	 * @param string $sql The SQL statements to execute (semicolon-separated).
	 *
	 * @return bool True on success, false on failure.
	 */
	public function multi_query(string $sql): bool {
		if (!self :: $link) {
			$this->connect();
		}

		$result = self :: $link->multi_query($sql);

		if ($result) {
			$this->affected_rows = self :: $link->affected_rows;
			$this->insert_id     = self :: $link->insert_id;
		}

		return $result;
	}

	/**
	 * Escape a value for safe inclusion in a SQL query.
	 *
	 * Strings are escaped with real_escape_string. Null values become 'null'.
	 * All other types are returned as-is.
	 *
	 * @param mixed $string The value to escape.
	 *
	 * @return mixed The escaped value.
	 */
	public function escape(mixed $string): mixed {
		if (is_string($string)) {
			return self :: $link->real_escape_string($string);
		}

		if (is_null($string)) {
			return 'null';
		}

		return $string;
	}

	/**
	 * Escape a string value for use in a SQL literal.
	 *
	 * @param string $string The string to escape.
	 *
	 * @return string The escaped string.
	 */
	public function escapeLiteral(string $string): string {
		return $this->escape($string);
	}

	/**
	 * Generate a MySQL-compatible LIMIT clause.
	 *
	 * @param int|string $start Offset (0-based).
	 * @param int|string $limit Maximum number of rows.
	 *
	 * @return string The LIMIT clause (e.g. 'LIMIT 0, 10').
	 */
	public function sqlLimit(int|string $start, int|string $limit): string {
		return "LIMIT $start, $limit";
	}

	/**
	 * Fetch a single row from a statement or result set.
	 *
	 * @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
	 *
	 * @return array<string, mixed> The fetched row, or empty array if no rows.
	 */
	public function fetchArray(\mysqli_stmt|\mysqli_result $stmt): array {
		$data      = [];
		$statement = ($stmt instanceof \mysqli_stmt);

		if ($statement) {
			$this->store_result();
			$result = $stmt->get_result();
		} else {
			$result = $stmt;
		}

		if ($result) {
			$result = $result->fetch_array(MYSQLI_ASSOC);

			if ($result) {
				$data = $result;

				if ($statement && $stmt->more_results()) {
					$stmt->next_result();
				}
			}
		}

		return $data;
	}

	/**
	 * Fetch all rows from a statement or result set.
	 *
	 * @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetchAll(\mysqli_stmt|\mysqli_result $stmt): array {
		$data      = [];
		$statement = ($stmt instanceof \mysqli_stmt);

		if ($statement) {
			$this->store_result();
			$result = $stmt->get_result();
		} else {
			$result = $stmt;
		}

		if ($result) {
			$data = $result->fetch_all(MYSQLI_ASSOC);
		}

		return $data;
	}

	/**
	 * Store the result of the last query in the client-side buffer.
	 *
	 * @return \mysqli_result|false The stored result, or false on failure.
	 */
	public function store_result(): \mysqli_result|false {
		return self :: $link->store_result();
	}

	/**
	 * Check if the current statement has more result sets.
	 *
	 * @return bool True if more results are available.
	 */
	public function more_results(): bool {
		return self :: $link->more_results();
	}

	/**
	 * Move to the next result set in a multi-query.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function next_result(): bool {
		return self :: $link->next_result();
	}

	/**
	 * Close the database connection.
	 *
	 * @return ?bool True on success, null if not connected.
	 */
	public function close(): ?bool {
		if ((self :: $link instanceof \mysqli)/* && self :: $link->ping()*/) {
			return self :: $link->close();
		}

		return null;
	}

	/**
	 * Prepare and execute a parameterized query.
	 *
	 * Fires an Event::trigger hook before execution. Converts named parameters
	 * to positional placeholders, binds parameters, and executes the statement.
	 * Updates affected_rows and insert_id on success.
	 *
	 * @param string                $sql        The SQL query with :named parameters.
	 * @param array<string, mixed>  $params     Named parameter values.
	 * @param array<string, string> $paramTypes Optional explicit type overrides.
	 *
	 * @throws \Exception On prepare or execute failure.
	 *
	 * @return ?\mysqli_stmt The executed statement, or null on failure.
	 */
	public function execute(string $sql, array $params = [], array $paramTypes = []): ?\mysqli_stmt {
		[$sql, $params] = Event::trigger(__CLASS__, __FUNCTION__, $sql, $params);
		//save orig sql for debugging info
		$origSql = $sql;

		if (!self :: $link) {
			$this->connect();
		}

		[$parameters, $types] = $this->paramsToQmark($sql, $params, $paramTypes);

		try {
			$stmt = self::$link->prepare($sql);
		} catch (\mysqli_sql_exception $e) {
			$message = $e->getMessage() . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n - " . $origSql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

			throw new \Exception($message, $e->getCode());
		}

		if ($stmt && !empty($types)) {
			array_unshift($parameters, $types);

			//hack for php 7.x bind_param "expected to be a reference, value given" stupid warning
			$referenceArray = [];

			foreach ($parameters as $key => $value) {
				$referenceArray[$key] = &$parameters[$key];
			}

			@call_user_func_array([$stmt, 'bind_param'], $referenceArray);
		}

		if (LOG_SQL_QUERIES) {
			error_log($this->debugSql($origSql, $params, $paramTypes));
		}

		if ($stmt) {
			try {
				if ($stmt->execute()) {
					$this->affected_rows = self :: $link->affected_rows;
					$this->insert_id     = self :: $link->insert_id;

					return $stmt;
				} else {
					error_log(print_r($stmt, true));
					error_log($this->debugSql($sql, $params, $paramTypes));
				}
			} catch (\mysqli_sql_exception $e) {
				$message = $e->getMessage() . "\n" . $origSql . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n" . print_r($parameters, true) . $types . "\n" . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

				throw new \Exception($message, $e->getCode());
			}
		} else {
			error_log(print_r($stmt, true));
			error_log($this->debugSql($origSql, $params, $paramTypes));
		}

		return $stmt;
	}
}