]> nmode's Git Repositories - signal-cli/blob - lib/src/main/java/org/asamk/signal/manager/storage/AccountDatabase.java
git.nmode.ca / signal-cli / blob
? search:


43a146a7a700f4cb87c6d8bc864f29c6a0b4b03b
[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 = 19;
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 }
61
62 @Override
63 protected void upgradeDatabase(final Connection connection, final long oldVersion) throws SQLException {
64 if (oldVersion < 2) {
65 logger.debug("Updating database: Creating recipient table");
66 try (final var statement = connection.createStatement()) {
67 statement.executeUpdate("""
68 CREATE TABLE recipient (
69 _id INTEGER PRIMARY KEY AUTOINCREMENT,
70 number TEXT UNIQUE,
71 uuid BLOB UNIQUE,
72 profile_key BLOB,
73 profile_key_credential BLOB,
74
75 given_name TEXT,
76 family_name TEXT,
77 color TEXT,
78
79 expiration_time INTEGER NOT NULL DEFAULT 0,
80 blocked INTEGER NOT NULL DEFAULT FALSE,
81 archived INTEGER NOT NULL DEFAULT FALSE,
82 profile_sharing INTEGER NOT NULL DEFAULT FALSE,
83
84 profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
85 profile_given_name TEXT,
86 profile_family_name TEXT,
87 profile_about TEXT,
88 profile_about_emoji TEXT,
89 profile_avatar_url_path TEXT,
90 profile_mobile_coin_address BLOB,
91 profile_unidentified_access_mode TEXT,
92 profile_capabilities TEXT
93 ) STRICT;
94 """);
95 }
96 }
97 if (oldVersion < 3) {
98 logger.debug("Updating database: Creating sticker table");
99 try (final var statement = connection.createStatement()) {
100 statement.executeUpdate("""
101 CREATE TABLE sticker (
102 _id INTEGER PRIMARY KEY,
103 pack_id BLOB UNIQUE NOT NULL,
104 pack_key BLOB NOT NULL,
105 installed INTEGER NOT NULL DEFAULT FALSE
106 ) STRICT;
107 """);
108 }
109 }
110 if (oldVersion < 4) {
111 logger.debug("Updating database: Creating pre key tables");
112 try (final var statement = connection.createStatement()) {
113 statement.executeUpdate("""
114 CREATE TABLE signed_pre_key (
115 _id INTEGER PRIMARY KEY,
116 account_id_type INTEGER NOT NULL,
117 key_id INTEGER NOT NULL,
118 public_key BLOB NOT NULL,
119 private_key BLOB NOT NULL,
120 signature BLOB NOT NULL,
121 timestamp INTEGER DEFAULT 0,
122 UNIQUE(account_id_type, key_id)
123 ) STRICT;
124 CREATE TABLE pre_key (
125 _id INTEGER PRIMARY KEY,
126 account_id_type INTEGER NOT NULL,
127 key_id INTEGER NOT NULL,
128 public_key BLOB NOT NULL,
129 private_key BLOB NOT NULL,
130 UNIQUE(account_id_type, key_id)
131 ) STRICT;
132 """);
133 }
134 }
135 if (oldVersion < 5) {
136 logger.debug("Updating database: Creating group tables");
137 try (final var statement = connection.createStatement()) {
138 statement.executeUpdate("""
139 CREATE TABLE group_v2 (
140 _id INTEGER PRIMARY KEY,
141 group_id BLOB UNIQUE NOT NULL,
142 master_key BLOB NOT NULL,
143 group_data BLOB,
144 distribution_id BLOB UNIQUE NOT NULL,
145 blocked INTEGER NOT NULL DEFAULT FALSE,
146 permission_denied INTEGER NOT NULL DEFAULT FALSE
147 ) STRICT;
148 CREATE TABLE group_v1 (
149 _id INTEGER PRIMARY KEY,
150 group_id BLOB UNIQUE NOT NULL,
151 group_id_v2 BLOB UNIQUE,
152 name TEXT,
153 color TEXT,
154 expiration_time INTEGER NOT NULL DEFAULT 0,
155 blocked INTEGER NOT NULL DEFAULT FALSE,
156 archived INTEGER NOT NULL DEFAULT FALSE
157 ) STRICT;
158 CREATE TABLE group_v1_member (
159 _id INTEGER PRIMARY KEY,
160 group_id INTEGER NOT NULL REFERENCES group_v1 (_id) ON DELETE CASCADE,
161 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
162 UNIQUE(group_id, recipient_id)
163 ) STRICT;
164 """);
165 }
166 }
167 if (oldVersion < 6) {
168 logger.debug("Updating database: Creating session tables");
169 try (final var statement = connection.createStatement()) {
170 statement.executeUpdate("""
171 CREATE TABLE session (
172 _id INTEGER PRIMARY KEY,
173 account_id_type INTEGER NOT NULL,
174 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
175 device_id INTEGER NOT NULL,
176 record BLOB NOT NULL,
177 UNIQUE(account_id_type, recipient_id, device_id)
178 ) STRICT;
179 """);
180 }
181 }
182 if (oldVersion < 7) {
183 logger.debug("Updating database: Creating identity table");
184 try (final var statement = connection.createStatement()) {
185 statement.executeUpdate("""
186 CREATE TABLE identity (
187 _id INTEGER PRIMARY KEY,
188 recipient_id INTEGER UNIQUE NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
189 identity_key BLOB NOT NULL,
190 added_timestamp INTEGER NOT NULL,
191 trust_level INTEGER NOT NULL
192 ) STRICT;
193 """);
194 }
195 }
196 if (oldVersion < 8) {
197 logger.debug("Updating database: Creating sender key tables");
198 try (final var statement = connection.createStatement()) {
199 statement.executeUpdate("""
200 CREATE TABLE sender_key (
201 _id INTEGER PRIMARY KEY,
202 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
203 device_id INTEGER NOT NULL,
204 distribution_id BLOB NOT NULL,
205 record BLOB NOT NULL,
206 created_timestamp INTEGER NOT NULL,
207 UNIQUE(recipient_id, device_id, distribution_id)
208 ) STRICT;
209 CREATE TABLE sender_key_shared (
210 _id INTEGER PRIMARY KEY,
211 recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
212 device_id INTEGER NOT NULL,
213 distribution_id BLOB NOT NULL,
214 timestamp INTEGER NOT NULL,
215 UNIQUE(recipient_id, device_id, distribution_id)
216 ) STRICT;
217 """);
218 }
219 }
220 if (oldVersion < 9) {
221 logger.debug("Updating database: Adding urgent field");
222 try (final var statement = connection.createStatement()) {
223 statement.executeUpdate("""
224 ALTER TABLE message_send_log_content ADD COLUMN urgent INTEGER NOT NULL DEFAULT TRUE;
225 """);
226 }
227 }
228 if (oldVersion < 10) {
229 logger.debug("Updating database: Key tables on serviceId instead of recipientId");
230 try (final var statement = connection.createStatement()) {
231 statement.executeUpdate("""
232 CREATE TABLE identity2 (
233 _id INTEGER PRIMARY KEY,
234 uuid BLOB UNIQUE NOT NULL,
235 identity_key BLOB NOT NULL,
236 added_timestamp INTEGER NOT NULL,
237 trust_level INTEGER NOT NULL
238 ) STRICT;
239 INSERT INTO identity2 (_id, uuid, identity_key, added_timestamp, trust_level)
240 SELECT i._id, r.uuid, i.identity_key, i.added_timestamp, i.trust_level
241 FROM identity i LEFT JOIN recipient r ON i.recipient_id = r._id
242 WHERE uuid IS NOT NULL;
243 DROP TABLE identity;
244 ALTER TABLE identity2 RENAME TO identity;
245
246 DROP INDEX msl_recipient_index;
247 ALTER TABLE message_send_log ADD COLUMN uuid BLOB;
248 UPDATE message_send_log
249 SET uuid = r.uuid
250 FROM message_send_log i, (SELECT _id, uuid FROM recipient) AS r
251 WHERE i.recipient_id = r._id;
252 DELETE FROM message_send_log WHERE uuid IS NULL;
253 ALTER TABLE message_send_log DROP COLUMN recipient_id;
254 CREATE INDEX msl_recipient_index ON message_send_log (uuid, device_id, content_id);
255
256 CREATE TABLE sender_key2 (
257 _id INTEGER PRIMARY KEY,
258 uuid BLOB NOT NULL,
259 device_id INTEGER NOT NULL,
260 distribution_id BLOB NOT NULL,
261 record BLOB NOT NULL,
262 created_timestamp INTEGER NOT NULL,
263 UNIQUE(uuid, device_id, distribution_id)
264 ) STRICT;
265 INSERT INTO sender_key2 (_id, uuid, device_id, distribution_id, record, created_timestamp)
266 SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.record, s.created_timestamp
267 FROM sender_key s LEFT JOIN recipient r ON s.recipient_id = r._id
268 WHERE uuid IS NOT NULL;
269 DROP TABLE sender_key;
270 ALTER TABLE sender_key2 RENAME TO sender_key;
271
272 CREATE TABLE sender_key_shared2 (
273 _id INTEGER PRIMARY KEY,
274 uuid BLOB NOT NULL,
275 device_id INTEGER NOT NULL,
276 distribution_id BLOB NOT NULL,
277 timestamp INTEGER NOT NULL,
278 UNIQUE(uuid, device_id, distribution_id)
279 ) STRICT;
280 INSERT INTO sender_key_shared2 (_id, uuid, device_id, distribution_id, timestamp)
281 SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.timestamp
282 FROM sender_key_shared s LEFT JOIN recipient r ON s.recipient_id = r._id
283 WHERE uuid IS NOT NULL;
284 DROP TABLE sender_key_shared;
285 ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
286
287 CREATE TABLE session2 (
288 _id INTEGER PRIMARY KEY,
289 account_id_type INTEGER NOT NULL,
290 uuid BLOB NOT NULL,
291 device_id INTEGER NOT NULL,
292 record BLOB NOT NULL,
293 UNIQUE(account_id_type, uuid, device_id)
294 ) STRICT;
295 INSERT INTO session2 (_id, account_id_type, uuid, device_id, record)
296 SELECT s._id, s.account_id_type, r.uuid, s.device_id, s.record
297 FROM session s LEFT JOIN recipient r ON s.recipient_id = r._id
298 WHERE uuid IS NOT NULL;
299 DROP TABLE session;
300 ALTER TABLE session2 RENAME TO session;
301 """);
302 }
303 }
304 if (oldVersion < 11) {
305 logger.debug("Updating database: Adding pni field");
306 try (final var statement = connection.createStatement()) {
307 statement.executeUpdate("""
308 ALTER TABLE recipient ADD COLUMN pni BLOB;
309 """);
310 }
311 }
312 if (oldVersion < 12) {
313 logger.debug("Updating database: Adding username field");
314 try (final var statement = connection.createStatement()) {
315 statement.executeUpdate("""
316 ALTER TABLE recipient ADD COLUMN username TEXT;
317 """);
318 }
319 }
320 if (oldVersion < 13) {
321 logger.debug("Updating database: Cleanup unknown service ids");
322 {
323 final var sql = """
324 DELETE FROM identity AS i
325 WHERE i.uuid = ?
326 """;
327 try (final var statement = connection.prepareStatement(sql)) {
328 statement.setBytes(1, ACI.UNKNOWN.toByteArray());
329 statement.executeUpdate();
330 }
331 }
332 {
333 final var sql = """
334 DELETE FROM sender_key_shared AS i
335 WHERE i.uuid = ?
336 """;
337 try (final var statement = connection.prepareStatement(sql)) {
338 statement.setBytes(1, ACI.UNKNOWN.toByteArray());
339 statement.executeUpdate();
340 }
341 }
342 }
343 if (oldVersion < 14) {
344 logger.debug("Updating database: Creating kyber_pre_key table");
345 {
346 try (final var statement = connection.createStatement()) {
347 statement.executeUpdate("""
348 CREATE TABLE kyber_pre_key (
349 _id INTEGER PRIMARY KEY,
350 account_id_type INTEGER NOT NULL,
351 key_id INTEGER NOT NULL,
352 serialized BLOB NOT NULL,
353 is_last_resort INTEGER NOT NULL,
354 UNIQUE(account_id_type, key_id)
355 ) STRICT;
356 """);
357 }
358 }
359 }
360 if (oldVersion < 15) {
361 logger.debug("Updating database: Store serviceId as TEXT");
362 try (final var statement = connection.createStatement()) {
363 statement.executeUpdate("""
364 CREATE TABLE tmp_mapping_table (
365 uuid BLOB NOT NULL,
366 address TEXT NOT NULL
367 ) STRICT;
368 """);
369
370 final var sql = (
371 """
372 SELECT r.uuid, r.pni
373 FROM recipient r
374 """
375 );
376 final var uuidAddressMapping = new HashMap<UUID, ServiceId>();
377 try (final var preparedStatement = connection.prepareStatement(sql)) {
378 try (var result = Utils.executeQueryForStream(preparedStatement, (resultSet) -> {
379 final var pni = Optional.ofNullable(resultSet.getBytes("pni"))
380 .map(UuidUtil::parseOrNull)
381 .map(ServiceId.PNI::from);
382 final var serviceIdUuid = Optional.ofNullable(resultSet.getBytes("uuid"))
383 .map(UuidUtil::parseOrNull);
384 final var serviceId = serviceIdUuid.isPresent() && pni.isPresent() && serviceIdUuid.get()
385 .equals(pni.get().getRawUuid())
386 ? pni.<ServiceId>map(p -> p)
387 : serviceIdUuid.<ServiceId>map(ACI::from);
388
389 return new Pair<>(serviceId, pni);
390 })) {
391 result.forEach(p -> {
392 final var serviceId = p.first();
393 final var pni = p.second();
394 if (serviceId.isPresent()) {
395 uuidAddressMapping.put(serviceId.get().getRawUuid(), serviceId.get());
396 }
397 if (pni.isPresent()) {
398 uuidAddressMapping.put(pni.get().getRawUuid(), pni.get());
399 }
400 });
401 }
402 }
403
404 final var insertSql = """
405 INSERT INTO tmp_mapping_table (uuid, address)
406 VALUES (?,?)
407 """;
408 try (final var insertStatement = connection.prepareStatement(insertSql)) {
409 for (final var entry : uuidAddressMapping.entrySet()) {
410 final var uuid = entry.getKey();
411 final var serviceId = entry.getValue();
412 insertStatement.setBytes(1, UuidUtil.toByteArray(uuid));
413 insertStatement.setString(2, serviceId.toString());
414 insertStatement.execute();
415 }
416 }
417
418 statement.executeUpdate("""
419 CREATE TABLE identity2 (
420 _id INTEGER PRIMARY KEY,
421 address TEXT UNIQUE NOT NULL,
422 identity_key BLOB NOT NULL,
423 added_timestamp INTEGER NOT NULL,
424 trust_level INTEGER NOT NULL
425 ) STRICT;
426 INSERT INTO identity2 (_id, address, identity_key, added_timestamp, trust_level)
427 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
428 FROM identity i
429 WHERE address IS NOT NULL;
430 DROP TABLE identity;
431 ALTER TABLE identity2 RENAME TO identity;
432
433 CREATE TABLE message_send_log2 (
434 _id INTEGER PRIMARY KEY,
435 content_id INTEGER NOT NULL REFERENCES message_send_log_content (_id) ON DELETE CASCADE,
436 address TEXT NOT NULL,
437 device_id INTEGER NOT NULL
438 ) STRICT;
439 INSERT INTO message_send_log2 (_id, content_id, address, device_id)
440 SELECT m._id, m.content_id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = m.uuid) address, m.device_id
441 FROM message_send_log m
442 WHERE address IS NOT NULL;
443 DROP INDEX msl_recipient_index;
444 DROP INDEX msl_content_index;
445 DROP TABLE message_send_log;
446 ALTER TABLE message_send_log2 RENAME TO message_send_log;
447 CREATE INDEX msl_recipient_index ON message_send_log (address, device_id, content_id);
448 CREATE INDEX msl_content_index ON message_send_log (content_id);
449
450 CREATE TABLE sender_key2 (
451 _id INTEGER PRIMARY KEY,
452 address TEXT NOT NULL,
453 device_id INTEGER NOT NULL,
454 distribution_id BLOB NOT NULL,
455 record BLOB NOT NULL,
456 created_timestamp INTEGER NOT NULL,
457 UNIQUE(address, device_id, distribution_id)
458 ) STRICT;
459 INSERT INTO sender_key2 (_id, address, device_id, distribution_id, record, created_timestamp)
460 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
461 FROM sender_key s
462 WHERE address IS NOT NULL;
463 DROP TABLE sender_key;
464 ALTER TABLE sender_key2 RENAME TO sender_key;
465
466 CREATE TABLE sender_key_shared2 (
467 _id INTEGER PRIMARY KEY,
468 address TEXT NOT NULL,
469 device_id INTEGER NOT NULL,
470 distribution_id BLOB NOT NULL,
471 timestamp INTEGER NOT NULL,
472 UNIQUE(address, device_id, distribution_id)
473 ) STRICT;
474 INSERT INTO sender_key_shared2 (_id, address, device_id, distribution_id, timestamp)
475 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
476 FROM sender_key_shared s
477 WHERE address IS NOT NULL;
478 DROP TABLE sender_key_shared;
479 ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
480
481 CREATE TABLE session2 (
482 _id INTEGER PRIMARY KEY,
483 account_id_type INTEGER NOT NULL,
484 address TEXT NOT NULL,
485 device_id INTEGER NOT NULL,
486 record BLOB NOT NULL,
487 UNIQUE(account_id_type, address, device_id)
488 ) STRICT;
489 INSERT INTO session2 (_id, account_id_type, address, device_id, record)
490 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
491 FROM session s
492 WHERE address IS NOT NULL;
493 DROP TABLE session;
494 ALTER TABLE session2 RENAME TO session;
495
496 DROP TABLE tmp_mapping_table;
497 """);
498 }
499 }
500 if (oldVersion < 16) {
501 logger.debug("Updating database: Adding stale_timestamp prekey field");
502 try (final var statement = connection.createStatement()) {
503 statement.executeUpdate("""
504 ALTER TABLE pre_key ADD COLUMN stale_timestamp INTEGER;
505 ALTER TABLE kyber_pre_key ADD COLUMN stale_timestamp INTEGER;
506 ALTER TABLE kyber_pre_key ADD COLUMN timestamp INTEGER DEFAULT 0;
507 """);
508 }
509 }
510 if (oldVersion < 17) {
511 logger.debug("Updating database: Adding key_value table");
512 try (final var statement = connection.createStatement()) {
513 statement.executeUpdate("""
514 CREATE TABLE key_value (
515 _id INTEGER PRIMARY KEY,
516 key TEXT UNIQUE NOT NULL,
517 value ANY
518 ) STRICT;
519 """);
520 }
521 }
522 if (oldVersion < 18) {
523 logger.debug("Updating database: Adding cdsi table");
524 try (final var statement = connection.createStatement()) {
525 statement.executeUpdate("""
526 CREATE TABLE cdsi (
527 _id INTEGER PRIMARY KEY,
528 number TEXT NOT NULL UNIQUE,
529 last_seen_at INTEGER NOT NULL
530 ) STRICT;
531 """);
532 }
533 }
534 if (oldVersion < 19) {
535 logger.debug("Updating database: Adding contact hidden column");
536 try (final var statement = connection.createStatement()) {
537 statement.executeUpdate("""
538 ALTER TABLE recipient ADD COLUMN hidden INTEGER NOT NULL DEFAULT FALSE;
539 """);
540 }
541 }
542 }
543 }
[GitHub/AsamK] signal-cli provides an unofficial commandline, JSON-RPC and dbus interface for the Signal messenger.

Codeberg
GitHub
GitLab
SourceHut