]> 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:


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

Codeberg
GitHub
GitLab
SourceHut