HomePhabricator
Diffusion Comm e21b2236daa7

[keyserver] Introduce columns in messages table to support pinned messages

Description

[keyserver] Introduce columns in messages table to support pinned messages

Summary:
Introduce two new columns to the messages table to handle messages being pinned.

  1. pinned - represents a boolean 0 (unpinned) or 1 (pinned)
  2. pin_time - the time the message was pinned. Necessary so we can display pins from newest to oldest.
  3. Add an index onto pinned since we'll be querying on messages where pinned = 1 when fetching all pins.

I've also added a migration to update messages.

Linear: https://linear.app/comm/issue/ENG-3182/introduce-columns-in-messages-table-to-support-pinned-messages

Test Plan:
Tested the ALTER TABLE` statements locally in TablePlus individually to confirm the columns gets created and no syntax errors are present.

ALTER TABLE messages
   ADD COLUMN IF NOT EXISTS pinned tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
   ADD COLUMN IF NOT EXISTS pin_time bigint(20) DEFAULT NULL,
   ADD INDEX IF NOT EXISTS pinned (pinned);

Also checked the table after the migration was successful:

Screenshot 2023-03-20 at 2.58.02 PM.png (198×2 px, 64 KB)

Reviewers: atul, ginsu, tomek

Reviewed By: tomek

Subscribers: ashoat

Differential Revision: https://phab.comm.dev/D6924