From d73dd37027e5d9b53cd56481c78d43c0942d2d68 Mon Sep 17 00:00:00 2001
From: Alexander Kavon <me+git@alexkavon.com>
Date: Wed, 24 Jan 2024 04:11:59 -0500
Subject: remove .sql.sql file extension for migration files, drop type enum on
 migrate down

---
 migrations/003_create_posts_table.sql     | 18 ++++++++++++++++++
 migrations/003_create_posts_table.sql.sql | 18 ------------------
 migrations/004_create_tags_table.sql      | 24 ++++++++++++++++++++++++
 migrations/004_create_tags_table.sql.sql  | 24 ------------------------
 migrations/005_create_comments_tables.sql | 22 ++++++++++++++++++++++
 5 files changed, 64 insertions(+), 42 deletions(-)
 create mode 100644 migrations/003_create_posts_table.sql
 delete mode 100644 migrations/003_create_posts_table.sql.sql
 create mode 100644 migrations/004_create_tags_table.sql
 delete mode 100644 migrations/004_create_tags_table.sql.sql
 create mode 100644 migrations/005_create_comments_tables.sql

diff --git a/migrations/003_create_posts_table.sql b/migrations/003_create_posts_table.sql
new file mode 100644
index 0000000..584b7b4
--- /dev/null
+++ b/migrations/003_create_posts_table.sql
@@ -0,0 +1,18 @@
+CREATE TABLE posts(
+    id SERIAL NOT NULL PRIMARY KEY,
+    title VARCHAR(100) NOT NULL,
+    description TEXT,
+    url VARCHAR(255) UNIQUE,
+    user_id INT NOT NULL REFERENCES users(id),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TRIGGER set_timestamp
+BEFORE UPDATE ON posts
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_set_timestamp();
+
+---- create above / drop below ----
+
+DROP TABLE posts;
diff --git a/migrations/003_create_posts_table.sql.sql b/migrations/003_create_posts_table.sql.sql
deleted file mode 100644
index 584b7b4..0000000
--- a/migrations/003_create_posts_table.sql.sql
+++ /dev/null
@@ -1,18 +0,0 @@
-CREATE TABLE posts(
-    id SERIAL NOT NULL PRIMARY KEY,
-    title VARCHAR(100) NOT NULL,
-    description TEXT,
-    url VARCHAR(255) UNIQUE,
-    user_id INT NOT NULL REFERENCES users(id),
-    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-);
-
-CREATE TRIGGER set_timestamp
-BEFORE UPDATE ON posts
-FOR EACH ROW
-EXECUTE PROCEDURE trigger_set_timestamp();
-
----- create above / drop below ----
-
-DROP TABLE posts;
diff --git a/migrations/004_create_tags_table.sql b/migrations/004_create_tags_table.sql
new file mode 100644
index 0000000..0b184f8
--- /dev/null
+++ b/migrations/004_create_tags_table.sql
@@ -0,0 +1,24 @@
+CREATE TABLE tags(
+    id SERIAL NOT NULL PRIMARY KEY,
+    tag VARCHAR(30) NOT NULL,
+    description TEXT NOT NULL,
+    user_id INT NOT NULL REFERENCES users(id),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE post_tags(
+    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
+    tag_id  INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
+    PRIMARY KEY (post_id, tag_id)
+);
+
+CREATE TRIGGER set_timestamp
+BEFORE UPDATE ON tags
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_set_timestamp();
+
+---- create above / drop below ----
+
+DROP TABLE post_tags;
+DROP TABLE tags;
diff --git a/migrations/004_create_tags_table.sql.sql b/migrations/004_create_tags_table.sql.sql
deleted file mode 100644
index 0b184f8..0000000
--- a/migrations/004_create_tags_table.sql.sql
+++ /dev/null
@@ -1,24 +0,0 @@
-CREATE TABLE tags(
-    id SERIAL NOT NULL PRIMARY KEY,
-    tag VARCHAR(30) NOT NULL,
-    description TEXT NOT NULL,
-    user_id INT NOT NULL REFERENCES users(id),
-    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-);
-
-CREATE TABLE post_tags(
-    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
-    tag_id  INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
-    PRIMARY KEY (post_id, tag_id)
-);
-
-CREATE TRIGGER set_timestamp
-BEFORE UPDATE ON tags
-FOR EACH ROW
-EXECUTE PROCEDURE trigger_set_timestamp();
-
----- create above / drop below ----
-
-DROP TABLE post_tags;
-DROP TABLE tags;
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;
-- 
cgit v1.2.3