ringfinger/backend/classes/core/MySqlDatabase.php

<?php

declare(strict_types=1);

final class MySqlDatabase implements DatabaseInterface
{
	private const CHARS_ALLOWED_IN_TABLE_NAMES = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890_-';

	private ?PDO $connection;
	private ?PDOStatement $cursor;
	private bool $isTransactionOpen = false;

	public function __construct(
		string $hostname = Setting::MYSQL_HOST,
		string $user = Setting::MYSQL_USER,
		string $password = Setting::MYSQL_PASSWORD,
		string $database = Setting::MYSQL_DATABASE
	) {
		try {
			$this->connection = new PDO("mysql:host=$hostname;dbname=$database", $user, $password);
		} catch (Throwable $e) {
			throw new DatabaseException(
				'Couldn\'t connect to the database!',
				ServerStatus::INTERNAL_ERROR
			);
		}
	}

	public function __destruct()
	{
		$this->connection = null;
	}

	public function Query(string $query, array $params = []): void
	{
		$this->cursor = $this->connection->prepare($query);

		if (!$this->cursor) {
			throw new DatabaseException(
				'Initialization of database cursor failed',
				DatabaseException::CONNECTION_FAILED
			);
		}

		foreach ($params as $key => $param) {
			if (is_bool($param)) {
				$param = (int)$param;
			}

			$this->cursor->bindValue(':' . $key, $param);
		}

		if (!$this->cursor->execute()) {
			throw new DatabaseException($this->cursor->errorInfo()[2], $this->cursor->errorInfo()[1]);
		}
	}

	public function getResult(): array
	{
		$result = [];

		while ($fetch = $this->cursor->fetchObject()) {
			$row = [];

			foreach (get_object_vars($fetch) as $key => $value) {
				$row[$key] = $value;
			}

			$result[] = $row;
		}

		return $result;
	}

	/**
	 * Selects backend from a table.
	 */
	public function Select(
		string $tableName,
		array $fields = [],
		array $conditions = [],
		int $limit = 0,
		array $orderBy = [],
		bool $asc = true,
		int $offset = 0
	): array {
		if (!self::isValidTableName($tableName)) {
			[];
		}

		if (count($fields) === 0) {
			$fieldsExpression = '*';
		} else {
			$fieldsExpression = implode(',', $fields);
		}

		$conditionsExpression = '';
		$conditionPairs = [];

		foreach ($conditions as $field => $value) {
			$conditionPairs[] = sprintf('%s = :%s ', $field, $field);
		}

		if (count($conditions) > 0) {
			$conditionsExpression = 'WHERE ';
			$conditionsExpression .= implode(' AND ', $conditionPairs);
		}

		$orderStatement = '';

		if (count($orderBy) > 0) {
			$orderStatement = 'ORDER BY ' . implode(',', $orderBy);

			if (!$asc) {
				$orderStatement .= ' DESC';
			}
		}

		$limitStatement = '';

		if ($limit > 0) {
			$limitStatement = 'LIMIT ' . $limit;
		}

		$offsetStatement = '';

		if ($offset > 0) {
			$offsetStatement = 'OFFSET ' . $offset;
		}

		$query = sprintf(
			'SELECT %s FROM %s %s %s %s %s',
			$fieldsExpression,
			$tableName,
			$conditionsExpression,
			$orderStatement,
			$limitStatement,
			$offsetStatement
		);

		try {
			$this->Query($query, $conditions);
		} catch (Throwable $e) {
			throw new DatabaseException(
				$e->getMessage(),
				ServerStatus::INTERNAL_ERROR
			);
		}

		return $this->getResult();
	}

	/**
	 * Deletes rows from a table.
	 */
	public function Delete(string $table, array $conditions): void
	{
		if (count($conditions) === 0) {
			$conditionsStatement = '1';
		} else {
			$conditionPairs = [];

			foreach ($conditions as $field => $value) {
				$conditionPairs[] = sprintf('%s=:Condition%s', $field, $field);
				$conditions['Condition' . $field] = $value;
				unset($conditions[$field]);
			}

			$conditionsStatement = implode(' AND ', $conditionPairs);
		}

		$query = sprintf('DELETE FROM %s WHERE %s', $table, $conditionsStatement);

		$this->Query($query, $conditions);
	}

	public function Insert(string $table, array $fields): ?int
	{
		if (count($fields) === 0) {
		   throw new DatabaseException('Row to insert is empty!');
		}

		$fieldNames = implode(',', array_keys($fields));
		$fieldPlaceholder = [];

		foreach ($fields as $name => $value) {
			$fieldPlaceholder[] = ':' . $name;
		}

		$query = sprintf(
			'INSERT INTO %s (%s) VALUES (%s)', $table, $fieldNames, implode(',', $fieldPlaceholder)
		);

		$this->Query($query, $fields);

		$lastInsertedId = $this->GetLastInsertedId();

		if ((int)$lastInsertedId === 0) {
			return null;
		}

		return $lastInsertedId;
	}

	public function Update(string $table, array $fields, array $conditions): void
	{
		$conditionPairs = [];

		foreach ($conditions as $field => $value) {
			$conditionPairs[] = sprintf('%s=:Condition%s', $field, $field);
			$conditions['Condition' . $field] = $value;
			unset($conditions[$field]);
		}

		$conditionsStatement = implode(' AND ', $conditionPairs);

		$fieldPairs = [];

		foreach ($fields as $field => $value) {
			$fieldPairs[] = sprintf('%s=:%s', $field, $field);
		}

		$fieldsStatement = implode(',', $fieldPairs);

		$query = sprintf('UPDATE %s SET %s WHERE %s', $table, $fieldsStatement, $conditionsStatement);


		$this->Query($query, array_merge($fields, $conditions));
	}

	public function Count(string $table, array $conditions = []): int
	{
		$result = $this->Select($table, ['count(*)'], $conditions);

		return (int)$result[0]['count(*)'];
	}

	public function hasTransaction(): bool
	{
		return $this->isTransactionOpen;
	}

	public function startTransaction(): void
	{
		$this->connection->beginTransaction();
		$this->isTransactionOpen = true;
	}

	public function rollback(): void
	{
		$this->connection->rollBack();
		$this->isTransactionOpen = false;
	}

	public function commit(): void
	{
		$this->connection->commit();
		$this->isTransactionOpen = false;
	}

	public function GetLastInsertedId(): int
	{
		$this->Query('SELECT LAST_INSERT_ID() as ID');

		return (int)$this->getResult()[0]['ID'];
	}

	/**
	 * Does a check if the given table name contains forbidden chars.
	 */
	private static function isValidTableName(string $tableName): bool
	{
		foreach (str_split($tableName) as $char) {
			if (substr_count(self::CHARS_ALLOWED_IN_TABLE_NAMES, $char) === 0) {
				return false;
			}
		}

		return true;
	}
}