github.com/status-im/status-go@v1.1.0/protocol/migrations/sqlite/1632303896_modify_contacts_table.up.sql (about)

     1  ALTER TABLE contacts ADD COLUMN added BOOLEAN DEFAULT FALSE;
     2  ALTER TABLE contacts ADD COLUMN has_added_us BOOLEAN DEFAULT FALSE;
     3  ALTER TABLE contacts ADD COLUMN blocked BOOLEAN DEFAULT FALSE;
     4  
     5  CREATE INDEX tmp_contacts ON contacts(hex(system_tags));
     6  
     7  UPDATE contacts SET added = 1 WHERE hex(system_tags) LIKE '%6164646564%';
     8  UPDATE contacts SET has_added_us = 1 WHERE hex(system_tags) LIKE '%72656365%';
     9  UPDATE contacts SET blocked = 1 WHERE hex(system_tags) LIKE '%626c6f636b6564%';
    10  CREATE INDEX contacts_added on contacts(added);
    11  CREATE INDEX contacts_has_added_us on contacts(has_added_us);
    12  CREATE INDEX contacts_blocked on contacts(blocked);
    13  CREATE INDEX contacts_local_nickname on contacts(local_nickname);
    14  CREATE INDEX tmp_contacts_delete ON contacts(added, blocked, local_nickname);
    15  
    16  DELETE FROM contacts
    17  WHERE NOT(added) AND NOT(blocked) AND NOT(has_added_us) AND (local_nickname == "" OR local_nickname IS NULL)
    18        AND NOT EXISTS (SELECT 1 FROM chat_identity_contacts i WHERE id = i.contact_id)
    19        AND NOT EXISTS (SELECT 1 FROM ens_verification_records v  WHERE id = v.public_key);
    20  
    21  /*
    22  would be cool to remove these columns
    23  
    24  ALTER TABLE contacts DROP COLUMN name;
    25  ALTER TABLE contacts DROP COLUMN ens_verified;
    26  ALTER TABLE contacts DROP COLUMN ens_verified_at;
    27  ALTER TABLE contacts DROP COLUMN device_info;
    28  ALTER TABLE contacts DROP COLUMN system_tags;
    29  ALTER TABLE contacts DROP COLUMN tribute_to_talk;
    30  ALTER TABLE contacts DROP COLUMN last_ens_clock_value;
    31  ALTER TABLE contacts DROP COLUMN photo;
    32  */
    33  
    34  DROP INDEX tmp_contacts_delete;
    35  DROP INDEX tmp_contacts;