diff --git a/keyserver/src/database/migration-config.js b/keyserver/src/database/migration-config.js --- a/keyserver/src/database/migration-config.js +++ b/keyserver/src/database/migration-config.js @@ -277,6 +277,25 @@ ], [23, updateRolesAndPermissionsForAllThreads], [24, updateRolesAndPermissionsForAllThreads], + [ + 25, + async () => { + await dbQuery( + SQL` + CREATE TABLE IF NOT EXISTS message_search ( + original_message_id bigint(20) NOT NULL, + message_id bigint(20) NOT NULL, + processed_content mediumtext COLLATE utf8mb4_bin + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + + ALTER TABLE message_search + ADD PRIMARY KEY (original_message_id), + ADD FULLTEXT INDEX processed_content (processed_content); + `, + { multipleStatements: true }, + ); + }, + ], ]); const newDatabaseVersion: number = Math.max(...migrations.keys()); diff --git a/keyserver/src/database/setup-db.js b/keyserver/src/database/setup-db.js --- a/keyserver/src/database/setup-db.js +++ b/keyserver/src/database/setup-db.js @@ -248,6 +248,12 @@ creation_time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CREATE TABLE message_search ( + original_message_id bigint(20) NOT NULL, + message_id bigint(20) NOT NULL, + processed_content mediumtext COLLATE utf8mb4_bin + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + ALTER TABLE cookies ADD PRIMARY KEY (id), ADD UNIQUE KEY device_token (device_token(512)), @@ -366,6 +372,10 @@ ALTER TABLE siwe_nonces ADD PRIMARY KEY (nonce); + + ALTER TABLE message_search + ADD PRIMARY KEY (original_message_id), + ADD FULLTEXT INDEX processed_content (processed_content); `, { multipleStatements: true }, );