gmessages/database/upgrades/00-latest-revision.sql

89 lines
3 KiB
MySQL
Raw Permalink Normal View History

-- v0 -> v10 (compatible with v9+): Latest revision
CREATE TABLE "user" (
-- only: postgres
rowid BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-- only: sqlite
rowid INTEGER PRIMARY KEY,
mxid TEXT NOT NULL UNIQUE,
phone_id TEXT UNIQUE,
session jsonb,
self_participant_ids jsonb NOT NULL DEFAULT '[]',
sim_metadata jsonb NOT NULL DEFAULT '{}',
2023-08-30 17:45:14 +00:00
settings jsonb NOT NULL DEFAULT '{}',
management_room TEXT,
space_room TEXT,
access_token TEXT
);
CREATE TABLE puppet (
id TEXT NOT NULL,
receiver BIGINT NOT NULL,
phone TEXT,
2023-09-04 22:18:01 +00:00
contact_id TEXT NOT NULL,
name TEXT NOT NULL,
name_set BOOLEAN NOT NULL DEFAULT false,
2023-09-04 22:18:01 +00:00
avatar_hash bytea CHECK ( LENGTH(avatar_hash) = 32 ),
avatar_update_ts BIGINT NOT NULL,
avatar_mxc TEXT NOT NULL,
avatar_set BOOLEAN NOT NULL DEFAULT false,
contact_info_set BOOLEAN NOT NULL DEFAULT false,
2023-07-11 22:57:07 +00:00
PRIMARY KEY (id, receiver),
CONSTRAINT puppet_user_fkey FOREIGN KEY (receiver) REFERENCES "user"(rowid) ON DELETE CASCADE,
CONSTRAINT puppet_phone_unique UNIQUE (phone, receiver)
);
CREATE TABLE portal (
id TEXT NOT NULL,
receiver BIGINT NOT NULL,
self_user TEXT,
other_user TEXT,
type INTEGER NOT NULL,
send_mode INTEGER NOT NULL,
force_rcs BOOLEAN NOT NULL DEFAULT false,
mxid TEXT UNIQUE,
name TEXT NOT NULL,
name_set BOOLEAN NOT NULL DEFAULT false,
encrypted BOOLEAN NOT NULL DEFAULT false,
in_space BOOLEAN NOT NULL DEFAULT false,
2023-07-11 22:57:07 +00:00
PRIMARY KEY (id, receiver),
CONSTRAINT portal_user_fkey FOREIGN KEY (receiver) REFERENCES "user"(rowid) ON DELETE CASCADE,
CONSTRAINT portal_puppet_fkey FOREIGN KEY (other_user, receiver) REFERENCES puppet(id, receiver) ON DELETE CASCADE
);
2023-09-04 22:58:57 +00:00
CREATE INDEX portal_other_user_idx ON portal(receiver, other_user);
CREATE TABLE message (
conv_id TEXT NOT NULL,
conv_receiver BIGINT NOT NULL,
id TEXT NOT NULL,
mxid TEXT NOT NULL,
mx_room TEXT NOT NULL,
sender TEXT NOT NULL,
timestamp BIGINT NOT NULL,
2023-07-11 22:57:07 +00:00
status jsonb NOT NULL,
PRIMARY KEY (conv_receiver, id),
CONSTRAINT message_portal_fkey FOREIGN KEY (conv_id, conv_receiver) REFERENCES portal(id, receiver) ON DELETE CASCADE,
CONSTRAINT message_mxid_unique UNIQUE (mxid)
);
CREATE INDEX message_conv_timestamp_idx ON message(conv_id, conv_receiver, timestamp);
CREATE TABLE reaction (
conv_id TEXT NOT NULL,
conv_receiver BIGINT NOT NULL,
msg_id TEXT NOT NULL,
sender TEXT NOT NULL,
reaction TEXT NOT NULL,
mxid TEXT NOT NULL,
PRIMARY KEY (conv_receiver, msg_id, sender),
CONSTRAINT reaction_message_fkey FOREIGN KEY (conv_receiver, msg_id) REFERENCES message(conv_receiver, id) ON DELETE CASCADE,
CONSTRAINT reaction_mxid_unique UNIQUE (mxid)
)