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
| CREATE TABLE channels (
id serial NOT NULL,
name character(255) NOT NULL,
reg_date timestamp with time zone,
last_activity timestamp with time zone,
flags character(24),
CONSTRAINT channel_pk PRIMARY KEY (id),
CONSTRAINT channel_unik UNIQUE (id,name)
);
CREATE TABLE users (
id serial NOT NULL,
name text NOT NULL,
admin_level integer,
last_activity timestamp with time zone,
reg_date timestamp with time zone,
CONSTRAINT users_pk PRIMARY KEY (id),
CONSTRAINT users_unik UNIQUE (id,name)
);
CREATE TABLE messages (
id serial NOT NULL,
content text,
user_id integer,
channel_id integer,
post_date timestamp with time zone,
CONSTRAINT messages_pk PRIMARY KEY (id),
CONSTRAINT messages_unik UNIQUE (id),
CONSTRAINT messages_fk_channels FOREIGN KEY (channel_id) REFERENCES channels(id),
CONSTRAINT messages_fk_users FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE user_on_channel (
id serial NOT NULL,
user_id integer NOT NULL,
channel_id integer NOT NULL,
level integer,
flags character(24),
CONSTRAINT user_on_channel_pk PRIMARY KEY (id),
CONSTRAINT user_on_channel_unik UNIQUE (id),
CONSTRAINT user_on_channel_fk_users FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT user_on_channel_fk_channels FOREIGN KEY (channel_id) REFERENCES channels(id)
);
CREATE INDEX channels_idx_btree_id_name on channels using btree(id, name);
CREATE INDEX users_idx_btree_id_name on users using btree(id, name);
CREATE INDEX messages_idx_btree_id_content on messages using btree(id, content);
CREATE INDEX messages_idx_gin_content on messages using gin(to_tsvector(content)); |
