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