Page MenuHomePhabricator
Differential D7076 Diff 23837 keyserver/src/database/setup-db.js

Changeset View

Standalone View

View Options

keyserver/src/database/setup-db.js

Show First 20 Lines • Show All 241 Lines • ▼ Show 20 Lines SQL`
confirmed tinyint(1) UNSIGNED NOT NULL DEFAULT 0 confirmed tinyint(1) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE siwe_nonces ( CREATE TABLE siwe_nonces (
nonce char(17) NOT NULL, nonce char(17) NOT NULL,
creation_time bigint(20) NOT NULL creation_time bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE search (
ashoatUnsubmitted
Not Done
Inline Actions

Can we name this message_search, since the columns seem specific to messages?

ashoat: Can we name this `message_search`, since the columns seem specific to messages?
inkaAuthorUnsubmitted
Done
Inline Actions

When I talked to @tomek about this task initially, we thought that in the future we might want to use this for searching over calendar entries as well. I was referring to that in my comment on this diff

I'm wondering if I shouldn't change "message" to some other word, since we might want to include calendar entries in the future. But the word "entry" already has a special meaning in our code, and I don't have good ideas how to call a super class of both message and entry

and in the summary

For now we will be only keeping here processed text messages, but we might extend this to ex calendar entries in the future.

I can change it either way, but we should decide whether we possibly want to use this table for calendar entires in the future. The benefit of using the same table for messages and entries is that if we fetch them by for example time, we will get them sorted correctly right away, and won't have to think about how many of each we should fetch

inka: When I talked to @tomek about this task initially, we thought that in the future we might want…
original_message_id bigint(20) NOT NULL,
message_id bigint(20) NOT NULL,
processed_content mediumtext COLLATE utf8mb4_bin,
FULLTEXT(processed_content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ashoatUnsubmitted
Not Done
Inline Actions

For the messages table, we set COLLATE=utf8mb4_bin here as well. Can we add that here? It seems like a good idea in case we ever add another column but forget to set the COLLATE mode

ashoat: For the `messages` table, we set `COLLATE=utf8mb4_bin` here as well. Can we add that here? It…
ALTER TABLE cookies ALTER TABLE cookies
ADD PRIMARY KEY (id), ADD PRIMARY KEY (id),
ADD UNIQUE KEY device_token (device_token(512)), ADD UNIQUE KEY device_token (device_token(512)),
ADD KEY user_device_token (user,device_token(512)); ADD KEY user_device_token (user,device_token(512));
ALTER TABLE days ALTER TABLE days
ADD PRIMARY KEY (id), ADD PRIMARY KEY (id),
ADD UNIQUE KEY date_thread (date,thread) USING BTREE; ADD UNIQUE KEY date_thread (date,thread) USING BTREE;
▲ Show 20 Lines • Show All 100 Lines • ▼ Show 20 Lines SQL`
ALTER TABLE metadata ALTER TABLE metadata
ADD PRIMARY KEY (name); ADD PRIMARY KEY (name);
ALTER TABLE policy_acknowledgments ALTER TABLE policy_acknowledgments
ADD PRIMARY KEY (user, policy); ADD PRIMARY KEY (user, policy);
ALTER TABLE siwe_nonces ALTER TABLE siwe_nonces
ADD PRIMARY KEY (nonce); ADD PRIMARY KEY (nonce);
ALTER TABLE search
ADD PRIMARY KEY (original_message_id);
`, `,
{ multipleStatements: true }, { multipleStatements: true },
); );
} }
async function createUsers() { async function createUsers() {
const [user1, user2] = sortIDs(bots.commbot.userID, ashoat.id); const [user1, user2] = sortIDs(bots.commbot.userID, ashoat.id);
await dbQuery( await dbQuery(
▲ Show 20 Lines • Show All 57 Lines • Show Last 20 Lines