github.com/status-im/status-go@v1.1.0/appdatabase/migrations/sql/1683627613_accounts_and_keycards_improvements.up.sql (about)

     1  -- Possible `operable` column values:
     2  -- "no"        // an account is non operable it is not a keycard account and there is no keystore file for it and no keystore file for the address it is derived from
     3  -- "partially" // an account is partially operable if it is not a keycard account and there is created keystore file for the address it is derived from
     4  -- "fully"     // an account is fully operable if it is not a keycard account and there is a keystore file for it
     5  
     6  -- Adding new tables `keypairs` and `keypairs_accounts`
     7  CREATE TABLE IF NOT EXISTS keypairs (
     8    key_uid VARCHAR PRIMARY KEY NOT NULL CHECK (length(trim(key_uid)) > 0),
     9    name VARCHAR NOT NULL DEFAULT "",
    10    type VARCHAR NOT NULL DEFAULT "",
    11    derived_from VARCHAR NOT NULL DEFAULT "",
    12    last_used_derivation_index INT NOT NULL DEFAULT 0,
    13    synced_from VARCHAR NOT NULL DEFAULT "", -- keeps an info which device this keypair is added from
    14    clock INT NOT NULL DEFAULT 0
    15  );
    16  
    17  CREATE TABLE IF NOT EXISTS keypairs_accounts (
    18    address VARCHAR PRIMARY KEY,
    19    key_uid VARCHAR,
    20    pubkey VARCHAR,
    21    path VARCHAR NOT NULL DEFAULT "",
    22    name VARCHAR NOT NULL DEFAULT "",
    23    color VARCHAR NOT NULL DEFAULT "",
    24    emoji VARCHAR NOT NULL DEFAULT "",
    25    wallet BOOL NOT NULL DEFAULT FALSE,
    26    chat BOOL NOT NULL DEFAULT FALSE,
    27    hidden BOOL NOT NULL DEFAULT FALSE,
    28    operable VARCHAR NOT NULL DEFAULT "no", -- describes an account's operability (read an explanation at the top of this file)
    29    created_at DATETIME NOT NULL,
    30    updated_at DATETIME NOT NULL,
    31    clock INT NOT NULL DEFAULT 0,
    32    FOREIGN KEY(key_uid) REFERENCES keypairs(key_uid) 
    33      ON DELETE CASCADE
    34  );
    35  
    36  -- Fulfilling the tables
    37  INSERT INTO keypairs 
    38    SELECT key_uid, keypair_name, "profile", derived_from, last_used_derivation_index, "", clock 
    39    FROM accounts 
    40    WHERE type != "watch" AND type != "seed" AND type != "key"
    41    GROUP BY key_uid;
    42  
    43  INSERT INTO keypairs 
    44    SELECT key_uid, keypair_name, type, derived_from, last_used_derivation_index, "", clock 
    45    FROM accounts 
    46    WHERE type != "watch" AND type != "" AND type != "generated"
    47    GROUP BY key_uid;
    48  
    49  INSERT INTO keypairs_accounts 
    50    SELECT a.address, kp.key_uid, a.pubkey, a.path, a.name, a.color, a.emoji, a.wallet, a.chat, a.hidden, "fully", a.created_at, a.updated_at, a.clock 
    51    FROM accounts a 
    52    LEFT JOIN keypairs kp 
    53    ON a.key_uid = kp.key_uid;
    54  
    55  -- Removing old `accounts` table
    56  DROP TABLE accounts;
    57  
    58  -- Add foreign key to `keycards` table
    59  -- There is no other way to add foreign key to `keycards` table except to re-create tables.
    60  ALTER TABLE keycards RENAME TO keycards_old;
    61  ALTER TABLE keycards_accounts RENAME TO keycards_accounts_old;
    62  
    63  CREATE TABLE IF NOT EXISTS keycards (
    64      keycard_uid VARCHAR NOT NULL PRIMARY KEY,
    65      keycard_name VARCHAR NOT NULL,
    66      keycard_locked BOOLEAN DEFAULT FALSE,
    67      key_uid VARCHAR NOT NULL,
    68      last_update_clock INT NOT NULL DEFAULT 0,
    69      FOREIGN KEY(key_uid) REFERENCES keypairs(key_uid) 
    70        ON DELETE CASCADE
    71  );
    72  
    73  CREATE TABLE IF NOT EXISTS keycards_accounts (
    74      keycard_uid VARCHAR NOT NULL,
    75      account_address VARCHAR NOT NULL,
    76      FOREIGN KEY(keycard_uid) REFERENCES keycards(keycard_uid) 
    77        ON UPDATE CASCADE
    78        ON DELETE CASCADE
    79  );
    80  
    81  INSERT INTO keycards 
    82    SELECT kc_old.keycard_uid, kc_old.keycard_name, kc_old.keycard_locked, kp.key_uid, kc_old.last_update_clock 
    83    FROM keycards_old kc_old
    84    JOIN keypairs kp 
    85    ON kc_old.key_uid = kp.key_uid;
    86  
    87  INSERT INTO keycards_accounts 
    88    SELECT kc.keycard_uid, kc_acc_old.account_address 
    89    FROM keycards_accounts_old kc_acc_old
    90    JOIN keycards kc
    91    ON kc_acc_old.keycard_uid = kc.keycard_uid;
    92  
    93  DROP TABLE keycards_accounts_old;
    94  DROP TABLE keycards_old;