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

   1 package org.asamk.signal.manager.storage;
   2
   3 import com.zaxxer.hikari.HikariDataSource;
   4
   5 import org.asamk.signal.manager.api.Pair;
   6 import org.asamk.signal.manager.storage.groups.GroupStore;
   7 import org.asamk.signal.manager.storage.identities.IdentityKeyStore;
   8 import org.asamk.signal.manager.storage.keyValue.KeyValueStore;
   9 import org.asamk.signal.manager.storage.prekeys.KyberPreKeyStore;
  10 import org.asamk.signal.manager.storage.prekeys.PreKeyStore;
  11 import org.asamk.signal.manager.storage.prekeys.SignedPreKeyStore;
  12 import org.asamk.signal.manager.storage.recipients.CdsiStore;
  13 import org.asamk.signal.manager.storage.recipients.RecipientStore;
  14 import org.asamk.signal.manager.storage.sendLog.MessageSendLogStore;
  15 import org.asamk.signal.manager.storage.senderKeys.SenderKeyRecordStore;
  16 import org.asamk.signal.manager.storage.senderKeys.SenderKeySharedStore;
  17 import org.asamk.signal.manager.storage.sessions.SessionStore;
  18 import org.asamk.signal.manager.storage.stickers.StickerStore;
  19 import org.slf4j.Logger;
  20 import org.slf4j.LoggerFactory;
  21 import org.whispersystems.signalservice.api.push.ServiceId;
  22 import org.whispersystems.signalservice.api.push.ServiceId.ACI;
  23 import org.whispersystems.signalservice.api.util.UuidUtil;
  24
  25 import java.io.File;
  26 import java.sql.Connection;
  27 import java.sql.SQLException;
  28 import java.sql.Statement;
  29 import java.util.HashMap;
  30 import java.util.Optional;
  31 import java.util.UUID;
  32
  33 public class AccountDatabase extends Database {
  34
  35     private static final Logger logger = LoggerFactory.getLogger(AccountDatabase.class);
  36     private static final long DATABASE_VERSION = 27;
  37
  38     private AccountDatabase(final HikariDataSource dataSource) {
  39         super(logger, DATABASE_VERSION, dataSource);
  40     }
  41
  42     public static AccountDatabase init(File databaseFile) throws SQLException {
  43         return initDatabase(databaseFile, AccountDatabase::new);
  44     }
  45
  46     @Override
  47     protected void createDatabase(final Connection connection) throws SQLException {
  48         RecipientStore.createSql(connection);
  49         MessageSendLogStore.createSql(connection);
  50         StickerStore.createSql(connection);
  51         PreKeyStore.createSql(connection);
  52         SignedPreKeyStore.createSql(connection);
  53         KyberPreKeyStore.createSql(connection);
  54         GroupStore.createSql(connection);
  55         SessionStore.createSql(connection);
  56         IdentityKeyStore.createSql(connection);
  57         SenderKeyRecordStore.createSql(connection);
  58         SenderKeySharedStore.createSql(connection);
  59         KeyValueStore.createSql(connection);
  60         CdsiStore.createSql(connection);
  61         UnknownStorageIdStore.createSql(connection);
  62     }
  63
  64     @Override
  65     protected void upgradeDatabase(final Connection connection, final long oldVersion) throws SQLException {
  66         if (oldVersion < 2) {
  67             logger.debug("Updating database: Creating recipient table");
  68             try (final var statement = connection.createStatement()) {
  69                 statement.executeUpdate("""
  70                                         CREATE TABLE recipient (
  71                                           _id INTEGER PRIMARY KEY AUTOINCREMENT,
  72                                           number TEXT UNIQUE,
  73                                           uuid BLOB UNIQUE,
  74                                           profile_key BLOB,
  75                                           profile_key_credential BLOB,
  76
  77                                           given_name TEXT,
  78                                           family_name TEXT,
  79                                           color TEXT,
  80
  81                                           expiration_time INTEGER NOT NULL DEFAULT 0,
  82                                           blocked INTEGER NOT NULL DEFAULT FALSE,
  83                                           archived INTEGER NOT NULL DEFAULT FALSE,
  84                                           profile_sharing INTEGER NOT NULL DEFAULT FALSE,
  85
  86                                           profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
  87                                           profile_given_name TEXT,
  88                                           profile_family_name TEXT,
  89                                           profile_about TEXT,
  90                                           profile_about_emoji TEXT,
  91                                           profile_avatar_url_path TEXT,
  92                                           profile_mobile_coin_address BLOB,
  93                                           profile_unidentified_access_mode TEXT,
  94                                           profile_capabilities TEXT
  95                                         ) STRICT;
  96                                         """);
  97             }
  98         }
  99         if (oldVersion < 3) {
 100             logger.debug("Updating database: Creating sticker table");
 101             try (final var statement = connection.createStatement()) {
 102                 statement.executeUpdate("""
 103                                         CREATE TABLE sticker (
 104                                           _id INTEGER PRIMARY KEY,
 105                                           pack_id BLOB UNIQUE NOT NULL,
 106                                           pack_key BLOB NOT NULL,
 107                                           installed INTEGER NOT NULL DEFAULT FALSE
 108                                         ) STRICT;
 109                                         """);
 110             }
 111         }
 112         if (oldVersion < 4) {
 113             logger.debug("Updating database: Creating pre key tables");
 114             try (final var statement = connection.createStatement()) {
 115                 statement.executeUpdate("""
 116                                         CREATE TABLE signed_pre_key (
 117                                           _id INTEGER PRIMARY KEY,
 118                                           account_id_type INTEGER NOT NULL,
 119                                           key_id INTEGER NOT NULL,
 120                                           public_key BLOB NOT NULL,
 121                                           private_key BLOB NOT NULL,
 122                                           signature BLOB NOT NULL,
 123                                           timestamp INTEGER DEFAULT 0,
 124                                           UNIQUE(account_id_type, key_id)
 125                                         ) STRICT;
 126                                         CREATE TABLE pre_key (
 127                                           _id INTEGER PRIMARY KEY,
 128                                           account_id_type INTEGER NOT NULL,
 129                                           key_id INTEGER NOT NULL,
 130                                           public_key BLOB NOT NULL,
 131                                           private_key BLOB NOT NULL,
 132                                           UNIQUE(account_id_type, key_id)
 133                                         ) STRICT;
 134                                         """);
 135             }
 136         }
 137         if (oldVersion < 5) {
 138             logger.debug("Updating database: Creating group tables");
 139             try (final var statement = connection.createStatement()) {
 140                 statement.executeUpdate("""
 141                                         CREATE TABLE group_v2 (
 142                                           _id INTEGER PRIMARY KEY,
 143                                           group_id BLOB UNIQUE NOT NULL,
 144                                           master_key BLOB NOT NULL,
 145                                           group_data BLOB,
 146                                           distribution_id BLOB UNIQUE NOT NULL,
 147                                           blocked INTEGER NOT NULL DEFAULT FALSE,
 148                                           permission_denied INTEGER NOT NULL DEFAULT FALSE
 149                                         ) STRICT;
 150                                         CREATE TABLE group_v1 (
 151                                           _id INTEGER PRIMARY KEY,
 152                                           group_id BLOB UNIQUE NOT NULL,
 153                                           group_id_v2 BLOB UNIQUE,
 154                                           name TEXT,
 155                                           color TEXT,
 156                                           expiration_time INTEGER NOT NULL DEFAULT 0,
 157                                           blocked INTEGER NOT NULL DEFAULT FALSE,
 158                                           archived INTEGER NOT NULL DEFAULT FALSE
 159                                         ) STRICT;
 160                                         CREATE TABLE group_v1_member (
 161                                           _id INTEGER PRIMARY KEY,
 162                                           group_id INTEGER NOT NULL REFERENCES group_v1 (_id) ON DELETE CASCADE,
 163                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 164                                           UNIQUE(group_id, recipient_id)
 165                                         ) STRICT;
 166                                         """);
 167             }
 168         }
 169         if (oldVersion < 6) {
 170             logger.debug("Updating database: Creating session tables");
 171             try (final var statement = connection.createStatement()) {
 172                 statement.executeUpdate("""
 173                                         CREATE TABLE session (
 174                                           _id INTEGER PRIMARY KEY,
 175                                           account_id_type INTEGER NOT NULL,
 176                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 177                                           device_id INTEGER NOT NULL,
 178                                           record BLOB NOT NULL,
 179                                           UNIQUE(account_id_type, recipient_id, device_id)
 180                                         ) STRICT;
 181                                         """);
 182             }
 183         }
 184         if (oldVersion < 7) {
 185             logger.debug("Updating database: Creating identity table");
 186             try (final var statement = connection.createStatement()) {
 187                 statement.executeUpdate("""
 188                                         CREATE TABLE identity (
 189                                           _id INTEGER PRIMARY KEY,
 190                                           recipient_id INTEGER UNIQUE NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 191                                           identity_key BLOB NOT NULL,
 192                                           added_timestamp INTEGER NOT NULL,
 193                                           trust_level INTEGER NOT NULL
 194                                         ) STRICT;
 195                                         """);
 196             }
 197         }
 198         if (oldVersion < 8) {
 199             logger.debug("Updating database: Creating sender key tables");
 200             try (final var statement = connection.createStatement()) {
 201                 statement.executeUpdate("""
 202                                         CREATE TABLE sender_key (
 203                                           _id INTEGER PRIMARY KEY,
 204                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 205                                           device_id INTEGER NOT NULL,
 206                                           distribution_id BLOB NOT NULL,
 207                                           record BLOB NOT NULL,
 208                                           created_timestamp INTEGER NOT NULL,
 209                                           UNIQUE(recipient_id, device_id, distribution_id)
 210                                         ) STRICT;
 211                                         CREATE TABLE sender_key_shared (
 212                                           _id INTEGER PRIMARY KEY,
 213                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 214                                           device_id INTEGER NOT NULL,
 215                                           distribution_id BLOB NOT NULL,
 216                                           timestamp INTEGER NOT NULL,
 217                                           UNIQUE(recipient_id, device_id, distribution_id)
 218                                         ) STRICT;
 219                                         """);
 220             }
 221         }
 222         if (oldVersion < 9) {
 223             logger.debug("Updating database: Adding urgent field");
 224             try (final var statement = connection.createStatement()) {
 225                 statement.executeUpdate("""
 226                                         ALTER TABLE message_send_log_content ADD COLUMN urgent INTEGER NOT NULL DEFAULT TRUE;
 227                                         """);
 228             }
 229         }
 230         if (oldVersion < 10) {
 231             logger.debug("Updating database: Key tables on serviceId instead of recipientId");
 232             try (final var statement = connection.createStatement()) {
 233                 statement.executeUpdate("""
 234                                         CREATE TABLE identity2 (
 235                                           _id INTEGER PRIMARY KEY,
 236                                           uuid BLOB UNIQUE NOT NULL,
 237                                           identity_key BLOB NOT NULL,
 238                                           added_timestamp INTEGER NOT NULL,
 239                                           trust_level INTEGER NOT NULL
 240                                         ) STRICT;
 241                                         INSERT INTO identity2 (_id, uuid, identity_key, added_timestamp, trust_level)
 242                                           SELECT i._id, r.uuid, i.identity_key, i.added_timestamp, i.trust_level
 243                                           FROM identity i LEFT JOIN recipient r ON i.recipient_id = r._id
 244                                           WHERE uuid IS NOT NULL;
 245                                         DROP TABLE identity;
 246                                         ALTER TABLE identity2 RENAME TO identity;
 247
 248                                         DROP INDEX msl_recipient_index;
 249                                         ALTER TABLE message_send_log ADD COLUMN uuid BLOB;
 250                                         UPDATE message_send_log
 251                                           SET uuid = r.uuid
 252                                           FROM message_send_log i, (SELECT _id, uuid FROM recipient) AS r
 253                                           WHERE i.recipient_id = r._id;
 254                                         DELETE FROM message_send_log WHERE uuid IS NULL;
 255                                         ALTER TABLE message_send_log DROP COLUMN recipient_id;
 256                                         CREATE INDEX msl_recipient_index ON message_send_log (uuid, device_id, content_id);
 257
 258                                         CREATE TABLE sender_key2 (
 259                                           _id INTEGER PRIMARY KEY,
 260                                           uuid BLOB NOT NULL,
 261                                           device_id INTEGER NOT NULL,
 262                                           distribution_id BLOB NOT NULL,
 263                                           record BLOB NOT NULL,
 264                                           created_timestamp INTEGER NOT NULL,
 265                                           UNIQUE(uuid, device_id, distribution_id)
 266                                         ) STRICT;
 267                                         INSERT INTO sender_key2 (_id, uuid, device_id, distribution_id, record, created_timestamp)
 268                                           SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.record, s.created_timestamp
 269                                           FROM sender_key s LEFT JOIN recipient r ON s.recipient_id = r._id
 270                                           WHERE uuid IS NOT NULL;
 271                                         DROP TABLE sender_key;
 272                                         ALTER TABLE sender_key2 RENAME TO sender_key;
 273
 274                                         CREATE TABLE sender_key_shared2 (
 275                                           _id INTEGER PRIMARY KEY,
 276                                           uuid BLOB NOT NULL,
 277                                           device_id INTEGER NOT NULL,
 278                                           distribution_id BLOB NOT NULL,
 279                                           timestamp INTEGER NOT NULL,
 280                                           UNIQUE(uuid, device_id, distribution_id)
 281                                         ) STRICT;
 282                                         INSERT INTO sender_key_shared2 (_id, uuid, device_id, distribution_id, timestamp)
 283                                           SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.timestamp
 284                                           FROM sender_key_shared s LEFT JOIN recipient r ON s.recipient_id = r._id
 285                                           WHERE uuid IS NOT NULL;
 286                                         DROP TABLE sender_key_shared;
 287                                         ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
 288
 289                                         CREATE TABLE session2 (
 290                                           _id INTEGER PRIMARY KEY,
 291                                           account_id_type INTEGER NOT NULL,
 292                                           uuid BLOB NOT NULL,
 293                                           device_id INTEGER NOT NULL,
 294                                           record BLOB NOT NULL,
 295                                           UNIQUE(account_id_type, uuid, device_id)
 296                                         ) STRICT;
 297                                         INSERT INTO session2 (_id, account_id_type, uuid, device_id, record)
 298                                           SELECT s._id, s.account_id_type, r.uuid, s.device_id, s.record
 299                                           FROM session s LEFT JOIN recipient r ON s.recipient_id = r._id
 300                                           WHERE uuid IS NOT NULL;
 301                                         DROP TABLE session;
 302                                         ALTER TABLE session2 RENAME TO session;
 303                                         """);
 304             }
 305         }
 306         if (oldVersion < 11) {
 307             logger.debug("Updating database: Adding pni field");
 308             try (final var statement = connection.createStatement()) {
 309                 statement.executeUpdate("""
 310                                         ALTER TABLE recipient ADD COLUMN pni BLOB;
 311                                         """);
 312             }
 313         }
 314         if (oldVersion < 12) {
 315             logger.debug("Updating database: Adding username field");
 316             try (final var statement = connection.createStatement()) {
 317                 statement.executeUpdate("""
 318                                         ALTER TABLE recipient ADD COLUMN username TEXT;
 319                                         """);
 320             }
 321         }
 322         if (oldVersion < 13) {
 323             logger.debug("Updating database: Cleanup unknown service ids");
 324             {
 325                 final var sql = """
 326                                 DELETE FROM identity AS i
 327                                 WHERE i.uuid = ?
 328                                 """;
 329                 try (final var statement = connection.prepareStatement(sql)) {
 330                     statement.setBytes(1, ACI.UNKNOWN.toByteArray());
 331                     statement.executeUpdate();
 332                 }
 333             }
 334             {
 335                 final var sql = """
 336                                 DELETE FROM sender_key_shared AS i
 337                                 WHERE i.uuid = ?
 338                                 """;
 339                 try (final var statement = connection.prepareStatement(sql)) {
 340                     statement.setBytes(1, ACI.UNKNOWN.toByteArray());
 341                     statement.executeUpdate();
 342                 }
 343             }
 344         }
 345         if (oldVersion < 14) {
 346             logger.debug("Updating database: Creating kyber_pre_key table");
 347             {
 348                 try (final var statement = connection.createStatement()) {
 349                     statement.executeUpdate("""
 350                                             CREATE TABLE kyber_pre_key (
 351                                                     _id INTEGER PRIMARY KEY,
 352                                                     account_id_type INTEGER NOT NULL,
 353                                                     key_id INTEGER NOT NULL,
 354                                                     serialized BLOB NOT NULL,
 355                                                     is_last_resort INTEGER NOT NULL,
 356                                                     UNIQUE(account_id_type, key_id)
 357                                             ) STRICT;
 358                                             """);
 359                 }
 360             }
 361         }
 362         if (oldVersion < 15) {
 363             logger.debug("Updating database: Store serviceId as TEXT");
 364             try (final var statement = connection.createStatement()) {
 365                 createUuidMappingTable(connection, statement);
 366
 367                 statement.executeUpdate("""
 368                                         CREATE TABLE identity2 (
 369                                           _id INTEGER PRIMARY KEY,
 370                                           address TEXT UNIQUE NOT NULL,
 371                                           identity_key BLOB NOT NULL,
 372                                           added_timestamp INTEGER NOT NULL,
 373                                           trust_level INTEGER NOT NULL
 374                                         ) STRICT;
 375                                         INSERT INTO identity2 (_id, address, identity_key, added_timestamp, trust_level)
 376                                           SELECT i._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = i.uuid) address, i.identity_key, i.added_timestamp, i.trust_level
 377                                           FROM identity i
 378                                           WHERE address IS NOT NULL;
 379                                         DROP TABLE identity;
 380                                         ALTER TABLE identity2 RENAME TO identity;
 381
 382                                         CREATE TABLE message_send_log2 (
 383                                           _id INTEGER PRIMARY KEY,
 384                                           content_id INTEGER NOT NULL REFERENCES message_send_log_content (_id) ON DELETE CASCADE,
 385                                           address TEXT NOT NULL,
 386                                           device_id INTEGER NOT NULL
 387                                         ) STRICT;
 388                                         INSERT INTO message_send_log2 (_id, content_id, address, device_id)
 389                                           SELECT m._id, m.content_id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = m.uuid) address, m.device_id
 390                                           FROM message_send_log m
 391                                           WHERE address IS NOT NULL;
 392                                         DROP INDEX msl_recipient_index;
 393                                         DROP INDEX msl_content_index;
 394                                         DROP TABLE message_send_log;
 395                                         ALTER TABLE message_send_log2 RENAME TO message_send_log;
 396                                         CREATE INDEX msl_recipient_index ON message_send_log (address, device_id, content_id);
 397                                         CREATE INDEX msl_content_index ON message_send_log (content_id);
 398
 399                                         CREATE TABLE sender_key2 (
 400                                           _id INTEGER PRIMARY KEY,
 401                                           address TEXT NOT NULL,
 402                                           device_id INTEGER NOT NULL,
 403                                           distribution_id BLOB NOT NULL,
 404                                           record BLOB NOT NULL,
 405                                           created_timestamp INTEGER NOT NULL,
 406                                           UNIQUE(address, device_id, distribution_id)
 407                                         ) STRICT;
 408                                         INSERT INTO sender_key2 (_id, address, device_id, distribution_id, record, created_timestamp)
 409                                           SELECT s._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.distribution_id, s.record, s.created_timestamp
 410                                           FROM sender_key s
 411                                           WHERE address IS NOT NULL;
 412                                         DROP TABLE sender_key;
 413                                         ALTER TABLE sender_key2 RENAME TO sender_key;
 414
 415                                         CREATE TABLE sender_key_shared2 (
 416                                           _id INTEGER PRIMARY KEY,
 417                                           address TEXT NOT NULL,
 418                                           device_id INTEGER NOT NULL,
 419                                           distribution_id BLOB NOT NULL,
 420                                           timestamp INTEGER NOT NULL,
 421                                           UNIQUE(address, device_id, distribution_id)
 422                                         ) STRICT;
 423                                         INSERT INTO sender_key_shared2 (_id, address, device_id, distribution_id, timestamp)
 424                                           SELECT s._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.distribution_id, s.timestamp
 425                                           FROM sender_key_shared s
 426                                           WHERE address IS NOT NULL;
 427                                         DROP TABLE sender_key_shared;
 428                                         ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
 429
 430                                         CREATE TABLE session2 (
 431                                           _id INTEGER PRIMARY KEY,
 432                                           account_id_type INTEGER NOT NULL,
 433                                           address TEXT NOT NULL,
 434                                           device_id INTEGER NOT NULL,
 435                                           record BLOB NOT NULL,
 436                                           UNIQUE(account_id_type, address, device_id)
 437                                         ) STRICT;
 438                                         INSERT INTO session2 (_id, account_id_type, address, device_id, record)
 439                                           SELECT s._id, s.account_id_type, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = s.uuid) address, s.device_id, s.record
 440                                           FROM session s
 441                                           WHERE address IS NOT NULL;
 442                                         DROP TABLE session;
 443                                         ALTER TABLE session2 RENAME TO session;
 444
 445                                         DROP TABLE tmp_mapping_table;
 446                                         """);
 447             }
 448         }
 449         if (oldVersion < 16) {
 450             logger.debug("Updating database: Adding stale_timestamp prekey field");
 451             try (final var statement = connection.createStatement()) {
 452                 statement.executeUpdate("""
 453                                         ALTER TABLE pre_key ADD COLUMN stale_timestamp INTEGER;
 454                                         ALTER TABLE kyber_pre_key ADD COLUMN stale_timestamp INTEGER;
 455                                         ALTER TABLE kyber_pre_key ADD COLUMN timestamp INTEGER DEFAULT 0;
 456                                         """);
 457             }
 458         }
 459         if (oldVersion < 17) {
 460             logger.debug("Updating database: Adding key_value table");
 461             try (final var statement = connection.createStatement()) {
 462                 statement.executeUpdate("""
 463                                         CREATE TABLE key_value (
 464                                                 _id INTEGER PRIMARY KEY,
 465                                                 key TEXT UNIQUE NOT NULL,
 466                                                 value ANY
 467                                         ) STRICT;
 468                                         """);
 469             }
 470         }
 471         if (oldVersion < 18) {
 472             logger.debug("Updating database: Adding cdsi table");
 473             try (final var statement = connection.createStatement()) {
 474                 statement.executeUpdate("""
 475                                         CREATE TABLE cdsi (
 476                                           _id INTEGER PRIMARY KEY,
 477                                           number TEXT NOT NULL UNIQUE,
 478                                           last_seen_at INTEGER NOT NULL
 479                                         ) STRICT;
 480                                         """);
 481             }
 482         }
 483         if (oldVersion < 19) {
 484             logger.debug("Updating database: Adding contact hidden column");
 485             try (final var statement = connection.createStatement()) {
 486                 statement.executeUpdate("""
 487                                         ALTER TABLE recipient ADD COLUMN hidden INTEGER NOT NULL DEFAULT FALSE;
 488                                         """);
 489             }
 490         }
 491         if (oldVersion < 20) {
 492             logger.debug("Updating database: Creating storage id tables and columns");
 493             try (final var statement = connection.createStatement()) {
 494                 statement.executeUpdate("""
 495                                         CREATE TABLE storage_id (
 496                                           _id INTEGER PRIMARY KEY,
 497                                           type INTEGER NOT NULL,
 498                                           storage_id BLOB UNIQUE NOT NULL
 499                                         ) STRICT;
 500                                         ALTER TABLE group_v1 ADD COLUMN storage_id BLOB;
 501                                         ALTER TABLE group_v1 ADD COLUMN storage_record BLOB;
 502                                         ALTER TABLE group_v2 ADD COLUMN storage_id BLOB;
 503                                         ALTER TABLE group_v2 ADD COLUMN storage_record BLOB;
 504                                         ALTER TABLE recipient ADD COLUMN storage_id BLOB;
 505                                         ALTER TABLE recipient ADD COLUMN storage_record BLOB;
 506                                         """);
 507             }
 508         }
 509         if (oldVersion < 21) {
 510             logger.debug("Updating database: Create unregistered column");
 511             try (final var statement = connection.createStatement()) {
 512                 statement.executeUpdate("""
 513                                         ALTER TABLE recipient ADD unregistered_timestamp INTEGER;
 514                                         """);
 515             }
 516         }
 517         if (oldVersion < 22) {
 518             logger.debug("Updating database: Store recipient aci/pni as TEXT");
 519             try (final var statement = connection.createStatement()) {
 520                 createUuidMappingTable(connection, statement);
 521
 522                 statement.executeUpdate("""
 523                                         CREATE TABLE recipient2 (
 524                                           _id INTEGER PRIMARY KEY AUTOINCREMENT,
 525                                           storage_id BLOB UNIQUE,
 526                                           storage_record BLOB,
 527                                           number TEXT UNIQUE,
 528                                           username TEXT UNIQUE,
 529                                           aci TEXT UNIQUE,
 530                                           pni TEXT UNIQUE,
 531                                           unregistered_timestamp INTEGER,
 532                                           profile_key BLOB,
 533                                           profile_key_credential BLOB,
 534
 535                                           given_name TEXT,
 536                                           family_name TEXT,
 537                                           nick_name TEXT,
 538                                           color TEXT,
 539
 540                                           expiration_time INTEGER NOT NULL DEFAULT 0,
 541                                           mute_until INTEGER NOT NULL DEFAULT 0,
 542                                           blocked INTEGER NOT NULL DEFAULT FALSE,
 543                                           archived INTEGER NOT NULL DEFAULT FALSE,
 544                                           profile_sharing INTEGER NOT NULL DEFAULT FALSE,
 545                                           hide_story INTEGER NOT NULL DEFAULT FALSE,
 546                                           hidden INTEGER NOT NULL DEFAULT FALSE,
 547
 548                                           profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
 549                                           profile_given_name TEXT,
 550                                           profile_family_name TEXT,
 551                                           profile_about TEXT,
 552                                           profile_about_emoji TEXT,
 553                                           profile_avatar_url_path TEXT,
 554                                           profile_mobile_coin_address BLOB,
 555                                           profile_unidentified_access_mode TEXT,
 556                                           profile_capabilities TEXT
 557                                         ) STRICT;
 558                                         INSERT INTO recipient2 (_id, aci, pni, storage_id, storage_record, number, username, unregistered_timestamp, profile_key, profile_key_credential, given_name, family_name, color, expiration_time, blocked, archived, profile_sharing, hidden, profile_last_update_timestamp, profile_given_name, profile_family_name, profile_about, profile_about_emoji, profile_avatar_url_path, profile_mobile_coin_address, profile_unidentified_access_mode, profile_capabilities)
 559                                           SELECT r._id, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = r.uuid AND t.address not like 'PNI:%') aci, (SELECT t.address FROM tmp_mapping_table t WHERE t.uuid = r.pni AND t.address like 'PNI:%' AND (SELECT COUNT(pni) FROM recipient WHERE pni = r.pni) = 1) pni, storage_id, storage_record, number, username, unregistered_timestamp, profile_key, profile_key_credential, given_name, family_name, color, expiration_time, blocked, archived, profile_sharing, hidden, profile_last_update_timestamp, profile_given_name, profile_family_name, profile_about, profile_about_emoji, profile_avatar_url_path, profile_mobile_coin_address, profile_unidentified_access_mode, profile_capabilities
 560                                           FROM recipient r;
 561                                         DROP TABLE recipient;
 562                                         ALTER TABLE recipient2 RENAME TO recipient;
 563
 564                                         DROP TABLE tmp_mapping_table;
 565                                         """);
 566             }
 567         }
 568         if (oldVersion < 23) {
 569             logger.debug("Updating database: Create group profile sharing column");
 570             try (final var statement = connection.createStatement()) {
 571                 statement.executeUpdate("""
 572                                         ALTER TABLE group_v2 ADD profile_sharing INTEGER NOT NULL DEFAULT TRUE;
 573                                         """);
 574             }
 575         }
 576         if (oldVersion < 24) {
 577             logger.debug("Updating database: Create needs_pni_signature column");
 578             try (final var statement = connection.createStatement()) {
 579                 statement.executeUpdate("""
 580                                         ALTER TABLE recipient ADD needs_pni_signature INTEGER NOT NULL DEFAULT FALSE;
 581                                         """);
 582             }
 583         }
 584         if (oldVersion < 25) {
 585             logger.debug("Updating database: Create nick_name and note columns");
 586             try (final var statement = connection.createStatement()) {
 587                 statement.executeUpdate("""
 588                                         ALTER TABLE recipient ADD nick_name_given_name TEXT;
 589                                         ALTER TABLE recipient ADD nick_name_family_name TEXT;
 590                                         ALTER TABLE recipient ADD note TEXT;
 591                                         """);
 592             }
 593         }
 594         if (oldVersion < 26) {
 595             logger.debug("Updating database: Create discoverabel and profile_phone_number_sharing columns");
 596             try (final var statement = connection.createStatement()) {
 597                 statement.executeUpdate("""
 598                                         ALTER TABLE recipient ADD discoverable INTEGER;
 599                                         ALTER TABLE recipient ADD profile_phone_number_sharing TEXT;
 600                                         """);
 601             }
 602         }
 603         if (oldVersion < 27) {
 604             logger.debug("Updating database: Create expiration_time_version column");
 605             try (final var statement = connection.createStatement()) {
 606                 statement.executeUpdate("""
 607                                         ALTER TABLE recipient ADD expiration_time_version INTEGER DEFAULT 1 NOT NULL;
 608                                         """);
 609             }
 610         }
 611     }
 612
 613     private static void createUuidMappingTable(
 614             final Connection connection, final Statement statement
 615     ) throws SQLException {
 616         statement.executeUpdate("""
 617                                 CREATE TABLE tmp_mapping_table (
 618                                   uuid BLOB NOT NULL,
 619                                   address TEXT NOT NULL
 620                                 ) STRICT;
 621                                 """);
 622
 623         final var sql = (
 624                 """
 625                 SELECT r.uuid, r.pni
 626                 FROM recipient r
 627                 """
 628         );
 629         final var uuidAddressMapping = new HashMap<UUID, ServiceId>();
 630         try (final var preparedStatement = connection.prepareStatement(sql)) {
 631             try (var result = Utils.executeQueryForStream(preparedStatement, (resultSet) -> {
 632                 final var pni = Optional.ofNullable(resultSet.getBytes("pni"))
 633                         .map(UuidUtil::parseOrNull)
 634                         .map(ServiceId.PNI::from);
 635                 final var serviceIdUuid = Optional.ofNullable(resultSet.getBytes("uuid")).map(UuidUtil::parseOrNull);
 636                 final var serviceId = serviceIdUuid.isPresent() && pni.isPresent() && serviceIdUuid.get()
 637                         .equals(pni.get().getRawUuid())
 638                         ? pni.<ServiceId>map(p -> p)
 639                         : serviceIdUuid.<ServiceId>map(ACI::from);
 640
 641                 return new Pair<>(serviceId, pni);
 642             })) {
 643                 result.forEach(p -> {
 644                     final var serviceId = p.first();
 645                     final var pni = p.second();
 646                     if (serviceId.isPresent()) {
 647                         final var rawUuid = serviceId.get().getRawUuid();
 648                         if (!uuidAddressMapping.containsKey(rawUuid)) {
 649                             uuidAddressMapping.put(rawUuid, serviceId.get());
 650                         }
 651                     }
 652                     if (pni.isPresent()) {
 653                         uuidAddressMapping.put(pni.get().getRawUuid(), pni.get());
 654                     }
 655                 });
 656             }
 657         }
 658
 659         final var insertSql = """
 660                               INSERT INTO tmp_mapping_table (uuid, address)
 661                               VALUES (?,?)
 662                               """;
 663         try (final var insertStatement = connection.prepareStatement(insertSql)) {
 664             for (final var entry : uuidAddressMapping.entrySet()) {
 665                 final var uuid = entry.getKey();
 666                 final var serviceId = entry.getValue();
 667                 insertStatement.setBytes(1, UuidUtil.toByteArray(uuid));
 668                 insertStatement.setString(2, serviceId.toString());
 669                 insertStatement.execute();
 670             }
 671         }
 672     }
 673 }