Implement simple attachment support

It is now possible to upload and view attachments!

Attachments are saved by their content hash, therefore they are
automatically deduplicated and we can later easily add integrity checks.

Still missing:
- Deleting attachments
- Multiple file inputs (idea: when the user fills in a file input, create a
  new empty file input beneath with js)
- (nice to have) Thumbnails

10 files changed, 384 insertions, 3 deletions

diff --git a/src/BoundVal.php b/src/BoundVal.php
index 7a3560a..892396e 100644
--- a/src/BoundVal.php
+++ b/src/BoundVal.php
@@ -24,7 +24,7 @@ class BoundVal
     public static function ofFloat($val): self { return new self($val, SQLITE3_FLOAT); }
     public static function ofText($val): self { return new self($val, SQLITE3_TEXT); }
     public static function ofBlob($val): self { return new self($val, SQLITE3_BLOB); }
-    public static function ofNull($val): self { return new self($val, SQLITE3_NULL); }
+    public static function ofNull(): self { return new self(null, SQLITE3_NULL); }
 
     public function bind(SQLite3Stmt $stmt, $where): void
     {
diff --git a/src/Env.php b/src/Env.php
index 34b9f1f..38d9279 100644
--- a/src/Env.php
+++ b/src/Env.php
@@ -2,6 +2,7 @@
 
 namespace Micropoly;
 
+use RuntimeException;
 use SQLite3;
 use Twig\Environment;
 use Twig\Loader\FilesystemLoader;
@@ -77,4 +78,12 @@ class Env
             return $db;
         });
     }
+
+    public function attachmentsPath(): string
+    {
+        $attachments = $this->config['attachments'];
+        if (!is_dir($attachments) || !is_writable($attachments))
+            throw new RuntimeException("Attachment directory '$attachments' is not a writable directory.");
+        return $attachments;
+    }
 }
diff --git a/src/Handlers/AttachmentHandler.php b/src/Handlers/AttachmentHandler.php
new file mode 100644
index 0000000..9b329b0
--- /dev/null
+++ b/src/Handlers/AttachmentHandler.php
@@ -0,0 +1,26 @@
+<?php
+
+
+namespace Micropoly\Handlers;
+
+
+use Micropoly\Env;
+use Micropoly\Handler;
+use Micropoly\Models\Attachment;
+
+class AttachmentHandler implements Handler
+{
+    public function handle(\Micropoly\Env $env, array $variables)
+    {
+        $db = $env->db();
+
+        $attachment = Attachment::byId($db, $variables["id"]);
+        if ($attachment === null) {
+            (new NotFoundHandler())->handle($env, []);
+            return;
+        }
+
+        header("Content-Type: {$attachment->getMime()}");
+        readfile($attachment->getFilePath($env->attachmentsPath()));
+    }
+}
\ No newline at end of file
diff --git a/src/Handlers/NewNote.php b/src/Handlers/NewNote.php
index 9c60757..9d4286d 100644
--- a/src/Handlers/NewNote.php
+++ b/src/Handlers/NewNote.php
@@ -7,6 +7,7 @@ namespace Micropoly\Handlers;
 use Micropoly\Env;
 use Micropoly\Esc;
 use Micropoly\Handler;
+use Micropoly\Models\Attachment;
 use Micropoly\Models\Note;
 
 class NewNote implements Handler
@@ -29,6 +30,9 @@ class NewNote implements Handler
             $note->setTags($_POST["tag"]);
             $note->save($env->db());
 
+            if (isset($_FILES['attachments']))
+                Attachment::createFromUploads($env->db(), $env->attachmentsPath(), $note, $_FILES['attachments']);
+
             $url = $env->documentRoot() . "n/" . $note->getId();
             http_response_code(303);
             header("Location: {$url}");
diff --git a/src/Handlers/NoteHandler.php b/src/Handlers/NoteHandler.php
index afdabb5..aa1cf78 100644
--- a/src/Handlers/NoteHandler.php
+++ b/src/Handlers/NoteHandler.php
@@ -6,7 +6,9 @@ namespace Micropoly\Handlers;
 
 use Micropoly\Env;
 use Micropoly\Handler;
+use Micropoly\Models\Attachment;
 use Micropoly\Models\Note;
+use Micropoly\TemplateModelWrappers\NoteForTemplate;
 
 class NoteHandler implements Handler
 {
@@ -32,8 +34,11 @@ class NoteHandler implements Handler
             $note->setContent($_POST["content"]);
             $note->setTags($_POST["tag"]);
             $note->save($db);
+
+            if (isset($_FILES['attachments']))
+                Attachment::createFromUploads($env->db(), $env->attachmentsPath(), $note, $_FILES['attachments']);
         }
 
