web-version-2/database.sql at main · IceCraft-Studio/web-version-2 · 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
CREATE TABLE `category` (
  `id` varchar(255) PRIMARY KEY,
  `name` varchar(255) NOT NULL
);

CREATE TABLE `role` (
  `id` varchar(255) PRIMARY KEY,
  `name` varchar(255) NOT NULL
);

CREATE TABLE `user` (
  `username` varchar(255) PRIMARY KEY,
  `password_hash` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL,
  `display_name` varchar(255),
  `email` varchar(255) UNIQUE,
  `social_reddit` varchar(255),
  `social_twitter` varchar(255),
  `social_instagram` varchar(255),
  `social_discord` varchar(255),
  `social_website` varchar(255),
  `datetime_created` datetime NOT NULL,
  FOREIGN KEY (`role`) REFERENCES `role`(`id`)
);

CREATE TABLE `project` (
  `category` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `datetime_created` datetime NOT NULL,
  `datetime_modified` datetime NOT NULL,
  PRIMARY KEY (`category`, `slug`),
  FOREIGN KEY (`category`) REFERENCES `category`(`id`),
  FOREIGN KEY (`username`) REFERENCES `user`(`username`)
);

CREATE TABLE `project_gallery` (
  `category` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  PRIMARY KEY (`category`, `slug`,`file_name`),
  FOREIGN KEY (`category`, `slug`)
    REFERENCES `project`(`category`, `slug`)
);

CREATE TABLE `project_link` (
  `category` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  PRIMARY KEY (`category`, `slug`,`url`),
  FOREIGN KEY (`category`, `slug`)
    REFERENCES `project`(`category`, `slug`)
);

CREATE TABLE `project_upload` (
  `category` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  PRIMARY KEY (`category`, `slug`,`file_name`),
  FOREIGN KEY (`category`, `slug`)
    REFERENCES `project`(`category`, `slug`)
);

CREATE TABLE `session` (
  `token` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `timestamp` datetime NOT NULL,
  `expires` datetime NOT NULL,
  PRIMARY KEY (`token`),
  FOREIGN KEY (`username`) REFERENCES `user`(`username`)
);