diff options
Diffstat (limited to 'src/web/helpers/database.php')
| -rw-r--r-- | src/web/helpers/database.php | 282 |
1 files changed, 282 insertions, 0 deletions
diff --git a/src/web/helpers/database.php b/src/web/helpers/database.php new file mode 100644 index 0000000..25cb5ba --- /dev/null +++ b/src/web/helpers/database.php @@ -0,0 +1,282 @@ +<?php /* Copyright (c) 2024 Freya Murphy */ + +class DatabaseQuery { + + private $conn; + private $query; + + private $where; + private $set; + + private $param; + + function __construct($conn) { + $this->conn = $conn; + $this->query = ''; + + $this->set = FALSE; + $this->where = FALSE; + $this->param = array(); + } + + /// + /// ARBITRARY QUERY + /// + + public function query($query) { + $this->query .= $query; + return $this; + } + + /// + /// SELECT + /// + + public function select($select) { + $this->query .= "SELECT $select\n"; + return $this; + } + + public function from($from) { + $this->query .= "FROM $from\n"; + return $this; + } + + /// + /// INSERT + /// + + public function insert_into($insert, ...$columns) { + $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(...$values) { + $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($cond) { + if (!$this->where) { + $this->where = TRUE; + $this->query .= "WHERE "; + } else { + $this->query .= "AND "; + } + $this->query .= "$cond "; + return $this; + } + + public function where_in($column, $array) { + 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) { + $in = 'IN ('; + foreach ($array as $idx => $item) { + if ($idx != 0) { + $in .= ","; + } + $in .= "?"; + array_push($this->param, $item); + } + $in .= ")"; + return $in; + } + + /// + /// OPERATORS + /// + + public function like($item) { + $this->query .= "LIKE ?\n"; + array_push($this->param, $item); + return $this; + } + + public function eq($item) { + $this->query .= "= ?\n"; + array_push($this->param, $item); + return $this; + } + + public function ne($item) { + $this->query .= "<> ?\n"; + array_push($this->param, $item); + return $this; + } + + public function lt($item) { + $this->query .= "< ?\n"; + array_push($this->param, $item); + return $this; + } + + public function le($item) { + $this->query .= "<= ?\n"; + array_push($this->param, $item); + return $this; + } + + /// + /// JOINS + /// + + public function join($table, $on, $type = 'LEFT') { + $this->query .= "$type JOIN $table ON $on\n"; + return $this; + } + + /// + /// LIMIT, OFFSET, ORDER + /// + + public function limit($limit) { + $this->query .= "LIMIT ?\n"; + array_push($this->param, $limit); + return $this; + } + + public function offset($offset) { + $this->query .= "OFFSET ?\n"; + array_push($this->param, $offset); + return $this; + } + + public function order_by($column, $order = 'ASC') { + $this->query .= "ORDER BY " . $column . ' ' . $order . ' '; + return $this; + } + + /// + /// COLLECT + /// + + public function rows(...$params) { + $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 $stmt->fetchAll(PDO::FETCH_ASSOC); + } + + public function row(...$params) { + $args = $this->param; + foreach ($params as $param) { + array_push($args, $param); + } + $stmt = $this->conn->prepare($this->query); + $stmt->execute($args); + return $stmt->fetch(PDO::FETCH_ASSOC); + } + + public function execute(...$params) { + $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 $conn; + + function __construct() { + $this->conn = NULL; + } + + private function connect() { + 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($select) { + $conn = $this->connect(); + $query = new DatabaseQuery($conn); + return $query->select($select); + } + + public function insert_into($insert, ...$columns) { + $conn = $this->connect(); + $query = new DatabaseQuery($conn); + return $query->insert_into($insert, ...$columns); + } + + public function query($query_str) { + $conn = $this->connect(); + $query = new DatabaseQuery($conn); + return $query->query($query_str); + } +} + |