-        echo $env->twig()->render("/note.twig", ["note" => $note]);
+        echo $env->twig()->render("/note.twig", ["note" => new NoteForTemplate($db, $note)]);
     }
 }
\ No newline at end of file
diff --git a/src/Main.php b/src/Main.php
index 443960b..c82a334 100644
--- a/src/Main.php
+++ b/src/Main.php
@@ -6,6 +6,7 @@ use Closure;
 use FastRoute\Dispatcher;
 use FastRoute\RouteCollector;
 use Micropoly\Handlers\ApiTagsHandler;
+use Micropoly\Handlers\AttachmentHandler;
 use Micropoly\Handlers\Index;
 use Micropoly\Handlers\MethodNotAllowedHandler;
 use Micropoly\Handlers\NewNote;
@@ -13,6 +14,7 @@ use Micropoly\Handlers\NoteHandler;
 use Micropoly\Handlers\NotFoundHandler;
 
 use Micropoly\Handlers\Search;
+use Micropoly\Models\Attachment;
 use function FastRoute\simpleDispatcher;
 
 class Main implements Entrypoint
@@ -24,6 +26,7 @@ class Main implements Entrypoint
         $r->addRoute(["GET"], "/search", Search::class);
         $r->addRoute(["GET", "POST"], "/n/{id}", NoteHandler::class);
         $r->addRoute(["GET"], "/api/tags", ApiTagsHandler::class);
+        $r->addRoute(["GET"], "/attachments/{id}", AttachmentHandler::class);
     }
 
     public function run(Env $env)
