socialifesweb/social.sql at main · danielrt/socialifesweb · 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
BEGIN;

CREATE TABLE public.usuario
(
    login text NOT NULL,
    token text NOT NULL,
    nome text NOT NULL,
    data_nascimento date NOT NULL,
    cidade text NOT NULL,
    foto text NOT NULL,
    PRIMARY KEY (login)
);

CREATE TABLE public.post
(
    id serial NOT NULL,
    data_hora timestamp without time zone NOT NULL,
    texto text,
    imagem text,
    usuario_login text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT check_texto_ou_imagem CHECK (texto IS NOT NULL OR imagem IS NOT NULL),
    CONSTRAINT fk_post_usuario FOREIGN KEY (usuario_login) REFERENCES public.usuario (login) ON DELETE CASCADE
);

CREATE TABLE public.comentario
(
    id serial NOT NULL,
    texto text NOT NULL,
    data_hora timestamp without time zone NOT NULL,
    post_id integer NOT NULL,
    usuario_login text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_comentario_usuario FOREIGN KEY (usuario_login) REFERENCES public.usuario (login) ON DELETE CASCADE,
    CONSTRAINT fk_comentario_post FOREIGN KEY (post_id) REFERENCES public.post (id) ON DELETE CASCADE
);

CREATE TABLE public.seguindo
(
    id serial NOT NULL,
    usuario_login text NOT NULL,
    usuario_login_seguindo text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_seguindo_usuario FOREIGN KEY (usuario_login) REFERENCES public.usuario (login) ON DELETE CASCADE,
    CONSTRAINT fk_seguindo_seguindo FOREIGN KEY (usuario_login_seguindo) REFERENCES public.usuario (login) ON DELETE CASCADE
);

CREATE TABLE public.curtida
(
    id serial NOT NULL,
    post_id integer NOT NULL,
    usuario_login text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT unique_curtida UNIQUE (post_id, usuario_login),
    CONSTRAINT fk_curtida_usuario FOREIGN KEY (usuario_login) REFERENCES public.usuario (login) ON DELETE CASCADE,
    CONSTRAINT fk_curtida_post FOREIGN KEY (post_id) REFERENCES public.post (id) ON DELETE CASCADE
);

CREATE TABLE public.notificacao
(
    id serial NOT NULL,
    nova boolean NOT NULL,
    data_hora timestamp without time zone NOT NULL,
    usuario_login text NOT NULL,
    usuario_login_alvo text NOT NULL,
    acao integer NOT NULL,
    post_id integer,
    PRIMARY KEY (id),
    CONSTRAINT fk_notificacao_usuario FOREIGN KEY (usuario_login) REFERENCES public.usuario (login) ON DELETE CASCADE,
    CONSTRAINT fk_notificacao_usuario_alvo FOREIGN KEY (usuario_login_alvo) REFERENCES public.usuario (login) ON DELETE CASCADE,
    CONSTRAINT fk_notificacao_post FOREIGN KEY (post_id) REFERENCES public.post (id) ON DELETE CASCADE
);

END;