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