-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase-structure.txt
143 lines (126 loc) · 6.04 KB
/
database-structure.txt
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
# Create the database using these commands:
```
DROP DATABASE IF EXISTS hirahira_example;
CREATE DATABASE hirahira_example;
CREATE TABLE accounts (id, username, password, role);
CREATE TABLE archives (id int NOT NULL AUTO_INCREMENT,
vimeo_id tinytext,
youtube_id tinytext,
embed_code longtext,
thumb longtext,
date datetime,
length time,
PRIMARY KEY (id));
CREATE TABLE games (id int NOT NULL AUTO_INCREMENT,
name tinytext NOT NULL,
type tinytext NOT NULL,
PRIMARY KEY (id),
CONSTRAINT game_type CHECK (type in ('primary', 'secondary')));
CREATE TABLE archives_games (archive_id int NOT NULL,
game_id int NOT NULL,
FOREIGN KEY (archive_id) REFERENCES archives (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE RESTRICT ON UPDATE CASCADE);
CREATE TABLE clips (id int NOT NULL AUTO_INCREMENT,
type tinytext NOT NULL,
title tinytext,
vimeo_id tinytext,
youtube_id tinytext,
embed_code longtext,
thumb longtext,
date datetime,
length time,
archive_id int,
PRIMARY KEY (id),
FOREIGN KEY (archive_id) REFERENCES archives (id) ON DELETE RESTRICT ON UPDATE CASCADE),
CONSTRAINT clip_type CHECK (type in ('highlight', 'bts')));
```
# The database consists of the following tables:
## accounts
- id: Numeric ID (auto-incrementing)
- username
- password
- role: `normal` or `admin`
## videos
- id: Numeric ID (auto-incrementing)
- type: `episode` (uncut archive) or `clip` (short outtake)
- title
- date
- vimeo_id: Numeric Vimeo ID for the video (takes highest precedence)
- youtube_id: Alphanumeric YouTube ID for the video
- embed_code: Manual embed code for the video (takes lowest precedence)
- thumb: Link to thumbnail URL (optional if using `vimeo_id` or `youtube_id`; takes highest precedence)
- length: Runtime (`time` type)
## games
- id: Numeric ID (auto-incrementing)
- name: Name of the game
- type: "primary" (usually a long-form game with a plot)
or "secondary" (usually a short-form game played in the last 1/3 of the stream).
## archives_games
- archive_id = archives(id)
- game_id = games(id)
# Resulting tables on an installed instance:
MariaDB [exampledb]> SHOW TABLES;
+-----------------------+
| Tables_in_exampledb |
+-----------------------+
| accounts |
| archives |
| archives_games |
| full |
| games |
+-----------------------+
5 rows in set (0.001 sec)
MariaDB [exampledb]> DESCRIBE accounts;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | UNI | NULL | |
| password | varchar(255) | NO | | NULL | |
| admin | tinyint(1) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [exampledb]> DESCRIBE archives;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| vimeo_id | tinytext | YES | | NULL | |
| youtube_id | tinytext | YES | | NULL | |
| embed_code | longtext | YES | | NULL | |
| thumb | longtext | YES | | NULL | |
| date_time | datetime | YES | | NULL | |
| length | time | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.001 sec)
MariaDB [exampledb]> DESCRIBE archives_games;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| archive_id | int(11) | NO | MUL | NULL | |
| game_id | int(11) | NO | MUL | NULL | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.001 sec)
MariaDB [exampledb]> DESCRIBE full;
+----------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | longtext | YES | | NULL | |
| date | datetime | YES | | NULL | |
| embed_code | longtext | YES | | NULL | |
| primary_game | longtext | YES | | NULL | |
| secondary_game | longtext | YES | | NULL | |
| thumb | longtext | YES | | NULL | |
| length | time | YES | | NULL | |
+----------------+----------+------+-----+---------+----------------+
8 rows in set (0.001 sec)
MariaDB [exampledb]> DESCRIBE games;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | tinytext | NO | | NULL | |
| type | tinytext | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.001 sec)