From a91eb914edb95a5facd62b2eac00186b7b8c63c1 Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Tue, 19 Jan 2010 23:25:29 -0500 Subject: Getting ready for commit --- db/0-create.psql | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'db') 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) -- cgit v1.2.3-70-g09d2