No OneTemporary
Actions

Size

44 KB

Referenced Files

None

Subscribers

None

View Options

	diff --git a/keyserver/src/database/migration-config.js b/keyserver/src/database/migration-config.js
	index 62331a560..f0953111c 100644
	--- a/keyserver/src/database/migration-config.js
	+++ b/keyserver/src/database/migration-config.js
	@@ -1,920 +1,940 @@
	// @flow

	import fs from 'fs';

	import bots from 'lib/facts/bots.js';
	import genesis from 'lib/facts/genesis.js';
	import { policyTypes } from 'lib/facts/policies.js';
	import { specialRoles } from 'lib/permissions/special-roles.js';
	import { messageTypes } from 'lib/types/message-types-enum.js';
	import { threadPermissions } from 'lib/types/thread-permission-types.js';
	import { threadTypes } from 'lib/types/thread-types-enum.js';
	import { permissionsToRemoveInMigration } from 'lib/utils/migration-utils.js';

	import { dbQuery, SQL } from '../database/database.js';
	import { processMessagesInDBForSearch } from '../database/search-utils.js';
	import { deleteThread } from '../deleters/thread-deleters.js';
	import { createScriptViewer } from '../session/scripts.js';
	import { fetchOlmAccount } from '../updaters/olm-account-updater.js';
	import { updateRolesAndPermissionsForAllThreads } from '../updaters/thread-permission-updaters.js';
	import { updateThread } from '../updaters/thread-updaters.js';
	import { ensureUserCredentials } from '../user/checks.js';
	import {
	createPickledOlmAccount,
	publishPrekeysToIdentity,
	} from '../utils/olm-utils.js';
	import type { PickledOlmAccount } from '../utils/olm-utils.js';
	import { synchronizeInviteLinksWithBlobs } from '../utils/synchronize-invite-links-with-blobs.js';

	const botViewer = createScriptViewer(bots.commbot.userID);

	const migrations: $ReadOnlyMap<number, () => Promise<mixed>> = 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;`);
	},
	],
	[
	31,
	async () => {
	await dbQuery(
	SQL`
	CREATE TABLE IF NOT EXISTS invite_links (
	id bigint(20) NOT NULL,
	name varchar(255) CHARSET latin1 NOT NULL,
	\`primary\` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
	role bigint(20) NOT NULL,
	community bigint(20) NOT NULL,
	expiration_time bigint(20),
	limit_of_uses int UNSIGNED,
	number_of_uses int UNSIGNED NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	ALTER TABLE invite_links
	ADD PRIMARY KEY (id),
	ADD UNIQUE KEY (name),
	ADD INDEX community_primary (community, \`primary\`);
	`,
	{ multipleStatements: true },
	);
	},
	],
	[
	32,
	async () => {
	await dbQuery(SQL`
	UPDATE messages
	SET target_message = JSON_VALUE(content, "$.sourceMessageID")
	WHERE type = ${messageTypes.SIDEBAR_SOURCE};
	`);
	},
	],
	[
	33,
	async () => {
	await dbQuery(
	SQL`
	CREATE TABLE IF NOT EXISTS olm_sessions (
	cookie_id bigint(20) NOT NULL,
	is_content tinyint(1) NOT NULL,
	version bigint(20) NOT NULL,
	pickled_olm_session text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

	ALTER TABLE olm_sessions
	ADD PRIMARY KEY (cookie_id, is_content);
	`,
	{ multipleStatements: true },
	);
	},
	],
	[
	34,
	async () => {
	await dbQuery(
	SQL`
	CREATE TABLE IF NOT EXISTS olm_accounts (
	is_content tinyint(1) NOT NULL,
	version bigint(20) NOT NULL,
	pickling_key text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL,
	pickled_olm_account text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

	ALTER TABLE olm_accounts
	ADD PRIMARY KEY (is_content);
	`,
	{ multipleStatements: true },
	);
	},
	],
	[
	35,
	async () => {
	await createOlmAccounts();
	},
	],
	[36, updateRolesAndPermissionsForAllThreads],
	[
	37,
	async () => {
	await dbQuery(
	SQL`
	DELETE FROM olm_accounts;
	DELETE FROM olm_sessions;
	`,
	{ multipleStatements: true },
	);
	await createOlmAccounts();
	},
	],
	[
	38,
	async () => {
	const [result] = await dbQuery(SQL`
	SELECT t.id
	FROM threads t
	INNER JOIN memberships m ON m.thread = t.id AND m.role > 0
	INNER JOIN users u ON u.id = m.user
	WHERE t.type = ${threadTypes.PRIVATE}
	AND t.name = u.ethereum_address
	`);
	const threadIDs = result.map(({ id }) => id.toString());
	while (threadIDs.length > 0) {
	// Batch 10 updateThread calls at a time
	const batch = threadIDs.splice(0, 10);
	await Promise.all(
	batch.map(threadID =>
	updateThread(
	botViewer,
	{
	threadID,
	changes: {
	name: '',
	},
	},
	{
	silenceMessages: true,
	ignorePermissions: true,
	},
	),
	),
	);
	}
	},
	],
	[39, ensureUserCredentials],
	[
	40,
	// Tokens from identity service are 512 characters long
	() =>
	dbQuery(
	SQL`
	ALTER TABLE metadata
	MODIFY COLUMN data VARCHAR(1023)
	`,
	),
	],
	[
	41,
	() =>
	dbQuery(
	SQL`
	ALTER TABLE memberships
	DROP INDEX user,
	ADD KEY user_role_thread (user, role, thread)
	`,
	),
	],
	[
	42,
	async () => {
	await dbQuery(SQL`
	ALTER TABLE roles
	ADD UNIQUE KEY thread_name (thread, name);
	`);
	},
	],
	[
	43,
	() =>
	dbQuery(
	SQL`
	UPDATE threads
	SET pinned_count = (
	SELECT COUNT(*)
	FROM messages
	WHERE messages.thread = threads.id
	AND messages.pinned = 1
	)
	`,
	),
	],
	[
	44,
	async () => {
	const { SIDEBAR_SOURCE, TOGGLE_PIN } = messageTypes;
	const [result] = await dbQuery(SQL`
	SELECT m1.thread
	FROM messages m1
	LEFT JOIN messages m2
	ON m2.id = m1.target_message
	WHERE m1.type = ${SIDEBAR_SOURCE} AND m2.type = ${TOGGLE_PIN}
	`);

	const threadIDs = new Set<string>();
	for (const row of result) {
	threadIDs.add(row.thread.toString());
	}

	await Promise.all(
	[...threadIDs].map(threadID =>
	deleteThread(botViewer, { threadID }, { ignorePermissions: true }),
	),
	);
	},
	],
	[
	45,
	() =>
	dbQuery(
	SQL`
	ALTER TABLE uploads
	CHARSET utf8mb4 COLLATE utf8mb4_bin,
	MODIFY COLUMN type varchar(255)
	CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL,
	MODIFY COLUMN filename varchar(255)
	CHARSET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	MODIFY COLUMN mime varchar(255)
	CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL,
	MODIFY COLUMN secret varchar(255)
	CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL;
	`,
	),
	],
	[
	46,
	async () => {
	try {
	const [content, notif] = await Promise.all([
	fetchOlmAccount('content'),
	fetchOlmAccount('notifications'),
	]);
	await publishPrekeysToIdentity(content.account, notif.account);
	} catch (e) {
	console.warn('Encountered error while trying to publish prekeys', e);
	if (process.env.NODE_ENV !== 'development') {
	throw e;
	}
	}
	},
	],
	[
	47,
	() =>
	dbQuery(SQL`ALTER TABLE cookies MODIFY COLUMN hash char(64) NOT NULL`),
	],
	[
	48,
	async () => {
	const visibleExtractString = `$.${threadPermissions.VISIBLE}.value`;
	const query = SQL`
	UPDATE memberships mm
	LEFT JOIN (
	SELECT m.thread, MAX(m.id) AS message FROM messages m
	WHERE m.type != ${messageTypes.CREATE_SUB_THREAD}
	AND m.thread = ${genesis().id}
	GROUP BY m.thread
	) all_users_query ON mm.thread = all_users_query.thread
	LEFT JOIN (
	SELECT m.thread, stm.user, MAX(m.id) AS message FROM messages m
	LEFT JOIN memberships stm ON m.type = ${
	messageTypes.CREATE_SUB_THREAD
	}
	AND stm.thread = m.content
	WHERE JSON_EXTRACT(stm.permissions, ${visibleExtractString}) IS TRUE
	AND m.thread = ${genesis().id}
	GROUP BY m.thread, stm.user
	) last_subthread_message_for_user_query
	ON mm.thread = last_subthread_message_for_user_query.thread
	AND mm.user = last_subthread_message_for_user_query.user
	SET
	mm.last_message = GREATEST(COALESCE(all_users_query.message, 0),
	COALESCE(last_subthread_message_for_user_query.message, 0))
	WHERE mm.thread = ${genesis().id};
	`;
	await dbQuery(query);
	},
	],
	[
	49,
	async () => {
	if (isDockerEnvironment()) {
	return;
	}
	const defaultCorsConfig = {
	domain: 'http://localhost:3000',
	};
	await writeJSONToFile(defaultCorsConfig, 'facts/webapp_cors.json');
	},
	],
	[
	50,
	async () => {
	await moveToNonApacheConfig('facts/webapp_url.json', '/webapp/');
	await moveToNonApacheConfig('facts/keyserver_url.json', '/keyserver/');
	},
	],
	[
	51,
	async () => {
	if (permissionsToRemoveInMigration.length === 0) {
	return;
	}

	const setClause = SQL`permissions =
	JSON_REMOVE(permissions, ${permissionsToRemoveInMigration.map(
	path => `$.${path}`,
	)})`;

	const updateQuery = SQL`
	UPDATE roles r
	LEFT JOIN threads t ON t.id = r.thread
	`;

	updateQuery.append(SQL`SET `.append(setClause));

	updateQuery.append(SQL`
	WHERE r.name != 'Admins'
	AND (t.type = ${threadTypes.COMMUNITY_ROOT}
	OR t.type = ${threadTypes.COMMUNITY_ANNOUNCEMENT_ROOT})
	`);

	await dbQuery(updateQuery);
	},
	],
	[
	52,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE roles
	ADD COLUMN IF NOT EXISTS
	special_role tinyint(2) UNSIGNED DEFAULT NULL
	`,
	);

	await updateRolesAndPermissionsForAllThreads();
	},
	],
	[
	53,
	async () =>
	dbQuery(SQL`
	ALTER TABLE invite_links
	ADD COLUMN blob_holder char(36) CHARSET latin1
	`),
	],
	[
	54,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE roles
	ADD COLUMN IF NOT EXISTS special_role
	tinyint(2) UNSIGNED DEFAULT NULL,
	DROP KEY IF EXISTS thread,
	ADD KEY IF NOT EXISTS thread_special_role (thread, special_role);

	UPDATE roles r
	JOIN threads t ON r.id = t.default_role
	SET r.special_role = ${specialRoles.DEFAULT_ROLE};
	`,
	{ multipleStatements: true },
	);
	},
	],
	[
	55,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE threads
	DROP COLUMN IF EXISTS default_role
	`,
	);
	},
	],
	[
	56,
	async () => {
	await dbQuery(
	SQL`
	UPDATE roles
	SET special_role = ${specialRoles.ADMIN_ROLE}
	WHERE name = 'Admins'
	`,
	);
	},
	],
	[57, synchronizeInviteLinksWithBlobs],
	[
	58,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE updates
	MODIFY \`key\` varchar(255) CHARSET latin1 COLLATE latin1_bin
	`,
	);
	},
	],
	[59, () => dbQuery(SQL`DROP TABLE one_time_keys`)],
	[
	60,
	async () => {
	await dbQuery(
	SQL`
	DELETE
	FROM messages
	WHERE type = 22
	AND JSON_EXTRACT(content, '$.operation') = 'farcaster_mutual'
	`,
	);
	},
	],
	[
	61,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE uploads
	MODIFY container varchar(255) CHARSET latin1 COLLATE latin1_bin
	`,
	);
	},
	],
	[
	62,
	async () => {
	await dbQuery(
	SQL`
	ALTER TABLE uploads
	MODIFY container bigint(20)
	`,
	);
	},
	],
	+ [
	+ 63,
	+ async () => {
	+ await dbQuery(
	+ SQL`
	+ ALTER TABLE uploads
	+ ADD COLUMN user_container varchar(255)
	+ CHARSET latin1
	+ COLLATE latin1_bin
	+ DEFAULT NULL
	+ AFTER container,
	+ ADD INDEX user_container (user_container);
	+ UPDATE IGNORE uploads u
	+ INNER JOIN users us ON us.id = u.container
	+ SET u.container = NULL, u.user_container = us.id;
	+ `,
	+ { multipleStatements: true },
	+ );
	+ },
	+ ],
	]);
	const newDatabaseVersion: number = Math.max(...migrations.keys());

	async function writeJSONToFile(data: any, filePath: string): Promise<void> {
	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<void> {
	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<void> {
	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<void> {
	if (isDockerEnvironment()) {
	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<void> {
	if (isDockerEnvironment()) {
	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);
	}

	async function createOlmAccounts() {
	const [pickledContentAccount, pickledNotificationsAccount] =
	await Promise.all([createPickledOlmAccount(), createPickledOlmAccount()]);

	await saveNewOlmAccounts(pickledContentAccount, pickledNotificationsAccount);
	}

	async function saveNewOlmAccounts(
	pickledContentAccount: PickledOlmAccount,
	pickledNotificationsAccount: PickledOlmAccount,
	) {
	await dbQuery(
	SQL`
	INSERT INTO olm_accounts (is_content, version,
	pickling_key, pickled_olm_account)
	VALUES
	(
	TRUE,
	0,
	${pickledContentAccount.picklingKey},
	${pickledContentAccount.pickledAccount}
	),
	(
	FALSE,
	0,
	${pickledNotificationsAccount.picklingKey},
	${pickledNotificationsAccount.pickledAccount}
	);
	`,
	);
	}

	function isDockerEnvironment(): boolean {
	return !!process.env.COMM_DATABASE_HOST;
	}

	export {
	migrations,
	newDatabaseVersion,
	createOlmAccounts,
	saveNewOlmAccounts,
	};
	diff --git a/keyserver/src/database/setup-db.js b/keyserver/src/database/setup-db.js
	index c53be84e5..a65bfc399 100644
	--- a/keyserver/src/database/setup-db.js
	+++ b/keyserver/src/database/setup-db.js
	@@ -1,498 +1,501 @@
	// @flow

	import bots from 'lib/facts/bots.js';
	import genesis from 'lib/facts/genesis.js';
	import { usernameMaxLength } from 'lib/shared/account-utils.js';
	import { sortUserIDs } from 'lib/shared/relationship-utils.js';
	import { undirectedStatus } from 'lib/types/relationship-types.js';
	import { threadTypes } from 'lib/types/thread-types-enum.js';

	import { createThread } from '../creators/thread-creator.js';
	import { dbQuery, SQL } from '../database/database.js';
	import { updateDBVersion } from '../database/db-version.js';
	import {
	newDatabaseVersion,
	saveNewOlmAccounts,
	} from '../database/migration-config.js';
	import { createScriptViewer } from '../session/scripts.js';
	import { ensureUserCredentials } from '../user/checks.js';
	import { thisKeyserverAdmin, saveIdentityInfo } from '../user/identity.js';
	import { verifyUserLoggedInWithoutDB } from '../user/login.js';
	import { createPickledOlmAccount } from '../utils/olm-utils.js';

	async function setupDB() {
	const [pickledContentAccount, pickledNotificationsAccount] =
	await Promise.all([createPickledOlmAccount(), createPickledOlmAccount()]);

	const {
	identityInfo,
	pickledContentAccount: contentAccount,
	pickledNotificationsAccount: notificationsAccount,
	} = await verifyUserLoggedInWithoutDB(
	pickledContentAccount,
	pickledNotificationsAccount,
	);
	await ensureUserCredentials();
	await createTables();
	await saveNewOlmAccounts(contentAccount, notificationsAccount);
	await saveIdentityInfo(identityInfo);
	await createUsers();
	await createThreads();
	await setUpMetadataTable();
	}

	async function createTables() {
	await dbQuery(
	SQL`
	CREATE TABLE cookies (
	id bigint(20) NOT NULL,
	hash char(64) 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,
	special_role tinyint(2) UNSIGNED DEFAULT 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,
	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\` varchar(255) CHARSET latin1 COLLATE latin1_bin 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,
	+ user_container varchar(255)
	+ CHARSET latin1 COLLATE latin1_bin DEFAULT NULL,
	type varchar(255) CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL,
	filename varchar(255) NOT NULL,
	mime varchar(255) CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL,
	content longblob NOT NULL,
	secret varchar(255) CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL,
	creation_time bigint(20) NOT NULL,
	extra json DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

	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 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(1023)
	) 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;

	CREATE TABLE invite_links (
	id bigint(20) NOT NULL,
	name varchar(255) CHARSET latin1 NOT NULL,
	\`primary\` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
	role bigint(20) NOT NULL,
	community bigint(20) NOT NULL,
	expiration_time bigint(20),
	limit_of_uses int UNSIGNED,
	number_of_uses int UNSIGNED NOT NULL DEFAULT 0,
	blob_holder char(36) CHARSET latin1
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	CREATE TABLE olm_sessions (
	cookie_id bigint(20) NOT NULL,
	is_content tinyint(1) NOT NULL,
	version bigint(20) NOT NULL,
	pickled_olm_session text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

	CREATE TABLE olm_accounts (
	is_content tinyint(1) NOT NULL,
	version bigint(20) NOT NULL,
	pickling_key text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL,
	pickled_olm_account text
	CHARACTER SET latin1
	COLLATE latin1_bin NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_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,
	ADD KEY user_role_thread (user, role, thread);

	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_special_role (thread, special_role),
	ADD UNIQUE KEY thread_name (thread, name);

	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 user_container (user_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 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);

	ALTER TABLE invite_links
	ADD PRIMARY KEY (id),
	ADD UNIQUE KEY (name),
	ADD INDEX community_primary (community, \`primary\`);

	ALTER TABLE olm_sessions
	ADD PRIMARY KEY (cookie_id, is_content);

	ALTER TABLE olm_accounts
	ADD PRIMARY KEY (is_content);
	`,
	{ multipleStatements: true },
	);
	}

	async function createUsers() {
	const admin = await thisKeyserverAdmin();

	const [user1, user2] = sortUserIDs(bots.commbot.userID, admin.id);
	const query = SQL`
	INSERT INTO ids (id, table_name)
	VALUES
	(${bots.commbot.userID}, 'users');
	INSERT INTO users (id, username, hash, avatar, creation_time)
	VALUES
	(${bots.commbot.userID}, 'commbot', '', NULL, 1530049900980),
	(${admin.id}, ${admin.username}, '', NULL, 1463588881886);
	INSERT INTO relationships_undirected (user1, user2, status)
	VALUES (${user1}, ${user2}, ${undirectedStatus.KNOW_OF});
	`;

	if (!isNaN(Number(admin.id))) {
	query.append(SQL`
	INSERT INTO ids (id, table_name)
	VALUES (${admin.id}, 'users');
	`);
	}
	await dbQuery(query, { 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 admin = await thisKeyserverAdmin();

	const ashoatViewer = createScriptViewer(admin.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: [admin.id],
	},
	createThreadOptions,
	);
	}

	async function setUpMetadataTable() {
	await updateDBVersion(newDatabaseVersion);
	}

	export { setupDB };

File Metadata

Mime Type: text/x-diff
Expires: Sat, Nov 23, 2:20 AM (1 d, 6 h)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 2559369
Default Alt Text: (44 KB)

No OneTemporaryActions

View Options

File Metadata

Event Timeline

No OneTemporary
Actions