github.com/status-im/status-go@v1.1.0/protocol/migrations/sqlite/000001_init.up.db.sql (about)

     1  CREATE TABLE IF NOT EXISTS chats (
     2    id VARCHAR PRIMARY KEY ON CONFLICT REPLACE,
     3    name VARCHAR NOT NULL,
     4    color VARCHAR NOT NULL DEFAULT '#a187d5',
     5    type INT NOT NULL,
     6    active BOOLEAN NOT NULL DEFAULT TRUE,
     7    timestamp INT NOT NULL,
     8    deleted_at_clock_value INT NOT NULL DEFAULT 0,
     9    public_key BLOB,
    10    unviewed_message_count INT NOT NULL DEFAULT 0,
    11    last_clock_value INT NOT NULL DEFAULT 0,
    12    last_message BLOB,
    13    members BLOB,
    14    membership_updates BLOB
    15  );
    16  
    17  CREATE TABLE contacts (
    18    id TEXT PRIMARY KEY ON CONFLICT REPLACE,
    19    address TEXT NOT NULL,
    20    name TEXT NOT NULL,
    21    ens_verified BOOLEAN DEFAULT FALSE,
    22    ens_verified_at INT NOT NULL DEFAULT 0,
    23    alias TEXT NOT NULL,
    24    identicon TEXT NOT NULL,
    25    photo TEXT NOT NULL,
    26    last_updated INT NOT NULL DEFAULT 0,
    27    system_tags BLOB,
    28    device_info BLOB,
    29    tribute_to_talk TEXT NOT NULL
    30  );
    31  
    32  -- It's important that this table has rowid as we rely on it
    33  -- when implementing infinite-scroll.
    34  CREATE TABLE IF NOT EXISTS user_messages (
    35      id VARCHAR PRIMARY KEY ON CONFLICT REPLACE,
    36      whisper_timestamp INTEGER NOT NULL,
    37      source TEXT NOT NULL,
    38      destination BLOB,
    39      text VARCHAR NOT NULL,
    40      content_type INT NOT NULL,
    41      username VARCHAR,
    42      timestamp INT NOT NULL,
    43      chat_id VARCHAR NOT NULL,
    44      local_chat_id VARCHAR NOT NULL,
    45      hide BOOLEAN DEFAULT FALSE,
    46      response_to VARCHAR,
    47      message_type INT,
    48      clock_value INT NOT NULL,
    49      seen BOOLEAN NOT NULL DEFAULT FALSE,
    50      outgoing_status VARCHAR,
    51      parsed_text BLOB,
    52      raw_payload BLOB,
    53      sticker_pack INT,
    54      sticker_hash VARCHAR,
    55      command_id VARCHAR,
    56      command_value VARCHAR,
    57      command_address VARCHAR,
    58      command_from VARCHAR,
    59      command_contract VARCHAR,
    60      command_transaction_hash VARCHAR,
    61      command_signature BLOB,
    62      command_state INT
    63  );
    64  
    65  CREATE INDEX idx_source ON user_messages(source);
    66  CREATE INDEX idx_search_by_chat_id ON  user_messages(
    67      substr('0000000000000000000000000000000000000000000000000000000000000000' || clock_value, -64, 64) || id, chat_id, hide
    68  );
    69  
    70  CREATE TABLE IF NOT EXISTS raw_messages (
    71    id VARCHAR PRIMARY KEY ON CONFLICT REPLACE,
    72    local_chat_id VARCHAR NOT NULL,
    73    last_sent INT NOT NULL,
    74    send_count INT NOT NULL,
    75    sent BOOLEAN DEFAULT FALSE,
    76    resend_automatically BOOLEAN DEFAULT FALSE,
    77    message_type INT,
    78    recipients BLOB,
    79    payload BLOB);
    80  
    81  CREATE TABLE IF NOT EXISTS messenger_transactions_to_validate (
    82    message_id VARCHAR,
    83    command_id VARCHAR NOT NULL,
    84    transaction_hash VARCHAR PRIMARY KEY,
    85    retry_count INT,
    86    first_seen INT,
    87    signature BLOB NOT NULL,
    88    to_validate BOOLEAN DEFAULT TRUE,
    89    public_key BLOB);
    90  
    91  CREATE INDEX idx_messenger_transaction_to_validate ON  messenger_transactions_to_validate(to_validate);