romest/dbschema.sql at master · RobSil/romest · 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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
drop table if exists public.users cascade;
drop table if exists public.roles cascade;
drop table if exists public.users_roles cascade;
drop table if exists public.properties cascade;
drop table if exists public.photos cascade;
drop table if exists public.posts cascade;
drop table if exists public.tags cascade;
drop table if exists public.posts_tags;
drop table if exists public.likes;
drop table if exists public.avatars cascade;
drop table if exists public.boards cascade;

create table if not exists public.users
(
    id                 bigint                                                not null generated always as identity,
    created_date       timestamp without time zone default CURRENT_TIMESTAMP not null,
    last_modified_date timestamp without time zone,

    username           character varying(64)                                 not null unique,
    email              character varying(64)                                 not null unique,
    password_hash      character varying(512)                                not null,
    is_blocked         bool                                                  not null,

    constraint users_pkey primary key (id)
);

create table if not exists public.roles
(
    id                 bigint                not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    title              character varying(32) not null,

    constraint roles_pkey primary key (id)
);

create table if not exists public.users_roles
(
    id                 bigint not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    user_id            bigint not null,
    role_id            bigint not null,

    constraint user_role_pkey primary key (id),
    constraint fk_users foreign key (user_id) references public.users (id),
    constraint fk_roles foreign key (role_id) references public.roles (id)
);

create table if not exists public.properties
(
    id                 bigint                not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    name               character varying(64) not null,
    value              character varying(64) not null,

    constraint properties_pkey primary key (id)
);

create table if not exists public.boards
(
    id                 bigint                not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    name               character varying(64) not null,
    minimized_name     character varying(64) not null,
    is_private         bool                  not null,

    user_id            bigint                not null,

    unique (minimized_name, user_id),

    constraint boards_pkey primary key (id),
    constraint fk_user foreign key (user_id) references public.users (id)
);

-- create table if not exists public.posts_boards
-- (
--     id                 bigint not null generated always as identity,
--     created_date       timestamp without time zone default current_timestamp,
--     last_modified_date timestamp without time zone,
--
--     post_id            bigint not null,
--     board_id           bigint not null,
--
--     constraint post_board_pkey primary key (id),
--     constraint fk_posts foreign key (post_id) references public.posts (id),
--     constraint fk_boards foreign key (board_id) references public.boards (id)
-- );

create table if not exists public.photos
(
    id                 bigint                  not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    path               character varying(1024) not null,
    storing_source     character varying(128)  not null,
    image_kit_id       character varying(128)  not null,
    image_kit_url      character varying(512)  not null,
--     post_id            bigint                  not null,

    constraint photos_pkey primary key (id)
--     constraint fk_posts foreign key (post_id) references public.posts (id)
);

create table if not exists public.tags
(
    id                 bigint                not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    title              character varying(64) not null unique,

    constraint tags_pkey primary key (id)
);

create table if not exists public.posts
(
    id                 bigint                  not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    title              character varying(256)  null,
    text               character varying(8192) null,

    board_id           bigint                  not null,
    photo_id           bigint                  not null,

    constraint posts_pkey primary key (id),
    constraint fk_board foreign key (board_id) references public.boards (id),
    constraint fk_photo foreign key (photo_id) references public.photos (id)
);

create table if not exists public.posts_tags
(
    id                 bigint not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    post_id            bigint not null,
    tag_id             bigint not null,

    constraint posts_tags_pkey primary key (id),
    constraint fk_post foreign key (post_id) references public.posts (id),
    constraint fk_tag foreign key (tag_id) references public.tags (id)
);

create table if not exists public.likes
(
    id                 bigint not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    post_id            bigint not null,
    user_id            bigint not null,

    unique (post_id, user_id),

    constraint likes_pkey primary key (id),
    constraint fk_post foreign key (post_id) references public.posts (id),
    constraint fk_user foreign key (user_id) references public.users (id)
);

create table if not exists public.avatars
(
    id                 bigint                  not null generated always as identity,
    created_date       timestamp without time zone default current_timestamp,
    last_modified_date timestamp without time zone,

    path               character varying(1024) not null,
    user_id            bigint                  not null,

    constraint avatars_pkey primary key (id),
    constraint fk_users foreign key (user_id) references public.users (id)
);