From d6bea61fa917d257219a43386c88a4f04cf82408 Mon Sep 17 00:00:00 2001
From: Nathan Perry <avaglir@gmail.com>
Date: Fri, 6 Apr 2018 19:22:55 -0400
Subject: database alteration in-flight

---
 migrations/2018-04-06-223934_add_hash_to_data/up.sql | 17 +++++++++++++++++
 1 file changed, 17 insertions(+)
 create mode 100644 migrations/2018-04-06-223934_add_hash_to_data/up.sql

(limited to 'migrations/2018-04-06-223934_add_hash_to_data/up.sql')

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;
-- 
cgit v1.3.1

