createdb.sql

DROP DATABASE IF EXISTS forums;
CREATE DATABASE forums CHARACTER SET 'UTF8';
USE forums;

CREATE TABLE User (
	id MEDIUMINT NOT NULL AUTO_INCREMENT, 
	about VARCHAR(80), 
	email VARCHAR(50) CHARACTER SET 'CP1251' NOT NULL UNIQUE, 
	isAnonymous BOOLEAN NOT NULL DEFAULT False, 
	name VARCHAR(40), 
	username VARCHAR(20) CHARACTER SET 'CP1251', 
	PRIMARY KEY(id),
	KEY(email(10))
);

CREATE TABLE Forum (
	id MEDIUMINT NOT NULL AUTO_INCREMENT, 
	name VARCHAR(80) NOT NULL UNIQUE, 
	short_name VARCHAR(40) CHARACTER SET 'CP1251' NOT NULL UNIQUE, 
	user VARCHAR(50) CHARACTER SET 'CP1251' NOT NULL, 
	PRIMARY KEY(id),
	FOREIGN KEY(user) REFERENCES User(email),
	KEY(short_name(20))
);

CREATE TABLE Thread (
	id MEDIUMINT NOT NULL AUTO_INCREMENT, 
	forum VARCHAR(40) CHARACTER SET 'CP1251' NOT NULL, 
	isClosed BOOLEAN NOT NULL DEFAULT False, 
	isDeleted BOOLEAN NOT NULL DEFAULT False, 
	message VARCHAR(500) NOT NULL, 
	slug VARCHAR(40) CHARACTER SET 'CP1251' NOT NULL, 
	title VARCHAR(80) NOT NULL, 
	user VARCHAR(50) CHARACTER SET 'CP1251' NOT NULL,
	date DATETIME NOT NULL,
	PRIMARY KEY(id),
	FOREIGN KEY(forum) REFERENCES Forum(short_name),
	FOREIGN KEY(user) REFERENCES User(email)
);

CREATE TABLE Post (
	id MEDIUMINT NOT NULL AUTO_INCREMENT, 
	date DATETIME NOT NULL, 
	forum VARCHAR(40) CHARACTER SET 'CP1251' NOT NULL, 
	parent MEDIUMINT, 
	isApproved BOOLEAN NOT NULL DEFAULT False, 
	isDeleted BOOLEAN NOT NULL DEFAULT False, 
	isEdited BOOLEAN NOT NULL DEFAULT False, 
	isHighlighted BOOLEAN NOT NULL DEFAULT False, 
	isSpam BOOLEAN NOT NULL DEFAULT False, 
	message VARCHAR(500) NOT NULL, 
	thread MEDIUMINT NOT NULL, 
	user VARCHAR(50) CHARACTER SET 'CP1251' NOT NULL, 
	PRIMARY KEY(id),
	FOREIGN KEY(parent) REFERENCES Post(id),
	FOREIGN KEY(thread) REFERENCES Thread(id),
	FOREIGN KEY(user) REFERENCES User(email),
	FOREIGN KEY(forum) REFERENCES Forum(short_name)
);

CREATE TABLE Followers (
        follower_id MEDIUMINT NOT NULL,
        target_id MEDIUMINT NOT NULL,
        FOREIGN KEY(follower_id) REFERENCES User(id),
        FOREIGN KEY(target_id) REFERENCES User(id)
) ENGINE = MYISAM;

CREATE TABLE Postrating (
        id MEDIUMINT NOT NULL,
        likes MEDIUMINT NOT NULL DEFAULT 0,
        dislikes MEDIUMINT NOT NULL DEFAULT 0,
        FOREIGN KEY(id) REFERENCES Post(id)
) ENGINE = MYISAM;

CREATE TABLE Threadrating (
        id MEDIUMINT NOT NULL,
        likes MEDIUMINT NOT NULL DEFAULT 0,
        dislikes MEDIUMINT NOT NULL DEFAULT 0,
        FOREIGN KEY(id) REFERENCES Thread(id)
) ENGINE = MYISAM;

CREATE TABLE Subscribe (
        user_id MEDIUMINT NOT NULL,
        thread_id MEDIUMINT NOT NULL,
        FOREIGN KEY(user_id) REFERENCES User(id),
        FOREIGN KEY(thread_id) REFERENCES Thread(id)
) ENGINE = MYISAM;