initial

1 files changed, 337 insertions, 0 deletions

diff --git a/src/lib/database.php b/src/lib/database.php
new file mode 100644
index 0000000..416ef8f
--- /dev/null
+++ b/src/lib/database.php
@@ -0,0 +1,337 @@
+<?php
+/// CRIMSON --- A simple PHP framework.
+/// Copyright © 2024 Freya Murphy <contact@freyacat.org>
+///
+/// This file is part of CRIMSON.
+///
+/// CRIMSON 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 3 of the License, or (at
+/// your option) any later version.
+///
+/// CRIMSON 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 General Public License for more details.
+///
+/// You should have received a copy of the GNU General Public License
+/// along with CRIMSON. If not, see <http://www.gnu.org/licenses/>.
+
+function __nullify(mixed $val): mixed
+{
+	if (!$val) {
+		return NULL;
+	} else {
+		return $val;
+	}
+}
+
+class DatabaseQuery {
+
+	private \PDO $conn;
+	private string $query;
+
+	private bool $where;
+	private bool $set;
+
+	private array $param;
+
+	function __construct(\PDO $conn)
+	{
+		$this->conn = $conn;
+		$this->query = '';
+
+		$this->set = FALSE;
+		$this->where = FALSE;
+		$this->param = array();
+	}
+
+	///
+	/// ARBITRARY QUERY
+	///
+
+	public function query(string $query): DatabaseQuery
+	{
+		$this->query .= $query;
+		return $this;
+	}
+
+	///
+	/// SELECT
+	///
+
+	public function select(string $select): DatabaseQuery
+	{
+		$this->query .= "SELECT $select\n";
+		return $this;
+	}
+
+	public function from(string $from): DatabaseQuery
+	{
+		$this->query .= "FROM $from\n";
+		return $this;
+	}
+
+	///
+	/// INSERT
+	///
+
+	public function insert_into(string $insert, string ...$columns): DatabaseQuery
+	{
+		$this->query .= "INSERT INTO $insert\n (";
+		foreach ($columns as $idx => $column) {
+			if ($idx !== 0) {
+				$this->query .= ",";
+			}
+			$this->query .= $column;
+		}
+		$this->query .= ")\n";
+		return $this;
+	}
+
+	public function values(mixed ...$values): DatabaseQuery
+	{
+		$this->query .= "VALUES (";
+		foreach ($values as $idx => $value) {
+			if ($idx !== 0) {
+				$this->query .= ",";
+			}
+			$this->query .= "?";
+			array_push($this->param, $value);
+		}
+		$this->query .= ")\n";
+		return $this;
+	}
+
+	///
+	/// WHERE
+	///
+
+	public function where(string $cond): DatabaseQuery
+	{
+		if (!$this->where) {
+			$this->where = TRUE;
+			$this->query .= "WHERE ";
+		} else {
+			$this->query .= "AND ";
+		}
+		$this->query .= "$cond ";
+		return $this;
+	}
+
+	/**
+	 * @param array<mixed> $array
+	 */
+	public function where_in(string $column, array $array): DatabaseQuery
+	{
+		if (!$this->where) {
+			$this->where = TRUE;
+			$this->query .= "WHERE ";
+		} else {
+			$this->query .= "AND ";
+		}
+		if (empty($array)) {
+			$this->query .= "FALSE\n";
+			return $this;
+		}
+		$in = $this->in($array);
+		$this->query .= "$column $in\n";
+		return $this;
+	}
+
+	private function in(array $array): string
+	{
+		$in = 'IN (';
+		foreach ($array as $idx => $item) {
+			if ($idx != 0) {
+				$in .= ",";
+			}
+			$in .= "?";
+			array_push($this->param, $item);
+		}
+		$in .= ")";
+		return $in;
+	}
+
+	///
+	/// OPERATORS
+	///
+
+	public function like(mixed $item): DatabaseQuery
+	{
+		$this->query .= "LIKE ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function eq(mixed $item): DatabaseQuery
+	{
+		$this->query .= "= ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function ne(mixed $item): DatabaseQuery
+	{
+		$this->query .= "<> ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function lt(mixed $item): DatabaseQuery
+	{
+		$this->query .= "< ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	public function le(mixed $item): DatabaseQuery
+	{
+		$this->query .= "<= ?\n";
+		array_push($this->param, $item);
+		return $this;
+	}
+
+	///
+	/// JOINS
+	///
+
+	public function join(string $table, string $on, string $type = 'LEFT'): DatabaseQuery
+	{
+		$this->query .= "$type JOIN $table ON $on\n";
+		return $this;
+	}
+
+	///
+	/// LIMIT, OFFSET, ORDER
+	///
+
+	public function limit(int $limit): DatabaseQuery
+	{
+		$this->query .= "LIMIT ?\n";
+		array_push($this->param, $limit);
+		return $this;
+	}
+
+	public function offset(int $offset): DatabaseQuery
+	{
+		$this->query .= "OFFSET ?\n";
+		array_push($this->param, $offset);
+		return $this;
+	}
+
+	public function order_by(string $column, string $order = 'ASC'): DatabaseQuery
+	{
+		$this->query .= "ORDER BY " . $column . ' ' . $order . ' ';
+		return $this;
+	}
+
+	///
+	/// COLLECT
+	///
+
+	public function rows(mixed ...$params): array
+	{
+		$args = $this->param;
+		foreach ($params as $param) {
+			array_push($args, $param);
+		}
+		$stmt = $this->conn->prepare($this->query);
+		try {
+			$stmt->execute($args);
+		} catch (Exception $ex) {
+			echo $ex;
+			echo '<br> >> caused by <<<br>';
+			echo str_replace("\n", "<br>", $this->query);
+		}
+		return __nullify($stmt->fetchAll(PDO::FETCH_ASSOC)) ?? [];
+	}
+
+	public function row(mixed ...$params): ?array
+	{
+		$args = $this->param;
+		foreach ($params as $param) {
+			array_push($args, $param);
+		}
+		$stmt = $this->conn->prepare($this->query);
+		$stmt->execute($args);
+		return __nullify($stmt->fetch(PDO::FETCH_ASSOC));
+	}
+
+	public function execute(mixed ...$params): bool
+	{
+		$args = $this->param;
+		foreach ($params as $param) {
+			array_push($args, $param);
+		}
+		$stmt = $this->conn->prepare($this->query);
+		try {
+			$stmt->execute($args);
+			return TRUE;
+		} catch (Exception $_e) {
+			echo $_e;
+			echo '<br> >> caused by <<<br>';
+			echo str_replace("\n", "<br>", $this->query);
+			return FALSE;
+		}
+	}
+}
+
+/**
+ * DatabaseHelper
+ * allows queries on the
+ * postgres database
+ */
+class DatabaseHelper {
+
+	private ?\PDO $conn;
+
+	function __construct()
+	{
+		$this->conn = NULL;
+	}
+
+	private function connect(): \PDO
+	{
+		if ($this->conn === NULL) {
+			$user = getenv("POSTGRES_USER");
+			$pass = getenv("POSTGRES_PASSWORD");
+			$db   = getenv("POSTGRES_DB");
+			$host = 'postgres';
+			$port = '5432';
+
+			$conn_str = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
+				$host,
+				$port,
+				$db,
+				$user,
+				$pass
+			);
+			$this->conn = new \PDO($conn_str);
+			$this->conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
+		}
+		return $this->conn;
+	}
+
+	public function select(string $select): DatabaseQuery
+	{
+		$conn = $this->connect();
+		$query = new DatabaseQuery($conn);
+		return $query->select($select);
+	}
+
+	public function insert_into(string $insert, string ...$columns): DatabaseQuery
+	{
+		$conn = $this->connect();
+		$query = new DatabaseQuery($conn);
+		return $query->insert_into($insert, ...$columns);
+	}
+
+	public function query(string $query_str): DatabaseQuery
+	{
+		$conn = $this->connect();
+		$query = new DatabaseQuery($conn);
+		return $query->query($query_str);
+	}
+}
+