Function validateDatabaseCharset

api/src/database/index.ts:345–389

(database?: Knex)

}

async function validateDatabaseCharset(database?: Knex): Promise<void> {
	const env = useEnv();
	database = database ?? getDatabase();
	const logger = useLogger();

	if (getDatabaseClient(database) === 'mysql') {
		const { collation } = await database.select(database.raw(`@@collation_database as collation`)).first();

		const tables = await database('information_schema.tables')
			.select({ name: 'TABLE_NAME', collation: 'TABLE_COLLATION' })
			.where({ TABLE_SCHEMA: env['DB_DATABASE'] });

		const columns = await database('information_schema.columns')
			.select({ table_name: 'TABLE_NAME', name: 'COLUMN_NAME', collation: 'COLLATION_NAME' })
			.where({ TABLE_SCHEMA: env['DB_DATABASE'] })
			.whereNot({ COLLATION_NAME: collation });

		const excludedTables: string[] = toArray(env['DB_EXCLUDE_TABLES']);

		let inconsistencies = '';

		for (const table of tables) {
			if (excludedTables.includes(table.name)) continue;

			const tableColumns = columns.filter((column) => column.table_name === table.name);
			const tableHasInvalidCollation = table.collation !== collation;

			if (tableHasInvalidCollation || tableColumns.length > 0) {
				inconsistencies += `\t\t- Table "${table.name}": "${table.collation}"\n`;

				for (const column of tableColumns) {
					inconsistencies += `\t\t  - Column "${column.name}": "${column.collation}"\n`;
				}
			}
		}

		if (inconsistencies) {
			logger.warn(
				`Some tables and columns do not match your database's default collation (${collation}):\n${inconsistencies}`,
			);
		}
	}

	return;
}