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
|
CREATE DATABASE dumpfm;
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(),
last_login timestamp,
created_ip cidr DEFAULT '0',
last_ip cidr DEFAULT '0',
is_admin boolean NOT NULL DEFAULT false,
avatar text NOT NULL DEFAULT '',
contact text NOT NULL DEFAULT '',
bio text NOT NULL DEFAULT '',
is_bot boolean NOT NULL DEFAULT false,
profile_bg text
);
ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick));
CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
-- ALTER TABLE users
-- ADD COLUMN last_login timestamp;
-- ALTER TABLE users
-- ADD COLUMN created_ip cidr DEFAULT '0';
-- ALTER TABLE users
-- ADD COLUMN last_ip cidr DEFAULT '0';
CREATE TABLE rooms (
room_id SERIAL PRIMARY KEY,
key text UNIQUE NOT NULL,
name text NOT NULL,
description text NOT NULL,
created_on timestamp NOT NULL DEFAULT now(),
admin_only bool NOT NULL DEFAULT false,
active bool NOT NULL DEFAULT true
);
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,
is_text bool NOT NULL DEFAULT false
);
-- historical image dumps per user
CREATE INDEX messages_user_created_on_image_only_idx ON messages (user_id, created_on)
WHERE is_image;
-- historical image dumps in a room. needs to handle non-image messages.
CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on);
-- Queries to support:
-- 1) What messages are tagged x? (ordered by time, popularity)
-- 2) What did I tag x?
-- 3) Which of my things are tagged x?
-- 4) Which of user u's messages are tagged x?
-- 5) What are the popular tags lately?
CREATE TABLE tags (
user_id integer NOT NULL REFERENCES users,
message_id integer NOT NULL REFERENCES messages,
created_on timestamp NOT NULL DEFAULT now(),
tag text NOT NULL,
PRIMARY KEY (user_id, message_id, tag)
);
CREATE INDEX tags_message_id_idx ON tags (message_id);
CREATE INDEX tags_tag_lowercase_idx ON tags (lower(tag));
CREATE INDEX tags_user_id_created_on_idx ON tags(user_id, created_on);
CREATE TABLE mutes (
mute_id SERIAL PRIMARY KEY,
user_id integer NOT NULL REFERENCES users,
admin_id integer NOT NULL REFERENCES users,
set_on timestamp NOT NULL DEFAULT now(),
duration interval NOT NULL,
reason text NOT NULL,
cancelled bool NOT NULL DEFAULT false,
cancel_admin_id integer REFERENCES users,
cancel_reason text
);
CREATE INDEX mutes_expires_idx ON mutes ((set_on + duration));
INSERT INTO rooms (key, name, description, admin_only)
VALUES ('dumpfm', 'Room A', 'Hangout', false);
INSERT INTO rooms (key, name, description, admin_only)
VALUES ('VIP', 'The VIP Room', 'Command Post', true);
-- Initially, feed_images are added with no message_id,
-- and are used as a queue of pending messages to post.
-- When the image is posted, message_id is updated to link
-- to the resulting message.
CREATE TABLE feed_images (
feed_url text NOT NULL,
link text NOT NULL,
title text NOT NULL,
image_url text NOT NULL,
room text NOT NULL,
message_id integer REFERENCES messages,
queued_on timestamp NOT NULL DEFAULT now(),
PRIMARY KEY (room, image_url)
);
CREATE INDEX feed_images_url_room_idx ON feed_images (image_url, room);
CREATE INDEX feed_images_room_message_idx ON feed_images (room, message_id);
CREATE INDEX feed_images_room_message_id_idx ON feed_images (room, message_id)
WHERE message_id IS NULL;
CREATE TABLE invalid_feed_images (
image_url text NOT NULL,
reason text NOT NULL,
added_on timestamp NOT NULL DEFAULT now(),
PRIMARY KEY (image_url)
);
CREATE INDEX invalid_feed_images_idx ON invalid_feed_images (image_url);
-- dont add this yet
CREATE TABLE avatars (
avatar_id SERIAL PRIMARY KEY,
user_id NOT NULL REFERENCES users,
created_on timestamp NOT NULL DEFAULT now(),
type text,
url text,
w integer,
h integer,
thumb text,
tw integer,
th integer
);
-- dont add this yet
CREATE TABLE images (
image_id SERIAL PRIMARY KEY,
);
|
