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