From 62b0b360fa8a1a2d1fd6d89d4d227a0ef559cb8a Mon Sep 17 00:00:00 2001 From: Laria Carolin Chabowski Date: Mon, 10 Feb 2020 22:36:13 +0100 Subject: 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 --- config.php | 3 +- src/BoundVal.php | 2 +- src/Env.php | 9 + src/Handlers/AttachmentHandler.php | 26 +++ src/Handlers/NewNote.php | 4 + src/Handlers/NoteHandler.php | 7 +- src/Main.php | 3 + src/Models/Attachment.php | 258 ++++++++++++++++++++++++++ src/Models/Note.php | 9 + src/Schema.php | 25 ++- src/TemplateModelWrappers/NoteForTemplate.php | 44 +++++ templates/macros.twig | 16 +- templates/note.twig | 2 +- 13 files changed, 402 insertions(+), 6 deletions(-) create mode 100644 src/Handlers/AttachmentHandler.php create mode 100644 src/Models/Attachment.php create mode 100644 src/TemplateModelWrappers/NoteForTemplate.php diff --git a/config.php b/config.php index 5b7a17f..5207125 100644 --- a/config.php +++ b/config.php @@ -2,5 +2,6 @@ return [ "templates_path" => __DIR__ . "/templates", "templates_cache" => false, //__DIR__ . "/.templates_cache", - "sqlitedb" => __DIR__ . "/notes.db", + "sqlitedb" => __DIR__ . "/notes-dev.db", + "attachments" => __DIR__ . "/attachments", ]; 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 @@ +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 @@ +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 @@ +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 diff --git a/templates/macros.twig b/templates/macros.twig index dc7e721..4e1b336 100644 --- a/templates/macros.twig +++ b/templates/macros.twig @@ -37,12 +37,26 @@ {% endfor %} +
+

Attachments

+ + + +
{% endmacro %} {% macro new_note() %}

New Note

-
+ {{ _self.note_form_content({}) }}
diff --git a/templates/note.twig b/templates/note.twig index 14989aa..cb0b6d8 100644 --- a/templates/note.twig +++ b/templates/note.twig @@ -1,7 +1,7 @@ {% extends "skeleton.twig" %} {% import "macros.twig" as macros %} {% block body %} -
+ {{ macros.note_form_content(note) }}