SCRIPTS.md

Инициализация схемы БД

psql postgresql://root:root@localhost:5432/blog

CREATE TABLE posts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   created_by VARCHAR(255),
   created_date TIMESTAMP WITHOUT TIME ZONE,
   last_modified_by VARCHAR(255),
   last_modified_date TIMESTAMP WITHOUT TIME ZONE,
   title VARCHAR(255) NOT NULL,
   text OID NOT NULL,
   published_at TIMESTAMP WITHOUT TIME ZONE,
   author_id BIGINT NOT NULL,
   CONSTRAINT pk_posts PRIMARY KEY (id)
);

CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   created_by VARCHAR(255),
   created_date TIMESTAMP WITHOUT TIME ZONE,
   last_modified_by VARCHAR(255),
   last_modified_date TIMESTAMP WITHOUT TIME ZONE,
   first_name VARCHAR(255) NOT NULL,
   last_name VARCHAR(255) NOT NULL,
   CONSTRAINT pk_users PRIMARY KEY (id)
);

CREATE INDEX idx_user_names ON users(first_name, last_name);

ALTER TABLE posts ADD CONSTRAINT FK_POSTS_ON_AUTHOR FOREIGN KEY (author_id) REFERENCES users (id);

\q

Изменение схемы БД

psql postgresql://root:root@localhost:5432/blog

ALTER TABLE users
    ADD email VARCHAR(255);

ALTER TABLE users
    ADD last_activity TIMESTAMP WITHOUT TIME ZONE;

CREATE TABLE comments
(
    id                 BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    created_by         VARCHAR(255),
    created_date       TIMESTAMP WITHOUT TIME ZONE,
    last_modified_by   VARCHAR(255),
    last_modified_date TIMESTAMP WITHOUT TIME ZONE,
    author_id          BIGINT                                  NOT NULL,
    text               VARCHAR(255),
    CONSTRAINT pk_comments PRIMARY KEY (id)
);

CREATE TABLE comments_posts
(
    comments_id BIGINT NOT NULL,
    posts_id   BIGINT NOT NULL,
    CONSTRAINT pk_comments_posts PRIMARY KEY (comments_id, posts_id)
);

ALTER TABLE comments
    ADD CONSTRAINT FK_COMMENT_ON_AUTHOR FOREIGN KEY (author_id) REFERENCES users (id);

ALTER TABLE comments_posts
    ADD CONSTRAINT fk_compos_on_comments FOREIGN KEY (comments_id) REFERENCES comments (id);

ALTER TABLE comments_posts
    ADD CONSTRAINT fk_compos_on_post FOREIGN KEY (posts_id) REFERENCES posts (id);

\q

Создание DB-view

psql postgresql://root:root@localhost:5432/blog

CREATE VIEW USER_STATISTICS (USER_ID, NUMBER_OF_POSTS) AS
SELECT
	U.ID AS USER_ID,
	COUNT(P.ID) AS NUMBER_OF_POSTS
FROM
	USERS U
	JOIN POSTS P ON U.ID = P.AUTHOR_ID
GROUP BY
	U.ID;
\q