diff options
Diffstat (limited to 'db')
| -rwxr-xr-x | db/0-create.psql | 13 |
1 files changed, 13 insertions, 0 deletions
diff --git a/db/0-create.psql b/db/0-create.psql index ac0dac6..7c6b4b0 100755 --- a/db/0-create.psql +++ b/db/0-create.psql @@ -28,10 +28,23 @@ CREATE TABLE messages ( is_image bool NOT NULL ); +-- Queries to support: +-- 1) What are my favorite images? (By room, time, or author) +-- 2) Who favorited me? (By user, image, or time) +-- 3) What are the most favorited images? (By room, time, or author) +CREATE TABLE favorites ( + favorite_id SERIAL PRIMARY KEY, + src_user_id integer NOT NULL REFERENCES users, + message_id integer NOT NULL REFERENCES messages, + created_on timestamp NOT NULL DEFAULT now() +); + 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); +CREATE INDEX src_user_id_idx ON favorites (src_user_id); + INSERT INTO rooms (key, name, description, admin_only) VALUES ('RoomA', 'Room A', 'Hangout', false); INSERT INTO rooms (key, name, description, admin_only) |