-
Notifications
You must be signed in to change notification settings - Fork 0
/
to_dentro.sql
292 lines (237 loc) · 6.98 KB
/
to_dentro.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
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
create table dia(
id_dia serial not null,
dia varchar(100) not null,
primary key(id_dia)
);
INSERT INTO dia (dia) VALUES ('Segunda-Feira');
INSERT INTO dia (dia) VALUES ('Terça-Feira');
INSERT INTO dia (dia) VALUES ('Quarta-Feira');
INSERT INTO dia (dia) VALUES ('Quinta-Feira');
INSERT INTO dia (dia) VALUES ('Sexta-Feira');
create table categorias(
id_cat serial not null,
categoria varchar(150) not null,
primary key(id_cat)
);
create table local(
id_lo serial not null,
sala varchar(150) not null,
Primary key(id_lo)
);
create table status(
id_sta serial not null,
status text not null,
primary key(id_sta)
);
INSERT INTO status (status) VALUES ('Sob Avaliação!');
INSERT INTO status (status) VALUES ('Rejeitado!');
INSERT INTO status (status) VALUES ('Editado!');
INSERT INTO status (status) VALUES ('Publicado!');
INSERT INTO status (status) VALUES ('Publicado e editado!');
create table assistencias(
id_assist serial not null,
assist varchar(150) not null,
texto text not null,
primary key(id_assist)
);
create table semestre(
id_sem serial not null,
semestre text not null,
Primary key(id_sem)
);
INSERT INTO semestre (semestre) VALUES ('1º Semestre');
INSERT INTO semestre (semestre) VALUES ('2º Semestre');
INSERT INTO semestre (semestre) VALUES ('3º Semestre');
INSERT INTO semestre (semestre) VALUES ('4º Semestre');
INSERT INTO semestre (semestre) VALUES ('5º Semestre');
INSERT INTO semestre (semestre) VALUES ('6º Semestre');
INSERT INTO semestre (semestre) VALUES ('7º Semestre');
INSERT INTO semestre (semestre) VALUES ('8º Semestre');
INSERT INTO semestre (semestre) VALUES ('9º Semestre');
INSERT INTO semestre (semestre) VALUES ('10º Semestre');
INSERT INTO semestre (semestre) VALUES ('11º Semestre');
INSERT INTO semestre (semestre) VALUES ('12º Semestre');
create table eventos(
id_event serial not null,
evento varchar(300) not null,
event_cat integer not null,
data_inicio date not null,
data_fim date default(null),
horario varchar(100) not null,
texto text not null,
imagem text,
primary key(id_event),
foreign key(event_cat) references categorias
ON UPDATE CASCADE ON DELETE CASCADE
);
create table estagios(
id_est serial not null,
titulo varchar(200) not null,
salario numeric(10,2) not null,
condicoes text not null,
atividades text not null,
exigencias text not null,
info_est text,
primary key(id_est)
);
create table cardapios(
id_card serial not null,
dia integer not null unique,
data date not null,
primary key(id_card),
foreign key(dia) references dia
ON UPDATE CASCADE ON DELETE CASCADE
);
create table alimentos(
id_ali serial not null,
alimento varchar(150) not null,
primary key(id_ali)
);
create table alimentos_cardapios(
card_id integer not null,
ali_id integer not null,
foreign key(card_id) references cardapios
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(ali_id) references alimentos
ON UPDATE CASCADE ON DELETE CASCADE
);
create table usertype(
id_type serial not null,
type varchar(50) not null,
primary key(id_type)
);
INSERT INTO usertype (type) VALUES('Autor');
INSERT INTO usertype (type) VALUES('Editor');
INSERT INTO usertype (type) VALUES('Administrador');
INSERT INTO usertype (type) VALUES('Revisor');
create table usuarios(
id_user serial not null,
nome varchar(200) not null,
email varchar(200) not null unique,
senha varchar(500) not null,
type_id integer default(1) not null,
primary key(id_user),
foreign key(type_id) references usertype
ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO usuarios (nome, email, senha, type_id) VALUES ('Administrador','admin@admin.com','admin', '3');
create table permissaoteste(
id_perm serial not null,
user_id integer not null,
noticias text DEFAULT(NULL),
cardapios text DEFAULT(NULL),
cursos text DEFAULT(NULL),
primary key(id_perm),
foreign key(user_id) references usuarios
ON UPDATE CASCADE ON DELETE CASCADE
);
create table permissions(
id_perm serial not null,
user_id integer not null,
noticias boolean not null default false,
cardapios boolean not null default false,
cursos boolean not null default false,
monitorias boolean not null default false,
eventos boolean not null default false,
setores boolean not null default false,
assistencia boolean not null default false,
categorias boolean not null default false,
locais boolean not null default false,
estagios boolean not null default false,
primary key(id_perm),
foreign key(user_id) references usuarios
ON UPDATE CASCADE ON DELETE CASCADE
);
create table noticias(
id_not serial not null,
titulo varchar(200) not null,
linha_apoio varchar(350),
texto text not null,
data date default(now()) not null,
hora time default(now()) not null,
autor integer not null,
status integer not null,
url text,
primary key(id_not),
foreign key(autor) references usuarios
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(status) references status
ON UPDATE CASCADE ON DELETE CASCADE
);
create table categorias_noticias(
cat_id integer not null,
not_id integer not null,
foreign key(cat_id) references categorias
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(not_id) references noticias
ON UPDATE CASCADE ON DELETE CASCADE
);
create table imagens_noticias(
id_im serial not null,
imagem text not null,
noticia integer not null,
primary key(id_im),
foreign key(noticia) references noticias
ON UPDATE CASCADE ON DELETE CASCADE
);
create table programacao(
id_prog serial not null,
evento_id integer not null,
primary key(id_prog),
foreign key(evento_id) references eventos
ON UPDATE CASCADE ON DELETE CASCADE
);
create table instituto(
id_inst serial not null,
instituto text not null,
primary key(id_inst)
);
INSERT INTO instituto (instituto) VALUES ('IFSul - Campus Pelotas');
create table cursos(
id_curso serial not null,
nome text not null,
inst_id integer default(1),
texto text,
logo text,
primary key(id_curso),
foreign key(inst_id) references instituto
);
create table disciplinas(
id_disc serial not null,
disciplina text not null,
curso integer not null,
primary key(id_disc),
foreign key(curso) references cursos
ON UPDATE CASCADE ON DELETE CASCADE
);
create table estagio_cursos(
est_id integer not null,
curso_id integer not null,
foreign key(est_id) references estagios
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(curso_id) references cursos
ON UPDATE CASCADE ON DELETE CASCADE
);
create table monitorias(
id_monit serial not null,
curso_m integer not null,
semestre_m integer not null,
sala_m integer not null,
disciplina_m integer not null,
info_m text not null,
primary key(id_monit),
foreign key(curso_m) references cursos
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(semestre_m) references semestre
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(sala_m) references local
ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(disciplina_m) references disciplinas
ON UPDATE CASCADE ON DELETE CASCADE
);
create table setores(
id_set serial not null,
setor varchar(75) not null,
texto text not null,
primary key(id_set)
);