You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 

96 lines
2.9 KiB

CREATE TABLE servers (
id SERIAL PRIMARY KEY,
domain VARCHAR UNIQUE NOT NULL
);
INSERT INTO servers (domain) VALUES ('*'); -- create the local server object
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR NOT NULL,
server INT NOT NULL REFERENCES servers ON DELETE RESTRICT,
UNIQUE(username, server)
);
CREATE TABLE local_users (
user_id INT UNIQUE NOT NULL REFERENCES users ON DELETE CASCADE,
password VARCHAR NOT NULL,
email VARCHAR NOT NULL
);
CREATE TABLE local_user_devices (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
user_id INT NOT NULL REFERENCES users ON DELETE CASCADE,
highest_seq INT NOT NULL DEFAULT 0,
UNIQUE(user_id, name)
);
CREATE TABLE groupchats (
uuid UUID PRIMARY KEY,
state_ver INT NOT NULL DEFAULT 0,
subject VARCHAR,
invite_only BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE groupchat_directory_entries (
name VARCHAR UNIQUE NOT NULL,
uuid UUID NOT NULL REFERENCES groupchats
);
CREATE TABLE groupchat_roles (
role_id SERIAL PRIMARY KEY,
groupchat_uuid UUID NOT NULL REFERENCES groupchats,
role_name VARCHAR NOT NULL,
UNIQUE(groupchat_uuid, role_name)
);
CREATE TABLE groupchat_role_capabilities (
role_id INT NOT NULL REFERENCES groupchat_roles,
capability VARCHAR NOT NULL,
UNIQUE(role_id, capability)
);
CREATE TABLE groupchat_sponsoring_servers (
groupchat_uuid UUID NOT NULL REFERENCES groupchats,
server_id INT NOT NULL REFERENCES servers,
PRIMARY KEY(groupchat_uuid, server_id)
);
CREATE TABLE groupchat_memberships (
groupchat_uuid UUID NOT NULL REFERENCES groupchats,
user_id INT NOT NULL REFERENCES users,
role_id INT NOT NULL REFERENCES groupchat_roles,
PRIMARY KEY(groupchat_uuid, user_id)
);
CREATE INDEX groupchat_memberships_user_id ON groupchat_memberships (user_id);
CREATE SEQUENCE messages_sequence;
CREATE TABLE messages (
msgid UUID PRIMARY KEY,
seq INT NOT NULL DEFAULT nextval('messages_sequence'),
user_from INT NOT NULL REFERENCES users,
user_to INT REFERENCES users,
groupchat_to UUID REFERENCES groupchats,
tags VARCHAR NOT NULL DEFAULT '',
body VARCHAR NOT NULL DEFAULT '',
origin_ts TIMESTAMP NOT NULL,
local_ts TIMESTAMP NOT NULL,
is_notice BOOLEAN NOT NULL DEFAULT false,
CHECK((user_to IS NULL) != (groupchat_to IS NULL))
);
CREATE TABLE system_messages (
msgid UUID PRIMARY KEY,
seq INT NOT NULL DEFAULT nextval('messages_sequence'),
type INT NOT NULL,
user_ref INT REFERENCES users,
target_user_ref INT REFERENCES users,
groupchat_ref UUID REFERENCES groupchats,
body VARCHAR,
local_ts TIMESTAMP NOT NULL
);
CREATE INDEX messages_user_to ON messages (user_to);