From 62b0b360fa8a1a2d1fd6d89d4d227a0ef559cb8a Mon Sep 17 00:00:00 2001
From: Laria Carolin Chabowski <laria@laria.me>
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
---
 src/Schema.php | 25 ++++++++++++++++++++++++-
 1 file changed, 24 insertions(+), 1 deletion(-)

(limited to 'src/Schema.php')

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)");
+    }
 }
-- 
cgit v1.2.3-54-g00ecf