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             var group = getGroup(connection, groupId);
 233
 234             if (group != null) {
 235                 return group;
 236             }
 237
 238             if (getGroupV2ByV1Id(connection, groupId) == null) {
 239                 return new GroupInfoV1(groupId);
 240             }
 241
 242             return null;
 243         } catch (SQLException e) {
 244             throw new RuntimeException("Failed read from group store", e);
 245         }
 246     }
 247
 248     public GroupInfoV2 getGroupOrPartialMigrate(
 249             Connection connection, final GroupMasterKey groupMasterKey
 250     ) throws SQLException {
 251         final var groupSecretParams = GroupSecretParams.deriveFromMasterKey(groupMasterKey);
 252         final var groupId = GroupUtils.getGroupIdV2(groupSecretParams);
 253
 254         return getGroupOrPartialMigrate(connection, groupMasterKey, groupId);
 255     }
 256
 257     public GroupInfoV2 getGroupOrPartialMigrate(
 258             final GroupMasterKey groupMasterKey, final GroupIdV2 groupId
 259     ) {
 260         try (final var connection = database.getConnection()) {
 261             return getGroupOrPartialMigrate(connection, groupMasterKey, groupId);
 262         } catch (SQLException e) {
 263             throw new RuntimeException("Failed read from group store", e);
 264         }
 265     }
 266
 267     private GroupInfoV2 getGroupOrPartialMigrate(
 268             Connection connection, final GroupMasterKey groupMasterKey, final GroupIdV2 groupId
 269     ) throws SQLException {
 270         switch (getGroup(groupId)) {
 271             case GroupInfoV1 groupInfoV1 -> {
 272                 // Received a v2 group message for a v1 group, we need to locally migrate the group
 273                 deleteGroup(connection, groupInfoV1.getGroupId());
 274                 final var groupInfoV2 = new GroupInfoV2(groupId, groupMasterKey, recipientResolver);
 275                 groupInfoV2.setBlocked(groupInfoV1.isBlocked());
 276                 updateGroup(connection, groupInfoV2);
 277                 logger.debug("Locally migrated group {} to group v2, id: {}",
 278                         groupInfoV1.getGroupId().toBase64(),
 279                         groupInfoV2.getGroupId().toBase64());
 280                 return groupInfoV2;
 281             }
 282             case GroupInfoV2 groupInfoV2 -> {
 283                 return groupInfoV2;
 284             }
 285             case null -> {
 286                 return new GroupInfoV2(groupId, groupMasterKey, recipientResolver);
 287             }
 288         }
 289     }
 290
 291     public List<GroupInfo> getGroups() {
 292         return Stream.concat(getGroupsV2().stream(), getGroupsV1().stream()).toList();
 293     }
 294
 295     public List<GroupIdV1> getGroupV1Ids(Connection connection) throws SQLException {
 296         final var sql = (
 297                 """
 298                 SELECT g.group_id
 299                 FROM %s g
 300                 """
 301         ).formatted(TABLE_GROUP_V1);
 302         try (final var statement = connection.prepareStatement(sql)) {
 303             return Utils.executeQueryForStream(statement, this::getGroupIdV1FromResultSet)
 304                     .filter(Objects::nonNull)
 305                     .toList();
 306         }
 307     }
 308
 309     public List<GroupIdV2> getGroupV2Ids(Connection connection) throws SQLException {
 310         final var sql = (
 311                 """
 312                 SELECT g.group_id
 313                 FROM %s g
 314                 """
 315         ).formatted(TABLE_GROUP_V2);
 316         try (final var statement = connection.prepareStatement(sql)) {
 317             return Utils.executeQueryForStream(statement, this::getGroupIdV2FromResultSet)
 318                     .filter(Objects::nonNull)
 319                     .toList();
 320         }
 321     }
 322
 323     public void mergeRecipients(
 324             final Connection connection, final RecipientId recipientId, final RecipientId toBeMergedRecipientId
 325     ) throws SQLException {
 326         final var sql = (
 327                 """
 328                 UPDATE OR REPLACE %s
 329                 SET recipient_id = ?
 330                 WHERE recipient_id = ?
 331                 """
 332         ).formatted(TABLE_GROUP_V1_MEMBER);
 333         try (final var statement = connection.prepareStatement(sql)) {
 334             statement.setLong(1, recipientId.id());
 335             statement.setLong(2, toBeMergedRecipientId.id());
 336             final var updatedRows = statement.executeUpdate();
 337             if (updatedRows > 0) {
 338                 logger.debug("Updated {} group members when merging recipients", updatedRows);
 339             }
 340         }
 341     }
 342
 343     public List<StorageId> getStorageIds(Connection connection) throws SQLException {
 344         final var storageIds = new ArrayList<StorageId>();
 345         final var sql = """
 346                         SELECT g.storage_id
 347                         FROM %s g WHERE g.storage_id IS NOT NULL
 348                         """;
 349         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V1))) {
 350             Utils.executeQueryForStream(statement, this::getGroupV1StorageIdFromResultSet).forEach(storageIds::add);
 351         }
 352         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V2))) {
 353             Utils.executeQueryForStream(statement, this::getGroupV2StorageIdFromResultSet).forEach(storageIds::add);
 354         }
 355         return storageIds;
 356     }
 357
 358     public void updateStorageIds(
 359             Connection connection, Map<GroupIdV1, StorageId> storageIdV1Map, Map<GroupIdV2, StorageId> storageIdV2Map
 360     ) throws SQLException {
 361         final var sql = (
 362                 """
 363                 UPDATE %s
 364                 SET storage_id = ?
 365                 WHERE group_id = ?
 366                 """
 367         );
 368         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V1))) {
 369             for (final var entry : storageIdV1Map.entrySet()) {
 370                 statement.setBytes(1, entry.getValue().getRaw());
 371                 statement.setBytes(2, entry.getKey().serialize());
 372                 statement.executeUpdate();
 373             }
 374         }
 375         try (final var statement = connection.prepareStatement(sql.formatted(TABLE_GROUP_V2))) {
 376             for (final var entry : storageIdV2Map.entrySet()) {
 377                 statement.setBytes(1, entry.getValue().getRaw());
 378                 statement.setBytes(2, entry.getKey().serialize());
 379                 statement.executeUpdate();
 380             }
 381         }
 382     }
 383
 384     public void updateStorageId(
 385             Connection connection, GroupId groupId, StorageId storageId
 386     ) throws SQLException {
 387         final var sqlV1 = (
 388                 """
 389                 UPDATE %s
 390                 SET storage_id = ?
 391                 WHERE group_id = ?
 392                 """
 393         ).formatted(groupId instanceof GroupIdV1 ? TABLE_GROUP_V1 : TABLE_GROUP_V2);
 394         try (final var statement = connection.prepareStatement(sqlV1)) {
 395             statement.setBytes(1, storageId.getRaw());
 396             statement.setBytes(2, groupId.serialize());
 397             statement.executeUpdate();
 398         }
 399     }
 400
 401     public void setMissingStorageIds() {
 402         final var selectSql = (
 403                 """
 404                 SELECT g.group_id
 405                 FROM %s g
 406                 WHERE g.storage_id IS NULL
 407                 """
 408         );
 409         final var updateSql = (
 410                 """
 411                 UPDATE %s
 412                 SET storage_id = ?
 413                 WHERE group_id = ?
 414                 """
 415         );
 416         try (final var connection = database.getConnection()) {
 417             connection.setAutoCommit(false);
 418             try (final var selectStmt = connection.prepareStatement(selectSql.formatted(TABLE_GROUP_V1))) {
 419                 final var groupIds = Utils.executeQueryForStream(selectStmt, this::getGroupIdV1FromResultSet).toList();
 420                 try (final var updateStmt = connection.prepareStatement(updateSql.formatted(TABLE_GROUP_V1))) {
 421                     for (final var groupId : groupIds) {
 422                         updateStmt.setBytes(1, KeyUtils.createRawStorageId());
 423                         updateStmt.setBytes(2, groupId.serialize());
 424                     }
 425                 }
 426             }
 427             try (final var selectStmt = connection.prepareStatement(selectSql.formatted(TABLE_GROUP_V2))) {
 428                 final var groupIds = Utils.executeQueryForStream(selectStmt, this::getGroupIdV2FromResultSet).toList();
 429                 try (final var updateStmt = connection.prepareStatement(updateSql.formatted(TABLE_GROUP_V2))) {
 430                     for (final var groupId : groupIds) {
 431                         updateStmt.setBytes(1, KeyUtils.createRawStorageId());
 432                         updateStmt.setBytes(2, groupId.serialize());
 433                         updateStmt.executeUpdate();
 434                     }
 435                 }
 436             }
 437             connection.commit();
 438         } catch (SQLException e) {
 439             throw new RuntimeException("Failed update group store", e);
 440         }
 441     }
 442
 443     void addLegacyGroups(final Collection<GroupInfo> groups) {
 444         logger.debug("Migrating legacy groups to database");
 445         long start = System.nanoTime();
 446         try (final var connection = database.getConnection()) {
 447             connection.setAutoCommit(false);
 448             for (final var group : groups) {
 449                 insertOrReplaceGroup(connection, null, group);
 450             }
 451             connection.commit();
 452         } catch (SQLException e) {
 453             throw new RuntimeException("Failed update group store", e);
 454         }
 455         logger.debug("Complete groups migration took {}ms", (System.nanoTime() - start) / 1000000);
 456     }
 457
 458     private void insertOrReplaceGroup(
 459             final Connection connection, Long internalId, final GroupInfo group
 460     ) throws SQLException {
 461         if (group instanceof GroupInfoV1 groupV1) {
 462             if (internalId != null) {
 463                 final var sqlDeleteMembers = "DELETE FROM %s where group_id = ?".formatted(TABLE_GROUP_V1_MEMBER);
 464                 try (final var statement = connection.prepareStatement(sqlDeleteMembers)) {
 465                     statement.setLong(1, internalId);
 466                     statement.executeUpdate();
 467                 }
 468             }
 469             final var sql = """
 470                             INSERT OR REPLACE INTO %s (_id, group_id, group_id_v2, name, color, expiration_time, blocked, archived, storage_id)
 471                             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
 472                             RETURNING _id
 473                             """.formatted(TABLE_GROUP_V1);
 474             try (final var statement = connection.prepareStatement(sql)) {
 475                 if (internalId == null) {
 476                     statement.setNull(1, Types.NUMERIC);
 477                 } else {
 478                     statement.setLong(1, internalId);
 479                 }
 480                 statement.setBytes(2, groupV1.getGroupId().serialize());
 481                 statement.setBytes(3, groupV1.getExpectedV2Id().serialize());
 482                 statement.setString(4, groupV1.getTitle());
 483                 statement.setString(5, groupV1.color);
 484                 statement.setLong(6, groupV1.getMessageExpirationTimer());
 485                 statement.setBoolean(7, groupV1.isBlocked());
 486                 statement.setBoolean(8, groupV1.archived);
 487                 statement.setBytes(9, KeyUtils.createRawStorageId());
 488                 final var generatedKey = Utils.executeQueryForOptional(statement, Utils::getIdMapper);
 489
 490                 if (internalId == null) {
 491                     if (generatedKey.isPresent()) {
 492                         internalId = generatedKey.get();
 493                     } else {
 494                         throw new RuntimeException("Failed to add new group to database");
 495                     }
 496                 }
 497             }
 498             final var sqlInsertMember = """
 499                                         INSERT OR REPLACE INTO %s (group_id, recipient_id)
 500                                         VALUES (?, ?)
 501                                         """.formatted(TABLE_GROUP_V1_MEMBER);
 502             try (final var statement = connection.prepareStatement(sqlInsertMember)) {
 503                 for (final var recipient : groupV1.getMembers()) {
 504                     statement.setLong(1, internalId);
 505                     statement.setLong(2, recipient.id());
 506                     statement.executeUpdate();
 507                 }
 508             }
 509         } else if (group instanceof GroupInfoV2 groupV2) {
 510             final var sql = (
 511                     """
 512                     INSERT OR REPLACE INTO %s (_id, group_id, master_key, group_data, distribution_id, blocked, permission_denied, storage_id, profile_sharing)
 513                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
 514                     """
 515             ).formatted(TABLE_GROUP_V2);
 516             try (final var statement = connection.prepareStatement(sql)) {
 517                 if (internalId == null) {
 518                     statement.setNull(1, Types.NUMERIC);
 519                 } else {
 520                     statement.setLong(1, internalId);
 521                 }
 522                 statement.setBytes(2, groupV2.getGroupId().serialize());
 523                 statement.setBytes(3, groupV2.getMasterKey().serialize());
 524                 if (groupV2.getGroup() == null) {
 525                     statement.setNull(4, Types.NUMERIC);
 526                 } else {
 527                     statement.setBytes(4, groupV2.getGroup().encode());
 528                 }
 529                 statement.setBytes(5, UuidUtil.toByteArray(groupV2.getDistributionId().asUuid()));
 530                 statement.setBoolean(6, groupV2.isBlocked());
 531                 statement.setBoolean(7, groupV2.isPermissionDenied());
 532                 statement.setBytes(8, KeyUtils.createRawStorageId());
 533                 statement.setBoolean(9, groupV2.isProfileSharingEnabled());
 534                 statement.executeUpdate();
 535             }
 536         } else {
 537             throw new AssertionError("Invalid group id type");
 538         }
 539     }
 540
 541     private List<GroupInfoV2> getGroupsV2() {
 542         final var sql = (
 543                 """
 544                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 545                 FROM %s g
 546                 """
 547         ).formatted(TABLE_GROUP_V2);
 548         try (final var connection = database.getConnection()) {
 549             try (final var statement = connection.prepareStatement(sql)) {
 550                 return Utils.executeQueryForStream(statement, this::getGroupInfoV2FromResultSet)
 551                         .filter(Objects::nonNull)
 552                         .toList();
 553             }
 554         } catch (SQLException e) {
 555             throw new RuntimeException("Failed read from group store", e);
 556         }
 557     }
 558
 559     public GroupInfoV2 getGroup(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 560         final var sql = (
 561                 """
 562                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 563                 FROM %s g
 564                 WHERE g.group_id = ?
 565                 """
 566         ).formatted(TABLE_GROUP_V2);
 567         try (final var statement = connection.prepareStatement(sql)) {
 568             statement.setBytes(1, groupIdV2.serialize());
 569             return Utils.executeQueryForOptional(statement, this::getGroupInfoV2FromResultSet).orElse(null);
 570         }
 571     }
 572
 573     public StorageId getGroupStorageId(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 574         final var sql = (
 575                 """
 576                 SELECT g.storage_id
 577                 FROM %s g
 578                 WHERE g.group_id = ?
 579                 """
 580         ).formatted(TABLE_GROUP_V2);
 581         try (final var statement = connection.prepareStatement(sql)) {
 582             statement.setBytes(1, groupIdV2.serialize());
 583             final var storageId = Utils.executeQueryForOptional(statement, this::getGroupV2StorageIdFromResultSet);
 584             if (storageId.isPresent()) {
 585                 return storageId.get();
 586             }
 587         }
 588         final var newStorageId = StorageId.forGroupV2(KeyUtils.createRawStorageId());
 589         updateStorageId(connection, groupIdV2, newStorageId);
 590         return newStorageId;
 591     }
 592
 593     public GroupInfoV2 getGroupV2(Connection connection, StorageId storageId) throws SQLException {
 594         final var sql = (
 595                 """
 596                 SELECT g.group_id, g.master_key, g.group_data, g.distribution_id, g.blocked, g.profile_sharing, g.permission_denied, g.storage_record
 597                 FROM %s g
 598                 WHERE g.storage_id = ?
 599                 """
 600         ).formatted(TABLE_GROUP_V2);
 601         try (final var statement = connection.prepareStatement(sql)) {
 602             statement.setBytes(1, storageId.getRaw());
 603             return Utils.executeQueryForOptional(statement, this::getGroupInfoV2FromResultSet).orElse(null);
 604         }
 605     }
 606
 607     private GroupIdV2 getGroupIdV2FromResultSet(ResultSet resultSet) throws SQLException {
 608         final var groupId = resultSet.getBytes("group_id");
 609         return GroupId.v2(groupId);
 610     }
 611
 612     private GroupInfoV2 getGroupInfoV2FromResultSet(ResultSet resultSet) throws SQLException {
 613         try {
 614             final var groupId = resultSet.getBytes("group_id");
 615             final var masterKey = resultSet.getBytes("master_key");
 616             final var groupData = resultSet.getBytes("group_data");
 617             final var distributionId = resultSet.getBytes("distribution_id");
 618             final var blocked = resultSet.getBoolean("blocked");
 619             final var profileSharingEnabled = resultSet.getBoolean("profile_sharing");
 620             final var permissionDenied = resultSet.getBoolean("permission_denied");
 621             final var storageRecord = resultSet.getBytes("storage_record");
 622             return new GroupInfoV2(GroupId.v2(groupId),
 623                     new GroupMasterKey(masterKey),
 624                     groupData == null ? null : DecryptedGroup.ADAPTER.decode(groupData),
 625                     DistributionId.from(UuidUtil.parseOrThrow(distributionId)),
 626                     blocked,
 627                     profileSharingEnabled,
 628                     permissionDenied,
 629                     storageRecord,
 630                     recipientResolver);
 631         } catch (InvalidInputException | IOException e) {
 632             return null;
 633         }
 634     }
 635
 636     private StorageId getGroupV1StorageIdFromResultSet(ResultSet resultSet) throws SQLException {
 637         final var storageId = resultSet.getBytes("storage_id");
 638         return storageId == null
 639                 ? StorageId.forGroupV1(KeyUtils.createRawStorageId())
 640                 : StorageId.forGroupV1(storageId);
 641     }
 642
 643     private StorageId getGroupV2StorageIdFromResultSet(ResultSet resultSet) throws SQLException {
 644         final var storageId = resultSet.getBytes("storage_id");
 645         return storageId == null
 646                 ? StorageId.forGroupV2(KeyUtils.createRawStorageId())
 647                 : StorageId.forGroupV2(storageId);
 648     }
 649
 650     private List<GroupInfoV1> getGroupsV1() {
 651         final var sql = (
 652                 """
 653                 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
 654                 FROM %s g
 655                 """
 656         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 657         try (final var connection = database.getConnection()) {
 658             try (final var statement = connection.prepareStatement(sql)) {
 659                 return Utils.executeQueryForStream(statement, this::getGroupInfoV1FromResultSet)
 660                         .filter(Objects::nonNull)
 661                         .toList();
 662             }
 663         } catch (SQLException e) {
 664             throw new RuntimeException("Failed read from group store", e);
 665         }
 666     }
 667
 668     public GroupInfoV1 getGroup(Connection connection, GroupIdV1 groupIdV1) throws SQLException {
 669         final var sql = (
 670                 """
 671                 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
 672                 FROM %s g
 673                 WHERE g.group_id = ?
 674                 """
 675         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 676         try (final var statement = connection.prepareStatement(sql)) {
 677             statement.setBytes(1, groupIdV1.serialize());
 678             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 679         }
 680     }
 681
 682     public StorageId getGroupStorageId(Connection connection, GroupIdV1 groupIdV1) throws SQLException {
 683         final var sql = (
 684                 """
 685                 SELECT g.storage_id
 686                 FROM %s g
 687                 WHERE g.group_id = ?
 688                 """
 689         ).formatted(TABLE_GROUP_V1);
 690         try (final var statement = connection.prepareStatement(sql)) {
 691             statement.setBytes(1, groupIdV1.serialize());
 692             final var storageId = Utils.executeQueryForOptional(statement, this::getGroupV1StorageIdFromResultSet);
 693             if (storageId.isPresent()) {
 694                 return storageId.get();
 695             }
 696         }
 697         final var newStorageId = StorageId.forGroupV1(KeyUtils.createRawStorageId());
 698         updateStorageId(connection, groupIdV1, newStorageId);
 699         return newStorageId;
 700     }
 701
 702     public GroupInfoV1 getGroupV1(Connection connection, StorageId storageId) throws SQLException {
 703         final var sql = (
 704                 """
 705                 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
 706                 FROM %s g
 707                 WHERE g.storage_id = ?
 708                 """
 709         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 710         try (final var statement = connection.prepareStatement(sql)) {
 711             statement.setBytes(1, storageId.getRaw());
 712             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 713         }
 714     }
 715
 716     private GroupIdV1 getGroupIdV1FromResultSet(ResultSet resultSet) throws SQLException {
 717         final var groupId = resultSet.getBytes("group_id");
 718         return GroupId.v1(groupId);
 719     }
 720
 721     private GroupInfoV1 getGroupInfoV1FromResultSet(ResultSet resultSet) throws SQLException {
 722         final var groupId = resultSet.getBytes("group_id");
 723         final var groupIdV2 = resultSet.getBytes("group_id_v2");
 724         final var name = resultSet.getString("name");
 725         final var color = resultSet.getString("color");
 726         final var membersString = resultSet.getString("members");
 727         final var members = membersString == null
 728                 ? Set.<RecipientId>of()
 729                 : Arrays.stream(membersString.split(","))
 730                         .map(Integer::valueOf)
 731                         .map(recipientIdCreator::create)
 732                         .collect(Collectors.toSet());
 733         final var expirationTime = resultSet.getInt("expiration_time");
 734         final var blocked = resultSet.getBoolean("blocked");
 735         final var archived = resultSet.getBoolean("archived");
 736         final var storageRecord = resultSet.getBytes("storage_record");
 737         return new GroupInfoV1(GroupId.v1(groupId),
 738                 groupIdV2 == null ? null : GroupId.v2(groupIdV2),
 739                 name,
 740                 members,
 741                 color,
 742                 expirationTime,
 743                 blocked,
 744                 archived,
 745                 storageRecord);
 746     }
 747
 748     private GroupInfoV2 getGroupV2ByV1Id(final Connection connection, final GroupIdV1 groupId) throws SQLException {
 749         return getGroup(connection, GroupUtils.getGroupIdV2(groupId));
 750     }
 751
 752     private GroupInfoV1 getGroupV1ByV2Id(Connection connection, GroupIdV2 groupIdV2) throws SQLException {
 753         final var sql = (
 754                 """
 755                 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
 756                 FROM %s g
 757                 WHERE g.group_id_v2 = ?
 758                 """
 759         ).formatted(TABLE_GROUP_V1_MEMBER, TABLE_GROUP_V1);
 760         try (final var statement = connection.prepareStatement(sql)) {
 761             statement.setBytes(1, groupIdV2.serialize());
 762             return Utils.executeQueryForOptional(statement, this::getGroupInfoV1FromResultSet).orElse(null);
 763         }
 764     }
 765 }