Forum_api/create.sql at master · akapust1n/Forum_api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
use forum;
DROP TABLE IF EXISTS Followers;
DROP TABLE IF EXISTS Subscribers;
DROP TABLE IF EXISTS Posts;
DROP TABLE IF EXISTS Threads;
DROP TABLE IF EXISTS Forums;
DROP TABLE IF EXISTS Users;

CREATE TABLE Users (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
email VARCHAR(50) NOT NULL,
username VARCHAR(50),
about TEXT,
name VARCHAR(50),
isAnonymous BOOLEAN NOT NULL DEFAULT 0,
UNIQUE KEY(email),
INDEX ind1(name),
INDEX ind2(id,name)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Forums (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(40) UNIQUE KEY,
short_name VARCHAR(40) UNIQUE KEY,
user VARCHAR(50) NOT NULL,
user_id INT UNSIGNED NOT NULL,
UNIQUE KEY (short_name),
FOREIGN KEY (user_id) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE Threads (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
forum VARCHAR(40),
user VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
slug VARCHAR(50) NOT NULL,
message TEXT NOT NULL,
date DATETIME NOT NULL,
likes INT NOT NULL DEFAULT 0,
dislikes INT NOT NULL DEFAULT 0,
isClosed BOOLEAN NOT NULL DEFAULT 0,
isDeleted BOOLEAN NOT NULL DEFAULT 0,
forum_id INT UNSIGNED NOT NULL DEFAULT 0,
user_id INT UNSIGNED NOT NULL DEFAULT 0,
posts INT UNSIGNED NOT NULL DEFAULT 0,
INDEX(user, date),
INDEX (forum, date),
FOREIGN KEY (user_id) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (forum_id) REFERENCES Forums(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Posts (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
user VARCHAR(50) NOT NULL,
message TEXT NOT NULL,
forum VARCHAR(40),
thread_id INT UNSIGNED NOT NULL DEFAULT 0,
parent INT NULL DEFAULT NULL,
date DATETIME NOT NULL,
likes INT NOT NULL DEFAULT 0,
dislikes INT NOT NULL DEFAULT 0,
isApproved BOOLEAN NOT NULL DEFAULT 0,
isHighlighted BOOLEAN NOT NULL DEFAULT 0,
isEdited BOOLEAN NOT NULL DEFAULT 0,
isSpam BOOLEAN NOT NULL DEFAULT 0,
isDeleted BOOLEAN NOT NULL DEFAULT 0,
Pathlvl1 INT DEFAULT 0,
Pathlvl2 INT DEFAULT 0,
Pathlvl3 INT DEFAULT 0,
Pathlvl4 INT DEFAULT 0,
forum_id INT UNSIGNED NOT NULL DEFAULT 0,
user_id INT UNSIGNED NOT NULL DEFAULT 0,
INDEX(id,isDeleted),
INDEX(user, date),
INDEX (forum, date),
INDEX(thread_id , date),
INDEX(Pathlvl1,Pathlvl2,id,Pathlvl3,Pathlvl4),
FOREIGN KEY (user_id) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (forum_id) REFERENCES Forums(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (thread_id) REFERENCES Threads(id)
ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE Followers (
follower VARCHAR(50) NOT NULL,
followee VARCHAR(50) NOT NULL,
follower_id INT UNSIGNED NOT NULL DEFAULT 0,
followee_id INT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY (followee, follower),
FOREIGN KEY (follower_id ) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (followee_id) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE Subscribers (
user VARCHAR(50) NOT NULL,
thread_id INT UNSIGNED NOT NULL DEFAULT 0,
user_id INT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY (user, thread_id),
FOREIGN KEY (user_id) REFERENCES Users(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (thread_id) REFERENCES Threads(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;