feat(users/Profpatsch/cas-serve): init r/4304

A dumb little daemon that stores arbitrary files by content-hash, and
exposes a randomly generated URL by which the file can be fetched
again.

If the same file is uploaded twice, it will only be stored once.
CAS hashes are not exposed to the user, so they can’t figure out
whether a file they know is in the database.

Change-Id: Ie57bc09d429a9f31c8f0fc5f63f78d6a84d650f7
Reviewed-on: https://cl.tvl.fyi/c/depot/+/5952
Tested-by: BuildkiteCI
Reviewed-by: Profpatsch <mail@profpatsch.de>

1 files changed, 38 insertions, 0 deletions

diff --git a/users/Profpatsch/cas-serve/schema.sql b/users/Profpatsch/cas-serve/schema.sql
new file mode 100644
index 000000000000..b61a7a1ad57d
--- /dev/null
+++ b/users/Profpatsch/cas-serve/schema.sql
@@ -0,0 +1,38 @@
+-- SQLite
+.dump
+
+PRAGMA foreign_keys = ON;
+
+BEGIN transaction;
+
+create table if not exists file_content (
+  content blob NOT NULL,
+  hash_sha256 blob PRIMARY KEY,
+  size integer NOT NULL
+) WITHOUT ROWID;
+
+
+create table if not exists file_references (
+  rowid integer PRIMARY KEY,
+  file_content NOT NULL REFERENCES file_content ON DELETE CASCADE,
+  reference_type text NOT NULL,
+  name text NOT NULL,
+  extension text NOT NULL,
+  mimetype text NOT NULL
+);
+
+create unique index if not exists file_references_type_name_unique on file_references (reference_type, name);
+
+-- insert into file_content values ('mycontent', 'myhash', 9);
+-- insert into file_references values (NULL, 'myhash', 'by-id', 'myschranz', '.txt', 'text/plain');
+-- insert into file_content values (readfile('/home/philip/Pictures/screenshot.png'), 'anotherhash', 999);
+-- insert into file_references values (NULL, 'anotherhash', 'by-id', 'img', '.png', 'image/png');
+
+select * from file_content;
+
+select * from file_references;
+
+COMMIT;
+
+-- drop table file_content;
+-- drop table file_references;