53 lines
2.4 KiB
SQL
53 lines
2.4 KiB
SQL
-- v19 (compatible with v13+): Add tables for caching parsed backup data
|
|
CREATE TABLE signalmeow_backup_recipient (
|
|
account_id TEXT NOT NULL,
|
|
recipient_id BIGINT NOT NULL,
|
|
|
|
aci_uuid TEXT,
|
|
pni_uuid TEXT,
|
|
|
|
group_master_key TEXT,
|
|
|
|
data bytea NOT NULL,
|
|
|
|
PRIMARY KEY (account_id, recipient_id),
|
|
CONSTRAINT signalmeow_backup_recipient_device_fkey FOREIGN KEY (account_id)
|
|
REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
CREATE INDEX signalmeow_backup_recipient_group_idx ON signalmeow_backup_recipient (account_id, group_master_key);
|
|
CREATE INDEX signalmeow_backup_recipient_aci_idx ON signalmeow_backup_recipient (account_id, aci_uuid);
|
|
|
|
CREATE TABLE signalmeow_backup_chat (
|
|
account_id TEXT NOT NULL,
|
|
chat_id BIGINT NOT NULL,
|
|
recipient_id BIGINT NOT NULL,
|
|
data bytea NOT NULL,
|
|
|
|
latest_message_id BIGINT,
|
|
total_message_count INTEGER,
|
|
|
|
PRIMARY KEY (account_id, chat_id),
|
|
CONSTRAINT signalmeow_backup_chat_device_fkey FOREIGN KEY (account_id)
|
|
REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT signalmeow_backup_chat_recipient_fkey FOREIGN KEY (account_id, recipient_id)
|
|
REFERENCES signalmeow_backup_recipient (account_id, recipient_id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
CREATE INDEX signalmeow_backup_chat_recipient_id_idx ON signalmeow_backup_chat (account_id, recipient_id);
|
|
|
|
CREATE TABLE signalmeow_backup_message (
|
|
account_id TEXT NOT NULL,
|
|
chat_id BIGINT NOT NULL,
|
|
sender_id BIGINT NOT NULL,
|
|
message_id BIGINT NOT NULL,
|
|
data bytea NOT NULL,
|
|
|
|
PRIMARY KEY (account_id, sender_id, message_id),
|
|
CONSTRAINT signalmeow_backup_message_chat_fkey FOREIGN KEY (account_id, chat_id)
|
|
REFERENCES signalmeow_backup_chat (account_id, chat_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT signalmeow_backup_message_sender_fkey FOREIGN KEY (account_id, sender_id)
|
|
REFERENCES signalmeow_backup_recipient (account_id, recipient_id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT signalmeow_backup_message_device_fkey FOREIGN KEY (account_id)
|
|
REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
CREATE INDEX signalmeow_backup_message_chat_id_idx ON signalmeow_backup_message (account_id, chat_id);
|