diff options
Diffstat (limited to '')
| -rw-r--r-- | src/web/helpers/database.php | 323 |
1 files changed, 0 insertions, 323 deletions
diff --git a/src/web/helpers/database.php b/src/web/helpers/database.php deleted file mode 100644 index 82c711d..0000000 --- a/src/web/helpers/database.php +++ /dev/null @@ -1,323 +0,0 @@ -<?php /* Copyright (c) 2024 Freya Murphy */ - -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; - } - - /** - * @param array<mixed> $array - */ - private function in(array $array): DatabaseQuery - { - $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 = 'db'; - $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); - } -} - |