Q0HXcqgw

1create table if not exists users
2(
3    ID   SERIAL PRIMARY KEY,
4    NAME VARCHAR(64) UNIQUE not null
5);
6
7create table if not exists chats
8(
9    ID              SERIAL PRIMARY KEY,
10    NAME            VARCHAR(64)  not null,
11    TYPE            VARCHAR(16)  not null,
12    IMAGE_URL       varchar(256) not null,
13    OWNER           VARCHAR(64),
14    GROUP_CHAT_TYPE varchar(16)
15);
16
17create table if not exists messages
18(
19    ID      SERIAL PRIMARY KEY,
20    TEXT    VARCHAR(4096) not null,
21    OWNER   INTEGER       not null,
22    CHAT_ID INTEGER       not null,
23    TIME    TIMESTAMP     not null
24);
25
26create table if not exists user_chats
27(
28    USER_ID INTEGER not null,
29    CHAT_ID INTEGER not null,
30    PRIMARY KEY (USER_ID, CHAT_ID),
31    CONSTRAINT fk_uc_user FOREIGN KEY (USER_ID) REFERENCES users (id) ON DELETE CASCADE,
32    CONSTRAINT fk_uc_chat FOREIGN KEY (CHAT_ID) REFERENCES chats (id) ON DELETE CASCADE
33);
34
35CREATE INDEX if not exists userChatsUserIdIndex ON user_chats (USER_ID);