search

status-im / nim-status

MIT License
9 stars 6 forks source link

Create user db schema for use with db initialization #206

Open emizzle opened 3 years ago

emizzle commented 3 years ago

Create a fresh status-go db, and export the db without the accounts (can be done with DB Browser for SQLite. It should have default settings populated.

Migrations will then only be needed for incremental changes after nim-status is being actively used.

michaelsbradleyjr commented 3 years ago

Clean db schema-dump diff re: master of this repo and status-go relative to current master of status-desktop (https://github.com/status-im/status-go/tree/192ca6e3190714a888a8d585a1113eaffe0b7d9c):

modified   user.sql
@@ -1,4 +1,6 @@
-CREATE TABLE migrations (name VARCHAR NOT NULL PRIMARY KEY,hash VARCHAR NOT NULL);
+CREATE TABLE status_go_schema_migrations (version uint64,dirty bool);
+
+CREATE UNIQUE INDEX version_unique ON status_go_schema_migrations (version);

 CREATE TABLE chats (
     id VARCHAR PRIMARY KEY ON CONFLICT REPLACE,
@@ -17,7 +19,13 @@ CREATE TABLE chats (
     muted BOOLEAN DEFAULT FALSE,
     invitation_admin VARCHAR,
     profile VARCHAR,
-    community_id TEXT DEFAULT ""
+    community_id TEXT DEFAULT "",
+    accepted BOOLEAN DEFAULT false,
+    joined INT DEFAULT 0,
+    synced_to INTEGER DEFAULT 0,
+    synced_from INTEGER DEFAULT 0,
+    unviewed_mentions_count INT DEFAULT 0,
+    description TEXT DEFAULT ""
 );

 CREATE TABLE contacts (
@@ -26,7 +34,6 @@ CREATE TABLE contacts (
     name TEXT NOT NULL,
     ens_verified BOOLEAN DEFAULT FALSE,
     ens_verified_at INT NOT NULL DEFAULT 0,
-    ens_verification_retries INT NOT NULL DEFAULT 0,
     alias TEXT NOT NULL,
     identicon TEXT NOT NULL,
     photo TEXT NOT NULL,
@@ -79,7 +86,12 @@ CREATE TABLE user_messages (
     audio_base64 TEXT NOT NULL DEFAULT "",
     mentions BLOB,
     links BLOB,
-    community_id TEXT DEFAULT ""
+    community_id TEXT DEFAULT "",
+    gap_from INTEGER,
+    gap_to INTEGER,
+    mentioned BOOLEAN DEFAULT FALSE,
+    edited_at INTEGER,
+    deleted BOOL DEFAULT FALSE
 );

 CREATE INDEX idx_source ON user_messages(source);
@@ -96,7 +108,10 @@ CREATE TABLE raw_messages (
     payload BLOB,
     pow_target REAL default 0.02,
     skip_encryption BOOLEAN DEFAULT FALSE,
-    send_push_notification BOOLEAN DEFAULT FALSE
+    send_push_notification BOOLEAN DEFAULT FALSE,
+    skip_group_message_wrap BOOLEAN DEFAULT FALSE,
+    send_on_personal_topic BOOLEAN DEFAULT FALSE,
+    datasync_id BLOB
 );

 CREATE TABLE messenger_transactions_to_validate (
@@ -123,7 +138,8 @@ CREATE TABLE accounts (
     name TEXT,
     color TEXT,
     created_at DATETIME NOT NULL,
-    updated_at DATETIME NOT NULL
+    updated_at DATETIME NOT NULL,
+    hidden BOOL NOT NULL DEFAULT FALSE
 ) WITHOUT ROWID;

 CREATE UNIQUE INDEX unique_wallet_address ON accounts (wallet) WHERE (wallet);
@@ -186,7 +202,9 @@ CREATE TABLE blocks_ranges (
     network_id UNSIGNED BIGINT NOT NULL,
     address VARCHAR NOT NULL,
     blk_from BIGINT NOT NULL,
-    blk_to BIGINT NOT NULL
+    blk_to BIGINT NOT NULL,
+    balance BLOB,
+    nonce INTEGER
 );

 CREATE TABLE mailservers (
@@ -281,18 +299,31 @@ CREATE TABLE settings (
     webview_allow_permission_requests BOOLEAN DEFAULT FALSE,
     use_mailservers BOOLEAN DEFAULT TRUE,
     link_preview_request_enabled BOOLEAN DEFAULT TRUE,
-    link_previews_enabled_sites BLOB
+    link_previews_enabled_sites BLOB,
+    profile_pictures_visibility INT NOT NULL DEFAULT 1,
+    anon_metrics_should_send BOOLEAN DEFAULT false,
+    messages_from_contacts_only BOOLEAN DEFAULT FALSE,
+    default_sync_period INTEGER DEFAULT 86400,
+    current_user_status BLOB,
+    send_status_updates BOOLEAN DEFAULT TRUE,
+    gif_recents BLOB,
+    gif_favorites BLOB
 ) WITHOUT ROWID;

 CREATE TABLE pending_transactions (
     network_id UNSIGNED BIGINT NOT NULL,
-    transaction_hash VARCHAR NOT NULL,
-    blk_number BIGINT NOT NULL,
+    hash VARCHAR NOT NULL,
+    timestamp UNSIGNED BIGINT NOT NULL,
     from_address VARCHAR NOT NULL,
-    to_address VARCHAR NOT NULL,
-    type VARCHAR NOT NULL,
+    to_address VARCHAR,
+    symbol VARCHAR,
+    gas_price BLOB,
+    gas_limit BLOB,
+    value BLOB,
     data TEXT,
-    PRIMARY KEY (network_id, transaction_hash)
+    type VARCHAR,
+    additional_data TEXT,
+    PRIMARY KEY (network_id, hash)
 ) WITHOUT ROWID;

 CREATE TABLE favourites (
@@ -316,34 +347,6 @@ CREATE TABLE bookmarks (
     PRIMARY KEY (url)
 ) WITHOUT ROWID;

-CREATE TABLE tx_history_info (
-    address VARCHAR NOT NULL PRIMARY KEY,
-    balance int NOT NULL,
-    tx_count NOT NULL,
-    block_number INT NOT NULL
-) WITHOUT ROWID;
-
-CREATE TABLE tx_history (
-    id VARCHAR NOT NULL PRIMARY KEY,
-    address VARCHAR,
-    tx_type VARCHAR NOT NULL,
-    block_number INT NOT NULL,
-    block_hash VARCHAR NOT NULL,
-    timestamp INT NOT NULL,
-    gas_price INT,
-    gas_limit INT,
-    gas_used INT,
-    nonce INT,
-    tx_status INT,
-    input VARCHAR,
-    tx_hash VARCHAR,
-    value INT,
-    from_addr VARCHAR,
-    to_addr VARCHAR,
-    contract VARCHAR,
-    network_id int
-) WITHOUT ROWID;
-
 CREATE INDEX idx_search_by_local_chat_id_sort_on_cursor ON user_messages (local_chat_id ASC, substr('0000000000000000000000000000000000000000000000000000000000000000' || clock_value, -64, 64) || id DESC);

 CREATE TABLE emoji_reactions (
@@ -389,5 +392,314 @@ CREATE TABLE communities_communities (
     private_key BLOB,
     description BLOB NOT NULL,
     joined BOOL NOT NULL DEFAULT FALSE,
-    verified BOOL NOT NULL DEFAULT FALSE
+    verified BOOL NOT NULL DEFAULT FALSE,
+    muted BOOL NOT NULL DEFAULT FALSE,
+    synced_at TIMESTAMP DEFAULT 0 NOT NULL
 );
+
+CREATE TABLE app_metrics (
+    event VARCHAR NOT NULL,
+    value TEXT NOT NULL,
+    app_version VARCHAR NOT NULL,
+    operating_system VARCHAR NOT NULL,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    session_id VARCHAR
+);
+
+CREATE TABLE status_updates (
+    public_key TEXT PRIMARY KEY ON CONFLICT REPLACE,
+    status_type INT NOT NULL DEFAULT 0,
+    clock INT NOT NULL,
+    custom_text TEXT DEFAULT ""
+);
+
+CREATE TABLE mvds_schema_migrations (version uint64,dirty bool);
+
+CREATE TABLE mvds_peers (
+    group_id BLOB NOT NULL,
+    peer_id BLOB NOT NULL,
+    PRIMARY KEY (group_id, peer_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE mvds_states (
+    type INTEGER NOT NULL,
+    send_count INTEGER NOT NULL,
+    send_epoch INTEGER NOT NULL,
+    group_id BLOB,
+    peer_id BLOB NOT NULL,
+    message_id BLOB NOT NULL,
+    PRIMARY KEY (message_id, peer_id)
+);
+
+CREATE TABLE mvds_epoch (
+    peer_id BLOB PRIMARY KEY,
+    epoch INTEGER NOT NULL
+);
+
+CREATE TABLE mvds_messages (
+    id BLOB PRIMARY KEY,
+    group_id BLOB NOT NULL,
+    timestamp INTEGER NOT NULL,
+    body BLOB NOT NULL
+);
+
+CREATE TABLE status_protocol_go_schema_migrations (version uint64,dirty bool);
+
+CREATE TABLE sessions (
+    dhr BLOB,
+    dhs_public BLOB,
+    dhs_private BLOB,
+    root_chain_key BLOB,
+    send_chain_key BLOB,
+    send_chain_n INTEGER,
+    recv_chain_key BLOB,
+    recv_chain_n INTEGER,
+    step INTEGER,
+    pn INTEGER,
+    id BLOB NOT NULL PRIMARY KEY,
+    keys_count INTEGER NOT NULL DEFAULT 0,
+    UNIQUE(id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE keys (
+    public_key BLOB NOT NULL,
+    msg_num INTEGER,
+    message_key BLOB NOT NULL,
+    seq_num INTEGER NOT NULL DEFAULT 0,
+    session_id BLOB,
+    UNIQUE (msg_num, message_key) ON CONFLICT REPLACE
+);
+
+CREATE TABLE bundles (
+    identity BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    private_key BLOB,
+    signed_pre_key BLOB NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
+    timestamp UNSIGNED BIG INT NOT NULL,
+    expired BOOLEAN DEFAULT 0,
+    version INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE TABLE ratchet_info_v2 (
+    bundle_id BLOB NOT NULL,
+    ephemeral_key BLOB,
+    identity BLOB NOT NULL,
+    symmetric_key BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    UNIQUE(bundle_id, identity, installation_id) ON CONFLICT REPLACE,
+    FOREIGN KEY (bundle_id) REFERENCES bundles(signed_pre_key)
+);
+
+CREATE TABLE installations  (
+    identity BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    timestamp UNSIGNED BIG INT NOT NULL,
+    enabled BOOLEAN DEFAULT 1,
+    version INTEGER DEFAULT 0,
+    UNIQUE(identity, installation_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE secrets (
+    identity BLOB NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
+    secret BLOB NOT NULL
+);
+
+CREATE TABLE secret_installation_ids (
+    id TEXT NOT NULL,
+    identity_id BLOB NOT NULL,
+    UNIQUE(id, identity_id) ON CONFLICT IGNORE,
+    FOREIGN KEY (identity_id) REFERENCES secrets(identity)
+);
+
+CREATE TABLE contact_code_config (
+    unique_constraint varchar(1) NOT NULL PRIMARY KEY DEFAULT 'X',
+    last_published INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE TABLE waku_keys (
+    chat_id TEXT PRIMARY KEY ON CONFLICT IGNORE,
+    key BLOB NOT NULL
+) WITHOUT ROWID;
+
+CREATE TABLE installation_metadata  (
+    identity BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    name TEXT NOT NULL DEFAULT '',
+    device_type TEXT NOT NULL DEFAULT '',
+    fcm_token TEXT NOT NULL DEFAULT '',
+    UNIQUE(identity, installation_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_server_registrations (
+    public_key BLOB NOT NULL,
+    installation_id VARCHAR NOT NULL,
+    version INT NOT NULL,
+    registration BLOB,
+    UNIQUE(public_key, installation_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_server_identity (
+    private_key BLOB NOT NULL,
+    synthetic_id INT NOT NULL DEFAULT 0,
+    UNIQUE(synthetic_id)
+);
+
+CREATE INDEX idx_push_notification_server_registrations_public_key ON push_notification_server_registrations(public_key);
+
+CREATE INDEX idx_push_notification_server_registrations_public_key_installation_id ON push_notification_server_registrations(public_key, installation_id);
+
+CREATE TABLE push_notification_client_servers (
+    public_key BLOB NOT NULL,
+    registered BOOLEAN DEFAULT FALSE,
+    registered_at INT NOT NULL DEFAULT 0,
+    last_retried_at INT NOT NULL DEFAULT 0,
+    retry_count INT NOT NULL DEFAULT 0,
+    access_token TEXT,
+    server_type INT DEFAULT 2,
+    UNIQUE(public_key) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_client_queries (
+    public_key BLOB NOT NULL,
+    queried_at INT NOT NULL,
+    query_id BLOB NOT NULL,
+    UNIQUE(public_key,query_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_client_info (
+    public_key BLOB NOT NULL,
+    server_public_key BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    access_token TEXT NOT NULL,
+    retrieved_at INT NOT NULL,
+    version INT NOT NULL,
+    UNIQUE(public_key, installation_id, server_public_key) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_client_tracked_messages (
+    message_id BLOB NOT NULL,
+    chat_id TEXT NOT NULL,
+    tracked_at INT NOT NULL,
+    UNIQUE(message_id) ON CONFLICT IGNORE
+);
+
+CREATE TABLE push_notification_client_sent_notifications (
+    message_id BLOB NOT NULL,
+    public_key BLOB NOT NULL,
+    hashed_public_key BLOB NOT NULL,
+    installation_id TEXT NOT NULL,
+    last_tried_at INT NOT NULL,
+    retry_count INT NOT NULL DEFAULT 0,
+    success BOOLEAN NOT NULL DEFAULT FALSE,
+    error INT NOT NULL DEFAULT 0,
+    chat_id TEXT,
+    notification_type INT,
+    UNIQUE(message_id, public_key, installation_id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE push_notification_client_registrations (
+    registration BLOB NOT NULL,
+    contact_ids BLOB,
+    synthetic_id INT NOT NULL DEFAULT 0,
+    UNIQUE(synthetic_id) ON CONFLICT REPLACE
+);
+
+CREATE INDEX idx_push_notification_client_info_public_key ON push_notification_client_info(public_key, installation_id);
+
+CREATE TABLE push_notification_server_notifications (
+    id BLOB NOT NULL,
+    UNIQUE(id)
+);
+
+CREATE TABLE transport_message_cache (
+    id VARCHAR NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
+    timestamp INT NOT NULL
+);
+
+CREATE INDEX idx_datsync_id ON raw_messages(datasync_id);
+
+CREATE TABLE communities_requests_to_join  (
+    id BLOB NOT NULL,
+    public_key VARCHAR NOT NULL,
+    clock INT NOT NULL,
+    ens_name VARCHAR NOT NULL DEFAULT "",
+    chat_id VARCHAR NOT NULL DEFAULT "",
+    community_id BLOB NOT NULL,
+    state INT NOT NULL DEFAULT 0,
+    PRIMARY KEY (id) ON CONFLICT REPLACE
+);
+
+CREATE TABLE ens_verification_records (
+    public_key VARCHAR NOT NULL,
+    name VARCHAR NOT NULL,
+    verified BOOLEAN NOT NULL DEFAULT FALSE,
+    verified_at INT NOT NULL DEFAULT 0,
+    clock INT NOT NULL DEFAULT 0,
+    verification_retries INT NOT NULL DEFAULT 0,
+    next_retry INT NOT NULL DEFAULT 0,
+    PRIMARY KEY (public_key) ON CONFLICT REPLACE
+);
+
+CREATE TABLE raw_message_confirmations (
+    datasync_id BLOB NOT NULL,
+    message_id BLOB NOT NULL,
+    public_key BLOB NOT NULL,
+    confirmed_at INT NOT NULL DEFAULT 0,
+    PRIMARY KEY (message_id, public_key) ON CONFLICT REPLACE
+);
+
+CREATE TABLE wakuv2_keys (
+    chat_id TEXT PRIMARY KEY ON CONFLICT IGNORE,
+    key BLOB NOT NULL
+) WITHOUT ROWID;
+
+CREATE TABLE activity_center_notifications (
+    id VARCHAR NOT NULL PRIMARY KEY,
+    timestamp INT NOT NULL,
+    notification_type INT NOT NULL,
+    chat_id VARCHAR,
+    read BOOLEAN NOT NULL DEFAULT FALSE,
+    dismissed BOOLEAN NOT NULL DEFAULT FALSE,
+    accepted BOOLEAN NOT NULL DEFAULT FALSE,
+    message BLOB DEFAULT NULL,
+    author TEXT,
+    reply_message BLOB DEFAULT NULL
+) WITHOUT ROWID;
+
+CREATE INDEX activity_center_dimissed_accepted ON activity_center_notifications(dismissed, accepted);
+
+CREATE INDEX activity_center_read ON activity_center_notifications(read);
+
+CREATE TABLE pin_messages (
+    id VARCHAR PRIMARY KEY NOT NULL,
+    message_id VARCHAR NOT NULL,
+    whisper_timestamp INTEGER NOT NULL,
+    chat_id VARCHAR NOT NULL,
+    local_chat_id VARCHAR NOT NULL,
+    clock_value INT NOT NULL,
+    pinned BOOLEAN NOT NULL,
+    pinned_by TEXT
+);
+
+CREATE TABLE user_messages_edits (
+    clock INTEGER NOT NULL,
+    chat_id VARCHAR NOT NULL,
+    message_id VARCHAR NOT NULL,
+    source VARCHAR NOT NULL,
+    text VARCHAR NOT NULL,
+    id VARCHAR NOT NULL,
+    PRIMARY KEY(id)
+);
+
+CREATE INDEX user_messages_edits_message_id_source ON user_messages_edits(message_id, source);
+
+CREATE TABLE user_messages_deletes (
+    clock INTEGER NOT NULL,
+    chat_id VARCHAR NOT NULL,
+    message_id VARCHAR NOT NULL,
+    source VARCHAR NOT NULL,
+    id VARCHAR NOT NULL,
+    PRIMARY KEY(id)
+);
+
+CREATE INDEX user_messages_deletes_message_id_source ON user_messages_deletes(message_id, source);