diff --git a/keyserver/src/database/migration-config.js b/keyserver/src/database/migration-config.js index c3b4507e0..038485693 100644 --- a/keyserver/src/database/migration-config.js +++ b/keyserver/src/database/migration-config.js @@ -1,418 +1,424 @@ // @flow import fs from 'fs'; import { policyTypes } from 'lib/facts/policies.js'; import { dbQuery, SQL } from '../database/database.js'; import { processMessagesInDBForSearch } from '../database/search-utils.js'; import { updateRolesAndPermissionsForAllThreads } from '../updaters/thread-permission-updaters.js'; const migrations: $ReadOnlyMap Promise> = new Map([ [ 0, async () => { await makeSureBaseRoutePathExists('facts/commapp_url.json'); await makeSureBaseRoutePathExists('facts/squadcal_url.json'); }, ], [ 1, async () => { try { await fs.promises.unlink('facts/url.json'); } catch {} }, ], [ 2, async () => { await fixBaseRoutePathForLocalhost('facts/commapp_url.json'); await fixBaseRoutePathForLocalhost('facts/squadcal_url.json'); }, ], [3, updateRolesAndPermissionsForAllThreads], [ 4, async () => { await dbQuery(SQL`ALTER TABLE uploads ADD INDEX container (container)`); }, ], [ 5, async () => { await dbQuery(SQL` ALTER TABLE cookies ADD device_id varchar(255) DEFAULT NULL, ADD public_key varchar(255) DEFAULT NULL, ADD social_proof varchar(255) DEFAULT NULL; `); }, ], [ 7, async () => { await dbQuery( SQL` ALTER TABLE users DROP COLUMN IF EXISTS public_key, MODIFY hash char(60) COLLATE utf8mb4_bin DEFAULT NULL; ALTER TABLE sessions DROP COLUMN IF EXISTS public_key; `, { multipleStatements: true }, ); }, ], [ 8, async () => { await dbQuery( SQL` ALTER TABLE users ADD COLUMN IF NOT EXISTS ethereum_address char(42) DEFAULT NULL; `, ); }, ], [ 9, async () => { await dbQuery( SQL` ALTER TABLE messages ADD COLUMN IF NOT EXISTS target_message bigint(20) DEFAULT NULL; ALTER TABLE messages ADD INDEX target_message (target_message); `, { multipleStatements: true }, ); }, ], [ 10, async () => { await dbQuery(SQL` CREATE TABLE IF NOT EXISTS policy_acknowledgments ( user bigint(20) NOT NULL, policy varchar(255) NOT NULL, date bigint(20) NOT NULL, confirmed tinyint(1) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (user, policy) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; `); }, ], [ 11, async () => { const time = Date.now(); await dbQuery(SQL` INSERT IGNORE INTO policy_acknowledgments (policy, user, date, confirmed) SELECT ${policyTypes.tosAndPrivacyPolicy}, id, ${time}, 1 FROM users `); }, ], [ 12, async () => { await dbQuery(SQL` CREATE TABLE IF NOT EXISTS siwe_nonces ( nonce char(17) NOT NULL, creation_time bigint(20) NOT NULL, PRIMARY KEY (nonce) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; `); }, ], [ 13, async () => { await Promise.all([ writeSquadCalRoute('facts/squadcal_url.json'), moveToNonApacheConfig('facts/commapp_url.json', '/comm/'), moveToNonApacheConfig('facts/landing_url.json', '/commlanding/'), ]); }, ], [ 14, async () => { await dbQuery(SQL` ALTER TABLE cookies MODIFY COLUMN social_proof mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; `); }, ], [ 15, async () => { await dbQuery( SQL` ALTER TABLE uploads ADD COLUMN IF NOT EXISTS thread bigint(20) DEFAULT NULL, ADD INDEX IF NOT EXISTS thread (thread); UPDATE uploads SET thread = ( SELECT thread FROM messages WHERE messages.id = uploads.container ); `, { multipleStatements: true }, ); }, ], [ 16, async () => { await dbQuery( SQL` ALTER TABLE cookies DROP COLUMN IF EXISTS public_key; ALTER TABLE cookies ADD COLUMN IF NOT EXISTS signed_identity_keys mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; `, { multipleStatements: true }, ); }, ], [ 17, async () => { await dbQuery( SQL` ALTER TABLE cookies DROP INDEX device_token, DROP INDEX user_device_token; ALTER TABLE cookies MODIFY device_token mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, ADD UNIQUE KEY device_token (device_token(512)), ADD KEY user_device_token (user,device_token(512)); `, { multipleStatements: true }, ); }, ], [18, updateRolesAndPermissionsForAllThreads], [19, updateRolesAndPermissionsForAllThreads], [ 20, async () => { await dbQuery(SQL` ALTER TABLE threads ADD COLUMN IF NOT EXISTS avatar varchar(191) COLLATE utf8mb4_bin DEFAULT NULL; `); }, ], [ 21, async () => { await dbQuery(SQL` ALTER TABLE reports DROP INDEX IF EXISTS user, ADD INDEX IF NOT EXISTS user_type_platform_creation_time (user, type, platform, creation_time); `); }, ], [ 22, async () => { await dbQuery( SQL` ALTER TABLE cookies MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE entries MODIFY creator varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE focused MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE memberships MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE messages MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE notifications MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE reports MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE revisions MODIFY author varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE sessions MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE threads MODIFY creator varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE updates MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE uploads MODIFY uploader varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE users MODIFY id varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE relationships_undirected MODIFY user1 varchar(255) CHARSET latin1 COLLATE latin1_bin, MODIFY user2 varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE relationships_directed MODIFY user1 varchar(255) CHARSET latin1 COLLATE latin1_bin, MODIFY user2 varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE user_messages MODIFY recipient varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE settings MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; ALTER TABLE policy_acknowledgments MODIFY user varchar(255) CHARSET latin1 COLLATE latin1_bin; `, { multipleStatements: true }, ); }, ], [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 }, ); }, ], [26, processMessagesInDBForSearch], [ 27, async () => { await dbQuery(SQL` 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 thread_pinned (thread, pinned); `); }, ], [ 28, async () => { await dbQuery(SQL` ALTER TABLE threads ADD COLUMN IF NOT EXISTS pinned_count int UNSIGNED NOT NULL DEFAULT 0; `); }, ], [29, updateRolesAndPermissionsForAllThreads], + [ + 30, + async () => { + await dbQuery(SQL`DROP TABLE versions;`); + }, + ], ]); const newDatabaseVersion: number = Math.max(...migrations.keys()); async function writeJSONToFile(data: any, filePath: string): Promise { console.warn(`updating ${filePath} to ${JSON.stringify(data)}`); const fileHandle = await fs.promises.open(filePath, 'w'); await fileHandle.writeFile(JSON.stringify(data, null, ' '), 'utf8'); await fileHandle.close(); } async function makeSureBaseRoutePathExists(filePath: string): Promise { let readFile, json; try { readFile = await fs.promises.open(filePath, 'r'); const contents = await readFile.readFile('utf8'); json = JSON.parse(contents); } catch { return; } finally { if (readFile) { await readFile.close(); } } if (json.baseRoutePath) { return; } let baseRoutePath; if (json.baseDomain === 'http://localhost') { baseRoutePath = json.basePath; } else if (filePath.endsWith('commapp_url.json')) { baseRoutePath = '/commweb/'; } else { baseRoutePath = '/'; } const newJSON = { ...json, baseRoutePath }; console.warn(`updating ${filePath} to ${JSON.stringify(newJSON)}`); await writeJSONToFile(newJSON, filePath); } async function fixBaseRoutePathForLocalhost(filePath: string): Promise { let readFile, json; try { readFile = await fs.promises.open(filePath, 'r'); const contents = await readFile.readFile('utf8'); json = JSON.parse(contents); } catch { return; } finally { if (readFile) { await readFile.close(); } } if (json.baseDomain !== 'http://localhost') { return; } const baseRoutePath = '/'; json = { ...json, baseRoutePath }; console.warn(`updating ${filePath} to ${JSON.stringify(json)}`); await writeJSONToFile(json, filePath); } async function moveToNonApacheConfig( filePath: string, routePath: string, ): Promise { if (process.env.COMM_DATABASE_HOST) { return; } // Since the non-Apache config is so opinionated, just write expected config const newJSON = { baseDomain: 'http://localhost:3000', basePath: routePath, baseRoutePath: routePath, https: false, proxy: 'none', }; await writeJSONToFile(newJSON, filePath); } async function writeSquadCalRoute(filePath: string): Promise { if (process.env.COMM_DATABASE_HOST) { return; } // Since the non-Apache config is so opinionated, just write expected config const newJSON = { baseDomain: 'http://localhost:3000', basePath: '/comm/', baseRoutePath: '/', https: false, proxy: 'apache', }; await writeJSONToFile(newJSON, filePath); } export { migrations, newDatabaseVersion }; diff --git a/keyserver/src/database/setup-db.js b/keyserver/src/database/setup-db.js index 111b3ddd4..a48ffe15f 100644 --- a/keyserver/src/database/setup-db.js +++ b/keyserver/src/database/setup-db.js @@ -1,447 +1,435 @@ // @flow import ashoat from 'lib/facts/ashoat.js'; import bots from 'lib/facts/bots.js'; import genesis from 'lib/facts/genesis.js'; import { usernameMaxLength } from 'lib/shared/account-utils.js'; import { sortIDs } from 'lib/shared/relationship-utils.js'; import { undirectedStatus } from 'lib/types/relationship-types.js'; import { threadTypes } from 'lib/types/thread-types.js'; import { createThread } from '../creators/thread-creator.js'; import { dbQuery, SQL } from '../database/database.js'; import { updateDBVersion } from '../database/db-version.js'; import { newDatabaseVersion } from '../database/migration-config.js'; import { createScriptViewer } from '../session/scripts.js'; async function setupDB() { await createTables(); await createUsers(); await createThreads(); await setUpMetadataTable(); } async function createTables() { await dbQuery( SQL` CREATE TABLE cookies ( id bigint(20) NOT NULL, hash char(60) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL, platform varchar(255) DEFAULT NULL, creation_time bigint(20) NOT NULL, last_used bigint(20) NOT NULL, device_token mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, versions json DEFAULT NULL, device_id varchar(255) DEFAULT NULL, signed_identity_keys mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, social_proof mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, \`primary\` TINYINT(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE days ( id bigint(20) NOT NULL, date date NOT NULL, thread bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE entries ( id bigint(20) NOT NULL, day bigint(20) NOT NULL, text mediumtext COLLATE utf8mb4_bin NOT NULL, creator varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, creation_time bigint(20) NOT NULL, last_update bigint(20) NOT NULL, deleted tinyint(1) UNSIGNED NOT NULL, creation varchar(255) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE focused ( user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, session bigint(20) NOT NULL, thread bigint(20) NOT NULL, time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ids ( id bigint(20) NOT NULL, table_name varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE memberships ( thread bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, role bigint(20) NOT NULL, permissions json DEFAULT NULL, permissions_for_children json DEFAULT NULL, creation_time bigint(20) NOT NULL, subscription json NOT NULL, last_message bigint(20) NOT NULL DEFAULT 0, last_read_message bigint(20) NOT NULL DEFAULT 0, sender tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE messages ( id bigint(20) NOT NULL, thread bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, type tinyint(3) UNSIGNED NOT NULL, content mediumtext COLLATE utf8mb4_bin, time bigint(20) NOT NULL, creation varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, target_message bigint(20) DEFAULT NULL, pinned tinyint(1) UNSIGNED NOT NULL DEFAULT 0, pin_time bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE notifications ( id bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, thread bigint(20) DEFAULT NULL, message bigint(20) DEFAULT NULL, collapse_key varchar(255) DEFAULT NULL, delivery json NOT NULL, rescinded tinyint(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE reports ( id bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, type tinyint(3) UNSIGNED NOT NULL, platform varchar(255) NOT NULL, report json NOT NULL, creation_time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE revisions ( id bigint(20) NOT NULL, entry bigint(20) NOT NULL, author varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, text mediumtext COLLATE utf8mb4_bin NOT NULL, creation_time bigint(20) NOT NULL, session bigint(20) NOT NULL, last_update bigint(20) NOT NULL, deleted tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE roles ( id bigint(20) NOT NULL, thread bigint(20) NOT NULL, name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, permissions json NOT NULL, creation_time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE sessions ( id bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, cookie bigint(20) NOT NULL, query json NOT NULL, creation_time bigint(20) NOT NULL, last_update bigint(20) NOT NULL, last_validated bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE threads ( id bigint(20) NOT NULL, type tinyint(3) NOT NULL, name varchar(191) COLLATE utf8mb4_bin DEFAULT NULL, description mediumtext COLLATE utf8mb4_bin, parent_thread_id bigint(20) DEFAULT NULL, containing_thread_id bigint(20) DEFAULT NULL, community bigint(20) DEFAULT NULL, depth int UNSIGNED NOT NULL DEFAULT 0, default_role bigint(20) NOT NULL, creator varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, creation_time bigint(20) NOT NULL, color char(6) COLLATE utf8mb4_bin NOT NULL, source_message bigint(20) DEFAULT NULL UNIQUE, replies_count int UNSIGNED NOT NULL DEFAULT 0, avatar varchar(191) COLLATE utf8mb4_bin DEFAULT NULL, pinned_count int UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE updates ( id bigint(20) NOT NULL, user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, type tinyint(3) UNSIGNED NOT NULL, \`key\` bigint(20) DEFAULT NULL, updater bigint(20) DEFAULT NULL, target bigint(20) DEFAULT NULL, content mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE uploads ( id bigint(20) NOT NULL, thread bigint(20) DEFAULT NULL, uploader varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, container bigint(20) DEFAULT NULL, type varchar(255) NOT NULL, filename varchar(255) NOT NULL, mime varchar(255) NOT NULL, content longblob NOT NULL, secret varchar(255) NOT NULL, creation_time bigint(20) NOT NULL, extra json DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE users ( id varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, username varchar(${usernameMaxLength}) COLLATE utf8mb4_bin NOT NULL, hash char(60) COLLATE utf8mb4_bin DEFAULT NULL, avatar varchar(191) COLLATE utf8mb4_bin DEFAULT NULL, ethereum_address char(42) DEFAULT NULL, creation_time bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE relationships_undirected ( user1 varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, user2 varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, status tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE relationships_directed ( user1 varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, user2 varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, status tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; - CREATE TABLE versions ( - id bigint(20) NOT NULL, - code_version int(11) NOT NULL, - platform varchar(255) NOT NULL, - creation_time bigint(20) NOT NULL, - deploy_time bigint(20) DEFAULT NULL - ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - CREATE TABLE one_time_keys ( session bigint(20) NOT NULL, one_time_key char(43) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE user_messages ( recipient varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, thread bigint(20) NOT NULL, message bigint(20) NOT NULL, time bigint(20) NOT NULL, data mediumtext COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE settings ( user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, name varchar(255) NOT NULL, data mediumtext COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE metadata ( name varchar(255) NOT NULL, data varchar(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE policy_acknowledgments ( user varchar(255) CHARSET latin1 COLLATE latin1_bin NOT NULL, policy varchar(255) NOT NULL, date bigint(20) NOT NULL, confirmed tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE siwe_nonces ( nonce char(17) NOT NULL, 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)), ADD KEY user_device_token (user,device_token(512)); ALTER TABLE days ADD PRIMARY KEY (id), ADD UNIQUE KEY date_thread (date,thread) USING BTREE; ALTER TABLE entries ADD PRIMARY KEY (id), ADD UNIQUE KEY creator_creation (creator,creation), ADD KEY day (day); ALTER TABLE focused ADD UNIQUE KEY user_cookie_thread (user,session,thread), ADD KEY thread_user (thread,user); ALTER TABLE ids ADD PRIMARY KEY (id); ALTER TABLE memberships ADD UNIQUE KEY thread_user (thread,user) USING BTREE, ADD KEY role (role) USING BTREE; ALTER TABLE memberships ADD INDEX user (user); ALTER TABLE messages ADD PRIMARY KEY (id), ADD UNIQUE KEY user_creation (user,creation), ADD KEY thread (thread), ADD INDEX target_message (target_message), ADD INDEX thread_pinned (thread, pinned); ALTER TABLE notifications ADD PRIMARY KEY (id), ADD KEY rescinded_user_collapse_key (rescinded,user,collapse_key) USING BTREE, ADD KEY thread (thread), ADD KEY rescinded_user_thread_message (rescinded,user,thread,message) USING BTREE; ALTER TABLE notifications ADD INDEX user (user); ALTER TABLE reports ADD PRIMARY KEY (id), ADD INDEX user_type_platform_creation_time (user, type, platform, creation_time); ALTER TABLE revisions ADD PRIMARY KEY (id), ADD KEY entry (entry); ALTER TABLE roles ADD PRIMARY KEY (id), ADD KEY thread (thread); ALTER TABLE sessions ADD PRIMARY KEY (id), ADD KEY user (user); ALTER TABLE threads ADD PRIMARY KEY (id), ADD INDEX parent_thread_id (parent_thread_id), ADD INDEX containing_thread_id (containing_thread_id), ADD INDEX community (community); ALTER TABLE updates ADD PRIMARY KEY (id), ADD INDEX user_time (user,time), ADD INDEX target_time (target, time), ADD INDEX user_key_target_type_time (user, \`key\`, target, type, time), ADD INDEX user_key_type_time (user, \`key\`, type, time), ADD INDEX user_key_time (user, \`key\`, time); ALTER TABLE uploads ADD PRIMARY KEY (id), ADD INDEX container (container), ADD INDEX thread (thread); ALTER TABLE users ADD PRIMARY KEY (id), ADD UNIQUE KEY username (username); ALTER TABLE relationships_undirected ADD UNIQUE KEY user1_user2 (user1,user2), ADD UNIQUE KEY user2_user1 (user2,user1); ALTER TABLE relationships_directed ADD UNIQUE KEY user1_user2 (user1,user2), ADD UNIQUE KEY user2_user1 (user2,user1); - ALTER TABLE versions - ADD PRIMARY KEY (id), - ADD UNIQUE KEY code_version_platform (code_version,platform); - ALTER TABLE one_time_keys ADD PRIMARY KEY (session, one_time_key); ALTER TABLE user_messages ADD INDEX recipient_time (recipient, time), ADD INDEX recipient_thread_time (recipient, thread, time), ADD INDEX thread (thread), ADD PRIMARY KEY (recipient, message); ALTER TABLE ids MODIFY id bigint(20) NOT NULL AUTO_INCREMENT; ALTER TABLE settings ADD PRIMARY KEY (user, name); ALTER TABLE metadata ADD PRIMARY KEY (name); ALTER TABLE policy_acknowledgments ADD PRIMARY KEY (user, policy); 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 }, ); } async function createUsers() { const [user1, user2] = sortIDs(bots.commbot.userID, ashoat.id); await dbQuery( SQL` INSERT INTO ids (id, table_name) VALUES (${bots.commbot.userID}, 'users'), (${ashoat.id}, 'users'); INSERT INTO users (id, username, hash, avatar, creation_time) VALUES (${bots.commbot.userID}, 'commbot', '', NULL, 1530049900980), (${ashoat.id}, 'ashoat', '', NULL, 1463588881886); INSERT INTO relationships_undirected (user1, user2, status) VALUES (${user1}, ${user2}, ${undirectedStatus.KNOW_OF}); `, { multipleStatements: true }, ); } const createThreadOptions = { forceAddMembers: true }; async function createThreads() { const insertIDsPromise = dbQuery(SQL` INSERT INTO ids (id, table_name) VALUES (${genesis.id}, 'threads'), (${bots.commbot.staffThreadID}, 'threads') `); const ashoatViewer = createScriptViewer(ashoat.id); const createGenesisPromise = createThread( ashoatViewer, { id: genesis.id, type: threadTypes.GENESIS, name: genesis.name, description: genesis.description, initialMemberIDs: [bots.commbot.userID], }, createThreadOptions, ); await Promise.all([insertIDsPromise, createGenesisPromise]); const commbotViewer = createScriptViewer(bots.commbot.userID); await createThread( commbotViewer, { id: bots.commbot.staffThreadID, type: threadTypes.COMMUNITY_SECRET_SUBTHREAD, initialMemberIDs: [ashoat.id], }, createThreadOptions, ); } async function setUpMetadataTable() { await updateDBVersion(newDatabaseVersion); } export { setupDB }; diff --git a/keyserver/src/keyserver.js b/keyserver/src/keyserver.js index b44f6938a..f9962fa34 100644 --- a/keyserver/src/keyserver.js +++ b/keyserver/src/keyserver.js @@ -1,188 +1,174 @@ // @flow import olm from '@commapp/olm'; import cluster from 'cluster'; import cookieParser from 'cookie-parser'; import express from 'express'; import expressWs from 'express-ws'; import os from 'os'; import './cron/cron.js'; import { migrate } from './database/migrations.js'; import { jsonEndpoints } from './endpoints.js'; import { emailSubscriptionResponder } from './responders/comm-landing-responders.js'; import { jsonHandler, - httpGetHandler, downloadHandler, htmlHandler, uploadHandler, } from './responders/handlers.js'; import landingHandler from './responders/landing-handler.js'; import { errorReportDownloadResponder } from './responders/report-responders.js'; -import { - createNewVersionResponder, - markVersionDeployedResponder, -} from './responders/version-responders.js'; import { inviteResponder, websiteResponder, } from './responders/website-responders.js'; import { webWorkerResponder } from './responders/webworker-responders.js'; import { onConnection } from './socket/socket.js'; import { multerProcessor, multimediaUploadResponder, uploadDownloadResponder, } from './uploads/uploads.js'; import { initENSCache } from './utils/ens-cache.js'; import { prefetchAllURLFacts, getSquadCalURLFacts, getLandingURLFacts, getCommAppURLFacts, } from './utils/urls.js'; (async () => { await Promise.all([olm.init(), prefetchAllURLFacts(), initENSCache()]); const squadCalBaseRoutePath = getSquadCalURLFacts()?.baseRoutePath; const landingBaseRoutePath = getLandingURLFacts()?.baseRoutePath; const commAppBaseRoutePath = getCommAppURLFacts()?.baseRoutePath; const compiledFolderOptions = process.env.NODE_ENV === 'development' ? undefined : { maxAge: '1y', immutable: true }; if (cluster.isMaster) { const didMigrationsSucceed: boolean = await migrate(); if (!didMigrationsSucceed) { // The following line uses exit code 2 to ensure nodemon exits // in a dev environment, instead of restarting. Context provided // in https://github.com/remy/nodemon/issues/751 process.exit(2); } const cpuCount = os.cpus().length; for (let i = 0; i < cpuCount; i++) { cluster.fork(); } cluster.on('exit', () => cluster.fork()); } else { const server = express(); expressWs(server); server.use(express.json({ limit: '250mb' })); server.use(cookieParser()); const setupAppRouter = router => { router.use('/images', express.static('images')); router.use('/fonts', express.static('fonts')); router.use('/misc', express.static('misc')); router.use( '/.well-known', express.static( '.well-known', // Necessary for apple-app-site-association file { setHeaders: res => res.setHeader('Content-Type', 'application/json'), }, ), ); router.use( '/compiled', express.static('app_compiled', compiledFolderOptions), ); router.use('/', express.static('icons')); for (const endpoint in jsonEndpoints) { // $FlowFixMe Flow thinks endpoint is string const responder = jsonEndpoints[endpoint]; const expectCookieInvalidation = endpoint === 'log_out'; router.post( `/${endpoint}`, jsonHandler(responder, expectCookieInvalidation), ); } - router.get( - '/create_version/:deviceType/:codeVersion', - httpGetHandler(createNewVersionResponder), - ); - router.get( - '/mark_version_deployed/:deviceType/:codeVersion', - httpGetHandler(markVersionDeployedResponder), - ); - router.get( '/download_error_report/:reportID', downloadHandler(errorReportDownloadResponder), ); router.get( '/upload/:uploadID/:secret', downloadHandler(uploadDownloadResponder), ); // This endpoint should be handled by the mobile app. If the server // receives this request, it means that the app is not installed and we // should redirect the user to a place from which the app can be // downloaded. router.get('/invite/:secret', inviteResponder); // $FlowFixMe express-ws has side effects that can't be typed router.ws('/ws', onConnection); router.get('/worker/:worker', webWorkerResponder); router.get('*', htmlHandler(websiteResponder)); router.post( '/upload_multimedia', multerProcessor, uploadHandler(multimediaUploadResponder), ); }; // Note - the order of router declarations matters. On prod we have // squadCalBaseRoutePath configured to '/', which means it's a catch-all. If // we call server.use on squadCalRouter first, it will catch all requests // and prevent commAppRouter and landingRouter from working correctly. So we // make sure that squadCalRouter goes last if (landingBaseRoutePath) { const landingRouter = express.Router(); landingRouter.use('/images', express.static('images')); landingRouter.use('/fonts', express.static('fonts')); landingRouter.use( '/compiled', express.static('landing_compiled', compiledFolderOptions), ); landingRouter.use('/', express.static('landing_icons')); landingRouter.post('/subscribe_email', emailSubscriptionResponder); landingRouter.get('*', landingHandler); server.use(landingBaseRoutePath, landingRouter); } if (commAppBaseRoutePath) { const commAppRouter = express.Router(); setupAppRouter(commAppRouter); server.use(commAppBaseRoutePath, commAppRouter); } if (squadCalBaseRoutePath) { const squadCalRouter = express.Router(); setupAppRouter(squadCalRouter); server.use(squadCalBaseRoutePath, squadCalRouter); } const listenAddress = (() => { if (process.env.COMM_LISTEN_ADDR) { return process.env.COMM_LISTEN_ADDR; } else if (process.env.NODE_ENV === 'development') { return undefined; } else { return 'localhost'; } })(); server.listen(parseInt(process.env.PORT, 10) || 3000, listenAddress); } })(); diff --git a/keyserver/src/responders/version-responders.js b/keyserver/src/responders/version-responders.js deleted file mode 100644 index 8190660c7..000000000 --- a/keyserver/src/responders/version-responders.js +++ /dev/null @@ -1,78 +0,0 @@ -// @flow - -import type { $Response, $Request } from 'express'; -import t from 'tcomb'; - -import { isStaff } from 'lib/shared/user-utils.js'; -import type { CreateNewVersionsRequest } from 'lib/types/version-types.js'; -import { ServerError } from 'lib/utils/errors.js'; -import { tShape, tDeviceType } from 'lib/utils/validation-utils.js'; - -import createIDs from '../creators/id-creator.js'; -import { dbQuery, SQL } from '../database/database.js'; -import type { Viewer } from '../session/viewer.js'; -import { validateInput } from '../utils/validation-utils.js'; - -const createNewVersionInputValidator = tShape({ - codeVersion: t.Number, - deviceType: tDeviceType, -}); - -async function createNewVersionResponder( - viewer: Viewer, - req: $Request, - res: $Response, -): Promise { - if (!viewer.loggedIn || !isStaff(viewer.userID)) { - throw new ServerError('invalid_credentials'); - } - - const request: CreateNewVersionsRequest = ({ - codeVersion: parseInt(req.params.codeVersion), - deviceType: req.params.deviceType, - }: any); - await validateInput(viewer, createNewVersionInputValidator, request); - - const [id] = await createIDs('versions', 1); - const row = [id, request.codeVersion, request.deviceType, Date.now()]; - const insertQuery = SQL` - INSERT INTO versions (id, code_version, platform, creation_time) - VALUES ${[row]} - `; - try { - await dbQuery(insertQuery); - res.json({ success: true }); - } catch { - await dbQuery(SQL`DELETE FROM ids WHERE id = ${id}`); - res.json({ success: false }); - } -} - -async function markVersionDeployedResponder( - viewer: Viewer, - req: $Request, - res: $Response, -): Promise { - if (!viewer.loggedIn || !isStaff(viewer.userID)) { - throw new ServerError('invalid_credentials'); - } - - const request: CreateNewVersionsRequest = ({ - codeVersion: parseInt(req.params.codeVersion), - deviceType: req.params.deviceType, - }: any); - await validateInput(viewer, createNewVersionInputValidator, request); - - const updateQuery = SQL` - UPDATE versions - SET deploy_time = ${Date.now()} - WHERE code_version = ${request.codeVersion} - AND platform = ${request.deviceType} - `; - const [results] = await dbQuery(updateQuery); - - const success = !!(results.affectedRows && results.affectedRows > 0); - res.json({ success }); -} - -export { createNewVersionResponder, markVersionDeployedResponder }; diff --git a/lib/types/version-types.js b/lib/types/version-types.js deleted file mode 100644 index 02bb9ccc2..000000000 --- a/lib/types/version-types.js +++ /dev/null @@ -1,8 +0,0 @@ -// @flow - -import type { DeviceType } from './device-types.js'; - -export type CreateNewVersionsRequest = { - +codeVersion: number, - +deviceType: DeviceType, -};