From f9024ac302e7387cccc821174ec4c68e25251af3 Mon Sep 17 00:00:00 2001 From: sostler Date: Sat, 5 Jun 2010 03:17:50 -0400 Subject: Updated sql create script --- db/0-create.psql | 24 +++++++++++++++++++----- 1 file changed, 19 insertions(+), 5 deletions(-) (limited to 'db') diff --git a/db/0-create.psql b/db/0-create.psql index d06fde4..01831e5 100644 --- a/db/0-create.psql +++ b/db/0-create.psql @@ -78,16 +78,30 @@ INSERT INTO rooms (key, name, description, admin_only) 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_id integer REFERENCES rooms, + room text NOT NULL, message_id integer REFERENCES messages, - added_on timestamp NOT NULL DEFAULT now(), - UNIQUE (room_id, image_url) + queued_on timestamp NOT NULL DEFAULT now(), + UNIQUE (room, image_url) ); -CREATE INDEX feed_images_url_idx ON feed_images (image_url); -CREATE INDEX feed_images_url_room_idx ON feed_images (image_url, room_id); +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 TABLE invalid_feed_images ( + image_url text NOT NULL, + reason text NOT NULL, + added_on timestamp NOT NULL DEFAULT now(), + UNIQUE (image_url) +); +CREATE INDEX invalid_feed_images_idx ON invalid_feed_images (image_url); -- cgit v1.2.3-70-g09d2