Skip to content

Instantly share code, notes, and snippets.

@andrisasuke

andrisasuke/create room message table

Last active January 4, 2018 08:36
Show Gist options
  • Save andrisasuke/698e26e81fca63dca9f21d2da9252ee8 to your computer and use it in GitHub Desktop.
Save andrisasuke/698e26e81fca63dca9f21d2da9252ee8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
create room message table
CREATE TABLE room_messages (
id serial NOT NULL,
room_name varchar(255) NOT NULL,
request_id INT NOT NULL,
requester_id INT NOT NULL,
traveller_id INT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE ,
created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
updated_at TIMESTAMP(0) WITHOUT TIME ZONE,
CONSTRAINT room_messages_pk PRIMARY KEY (id),
CONSTRAINT room_messages_name_unique UNIQUE (room_name)
);
CREATE INDEX room_msg_name_idx ON room_messages (room_name);
CREATE INDEX room_msg_req_id_requester_traveller_idx ON room_messages (request_id, requester_id, traveller_id);
CREATE INDEX room_msg_requester_id_idx ON room_messages (requester_id);
CREATE INDEX room_msg_traveller_id_idx ON room_messages (traveller_id);
CREATE INDEX room_msg_updated_at_idx ON room_messages (updated_at);
CREATE TABLE message_histories (
id serial NOT NULL,
message_id VARCHAR(255) NOT NULL,
room_id INT NOT NULL,
from_id INT NOT NULL,
to_id INT NOT NULL,
body VARCHAR(2048) NOT NULL,
message_type VARCHAR(255) NOT NULL,
created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
updated_at TIMESTAMP(0) WITHOUT TIME ZONE,
CONSTRAINT message_histories_pk PRIMARY KEY (id),
CONSTRAINT message_histories_msgid_unique UNIQUE (message_id)
);
CREATE INDEX message_histories_msgid_idx ON message_histories (message_id);
CREATE INDEX message_histories_room_id_idx ON message_histories (room_id);
CREATE INDEX message_histories_created_at_idx ON message_histories (created_at);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment