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.storage.groups.GroupStore;
   6 import org.asamk.signal.manager.storage.identities.IdentityKeyStore;
   7 import org.asamk.signal.manager.storage.prekeys.KyberPreKeyStore;
   8 import org.asamk.signal.manager.storage.prekeys.PreKeyStore;
   9 import org.asamk.signal.manager.storage.prekeys.SignedPreKeyStore;
  10 import org.asamk.signal.manager.storage.recipients.RecipientStore;
  11 import org.asamk.signal.manager.storage.sendLog.MessageSendLogStore;
  12 import org.asamk.signal.manager.storage.senderKeys.SenderKeyRecordStore;
  13 import org.asamk.signal.manager.storage.senderKeys.SenderKeySharedStore;
  14 import org.asamk.signal.manager.storage.sessions.SessionStore;
  15 import org.asamk.signal.manager.storage.stickers.StickerStore;
  16 import org.slf4j.Logger;
  17 import org.slf4j.LoggerFactory;
  18 import org.whispersystems.signalservice.api.push.ServiceId;
  19
  20 import java.io.File;
  21 import java.sql.Connection;
  22 import java.sql.SQLException;
  23
  24 public class AccountDatabase extends Database {
  25
  26     private final static Logger logger = LoggerFactory.getLogger(AccountDatabase.class);
  27     private static final long DATABASE_VERSION = 14;
  28
  29     private AccountDatabase(final HikariDataSource dataSource) {
  30         super(logger, DATABASE_VERSION, dataSource);
  31     }
  32
  33     public static AccountDatabase init(File databaseFile) throws SQLException {
  34         return initDatabase(databaseFile, AccountDatabase::new);
  35     }
  36
  37     @Override
  38     protected void createDatabase(final Connection connection) throws SQLException {
  39         RecipientStore.createSql(connection);
  40         MessageSendLogStore.createSql(connection);
  41         StickerStore.createSql(connection);
  42         PreKeyStore.createSql(connection);
  43         SignedPreKeyStore.createSql(connection);
  44         KyberPreKeyStore.createSql(connection);
  45         GroupStore.createSql(connection);
  46         SessionStore.createSql(connection);
  47         IdentityKeyStore.createSql(connection);
  48         SenderKeyRecordStore.createSql(connection);
  49         SenderKeySharedStore.createSql(connection);
  50     }
  51
  52     @Override
  53     protected void upgradeDatabase(final Connection connection, final long oldVersion) throws SQLException {
  54         if (oldVersion < 2) {
  55             logger.debug("Updating database: Creating recipient table");
  56             try (final var statement = connection.createStatement()) {
  57                 statement.executeUpdate("""
  58                                         CREATE TABLE recipient (
  59                                           _id INTEGER PRIMARY KEY AUTOINCREMENT,
  60                                           number TEXT UNIQUE,
  61                                           uuid BLOB UNIQUE,
  62                                           profile_key BLOB,
  63                                           profile_key_credential BLOB,
  64
  65                                           given_name TEXT,
  66                                           family_name TEXT,
  67                                           color TEXT,
  68
  69                                           expiration_time INTEGER NOT NULL DEFAULT 0,
  70                                           blocked INTEGER NOT NULL DEFAULT FALSE,
  71                                           archived INTEGER NOT NULL DEFAULT FALSE,
  72                                           profile_sharing INTEGER NOT NULL DEFAULT FALSE,
  73
  74                                           profile_last_update_timestamp INTEGER NOT NULL DEFAULT 0,
  75                                           profile_given_name TEXT,
  76                                           profile_family_name TEXT,
  77                                           profile_about TEXT,
  78                                           profile_about_emoji TEXT,
  79                                           profile_avatar_url_path TEXT,
  80                                           profile_mobile_coin_address BLOB,
  81                                           profile_unidentified_access_mode TEXT,
  82                                           profile_capabilities TEXT
  83                                         ) STRICT;
  84                                         """);
  85             }
  86         }
  87         if (oldVersion < 3) {
  88             logger.debug("Updating database: Creating sticker table");
  89             try (final var statement = connection.createStatement()) {
  90                 statement.executeUpdate("""
  91                                         CREATE TABLE sticker (
  92                                           _id INTEGER PRIMARY KEY,
  93                                           pack_id BLOB UNIQUE NOT NULL,
  94                                           pack_key BLOB NOT NULL,
  95                                           installed INTEGER NOT NULL DEFAULT FALSE
  96                                         ) STRICT;
  97                                         """);
  98             }
  99         }
 100         if (oldVersion < 4) {
 101             logger.debug("Updating database: Creating pre key tables");
 102             try (final var statement = connection.createStatement()) {
 103                 statement.executeUpdate("""
 104                                         CREATE TABLE signed_pre_key (
 105                                           _id INTEGER PRIMARY KEY,
 106                                           account_id_type INTEGER NOT NULL,
 107                                           key_id INTEGER NOT NULL,
 108                                           public_key BLOB NOT NULL,
 109                                           private_key BLOB NOT NULL,
 110                                           signature BLOB NOT NULL,
 111                                           timestamp INTEGER DEFAULT 0,
 112                                           UNIQUE(account_id_type, key_id)
 113                                         ) STRICT;
 114                                         CREATE TABLE pre_key (
 115                                           _id INTEGER PRIMARY KEY,
 116                                           account_id_type INTEGER NOT NULL,
 117                                           key_id INTEGER NOT NULL,
 118                                           public_key BLOB NOT NULL,
 119                                           private_key BLOB NOT NULL,
 120                                           UNIQUE(account_id_type, key_id)
 121                                         ) STRICT;
 122                                         """);
 123             }
 124         }
 125         if (oldVersion < 5) {
 126             logger.debug("Updating database: Creating group tables");
 127             try (final var statement = connection.createStatement()) {
 128                 statement.executeUpdate("""
 129                                         CREATE TABLE group_v2 (
 130                                           _id INTEGER PRIMARY KEY,
 131                                           group_id BLOB UNIQUE NOT NULL,
 132                                           master_key BLOB NOT NULL,
 133                                           group_data BLOB,
 134                                           distribution_id BLOB UNIQUE NOT NULL,
 135                                           blocked INTEGER NOT NULL DEFAULT FALSE,
 136                                           permission_denied INTEGER NOT NULL DEFAULT FALSE
 137                                         ) STRICT;
 138                                         CREATE TABLE group_v1 (
 139                                           _id INTEGER PRIMARY KEY,
 140                                           group_id BLOB UNIQUE NOT NULL,
 141                                           group_id_v2 BLOB UNIQUE,
 142                                           name TEXT,
 143                                           color TEXT,
 144                                           expiration_time INTEGER NOT NULL DEFAULT 0,
 145                                           blocked INTEGER NOT NULL DEFAULT FALSE,
 146                                           archived INTEGER NOT NULL DEFAULT FALSE
 147                                         ) STRICT;
 148                                         CREATE TABLE group_v1_member (
 149                                           _id INTEGER PRIMARY KEY,
 150                                           group_id INTEGER NOT NULL REFERENCES group_v1 (_id) ON DELETE CASCADE,
 151                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 152                                           UNIQUE(group_id, recipient_id)
 153                                         ) STRICT;
 154                                         """);
 155             }
 156         }
 157         if (oldVersion < 6) {
 158             logger.debug("Updating database: Creating session tables");
 159             try (final var statement = connection.createStatement()) {
 160                 statement.executeUpdate("""
 161                                         CREATE TABLE session (
 162                                           _id INTEGER PRIMARY KEY,
 163                                           account_id_type INTEGER NOT NULL,
 164                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 165                                           device_id INTEGER NOT NULL,
 166                                           record BLOB NOT NULL,
 167                                           UNIQUE(account_id_type, recipient_id, device_id)
 168                                         ) STRICT;
 169                                         """);
 170             }
 171         }
 172         if (oldVersion < 7) {
 173             logger.debug("Updating database: Creating identity table");
 174             try (final var statement = connection.createStatement()) {
 175                 statement.executeUpdate("""
 176                                         CREATE TABLE identity (
 177                                           _id INTEGER PRIMARY KEY,
 178                                           recipient_id INTEGER UNIQUE NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 179                                           identity_key BLOB NOT NULL,
 180                                           added_timestamp INTEGER NOT NULL,
 181                                           trust_level INTEGER NOT NULL
 182                                         ) STRICT;
 183                                         """);
 184             }
 185         }
 186         if (oldVersion < 8) {
 187             logger.debug("Updating database: Creating sender key tables");
 188             try (final var statement = connection.createStatement()) {
 189                 statement.executeUpdate("""
 190                                         CREATE TABLE sender_key (
 191                                           _id INTEGER PRIMARY KEY,
 192                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 193                                           device_id INTEGER NOT NULL,
 194                                           distribution_id BLOB NOT NULL,
 195                                           record BLOB NOT NULL,
 196                                           created_timestamp INTEGER NOT NULL,
 197                                           UNIQUE(recipient_id, device_id, distribution_id)
 198                                         ) STRICT;
 199                                         CREATE TABLE sender_key_shared (
 200                                           _id INTEGER PRIMARY KEY,
 201                                           recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
 202                                           device_id INTEGER NOT NULL,
 203                                           distribution_id BLOB NOT NULL,
 204                                           timestamp INTEGER NOT NULL,
 205                                           UNIQUE(recipient_id, device_id, distribution_id)
 206                                         ) STRICT;
 207                                         """);
 208             }
 209         }
 210         if (oldVersion < 9) {
 211             logger.debug("Updating database: Adding urgent field");
 212             try (final var statement = connection.createStatement()) {
 213                 statement.executeUpdate("""
 214                                         ALTER TABLE message_send_log_content ADD COLUMN urgent INTEGER NOT NULL DEFAULT TRUE;
 215                                         """);
 216             }
 217         }
 218         if (oldVersion < 10) {
 219             logger.debug("Updating database: Key tables on serviceId instead of recipientId");
 220             try (final var statement = connection.createStatement()) {
 221                 statement.executeUpdate("""
 222                                         CREATE TABLE identity2 (
 223                                           _id INTEGER PRIMARY KEY,
 224                                           uuid BLOB UNIQUE NOT NULL,
 225                                           identity_key BLOB NOT NULL,
 226                                           added_timestamp INTEGER NOT NULL,
 227                                           trust_level INTEGER NOT NULL
 228                                         ) STRICT;
 229                                         INSERT INTO identity2 (_id, uuid, identity_key, added_timestamp, trust_level)
 230                                           SELECT i._id, r.uuid, i.identity_key, i.added_timestamp, i.trust_level
 231                                           FROM identity i LEFT JOIN recipient r ON i.recipient_id = r._id
 232                                           WHERE uuid IS NOT NULL;
 233                                         DROP TABLE identity;
 234                                         ALTER TABLE identity2 RENAME TO identity;
 235
 236                                         DROP INDEX msl_recipient_index;
 237                                         ALTER TABLE message_send_log ADD COLUMN uuid BLOB;
 238                                         UPDATE message_send_log
 239                                           SET uuid = r.uuid
 240                                           FROM message_send_log i, (SELECT _id, uuid FROM recipient) AS r
 241                                           WHERE i.recipient_id = r._id;
 242                                         DELETE FROM message_send_log WHERE uuid IS NULL;
 243                                         ALTER TABLE message_send_log DROP COLUMN recipient_id;
 244                                         CREATE INDEX msl_recipient_index ON message_send_log (uuid, device_id, content_id);
 245
 246                                         CREATE TABLE sender_key2 (
 247                                           _id INTEGER PRIMARY KEY,
 248                                           uuid BLOB NOT NULL,
 249                                           device_id INTEGER NOT NULL,
 250                                           distribution_id BLOB NOT NULL,
 251                                           record BLOB NOT NULL,
 252                                           created_timestamp INTEGER NOT NULL,
 253                                           UNIQUE(uuid, device_id, distribution_id)
 254                                         ) STRICT;
 255                                         INSERT INTO sender_key2 (_id, uuid, device_id, distribution_id, record, created_timestamp)
 256                                           SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.record, s.created_timestamp
 257                                           FROM sender_key s LEFT JOIN recipient r ON s.recipient_id = r._id
 258                                           WHERE uuid IS NOT NULL;
 259                                         DROP TABLE sender_key;
 260                                         ALTER TABLE sender_key2 RENAME TO sender_key;
 261
 262                                         CREATE TABLE sender_key_shared2 (
 263                                           _id INTEGER PRIMARY KEY,
 264                                           uuid BLOB NOT NULL,
 265                                           device_id INTEGER NOT NULL,
 266                                           distribution_id BLOB NOT NULL,
 267                                           timestamp INTEGER NOT NULL,
 268                                           UNIQUE(uuid, device_id, distribution_id)
 269                                         ) STRICT;
 270                                         INSERT INTO sender_key_shared2 (_id, uuid, device_id, distribution_id, timestamp)
 271                                           SELECT s._id, r.uuid, s.device_id, s.distribution_id, s.timestamp
 272                                           FROM sender_key_shared s LEFT JOIN recipient r ON s.recipient_id = r._id
 273                                           WHERE uuid IS NOT NULL;
 274                                         DROP TABLE sender_key_shared;
 275                                         ALTER TABLE sender_key_shared2 RENAME TO sender_key_shared;
 276
 277                                         CREATE TABLE session2 (
 278                                           _id INTEGER PRIMARY KEY,
 279                                           account_id_type INTEGER NOT NULL,
 280                                           uuid BLOB NOT NULL,
 281                                           device_id INTEGER NOT NULL,
 282                                           record BLOB NOT NULL,
 283                                           UNIQUE(account_id_type, uuid, device_id)
 284                                         ) STRICT;
 285                                         INSERT INTO session2 (_id, account_id_type, uuid, device_id, record)
 286                                           SELECT s._id, s.account_id_type, r.uuid, s.device_id, s.record
 287                                           FROM session s LEFT JOIN recipient r ON s.recipient_id = r._id
 288                                           WHERE uuid IS NOT NULL;
 289                                         DROP TABLE session;
 290                                         ALTER TABLE session2 RENAME TO session;
 291                                         """);
 292             }
 293         }
 294         if (oldVersion < 11) {
 295             logger.debug("Updating database: Adding pni field");
 296             try (final var statement = connection.createStatement()) {
 297                 statement.executeUpdate("""
 298                                         ALTER TABLE recipient ADD COLUMN pni BLOB;
 299                                         """);
 300             }
 301         }
 302         if (oldVersion < 12) {
 303             logger.debug("Updating database: Adding username field");
 304             try (final var statement = connection.createStatement()) {
 305                 statement.executeUpdate("""
 306                                         ALTER TABLE recipient ADD COLUMN username TEXT;
 307                                         """);
 308             }
 309         }
 310         if (oldVersion < 13) {
 311             logger.debug("Updating database: Cleanup unknown service ids");
 312             {
 313                 final var sql = """
 314                                 DELETE FROM identity AS i
 315                                 WHERE i.uuid = ?
 316                                 """;
 317                 try (final var statement = connection.prepareStatement(sql)) {
 318                     statement.setBytes(1, ServiceId.UNKNOWN.toByteArray());
 319                     statement.executeUpdate();
 320                 }
 321             }
 322             {
 323                 final var sql = """
 324                                 DELETE FROM sender_key_shared AS i
 325                                 WHERE i.uuid = ?
 326                                 """;
 327                 try (final var statement = connection.prepareStatement(sql)) {
 328                     statement.setBytes(1, ServiceId.UNKNOWN.toByteArray());
 329                     statement.executeUpdate();
 330                 }
 331             }
 332         }
 333         if (oldVersion < 14) {
 334             logger.debug("Updating database: Creating kyber_pre_key table");
 335             {
 336                 try (final var statement = connection.createStatement()) {
 337                     statement.executeUpdate("""
 338                                             CREATE TABLE kyber_pre_key (
 339                                                     _id INTEGER PRIMARY KEY,
 340                                                     account_id_type INTEGER NOT NULL,
 341                                                     key_id INTEGER NOT NULL,
 342                                                     serialized BLOB NOT NULL,
 343                                                     is_last_resort INTEGER NOT NULL,
 344                                                     UNIQUE(account_id_type, key_id)
 345                                             ) STRICT;
 346                                             """);
 347                 }
 348             }
 349
 350         }
 351     }
 352 }