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

1 files changed, 24 insertions, 1 deletions

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)");
+    }
 }