From d6bea61fa917d257219a43386c88a4f04cf82408 Mon Sep 17 00:00:00 2001 From: Nathan Perry Date: Fri, 6 Apr 2018 19:22:55 -0400 Subject: database alteration in-flight --- migrations/2018-04-06-223934_add_hash_to_data/down.sql | 11 +++++++++++ migrations/2018-04-06-223934_add_hash_to_data/up.sql | 17 +++++++++++++++++ 2 files changed, 28 insertions(+) create mode 100644 migrations/2018-04-06-223934_add_hash_to_data/down.sql 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') 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; -- cgit v1.3.1