SQL Statements.txt

CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    login VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    creation_date DATE DEFAULT(CURRENT_DATE)
);

INSERT INTO users (login, password, name);
VALUES (p_login, p_password, p_name);

CREATE TABLE IF NOT EXISTS posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    title VARCHAR(100) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES users (user_id),
    description TEXT,
    likes INT,
    text_tutorial TEXT,
    video_tutorial TINYTEXT
    creation_date DATETIME DEFAULT(CURRENT_TIMESTAMP)
);


SELECT name FROM users WHERE user_id = (SELECT author_id FROM posts WHERE post_id = p_current_post_id)

CREATE TABLE IF NOT EXISTS comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    parent_post_id INT,
    FOREIGN KEY (parent_post_id) REFERENCES posts (post_id),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES users (user_id),
    text TEXT,
    creation_date DATETIME DEFAULT(CURRENT_TIMESTAMP)
);

CREATE TABLE IF NOT EXISTS pictures (
    picture_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    parent_post_id INT,
    FOREIGN KEY (parent_post_id) REFERENCES posts (post_id)
);