mirror of
https://github.com/smogon/pokemon-showdown.git
synced 2026-04-25 15:40:31 -05:00
484 lines
16 KiB
TypeScript
484 lines
16 KiB
TypeScript
/**
|
|
* Modlog
|
|
* Pokemon Showdown - http://pokemonshowdown.com/
|
|
*
|
|
* Moderator actions are logged into a set of files known as the moderation log, or "modlog."
|
|
* This file handles reading, writing, and querying the modlog.
|
|
*
|
|
* @license MIT
|
|
*/
|
|
|
|
import {SQL, Utils, FS} from '../../lib';
|
|
import {Config} from '../config-loader';
|
|
|
|
// If a modlog query takes longer than this, it will be logged.
|
|
const LONG_QUERY_DURATION = 2000;
|
|
|
|
const MODLOG_SCHEMA_PATH = 'databases/schemas/modlog.sql';
|
|
const MODLOG_V2_MIGRATION_PATH = 'databases/migrations/modlog/v2.sql';
|
|
|
|
export const MODLOG_DB_PATH = Config.nofswriting ? ':memory:' : FS(`databases/modlog.db`).path;
|
|
|
|
const GLOBAL_PUNISHMENTS = [
|
|
'WEEKLOCK', 'LOCK', 'BAN', 'RANGEBAN', 'RANGELOCK', 'FORCERENAME',
|
|
'TICKETBAN', 'AUTOLOCK', 'AUTONAMELOCK', 'NAMELOCK', 'AUTOBAN', 'MONTHLOCK',
|
|
'AUTOWEEKLOCK', 'WEEKNAMELOCK', 'FORCEWEEKLOCK', 'FORCELOCK', 'FORCEMONTHLOCK',
|
|
'FORCERENAME OFFLINE',
|
|
];
|
|
|
|
const PUNISHMENTS = [
|
|
...GLOBAL_PUNISHMENTS, 'ROOMBAN', 'WEEKROOMBAN', 'UNROOMBAN', 'WARN', 'MUTE', 'HOURMUTE', 'UNMUTE',
|
|
'CRISISDEMOTE', 'UNLOCK', 'UNLOCKNAME', 'UNLOCKRANGE', 'UNLOCKIP', 'UNBAN',
|
|
'UNRANGEBAN', 'TRUSTUSER', 'UNTRUSTUSER', 'BLACKLIST', 'BATTLEBAN', 'UNBATTLEBAN',
|
|
'NAMEBLACKLIST', 'KICKBATTLE', 'UNTICKETBAN', 'HIDETEXT', 'HIDEALTSTEXT', 'REDIRECT',
|
|
'NOTE', 'MAFIAHOSTBAN', 'MAFIAUNHOSTBAN', 'MAFIAGAMEBAN', 'MAFIAUNGAMEBAN', 'GIVEAWAYBAN', 'GIVEAWAYUNBAN',
|
|
'TOUR BAN', 'TOUR UNBAN', 'UNNAMELOCK', 'PERMABLACKLIST',
|
|
];
|
|
|
|
export type ModlogID = RoomID | 'global' | 'all';
|
|
interface SQLQuery {
|
|
query: string;
|
|
args: (string | number)[];
|
|
}
|
|
interface ModlogResults {
|
|
results: (ModlogEntry & {entryID: number})[];
|
|
duration: number;
|
|
}
|
|
|
|
interface ModlogSQLQuery<T> {
|
|
queryText: string;
|
|
args: T[];
|
|
returnsResults?: boolean;
|
|
}
|
|
|
|
export interface ModlogSearch {
|
|
note: {search: string, isExact?: boolean, isExclusion?: boolean}[];
|
|
user: {search: string, isExact?: boolean, isExclusion?: boolean}[];
|
|
ip: {search: string, isExclusion?: boolean}[];
|
|
action: {search: string, isExclusion?: boolean}[];
|
|
actionTaker: {search: string, isExclusion?: boolean}[];
|
|
}
|
|
|
|
export interface ModlogEntry {
|
|
action: string;
|
|
roomID: string;
|
|
visualRoomID: string;
|
|
userid: ID | null;
|
|
autoconfirmedID: ID | null;
|
|
alts: ID[];
|
|
ip: string | null;
|
|
isGlobal: boolean;
|
|
loggedBy: ID | null;
|
|
note: string;
|
|
/** Milliseconds since the epoch */
|
|
time: number;
|
|
}
|
|
|
|
export interface TransactionArguments extends Record<string, unknown> {
|
|
entries: Iterable<ModlogEntry>;
|
|
modlogInsertionStatement: string;
|
|
altsInsertionStatement: string;
|
|
}
|
|
|
|
export type PartialModlogEntry = Partial<ModlogEntry> & {action: string};
|
|
|
|
export class Modlog {
|
|
readonly database: SQL.DatabaseManager;
|
|
readyPromise: Promise<void> | null;
|
|
private databaseReady: boolean;
|
|
/** entries to be written once the DB is ready */
|
|
queuedEntries: ModlogEntry[];
|
|
|
|
modlogInsertionQuery: SQL.Statement | null = null;
|
|
altsInsertionQuery: SQL.Statement | null = null;
|
|
renameQuery: SQL.Statement | null = null;
|
|
globalPunishmentsSearchQuery: SQL.Statement | null = null;
|
|
|
|
constructor(databasePath: string, options: Partial<SQL.Options>) {
|
|
this.queuedEntries = [];
|
|
this.databaseReady = false;
|
|
if (!options.onError) {
|
|
options.onError = (error, data, isParent) => {
|
|
if (!isParent) return;
|
|
Monitor.crashlog(error, 'A modlog SQLite query', {
|
|
query: JSON.stringify(data),
|
|
});
|
|
};
|
|
}
|
|
this.database = SQL(module, {
|
|
file: databasePath,
|
|
extension: 'server/modlog/transactions.js',
|
|
...options,
|
|
});
|
|
|
|
if (Config.usesqlite) {
|
|
if (this.database.isParentProcess) {
|
|
this.database.spawn(Config.modlogprocesses || 1);
|
|
} else {
|
|
global.Monitor = {
|
|
crashlog(error: Error, source = 'A modlog child process', details: AnyObject | null = null) {
|
|
const repr = JSON.stringify([error.name, error.message, source, details]);
|
|
process.send!(`THROW\n@!!@${repr}\n${error.stack}`);
|
|
},
|
|
};
|
|
process.on('uncaughtException', err => {
|
|
Monitor.crashlog(err, 'A modlog database process');
|
|
});
|
|
process.on('unhandledRejection', err => {
|
|
Monitor.crashlog(err as Error, 'A modlog database process');
|
|
});
|
|
}
|
|
}
|
|
|
|
this.readyPromise = this.setupDatabase().then(result => {
|
|
this.databaseReady = result;
|
|
this.readyPromise = null;
|
|
});
|
|
}
|
|
|
|
async setupDatabase() {
|
|
if (!Config.usesqlite) return false;
|
|
await this.database.exec("PRAGMA foreign_keys = ON;");
|
|
await this.database.exec(`PRAGMA case_sensitive_like = true;`);
|
|
|
|
// Set up tables, etc
|
|
const dbExists = await this.database.get(`SELECT * FROM sqlite_master WHERE name = 'modlog'`);
|
|
if (!dbExists) {
|
|
await this.database.runFile(MODLOG_SCHEMA_PATH);
|
|
}
|
|
|
|
const {hasDBInfo} = await this.database.get(
|
|
`SELECT count(*) AS hasDBInfo FROM sqlite_master WHERE type = 'table' AND name = 'db_info'`
|
|
);
|
|
|
|
if (hasDBInfo === 0) {
|
|
// needs v2 migration
|
|
const warnFunction = ('Monitor' in global && Monitor.warn) ? Monitor.warn : console.log;
|
|
warnFunction(`The modlog database is being migrated to version 2; this may take a while.`);
|
|
await this.database.runFile(MODLOG_V2_MIGRATION_PATH);
|
|
warnFunction(`Modlog database migration complete.`);
|
|
}
|
|
|
|
this.modlogInsertionQuery = await this.database.prepare(
|
|
`INSERT INTO modlog (timestamp, roomid, visual_roomid, action, userid, autoconfirmed_userid, ip, action_taker_userid, is_global, note)` +
|
|
` VALUES ($time, $roomID, $visualRoomID, $action, $userid, $autoconfirmedID, $ip, $loggedBy, $isGlobal, $note)`
|
|
);
|
|
this.altsInsertionQuery = await this.database.prepare(`INSERT INTO alts (modlog_id, userid) VALUES (?, ?)`);
|
|
this.renameQuery = await this.database.prepare(`UPDATE modlog SET roomid = ? WHERE roomid = ?`);
|
|
this.globalPunishmentsSearchQuery = await this.database.prepare(
|
|
`SELECT * FROM modlog WHERE is_global = 1 ` +
|
|
`AND (userid = ? OR autoconfirmed_userid = ? OR EXISTS(SELECT * FROM alts WHERE alts.modlog_id = modlog.modlog_id AND userid = ?)) ` +
|
|
`AND timestamp > ? ` +
|
|
`AND action IN (${Utils.formatSQLArray(GLOBAL_PUNISHMENTS, [])})`
|
|
);
|
|
await this.writeSQL(this.queuedEntries);
|
|
return true;
|
|
}
|
|
|
|
/******************
|
|
* Helper methods *
|
|
******************/
|
|
getSharedID(roomid: ModlogID): ID | false {
|
|
return roomid.includes('-') ? `${toID(roomid.split('-')[0])}-rooms` as ID : false;
|
|
}
|
|
|
|
/**************************************
|
|
* Methods for writing to the modlog. *
|
|
**************************************/
|
|
|
|
/**
|
|
* @deprecated Modlogs use SQLite and no longer need initialization.
|
|
*/
|
|
initialize(roomid: ModlogID) {
|
|
return;
|
|
}
|
|
|
|
|
|
/**
|
|
* Writes to the modlog
|
|
*/
|
|
async write(roomid: string, entry: PartialModlogEntry, overrideID?: string) {
|
|
if (!Config.usesqlite || !Config.usesqlitemodlog) return;
|
|
const roomID = entry.roomID || roomid;
|
|
const insertableEntry: ModlogEntry = {
|
|
action: entry.action,
|
|
roomID,
|
|
visualRoomID: overrideID || entry.visualRoomID || '',
|
|
userid: entry.userid || null,
|
|
autoconfirmedID: entry.autoconfirmedID || null,
|
|
alts: entry.alts ? [...new Set(entry.alts)] : [],
|
|
ip: entry.ip || null,
|
|
isGlobal: entry.isGlobal || roomID === 'global' || false,
|
|
loggedBy: entry.loggedBy || null,
|
|
note: entry.note || '',
|
|
time: entry.time || Date.now(),
|
|
};
|
|
|
|
await this.writeSQL([insertableEntry]);
|
|
}
|
|
|
|
async writeSQL(entries: Iterable<ModlogEntry>) {
|
|
if (!Config.usesqlite) return;
|
|
if (!this.databaseReady) {
|
|
this.queuedEntries.push(...entries);
|
|
return;
|
|
}
|
|
const toInsert: TransactionArguments = {
|
|
entries,
|
|
modlogInsertionStatement: this.modlogInsertionQuery!.toString(),
|
|
altsInsertionStatement: this.altsInsertionQuery!.toString(),
|
|
};
|
|
await this.database.transaction('insertion', toInsert);
|
|
}
|
|
|
|
/**
|
|
* @deprecated Modlogs use SQLite and no longer need to be destroyed
|
|
*/
|
|
async destroy(roomid: ModlogID) {
|
|
return Promise.resolve(undefined);
|
|
}
|
|
|
|
destroyAllSQLite() {
|
|
if (!this.database) return;
|
|
void this.database.destroy();
|
|
this.databaseReady = false;
|
|
}
|
|
|
|
destroyAll() {
|
|
this.destroyAllSQLite();
|
|
}
|
|
|
|
async rename(oldID: ModlogID, newID: ModlogID) {
|
|
if (!Config.usesqlite) return;
|
|
if (oldID === newID) return;
|
|
|
|
// rename SQL modlogs
|
|
if (this.readyPromise) await this.readyPromise;
|
|
if (this.databaseReady) {
|
|
await this.database.run(this.renameQuery!, [newID, oldID]);
|
|
} else {
|
|
// shouldn't happen since we await the ready promise and check that useslite is on
|
|
// but will still happen if usesqlite is enabled without a subsequent hotpatch
|
|
throw new Error(`Attempted to rename a room's modlog before the SQL database was ready.`);
|
|
}
|
|
}
|
|
|
|
/******************************************
|
|
* Methods for reading (searching) modlog *
|
|
******************************************/
|
|
async getGlobalPunishments(user: User | string, days = 30) {
|
|
if (!Config.usesqlite || !Config.usesqlitemodlog) return null;
|
|
return this.getGlobalPunishmentsSQL(toID(user), days);
|
|
}
|
|
|
|
async getGlobalPunishmentsSQL(userid: ID, days: number) {
|
|
if (this.readyPromise) await this.readyPromise;
|
|
|
|
if (!this.globalPunishmentsSearchQuery) {
|
|
throw new Error(`Modlog#globalPunishmentsSearchQuery is falsy but an SQL search function was called.`);
|
|
}
|
|
const args: (string | number)[] = [
|
|
userid, userid, userid, Date.now() - (days * 24 * 60 * 60 * 1000), ...GLOBAL_PUNISHMENTS,
|
|
];
|
|
const results = await this.database.all(this.globalPunishmentsSearchQuery, args);
|
|
return results.length;
|
|
}
|
|
|
|
/**
|
|
* Searches the modlog.
|
|
*
|
|
* @returns Either a promise for ModlogResults or `null` if modlog is disabled.
|
|
*/
|
|
async search(
|
|
roomid: ModlogID = 'global',
|
|
search: ModlogSearch = {note: [], user: [], ip: [], action: [], actionTaker: []},
|
|
maxLines = 20,
|
|
onlyPunishments = false,
|
|
): Promise<ModlogResults | null> {
|
|
if (!Config.usesqlite || !Config.usesqlitemodlog) return null;
|
|
const startTime = Date.now();
|
|
|
|
let rooms: ModlogID[] | 'all';
|
|
if (roomid === 'public') {
|
|
rooms = [...Rooms.rooms.values()]
|
|
.filter(room => !room.settings.isPrivate && !room.settings.isPersonal)
|
|
.map(room => room.roomid);
|
|
} else if (roomid === 'all') {
|
|
rooms = 'all';
|
|
} else {
|
|
rooms = [roomid];
|
|
}
|
|
|
|
if (this.readyPromise) await this.readyPromise;
|
|
if (!this.databaseReady) return null;
|
|
const query = this.prepareSQLSearch(rooms, maxLines, onlyPunishments, search);
|
|
const results = (await this.database.all(query.queryText, query.args))
|
|
.map((row: any) => this.dbRowToModlogEntry(row));
|
|
|
|
const duration = Date.now() - startTime;
|
|
if (duration > LONG_QUERY_DURATION) {
|
|
Monitor.slow(`[slow SQL modlog search] ${duration}ms - ${JSON.stringify(query)}`);
|
|
}
|
|
return {results, duration};
|
|
}
|
|
|
|
dbRowToModlogEntry(row: any): ModlogEntry & {entryID: number} {
|
|
return {
|
|
entryID: row.modlog_id,
|
|
action: row.action,
|
|
roomID: row.roomid,
|
|
visualRoomID: row.visual_roomid,
|
|
userid: row.userid,
|
|
autoconfirmedID: row.autoconfirmed_userid,
|
|
alts: row.alts?.split(',') || [],
|
|
ip: row.ip || null,
|
|
isGlobal: Boolean(row.is_global),
|
|
loggedBy: row.action_taker_userid,
|
|
note: row.note,
|
|
time: row.timestamp,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* This is a helper method to build SQL queries optimized to better utilize indices.
|
|
* This was discussed in https://psim.us/devdiscord (although the syntax is slightly different in practice):
|
|
* https://discord.com/channels/630837856075513856/630845310033330206/766736895132303371
|
|
*
|
|
* @param select A query fragment of the form `SELECT ... FROM ...`
|
|
* @param ors Each OR condition fragment (e.g. `userid = ?`)
|
|
* @param ands Each AND conditions to be appended to every OR condition (e.g. `roomid = ?`)
|
|
* @param sortAndLimit A fragment of the form `ORDER BY ... LIMIT ...`
|
|
*/
|
|
buildParallelIndexScanQuery(
|
|
select: string,
|
|
ors: SQLQuery[],
|
|
ands: SQLQuery[],
|
|
sortAndLimit: SQLQuery
|
|
): ModlogSQLQuery<string | number> {
|
|
if (!this.database) throw new Error(`Parallel index scan queries cannot be built when SQLite is not enabled.`);
|
|
// assemble AND fragment
|
|
let andQuery = ``;
|
|
const andArgs = [];
|
|
for (const and of ands) {
|
|
if (andQuery.length) andQuery += ` AND `;
|
|
andQuery += and.query;
|
|
andArgs.push(...and.args);
|
|
}
|
|
|
|
// assemble query
|
|
let query = ``;
|
|
const args = [];
|
|
if (!ors.length) {
|
|
query = `${select} ${andQuery ? ` WHERE ${andQuery}` : ``}`;
|
|
args.push(...andArgs);
|
|
} else {
|
|
for (const or of ors) {
|
|
if (query.length) query += ` UNION `;
|
|
query += `SELECT * FROM (${select} WHERE ${or.query} ${andQuery ? ` AND ${andQuery}` : ``} ${sortAndLimit.query})`;
|
|
args.push(...or.args, ...andArgs, ...sortAndLimit.args);
|
|
}
|
|
}
|
|
query += ` ${sortAndLimit.query}`;
|
|
args.push(...sortAndLimit.args);
|
|
|
|
return {
|
|
queryText: query,
|
|
args,
|
|
};
|
|
}
|
|
|
|
prepareSQLSearch(
|
|
rooms: ModlogID[] | 'all',
|
|
maxLines: number,
|
|
onlyPunishments: boolean,
|
|
search: ModlogSearch
|
|
): ModlogSQLQuery<string | number> {
|
|
const select = `SELECT *, (SELECT group_concat(userid, ',') FROM alts WHERE alts.modlog_id = modlog.modlog_id) as alts FROM modlog`;
|
|
const ors = [];
|
|
const ands = [];
|
|
const sortAndLimit = {query: `ORDER BY timestamp DESC`, args: []} as SQLQuery;
|
|
if (maxLines) {
|
|
sortAndLimit.query += ` LIMIT ?`;
|
|
sortAndLimit.args.push(maxLines);
|
|
}
|
|
|
|
// Limit the query to only the specified rooms, treating "global" as a pseudo-room that checks is_global
|
|
// (This is because the text modlog system gave global modlog entries their own file, as a room would have.)
|
|
if (rooms !== 'all') {
|
|
const args: (string | number)[] = [];
|
|
let roomChecker = `roomid IN (${Utils.formatSQLArray(rooms, args)})`;
|
|
if (rooms.includes('global')) {
|
|
if (rooms.length > 1) {
|
|
roomChecker = `(is_global = 1 OR ${roomChecker})`;
|
|
} else {
|
|
roomChecker = `is_global = 1`;
|
|
// remove the room argument added by the initial roomChecker assignment
|
|
args.pop();
|
|
}
|
|
}
|
|
ands.push({query: roomChecker, args});
|
|
}
|
|
|
|
for (const action of search.action) {
|
|
const args = [action.search + '%'];
|
|
if (action.isExclusion) {
|
|
ands.push({query: `action NOT LIKE ?`, args});
|
|
} else {
|
|
ands.push({query: `action LIKE ?`, args});
|
|
}
|
|
}
|
|
if (onlyPunishments) {
|
|
const args: (string | number)[] = [];
|
|
ands.push({query: `action IN (${Utils.formatSQLArray(PUNISHMENTS, args)})`, args});
|
|
}
|
|
|
|
for (const ip of search.ip) {
|
|
const args = [ip.search + '%'];
|
|
if (ip.isExclusion) {
|
|
ands.push({query: `ip NOT LIKE ?`, args});
|
|
} else {
|
|
ands.push({query: `ip LIKE ?`, args});
|
|
}
|
|
}
|
|
for (const actionTaker of search.actionTaker) {
|
|
const args = [actionTaker.search + '%'];
|
|
if (actionTaker.isExclusion) {
|
|
ands.push({query: `action_taker_userid NOT LIKE ?`, args});
|
|
} else {
|
|
ands.push({query: `action_taker_userid LIKE ?`, args});
|
|
}
|
|
}
|
|
|
|
for (const noteSearch of search.note) {
|
|
const tester = noteSearch.isExact ? `= ?` : `LIKE ?`;
|
|
const args = [noteSearch.isExact ? noteSearch.search : `%${noteSearch.search}%`];
|
|
if (noteSearch.isExclusion) {
|
|
ands.push({query: `note ${noteSearch.isExact ? '!' : 'NOT '}${tester}`, args});
|
|
} else {
|
|
ands.push({query: `note ${tester}`, args});
|
|
}
|
|
}
|
|
|
|
for (const user of search.user) {
|
|
let tester;
|
|
let param;
|
|
if (user.isExact) {
|
|
tester = user.isExclusion ? `!= ?` : `= ?`;
|
|
param = user.search.toLowerCase();
|
|
} else {
|
|
tester = user.isExclusion ? `NOT LIKE ?` : `LIKE ?`;
|
|
param = user.search.toLowerCase() + '%';
|
|
}
|
|
|
|
ors.push({query: `(userid ${tester} OR autoconfirmed_userid ${tester})`, args: [param, param]});
|
|
ors.push({
|
|
query: `EXISTS(SELECT * FROM alts WHERE alts.modlog_id = modlog.modlog_id AND alts.userid ${tester})`,
|
|
args: [param],
|
|
});
|
|
}
|
|
return this.buildParallelIndexScanQuery(select, ors, ands, sortAndLimit);
|
|
}
|
|
}
|
|
|
|
export const mainModlog = new Modlog(MODLOG_DB_PATH, {sqliteOptions: Config.modlogsqliteoptions});
|