From ee1d099c6acdb38a173a7455804724f3a1b78836 Mon Sep 17 00:00:00 2001
From: Nathan Perry <avaglir@gmail.com>
Date: Thu, 5 Apr 2018 22:52:02 -0400
Subject: consolidate memes to one table

---
 .../2018-04-06-005800_consolidate_memes/down.sql   | 41 ++++++++++++++++++++++
 1 file changed, 41 insertions(+)
 create mode 100644 migrations/2018-04-06-005800_consolidate_memes/down.sql

(limited to 'migrations/2018-04-06-005800_consolidate_memes/down.sql')

diff --git a/migrations/2018-04-06-005800_consolidate_memes/down.sql b/migrations/2018-04-06-005800_consolidate_memes/down.sql
new file mode 100644
index 0000000..339bf8f
--- /dev/null
+++ b/migrations/2018-04-06-005800_consolidate_memes/down.sql
@@ -0,0 +1,41 @@
+DROP INDEX memes_audio;
+DROP INDEX memes_content;
+DROP INDEX memes_image;
+
+ALTER TABLE memes RENAME TO text_memes;
+
+CREATE TABLE image_memes (
+    id          SERIAL PRIMARY KEY,
+    title       varchar UNIQUE NOT NULL,
+    image_id    INTEGER REFERENCES images NOT NULL,
+
+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,
+    UNIQUE(title, image_id)
+);
+
+
+CREATE TABLE audio_memes (
+    id          SERIAL PRIMARY KEY,
+    title       varchar UNIQUE NOT NULL,
+    audio_id    INTEGER REFERENCES audio NOT NULL,
+
+    metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,
+    UNIQUE(title, audio_id)
+);
+
+
+INSERT INTO audio_memes(title, audio_id, metadata_id) SELECT title, audio_id, metadata_id FROM text_memes
+    WHERE audio_id IS NOT NULL AND content IS NULL;
+
+DELETE FROM text_memes WHERE audio_id IS NOT NULL AND content IS NULL;
+
+
+INSERT INTO image_memes(title, image_id, metadata_id) SELECT title, image_id, metadata_id FROM text_memes
+    WHERE image_id IS NOT NULL AND content IS NULL;
+
+DELETE FROM text_memes WHERE image_id IS NOT NULL AND content IS NULL;
+
+
+ALTER TABLE text_memes ALTER COLUMN content SET NOT NULL;
+ALTER TABLE text_memes DROP CONSTRAINT text_memes_image_or_audio_null;
+ALTER TABLE text_memes DROP CONSTRAINT text_memes_content_not_all_null;
-- 
cgit v1.3.1

