remove .sql.sql file extension for migration files, drop type enum on migrate down

1 files changed, 22 insertions, 0 deletions

diff --git a/migrations/005_create_comments_tables.sql b/migrations/005_create_comments_tables.sql
new file mode 100644
index 0000000..3717c0c
--- /dev/null
+++ b/migrations/005_create_comments_tables.sql
@@ -0,0 +1,22 @@
+CREATE TYPE comment_state AS ENUM ('hidden', 'visible');
+
+CREATE TABLE comments(
+    id SERIAL NOT NULL PRIMARY KEY,
+    comment TEXT NOT NULL,
+    user_id INT NOT NULL REFERENCES users(id),
+    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
+    reply_id INT REFERENCES comments(id) ON DELETE CASCADE,
+    state comment_state DEFAULT 'visible',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TRIGGER set_timestamp
+BEFORE UPDATE ON comments
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_set_timestamp();
+
+---- create above / drop below ----
+
+DROP TABLE comments;
+DROP TYPE comment_state;