From 8510c36ded85740885e67b59ee2ec2360986c0a9 Mon Sep 17 00:00:00 2001 From: Alexander Kavon Date: Tue, 23 Jan 2024 01:40:39 -0500 Subject: new tables: tags, post_tags, generated sqlboiler tags model, updated posts/get template, updated posts table to take nullable description and user_id foreign key --- migrations/003_create_posts_table.sql.sql | 3 ++- migrations/004_create_tags_table.sql.sql | 24 ++++++++++++++++++++++++ 2 files changed, 26 insertions(+), 1 deletion(-) create mode 100644 migrations/004_create_tags_table.sql.sql (limited to 'migrations') diff --git a/migrations/003_create_posts_table.sql.sql b/migrations/003_create_posts_table.sql.sql index 81dab8f..584b7b4 100644 --- a/migrations/003_create_posts_table.sql.sql +++ b/migrations/003_create_posts_table.sql.sql @@ -1,8 +1,9 @@ CREATE TABLE posts( id SERIAL NOT NULL PRIMARY KEY, title VARCHAR(100) NOT NULL, - description TEXT 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() ); diff --git a/migrations/004_create_tags_table.sql.sql b/migrations/004_create_tags_table.sql.sql new file mode 100644 index 0000000..0b184f8 --- /dev/null +++ b/migrations/004_create_tags_table.sql.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; -- cgit v1.2.3