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
|
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
nick text UNIQUE NOT NULL,
hash text NOT NULL,
email text NOT NULL,
created_on timestamp NOT NULL DEFAULT now(),
avatar text NOT NULL DEFAULT '',
contact text NOT NULL DEFAULT '',
bio text NOT NULL DEFAULT ''
);
CREATE TABLE rooms (
room_id SERIAL PRIMARY KEY,
name text UNIQUE NOT NULL,
created_on timestamp NOT NULL DEFAULT now(),
admin_only bool NOT NULL DEFAULT false
);
CREATE TABLE messages (
message_id SERIAL PRIMARY KEY,
user_id integer NOT NULL REFERENCES users,
room_id integer NOT NULL REFERENCES rooms,
content text NOT NULL,
created_on timestamp NOT NULL DEFAULT now(),
is_image bool NOT NULL
);
CREATE INDEX user_id_idx ON messages (user_id);
CREATE INDEX room_id_idx ON messages (room_id);
CREATE INDEX created_on_idx ON messages (created_on);
INSERT INTO rooms (room_id, name) VALUES (1, 'dumpfm');
|
