From de9cae795f93d03e68d965c59af4b21d96df4ec7 Mon Sep 17 00:00:00 2001 From: Freya Murphy Date: Mon, 23 Dec 2024 10:39:16 -0500 Subject: initial --- src/lib/database.php | 337 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 337 insertions(+) create mode 100644 src/lib/database.php (limited to 'src/lib/database.php') 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 @@ + +/// +/// 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 . + +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 $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 '
>> caused by <<
'; + echo str_replace("\n", "
", $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 '
>> caused by <<
'; + echo str_replace("\n", "
", $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); + } +} + -- cgit v1.2.3-freya