13hB5sfb

1DROP DATABASE IF EXISTS ssas;
2CREATE DATABASE ssas;
3USE ssas;
4
5CREATE TABLE user(
6    id INT AUTO_INCREMENT,
7    username VARCHAR(256) UNIQUE,
8    password VARCHAR(256) NOT NULL,
9    PRIMARY KEY (id)
10) engine='innodb';
11
12CREATE TABLE image(
13    id INT AUTO_INCREMENT,
14    owner_id INT NOT NULL,
15    createdDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
16    PRIMARY KEY (id),
17    FOREIGN KEY fk_owner (owner_id) REFERENCES user(id) ON DELETE CASCADE
18) engine='innodb';
19
20CREATE TABLE shared_image(
21    user_id INT NOT NULL,
22    image_id INT NOT NULL,
23    PRIMARY KEY(user_id, image_id),
24    FOREIGN KEY fk_user (user_id) REFERENCES user(id) ON DELETE CASCADE,
25    FOREIGN KEY fk_image (image_id) REFERENCES image(id) ON DELETE CASCADE
26) engine='innodb';
27
28CREATE TABLE post(
29    id INT AUTO_INCREMENT,
30    text TEXT NOT NULL,
31    user_id INT NOT NULL,
32    image_id INT NOT NULL,
33    createdDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
34    PRIMARY KEY (id),
35    FOREIGN KEY fk_user (user_id) REFERENCES user(id) ON DELETE CASCADE,
36    FOREIGN KEY fk_image (image_id) REFERENCES image(id) ON DELETE CASCADE
37) engine='innodb';