-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
127 lines (102 loc) · 3.13 KB
/
schema.sql
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
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE DATABASE IF NOT EXISTS sonic DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE sonic;
CREATE TABLE category (
id int(11) NOT NULL,
code varchar(25) DEFAULT NULL,
name varchar(100) NOT NULL,
icon varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE categoryrelationships (
parent int(11) NOT NULL,
child int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE entity (
id int(11) NOT NULL,
name varchar(150) NOT NULL,
type int(11) DEFAULT NULL,
description varchar(1000) DEFAULT NULL,
img int(11) NOT NULL,
iconx int(11) DEFAULT NULL,
icony int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE entityancestors (
entityid int(11) NOT NULL,
ancestorid int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE feedback (
id int(11) NOT NULL,
text varchar(1000) NOT NULL,
name varchar(69) DEFAULT NULL,
contact varchar(69) DEFAULT NULL,
path varchar(150) DEFAULT NULL,
issue int(11) DEFAULT NULL,
date datetime DEFAULT NULL,
dismissed tinyint(4) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE issues (
id int(11) NOT NULL,
entity int(11) NOT NULL,
type int(11) NOT NULL,
issue varchar(5000) NOT NULL,
sourceurl varchar(300) NOT NULL,
startdate datetime NOT NULL,
enddate datetime DEFAULT NULL,
ongoing tinyint(4) DEFAULT NULL,
contentwarning varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE issuetype (
id int(11) NOT NULL,
name varchar(45) NOT NULL,
icon varchar(45) NOT NULL,
color varchar(15) NOT NULL,
showOnTop tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE relationships (
parent int(11) NOT NULL,
child int(11) NOT NULL,
relationtype int(11) NOT NULL DEFAULT 1,
asOfDate date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE synonym (
entityid int(11) NOT NULL,
synonym varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE category
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id_UNIQUE (id);
ALTER TABLE categoryrelationships
ADD KEY parent (parent),
ADD KEY child (child);
ALTER TABLE entity
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id_UNIQUE (id);
ALTER TABLE entity ADD FULLTEXT KEY name (name);
ALTER TABLE entityancestors
ADD KEY entityid (entityid),
ADD KEY ancestorid (ancestorid);
ALTER TABLE feedback
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id_UNIQUE (id);
ALTER TABLE issues
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id_UNIQUE (id);
ALTER TABLE issuetype
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id_UNIQUE (id);
ALTER TABLE relationships
ADD KEY parent (parent),
ADD KEY child (child);
ALTER TABLE synonym
ADD KEY entityid (entityid);
ALTER TABLE category
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE entity
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE feedback
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE issues
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE issuetype
MODIFY id int(11) NOT NULL AUTO_INCREMENT;