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

   1 package org.asamk.signal.manager.storage.groups;
   2
   3 import org.asamk.signal.manager.api.GroupId;
   4 import org.asamk.signal.manager.api.GroupIdV1;
   5 import org.asamk.signal.manager.api.GroupIdV2;
   6 import org.asamk.signal.manager.groups.GroupUtils;
   7 import org.asamk.signal.manager.storage.Database;
   8 import org.asamk.signal.manager.storage.Utils;
   9 import org.asamk.signal.manager.storage.recipients.RecipientId;
  10 import org.asamk.signal.manager.storage.recipients.RecipientIdCreator;
  11 import org.asamk.signal.manager.storage.recipients.RecipientResolver;
  12 import org.asamk.signal.manager.util.KeyUtils;
  13 import org.signal.libsignal.zkgroup.InvalidInputException;
  14 import org.signal.libsignal.zkgroup.groups.GroupMasterKey;
  15 import org.signal.libsignal.zkgroup.groups.GroupSecretParams;
  16 import org.signal.storageservice.protos.groups.local.DecryptedGroup;
  17 import org.slf4j.Logger;
  18 import org.slf4j.LoggerFactory;
  19 import org.whispersystems.signalservice.api.push.DistributionId;
  20 import org.whispersystems.signalservice.api.storage.StorageId;
  21 import org.whispersystems.signalservice.api.util.UuidUtil;
  22
  23 import java.io.IOException;
  24 import java.sql.Connection;
  25 import java.sql.ResultSet;
  26 import java.sql.SQLException;
  27 import java.sql.Types;
  28 import java.util.ArrayList;
  29 import java.util.Arrays;
  30 import java.util.Collection;
  31 import java.util.List;
  32 import java.util.Map;
  33 import java.util.Objects;
  34 import java.util.Set;
  35 import java.util.stream.Collectors;
  36 import java.util.stream.Stream;
  37
  38 public class GroupStore {
  39
  40     private static final Logger logger = LoggerFactory.getLogger(GroupStore.class);
  41     private static final String TABLE_GROUP_V2 = "group_v2";
  42     private static final String TABLE_GROUP_V1 = "group_v1";
  43     private static final String TABLE_GROUP_V1_MEMBER = "group_v1_member";
  44
  45     private final Database database;
  46     private final RecipientResolver recipientResolver;
  47     private final RecipientIdCreator recipientIdCreator;
  48
  49     public static void createSql(Connection connection) throws SQLException {
  50         // When modifying the CREATE statement here, also add a migration in AccountDatabase.java
  51         try (final var statement = connection.createStatement()) {
  52             statement.executeUpdate("""
  53                                     CREATE TABLE group_v2 (
  54                                       _id INTEGER PRIMARY KEY,
  55                                       storage_id BLOB UNIQUE,
  56                                       storage_record BLOB,
  57                                       group_id BLOB UNIQUE NOT NULL,
  58                                       master_key BLOB NOT NULL,
  59                                       group_data BLOB,
  60                                       distribution_id BLOB UNIQUE NOT NULL,
  61                                       blocked INTEGER NOT NULL DEFAULT FALSE,
  62                                       profile_sharing INTEGER NOT NULL DEFAULT FALSE,
  63                                       permission_denied INTEGER NOT NULL DEFAULT FALSE
  64                                     ) STRICT;
  65                                     CREATE TABLE group_v1 (
  66                                       _id INTEGER PRIMARY KEY,
  67                                       storage_id BLOB UNIQUE,
  68                                       storage_record BLOB,
  69                                       group_id BLOB UNIQUE NOT NULL,
  70                                       group_id_v2 BLOB UNIQUE,
  71                                       name TEXT,
  72                                       color TEXT,
  73                                       expiration_time INTEGER NOT NULL DEFAULT 0,
  74                                       blocked INTEGER NOT NULL DEFAULT FALSE,
  75                                       archived INTEGER NOT NULL DEFAULT FALSE
  76                                     ) STRICT;
  77                                     CREATE TABLE group_v1_member (
  78                                       _id INTEGER PRIMARY KEY,
  79                                       group_id INTEGER NOT NULL REFERENCES group_v1 (_id) ON DELETE CASCADE,
  80                                       recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
  81                                       UNIQUE(group_id, recipient_id)
  82                                     ) STRICT;
  83                                     """);
  84         }
  85     }
  86
  87     public GroupStore(
  88             final Database database,
  89             final RecipientResolver recipientResolver,
  90             final RecipientIdCreator recipientIdCreator
  91     ) {
  92         this.database = database;
  93         this.recipientResolver = recipientResolver;
  94         this.recipientIdCreator = recipientIdCreator;
  95     }
  96
  97     public void updateGroup(GroupInfo group) {
  98         try (final var connection = database.getConnection()) {
  99             connection.setAutoCommit(false);
 100             updateGroup(connection, group);
 101             connection.commit();
 102         } catch (SQLException e) {
 103             throw new RuntimeException("Failed update recipient store", e);
 104         }
 105     }
 106
 107     public void updateGroup(final Connection connection, final GroupInfo group) throws SQLException {
 108         final Long internalId;
 109         final var sql = (
 110                 """
 111                 SELECT g._id
 112                 FROM %s g
 113                 WHERE g.group_id = ?
 114                 """
 115         ).formatted(group instanceof GroupInfoV1 ? TABLE_GROUP_V1 : TABLE_GROUP_V2);
 116         try (final var statement = connection.prepareStatement(sql)) {
 117             statement.setBytes(1, group.getGroupId().serialize());
 118             internalId = Utils.executeQueryForOptional(statement, res -> res.getLong("_id")).orElse(null);
 119         }
 120         insertOrReplaceGroup(connection, internalId, group);
 121     }
 122
 123     public void storeStorageRecord(
 124             final Connection connection, final GroupId groupId, final StorageId storageId, final byte[] storageRecord
 125     ) throws SQLException {
 126         final var groupTable = groupId instanceof GroupIdV1 ? TABLE_GROUP_V1 : TABLE_GROUP_V2;
 127         final var deleteSql = (
 128                 """
 129                 UPDATE %s
 130                 SET storage_id = NULL
 131                 WHERE storage_id = ?
 132                 """
 133         ).formatted(groupTable);
 134         try (final var statement = connection.prepareStatement(deleteSql)) {
 135             statement.setBytes(1, storageId.getRaw());
 136             statement.executeUpdate();
 137         }
 138         final var sql = (
 139                 """
 140                 UPDATE %s
 141                 SET storage_id = ?, storage_record = ?
 142                 WHERE group_id = ?
 143                 """
 144         ).formatted(groupTable);
 145         try (final var statement = connection.prepareStatement(sql)) {
 146             statement.setBytes(1, storageId.getRaw());
 147             if (storageRecord == null) {
 148                 statement.setNull(2, Types.BLOB);
 149             } else {
 150                 statement.setBytes(2, storageRecord);
 151             }
 152             statement.setBytes(3, groupId.serialize());
 153             statement.executeUpdate();
 154         }
 155     }
 156
 157     public void deleteGroup(GroupId groupId) {
 158         if (groupId instanceof GroupIdV1 groupIdV1) {
 159             deleteGroup(groupIdV1);
 160         } else if (groupId instanceof GroupIdV2 groupIdV2) {
 161             deleteGroup(groupIdV2);
 162         }
 163     }
 164
 165     public void deleteGroup(GroupIdV1 groupIdV1) {
 166         try (final var connection = database.getConnection()) {
 167             deleteGroup(connection, groupIdV1);
 168         } catch (SQLException e) {
 169             throw new RuntimeException("Failed update group store", e);
 170         }
 171     }
 172
 173     private void deleteGroup(final Connection connection, final GroupIdV1 groupIdV1) throws SQLException {
 174         final var sql = (
 175                 """
 176                 DELETE FROM %s
 177                 WHERE group_id = ?
 178                 """
 179         ).formatted(TABLE_GROUP_V1);
 180         try (final var statement = connection.prepareStatement(sql)) {
 181             statement.setBytes(1, groupIdV1.serialize());
 182             statement.executeUpdate();
 183         }
 184     }
 185
 186     public void deleteGroup(GroupIdV2 groupIdV2) {
 187         try (final var connection = database.getConnection()) {
 188             final var sql = (
 189                     """
 190                     DELETE FROM %s
 191                     WHERE group_id = ?
 192                     """
 193             ).formatted(TABLE_GROUP_V2);
 194             try (final var statement = connection.prepareStatement(sql)) {
 195                 statement.setBytes(1, groupIdV2.serialize());
 196                 statement.executeUpdate();
 197             }
 198         } catch (SQLException e) {
 199             throw new RuntimeException("Failed update group store", e);
 200         }
 201     }
 202
 203     public GroupInfo getGroup(GroupId groupId) {
 204         try (final var connection = database.getConnection()) {
 205             return getGroup(connection, groupId);
 206         } catch (SQLException e) {
 207             throw new RuntimeException("Failed read from group store", e);
 208         }
 209     }
 210
 211     public GroupInfo getGroup(final Connection connection, final GroupId groupId) throws SQLException {
 212         switch (groupId) {
 213             case GroupIdV1 groupIdV1 -> {
 214                 final var group = getGroup(connection, groupIdV1);
 215                 if (group != null) {
 216                     return group;
 217                 }
 218                 return getGroupV2ByV1Id(connection, groupIdV1);
 219             }
 220             case GroupIdV2 groupIdV2 -> {
 221                 final var group = getGroup(connection, groupIdV2);
 222                 if (group != null) {
 223                     return group;
 224                 }
 225                 return getGroupV1ByV2Id(connection, groupIdV2);
 226             }
 227         }
 228     }
 229
 230     public GroupInfoV1 getOrCreateGroupV1(GroupIdV1 groupId) {
 231         try (final var connection = database.getConnection()) {
 232             return getOrCreateGroupV1(connection, groupId);
 233         } catch (SQLException e) {
 234             throw new RuntimeException("Failed read from group store", e);
 235         }
 236     }
 237
 238     public GroupInfoV1 getOrCreateGroupV1(final Connection connection, final GroupIdV1 groupId) throws SQLException {
 239         var group = getGroup(connection, groupId);
 240
 241         if (group != null) {
 242             return group;
 243         }
 244
 245         if (getGroupV2ByV1Id(connection, groupId) == null) {
 246             return new GroupInfoV1(groupId);
 247         }
 248
 249         return null;
 250     }
 251
 252     public GroupInfoV2 getGroupOrPartialMigrate(
 253             Connection connection, final GroupMasterKey groupMasterKey
 254     ) throws SQLException {
 255         final var groupSecretParams = GroupSecretParams.deriveFromMasterKey(groupMasterKey);
 256         final var groupId = GroupUtils.getGroupIdV2(groupSecretParams);
 257
 258         return getGroupOrPartialMigrate(connection, groupMasterKey, groupId);
 259     }
 260
 261     public GroupInfoV2 getGroupOrPartialMigrate(
 262             final GroupMasterKey groupMasterKey, final GroupIdV2 groupId
 263     ) {
 264         try (final var connection = database.getConnection()) {
 265             return getGroupOrPartialMigrate(connection, groupMasterKey, groupId);
 266         } catch (SQLException e) {
 267             throw new RuntimeException("Failed read from group store", e);
 268         }
 269     }
 270
 271     private GroupInfoV2 getGroupOrPartialMigrate(
 272             Connection connection, final GroupMasterKey groupMasterKey, final GroupIdV2 groupId
 273     ) throws SQLException {
 274         switch (getGroup(groupId)) {
 275             case GroupInfoV1 groupInfoV1 -> {
 276                 // Received a v2 group message for a v1 group, we need to locally migrate the group
 277                 deleteGroup(connection, groupInfoV1.getGroupId());
 278                 final var groupInfoV2 = new GroupInfoV2(groupId, groupMasterKey, recipientResolver);
 279                 groupInfoV2.setBlocked(groupInfoV1.isBlocked());
 280                 updateGroup(connection, groupInfoV2);
 281                 logger.debug("Locally migrated group {} to group v2, id: {}",
 282                         groupInfoV1.getGroupId().toBase64(),
 283                         groupInfoV2.getGroupId().toBase64());
 284                 return groupInfoV2;
 285             }
 286             case GroupInfoV2 groupInfoV2 -> {
 287                 return groupInfoV2;
 288             }
 289             case null -> {
 290                 return new GroupInfoV2(groupId, groupMasterKey, recipientResolver);
 291             }
 292         }
 293     }
 294
 295     public List<GroupInfo> getGroups() {
 296         return Stream.concat(getGroupsV2().stream(), getGroupsV1().stream()).toList();
 297     }
 298
 299     public List<GroupIdV1> getGroupV1Ids(Connection connection) throws SQLException {
 300         final var sql = (
 301                 """
 302                 SELECT g.group_id
 303                 FROM %s g
 304                 """
 305         ).formatted(TABLE_GROUP_V1);
 306         try (final var statement = connection.prepareStatement(sql)) {
 307             return Utils.executeQueryForStream(statement, this::getGroupIdV1FromResultSet)
 308                     .filter(Objects::nonNull)
 309                     .toList();
 310         }
 311     }
 312
 313     public List<GroupIdV2> getGroupV2Ids(Connection connection) throws SQLException {
 314         final var sql = (
 315                 """
 316                 SELECT g.group_id
 317                 FROM %s g
 318                 """
 319         ).formatted(TABLE_GROUP_V2);
 320         try (final var statement = connection.prepareStatement(sql)) {
 321             return Utils.executeQueryForStream(statement, this::getGroupIdV2FromResultSet)
 322                     .filter(Objects::nonNull)
 323                     .toList();
 324         }
 325     }
 326
 327     public void mergeRecipients(
 328             final Connection connection, final RecipientId recipientId, final RecipientId toBeMergedRecipientId
 329     ) throws SQLException {
 330         final var sql = (
 331                 """
 332                 UPDATE OR REPLACE %s
 333                 SET recipient_id = ?
 334                 WHERE recipient_id = ?
 335                 """
 336         ).formatted(TABLE_GROUP_V1_MEMBER);
 337         try (final var statement = connection.prepareStatement(sql)) {
 338             statement.setLong(1, recipientId.id());
 339             statement.setLong(2, toBeMergedRecipientId.id());
 340             final var updatedRows = statement.executeUpdate();
 341             if (updatedRows > 0) {
 342                 logger.debug("Updated {} group members when merging recipients", updatedRows);
 343             }
 344         }
 345     }
 346
 347     public List<StorageId> getStorageIds(Connection connection) throws SQLException {
 348         final var storageIds = new ArrayList<StorageId>();
 349         final var sql = """
 350                         SELECT g.storage_id
 351                         FROM %s g WHERE g.storage_id IS NOT NULL
 352                         """;
 353         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V1))) {
 354             Utils.executeQueryForStream(statement, this::getGroupV1StorageIdFromResultSet).forEach(storageIds::add);
 355         }
 356         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V2))) {
 357             Utils.executeQueryForStream(statement, this::getGroupV2StorageIdFromResultSet).forEach(storageIds::add);
 358         }
 359         return storageIds;
 360     }
 361
 362     public void updateStorageIds(
 363             Connection connection, Map<GroupIdV1, StorageId> storageIdV1Map, Map<GroupIdV2, StorageId> storageIdV2Map
 364     ) throws SQLException {
 365         final var sql = (
 366                 """
 367                 UPDATE %s
 368                 SET storage_id = ?
 369                 WHERE group_id = ?
 370                 """
 371         );
 372         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V1))) {
 373             for (final var entry : storageIdV1Map.entrySet()) {
 374                 statement.setBytes(1, entry.getValue().getRaw());
 375                 statement.setBytes(2, entry.getKey().serialize());
 376                 statement.executeUpdate();
 377             }
 378         }
 379         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V2))) {
 380             for (final var entry : storageIdV2Map.entrySet()) {
 381                 statement.setBytes(1, entry.getValue().getRaw());
 382                 statement.setBytes(2, entry.getKey().serialize());
 383                 statement.executeUpdate();
 384             }
 385         }
 386     }
 387
 388     public void updateStorageId(
 389             Connection connection, GroupId groupId, StorageId storageId
 390     ) throws SQLException {
 391         final var sqlV1 = (
 392                 """
 393                 UPDATE %s
 394                 SET storage_id = ?
 395                 WHERE group_id = ?
 396                 """
 397         ).formatted(groupId instanceof GroupIdV1 ? TABLE_GROUP_V1 : TABLE_GROUP_V2);
 398         try (final var statement = connection.prepareStatement(sqlV1)) {
 399             statement.setBytes(1, storageId.getRaw());
 400             statement.setBytes(2, groupId.serialize());
 401             statement.executeUpdate();
 402         }
 403     }
 404
 405     public void setMissingStorageIds() {
 406         final var selectSql = (
 407                 """
 408                 SELECT g.group_id
 409                 FROM %s g
 410                 WHERE g.storage_id IS NULL
 411                 """
 412         );
 413         final var updateSql = (
 414                 """
 415                 UPDATE %s
 416                 SET storage_id = ?
 417                 WHERE group_id = ?
 418                 """
 419         );
 420         try (final var connection = database.getConnection()) {
 421             connection.setAutoCommit(false);
 422             try (final var selectStmt = connection.prepareStatement(selectSql.formatted(TABLE_GROUP_V1))) {
 423                 final var groupIds = Utils.executeQueryForStream(selectStmt, this::getGroupIdV1FromResultSet).toList();
 424                 try (final var updateStmt = connection.prepareStatement(updateSql.formatted(TABLE_GROUP_V1))) {
 425                     for (final var groupId : groupIds) {
 426                         updateStmt.setBytes(1, KeyUtils.createRawStorageId());
 427                         updateStmt.setBytes(2, groupId.serialize());
 428                     }
 429                 }
 430             }
 431             try (final var selectStmt = connection.prepareStatement(selectSql.formatted(TABLE_GROUP_V2))) {
 432                 final var groupIds = Utils.executeQueryForStream(selectStmt, this::getGroupIdV2FromResultSet).toList();
 433                 try (final var updateStmt = connection.prepareStatement(updateSql.formatted(TABLE_GROUP_V2))) {
 434                     for (final var groupId : groupIds) {
 435                         updateStmt.setBytes(1, KeyUtils.createRawStorageId());
 436                         updateStmt.setBytes(2, groupId.serialize());
 437                         updateStmt.executeUpdate();
 438                     }
 439                 }
 440             }
 441             connection.commit();
 442         } catch (SQLException e) {
 443             throw new RuntimeException("Failed update group store", e);
 444         }
 445     }
 446
 447     void addLegacyGroups(final Collection<GroupInfo> groups) {
 448         logger.debug("Migrating legacy groups to database");
 449         long start = System.nanoTime();
 450         try (final var connection = database.getConnection()) {
 451             connection.setAutoCommit(false);
 452             for (final var group : groups) {
 453                 insertOrReplaceGroup(connection, null, group);
 454             }
 455             connection.commit();
 456         } catch (SQLException e) {
 457             throw new RuntimeException("Failed update group store", e);
 458         }
 459         logger.debug("Complete groups migration took {}ms", (System.nanoTime() - start) / 1000000);
 460     }
 461
 462     private void insertOrReplaceGroup(
 463             final Connection connection, Long internalId, final GroupInfo group
 464     ) throws SQLException {
 465         if (group instanceof GroupInfoV1 groupV1) {
 466             if (internalId != null) {
 467                 final var sqlDeleteMembers = "DELETE FROM %s where group_id = ?".formatted(TABLE_GROUP_V1_MEMBER);
 468                 try (final var statement = connection.prepareStatement(sqlDeleteMembers)) {
 469                     statement.setLong(1, internalId);
 470                     statement.executeUpdate();
 471                 }
 472             }
 473             final var sql = """
 474                             INSERT OR REPLACE INTO %s (_id, group_id, group_id_v2, name, color, expiration_time, blocked, archived, storage_id)
 475                             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
 476                             RETURNING _id
 477                             """.formatted(TABLE_GROUP_V1);
 478             try (final var statement = connection.prepareStatement(sql)) {
 479                 if (internalId == null) {
 480                     statement.setNull(1, Types.NUMERIC);
 481                 } else {
 482                     statement.setLong(1, internalId);
 483                 }
 484                 statement.setBytes(2, groupV1.getGroupId().serialize());
 485                 statement.setBytes(3, groupV1.getExpectedV2Id().serialize());
 486                 statement.setString(4, groupV1.getTitle());
 487                 statement.setString(5, groupV1.color);
 488                 statement.setLong(6, groupV1.getMessageExpirationTimer());
 489                 statement.setBoolean(7, groupV1.isBlocked());
 490                 statement.setBoolean(8, groupV1.archived);
 491                 statement.setBytes(9, KeyUtils.createRawStorageId());
 492                 final var generatedKey = Utils.executeQueryForOptional(statement, Utils::getIdMapper);
 493
 494                 if (internalId == null) {
 495                     if (generatedKey.isPresent()) {
 496                         internalId = generatedKey.get();
 497                     } else {
 498                         throw new RuntimeException("Failed to add new group to database");
 499                     }
 500                 }
 501             }
 502             final var sqlInsertMember = """
 503                                         INSERT OR REPLACE INTO %s (group_id, recipient_id)
 504                                         VALUES (?, ?)
 505                                         """.formatted(TABLE_GROUP_V1_MEMBER);
 506             try (final var statement = connection.prepareStatement(sqlInsertMember)) {
 507                 for (final var recipient : groupV1.getMembers()) {
 508                     statement.setLong(1, internalId);
 509                     statement.setLong(2, recipient.id());
 510                     statement.executeUpdate();
 511                 }
 512             }
 513         } else if (group instanceof GroupInfoV2 groupV2) {
 514             final var sql = (
 515                     """
 516                     INSERT OR REPLACE INTO %s (_id, group_id, master_key, group_data, distribution_id, blocked, permission_denied, storage_id, profile_sharing)
 517                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
 518                     """
 519             ).formatted(TABLE_GROUP_V2);
 520             try (final var statement = connection.prepareStatement(sql)) {
 521                 if (internalId == null) {
 522                     statement.setNull(1, Types.NUMERIC);
 523                 } else {
 524                     statement.setLong(1, internalId);
 525                 }
 526                 statement.setBytes(2, groupV2.getGroupId().serialize());
 527                 statement.setBytes(3, groupV2.getMasterKey().serialize());
 528                 if (groupV2.getGroup() == null) {
 529                     statement.setNull(4, Types.NUMERIC);
 530                 } else {
 531                     statement.setBytes(4, groupV2.getGroup().encode());
 532                 }
 533                 statement.setBytes(5, UuidUtil.toByteArray(groupV2.getDistributionId().asUuid()));
 534                 statement.setBoolean(6, groupV2.isBlocked());
 535                 statement.setBoolean(7, groupV2.isPermissionDenied());
 536                 statement.setBytes(8, KeyUtils.createRawStorageId());
 537                 statement.setBoolean(9, groupV2.isProfileSharingEnabled());
 538                 statement.executeUpdate();
 539             }
 540         } else {
 541             throw new AssertionError("Invalid group id type");
 542         }
 543     }
 544
 545     private List<GroupInfoV2> getGroupsV2() {
 546         final var sql = (
 547                 """
 548                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 549                 FROM %s g
 550                 """
 551         ).formatted(TABLE_GROUP_V2);
 552         try (final var connection = database.getConnection()) {
 553             try (final var statement = connection.prepareStatement(sql)) {
 554                 return Utils.executeQueryForStream(statement, this::getGroupInfoV2FromResultSet)
 555                         .filter(Objects::nonNull)
 556                         .toList();
 557             }
 558         } catch (SQLException e) {
 559             throw new RuntimeException("Failed read from group store", e);
 560         }
 561     }
 562
 563     public GroupInfoV2 getGroup(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 564         final var sql = (
 565                 """
 566                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 567                 FROM %s g
 568                 WHERE g.group_id = ?
 569                 """
 570         ).formatted(TABLE_GROUP_V2);
 571         try (final var statement = connection.prepareStatement(sql)) {
 572             statement.setBytes(1, groupIdV2.serialize());
 573             return Utils.executeQueryForOptional(statement, this::getGroupInfoV2FromResultSet).orElse(null);
 574         }
 575     }
 576
 577     public StorageId getGroupStorageId(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 578         final var sql = (
 579                 """
 580                 SELECT g.storage_id
 581                 FROM %s g
 582                 WHERE g.group_id = ?
 583                 """
 584         ).formatted(TABLE_GROUP_V2);
 585         try (final var statement = connection.prepareStatement(sql)) {
 586             statement.setBytes(1, groupIdV2.serialize());
 587             final var storageId = Utils.executeQueryForOptional(statement, this::getGroupV2StorageIdFromResultSet);
 588             if (storageId.isPresent()) {
 589                 return storageId.get();
 590             }
 591         }
 592         final var newStorageId = StorageId.forGroupV2(KeyUtils.createRawStorageId());
 593         updateStorageId(connection, groupIdV2, newStorageId);
 594         return newStorageId;
 595     }
 596
 597     public GroupInfoV2 getGroupV2(Connection connection, StorageId storageId) throws SQLException {
 598         final var sql = (
 599                 """
 600                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 601                 FROM %s g
 602                 WHERE g.storage_id = ?
 603                 """
 604         ).formatted(TABLE_GROUP_V2);
 605         try (final var statement = connection.prepareStatement(sql)) {
 606             statement.setBytes(1, storageId.getRaw());
 607             return Utils.executeQueryForOptional(statement, this::getGroupInfoV2FromResultSet).orElse(null);
 608         }
 609     }
 610
 611     private GroupIdV2 getGroupIdV2FromResultSet(ResultSet resultSet) throws SQLException {
 612         final var groupId = resultSet.getBytes("group_id");
 613         return GroupId.v2(groupId);
 614     }
 615
 616     private GroupInfoV2 getGroupInfoV2FromResultSet(ResultSet resultSet) throws SQLException {
 617         try {
 618             final var groupId = resultSet.getBytes("group_id");
 619             final var masterKey = resultSet.getBytes("master_key");
 620             final var groupData = resultSet.getBytes("group_data");
 621             final var distributionId = resultSet.getBytes("distribution_id");
 622             final var blocked = resultSet.getBoolean("blocked");
 623             final var profileSharingEnabled = resultSet.getBoolean("profile_sharing");
 624             final var permissionDenied = resultSet.getBoolean("permission_denied");
 625             final var storageRecord = resultSet.getBytes("storage_record");
 626             return new GroupInfoV2(GroupId.v2(groupId),
 627                     new GroupMasterKey(masterKey),
 628                     groupData == null ? null : DecryptedGroup.ADAPTER.decode(groupData),
 629                     DistributionId.from(UuidUtil.parseOrThrow(distributionId)),
 630                     blocked,
 631                     profileSharingEnabled,
 632                     permissionDenied,
 633                     storageRecord,
 634                     recipientResolver);
 635         } catch (InvalidInputException | IOException e) {
 636             return null;
 637         }
 638     }
 639
 640     private StorageId getGroupV1StorageIdFromResultSet(ResultSet resultSet) throws SQLException {
 641         final var storageId = resultSet.getBytes("storage_id");
 642         return storageId == null
 643                 ? StorageId.forGroupV1(KeyUtils.createRawStorageId())
 644                 : StorageId.forGroupV1(storageId);
 645     }
 646
 647     private StorageId getGroupV2StorageIdFromResultSet(ResultSet resultSet) throws SQLException {
 648         final var storageId = resultSet.getBytes("storage_id");
 649         return storageId == null
 650                 ? StorageId.forGroupV2(KeyUtils.createRawStorageId())
 651                 : StorageId.forGroupV2(storageId);
 652     }
 653
 654     private List<GroupInfoV1> getGroupsV1() {
 655         final var sql = (
 656                 """
 657                 SELECT g.group_id, g.group_id_v2, g.name, g.color, (select group_concat(gm.recipient_id) from %s gm where gm.group_id = g._id) as members, g.expiration_time, g.blocked, g.archived, g.storage_record
 658                 FROM %s g
 659                 """
 660         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 661         try (final var connection = database.getConnection()) {
 662             try (final var statement = connection.prepareStatement(sql)) {
 663                 return Utils.executeQueryForStream(statement, this::getGroupInfoV1FromResultSet)
 664                         .filter(Objects::nonNull)
 665                         .toList();
 666             }
 667         } catch (SQLException e) {
 668             throw new RuntimeException("Failed read from group store", e);
 669         }
 670     }
 671
 672     public GroupInfoV1 getGroup(Connection connection, GroupIdV1 groupIdV1) throws SQLException {
 673         final var sql = (
 674                 """
 675                 SELECT g.group_id, g.group_id_v2, g.name, g.color, (select group_concat(gm.recipient_id) from %s gm where gm.group_id = g._id) as members, g.expiration_time, g.blocked, g.archived, g.storage_record
 676                 FROM %s g
 677                 WHERE g.group_id = ?
 678                 """
 679         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 680         try (final var statement = connection.prepareStatement(sql)) {
 681             statement.setBytes(1, groupIdV1.serialize());
 682             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 683         }
 684     }
 685
 686     public StorageId getGroupStorageId(Connection connection, GroupIdV1 groupIdV1) throws SQLException {
 687         final var sql = (
 688                 """
 689                 SELECT g.storage_id
 690                 FROM %s g
 691                 WHERE g.group_id = ?
 692                 """
 693         ).formatted(TABLE_GROUP_V1);
 694         try (final var statement = connection.prepareStatement(sql)) {
 695             statement.setBytes(1, groupIdV1.serialize());
 696             final var storageId = Utils.executeQueryForOptional(statement, this::getGroupV1StorageIdFromResultSet);
 697             if (storageId.isPresent()) {
 698                 return storageId.get();
 699             }
 700         }
 701         final var newStorageId = StorageId.forGroupV1(KeyUtils.createRawStorageId());
 702         updateStorageId(connection, groupIdV1, newStorageId);
 703         return newStorageId;
 704     }
 705
 706     public GroupInfoV1 getGroupV1(Connection connection, StorageId storageId) throws SQLException {
 707         final var sql = (
 708                 """
 709                 SELECT g.group_id, g.group_id_v2, g.name, g.color, (select group_concat(gm.recipient_id) from %s gm where gm.group_id = g._id) as members, g.expiration_time, g.blocked, g.archived, g.storage_record
 710                 FROM %s g
 711                 WHERE g.storage_id = ?
 712                 """
 713         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 714         try (final var statement = connection.prepareStatement(sql)) {
 715             statement.setBytes(1, storageId.getRaw());
 716             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 717         }
 718     }
 719
 720     private GroupIdV1 getGroupIdV1FromResultSet(ResultSet resultSet) throws SQLException {
 721         final var groupId = resultSet.getBytes("group_id");
 722         return GroupId.v1(groupId);
 723     }
 724
 725     private GroupInfoV1 getGroupInfoV1FromResultSet(ResultSet resultSet) throws SQLException {
 726         final var groupId = resultSet.getBytes("group_id");
 727         final var groupIdV2 = resultSet.getBytes("group_id_v2");
 728         final var name = resultSet.getString("name");
 729         final var color = resultSet.getString("color");
 730         final var membersString = resultSet.getString("members");
 731         final var members = membersString == null
 732                 ? Set.<RecipientId>of()
 733                 : Arrays.stream(membersString.split(","))
 734                         .map(Integer::valueOf)
 735                         .map(recipientIdCreator::create)
 736                         .collect(Collectors.toSet());
 737         final var expirationTime = resultSet.getInt("expiration_time");
 738         final var blocked = resultSet.getBoolean("blocked");
 739         final var archived = resultSet.getBoolean("archived");
 740         final var storageRecord = resultSet.getBytes("storage_record");
 741         return new GroupInfoV1(GroupId.v1(groupId),
 742                 groupIdV2 == null ? null : GroupId.v2(groupIdV2),
 743                 name,
 744                 members,
 745                 color,
 746                 expirationTime,
 747                 blocked,
 748                 archived,
 749                 storageRecord);
 750     }
 751
 752     private GroupInfoV2 getGroupV2ByV1Id(final Connection connection, final GroupIdV1 groupId) throws SQLException {
 753         return getGroup(connection, GroupUtils.getGroupIdV2(groupId));
 754     }
 755
 756     private GroupInfoV1 getGroupV1ByV2Id(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 757         final var sql = (
 758                 """
 759                 SELECT g.group_id, g.group_id_v2, g.name, g.color, (select group_concat(gm.recipient_id) from %s gm where gm.group_id = g._id) as members, g.expiration_time, g.blocked, g.archived, g.storage_record
 760                 FROM %s g
 761                 WHERE g.group_id_v2 = ?
 762                 """
 763         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 764         try (final var statement = connection.prepareStatement(sql)) {
 765             statement.setBytes(1, groupIdV2.serialize());
 766             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 767         }
 768     }
 769 }