9 files changed, 714 insertions, 0 deletions
diff --git a/services/app/src/Sql/DateTimeParser.php b/services/app/src/Sql/DateTimeParser.php
new file mode 100644
index 0000000..eb3b58a
--- /dev/null
+++ b/services/app/src/Sql/DateTimeParser.php
@@ -0,0 +1,31 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql;
+
+use DateTimeImmutable;
+use DateTimeZone;
+
+final class DateTimeParser
+{
+    private const FORMATS = [
+        'Y-m-d H:i:s.u',
+        'Y-m-d H:i:s',
+    ];
+
+    public static function parse(string $s): DateTimeImmutable|false
+    {
+        foreach (self::FORMATS as $format) {
+            $dt = DateTimeImmutable::createFromFormat(
+                $format,
+                $s,
+                new DateTimeZone('UTC'),
+            );
+            if ($dt !== false) {
+                return $dt;
+            }
+        }
+        return false;
+    }
+}
diff --git a/services/app/src/Sql/Internal/Delete.php b/services/app/src/Sql/Internal/Delete.php
new file mode 100644
index 0000000..c0761a2
--- /dev/null
+++ b/services/app/src/Sql/Internal/Delete.php
@@ -0,0 +1,51 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+use Nsfisis\Albatross\Sql\QueryBuilder;
+
+final class Delete
+{
+    private string $where = '';
+
+    /**
+     * @internal
+     */
+    public function __construct(
+        private readonly QueryBuilder $sql,
+        private readonly string $table,
+    ) {
+    }
+
+    public function where(string $where): self
+    {
+        $this->where = $where;
+        return $this;
+    }
+
+    /**
+     * @param array<string, string|int> $params
+     */
+    public function execute(array $params = []): void
+    {
+        $this->sql->_executeDelete($this, $params);
+    }
+
+    /**
+     * @internal
+     */
+    public function _getTable(): string
+    {
+        return $this->table;
+    }
+
+    /**
+     * @internal
+     */
+    public function _getWhere(): string
+    {
+        return $this->where;
+    }
+}
diff --git a/services/app/src/Sql/Internal/Insert.php b/services/app/src/Sql/Internal/Insert.php
new file mode 100644
index 0000000..1bdd06f
--- /dev/null
+++ b/services/app/src/Sql/Internal/Insert.php
@@ -0,0 +1,62 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+use Nsfisis\Albatross\Exceptions\InvalidSqlException;
+use Nsfisis\Albatross\Sql\QueryBuilder;
+
+final class Insert
+{
+    /**
+     * @var ?array<string, string|int|Select> $values
+     */
+    private ?array $values;
+
+    /**
+     * @internal
+     */
+    public function __construct(
+        private readonly QueryBuilder $sql,
+        private readonly string $table,
+    ) {
+    }
+
+    /**
+     * @param array<string, string|int|Select> $values
+     */
+    public function values(array $values): self
+    {
+        $this->values = $values;
+        return $this;
+    }
+
+    /**
+     * @return positive-int
+     */
+    public function execute(): int
+    {
+        return $this->sql->_executeInsert($this);
+    }
+
+    /**
+     * @internal
+     */
+    public function _getTable(): string
+    {
+        return $this->table;
+    }
+
+    /**
+     * @internal
+     * @return array<string, string|int|Select>
+     */
+    public function _getValues(): array
+    {
+        if (!isset($this->values)) {
+            throw new InvalidSqlException('INSERT: $values must be set before calling execute()');
+        }
+        return $this->values;
+    }
+}
diff --git a/services/app/src/Sql/Internal/InsertFromSelect.php b/services/app/src/Sql/Internal/InsertFromSelect.php
new file mode 100644
index 0000000..c003aa4
--- /dev/null
+++ b/services/app/src/Sql/Internal/InsertFromSelect.php
@@ -0,0 +1,78 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+use Nsfisis\Albatross\Exceptions\InvalidSqlException;
+use Nsfisis\Albatross\Sql\QueryBuilder;
+
+final class InsertFromSelect
+{
+    /**
+     * @var ?list<string>
+     */
+    private ?array $fields;
+
+    private ?Select $from;
+
+    /**
+     * @internal
+     */
+    public function __construct(
+        private readonly QueryBuilder $sql,
+        private readonly string $table,
+    ) {
+    }
+
+    /**
+     * @param list<string> $fields
+     */
+    public function fields(array $fields): self
+    {
+        $this->fields = $fields;
+        return $this;
+    }
+
+    public function from(Select $from): self
+    {
+        $this->from = $from;
+        return $this;
+    }
+
+    /**
+     * @param array<string, string|int> $params
+     */
+    public function execute(array $params = []): void
+    {
+        $this->sql->_executeInsertFromSelect($this, $params);
+    }
+
+    /**
+     * @internal
+     */
+    public function _getTable(): string
+    {
+        return $this->table;
+    }
+
+    /**
+     * @internal
+     * @return list<string>
+     */
+    public function _getFields(): array
+    {
+        if (!isset($this->fields)) {
+            throw new InvalidSqlException('INSERT SELECT: $fields must be set before calling execute()');
+        }
+        return $this->fields;
+    }
+
+    public function _getFrom(): Select
+    {
+        if (!isset($this->from)) {
+            throw new InvalidSqlException('INSERT SELECT: $from must be set before calling execute()');
+        }
+        return $this->from;
+    }
+}
diff --git a/services/app/src/Sql/Internal/Join.php b/services/app/src/Sql/Internal/Join.php
new file mode 100644
index 0000000..4c85fd8
--- /dev/null
+++ b/services/app/src/Sql/Internal/Join.php
@@ -0,0 +1,18 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+final class Join
+{
+    /**
+     * @param 'LEFT JOIN' $type
+     */
+    public function __construct(
+        public readonly string $type,
+        public readonly string $table,
+        public readonly string $on,
+    ) {
+    }
+}
diff --git a/services/app/src/Sql/Internal/Select.php b/services/app/src/Sql/Internal/Select.php
new file mode 100644
index 0000000..cf3f77a
--- /dev/null
+++ b/services/app/src/Sql/Internal/Select.php
@@ -0,0 +1,146 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+use Nsfisis\Albatross\Exceptions\InvalidSqlException;
+use Nsfisis\Albatross\Sql\QueryBuilder;
+
+/**
+ * @internal
+ */
+final class Select
+{
+    /**
+     * @var ?list<string>
+     */
+    private ?array $fields;
+
+    private ?Join $join = null;
+
+    private string $where = '';
+
+    /**
+     * @var list<array{string, string}>
+     */
+    private array $orderBy = [];
+
+    /**
+     * @var ?positive-int
+     */
+    private ?int $limit = null;
+
+    public function __construct(
+        private readonly QueryBuilder $sql,
+        private readonly string $table,
+    ) {
+    }
+
+    public function leftJoin(string $table, string $on): self
+    {
+        $this->join = new Join('LEFT JOIN', $table, $on);
+        return $this;
+    }
+
+    /**
+     * @param list<string> $fields
+     */
+    public function fields(array $fields): self
+    {
+        $this->fields = $fields;
+        return $this;
+    }
+
+    public function where(string $where): self
+    {
+        $this->where = $where;
+        return $this;
+    }
+
+    /**
+     * @param list<array{string, string}> $orderBy
+     */
+    public function orderBy(array $orderBy): self
+    {
+        $this->orderBy = $orderBy;
+        return $this;
+    }
+
+    /**
+     * @param positive-int $limit
+     */
+    public function limit(int $limit): self
+    {
+        $this->limit = $limit;
+        return $this;
+    }
+
+    public function first(): SelectFirst
+    {
+        $this->limit = 1;
+        return new SelectFirst($this);
+    }
+
+    /**
+     * @param array<string, string|int> $params
+     * @return list<array<string, string>>
+     */
+    public function execute(array $params = []): array
+    {
+        return $this->sql->_executeSelect($this, $params);
+    }
+
+    /**
+     * @internal
+     */
+    public function _getTable(): string
+    {
+        return $this->table;
+    }
+
+    /**
+     * @internal
+     * @return list<string>
+     */
+    public function _getFields(): array
+    {
+        if (!isset($this->fields)) {
+            throw new InvalidSqlException('SELECT: $fields must be set before calling execute()');
+        }
+        return $this->fields;
+    }
+
+    /**
+     * @internal
+     */
+    public function _getJoin(): ?Join
+    {
+        return $this->join;
+    }
+
+    /**
+     * @internal
+     */
+    public function _getWhere(): string
+    {
+        return $this->where;
+    }
+
+    /**
+     * @internal
+     * @return list<array{string, string}>
+     */
+    public function _getOrderBy(): array
+    {
+        return $this->orderBy;
+    }
+
+    /**
+     * @return ?positive-int
+     */
+    public function _getLimit(): ?int
+    {
+        return $this->limit;
+    }
+}
diff --git a/services/app/src/Sql/Internal/SelectFirst.php b/services/app/src/Sql/Internal/SelectFirst.php
new file mode 100644
index 0000000..baf5aae
--- /dev/null
+++ b/services/app/src/Sql/Internal/SelectFirst.php
@@ -0,0 +1,26 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+/**
+ * @internal
+ */
+final class SelectFirst
+{
+    public function __construct(
+        private readonly Select $inner,
+    ) {
+    }
+
+    /**
+     * @param array<string, string|int> $params
+     * @return ?array<string, string>
+     */
+    public function execute(array $params = []): ?array
+    {
+        $result = $this->inner->execute($params);
+        return $result[0] ?? null;
+    }
+}
diff --git a/services/app/src/Sql/Internal/Update.php b/services/app/src/Sql/Internal/Update.php
new file mode 100644
index 0000000..a9e9816
--- /dev/null
+++ b/services/app/src/Sql/Internal/Update.php
@@ -0,0 +1,78 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql\Internal;
+
+use Nsfisis\Albatross\Exceptions\InvalidSqlException;
+use Nsfisis\Albatross\Sql\QueryBuilder;
+
+final class Update
+{
+    /**
+     * @var ?array<string, string|int>
+     */
+    private ?array $set;
+
+    private string $where = '';
+
+    /**
+     * @internal
+     */
+    public function __construct(
+        private readonly QueryBuilder $sql,
+        private readonly string $table,
+    ) {
+    }
+
+    /**
+     * @param array<string, string|int> $set
+     */
+    public function set(array $set): self
+    {
+        $this->set = $set;
+        return $this;
+    }
+
+    public function where(string $where): self
+    {
+        $this->where = $where;
+        return $this;
+    }
+
+    /**
+     * @param array<string, string|int> $params
+     */
+    public function execute(array $params = []): void
+    {
+        $this->sql->_executeUpdate($this, $params);
+    }
+
+    /**
+     * @internal
+     */
+    public function _getTable(): string
+    {
+        return $this->table;
+    }
+
+    /**
+     * @internal
+     */
+    public function _getWhere(): string
+    {
+        return $this->where;
+    }
+
+    /**
+     * @internal
+     * @return array<string, string|int>
+     */
+    public function _getSet(): array
+    {
+        if (!isset($this->set)) {
+            throw new InvalidSqlException('UPDATE: $set must be set before calling execute()');
+        }
+        return $this->set;
+    }
+}
diff --git a/services/app/src/Sql/QueryBuilder.php b/services/app/src/Sql/QueryBuilder.php
new file mode 100644
index 0000000..3a5443b
--- /dev/null
+++ b/services/app/src/Sql/QueryBuilder.php
@@ -0,0 +1,224 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Sql;
+
+use Nsfisis\Albatross\Sql\Internal\Delete;
+use Nsfisis\Albatross\Sql\Internal\Insert;
+use Nsfisis\Albatross\Sql\Internal\InsertFromSelect;
+use Nsfisis\Albatross\Sql\Internal\Select;
+use Nsfisis\Albatross\Sql\Internal\Update;
+use PDO;
+use PDOStatement;
+
+final class QueryBuilder
+{
+    /**
+     * @var array<string, PDOStatement>
+     */
+    private array $stmtCache = [];
+
+    /**
+     * @internal
+     */
+    public function __construct(
+        private readonly PDO $conn,
+    ) {
+    }
+
+    public function select(string $table): Select
+    {
+        return new Select($this, $table);
+    }
+
+    public function insert(string $table): Insert
+    {
+        return new Insert($this, $table);
+    }
+
+    public function insertFromSelect(string $table): InsertFromSelect
+    {
+        return new InsertFromSelect($this, $table);
+    }
+
+    public function update(string $table): Update
+    {
+        return new Update($this, $table);
+    }
+
+    public function delete(string $table): Delete
+    {
+        return new Delete($this, $table);
+    }
+
+    public function schema(string $sql): void
+    {
+        $this->conn->exec($sql);
+    }
+
+    /**
+     * @internal
+     * @param Select $select
+     * @param array<string, string|int> $params
+     * @return list<array<string, string>>
+     */
+    public function _executeSelect(Select $select, array $params): array
+    {
+        $stmt = $this->loadCacheOrPrepare($this->compileSelect($select));
+        $ok = $stmt->execute($params);
+        assert($ok);
+        /** @var list<array<string, string>> */
+        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+        return $rows;
+    }
+
+    /**
+     * @param Select $select
+     */
+    private function compileSelect(Select $select): string
+    {
+        $table = $select->_getTable();
+        $join = $select->_getJoin();
+        $fields = $select->_getFields();
+        $where = $select->_getWhere();
+        $orderBy = $select->_getOrderBy();
+        $limit = $select->_getLimit();
+
+        return "SELECT " .
+            implode(', ', $fields) .
+            " FROM $table" .
+            ($join !== null ? " $join->type $join->table ON $join->on" : '') .
+            ($where !== '' ? " WHERE $where" : '') .
+            (
+                0 < count($orderBy)
+                ? " ORDER BY " . implode(', ', array_map(fn ($field_and_order) => "{$field_and_order[0]} {$field_and_order[1]}", $orderBy))
+                : ''
+            ) .
+            ($limit !== null ? " LIMIT $limit" : '');
+    }
+
+    /**
+     * @internal
+     * @return positive-int
+     */
+    public function _executeInsert(Insert $insert): int
+    {
+        $stmt = $this->loadCacheOrPrepare($this->compileInsert($insert));
+        $ok = $stmt->execute(array_filter($insert->_getValues(), fn ($v) => !$v instanceof Select));
+        assert($ok);
+        return $this->lastInsertId();
+    }
+
+    private function compileInsert(Insert $insert): string
+    {
+        $table = $insert->_getTable();
+        $values = $insert->_getValues();
+        $columns = array_keys($values);
+
+        if (count($columns) === 0) {
+            return "INSERT INTO $table DEFAULT VALUES";
+        }
+
+        return "INSERT INTO $table (" .
+            implode(', ', $columns) .
+            ') VALUES (' .
+            implode(
+                ', ',
+                array_map(
+                    fn ($c) => (
+                        $values[$c] instanceof Select
+                            ? '(' . $this->compileSelect($values[$c]) . ')'
+                            : ":$c"
+                    ),
+                    $columns,
+                ),
+            ) .
+            ')';
+    }
+
+    /**
+     * @internal
+     * @param array<string, string|int> $params
+     */
+    public function _executeInsertFromSelect(InsertFromSelect $insert, array $params): void
+    {
+        $stmt = $this->loadCacheOrPrepare($this->compileInsertFromSelect($insert));
+        $ok = $stmt->execute($params);
+        assert($ok);
+    }
+
+    private function compileInsertFromSelect(InsertFromSelect $insert): string
+    {
+        $table = $insert->_getTable();
+        $fields = $insert->_getFields();
+        $from = $insert->_getFrom();
+
+        return "INSERT INTO $table (" .
+            implode(', ', $fields) .
+            ') ' .
+            $this->compileSelect($from);
+    }
+
+    /**
+     * @internal
+     * @param array<string, string|int> $params
+     */
+    public function _executeUpdate(Update $update, array $params): void
+    {
+        $stmt = $this->loadCacheOrPrepare($this->compileUpdate($update));
+        $ok = $stmt->execute($params + $update->_getSet());
+        assert($ok);
+    }
+
+    private function compileUpdate(Update $update): string
+    {
+        $table = $update->_getTable();
+        $set = $update->_getSet();
+        $columns = array_keys($set);
+        $where = $update->_getWhere();
+
+        return "UPDATE $table SET " .
+            implode(', ', array_map(fn ($c) => "$c = :$c", $columns)) .
+            ($where !== '' ? " WHERE $where" : '');
+    }
+
+    /**
+     * @internal
+     * @param array<string, string|int> $params
+     */
+    public function _executeDelete(Delete $delete, array $params): void
+    {
+        $stmt = $this->loadCacheOrPrepare($this->compileDelete($delete));
+        $ok = $stmt->execute($params);
+        assert($ok);
+    }
+
+    private function compileDelete(Delete $delete): string
+    {
+        $table = $delete->_getTable();
+        $where = $delete->_getWhere();
+
+        return "DELETE FROM $table" .
+            ($where !== '' ? " WHERE $where" : '');
+    }
+
+    private function loadCacheOrPrepare(string $sql): PDOStatement
+    {
+        $cache = $this->stmtCache[$sql] ?? null;
+        if ($cache !== null) {
+            return $cache;
+        }
+        return $this->stmtCache[$sql] = $this->conn->prepare($sql);
+    }
+
+    /**
+     * @return positive-int
+     */
+    private function lastInsertId(): int
+    {
+        $inserted_id = (int) $this->conn->lastInsertId();
+        assert(0 < $inserted_id);
+        return $inserted_id;
+    }
+}