diff --git a/src/Models/Attachment.php b/src/Models/Attachment.php
new file mode 100644
index 0000000..bbbd0ee
--- /dev/null
+++ b/src/Models/Attachment.php
@@ -0,0 +1,258 @@
+<?php
+
+
+namespace Micropoly\Models;
+
+
+use InvalidArgumentException;
+use Micropoly\BoundVal;
+use Micropoly\DbQuery;
+use RuntimeException;
+use SQLite3;
+
+class Attachment
+{
+    public const HASH_ALGO = "sha3-256";
+
+    private const HASH_PREFIX_LEN = 2;
+
+    private string $id;
+    private string $noteId;
+    private string $hash;
+    private ?string $fileName;
+    private string $mime;
+
+    private function __construct() {}
+
+    /**
+     * @param string $hash
+     * @return string[]
+     */
+    private static function splitHash(string $hash): array
+    {
+        if (strlen($hash) <= self::HASH_PREFIX_LEN+1) { // +1 so $tail won't be empty
+            throw new InvalidArgumentException("Invalid hash '$hash' given");
+        }
+
+        $head = substr($hash, 0, self::HASH_PREFIX_LEN);
+        $tail = substr($hash, self::HASH_PREFIX_LEN);
+        return [$head, $tail];
+    }
+
+    private static function relativeFilePathFromHash(string $hash): string
+    {
+        [$head, $tail] = self::splitHash($hash);
+
+        return $head . DIRECTORY_SEPARATOR . $tail;
+    }
+
+    /**
+     * @param string $attachmentPath
+     * @param string $hash
+     * @return string
+     */
+    private static function fullFilePathFromHash(string $attachmentPath, string $hash): string
+    {
+        return $attachmentPath . DIRECTORY_SEPARATOR . self::relativeFilePathFromHash($hash);
+    }
+
+    private static function deleteFileByHash(string $attachmentPath, string $hash): void
+    {
+        @unlink(self::fullFilePathFromHash($attachmentPath, $hash));
+    }
+
+    public function clearAbandoned(SQLite3 $db, string $attachmentPath): void
+    {
+        $db->exec("BEGIN");
+        foreach ((new DbQuery("
+            SELECT a.hash
+            FROM attachments a
+            LEFT JOIN note_attachments na
+                ON na.hash = a.hash
+            WHERE na.id IS NULL
+        "))->fetchRows($db) as $hash) {
+            self::deleteFileByHash($attachmentPath, $hash);
+        }
+
+        $db->exec("
+            DELETE FROM attachments
+            WHERE hash NOT IN (
+                SELECT hash
+                FROM note_attachments
+            )
+        ");
+        $db->exec("COMMIT");
+    }
+
+    private static function fromRow(array $row): self
+    {
+        $out = new self();
+
+        $out->id = $row["id"];
+        $out->noteId = $row["note_id"];
+        $out->hash = $row["hash"];
+        $out->fileName = $row["file_name"];
+        $out->mime = $row["mime"];
+
+        return $out;
+    }
+
+    /**
+     * @param SQLite3 $db
+     * @param DbQuery $query
+     * @return self[] Indexed by id
+     */
+    private static function byQuery(SQLite3 $db, DbQuery $query): array
+    {
+        return array_map([self::class, "fromRow"], $query->fetchIndexedRows($db, "id"));
+    }
+
+    /**
+     * @param SQLite3 $db
+     * @param string[] $ids
+     * @return self[] Indexed by id
+     */
+    public static function byIds(SQLite3 $db, array $ids): array
+    {
+        $ids = array_map("trim", $ids);
+        $ids = array_filter($ids);
+
+        if (empty($ids))
+            return [];
+
+        return self::byQuery(
+            $db,
+            (new DbQuery("
+                SELECT id, note_id, hash, file_name, mime
+                FROM note_attachments
+                WHERE id IN (" . DbQuery::valueListPlaceholders($ids) . ")
+            "))
+                ->bindMultiText($ids)
+        );
+    }
+
+    public static function byId(SQLite3 $db, string $id): ?self
+    {
+        return self::byIds($db, [$id])[$id] ?? null;
+    }
+
+    /**
+     * @param SQLite3 $db
+     * @param string $noteId
+     * @return self[] Indexed by id
+     */
+    public static function byNoteId(SQLite3 $db, string $noteId): array
+    {
+        return self::byQuery(
+            $db,
+            (new DbQuery("
+                SELECT id, note_id, hash, file_name, mime
+                FROM note_attachments
+                WHERE note_id = ?
+            "))
+                ->bind(1, BoundVal::ofText($noteId))
+        );
+    }
+
+    private static function transposeUploadsArray(array $uploads): array
+    {
+        $out = [];
+        foreach ($uploads as $key => $values) {
+            if (!is_array($values))
+                $values = [$values];
+
+            foreach ($values as $i => $v)
+                $out[$i][$key] = $v;
+        }
+
+        return $out;
+    }
+
+    private static function hasHash(SQLite3 $db, string $hash): bool
+    {
+        return (new DbQuery("SELECT COUNT(*) FROM attachments WHERE hash = ?"))
+                ->bind(1, BoundVal::ofText($hash))
+                ->fetchRow($db)[0] > 0;
+    }
+
+    private static function mkUploadDir(string $attachmentPath, string $hash): void
+    {
+        [$head] = self::splitHash($hash);
+        $dir = $attachmentPath . DIRECTORY_SEPARATOR . $head;
+
+        if (!is_dir($dir))
+            if (!mkdir($dir))
+                throw new RuntimeException("Failed creating upload dir '$dir'");
+    }
+
+    /**
+     * @param SQLite3 $db
+     * @param string $attachmentPath
+     * @param Note $note
+     * @param array $uploads a $_FILES[$name] like array.
+     *                       Can be populated by multiple files, like
+     *                       {@see https://www.php.net/manual/en/features.file-upload.multiple.php} describes it.
+     * @return self[]
+     */
+    public static function createFromUploads(SQLite3 $db, string $attachmentPath, Note $note, array $uploads): array
+    {
+        $out = [];
+        $inserts = [];
+
+        foreach (self::transposeUploadsArray($uploads) as $upload) {
+            $hash = hash_file(self::HASH_ALGO, $upload["tmp_name"]);
+            if (self::hasHash($db, $hash)) {
+                unlink($upload["tmp_name"]);
+            } else {
+                self::mkUploadDir($attachmentPath, $hash);
+                if (!move_uploaded_file($upload["tmp_name"], self::fullFilePathFromHash($attachmentPath, $hash))) {
+                    throw new RuntimeException("Failed uploading file '{$upload["tmp_name"]}', original name was: '{$upload["name"]}'");
+                }
+                DbQuery::insertKV($db, "attachments", ["hash" => BoundVal::ofText($hash)]);
+            }
+
+            $obj = new self();
+
+            $obj->id = uniqid("", true);
+            $obj->noteId = $note->getId();
+            $obj->hash = $hash;
+            $obj->fileName = $upload["name"] ?? null;
+            $obj->mime = (string)($upload["type"] ?? "application/octet-stream");
+
+            $out[] = $obj;
+            $inserts[] = $obj->buildInsertValues();
+        }
+
+        DbQuery::insert($db, "note_attachments", ["id", "note_id", "hash", "file_name", "mime"], $inserts);
+
+        return $out;
+    }
+
+    private function buildInsertValues()
+    {
+        return [
+            BoundVal::ofText($this->id),
+            BoundVal::ofText($this->noteId),
+            BoundVal::ofText($this->hash),
+            $this->fileName === null ? BoundVal::ofNull() : BoundVal::ofText($this->fileName),
+            BoundVal::ofText($this->mime),
+        ];
+    }
+
+    public function delete(SQLite3 $db, string $attachmentPath): void
+    {
+        (new DbQuery("DELETE FROM note_attachments WHERE id = ?"))->bind(1, BoundVal::ofText($this->id))->exec($db);
+        self::clearAbandoned($db, $attachmentPath);
+    }
+
+    public function getId(): string { return $this->id; }
+    public function getNoteId(): string { return $this->noteId; }
+    public function getHash(): string { return $this->hash; }
+    public function getFileName(): ?string { return $this->fileName; }
+    public function getMime(): string { return $this->mime; }
+
+    public function getFilePath(string $attachmentPath): string
+    {
+        return self::fullFilePathFromHash($attachmentPath, $this->hash);
+    }
+}
\ No newline at end of file
diff --git a/src/Models/Note.php b/src/Models/Note.php
index 901f5aa..594bc79 100644
--- a/src/Models/Note.php
+++ b/src/Models/Note.php
@@ -247,4 +247,13 @@ class Note
             ->exec($db);
         $this->savedToDb = false;
     }
+
+    /**
+     * @param SQLite3 $db
+     * @return Attachment[]
+     */
+    public function getAttachments(SQLite3 $db): array
+    {
+        return Attachment::byNoteId($db, $this->id);
+    }
 }
\ No newline at end of file
diff --git a/src/Schema.php b/src/Schema.php
index bbb47de..25dff5d 100644
--- a/src/Schema.php
+++ b/src/Schema.php
@@ -36,7 +36,10 @@ class Schema
         switch ($version) {
             case 0:
                 $this->v1();
-                $this->setSchemaVersion(1);
+                // fallthrough
+            case 1:
+                $this->v2();
+                $this->setSchemaVersion(2);
         }
     }
 
@@ -77,4 +80,24 @@ class Schema
                 GROUP BY tag
         ");
     }
+
+    private function v2()
+    {
+        $this->db->exec("
+            CREATE TABLE attachments (hash TEXT NOT NULL PRIMARY KEY) WITHOUT ROWID
+        ");
+
+        $this->db->exec("
+            CREATE TABLE note_attachments (
+                id VARCHAR(23) NOT NULL PRIMARY KEY,
+                note_id VARCHAR(23) NOT NULL REFERENCES notes (id) ON UPDATE CASCADE ON DELETE CASCADE,
+                hash TEXT NOT NULL REFERENCES attachments (hash) ON UPDATE CASCADE ON DELETE CASCADE,
+                file_name TEXT NULL DEFAULT NULL,
+                mime TEXT NOT NULL DEFAULT 'application/octet-stream'
+            ) WITHOUT ROWID;
+        ");
+
+        $this->db->exec("CREATE INDEX note_id ON note_attachments (note_id)");
+        $this->db->exec("CREATE INDEX hash ON note_attachments (hash)");
+    }
 }
diff --git a/src/TemplateModelWrappers/NoteForTemplate.php b/src/TemplateModelWrappers/NoteForTemplate.php
new file mode 100644
index 0000000..baafe21
--- /dev/null
+++ b/src/TemplateModelWrappers/NoteForTemplate.php
@@ -0,0 +1,44 @@
+<?php
+
+
+namespace Micropoly\TemplateModelWrappers;
+
+
+use Micropoly\Models\Note;
+use SQLite3;
+
+class NoteForTemplate
+{
+    private SQLite3 $db;
+    private Note $note;
+
+    /**
+     * NoteForModel constructor.
+     * @param SQLite3 $db
+     * @param Note $note
+     */
+    public function __construct(SQLite3 $db, Note $note)
+    {
+        $this->db = $db;
+        $this->note = $note;
+    }
+
+    /**
+     * @param SQLite3 $db
+     * @param Note[] $notes
+     * @return self[]
+     */
+    public static function wrapMany(SQLite3 $db, array $notes): array
+    {
+        return array_map(static fn(Note $note) => new self($db, $note), $notes);
+    }
+
+    public function getId(): string { return $this->note->getId(); }
+    public function getContent(): string { return $this->note->getContent(); }
+    public function getTags(): array { return $this->note->getTags(); }
+
+    public function getAttachments(): array
+    {
+        return $this->note->getAttachments($this->db);
+    }
+}
\ No newline at end of file