]> nmode's Git Repositories - signal-cli/blob - lib/src/main/java/org/asamk/signal/manager/storage/AccountDatabase.java
c49e56ab59b45598551d6b6e5c6dde1043b3e1df
[signal-cli] / lib / src / main / java / org / asamk / signal / manager / storage / AccountDatabase.java
1 package org.asamk.signal.manager.storage;
2
3 import com.zaxxer.hikari.HikariDataSource;
4
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;
24
25 import java.io.File;
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;
31
32 public class AccountDatabase extends Database {
33
34 private static final Logger logger = LoggerFactory.getLogger(AccountDatabase.class);
35 private static final long DATABASE_VERSION = 20;
36
37 private AccountDatabase(final HikariDataSource dataSource) {
38 super(logger, DATABASE_VERSION, dataSource);
39 }
40
41 public static AccountDatabase init(File databaseFile) throws SQLException {
42 return initDatabase(databaseFile, AccountDatabase::new);
43 }
44
45 @Override
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);
61 }
62
63 @Override
64 protected void upgradeDatabase(final Connection connection, final long oldVersion) throws SQLException {
65 if (oldVersion < 2) {
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,
71 number TEXT UNIQUE,
72 uuid BLOB UNIQUE,
73 profile_key BLOB,
74 profile_key_credential BLOB,
75
76 given_name TEXT,
77 family_name TEXT,
78 color TEXT,
79
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,
84
85 profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
86 profile_given_name TEXT,
87 profile_family_name TEXT,
88 profile_about 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
94 ) STRICT;
95 """);
96 }
97 }
98 if (oldVersion < 3) {
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
107 ) STRICT;
108 """);
109 }
110 }
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)
124 ) STRICT;
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)
132 ) STRICT;
133 """);
134 }
135 }
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,
144 group_data BLOB,
145 distribution_id BLOB UNIQUE NOT NULL,
146 blocked INTEGER NOT NULL DEFAULT FALSE,
147 permission_denied INTEGER NOT NULL DEFAULT FALSE
148 ) STRICT;
149 CREATE TABLE group_v1 (
150 _id INTEGER PRIMARY KEY,
151 group_id BLOB UNIQUE NOT NULL,
152 group_id_v2 BLOB UNIQUE,
153 name TEXT,
154 color TEXT,
155 expiration_time INTEGER NOT NULL DEFAULT 0,
156 blocked INTEGER NOT NULL DEFAULT FALSE,
157 archived INTEGER NOT NULL DEFAULT FALSE
158 ) STRICT;
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)
164 ) STRICT;
165 """);
166 }
167 }
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)
179 ) STRICT;
180 """);
181 }
182 }
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
193 ) STRICT;
194 """);
195 }
196 }
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)
209 ) STRICT;
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)
217 ) STRICT;
218 """);
219 }
220 }
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;
226 """);
227 }
228 }
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
239 ) STRICT;
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;
244 DROP TABLE identity;
245 ALTER TABLE identity2 RENAME TO identity;
246
247 DROP INDEX msl_recipient_index;
248 ALTER TABLE message_send_log ADD COLUMN uuid BLOB;
249 UPDATE message_send_log
250 SET uuid = r.uuid
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);
256
257 CREATE TABLE sender_key2 (
258 _id INTEGER PRIMARY KEY,
259 uuid BLOB NOT NULL,
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)
265 ) STRICT;
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;
272
273 CREATE TABLE sender_key_shared2 (
274 _id INTEGER PRIMARY KEY,
275 uuid BLOB NOT NULL,
276 device_id INTEGER NOT NULL,
277 distribution_id BLOB NOT NULL,
278 timestamp INTEGER NOT NULL,
279 UNIQUE(uuid, device_id, distribution_id)
280 ) STRICT;
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;
287
288 CREATE TABLE session2 (
289 _id INTEGER PRIMARY KEY,
290 account_id_type INTEGER NOT NULL,
291 uuid BLOB NOT NULL,
292 device_id INTEGER NOT NULL,
293 record BLOB NOT NULL,
294 UNIQUE(account_id_type, uuid, device_id)
295 ) STRICT;
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;
300 DROP TABLE session;
301 ALTER TABLE session2 RENAME TO session;
302 """);
303 }
304 }
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;
310 """);
311 }
312 }
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;
318 """);
319 }
320 }
321 if (oldVersion < 13) {
322 logger.debug("Updating database: Cleanup unknown service ids");
323 {
324 final var sql = """
325 DELETE FROM identity AS i
326 WHERE i.uuid = ?
327 """;
328 try (final var statement = connection.prepareStatement(sql)) {
329 statement.setBytes(1, ACI.UNKNOWN.toByteArray());
330 statement.executeUpdate();
331 }
332 }
333 {
334 final var sql = """
335 DELETE FROM sender_key_shared AS i
336 WHERE i.uuid = ?
337 """;
338 try (final var statement = connection.prepareStatement(sql)) {
339 statement.setBytes(1, ACI.UNKNOWN.toByteArray());
340 statement.executeUpdate();
341 }
342 }
343 }
344 if (oldVersion < 14) {
345 logger.debug("Updating database: Creating kyber_pre_key table");
346 {
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)
356 ) STRICT;
357 """);
358 }
359 }
360 }
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 (
366 uuid BLOB NOT NULL,
367 address TEXT NOT NULL
368 ) STRICT;
369 """);
370
371 final var sql = (
372 """
373 SELECT r.uuid, r.pni
374 FROM recipient r
375 """
376 );
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);
389
390 return new Pair<>(serviceId, pni);
391 })) {
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());
397 }
398 if (pni.isPresent()) {
399 uuidAddressMapping.put(pni.get().getRawUuid(), pni.get());
400 }
401 });
402 }
403 }
404
405 final var insertSql = """
406 INSERT INTO tmp_mapping_table (uuid, address)
407 VALUES (?,?)
408 """;
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();
416 }
417 }
418
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
426 ) STRICT;
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
429 FROM identity i
430 WHERE address IS NOT NULL;
431 DROP TABLE identity;
432 ALTER TABLE identity2 RENAME TO identity;
433
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
439 ) STRICT;
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);
450
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)
459 ) STRICT;
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
462 FROM sender_key s
463 WHERE address IS NOT NULL;
464 DROP TABLE sender_key;
465 ALTER TABLE sender_key2 RENAME TO sender_key;
466
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)
474 ) STRICT;
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;
481
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)
489 ) STRICT;
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
492 FROM session s
493 WHERE address IS NOT NULL;
494 DROP TABLE session;
495 ALTER TABLE session2 RENAME TO session;
496
497 DROP TABLE tmp_mapping_table;
498 """);
499 }
500 }
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;
508 """);
509 }
510 }
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,
518 value ANY
519 ) STRICT;
520 """);
521 }
522 }
523 if (oldVersion < 18) {
524 logger.debug("Updating database: Adding cdsi table");
525 try (final var statement = connection.createStatement()) {
526 statement.executeUpdate("""
527 CREATE TABLE cdsi (
528 _id INTEGER PRIMARY KEY,
529 number TEXT NOT NULL UNIQUE,
530 last_seen_at INTEGER NOT NULL
531 ) STRICT;
532 """);
533 }
534 }
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;
540 """);
541 }
542 }
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
551 ) STRICT;
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;
558 """);
559 }
560 }
561 }
562 }