60 lines
2.4 KiB
SQL
60 lines
2.4 KiB
SQL
-- v10: Change prekey store to use service IDs instead of UUID kind column
|
|
CREATE TABLE new_signalmeow_pre_keys (
|
|
account_id TEXT NOT NULL,
|
|
service_id TEXT NOT NULL,
|
|
key_id INTEGER NOT NULL,
|
|
is_signed BOOLEAN NOT NULL,
|
|
key_pair bytea NOT NULL,
|
|
|
|
PRIMARY KEY (account_id, service_id, is_signed, key_id),
|
|
FOREIGN KEY (account_id) REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
INSERT INTO new_signalmeow_pre_keys (account_id, service_id, key_id, is_signed, key_pair)
|
|
SELECT aci_uuid, CASE WHEN uuid_kind='pni' THEN 'PNI:'||(
|
|
SELECT pni_uuid FROM signalmeow_device WHERE signalmeow_device.aci_uuid=signalmeow_pre_keys.aci_uuid
|
|
) ELSE aci_uuid END, key_id, is_signed, key_pair
|
|
FROM signalmeow_pre_keys;
|
|
|
|
DROP TABLE signalmeow_pre_keys;
|
|
ALTER TABLE new_signalmeow_pre_keys RENAME TO signalmeow_pre_keys;
|
|
|
|
|
|
CREATE TABLE new_signalmeow_kyber_pre_keys (
|
|
account_id TEXT NOT NULL,
|
|
service_id TEXT NOT NULL,
|
|
key_id INTEGER NOT NULL,
|
|
key_pair bytea NOT NULL,
|
|
is_last_resort BOOLEAN NOT NULL,
|
|
|
|
PRIMARY KEY (account_id, service_id, key_id),
|
|
FOREIGN KEY (account_id) REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
INSERT INTO new_signalmeow_kyber_pre_keys (account_id, service_id, key_id, key_pair, is_last_resort)
|
|
SELECT aci_uuid, CASE WHEN uuid_kind='pni' THEN 'PNI:'||(
|
|
SELECT pni_uuid FROM signalmeow_device WHERE signalmeow_device.aci_uuid=signalmeow_kyber_pre_keys.aci_uuid
|
|
) ELSE aci_uuid END, key_id, key_pair, is_last_resort
|
|
FROM signalmeow_kyber_pre_keys;
|
|
|
|
DROP TABLE signalmeow_kyber_pre_keys;
|
|
ALTER TABLE new_signalmeow_kyber_pre_keys RENAME TO signalmeow_kyber_pre_keys;
|
|
|
|
|
|
CREATE TABLE new_signalmeow_sessions (
|
|
account_id TEXT NOT NULL,
|
|
service_id TEXT NOT NULL,
|
|
their_service_id TEXT NOT NULL,
|
|
their_device_id INTEGER NOT NULL,
|
|
record bytea NOT NULL,
|
|
|
|
PRIMARY KEY (account_id, service_id, their_service_id, their_device_id),
|
|
FOREIGN KEY (account_id) REFERENCES signalmeow_device (aci_uuid) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO new_signalmeow_sessions (account_id, service_id, their_service_id, their_device_id, record)
|
|
SELECT our_aci_uuid, our_aci_uuid, their_service_id, their_device_id, record
|
|
FROM signalmeow_sessions;
|
|
|
|
DROP TABLE signalmeow_sessions;
|
|
ALTER TABLE new_signalmeow_sessions RENAME TO signalmeow_sessions;
|