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     /**
 149      * Should only be used for recipientIds from the database.
 150      * Where the foreign key relations ensure a valid recipientId.
 151      */
 152     @Override
 153     public RecipientId create(final long recipientId) {
 154         return new RecipientId(recipientId, this);
 155     }
 156
 157     public RecipientId resolveRecipient(
 158             final String number, Supplier<ServiceId> serviceIdSupplier
 159     ) throws UnregisteredRecipientException {
 160         final Optional<RecipientWithAddress> byNumber;
 161         try (final var connection = database.getConnection()) {
 162             byNumber = findByNumber(connection, number);
 163         } catch (SQLException e) {
 164             throw new RuntimeException("Failed read from recipient store", e);
 165         }
 166         if (byNumber.isEmpty() || byNumber.get().address().serviceId().isEmpty()) {
 167             final var serviceId = serviceIdSupplier.get();
 168             if (serviceId == null) {
 169                 throw new UnregisteredRecipientException(new org.asamk.signal.manager.api.RecipientAddress(null,
 170                         number));
 171             }
 172
 173             return resolveRecipient(new RecipientAddress(serviceId, number), false, false);
 174         }
 175         return byNumber.get().id();
 176     }
 177
 178     public RecipientId resolveRecipient(RecipientAddress address) {
 179         return resolveRecipient(address, false, false);
 180     }
 181
 182     @Override
 183     public RecipientId resolveSelfRecipientTrusted(RecipientAddress address) {
 184         return resolveRecipient(address, true, true);
 185     }
 186
 187     public RecipientId resolveRecipientTrusted(RecipientAddress address) {
 188         return resolveRecipient(address, true, false);
 189     }
 190
 191     @Override
 192     public RecipientId resolveRecipientTrusted(SignalServiceAddress address) {
 193         return resolveRecipient(new RecipientAddress(address), true, false);
 194     }
 195
 196     @Override
 197     public RecipientId resolveRecipientTrusted(
 198             final Optional<ACI> aci, final Optional<PNI> pni, final Optional<String> number
 199     ) {
 200         final var serviceId = aci.map(a -> (ServiceId) a).or(() -> pni);
 201         return resolveRecipient(new RecipientAddress(serviceId, number), true, false);
 202     }
 203
 204     @Override
 205     public void storeContact(RecipientId recipientId, final Contact contact) {
 206         try (final var connection = database.getConnection()) {
 207             storeContact(connection, recipientId, contact);
 208         } catch (SQLException e) {
 209             throw new RuntimeException("Failed update recipient store", e);
 210         }
 211     }
 212
 213     @Override
 214     public Contact getContact(RecipientId recipientId) {
 215         try (final var connection = database.getConnection()) {
 216             return getContact(connection, recipientId);
 217         } catch (SQLException e) {
 218             throw new RuntimeException("Failed read from recipient store", e);
 219         }
 220     }
 221
 222     @Override
 223     public List<Pair<RecipientId, Contact>> getContacts() {
 224         final var sql = (
 225                 """
 226                 SELECT r._id, r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 227                 FROM %s r
 228                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 229                 """
 230         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 231         try (final var connection = database.getConnection()) {
 232             try (final var statement = connection.prepareStatement(sql)) {
 233                 try (var result = Utils.executeQueryForStream(statement,
 234                         resultSet -> new Pair<>(getRecipientIdFromResultSet(resultSet),
 235                                 getContactFromResultSet(resultSet)))) {
 236                     return result.toList();
 237                 }
 238             }
 239         } catch (SQLException e) {
 240             throw new RuntimeException("Failed read from recipient store", e);
 241         }
 242     }
 243
 244     public List<Recipient> getRecipients(
 245             boolean onlyContacts, Optional<Boolean> blocked, Set<RecipientId> recipientIds, Optional<String> name
 246     ) {
 247         final var sqlWhere = new ArrayList<String>();
 248         if (onlyContacts) {
 249             sqlWhere.add("(" + SQL_IS_CONTACT + ")");
 250         }
 251         if (blocked.isPresent()) {
 252             sqlWhere.add("r.blocked = ?");
 253         }
 254         if (!recipientIds.isEmpty()) {
 255             final var recipientIdsCommaSeparated = recipientIds.stream()
 256                     .map(recipientId -> String.valueOf(recipientId.id()))
 257                     .collect(Collectors.joining(","));
 258             sqlWhere.add("r._id IN (" + recipientIdsCommaSeparated + ")");
 259         }
 260         final var sql = (
 261                 """
 262                 SELECT r._id,
 263                        r.number, r.uuid,
 264                        r.profile_key, r.profile_key_credential,
 265                        r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived,
 266                        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
 267                 FROM %s r
 268                 WHERE (r.number IS NOT NULL OR r.uuid IS NOT NULL) AND %s
 269                 """
 270         ).formatted(TABLE_RECIPIENT, sqlWhere.size() == 0 ? "TRUE" : String.join(" AND ", sqlWhere));
 271         try (final var connection = database.getConnection()) {
 272             try (final var statement = connection.prepareStatement(sql)) {
 273                 if (blocked.isPresent()) {
 274                     statement.setBoolean(1, blocked.get());
 275                 }
 276                 try (var result = Utils.executeQueryForStream(statement, this::getRecipientFromResultSet)) {
 277                     return result.filter(r -> name.isEmpty() || (
 278                             r.getContact() != null && name.get().equals(r.getContact().getName())
 279                     ) || (r.getProfile() != null && name.get().equals(r.getProfile().getDisplayName()))).toList();
 280                 }
 281             }
 282         } catch (SQLException e) {
 283             throw new RuntimeException("Failed read from recipient store", e);
 284         }
 285     }
 286
 287     public Map<ServiceId, ProfileKey> getServiceIdToProfileKeyMap() {
 288         final var sql = (
 289                 """
 290                 SELECT r.uuid, r.profile_key
 291                 FROM %s r
 292                 WHERE r.uuid IS NOT NULL AND r.profile_key IS NOT NULL
 293                 """
 294         ).formatted(TABLE_RECIPIENT);
 295         try (final var connection = database.getConnection()) {
 296             try (final var statement = connection.prepareStatement(sql)) {
 297                 return Utils.executeQueryForStream(statement, resultSet -> {
 298                     final var serviceId = ServiceId.parseOrThrow(resultSet.getBytes("uuid"));
 299                     final var profileKey = getProfileKeyFromResultSet(resultSet);
 300                     return new Pair<>(serviceId, profileKey);
 301                 }).filter(Objects::nonNull).collect(Collectors.toMap(Pair::first, Pair::second));
 302             }
 303         } catch (SQLException e) {
 304             throw new RuntimeException("Failed read from recipient store", e);
 305         }
 306     }
 307
 308     @Override
 309     public void deleteContact(RecipientId recipientId) {
 310         storeContact(recipientId, null);
 311     }
 312
 313     public void deleteRecipientData(RecipientId recipientId) {
 314         logger.debug("Deleting recipient data for {}", recipientId);
 315         try (final var connection = database.getConnection()) {
 316             connection.setAutoCommit(false);
 317             storeContact(connection, recipientId, null);
 318             storeProfile(connection, recipientId, null);
 319             storeProfileKey(connection, recipientId, null, false);
 320             storeExpiringProfileKeyCredential(connection, recipientId, null);
 321             deleteRecipient(connection, recipientId);
 322             connection.commit();
 323         } catch (SQLException e) {
 324             throw new RuntimeException("Failed update recipient store", e);
 325         }
 326     }
 327
 328     @Override
 329     public Profile getProfile(final RecipientId recipientId) {
 330         try (final var connection = database.getConnection()) {
 331             return getProfile(connection, recipientId);
 332         } catch (SQLException e) {
 333             throw new RuntimeException("Failed read from recipient store", e);
 334         }
 335     }
 336
 337     @Override
 338     public ProfileKey getProfileKey(final RecipientId recipientId) {
 339         try (final var connection = database.getConnection()) {
 340             return getProfileKey(connection, recipientId);
 341         } catch (SQLException e) {
 342             throw new RuntimeException("Failed read from recipient store", e);
 343         }
 344     }
 345
 346     @Override
 347     public ExpiringProfileKeyCredential getExpiringProfileKeyCredential(final RecipientId recipientId) {
 348         try (final var connection = database.getConnection()) {
 349             return getExpiringProfileKeyCredential(connection, recipientId);
 350         } catch (SQLException e) {
 351             throw new RuntimeException("Failed read from recipient store", e);
 352         }
 353     }
 354
 355     @Override
 356     public void storeProfile(RecipientId recipientId, final Profile profile) {
 357         try (final var connection = database.getConnection()) {
 358             storeProfile(connection, recipientId, profile);
 359         } catch (SQLException e) {
 360             throw new RuntimeException("Failed update recipient store", e);
 361         }
 362     }
 363
 364     @Override
 365     public void storeSelfProfileKey(final RecipientId recipientId, final ProfileKey profileKey) {
 366         try (final var connection = database.getConnection()) {
 367             storeProfileKey(connection, recipientId, profileKey, false);
 368         } catch (SQLException e) {
 369             throw new RuntimeException("Failed update recipient store", e);
 370         }
 371     }
 372
 373     @Override
 374     public void storeProfileKey(RecipientId recipientId, final ProfileKey profileKey) {
 375         try (final var connection = database.getConnection()) {
 376             storeProfileKey(connection, recipientId, profileKey, true);
 377         } catch (SQLException e) {
 378             throw new RuntimeException("Failed update recipient store", e);
 379         }
 380     }
 381
 382     @Override
 383     public void storeExpiringProfileKeyCredential(
 384             RecipientId recipientId, final ExpiringProfileKeyCredential profileKeyCredential
 385     ) {
 386         try (final var connection = database.getConnection()) {
 387             storeExpiringProfileKeyCredential(connection, recipientId, profileKeyCredential);
 388         } catch (SQLException e) {
 389             throw new RuntimeException("Failed update recipient store", e);
 390         }
 391     }
 392
 393     void addLegacyRecipients(final Map<RecipientId, Recipient> recipients) {
 394         logger.debug("Migrating legacy recipients to database");
 395         long start = System.nanoTime();
 396         final var sql = (
 397                 """
 398                 INSERT INTO %s (_id, number, uuid)
 399                 VALUES (?, ?, ?)
 400                 """
 401         ).formatted(TABLE_RECIPIENT);
 402         try (final var connection = database.getConnection()) {
 403             connection.setAutoCommit(false);
 404             try (final var statement = connection.prepareStatement("DELETE FROM %s".formatted(TABLE_RECIPIENT))) {
 405                 statement.executeUpdate();
 406             }
 407             try (final var statement = connection.prepareStatement(sql)) {
 408                 for (final var recipient : recipients.values()) {
 409                     statement.setLong(1, recipient.getRecipientId().id());
 410                     statement.setString(2, recipient.getAddress().number().orElse(null));
 411                     statement.setBytes(3,
 412                             recipient.getAddress()
 413                                     .serviceId()
 414                                     .map(ServiceId::uuid)
 415                                     .map(UuidUtil::toByteArray)
 416                                     .orElse(null));
 417                     statement.executeUpdate();
 418                 }
 419             }
 420             logger.debug("Initial inserts took {}ms", (System.nanoTime() - start) / 1000000);
 421
 422             for (final var recipient : recipients.values()) {
 423                 if (recipient.getContact() != null) {
 424                     storeContact(connection, recipient.getRecipientId(), recipient.getContact());
 425                 }
 426                 if (recipient.getProfile() != null) {
 427                     storeProfile(connection, recipient.getRecipientId(), recipient.getProfile());
 428                 }
 429                 if (recipient.getProfileKey() != null) {
 430                     storeProfileKey(connection, recipient.getRecipientId(), recipient.getProfileKey(), false);
 431                 }
 432                 if (recipient.getExpiringProfileKeyCredential() != null) {
 433                     storeExpiringProfileKeyCredential(connection,
 434                             recipient.getRecipientId(),
 435                             recipient.getExpiringProfileKeyCredential());
 436                 }
 437             }
 438             connection.commit();
 439         } catch (SQLException e) {
 440             throw new RuntimeException("Failed update recipient store", e);
 441         }
 442         logger.debug("Complete recipients migration took {}ms", (System.nanoTime() - start) / 1000000);
 443     }
 444
 445     long getActualRecipientId(long recipientId) {
 446         while (recipientsMerged.containsKey(recipientId)) {
 447             final var newRecipientId = recipientsMerged.get(recipientId);
 448             logger.debug("Using {} instead of {}, because recipients have been merged", newRecipientId, recipientId);
 449             recipientId = newRecipientId;
 450         }
 451         return recipientId;
 452     }
 453
 454     private void storeContact(
 455             final Connection connection, final RecipientId recipientId, final Contact contact
 456     ) throws SQLException {
 457         final var sql = (
 458                 """
 459                 UPDATE %s
 460                 SET given_name = ?, family_name = ?, expiration_time = ?, profile_sharing = ?, color = ?, blocked = ?, archived = ?
 461                 WHERE _id = ?
 462                 """
 463         ).formatted(TABLE_RECIPIENT);
 464         try (final var statement = connection.prepareStatement(sql)) {
 465             statement.setString(1, contact == null ? null : contact.getGivenName());
 466             statement.setString(2, contact == null ? null : contact.getFamilyName());
 467             statement.setInt(3, contact == null ? 0 : contact.getMessageExpirationTime());
 468             statement.setBoolean(4, contact != null && contact.isProfileSharingEnabled());
 469             statement.setString(5, contact == null ? null : contact.getColor());
 470             statement.setBoolean(6, contact != null && contact.isBlocked());
 471             statement.setBoolean(7, contact != null && contact.isArchived());
 472             statement.setLong(8, recipientId.id());
 473             statement.executeUpdate();
 474         }
 475     }
 476
 477     private void storeExpiringProfileKeyCredential(
 478             final Connection connection,
 479             final RecipientId recipientId,
 480             final ExpiringProfileKeyCredential profileKeyCredential
 481     ) throws SQLException {
 482         final var sql = (
 483                 """
 484                 UPDATE %s
 485                 SET profile_key_credential = ?
 486                 WHERE _id = ?
 487                 """
 488         ).formatted(TABLE_RECIPIENT);
 489         try (final var statement = connection.prepareStatement(sql)) {
 490             statement.setBytes(1, profileKeyCredential == null ? null : profileKeyCredential.serialize());
 491             statement.setLong(2, recipientId.id());
 492             statement.executeUpdate();
 493         }
 494     }
 495
 496     private void storeProfile(
 497             final Connection connection, final RecipientId recipientId, final Profile profile
 498     ) throws SQLException {
 499         final var sql = (
 500                 """
 501                 UPDATE %s
 502                 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 = ?
 503                 WHERE _id = ?
 504                 """
 505         ).formatted(TABLE_RECIPIENT);
 506         try (final var statement = connection.prepareStatement(sql)) {
 507             statement.setLong(1, profile == null ? 0 : profile.getLastUpdateTimestamp());
 508             statement.setString(2, profile == null ? null : profile.getGivenName());
 509             statement.setString(3, profile == null ? null : profile.getFamilyName());
 510             statement.setString(4, profile == null ? null : profile.getAbout());
 511             statement.setString(5, profile == null ? null : profile.getAboutEmoji());
 512             statement.setString(6, profile == null ? null : profile.getAvatarUrlPath());
 513             statement.setBytes(7, profile == null ? null : profile.getMobileCoinAddress());
 514             statement.setString(8, profile == null ? null : profile.getUnidentifiedAccessMode().name());
 515             statement.setString(9,
 516                     profile == null
 517                             ? null
 518                             : profile.getCapabilities().stream().map(Enum::name).collect(Collectors.joining(",")));
 519             statement.setLong(10, recipientId.id());
 520             statement.executeUpdate();
 521         }
 522     }
 523
 524     private void storeProfileKey(
 525             Connection connection, RecipientId recipientId, final ProfileKey profileKey, boolean resetProfile
 526     ) throws SQLException {
 527         if (profileKey != null) {
 528             final var recipientProfileKey = getProfileKey(recipientId);
 529             if (profileKey.equals(recipientProfileKey)) {
 530                 final var recipientProfile = getProfile(recipientId);
 531                 if (recipientProfile == null || (
 532                         recipientProfile.getUnidentifiedAccessMode() != Profile.UnidentifiedAccessMode.UNKNOWN
 533                                 && recipientProfile.getUnidentifiedAccessMode()
 534                                 != Profile.UnidentifiedAccessMode.DISABLED
 535                 )) {
 536                     return;
 537                 }
 538             }
 539         }
 540
 541         final var sql = (
 542                 """
 543                 UPDATE %s
 544                 SET profile_key = ?, profile_key_credential = NULL%s
 545                 WHERE _id = ?
 546                 """
 547         ).formatted(TABLE_RECIPIENT, resetProfile ? ", profile_last_update_timestamp = 0" : "");
 548         try (final var statement = connection.prepareStatement(sql)) {
 549             statement.setBytes(1, profileKey == null ? null : profileKey.serialize());
 550             statement.setLong(2, recipientId.id());
 551             statement.executeUpdate();
 552         }
 553     }
 554
 555     /**
 556      * @param isHighTrust true, if the number/uuid connection was obtained from a trusted source.
 557      *                    Has no effect, if the address contains only a number or a uuid.
 558      */
 559     private RecipientId resolveRecipient(RecipientAddress address, boolean isHighTrust, boolean isSelf) {
 560         final Pair<RecipientId, Optional<RecipientId>> pair;
 561         synchronized (recipientsLock) {
 562             try (final var connection = database.getConnection()) {
 563                 connection.setAutoCommit(false);
 564                 pair = resolveRecipientLocked(connection, address, isHighTrust, isSelf);
 565                 connection.commit();
 566             } catch (SQLException e) {
 567                 throw new RuntimeException("Failed update recipient store", e);
 568             }
 569         }
 570
 571         if (pair.second().isPresent()) {
 572             try (final var connection = database.getConnection()) {
 573                 recipientMergeHandler.mergeRecipients(connection, pair.first(), pair.second().get());
 574                 deleteRecipient(connection, pair.second().get());
 575             } catch (SQLException e) {
 576                 throw new RuntimeException("Failed update recipient store", e);
 577             }
 578         }
 579         return pair.first();
 580     }
 581
 582     private Pair<RecipientId, Optional<RecipientId>> resolveRecipientLocked(
 583             Connection connection, RecipientAddress address, boolean isHighTrust, boolean isSelf
 584     ) throws SQLException {
 585         if (isHighTrust && !isSelf) {
 586             if (selfAddressProvider.getSelfAddress().matches(address)) {
 587                 isHighTrust = false;
 588             }
 589         }
 590         final var byNumber = address.number().isEmpty()
 591                 ? Optional.<RecipientWithAddress>empty()
 592                 : findByNumber(connection, address.number().get());
 593         final var byUuid = address.serviceId().isEmpty()
 594                 ? Optional.<RecipientWithAddress>empty()
 595                 : findByServiceId(connection, address.serviceId().get());
 596
 597         if (byNumber.isEmpty() && byUuid.isEmpty()) {
 598             logger.debug("Got new recipient, both uuid and number are unknown");
 599
 600             if (isHighTrust || address.serviceId().isEmpty() || address.number().isEmpty()) {
 601                 return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 602             }
 603
 604             return new Pair<>(addNewRecipient(connection, new RecipientAddress(address.serviceId().get())),
 605                     Optional.empty());
 606         }
 607
 608         if (!isHighTrust || address.serviceId().isEmpty() || address.number().isEmpty() || byNumber.equals(byUuid)) {
 609             return new Pair<>(byUuid.or(() -> byNumber).map(RecipientWithAddress::id).get(), Optional.empty());
 610         }
 611
 612         if (byNumber.isEmpty()) {
 613             logger.debug("Got recipient {} existing with uuid, updating with high trust number", byUuid.get().id());
 614             updateRecipientAddress(connection, byUuid.get().id(), address);
 615             return new Pair<>(byUuid.get().id(), Optional.empty());
 616         }
 617
 618         final var byNumberRecipient = byNumber.get();
 619
 620         if (byUuid.isEmpty()) {
 621             if (byNumberRecipient.address().serviceId().isPresent()) {
 622                 logger.debug(
 623                         "Got recipient {} existing with number, but different uuid, so stripping its number and adding new recipient",
 624                         byNumberRecipient.id());
 625
 626                 updateRecipientAddress(connection,
 627                         byNumberRecipient.id(),
 628                         new RecipientAddress(byNumberRecipient.address().serviceId().get()));
 629                 return new Pair<>(addNewRecipient(connection, address), Optional.empty());
 630             }
 631
 632             logger.debug("Got recipient {} existing with number and no uuid, updating with high trust uuid",
 633                     byNumberRecipient.id());
 634             updateRecipientAddress(connection, byNumberRecipient.id(), address);
 635             return new Pair<>(byNumberRecipient.id(), Optional.empty());
 636         }
 637
 638         final var byUuidRecipient = byUuid.get();
 639
 640         if (byNumberRecipient.address().serviceId().isPresent()) {
 641             logger.debug(
 642                     "Got separate recipients for high trust number {} and uuid {}, recipient for number has different uuid, so stripping its number",
 643                     byNumberRecipient.id(),
 644                     byUuidRecipient.id());
 645
 646             updateRecipientAddress(connection,
 647                     byNumberRecipient.id(),
 648                     new RecipientAddress(byNumberRecipient.address().serviceId().get()));
 649             updateRecipientAddress(connection, byUuidRecipient.id(), address);
 650             return new Pair<>(byUuidRecipient.id(), Optional.empty());
 651         }
 652
 653         logger.debug("Got separate recipients for high trust number {} and uuid {}, need to merge them",
 654                 byNumberRecipient.id(),
 655                 byUuidRecipient.id());
 656         // Create a fixed RecipientId that won't update its id after merge
 657         final var toBeMergedRecipientId = new RecipientId(byNumberRecipient.id().id(), null);
 658         mergeRecipientsLocked(connection, byUuidRecipient.id(), toBeMergedRecipientId);
 659         removeRecipientAddress(connection, toBeMergedRecipientId);
 660         updateRecipientAddress(connection, byUuidRecipient.id(), address);
 661         return new Pair<>(byUuidRecipient.id(), Optional.of(toBeMergedRecipientId));
 662     }
 663
 664     private RecipientId addNewRecipient(
 665             final Connection connection, final RecipientAddress address
 666     ) throws SQLException {
 667         final var sql = (
 668                 """
 669                 INSERT INTO %s (number, uuid)
 670                 VALUES (?, ?)
 671                 """
 672         ).formatted(TABLE_RECIPIENT);
 673         try (final var statement = connection.prepareStatement(sql)) {
 674             statement.setString(1, address.number().orElse(null));
 675             statement.setBytes(2, address.serviceId().map(ServiceId::uuid).map(UuidUtil::toByteArray).orElse(null));
 676             statement.executeUpdate();
 677             final var generatedKeys = statement.getGeneratedKeys();
 678             if (generatedKeys.next()) {
 679                 final var recipientId = new RecipientId(generatedKeys.getLong(1), this);
 680                 logger.debug("Added new recipient {} with address {}", recipientId, address);
 681                 return recipientId;
 682             } else {
 683                 throw new RuntimeException("Failed to add new recipient to database");
 684             }
 685         }
 686     }
 687
 688     private void removeRecipientAddress(Connection connection, RecipientId recipientId) throws SQLException {
 689         final var sql = (
 690                 """
 691                 UPDATE %s
 692                 SET number = NULL, uuid = NULL
 693                 WHERE _id = ?
 694                 """
 695         ).formatted(TABLE_RECIPIENT);
 696         try (final var statement = connection.prepareStatement(sql)) {
 697             statement.setLong(1, recipientId.id());
 698             statement.executeUpdate();
 699         }
 700     }
 701
 702     private void updateRecipientAddress(
 703             Connection connection, RecipientId recipientId, final RecipientAddress address
 704     ) throws SQLException {
 705         final var sql = (
 706                 """
 707                 UPDATE %s
 708                 SET number = ?, uuid = ?
 709                 WHERE _id = ?
 710                 """
 711         ).formatted(TABLE_RECIPIENT);
 712         try (final var statement = connection.prepareStatement(sql)) {
 713             statement.setString(1, address.number().orElse(null));
 714             statement.setBytes(2, address.serviceId().map(ServiceId::uuid).map(UuidUtil::toByteArray).orElse(null));
 715             statement.setLong(3, recipientId.id());
 716             statement.executeUpdate();
 717         }
 718     }
 719
 720     private void deleteRecipient(final Connection connection, final RecipientId recipientId) throws SQLException {
 721         final var sql = (
 722                 """
 723                 DELETE FROM %s
 724                 WHERE _id = ?
 725                 """
 726         ).formatted(TABLE_RECIPIENT);
 727         try (final var statement = connection.prepareStatement(sql)) {
 728             statement.setLong(1, recipientId.id());
 729             statement.executeUpdate();
 730         }
 731     }
 732
 733     private void mergeRecipientsLocked(
 734             Connection connection, RecipientId recipientId, RecipientId toBeMergedRecipientId
 735     ) throws SQLException {
 736         final var contact = getContact(connection, recipientId);
 737         if (contact == null) {
 738             final var toBeMergedContact = getContact(connection, toBeMergedRecipientId);
 739             storeContact(connection, recipientId, toBeMergedContact);
 740         }
 741
 742         final var profileKey = getProfileKey(connection, recipientId);
 743         if (profileKey == null) {
 744             final var toBeMergedProfileKey = getProfileKey(connection, toBeMergedRecipientId);
 745             storeProfileKey(connection, recipientId, toBeMergedProfileKey, false);
 746         }
 747
 748         final var profileKeyCredential = getExpiringProfileKeyCredential(connection, recipientId);
 749         if (profileKeyCredential == null) {
 750             final var toBeMergedProfileKeyCredential = getExpiringProfileKeyCredential(connection,
 751                     toBeMergedRecipientId);
 752             storeExpiringProfileKeyCredential(connection, recipientId, toBeMergedProfileKeyCredential);
 753         }
 754
 755         final var profile = getProfile(connection, recipientId);
 756         if (profile == null) {
 757             final var toBeMergedProfile = getProfile(connection, toBeMergedRecipientId);
 758             storeProfile(connection, recipientId, toBeMergedProfile);
 759         }
 760
 761         recipientsMerged.put(toBeMergedRecipientId.id(), recipientId.id());
 762     }
 763
 764     private Optional<RecipientWithAddress> findByNumber(
 765             final Connection connection, final String number
 766     ) throws SQLException {
 767         final var sql = """
 768                         SELECT r._id, r.number, r.uuid
 769                         FROM %s r
 770                         WHERE r.number = ?
 771                         """.formatted(TABLE_RECIPIENT);
 772         try (final var statement = connection.prepareStatement(sql)) {
 773             statement.setString(1, number);
 774             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 775         }
 776     }
 777
 778     private Optional<RecipientWithAddress> findByServiceId(
 779             final Connection connection, final ServiceId serviceId
 780     ) throws SQLException {
 781         final var sql = """
 782                         SELECT r._id, r.number, r.uuid
 783                         FROM %s r
 784                         WHERE r.uuid = ?
 785                         """.formatted(TABLE_RECIPIENT);
 786         try (final var statement = connection.prepareStatement(sql)) {
 787             statement.setBytes(1, UuidUtil.toByteArray(serviceId.uuid()));
 788             return Utils.executeQueryForOptional(statement, this::getRecipientWithAddressFromResultSet);
 789         }
 790     }
 791
 792     private Contact getContact(final Connection connection, final RecipientId recipientId) throws SQLException {
 793         final var sql = (
 794                 """
 795                 SELECT r.given_name, r.family_name, r.expiration_time, r.profile_sharing, r.color, r.blocked, r.archived
 796                 FROM %s r
 797                 WHERE r._id = ? AND (%s)
 798                 """
 799         ).formatted(TABLE_RECIPIENT, SQL_IS_CONTACT);
 800         try (final var statement = connection.prepareStatement(sql)) {
 801             statement.setLong(1, recipientId.id());
 802             return Utils.executeQueryForOptional(statement, this::getContactFromResultSet).orElse(null);
 803         }
 804     }
 805
 806     private ProfileKey getProfileKey(final Connection connection, final RecipientId recipientId) throws SQLException {
 807         final var sql = (
 808                 """
 809                 SELECT r.profile_key
 810                 FROM %s r
 811                 WHERE r._id = ?
 812                 """
 813         ).formatted(TABLE_RECIPIENT);
 814         try (final var statement = connection.prepareStatement(sql)) {
 815             statement.setLong(1, recipientId.id());
 816             return Utils.executeQueryForOptional(statement, this::getProfileKeyFromResultSet).orElse(null);
 817         }
 818     }
 819
 820     private ExpiringProfileKeyCredential getExpiringProfileKeyCredential(
 821             final Connection connection, final RecipientId recipientId
 822     ) throws SQLException {
 823         final var sql = (
 824                 """
 825                 SELECT r.profile_key_credential
 826                 FROM %s r
 827                 WHERE r._id = ?
 828                 """
 829         ).formatted(TABLE_RECIPIENT);
 830         try (final var statement = connection.prepareStatement(sql)) {
 831             statement.setLong(1, recipientId.id());
 832             return Utils.executeQueryForOptional(statement, this::getExpiringProfileKeyCredentialFromResultSet)
 833                     .orElse(null);
 834         }
 835     }
 836
 837     private Profile getProfile(final Connection connection, final RecipientId recipientId) throws SQLException {
 838         final var sql = (
 839                 """
 840                 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
 841                 FROM %s r
 842                 WHERE r._id = ? AND r.profile_capabilities IS NOT NULL
 843                 """
 844         ).formatted(TABLE_RECIPIENT);
 845         try (final var statement = connection.prepareStatement(sql)) {
 846             statement.setLong(1, recipientId.id());
 847             return Utils.executeQueryForOptional(statement, this::getProfileFromResultSet).orElse(null);
 848         }
 849     }
 850
 851     private RecipientAddress getRecipientAddressFromResultSet(ResultSet resultSet) throws SQLException {
 852         final var serviceId = Optional.ofNullable(resultSet.getBytes("uuid")).map(ServiceId::parseOrNull);
 853         final var number = Optional.ofNullable(resultSet.getString("number"));
 854         return new RecipientAddress(serviceId, Optional.empty(), number);
 855     }
 856
 857     private RecipientId getRecipientIdFromResultSet(ResultSet resultSet) throws SQLException {
 858         return new RecipientId(resultSet.getLong("_id"), this);
 859     }
 860
 861     private RecipientWithAddress getRecipientWithAddressFromResultSet(final ResultSet resultSet) throws SQLException {
 862         return new RecipientWithAddress(getRecipientIdFromResultSet(resultSet),
 863                 getRecipientAddressFromResultSet(resultSet));
 864     }
 865
 866     private Recipient getRecipientFromResultSet(final ResultSet resultSet) throws SQLException {
 867         return new Recipient(getRecipientIdFromResultSet(resultSet),
 868                 getRecipientAddressFromResultSet(resultSet),
 869                 getContactFromResultSet(resultSet),
 870                 getProfileKeyFromResultSet(resultSet),
 871                 getExpiringProfileKeyCredentialFromResultSet(resultSet),
 872                 getProfileFromResultSet(resultSet));
 873     }
 874
 875     private Contact getContactFromResultSet(ResultSet resultSet) throws SQLException {
 876         return new Contact(resultSet.getString("given_name"),
 877                 resultSet.getString("family_name"),
 878                 resultSet.getString("color"),
 879                 resultSet.getInt("expiration_time"),
 880                 resultSet.getBoolean("blocked"),
 881                 resultSet.getBoolean("archived"),
 882                 resultSet.getBoolean("profile_sharing"));
 883     }
 884
 885     private Profile getProfileFromResultSet(ResultSet resultSet) throws SQLException {
 886         final var profileCapabilities = resultSet.getString("profile_capabilities");
 887         final var profileUnidentifiedAccessMode = resultSet.getString("profile_unidentified_access_mode");
 888         return new Profile(resultSet.getLong("profile_last_update_timestamp"),
 889                 resultSet.getString("profile_given_name"),
 890                 resultSet.getString("profile_family_name"),
 891                 resultSet.getString("profile_about"),
 892                 resultSet.getString("profile_about_emoji"),
 893                 resultSet.getString("profile_avatar_url_path"),
 894                 resultSet.getBytes("profile_mobile_coin_address"),
 895                 profileUnidentifiedAccessMode == null
 896                         ? Profile.UnidentifiedAccessMode.UNKNOWN
 897                         : Profile.UnidentifiedAccessMode.valueOfOrUnknown(profileUnidentifiedAccessMode),
 898                 profileCapabilities == null
 899                         ? Set.of()
 900                         : Arrays.stream(profileCapabilities.split(","))
 901                                 .map(Profile.Capability::valueOfOrNull)
 902                                 .filter(Objects::nonNull)
 903                                 .collect(Collectors.toSet()));
 904     }
 905
 906     private ProfileKey getProfileKeyFromResultSet(ResultSet resultSet) throws SQLException {
 907         final var profileKey = resultSet.getBytes("profile_key");
 908
 909         if (profileKey == null) {
 910             return null;
 911         }
 912         try {
 913             return new ProfileKey(profileKey);
 914         } catch (InvalidInputException ignored) {
 915             return null;
 916         }
 917     }
 918
 919     private ExpiringProfileKeyCredential getExpiringProfileKeyCredentialFromResultSet(ResultSet resultSet) throws SQLException {
 920         final var profileKeyCredential = resultSet.getBytes("profile_key_credential");
 921
 922         if (profileKeyCredential == null) {
 923             return null;
 924         }
 925         try {
 926             return new ExpiringProfileKeyCredential(profileKeyCredential);
 927         } catch (Throwable ignored) {
 928             return null;
 929         }
 930     }
 931
 932     public interface RecipientMergeHandler {
 933
 934         void mergeRecipients(
 935                 final Connection connection, RecipientId recipientId, RecipientId toBeMergedRecipientId
 936         ) throws SQLException;
 937     }
 938
 939     private record RecipientWithAddress(RecipientId id, RecipientAddress address) {}
 940 }