1 package org
.asamk
.signal
.manager
.storage
;
3 import com
.zaxxer
.hikari
.HikariDataSource
;
5 import org
.asamk
.signal
.manager
.api
.Pair
;
6 import org
.asamk
.signal
.manager
.storage
.groups
.GroupStore
;
7 import org
.asamk
.signal
.manager
.storage
.identities
.IdentityKeyStore
;
8 import org
.asamk
.signal
.manager
.storage
.keyValue
.KeyValueStore
;
9 import org
.asamk
.signal
.manager
.storage
.prekeys
.KyberPreKeyStore
;
10 import org
.asamk
.signal
.manager
.storage
.prekeys
.PreKeyStore
;
11 import org
.asamk
.signal
.manager
.storage
.prekeys
.SignedPreKeyStore
;
12 import org
.asamk
.signal
.manager
.storage
.recipients
.CdsiStore
;
13 import org
.asamk
.signal
.manager
.storage
.recipients
.RecipientStore
;
14 import org
.asamk
.signal
.manager
.storage
.sendLog
.MessageSendLogStore
;
15 import org
.asamk
.signal
.manager
.storage
.senderKeys
.SenderKeyRecordStore
;
16 import org
.asamk
.signal
.manager
.storage
.senderKeys
.SenderKeySharedStore
;
17 import org
.asamk
.signal
.manager
.storage
.sessions
.SessionStore
;
18 import org
.asamk
.signal
.manager
.storage
.stickers
.StickerStore
;
19 import org
.slf4j
.Logger
;
20 import org
.slf4j
.LoggerFactory
;
21 import org
.whispersystems
.signalservice
.api
.push
.ServiceId
;
22 import org
.whispersystems
.signalservice
.api
.push
.ServiceId
.ACI
;
23 import org
.whispersystems
.signalservice
.api
.util
.UuidUtil
;
26 import java
.sql
.Connection
;
27 import java
.sql
.SQLException
;
28 import java
.util
.HashMap
;
29 import java
.util
.Optional
;
30 import java
.util
.UUID
;
32 public class AccountDatabase
extends Database
{
34 private static final Logger logger
= LoggerFactory
.getLogger(AccountDatabase
.class);
35 private static final long DATABASE_VERSION
= 20;
37 private AccountDatabase(final HikariDataSource dataSource
) {
38 super(logger
, DATABASE_VERSION
, dataSource
);
41 public static AccountDatabase
init(File databaseFile
) throws SQLException
{
42 return initDatabase(databaseFile
, AccountDatabase
::new);
46 protected void createDatabase(final Connection connection
) throws SQLException
{
47 RecipientStore
.createSql(connection
);
48 MessageSendLogStore
.createSql(connection
);
49 StickerStore
.createSql(connection
);
50 PreKeyStore
.createSql(connection
);
51 SignedPreKeyStore
.createSql(connection
);
52 KyberPreKeyStore
.createSql(connection
);
53 GroupStore
.createSql(connection
);
54 SessionStore
.createSql(connection
);
55 IdentityKeyStore
.createSql(connection
);
56 SenderKeyRecordStore
.createSql(connection
);
57 SenderKeySharedStore
.createSql(connection
);
58 KeyValueStore
.createSql(connection
);
59 CdsiStore
.createSql(connection
);
60 UnknownStorageIdStore
.createSql(connection
);
64 protected void upgradeDatabase(final Connection connection
, final long oldVersion
) throws SQLException
{
66 logger
.debug("Updating database: Creating recipient table");
67 try (final var statement
= connection
.createStatement()) {
68 statement
.executeUpdate("""
69 CREATE TABLE recipient (
70 _id INTEGER PRIMARY KEY AUTOINCREMENT,
74 profile_key_credential BLOB,
80 expiration_time INTEGER NOT NULL DEFAULT 0,
81 blocked INTEGER NOT NULL DEFAULT FALSE,
82 archived INTEGER NOT NULL DEFAULT FALSE,
83 profile_sharing INTEGER NOT NULL DEFAULT FALSE,
85 profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
86 profile_given_name TEXT,
87 profile_family_name TEXT,
89 profile_about_emoji TEXT,
90 profile_avatar_url_path TEXT,
91 profile_mobile_coin_address BLOB,
92 profile_unidentified_access_mode TEXT,
93 profile_capabilities TEXT
99 logger
.debug("Updating database: Creating sticker table");
100 try (final var statement
= connection
.createStatement()) {
101 statement
.executeUpdate("""
102 CREATE TABLE sticker (
103 _id INTEGER PRIMARY KEY,
104 pack_id BLOB UNIQUE NOT NULL,
105 pack_key BLOB NOT NULL,
106 installed INTEGER NOT NULL DEFAULT FALSE
111 if (oldVersion
< 4) {
112 logger
.debug("Updating database: Creating pre key tables");
113 try (final var statement
= connection
.createStatement()) {
114 statement
.executeUpdate("""
115 CREATE TABLE signed_pre_key (
116 _id INTEGER PRIMARY KEY,
117 account_id_type INTEGER NOT NULL,
118 key_id INTEGER NOT NULL,
119 public_key BLOB NOT NULL,
120 private_key BLOB NOT NULL,
121 signature BLOB NOT NULL,
122 timestamp INTEGER DEFAULT 0,
123 UNIQUE(account_id_type, key_id)
125 CREATE TABLE pre_key (
126 _id INTEGER PRIMARY KEY,
127 account_id_type INTEGER NOT NULL,
128 key_id INTEGER NOT NULL,
129 public_key BLOB NOT NULL,
130 private_key BLOB NOT NULL,
131 UNIQUE(account_id_type, key_id)
136 if (oldVersion
< 5) {
137 logger
.debug("Updating database: Creating group tables");
138 try (final var statement
= connection
.createStatement()) {
139 statement
.executeUpdate("""
140 CREATE TABLE group_v2 (
141 _id INTEGER PRIMARY KEY,
142 group_id BLOB UNIQUE NOT NULL,
143 master_key BLOB NOT NULL,
145 distribution_id BLOB UNIQUE NOT NULL,
146 blocked INTEGER NOT NULL DEFAULT FALSE,
147 permission_denied INTEGER NOT NULL DEFAULT FALSE
149 CREATE TABLE group_v1 (
150 _id INTEGER PRIMARY KEY,
151 group_id BLOB UNIQUE NOT NULL,
152 group_id_v2 BLOB UNIQUE,
155 expiration_time INTEGER NOT NULL DEFAULT 0,
156 blocked INTEGER NOT NULL DEFAULT FALSE,
157 archived INTEGER NOT NULL DEFAULT FALSE
159 CREATE TABLE group_v1_member (
160 _id INTEGER PRIMARY KEY,
161 group_id INTEGER NOT NULL REFERENCES group_v1 (_id) ON DELETE CASCADE,
162 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
163 UNIQUE(group_id, recipient_id)
168 if (oldVersion
< 6) {
169 logger
.debug("Updating database: Creating session tables");
170 try (final var statement
= connection
.createStatement()) {
171 statement
.executeUpdate("""
172 CREATE TABLE session (
173 _id INTEGER PRIMARY KEY,
174 account_id_type INTEGER NOT NULL,
175 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
176 device_id INTEGER NOT NULL,
177 record BLOB NOT NULL,
178 UNIQUE(account_id_type, recipient_id, device_id)
183 if (oldVersion
< 7) {
184 logger
.debug("Updating database: Creating identity table");
185 try (final var statement
= connection
.createStatement()) {
186 statement
.executeUpdate("""
187 CREATE TABLE identity (
188 _id INTEGER PRIMARY KEY,
189 recipient_id INTEGER UNIQUE NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
190 identity_key BLOB NOT NULL,
191 added_timestamp INTEGER NOT NULL,
192 trust_level INTEGER NOT NULL
197 if (oldVersion
< 8) {
198 logger
.debug("Updating database: Creating sender key tables");
199 try (final var statement
= connection
.createStatement()) {
200 statement
.executeUpdate("""
201 CREATE TABLE sender_key (
202 _id INTEGER PRIMARY KEY,
203 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
204 device_id INTEGER NOT NULL,
205 distribution_id BLOB NOT NULL,
206 record BLOB NOT NULL,
207 created_timestamp INTEGER NOT NULL,
208 UNIQUE(recipient_id, device_id, distribution_id)
210 CREATE TABLE sender_key_shared (
211 _id INTEGER PRIMARY KEY,
212 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
213 device_id INTEGER NOT NULL,
214 distribution_id BLOB NOT NULL,
215 timestamp INTEGER NOT NULL,
216 UNIQUE(recipient_id, device_id, distribution_id)
221 if (oldVersion
< 9) {
222 logger
.debug("Updating database: Adding urgent field");
223 try (final var statement
= connection
.createStatement()) {
224 statement
.executeUpdate("""
225 ALTER TABLE message_send_log_content ADD COLUMN urgent INTEGER NOT NULL DEFAULT TRUE;
229 if (oldVersion
< 10) {
230 logger
.debug("Updating database: Key tables on serviceId instead of recipientId");
231 try (final var statement
= connection
.createStatement()) {
232 statement
.executeUpdate("""
233 CREATE TABLE identity2 (
234 _id INTEGER PRIMARY KEY,
235 uuid BLOB UNIQUE NOT NULL,
236 identity_key BLOB NOT NULL,
237 added_timestamp INTEGER NOT NULL,
238 trust_level INTEGER NOT NULL
240 INSERT INTO identity2 (_id, uuid, identity_key, added_timestamp, trust_level)
241 SELECT i._id, r.uuid, i.identity_key, i.added_timestamp, i.trust_level
242 FROM identity i LEFT JOIN recipient r ON i.recipient_id = r._id
243 WHERE uuid IS NOT NULL;
245 ALTER TABLE identity2 RENAME TO identity;
247 DROP INDEX msl_recipient_index;
248 ALTER TABLE message_send_log ADD COLUMN uuid BLOB;
249 UPDATE message_send_log
251 FROM message_send_log i, (SELECT _id, uuid FROM recipient) AS r
252 WHERE i.recipient_id = r._id;
253 DELETE FROM message_send_log WHERE uuid IS NULL;
254 ALTER TABLE message_send_log DROP COLUMN recipient_id;
255 CREATE INDEX msl_recipient_index ON message_send_log (uuid, device_id, content_id);
257 CREATE TABLE sender_key2 (
258 _id INTEGER PRIMARY KEY,
260 device_id INTEGER NOT NULL,
261 distribution_id BLOB NOT NULL,
262 record BLOB NOT NULL,
263 created_timestamp INTEGER NOT NULL,
264 UNIQUE(uuid, device_id, distribution_id)
266 INSERT INTO sender_key2 (_id, uuid, device_id, distribution_id, record, created_timestamp)
267 SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.record, s.created_timestamp
268 FROM sender_key s LEFT JOIN recipient r ON s.recipient_id = r._id
269 WHERE uuid IS NOT NULL;
270 DROP TABLE sender_key;
271 ALTER TABLE sender_key2 RENAME TO sender_key;
273 CREATE TABLE sender_key_shared2 (
274 _id INTEGER PRIMARY KEY,
276 device_id INTEGER NOT NULL,
277 distribution_id BLOB NOT NULL,
278 timestamp INTEGER NOT NULL,
279 UNIQUE(uuid, device_id, distribution_id)
281 INSERT INTO sender_key_shared2 (_id, uuid, device_id, distribution_id, timestamp)
282 SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.timestamp
283 FROM sender_key_shared s LEFT JOIN recipient r ON s.recipient_id = r._id
284 WHERE uuid IS NOT NULL;
285 DROP TABLE sender_key_shared;
286 ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
288 CREATE TABLE session2 (
289 _id INTEGER PRIMARY KEY,
290 account_id_type INTEGER NOT NULL,
292 device_id INTEGER NOT NULL,
293 record BLOB NOT NULL,
294 UNIQUE(account_id_type, uuid, device_id)
296 INSERT INTO session2 (_id, account_id_type, uuid, device_id, record)
297 SELECT s._id, s.account_id_type, r.uuid, s.device_id, s.record
298 FROM session s LEFT JOIN recipient r ON s.recipient_id = r._id
299 WHERE uuid IS NOT NULL;
301 ALTER TABLE session2 RENAME TO session;
305 if (oldVersion
< 11) {
306 logger
.debug("Updating database: Adding pni field");
307 try (final var statement
= connection
.createStatement()) {
308 statement
.executeUpdate("""
309 ALTER TABLE recipient ADD COLUMN pni BLOB;
313 if (oldVersion
< 12) {
314 logger
.debug("Updating database: Adding username field");
315 try (final var statement
= connection
.createStatement()) {
316 statement
.executeUpdate("""
317 ALTER TABLE recipient ADD COLUMN username TEXT;
321 if (oldVersion
< 13) {
322 logger
.debug("Updating database: Cleanup unknown service ids");
325 DELETE FROM identity AS i
328 try (final var statement
= connection
.prepareStatement(sql
)) {
329 statement
.setBytes(1, ACI
.UNKNOWN
.toByteArray());
330 statement
.executeUpdate();
335 DELETE FROM sender_key_shared AS i
338 try (final var statement
= connection
.prepareStatement(sql
)) {
339 statement
.setBytes(1, ACI
.UNKNOWN
.toByteArray());
340 statement
.executeUpdate();
344 if (oldVersion
< 14) {
345 logger
.debug("Updating database: Creating kyber_pre_key table");
347 try (final var statement
= connection
.createStatement()) {
348 statement
.executeUpdate("""
349 CREATE TABLE kyber_pre_key (
350 _id INTEGER PRIMARY KEY,
351 account_id_type INTEGER NOT NULL,
352 key_id INTEGER NOT NULL,
353 serialized BLOB NOT NULL,
354 is_last_resort INTEGER NOT NULL,
355 UNIQUE(account_id_type, key_id)
361 if (oldVersion
< 15) {
362 logger
.debug("Updating database: Store serviceId as TEXT");
363 try (final var statement
= connection
.createStatement()) {
364 statement
.executeUpdate("""
365 CREATE TABLE tmp_mapping_table (
367 address TEXT NOT NULL
377 final var uuidAddressMapping
= new HashMap
<UUID
, ServiceId
>();
378 try (final var preparedStatement
= connection
.prepareStatement(sql
)) {
379 try (var result
= Utils
.executeQueryForStream(preparedStatement
, (resultSet
) -> {
380 final var pni
= Optional
.ofNullable(resultSet
.getBytes("pni"))
381 .map(UuidUtil
::parseOrNull
)
382 .map(ServiceId
.PNI
::from
);
383 final var serviceIdUuid
= Optional
.ofNullable(resultSet
.getBytes("uuid"))
384 .map(UuidUtil
::parseOrNull
);
385 final var serviceId
= serviceIdUuid
.isPresent() && pni
.isPresent() && serviceIdUuid
.get()
386 .equals(pni
.get().getRawUuid())
387 ? pni
.<ServiceId
>map(p
-> p
)
388 : serviceIdUuid
.<ServiceId
>map(ACI
::from
);
390 return new Pair
<>(serviceId
, pni
);
392 result
.forEach(p
-> {
393 final var serviceId
= p
.first();
394 final var pni
= p
.second();
395 if (serviceId
.isPresent()) {
396 uuidAddressMapping
.put(serviceId
.get().getRawUuid(), serviceId
.get());
398 if (pni
.isPresent()) {
399 uuidAddressMapping
.put(pni
.get().getRawUuid(), pni
.get());
405 final var insertSql
= """
406 INSERT INTO tmp_mapping_table (uuid, address)
409 try (final var insertStatement
= connection
.prepareStatement(insertSql
)) {
410 for (final var entry
: uuidAddressMapping
.entrySet()) {
411 final var uuid
= entry
.getKey();
412 final var serviceId
= entry
.getValue();
413 insertStatement
.setBytes(1, UuidUtil
.toByteArray(uuid
));
414 insertStatement
.setString(2, serviceId
.toString());
415 insertStatement
.execute();
419 statement
.executeUpdate("""
420 CREATE TABLE identity2 (
421 _id INTEGER PRIMARY KEY,
422 address TEXT UNIQUE NOT NULL,
423 identity_key BLOB NOT NULL,
424 added_timestamp INTEGER NOT NULL,
425 trust_level INTEGER NOT NULL
427 INSERT INTO identity2 (_id, address, identity_key, added_timestamp, trust_level)
428 SELECT i._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = i.uuid) address, i.identity_key, i.added_timestamp, i.trust_level
430 WHERE address IS NOT NULL;
432 ALTER TABLE identity2 RENAME TO identity;
434 CREATE TABLE message_send_log2 (
435 _id INTEGER PRIMARY KEY,
436 content_id INTEGER NOT NULL REFERENCES message_send_log_content (_id) ON DELETE CASCADE,
437 address TEXT NOT NULL,
438 device_id INTEGER NOT NULL
440 INSERT INTO message_send_log2 (_id, content_id, address, device_id)
441 SELECT m._id, m.content_id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = m.uuid) address, m.device_id
442 FROM message_send_log m
443 WHERE address IS NOT NULL;
444 DROP INDEX msl_recipient_index;
445 DROP INDEX msl_content_index;
446 DROP TABLE message_send_log;
447 ALTER TABLE message_send_log2 RENAME TO message_send_log;
448 CREATE INDEX msl_recipient_index ON message_send_log (address, device_id, content_id);
449 CREATE INDEX msl_content_index ON message_send_log (content_id);
451 CREATE TABLE sender_key2 (
452 _id INTEGER PRIMARY KEY,
453 address TEXT NOT NULL,
454 device_id INTEGER NOT NULL,
455 distribution_id BLOB NOT NULL,
456 record BLOB NOT NULL,
457 created_timestamp INTEGER NOT NULL,
458 UNIQUE(address, device_id, distribution_id)
460 INSERT INTO sender_key2 (_id, address, device_id, distribution_id, record, created_timestamp)
461 SELECT s._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.distribution_id, s.record, s.created_timestamp
463 WHERE address IS NOT NULL;
464 DROP TABLE sender_key;
465 ALTER TABLE sender_key2 RENAME TO sender_key;
467 CREATE TABLE sender_key_shared2 (
468 _id INTEGER PRIMARY KEY,
469 address TEXT NOT NULL,
470 device_id INTEGER NOT NULL,
471 distribution_id BLOB NOT NULL,
472 timestamp INTEGER NOT NULL,
473 UNIQUE(address, device_id, distribution_id)
475 INSERT INTO sender_key_shared2 (_id, address, device_id, distribution_id, timestamp)
476 SELECT s._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.distribution_id, s.timestamp
477 FROM sender_key_shared s
478 WHERE address IS NOT NULL;
479 DROP TABLE sender_key_shared;
480 ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
482 CREATE TABLE session2 (
483 _id INTEGER PRIMARY KEY,
484 account_id_type INTEGER NOT NULL,
485 address TEXT NOT NULL,
486 device_id INTEGER NOT NULL,
487 record BLOB NOT NULL,
488 UNIQUE(account_id_type, address, device_id)
490 INSERT INTO session2 (_id, account_id_type, address, device_id, record)
491 SELECT s._id, s.account_id_type, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.record
493 WHERE address IS NOT NULL;
495 ALTER TABLE session2 RENAME TO session;
497 DROP TABLE tmp_mapping_table;
501 if (oldVersion
< 16) {
502 logger
.debug("Updating database: Adding stale_timestamp prekey field");
503 try (final var statement
= connection
.createStatement()) {
504 statement
.executeUpdate("""
505 ALTER TABLE pre_key ADD COLUMN stale_timestamp INTEGER;
506 ALTER TABLE kyber_pre_key ADD COLUMN stale_timestamp INTEGER;
507 ALTER TABLE kyber_pre_key ADD COLUMN timestamp INTEGER DEFAULT 0;
511 if (oldVersion
< 17) {
512 logger
.debug("Updating database: Adding key_value table");
513 try (final var statement
= connection
.createStatement()) {
514 statement
.executeUpdate("""
515 CREATE TABLE key_value (
516 _id INTEGER PRIMARY KEY,
517 key TEXT UNIQUE NOT NULL,
523 if (oldVersion
< 18) {
524 logger
.debug("Updating database: Adding cdsi table");
525 try (final var statement
= connection
.createStatement()) {
526 statement
.executeUpdate("""
528 _id INTEGER PRIMARY KEY,
529 number TEXT NOT NULL UNIQUE,
530 last_seen_at INTEGER NOT NULL
535 if (oldVersion
< 19) {
536 logger
.debug("Updating database: Adding contact hidden column");
537 try (final var statement
= connection
.createStatement()) {
538 statement
.executeUpdate("""
539 ALTER TABLE recipient ADD COLUMN hidden INTEGER NOT NULL DEFAULT FALSE;
543 if (oldVersion
< 20) {
544 logger
.debug("Updating database: Creating storage id tables and columns");
545 try (final var statement
= connection
.createStatement()) {
546 statement
.executeUpdate("""
547 CREATE TABLE storage_id (
548 _id INTEGER PRIMARY KEY,
549 type INTEGER NOT NULL,
550 storage_id BLOB NOT NULL
552 ALTER TABLE group_v1 ADD COLUMN storage_id BLOB;
553 ALTER TABLE group_v1 ADD COLUMN storage_record BLOB;
554 ALTER TABLE group_v2 ADD COLUMN storage_id BLOB;
555 ALTER TABLE group_v2 ADD COLUMN storage_record BLOB;
556 ALTER TABLE recipient ADD COLUMN storage_id BLOB;
557 ALTER TABLE recipient ADD COLUMN storage_record BLOB;