init.sql

-- Active: 1763280620260@@127.0.0.1@3306@pictureweb
CREATE DATABASE IF NOT EXISTS pictureweb;
USE pictureweb;

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE KEY NOT NULL,
    email VARCHAR(255) UNIQUE KEY NOT NULL,
    password VARCHAR(255) NOT NULL
);
DROP TABLE IF EXISTS images;
CREATE TABLE IF NOT EXISTS images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(255) NOT NULL,
    resolutionX INT NOT NULL,
    resolutionY INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);


DROP TABLE IF EXISTS labels;
CREATE TABLE IF NOT EXISTS labels(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
)

DROP TABLE IF EXISTS labelLinks;
CREATE TABLE IF NOT EXISTS labelLinks(
    p_id INT NOT NULL,
    l_id INT NOT NULL,
    PRIMARY KEY (p_id,l_id),
    FOREIGN KEY (p_id) REFERENCES images(id),
    FOREIGN KEY (l_id) REFERENCES labels(id)
)