diff options
| -rw-r--r-- | db/0-create.psql-altered | 165 |
1 files changed, 165 insertions, 0 deletions
diff --git a/db/0-create.psql-altered b/db/0-create.psql-altered new file mode 100644 index 0000000..cb1469b --- /dev/null +++ b/db/0-create.psql-altered @@ -0,0 +1,165 @@ +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)); + +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, + history_size integer NOT NULL DEFAULT 25, + max_file_size integer NOT NULL DEFAULT 1048576, + max_image_width integer NOT NULL DEFAULT 2000, + max_image_height integer NOT NULL DEFAULT 2000 +); + +-- ALTER TABLE rooms ADD COLUMN history_size integer NOT NULL DEFAULT 25; +-- ALTER TABLE rooms ADD COLUMN max_file_size integer NOT NULL DEFAULT 1048576; +-- ALTER TABLE rooms ADD COLUMN max_image_width integer NOT NULL DEFAULT 2000; +-- ALTER TABLE rooms ADD COLUMN max_image_height integer NOT NULL DEFAULT 2000; + +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); + +CREATE TABLE image_altars ( + message_id integer NOT NULL, + user_id integer NOT NULL, + PRIMARY KEY(message_id) +); + +CREATE TABLE image_urls ( + url text, + last_posted timestamp NOT NULL DEFAULT now(), + PRIMARY KEY(url) +); + +-- 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); + +CREATE TABLE direct_messages ( + dm_id SERIAL PRIMARY KEY, + message_id integer NOT NULL REFERENCES messages, + author_id integer NOT NULL REFERENCES users, + recip_id integer NOT NULL REFERENCES users +); + +-- dont add this yet +CREATE TABLE avatars ( + avatar_id SERIAL PRIMARY KEY, + user_id integer 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 + +); |