merix/install.sql at main · pertinent23/merix · 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
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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
#CREATE DATABASE IF NOT EXISTS merix CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS USERS(
    user_id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(300) NOT NULL,
    name VARCHAR(200) NOT NULL,
    role VARCHAR(10) NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS FILES(
    file_id INT NOT NULL AUTO_INCREMENT,
    url VARCHAR(250) NOT NULL,
    title VARCHAR(50) NOT NULL,
    description VARCHAR(400) NOT NULL,
    type VARCHAR(20),
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(file_id)
);

CREATE TABLE IF NOT EXISTS THEMES(
    theme_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(theme_id),
    FOREIGN KEY(user_id) REFERENCES USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS SITES(
    site_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    file_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    slogan VARCHAR(200) NOT NULL,
    district VARCHAR(200) NOT NULL,
    country VARCHAR(200) NOT NULL,
    position VARCHAR(200) NOT NULL,
    history TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(site_id),
    FOREIGN KEY(user_id) REFERENCES USERS(user_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS POSTS_TYPE(
    post_type_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    label VARCHAR(200) NOT NULL UNIQUE,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(post_type_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS POSTS(
    post_id INT NOT NULL AUTO_INCREMENT,
    post_type_id INT NOT NULL,
    label VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(post_id),
    FOREIGN KEY(post_type_id) REFERENCES POSTS_TYPE(post_type_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS POSTS_FILES(
    post_id INT NOT NULL,
    file_id INT NOT NULL,
    FOREIGN KEY(post_id) REFERENCES POSTS(post_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ROLES(
    role_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    label VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(role_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS EMPLOYEES(
    employee_id INT NOT NULL AUTO_INCREMENT,
    role_id INT NOT NULL,
    file_id INT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    background TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(employee_id),
    FOREIGN KEY(role_id) REFERENCES ROLES(role_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE,
    CHECK(age >= 10)
);

CREATE TABLE IF NOT EXISTS PLACES(
    place_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(place_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS PLACES_FILES(
    place_id INT NOT NULL,
    file_id INT NOT NULL,
    FOREIGN KEY(place_id) REFERENCES PLACES(place_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ACTIVITIES(
    activity_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(activity_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ACTIVITIES_FILES(
    activity_id INT NOT NULL,
    file_id INT NOT NULL,
    FOREIGN KEY(activity_id) REFERENCES ACTIVITIES(activity_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS PUBS(
    pub_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    file_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(pub_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS PROJECTS(
    project_id INT NOT NULL AUTO_INCREMENT,
    site_id INT NOT NULL,
    file_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    createdAt DATETIME NOT NUll,
    updatedAt DATETIME NOT NUll,
    PRIMARY KEY(project_id),
    FOREIGN KEY(site_id) REFERENCES SITES(site_id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES FILES(file_id) ON DELETE CASCADE
);