SQL implementation of content_scope

2 files changed, 36 insertions, 0 deletions

diff --git a/rust/migrations/2021-11-17-222046_content_scope/down.sql b/rust/migrations/2021-11-17-222046_content_scope/down.sql
new file mode 100644
index 00000000..b2d55321
--- /dev/null
+++ b/rust/migrations/2021-11-17-222046_content_scope/down.sql
@@ -0,0 +1,10 @@
+-- This file should undo anything in `up.sql`
+
+ALTER TABLE file_rev
+DROP COLUMN content_scope;
+
+ALTER TABLE fileset_rev
+DROP COLUMN content_scope;
+
+ALTER TABLE webcapture_rev
+DROP COLUMN content_scope;
diff --git a/rust/migrations/2021-11-17-222046_content_scope/up.sql b/rust/migrations/2021-11-17-222046_content_scope/up.sql
new file mode 100644
index 00000000..bedd0870
--- /dev/null
+++ b/rust/migrations/2021-11-17-222046_content_scope/up.sql
@@ -0,0 +1,26 @@
+-- Add `content_scope` field to file, fileset, webcapture
+
+ALTER TABLE file_rev
+ADD COLUMN content_scope       TEXT CHECK (octet_length(content_scope) >= 1);
+
+ALTER TABLE fileset_rev
+ADD COLUMN content_scope       TEXT CHECK (octet_length(content_scope) >= 1);
+
+ALTER TABLE webcapture_rev
+ADD COLUMN content_scope       TEXT CHECK (octet_length(content_scope) >= 1);
+
+-------------------- Update Test Revs --------------------------------------
+-- IMPORTANT: don't create new entities here, only mutate existing
+
+BEGIN;
+
+UPDATE file_rev SET content_scope = 'article'
+WHERE id = '00000000-0000-0000-3333-FFF000000003';
+
+UPDATE fileset_rev SET content_scope = 'dataset'
+WHERE id = '00000000-0000-0000-6666-fff000000003';
+
+UPDATE webcapture_rev SET content_scope = 'webpage'
+WHERE id = '00000000-0000-0000-7777-FFF000000003';
+
+COMMIT;