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


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

Codeberg
GitHub
GitLab
SourceHut