From 60eb2e7fe5ed3627ad4bd40286df1741529da75b Mon Sep 17 00:00:00 2001 From: Alexander Kavon Date: Thu, 25 Jan 2024 02:47:26 -0500 Subject: update hooks to more identifiable names, update migrations to work with votes, use a common enum state type --- migrations/003_create_posts_table.sql | 6 +++++- migrations/005_create_comments_tables.sql | 5 +---- migrations/006_create_votes_table.sql | 2 +- 3 files changed, 7 insertions(+), 6 deletions(-) (limited to 'migrations') diff --git a/migrations/003_create_posts_table.sql b/migrations/003_create_posts_table.sql index 584b7b4..b8324d3 100644 --- a/migrations/003_create_posts_table.sql +++ b/migrations/003_create_posts_table.sql @@ -1,9 +1,12 @@ +CREATE TYPE postable_state AS ENUM ('hidden', 'visible'); + CREATE TABLE posts( id SERIAL NOT NULL PRIMARY KEY, - title VARCHAR(100) NOT NULL, + title VARCHAR(80) NOT NULL, description TEXT, url VARCHAR(255) UNIQUE, user_id INT NOT NULL REFERENCES users(id), + state postable_state DEFAULT 'visible', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); @@ -16,3 +19,4 @@ EXECUTE PROCEDURE trigger_set_timestamp(); ---- create above / drop below ---- DROP TABLE posts; +DROP TYPE postable_state; diff --git a/migrations/005_create_comments_tables.sql b/migrations/005_create_comments_tables.sql index 3717c0c..8abf274 100644 --- a/migrations/005_create_comments_tables.sql +++ b/migrations/005_create_comments_tables.sql @@ -1,12 +1,10 @@ -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', + state postable_state DEFAULT 'visible', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); @@ -19,4 +17,3 @@ EXECUTE PROCEDURE trigger_set_timestamp(); ---- create above / drop below ---- DROP TABLE comments; -DROP TYPE comment_state; diff --git a/migrations/006_create_votes_table.sql b/migrations/006_create_votes_table.sql index 8ed4c5f..ac73918 100644 --- a/migrations/006_create_votes_table.sql +++ b/migrations/006_create_votes_table.sql @@ -6,7 +6,7 @@ CREATE TABLE votes ( inc INT NOT NULL CHECK (inc in (-1, 1)), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), - CONSTRAINT vote_pkey UNIQUE NULLS NOT DISTINCT (post_id, comment_id, user_id), + CONSTRAINT post_comment_user_pkey UNIQUE NULLS NOT DISTINCT (post_id, comment_id, user_id), CHECK ((post_id IS NOT NULL AND comment_id IS NULL) OR (comment_id IS NOT NULL AND post_id IS NULL)) ); -- cgit v1.2.3