62 lines
2.0 KiB
SQL
62 lines
2.0 KiB
SQL
CREATE TABLE boards (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
board_id VARCHAR(10) NOT NULL UNIQUE,
|
|
name VARCHAR(50) NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_board_id (board_id)
|
|
);
|
|
|
|
CREATE TABLE threads (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
thread_id VARCHAR(6) NOT NULL UNIQUE,
|
|
board_id VARCHAR(10) NOT NULL,
|
|
title VARCHAR(255),
|
|
description TEXT,
|
|
file_name VARCHAR(255),
|
|
file_size INT,
|
|
file_type VARCHAR(10),
|
|
ip_address VARCHAR(45),
|
|
anonymous_id VARCHAR(20),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_thread_id (thread_id),
|
|
INDEX idx_board_id (board_id),
|
|
INDEX idx_updated_at (updated_at),
|
|
INDEX idx_anonymous_id (anonymous_id),
|
|
FOREIGN KEY (board_id) REFERENCES boards(board_id)
|
|
);
|
|
|
|
CREATE TABLE posts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
post_id VARCHAR(6) NOT NULL UNIQUE,
|
|
thread_id VARCHAR(6) NOT NULL,
|
|
board_id VARCHAR(10) NOT NULL,
|
|
message TEXT,
|
|
file_name VARCHAR(255),
|
|
file_size INT,
|
|
file_type VARCHAR(10),
|
|
ip_address VARCHAR(45),
|
|
anonymous_id VARCHAR(20),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_post_id (post_id),
|
|
INDEX idx_thread_id (thread_id),
|
|
INDEX idx_board_id (board_id),
|
|
INDEX idx_created_at (created_at),
|
|
INDEX idx_anonymous_id (anonymous_id),
|
|
FOREIGN KEY (thread_id) REFERENCES threads(thread_id),
|
|
FOREIGN KEY (board_id) REFERENCES boards(board_id)
|
|
);
|
|
|
|
CREATE TABLE rate_limits (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ip_address VARCHAR(45) NOT NULL,
|
|
action_type VARCHAR(20) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_ip_action (ip_address, action_type),
|
|
INDEX idx_created_at (created_at)
|
|
);
|
|
|
|
INSERT INTO boards (board_id, name, description) VALUES
|
|
('b', 'Random', 'Случайные темы'),
|
|
('mk', 'MK', 'MK темы'); |