diff options
| author | Nathan Perry <avaglir@gmail.com> | 2018-04-04 22:09:13 -0400 |
|---|---|---|
| committer | Nathan Perry <avaglir@gmail.com> | 2018-04-04 22:09:13 -0400 |
| commit | bf6745af21f82562af0b85de566f4e7b7ef5df8c (patch) | |
| tree | a7b70656c84b7a9cf1d71a67921b5d171308234b /migrations | |
| parent | 180ef480c0037cfcb5735fdfd2c60f910bf5ba5a (diff) | |
revamp database structure
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/2018-02-15-031841_create_memes/down.sql | 19 | ||||
| -rw-r--r-- | migrations/2018-02-15-031841_create_memes/up.sql | 71 |
2 files changed, 82 insertions, 8 deletions
diff --git a/migrations/2018-02-15-031841_create_memes/down.sql b/migrations/2018-02-15-031841_create_memes/down.sql index 8de6d95..91d88ab 100644 --- a/migrations/2018-02-15-031841_create_memes/down.sql +++ b/migrations/2018-02-15-031841_create_memes/down.sql @@ -1,3 +1,18 @@ -DROP TABLE text_memes;
-DROP TABLE image_memes;
DROP TABLE audio_memes;
+DROP TABLE image_memes;
+DROP TABLE text_memes;
+
+DROP TABLE audio;
+DROP TABLE images;
+
+DROP INDEX audit_updated;
+DROP INDEX audit_updated_by;
+DROP INDEX audit_metadata;
+DROP INDEX audit_metadata_updated_by;
+
+DROP TABLE audit_records;
+
+DROP INDEX metadata_created;
+DROP INDEX metadata_created_by;
+
+DROP TABLE metadata;
diff --git a/migrations/2018-02-15-031841_create_memes/up.sql b/migrations/2018-02-15-031841_create_memes/up.sql index 38097e7..dc24710 100644 --- a/migrations/2018-02-15-031841_create_memes/up.sql +++ b/migrations/2018-02-15-031841_create_memes/up.sql @@ -1,14 +1,73 @@ +CREATE TABLE metadata (
+ id SERIAL PRIMARY KEY,
+
+ created TIMESTAMP NOT NULL DEFAULT current_timestamp,
+ created_by BIGINT NOT NULL
+);
+
+CREATE INDEX metadata_created on metadata (created);
+CREATE INDEX metadata_created_by on metadata (created_by);
+
+
+CREATE TABLE audit_records (
+ id SERIAL PRIMARY KEY,
+
+ updated TIMESTAMP NOT NULL DEFAULT current_timestamp,
+ updated_by BIGINT NOT NULL,
+
+ metadata_id INTEGER REFERENCES metadata NOT NULL
+);
+
+CREATE INDEX audit_updated on audit_records (updated);
+CREATE INDEX audit_updated_by on audit_records (updated_by);
+CREATE INDEX audit_metadata on audit_records (metadata_id);
+
+CREATE INDEX audit_metadata_updated_by on audit_records (metadata_id, updated_by);
+
+
+CREATE TABLE images (
+ id SERIAL PRIMARY KEY,
+ data bytea NOT NULL,
+
+ metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL
+);
+
+
+CREATE TABLE audio (
+ id SERIAL PRIMARY KEY,
+ data bytea NOT NULL,
+
+ metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL
+);
+
+
CREATE TABLE text_memes (
id SERIAL PRIMARY KEY,
title varchar UNIQUE NOT NULL,
content TEXT NOT NULL,
- pic_related varchar NULL,
- UNIQUE(content, pic_related)
+ image_id INTEGER REFERENCES images NULL,
+ audio_id INTEGER REFERENCES audio NULL,
+
+ metadata_id INTEGER REFERENCES metadata UNIQUE NOT NULL,
+ UNIQUE(content, image_id, audio_id)
);
-CREATE TABLE audio_memes (
- id SERIAL PRIMARY KEY,
- title varchar UNIQUE NOT NULL,
- link varchar UNIQUE NOT NULL
+
+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)
+);
|