database alteration in-flight

2 files changed, 28 insertions, 0 deletions

diff --git a/migrations/2018-04-06-223934_add_hash_to_data/down.sql b/migrations/2018-04-06-223934_add_hash_to_data/down.sql
new file mode 100644
index 0000000..faf2150
--- /dev/null
+++ b/migrations/2018-04-06-223934_add_hash_to_data/down.sql
@@ -0,0 +1,11 @@
+CREATE UNIQUE INDEX audio_data on audio     (digest(data, 'sha1'));
+CREATE UNIQUE INDEX image_data on images    (digest(data, 'sha1'));
+
+DROP INDEX audio_hash;
+DROP INDEX image_hash;
+
+ALTER TABLE audio   DROP CONSTRAINT audio_hash_valid;
+ALTER TABLE images  DROP CONSTRAINT image_hash_valid;
+
+ALTER TABLE audio DROP COLUMN data_hash;
+ALTER TABLE images DROP COLUMN data_hash;
diff --git a/migrations/2018-04-06-223934_add_hash_to_data/up.sql b/migrations/2018-04-06-223934_add_hash_to_data/up.sql
new file mode 100644
index 0000000..c132009
--- /dev/null
+++ b/migrations/2018-04-06-223934_add_hash_to_data/up.sql
@@ -0,0 +1,17 @@
+ALTER TABLE audio   ADD COLUMN data_hash bytea;
+ALTER TABLE images  ADD COLUMN data_hash bytea;
+
+UPDATE audio    SET data_hash = digest(data, 'sha1');
+UPDATE images   SET data_hash = digest(data, 'sha1');
+
+ALTER TABLE audio   ADD CONSTRAINT audio_hash_valid CHECK (data_hash = digest(data, 'sha1'));
+ALTER TABLE images  ADD CONSTRAINT image_hash_valid CHECK (data_hash = digest(data, 'sha1'));
+
+ALTER TABLE audio   ALTER COLUMN data_hash SET NOT NULL;
+ALTER TABLE images  ALTER COLUMN data_hash SET NOT NULL;
+
+CREATE UNIQUE INDEX audio_hash on audio     (data_hash);
+CREATE UNIQUE INDEX image_hash on images    (data_hash);
+
+DROP INDEX audio_data;
+DROP INDEX image_data;