diff options
Diffstat (limited to 'db/0-create.psql')
| -rw-r--r-- | db/0-create.psql | 24 |
1 files changed, 19 insertions, 5 deletions
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); |