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