nmode's Git Repositories - signal-cli/blob - lib/src/main/java/org/asamk/signal/manager/storage/recipients/RecipientStore.java

   1 package org.asamk.signal.manager.storage.recipients;
   2
   3 import org.asamk.signal.manager.api.Pair;
   4 import org.asamk.signal.manager.api.UnregisteredRecipientException;
   5 import org.asamk.signal.manager.storage.Database;
   6 import org.asamk.signal.manager.storage.Utils;
   7 import org.asamk.signal.manager.storage.contacts.ContactsStore;
   8 import org.asamk.signal.manager.storage.profiles.ProfileStore;
   9 import org.signal.libsignal.zkgroup.InvalidInputException;
  10 import org.signal.libsignal.zkgroup.profiles.ExpiringProfileKeyCredential;
  11 import org.signal.libsignal.zkgroup.profiles.ProfileKey;
  12 import org.slf4j.Logger;
  13 import org.slf4j.LoggerFactory;
  14 import org.whispersystems.signalservice.api.push.ACI;
  15 import org.whispersystems.signalservice.api.push.PNI;
  16 import org.whispersystems.signalservice.api.push.ServiceId;
  17 import org.whispersystems.signalservice.api.push.SignalServiceAddress;
  18 import org.whispersystems.signalservice.api.util.UuidUtil;
  19
  20 import java.sql.Connection;
  21 import java.sql.ResultSet;
  22 import java.sql.SQLException;
  23 import java.util.ArrayList;
  24 import java.util.Arrays;
  25 import java.util.Collection;
  26 import java.util.HashMap;
  27 import java.util.List;
  28 import java.util.Map;
  29 import java.util.Objects;
  30 import java.util.Optional;
  31 import java.util.Set;
  32 import java.util.function.Supplier;
  33 import java.util.stream.Collectors;
  34
  35 public class RecipientStore implements RecipientIdCreator, RecipientResolver, RecipientTrustedResolver, ContactsStore, ProfileStore {
  36
  37     private final static Logger logger = LoggerFactory.getLogger(RecipientStore.class);
  38     private static final String TABLE_RECIPIENT = "recipient";
  39     private static final String SQL_IS_CONTACT = "r.given_name IS NOT NULL OR r.family_name IS NOT NULL OR r.expiration_time > 0 OR r.profile_sharing = TRUE OR r.color IS NOT NULL OR r.blocked = TRUE OR r.archived = TRUE";
  40
  41     private final RecipientMergeHandler recipientMergeHandler;
  42     private final SelfAddressProvider selfAddressProvider;
  43     private final Database database;
  44
  45     private final Object recipientsLock = new Object();
  46     private final Map<Long, Long> recipientsMerged = new HashMap<>();
  47
  48     public static void createSql(Connection connection) throws SQLException {
  49         // When modifying the CREATE statement here, also add a migration in AccountDatabase.java
  50         try (final var statement = connection.createStatement()) {
  51             statement.executeUpdate("""
  52                                     CREATE TABLE recipient (
  53                                       _id INTEGER PRIMARY KEY AUTOINCREMENT,
  54                                       number TEXT UNIQUE,
  55                                       uuid BLOB UNIQUE,
  56                                       profile_key BLOB,
  57                                       profile_key_credential BLOB,
  58
  59                                       given_name TEXT,
  60                                       family_name TEXT,
  61                                       color TEXT,
  62
  63                                       expiration_time INTEGER NOT NULL DEFAULT 0,
  64                                       blocked INTEGER NOT NULL DEFAULT FALSE,
  65                                       archived INTEGER NOT NULL DEFAULT FALSE,
  66                                       profile_sharing INTEGER NOT NULL DEFAULT FALSE,
  67
  68                                       profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
  69                                       profile_given_name TEXT,
  70                                       profile_family_name TEXT,
  71                                       profile_about TEXT,
  72                                       profile_about_emoji TEXT,
  73                                       profile_avatar_url_path TEXT,
  74                                       profile_mobile_coin_address BLOB,
  75                                       profile_unidentified_access_mode TEXT,
  76                                       profile_capabilities TEXT
  77                                     ) STRICT;
  78                                     """);
  79         }
  80     }
  81
  82     public RecipientStore(
  83             final RecipientMergeHandler recipientMergeHandler,
  84             final SelfAddressProvider selfAddressProvider,
  85             final Database database
  86     ) {
  87         this.recipientMergeHandler = recipientMergeHandler;
  88         this.selfAddressProvider = selfAddressProvider;
  89         this.database = database;
  90     }
  91
  92     public RecipientAddress resolveRecipientAddress(RecipientId recipientId) {
  93         final var sql = (
  94                 """
  95                 SELECT r.number, r.uuid
  96                 FROM %s r
  97                 WHERE r._id = ?
  98                 """
  99         ).formatted(TABLE_RECIPIENT);
 100         try (final var connection = database.getConnection()) {
 101             try (final var statement = connection.prepareStatement(sql)) {
 102                 statement.setLong(1, recipientId.id());
 103                 return Utils.executeQuerySingleRow(statement, this::getRecipientAddressFromResultSet);
 104             }
 105         } catch (SQLException e) {
 106             throw new RuntimeException("Failed read from recipient store", e);
 107         }
 108     }
 109
 110     public Collection<RecipientId> getRecipientIdsWithEnabledProfileSharing() {
 111         final var sql = (
 112                 """
 113                 SELECT r._id
 114                 FROM %s r
 115                 WHERE r.blocked = FALSE AND r.profile_sharing = TRUE
 116                 """
 117         ).formatted(TABLE_RECIPIENT);
 118         try (final var connection = database.getConnection()) {
 119             try (final var statement = connection.prepareStatement(sql)) {
 120                 try (var result = Utils.executeQueryForStream(statement, this::getRecipientIdFromResultSet)) {
 121                     return result.toList();
 122                 }
 123             }
 124         } catch (SQLException e) {
 125             throw new RuntimeException("Failed read from recipient store", e);
 126         }
 127     }
 128
 129     @Override
 130     public RecipientId resolveRecipient(final long rawRecipientId) {
 131         final var sql = (
 132                 """
 133                 SELECT r._id
 134                 FROM %s r
 135                 WHERE r._id = ?
 136                 """
 137         ).formatted(TABLE_RECIPIENT);
 138         try (final var connection = database.getConnection()) {
 139             try (final var statement = connection.prepareStatement(sql)) {
 140                 statement.setLong(1, rawRecipientId);
 141                 return Utils.executeQueryForOptional(statement, this::getRecipientIdFromResultSet).orElse(null);
 142             }
 143         } catch (SQLException e) {
 144             throw new RuntimeException("Failed read from recipient store", e);
 145         }
 146     }
 147
 148     @Override
 149     public RecipientId resolveRecipient(final String identifier) {
 150         if (UuidUtil.isUuid(identifier)) {
 151             return resolveRecipient(ServiceId.parseOrThrow(identifier));
 152         } else {
 153             return resolveRecipientByNumber(identifier);
 154         }
 155     }
 156
 157     private RecipientId resolveRecipientByNumber(final String number) {
 158         synchronized (recipientsLock) {
 159             final RecipientId recipientId;
 160             try (final var connection = database.getConnection()) {
 161                 connection.setAutoCommit(false);
 162                 recipientId = resolveRecipientLocked(connection, number);
 163                 connection.commit();
 164             } catch (SQLException e) {
 165                 throw new RuntimeException("Failed read recipient store", e);
 166             }
 167             return recipientId;
 168         }
 169     }
 170
 171     @Override
 172     public RecipientId resolveRecipient(final ServiceId serviceId) {
 173         synchronized (recipientsLock) {
 174             final RecipientId recipientId;
 175             try (final var connection = database.getConnection()) {
 176                 connection.setAutoCommit(false);
 177                 recipientId = resolveRecipientLocked(connection, serviceId);
 178                 connection.commit();
 179             } catch (SQLException e) {
 180                 throw new RuntimeException("Failed read recipient store", e);
 181             }
 182             return recipientId;
 183         }
 184     }
 185
 186     /**
 187      * Should only be used for recipientIds from the database.
 188      * Where the foreign key relations ensure a valid recipientId.
 189      */
 190     @Override
 191     public RecipientId create(final long recipientId) {
 192         return new RecipientId(recipientId, this);
 193     }
 194
 195     public RecipientId resolveRecipient(
 196             final String number, Supplier<ServiceId> serviceIdSupplier
 197     ) throws UnregisteredRecipientException {
 198         final Optional<RecipientWithAddress> byNumber;
 199         try (final var connection = database.getConnection()) {
 200             byNumber = findByNumber(connection, number);
 201         } catch (SQLException e) {
 202             throw new RuntimeException("Failed read from recipient store", e);
 203         }
 204         if (byNumber.isEmpty() || byNumber.get().address().serviceId().isEmpty()) {
 205             final var serviceId = serviceIdSupplier.get();
 206             if (serviceId == null) {
 207                 throw new UnregisteredRecipientException(new org.asamk.signal.manager.api.RecipientAddress(null,
 208                         number));
 209             }
 210
 211             return resolveRecipient(serviceId);
 212         }
 213         return byNumber.get().id();
 214     }
 215
 216     public RecipientId resolveRecipient(RecipientAddress address) {
 217         synchronized (recipientsLock) {
 218             final RecipientId recipientId;
 219             try (final var connection = database.getConnection()) {
 220                 connection.setAutoCommit(false);
 221                 recipientId = resolveRecipientLocked(connection, address);
 222                 connection.commit();
 223             } catch (SQLException e) {
 224                 throw new RuntimeException("Failed read recipient store", e);
 225             }
 226             return recipientId;
 227         }
 228     }
 229
 230     @Override
 231     public RecipientId resolveSelfRecipientTrusted(RecipientAddress address) {
 232         return resolveRecipientTrusted(address, true);
 233     }
 234
 235     public RecipientId resolveRecipientTrusted(RecipientAddress address) {
 236         return resolveRecipientTrusted(address, false);
 237     }
 238
 239     @Override
 240     public RecipientId resolveRecipientTrusted(SignalServiceAddress address) {
 241         return resolveRecipientTrusted(new RecipientAddress(address), false);
 242     }
 243
 244     @Override
 245     public RecipientId resolveRecipientTrusted(
 246             final Optional<ACI> aci, final Optional<PNI> pni, final Optional<String> number
 247     ) {
 248         final var serviceId = aci.map(a -> (ServiceId) a).or(() -> pni);
 249         return resolveRecipientTrusted(new RecipientAddress(serviceId, number), false);
 250     }
 251
 252     @Override
 253     public void storeContact(RecipientId recipientId, final Contact contact) {
 254         try (final var connection = database.getConnection()) {
 255             storeContact(connection, recipientId, contact);
 256         } catch (SQLException e) {
 257             throw new RuntimeException("Failed update recipient store", e);
 258         }
 259     }
 260
 261     @Override
 262     public Contact getContact(RecipientId recipientId) {
 263         try (final var connection = database.getConnection()) {
 264             return getContact(connection, recipientId);
 265         } catch (SQLException e) {
 266             throw new RuntimeException("Failed read from recipient store", e);
 267         }
 268     }
 269
 270     @Override
 271     public List<Pair<RecipientId, Contact>> getContacts() {
 272         final var sql = (
 273                 """
 274                 SELECT r._id, r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 275                 FROM %s r
 276                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 277                 """
 278         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 279         try (final var connection = database.getConnection()) {
 280             try (final var statement = connection.prepareStatement(sql)) {
 281                 try (var result = Utils.executeQueryForStream(statement,
 282                         resultSet -> new Pair<>(getRecipientIdFromResultSet(resultSet),
 283                                 getContactFromResultSet(resultSet)))) {
 284                     return result.toList();
 285                 }
 286             }
 287         } catch (SQLException e) {
 288             throw new RuntimeException("Failed read from recipient store", e);
 289         }
 290     }
 291
 292     public List<Recipient> getRecipients(
 293             boolean onlyContacts, Optional<Boolean> blocked, Set<RecipientId> recipientIds, Optional<String> name
 294     ) {
 295         final var sqlWhere = new ArrayList<String>();
 296         if (onlyContacts) {
 297             sqlWhere.add("(" + SQL_IS_CONTACT + ")");
 298         }
 299         if (blocked.isPresent()) {
 300             sqlWhere.add("r.blocked = ?");
 301         }
 302         if (!recipientIds.isEmpty()) {
 303             final var recipientIdsCommaSeparated = recipientIds.stream()
 304                     .map(recipientId -> String.valueOf(recipientId.id()))
 305                     .collect(Collectors.joining(","));
 306             sqlWhere.add("r._id IN (" + recipientIdsCommaSeparated + ")");
 307         }
 308         final var sql = (
 309                 """
 310                 SELECT r._id,
 311                        r.number, r.uuid,
 312                        r.profile_key, r.profile_key_credential,
 313                        r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived,
 314                        r.profile_last_update_timestamp, r.profile_given_name, r.profile_family_name, r.profile_about, r.profile_about_emoji, r.profile_avatar_url_path, r.profile_mobile_coin_address, r.profile_unidentified_access_mode, r.profile_capabilities
 315                 FROM %s r
 316                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 317                 """
 318         ).formatted(TABLE_RECIPIENT, sqlWhere.size() == 0 ? "TRUE" : String.join(" AND ", sqlWhere));
 319         try (final var connection = database.getConnection()) {
 320             try (final var statement = connection.prepareStatement(sql)) {
 321                 if (blocked.isPresent()) {
 322                     statement.setBoolean(1, blocked.get());
 323                 }
 324                 try (var result = Utils.executeQueryForStream(statement, this::getRecipientFromResultSet)) {
 325                     return result.filter(r -> name.isEmpty() || (
 326                             r.getContact() != null && name.get().equals(r.getContact().getName())
 327                     ) || (r.getProfile() != null && name.get().equals(r.getProfile().getDisplayName()))).toList();
 328                 }
 329             }
 330         } catch (SQLException e) {
 331             throw new RuntimeException("Failed read from recipient store", e);
 332         }
 333     }
 334
 335     public Map<ServiceId, ProfileKey> getServiceIdToProfileKeyMap() {
 336         final var sql = (
 337                 """
 338                 SELECT r.uuid, r.profile_key
 339                 FROM %s r
 340                 WHERE r.uuid IS NOT NULL AND r.profile_key IS NOT NULL
 341                 """
 342         ).formatted(TABLE_RECIPIENT);
 343         try (final var connection = database.getConnection()) {
 344             try (final var statement = connection.prepareStatement(sql)) {
 345                 return Utils.executeQueryForStream(statement, resultSet -> {
 346                     final var serviceId = ServiceId.parseOrThrow(resultSet.getBytes("uuid"));
 347                     final var profileKey = getProfileKeyFromResultSet(resultSet);
 348                     return new Pair<>(serviceId, profileKey);
 349                 }).filter(Objects::nonNull).collect(Collectors.toMap(Pair::first, Pair::second));
 350             }
 351         } catch (SQLException e) {
 352             throw new RuntimeException("Failed read from recipient store", e);
 353         }
 354     }
 355
 356     @Override
 357     public void deleteContact(RecipientId recipientId) {
 358         storeContact(recipientId, null);
 359     }
 360
 361     public void deleteRecipientData(RecipientId recipientId) {
 362         logger.debug("Deleting recipient data for {}", recipientId);
 363         try (final var connection = database.getConnection()) {
 364             connection.setAutoCommit(false);
 365             storeContact(connection, recipientId, null);
 366             storeProfile(connection, recipientId, null);
 367             storeProfileKey(connection, recipientId, null, false);
 368             storeExpiringProfileKeyCredential(connection, recipientId, null);
 369             deleteRecipient(connection, recipientId);
 370             connection.commit();
 371         } catch (SQLException e) {
 372             throw new RuntimeException("Failed update recipient store", e);
 373         }
 374     }
 375
 376     @Override
 377     public Profile getProfile(final RecipientId recipientId) {
 378         try (final var connection = database.getConnection()) {
 379             return getProfile(connection, recipientId);
 380         } catch (SQLException e) {
 381             throw new RuntimeException("Failed read from recipient store", e);
 382         }
 383     }
 384
 385     @Override
 386     public ProfileKey getProfileKey(final RecipientId recipientId) {
 387         try (final var connection = database.getConnection()) {
 388             return getProfileKey(connection, recipientId);
 389         } catch (SQLException e) {
 390             throw new RuntimeException("Failed read from recipient store", e);
 391         }
 392     }
 393
 394     @Override
 395     public ExpiringProfileKeyCredential getExpiringProfileKeyCredential(final RecipientId recipientId) {
 396         try (final var connection = database.getConnection()) {
 397             return getExpiringProfileKeyCredential(connection, recipientId);
 398         } catch (SQLException e) {
 399             throw new RuntimeException("Failed read from recipient store", e);
 400         }
 401     }
 402
 403     @Override
 404     public void storeProfile(RecipientId recipientId, final Profile profile) {
 405         try (final var connection = database.getConnection()) {
 406             storeProfile(connection, recipientId, profile);
 407         } catch (SQLException e) {
 408             throw new RuntimeException("Failed update recipient store", e);
 409         }
 410     }
 411
 412     @Override
 413     public void storeSelfProfileKey(final RecipientId recipientId, final ProfileKey profileKey) {
 414         try (final var connection = database.getConnection()) {
 415             storeProfileKey(connection, recipientId, profileKey, false);
 416         } catch (SQLException e) {
 417             throw new RuntimeException("Failed update recipient store", e);
 418         }
 419     }
 420
 421     @Override
 422     public void storeProfileKey(RecipientId recipientId, final ProfileKey profileKey) {
 423         try (final var connection = database.getConnection()) {
 424             storeProfileKey(connection, recipientId, profileKey, true);
 425         } catch (SQLException e) {
 426             throw new RuntimeException("Failed update recipient store", e);
 427         }
 428     }
 429
 430     @Override
 431     public void storeExpiringProfileKeyCredential(
 432             RecipientId recipientId, final ExpiringProfileKeyCredential profileKeyCredential
 433     ) {
 434         try (final var connection = database.getConnection()) {
 435             storeExpiringProfileKeyCredential(connection, recipientId, profileKeyCredential);
 436         } catch (SQLException e) {
 437             throw new RuntimeException("Failed update recipient store", e);
 438         }
 439     }
 440
 441     void addLegacyRecipients(final Map<RecipientId, Recipient> recipients) {
 442         logger.debug("Migrating legacy recipients to database");
 443         long start = System.nanoTime();
 444         final var sql = (
 445                 """
 446                 INSERT INTO %s (_id, number, uuid)
 447                 VALUES (?, ?, ?)
 448                 """
 449         ).formatted(TABLE_RECIPIENT);
 450         try (final var connection = database.getConnection()) {
 451             connection.setAutoCommit(false);
 452             try (final var statement = connection.prepareStatement("DELETE FROM %s".formatted(TABLE_RECIPIENT))) {
 453                 statement.executeUpdate();
 454             }
 455             try (final var statement = connection.prepareStatement(sql)) {
 456                 for (final var recipient : recipients.values()) {
 457                     statement.setLong(1, recipient.getRecipientId().id());
 458                     statement.setString(2, recipient.getAddress().number().orElse(null));
 459                     statement.setBytes(3,
 460                             recipient.getAddress()
 461                                     .serviceId()
 462                                     .map(ServiceId::uuid)
 463                                     .map(UuidUtil::toByteArray)
 464                                     .orElse(null));
 465                     statement.executeUpdate();
 466                 }
 467             }
 468             logger.debug("Initial inserts took {}ms", (System.nanoTime() - start) / 1000000);
 469
 470             for (final var recipient : recipients.values()) {
 471                 if (recipient.getContact() != null) {
 472                     storeContact(connection, recipient.getRecipientId(), recipient.getContact());
 473                 }
 474                 if (recipient.getProfile() != null) {
 475                     storeProfile(connection, recipient.getRecipientId(), recipient.getProfile());
 476                 }
 477                 if (recipient.getProfileKey() != null) {
 478                     storeProfileKey(connection, recipient.getRecipientId(), recipient.getProfileKey(), false);
 479                 }
 480                 if (recipient.getExpiringProfileKeyCredential() != null) {
 481                     storeExpiringProfileKeyCredential(connection,
 482                             recipient.getRecipientId(),
 483                             recipient.getExpiringProfileKeyCredential());
 484                 }
 485             }
 486             connection.commit();
 487         } catch (SQLException e) {
 488             throw new RuntimeException("Failed update recipient store", e);
 489         }
 490         logger.debug("Complete recipients migration took {}ms", (System.nanoTime() - start) / 1000000);
 491     }
 492
 493     long getActualRecipientId(long recipientId) {
 494         while (recipientsMerged.containsKey(recipientId)) {
 495             final var newRecipientId = recipientsMerged.get(recipientId);
 496             logger.debug("Using {} instead of {}, because recipients have been merged", newRecipientId, recipientId);
 497             recipientId = newRecipientId;
 498         }
 499         return recipientId;
 500     }
 501
 502     private void storeContact(
 503             final Connection connection, final RecipientId recipientId, final Contact contact
 504     ) throws SQLException {
 505         final var sql = (
 506                 """
 507                 UPDATE %s
 508                 SET given_name = ?, family_name = ?, expiration_time = ?, profile_sharing = ?, color = ?, blocked = ?, archived = ?
 509                 WHERE _id = ?
 510                 """
 511         ).formatted(TABLE_RECIPIENT);
 512         try (final var statement = connection.prepareStatement(sql)) {
 513             statement.setString(1, contact == null ? null : contact.getGivenName());
 514             statement.setString(2, contact == null ? null : contact.getFamilyName());
 515             statement.setInt(3, contact == null ? 0 : contact.getMessageExpirationTime());
 516             statement.setBoolean(4, contact != null && contact.isProfileSharingEnabled());
 517             statement.setString(5, contact == null ? null : contact.getColor());
 518             statement.setBoolean(6, contact != null && contact.isBlocked());
 519             statement.setBoolean(7, contact != null && contact.isArchived());
 520             statement.setLong(8, recipientId.id());
 521             statement.executeUpdate();
 522         }
 523     }
 524
 525     private void storeExpiringProfileKeyCredential(
 526             final Connection connection,
 527             final RecipientId recipientId,
 528             final ExpiringProfileKeyCredential profileKeyCredential
 529     ) throws SQLException {
 530         final var sql = (
 531                 """
 532                 UPDATE %s
 533                 SET profile_key_credential = ?
 534                 WHERE _id = ?
 535                 """
 536         ).formatted(TABLE_RECIPIENT);
 537         try (final var statement = connection.prepareStatement(sql)) {
 538             statement.setBytes(1, profileKeyCredential == null ? null : profileKeyCredential.serialize());
 539             statement.setLong(2, recipientId.id());
 540             statement.executeUpdate();
 541         }
 542     }
 543
 544     private void storeProfile(
 545             final Connection connection, final RecipientId recipientId, final Profile profile
 546     ) throws SQLException {
 547         final var sql = (
 548                 """
 549                 UPDATE %s
 550                 SET profile_last_update_timestamp = ?, profile_given_name = ?, profile_family_name = ?, profile_about = ?, profile_about_emoji = ?, profile_avatar_url_path = ?, profile_mobile_coin_address = ?, profile_unidentified_access_mode = ?, profile_capabilities = ?
 551                 WHERE _id = ?
 552                 """
 553         ).formatted(TABLE_RECIPIENT);
 554         try (final var statement = connection.prepareStatement(sql)) {
 555             statement.setLong(1, profile == null ? 0 : profile.getLastUpdateTimestamp());
 556             statement.setString(2, profile == null ? null : profile.getGivenName());
 557             statement.setString(3, profile == null ? null : profile.getFamilyName());
 558             statement.setString(4, profile == null ? null : profile.getAbout());
 559             statement.setString(5, profile == null ? null : profile.getAboutEmoji());
 560             statement.setString(6, profile == null ? null : profile.getAvatarUrlPath());
 561             statement.setBytes(7, profile == null ? null : profile.getMobileCoinAddress());
 562             statement.setString(8, profile == null ? null : profile.getUnidentifiedAccessMode().name());
 563             statement.setString(9,
 564                     profile == null
 565                             ? null
 566                             : profile.getCapabilities().stream().map(Enum::name).collect(Collectors.joining(",")));
 567             statement.setLong(10, recipientId.id());
 568             statement.executeUpdate();
 569         }
 570     }
 571
 572     private void storeProfileKey(
 573             Connection connection, RecipientId recipientId, final ProfileKey profileKey, boolean resetProfile
 574     ) throws SQLException {
 575         if (profileKey != null) {
 576             final var recipientProfileKey = getProfileKey(recipientId);
 577             if (profileKey.equals(recipientProfileKey)) {
 578                 final var recipientProfile = getProfile(recipientId);
 579                 if (recipientProfile == null || (
 580                         recipientProfile.getUnidentifiedAccessMode() != Profile.UnidentifiedAccessMode.UNKNOWN
 581                                 && recipientProfile.getUnidentifiedAccessMode()
 582                                 != Profile.UnidentifiedAccessMode.DISABLED
 583                 )) {
 584                     return;
 585                 }
 586             }
 587         }
 588
 589         final var sql = (
 590                 """
 591                 UPDATE %s
 592                 SET profile_key = ?, profile_key_credential = NULL%s
 593                 WHERE _id = ?
 594                 """
 595         ).formatted(TABLE_RECIPIENT, resetProfile ? ", profile_last_update_timestamp = 0" : "");
 596         try (final var statement = connection.prepareStatement(sql)) {
 597             statement.setBytes(1, profileKey == null ? null : profileKey.serialize());
 598             statement.setLong(2, recipientId.id());
 599             statement.executeUpdate();
 600         }
 601     }
 602
 603     private RecipientId resolveRecipientTrusted(RecipientAddress address, boolean isSelf) {
 604         final Pair<RecipientId, Optional<RecipientId>> pair;
 605         synchronized (recipientsLock) {
 606             try (final var connection = database.getConnection()) {
 607                 connection.setAutoCommit(false);
 608                 pair = resolveRecipientTrustedLocked(connection, address, isSelf);
 609                 connection.commit();
 610             } catch (SQLException e) {
 611                 throw new RuntimeException("Failed update recipient store", e);
 612             }
 613         }
 614
 615         if (pair.second().isPresent()) {
 616             try (final var connection = database.getConnection()) {
 617                 recipientMergeHandler.mergeRecipients(connection, pair.first(), pair.second().get());
 618                 deleteRecipient(connection, pair.second().get());
 619             } catch (SQLException e) {
 620                 throw new RuntimeException("Failed update recipient store", e);
 621             }
 622         }
 623         return pair.first();
 624     }
 625
 626     private Pair<RecipientId, Optional<RecipientId>> resolveRecipientTrustedLocked(
 627             Connection connection, RecipientAddress address, boolean isSelf
 628     ) throws SQLException {
 629         if (!isSelf) {
 630             if (selfAddressProvider.getSelfAddress().matches(address)) {
 631                 return new Pair<>(resolveRecipientLocked(connection, address), Optional.empty());
 632             }
 633         }
 634         final var byNumber = address.number().isEmpty()
 635                 ? Optional.<RecipientWithAddress>empty()
 636                 : findByNumber(connection, address.number().get());
 637         final var byUuid = address.serviceId().isEmpty()
 638                 ? Optional.<RecipientWithAddress>empty()
 639                 : findByServiceId(connection, address.serviceId().get());
 640
 641         if (byNumber.isEmpty() && byUuid.isEmpty()) {
 642             logger.debug("Got new recipient, both uuid and number are unknown");
 643             return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 644         }
 645
 646         if (address.serviceId().isEmpty() || address.number().isEmpty() || byNumber.equals(byUuid)) {
 647             return new Pair<>(byUuid.or(() -> byNumber).map(RecipientWithAddress::id).get(), Optional.empty());
 648         }
 649
 650         if (byNumber.isEmpty()) {
 651             logger.debug("Got recipient {} existing with uuid, updating with high trust number", byUuid.get().id());
 652             updateRecipientAddress(connection, byUuid.get().id(), address);
 653             return new Pair<>(byUuid.get().id(), Optional.empty());
 654         }
 655
 656         final var byNumberRecipient = byNumber.get();
 657
 658         if (byUuid.isEmpty()) {
 659             if (byNumberRecipient.address().serviceId().isPresent()) {
 660                 logger.debug(
 661                         "Got recipient {} existing with number, but different uuid, so stripping its number and adding new recipient",
 662                         byNumberRecipient.id());
 663
 664                 updateRecipientAddress(connection,
 665                         byNumberRecipient.id(),
 666                         new RecipientAddress(byNumberRecipient.address().serviceId().get()));
 667                 return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 668             }
 669
 670             logger.debug("Got recipient {} existing with number and no uuid, updating with high trust uuid",
 671                     byNumberRecipient.id());
 672             updateRecipientAddress(connection, byNumberRecipient.id(), address);
 673             return new Pair<>(byNumberRecipient.id(), Optional.empty());
 674         }
 675
 676         final var byUuidRecipient = byUuid.get();
 677
 678         if (byNumberRecipient.address().serviceId().isPresent()) {
 679             logger.debug(
 680                     "Got separate recipients for high trust number {} and uuid {}, recipient for number has different uuid, so stripping its number",
 681                     byNumberRecipient.id(),
 682                     byUuidRecipient.id());
 683
 684             updateRecipientAddress(connection,
 685                     byNumberRecipient.id(),
 686                     new RecipientAddress(byNumberRecipient.address().serviceId().get()));
 687             updateRecipientAddress(connection, byUuidRecipient.id(), address);
 688             return new Pair<>(byUuidRecipient.id(), Optional.empty());
 689         }
 690
 691         logger.debug("Got separate recipients for high trust number {} and uuid {}, need to merge them",
 692                 byNumberRecipient.id(),
 693                 byUuidRecipient.id());
 694         // Create a fixed RecipientId that won't update its id after merge
 695         final var toBeMergedRecipientId = new RecipientId(byNumberRecipient.id().id(), null);
 696         mergeRecipientsLocked(connection, byUuidRecipient.id(), toBeMergedRecipientId);
 697         removeRecipientAddress(connection, toBeMergedRecipientId);
 698         updateRecipientAddress(connection, byUuidRecipient.id(), address);
 699         return new Pair<>(byUuidRecipient.id(), Optional.of(toBeMergedRecipientId));
 700     }
 701
 702     private RecipientId resolveRecipientLocked(
 703             Connection connection, RecipientAddress address
 704     ) throws SQLException {
 705         final var byServiceId = address.serviceId().isEmpty()
 706                 ? Optional.<RecipientWithAddress>empty()
 707                 : findByServiceId(connection, address.serviceId().get());
 708
 709         if (byServiceId.isPresent()) {
 710             return byServiceId.get().id();
 711         }
 712
 713         final var byPni = address.pni().isEmpty()
 714                 ? Optional.<RecipientWithAddress>empty()
 715                 : findByServiceId(connection, address.pni().get());
 716
 717         if (byPni.isPresent()) {
 718             return byPni.get().id();
 719         }
 720
 721         final var byNumber = address.number().isEmpty()
 722                 ? Optional.<RecipientWithAddress>empty()
 723                 : findByNumber(connection, address.number().get());
 724
 725         if (byNumber.isPresent()) {
 726             return byNumber.get().id();
 727         }
 728
 729         logger.debug("Got new recipient, both serviceId and number are unknown");
 730
 731         if (address.serviceId().isEmpty()) {
 732             return addNewRecipient(connection, address);
 733         }
 734
 735         return addNewRecipient(connection, new RecipientAddress(address.serviceId().get()));
 736     }
 737
 738     private RecipientId resolveRecipientLocked(Connection connection, ServiceId serviceId) throws SQLException {
 739         final var recipient = findByServiceId(connection, serviceId);
 740
 741         if (recipient.isEmpty()) {
 742             logger.debug("Got new recipient, serviceId is unknown");
 743             return addNewRecipient(connection, new RecipientAddress(serviceId));
 744         }
 745
 746         return recipient.get().id();
 747     }
 748
 749     private RecipientId resolveRecipientLocked(Connection connection, String number) throws SQLException {
 750         final var recipient = findByNumber(connection, number);
 751
 752         if (recipient.isEmpty()) {
 753             logger.debug("Got new recipient, number is unknown");
 754             return addNewRecipient(connection, new RecipientAddress(null, number));
 755         }
 756
 757         return recipient.get().id();
 758     }
 759
 760     private RecipientId addNewRecipient(
 761             final Connection connection, final RecipientAddress address
 762     ) throws SQLException {
 763         final var sql = (
 764                 """
 765                 INSERT INTO %s (number, uuid)
 766                 VALUES (?, ?)
 767                 """
 768         ).formatted(TABLE_RECIPIENT);
 769         try (final var statement = connection.prepareStatement(sql)) {
 770             statement.setString(1, address.number().orElse(null));
 771             statement.setBytes(2, address.serviceId().map(ServiceId::uuid).map(UuidUtil::toByteArray).orElse(null));
 772             statement.executeUpdate();
 773             final var generatedKeys = statement.getGeneratedKeys();
 774             if (generatedKeys.next()) {
 775                 final var recipientId = new RecipientId(generatedKeys.getLong(1), this);
 776                 logger.debug("Added new recipient {} with address {}", recipientId, address);
 777                 return recipientId;
 778             } else {
 779                 throw new RuntimeException("Failed to add new recipient to database");
 780             }
 781         }
 782     }
 783
 784     private void removeRecipientAddress(Connection connection, RecipientId recipientId) throws SQLException {
 785         final var sql = (
 786                 """
 787                 UPDATE %s
 788                 SET number = NULL, uuid = NULL
 789                 WHERE _id = ?
 790                 """
 791         ).formatted(TABLE_RECIPIENT);
 792         try (final var statement = connection.prepareStatement(sql)) {
 793             statement.setLong(1, recipientId.id());
 794             statement.executeUpdate();
 795         }
 796     }
 797
 798     private void updateRecipientAddress(
 799             Connection connection, RecipientId recipientId, final RecipientAddress address
 800     ) throws SQLException {
 801         final var sql = (
 802                 """
 803                 UPDATE %s
 804                 SET number = ?, uuid = ?
 805                 WHERE _id = ?
 806                 """
 807         ).formatted(TABLE_RECIPIENT);
 808         try (final var statement = connection.prepareStatement(sql)) {
 809             statement.setString(1, address.number().orElse(null));
 810             statement.setBytes(2, address.serviceId().map(ServiceId::uuid).map(UuidUtil::toByteArray).orElse(null));
 811             statement.setLong(3, recipientId.id());
 812             statement.executeUpdate();
 813         }
 814     }
 815
 816     private void deleteRecipient(final Connection connection, final RecipientId recipientId) throws SQLException {
 817         final var sql = (
 818                 """
 819                 DELETE FROM %s
 820                 WHERE _id = ?
 821                 """
 822         ).formatted(TABLE_RECIPIENT);
 823         try (final var statement = connection.prepareStatement(sql)) {
 824             statement.setLong(1, recipientId.id());
 825             statement.executeUpdate();
 826         }
 827     }
 828
 829     private void mergeRecipientsLocked(
 830             Connection connection, RecipientId recipientId, RecipientId toBeMergedRecipientId
 831     ) throws SQLException {
 832         final var contact = getContact(connection, recipientId);
 833         if (contact == null) {
 834             final var toBeMergedContact = getContact(connection, toBeMergedRecipientId);
 835             storeContact(connection, recipientId, toBeMergedContact);
 836         }
 837
 838         final var profileKey = getProfileKey(connection, recipientId);
 839         if (profileKey == null) {
 840             final var toBeMergedProfileKey = getProfileKey(connection, toBeMergedRecipientId);
 841             storeProfileKey(connection, recipientId, toBeMergedProfileKey, false);
 842         }
 843
 844         final var profileKeyCredential = getExpiringProfileKeyCredential(connection, recipientId);
 845         if (profileKeyCredential == null) {
 846             final var toBeMergedProfileKeyCredential = getExpiringProfileKeyCredential(connection,
 847                     toBeMergedRecipientId);
 848             storeExpiringProfileKeyCredential(connection, recipientId, toBeMergedProfileKeyCredential);
 849         }
 850
 851         final var profile = getProfile(connection, recipientId);
 852         if (profile == null) {
 853             final var toBeMergedProfile = getProfile(connection, toBeMergedRecipientId);
 854             storeProfile(connection, recipientId, toBeMergedProfile);
 855         }
 856
 857         recipientsMerged.put(toBeMergedRecipientId.id(), recipientId.id());
 858     }
 859
 860     private Optional<RecipientWithAddress> findByNumber(
 861             final Connection connection, final String number
 862     ) throws SQLException {
 863         final var sql = """
 864                         SELECT r._id, r.number, r.uuid
 865                         FROM %s r
 866                         WHERE r.number = ?
 867                         """.formatted(TABLE_RECIPIENT);
 868         try (final var statement = connection.prepareStatement(sql)) {
 869             statement.setString(1, number);
 870             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 871         }
 872     }
 873
 874     private Optional<RecipientWithAddress> findByServiceId(
 875             final Connection connection, final ServiceId serviceId
 876     ) throws SQLException {
 877         final var sql = """
 878                         SELECT r._id, r.number, r.uuid
 879                         FROM %s r
 880                         WHERE r.uuid = ?
 881                         """.formatted(TABLE_RECIPIENT);
 882         try (final var statement = connection.prepareStatement(sql)) {
 883             statement.setBytes(1, UuidUtil.toByteArray(serviceId.uuid()));
 884             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 885         }
 886     }
 887
 888     private Contact getContact(final Connection connection, final RecipientId recipientId) throws SQLException {
 889         final var sql = (
 890                 """
 891                 SELECT r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 892                 FROM %s r
 893                 WHERE r._id = ? AND (%s)
 894                 """
 895         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 896         try (final var statement = connection.prepareStatement(sql)) {
 897             statement.setLong(1, recipientId.id());
 898             return Utils.executeQueryForOptional(statement, this::getContactFromResultSet).orElse(null);
 899         }
 900     }
 901
 902     private ProfileKey getProfileKey(final Connection connection, final RecipientId recipientId) throws SQLException {
 903         final var sql = (
 904                 """
 905                 SELECT r.profile_key
 906                 FROM %s r
 907                 WHERE r._id = ?
 908                 """
 909         ).formatted(TABLE_RECIPIENT);
 910         try (final var statement = connection.prepareStatement(sql)) {
 911             statement.setLong(1, recipientId.id());
 912             return Utils.executeQueryForOptional(statement, this::getProfileKeyFromResultSet).orElse(null);
 913         }
 914     }
 915
 916     private ExpiringProfileKeyCredential getExpiringProfileKeyCredential(
 917             final Connection connection, final RecipientId recipientId
 918     ) throws SQLException {
 919         final var sql = (
 920                 """
 921                 SELECT r.profile_key_credential
 922                 FROM %s r
 923                 WHERE r._id = ?
 924                 """
 925         ).formatted(TABLE_RECIPIENT);
 926         try (final var statement = connection.prepareStatement(sql)) {
 927             statement.setLong(1, recipientId.id());
 928             return Utils.executeQueryForOptional(statement, this::getExpiringProfileKeyCredentialFromResultSet)
 929                     .orElse(null);
 930         }
 931     }
 932
 933     private Profile getProfile(final Connection connection, final RecipientId recipientId) throws SQLException {
 934         final var sql = (
 935                 """
 936                 SELECT r.profile_last_update_timestamp, r.profile_given_name, r.profile_family_name, r.profile_about, r.profile_about_emoji, r.profile_avatar_url_path, r.profile_mobile_coin_address, r.profile_unidentified_access_mode, r.profile_capabilities
 937                 FROM %s r
 938                 WHERE r._id = ? AND r.profile_capabilities IS NOT NULL
 939                 """
 940         ).formatted(TABLE_RECIPIENT);
 941         try (final var statement = connection.prepareStatement(sql)) {
 942             statement.setLong(1, recipientId.id());
 943             return Utils.executeQueryForOptional(statement, this::getProfileFromResultSet).orElse(null);
 944         }
 945     }
 946
 947     private RecipientAddress getRecipientAddressFromResultSet(ResultSet resultSet) throws SQLException {
 948         final var serviceId = Optional.ofNullable(resultSet.getBytes("uuid")).map(ServiceId::parseOrNull);
 949         final var number = Optional.ofNullable(resultSet.getString("number"));
 950         return new RecipientAddress(serviceId, Optional.empty(), number);
 951     }
 952
 953     private RecipientId getRecipientIdFromResultSet(ResultSet resultSet) throws SQLException {
 954         return new RecipientId(resultSet.getLong("_id"), this);
 955     }
 956
 957     private RecipientWithAddress getRecipientWithAddressFromResultSet(final ResultSet resultSet) throws SQLException {
 958         return new RecipientWithAddress(getRecipientIdFromResultSet(resultSet),
 959                 getRecipientAddressFromResultSet(resultSet));
 960     }
 961
 962     private Recipient getRecipientFromResultSet(final ResultSet resultSet) throws SQLException {
 963         return new Recipient(getRecipientIdFromResultSet(resultSet),
 964                 getRecipientAddressFromResultSet(resultSet),
 965                 getContactFromResultSet(resultSet),
 966                 getProfileKeyFromResultSet(resultSet),
 967                 getExpiringProfileKeyCredentialFromResultSet(resultSet),
 968                 getProfileFromResultSet(resultSet));
 969     }
 970
 971     private Contact getContactFromResultSet(ResultSet resultSet) throws SQLException {
 972         return new Contact(resultSet.getString("given_name"),
 973                 resultSet.getString("family_name"),
 974                 resultSet.getString("color"),
 975                 resultSet.getInt("expiration_time"),
 976                 resultSet.getBoolean("blocked"),
 977                 resultSet.getBoolean("archived"),
 978                 resultSet.getBoolean("profile_sharing"));
 979     }
 980
 981     private Profile getProfileFromResultSet(ResultSet resultSet) throws SQLException {
 982         final var profileCapabilities = resultSet.getString("profile_capabilities");
 983         final var profileUnidentifiedAccessMode = resultSet.getString("profile_unidentified_access_mode");
 984         return new Profile(resultSet.getLong("profile_last_update_timestamp"),
 985                 resultSet.getString("profile_given_name"),
 986                 resultSet.getString("profile_family_name"),
 987                 resultSet.getString("profile_about"),
 988                 resultSet.getString("profile_about_emoji"),
 989                 resultSet.getString("profile_avatar_url_path"),
 990                 resultSet.getBytes("profile_mobile_coin_address"),
 991                 profileUnidentifiedAccessMode == null
 992                         ? Profile.UnidentifiedAccessMode.UNKNOWN
 993                         : Profile.UnidentifiedAccessMode.valueOfOrUnknown(profileUnidentifiedAccessMode),
 994                 profileCapabilities == null
 995                         ? Set.of()
 996                         : Arrays.stream(profileCapabilities.split(","))
 997                                 .map(Profile.Capability::valueOfOrNull)
 998                                 .filter(Objects::nonNull)
 999                                 .collect(Collectors.toSet()));
1000     }
1001
1002     private ProfileKey getProfileKeyFromResultSet(ResultSet resultSet) throws SQLException {
1003         final var profileKey = resultSet.getBytes("profile_key");
1004
1005         if (profileKey == null) {
1006             return null;
1007         }
1008         try {
1009             return new ProfileKey(profileKey);
1010         } catch (InvalidInputException ignored) {
1011             return null;
1012         }
1013     }
1014
1015     private ExpiringProfileKeyCredential getExpiringProfileKeyCredentialFromResultSet(ResultSet resultSet) throws SQLException {
1016         final var profileKeyCredential = resultSet.getBytes("profile_key_credential");
1017
1018         if (profileKeyCredential == null) {
1019             return null;
1020         }
1021         try {
1022             return new ExpiringProfileKeyCredential(profileKeyCredential);
1023         } catch (Throwable ignored) {
1024             return null;
1025         }
1026     }
1027
1028     public interface RecipientMergeHandler {
1029
1030         void mergeRecipients(
1031                 final Connection connection, RecipientId recipientId, RecipientId toBeMergedRecipientId
1032         ) throws SQLException;
1033     }
1034
1035     private record RecipientWithAddress(RecipientId id, RecipientAddress address) {}
1036 }