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