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