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

     1  -- Create new table where we will store profile social links,
     2  -- after discussion https://github.com/status-im/status-go/pull/3552#issuecomment-1573817044
     3  -- we decided to replace the entire db table content on every change (because of that position column is not needed)
     4  CREATE TABLE IF NOT EXISTS profile_social_links (
     5    text VARCHAR NOT NULL CHECK (length(trim(text)) > 0),
     6    url VARCHAR NOT NULL CHECK (length(trim(url)) > 0),
     7    position INT DEFAULT 0,
     8    PRIMARY KEY (text, url)
     9  );
    10  
    11  -- Create new column to keep clock of the last change of profile social links (since content is replaced all row share the same clock value)
    12  ALTER TABLE settings_sync_clock ADD COLUMN social_links INTEGER NOT NULL DEFAULT 0;
    13  
    14  -- Create temp table to generate a position for rows
    15  CREATE TEMPORARY TABLE temp_social_links_settings (
    16    id INTEGER PRIMARY KEY AUTOINCREMENT,
    17    text VARCHAR NOT NULL,
    18    url VARCHAR NOT NULL
    19  );
    20  
    21  -- Copy to temp table
    22  INSERT INTO temp_social_links_settings (text, url)
    23    SELECT link_text, link_url
    24    FROM social_links_settings
    25    WHERE link_url != "";
    26  
    27  -- Insert into `profile_social_links` table
    28  INSERT INTO profile_social_links
    29    SELECT text, url, id
    30    FROM temp_social_links_settings;
    31  
    32  -- From some reason the following sql doesn't work through status-go migrations, although the query is correct.
    33  -- -- Set the clock for profile social links
    34  -- UPDATE settings_sync_clock
    35  --   SET
    36  --     social_links = (SELECT clock
    37  --     FROM social_links_settings
    38  --     WHERE link_url != ""
    39  --     LIMIT 1)
    40  --   WHERE synthetic_id = "id";
    41  
    42  -- Drop temp table
    43  DROP TABLE temp_social_links_settings;
    44  
    45  -- Drop old table
    46  DROP TABLE social_links_settings;